Architecting Discord’s Data Platform: Processing Trillions of Points with Derive

When modern software organizations scale, they inevitably adopt a microservices architecture. In this paradigm, services are isolated by domain boundaries, each backed by its own purpose-built database—an authentication service might leverage MongoDB, while a payment or billing service relies on MySQL.

While this decentralization offers autonomy and operational isolation, it introduces critical challenges for cross-domain intelligence. Product analytics, business intelligence, data science, and machine learning models require a holistic, unified view of the entire organization’s data. To tackle this at massive scale—processing trillions of data points and petabytes of data daily—Discord built Derive, a declarative, self-serve data platform.

---

Why Operational Databases Cannot Power Analytics

A naive approach to data analysis is querying production operational databases directly. In practice, this breaks down immediately across several dimensions:

1. Heterogeneous Query Interfaces: When services choose distinct database technologies (e.g., key-value stores, document stores, relational databases), there is no single standardized query engine to pull information across them.
2. Distributed Cross-Database Joins: Executing distributed joins across disparate transactional databases across a network is computationally prohibitive, brittle, and introduces immense network overhead.
3. OLTP Workload Degradation: Transactional databases are optimized for low-latency, high-concurrency point reads and writes (OLTP). Running heavy, long-running analytical queries with wide table scans can monopolize CPU, choke disk I/O, lock tables, and severely degrade user-facing production traffic.
4. Unstructured and Semi-Structured Formats: Raw data stored in operational stores may be denormalized or semi-structured, requiring cleansing, schema standardization, and normalization before downstream consumers can reliably query it.

To decouple analytical workloads from transactional systems, modern data infrastructure leverages a centralized Data Warehouse or Data Lake (such as Google BigQuery, Amazon Redshift, or Apache Iceberg on top of distributed object storage).

+-----------------------+     +-----------------------+
|  Profile Service (DB) |     |  Payment Service (DB) |
+-----------+-----------+     +-----------+-----------+
            |                             |
            +------------+   +------------+
                         |   |
                         v   v
             +---------------------------+
             | Ingestion & Transformation|
             +-------------+-------------+
                           |
                           v
             +---------------------------+
             |  Unified Data Warehouse   |
             |   (e.g., Google BigQuery) |
             +---------------------------+

---

Discord’s Three-Tier Data Model

To manage trillions of records without placing an unmanageable burden on the central data engineering team, Discord organized their data warehouse model into a clear three-tier hierarchy:

[ Transactional Tables ] (Service-level OLTP databases)
           │
           ▼
    [ Core Tables ]      (Cleaned, unified base entities)
           │
           ▼
   [ Derived Tables ]    (Domain-specific materialized views/aggregations)

1. Transactional Tables

These are direct or semi-direct replicas of individual microservice production datastores (e.g., user profiles, raw messages, billing events). They represent the operational state of the world.

2. Core Tables

Core tables represent foundational, normalized domain abstractions generated by merging tightly coupled operational sources.

• Example: An auth table (holding credentials, emails, and account state) and a profile table (holding user preferences, avatars, and locales) have a natural 1:1 relationship. Joining these into a consolidated user_details core table creates an authoritative entity that downstream analytics jobs can query without repeating the same join logic.

3. Derived Tables

Derived tables are custom, domain-specific projections and aggregations generated directly from core tables or other derived tables. Individual product teams—such as Trust & Safety, Machine Learning, Analytics, or Growth—define their own derived tables to tailor views specifically for their business logic (e.g., payment cohorts, user retention curves, or recommendation candidate sets).

---

Declarative Self-Serve Pipelines via YAML

To prevent data engineering teams from becoming a bottleneck for every new query or metric, Discord built Derive around a declarative self-serve interface. Internal stakeholders define what they need in a structured YAML configuration file, and the platform automatically handles scheduling, execution, data placement, and fault tolerance.

Structure of a Derive Configuration

A typical configuration encapsulates the complete lifecycle of a derived dataset:

table_name: user_payment_summary
description: Aggregated payment statistics per verified user

schema:
  - name: user_id
    type: STRING
    mode: REQUIRED
  - name: total_spent
    type: FLOAT
    mode: REQUIRED
  - name: transaction_count
    type: INTEGER
    mode: REQUIRED
  - name: last_payment_date
    type: TIMESTAMP
    mode: NULLABLE

# Execution strategy: merge | append | replace
strategy: merge
merge_key: user_id

# Execution Cadence and Processing Window
schedule: "0 * * * *"  # Every hour
window: 1h

# Physical Storage Layout (BigQuery optimization)
partition_by:
  field: last_payment_date
  data_type: timestamp
cluster_by:
  - user_id

# Transformation logic
query: >
  SELECT 
    u.id AS user_id,
    SUM(p.amount) AS total_spent,
    COUNT(p.id) AS transaction_count,
    MAX(p.timestamp) AS last_payment_date
  FROM core.user_details u
  INNER JOIN core.payments p ON u.id = p.user_id
  WHERE p.timestamp >= @window_start AND p.timestamp < @window_end
  GROUP BY u.id;

Key Pipeline Parameters

• Ingestion Strategy:
  • replace: Drops and recreates the target table entirely. Ideal for small, slowly changing dimensions or lookup sets.
  • append: Inserts only new, immutable event rows (common for log streams, telemetry, or clickstream events).
  • merge: Executes an UPSERT using a defined primary key. Updates existing rows if matched, or appends new records otherwise.
• Partitioning and Clustering: Because analytical tables scale to billions or trillions of rows, query performance and cost-efficiency depend heavily on physical data layout. By specifying partition keys (e.g., date ranges) and clustering keys (e.g., entity IDs), downstream queries scan only relevant storage segments, preventing expensive full-table scans.

---

System Architecture & Execution Engine

Behind the declarative configuration lies an orchestration engine managed inside Kubernetes clusters and synchronized with stateful metadata tracking.

graph TD
    YAML[YAML Pipeline Definitions] --> K8s[Kubernetes Pod / Worker]
    Meta[(Metadata Log / State)] <--> K8s
    Sources[(Transactional / Core Data)] --> K8s
    K8s -->|Execute & Materialize| BQ[(Google BigQuery OLAP)]
    BQ --> InternalConsumers[Insights, Product, & ML Teams]
    BQ -->|Export Pipeline / Airflow| Scylla[(ScyllaDB Low-Latency Serving)]
    Scylla --> OnlineServices[Online Prediction / User-Facing Services]

Execution Flow

1. Pipeline Instantiation: Each YAML specification maps to isolated runner workloads scheduled within Kubernetes.
2. Metadata Log & Incremental Processing: Processing petabytes of data from scratch on every run is computationally infeasible. Derive utilizes a centralized Metadata Log that maintains checkpoint states:
   • High-water mark offsets / IDs processed in the prior iteration.
   • Execution window boundaries (window_start to window_end).
   • Job runtime durations and performance metrics.
   • End-to-end data lineage across parent and child dependencies.
3. Query Dispatch: The worker queries the source tables incrementally, processes the transformation logic, and pushes the output into Google BigQuery according to the specified strategy (merge, append, or replace).

---

Bridging OLAP and OLTP: The Real-Time Serving Layer

While Google BigQuery is an exceptional tool for OLAP (Online Analytical Processing) workloads, it is fundamentally an asynchronous, distributed columnar data warehouse. Query execution times range from hundreds of milliseconds to several seconds or minutes—making BigQuery unsuitable for user-facing, sub-millisecond production APIs.

Consider an online Machine Learning Prediction Service: when a user accesses a feature, the service must retrieve precomputed user features (e.g., aggregated affinity scores, historical interactions) within single-digit milliseconds to generate a real-time recommendation.

+--------------------+         +--------------------+         +-----------------------+
| BigQuery           | Export  | ScyllaDB           | Read    | Real-Time Prediction  |
| (Derived Tables)   +-------->+ (Distributed NoSQL)+-------->+ Service (API)         |
| High Throughput    | Pipeline| Sub-ms Latency     | (<10ms) | Low Latency Serving   |
+--------------------+         +--------------------+         +-----------------------+

Exporting to ScyllaDB

To solve this discrepancy between batch computation and real-time retrieval:

1. Complex feature engineering and table joins are performed inside BigQuery via Derive on a scheduled basis.
2. Specialized export pipelines (orchestrated via Apache Airflow) continuously extract materialized derived tables from BigQuery.
3. Data is loaded into ScyllaDB—a high-performance, distributed NoSQL database implemented in C++ (compatible with Apache Cassandra) optimized for extreme throughput and sub-millisecond point reads.
4. Online prediction and inference engines query ScyllaDB at runtime to serve live user requests with minimal latency.

---

Key Architectural Takeaways

Design Dimension	Operational OLTP Layer	Data Warehouse (OLAP)	Real-Time Serving (ScyllaDB)
Primary Workload	High-frequency point reads/writes	Massive batch aggregation/joins	High-throughput, low-latency lookups
Target Latency	Sub-10 milliseconds	Seconds to minutes	Sub-millisecond to single-digit ms
Data Structure	Highly normalized (or microservice document)	Denormalized, partitioned, clustered columnar	Key-value / wide-column keyed by entity
Primary Consumer	Production microservices	ML engineers, analysts, BI dashboards	Online ML models & user-facing APIs

Summary

Discord’s Derive platform demonstrates how modern hyper-scale systems balance analytical depth with operational stability:

• Decoupled Workloads: Shield transactional databases from heavy analytical queries by replicating operational state to a data warehouse.
• Standardized Layers: Establish clear tiers (Transactional $\to$ Core $\to$ Derived) to prevent repeated join computation and promote DRY data modeling.
• Declarative Self-Serve Model: Empower cross-functional teams to define datasets via YAML configurations, abstracting away the underlying cluster orchestration.
• Hybrid Ingestion & Serving: Pair columnar OLAP warehouses (for intensive batch transformations) with distributed NoSQL stores like ScyllaDB to bridge the gap between offline intelligence and real-time serving.