DDIA Learn — Designing Data-Intensive Applications

Transaction processing (OLTP) systems power live applications: checkout, account updates, messaging. Analytics (OLAP) systems answer business questions over months of history. Their hardware, schema, and storage layouts diverge completely.

Data Warehousing

A data warehouse ingests copies of production data via ETL, denormalizes into star schemas (fact tables surrounded by dimension tables), and optimizes for scan-heavy aggregation — not millisecond point lookups.

In practice

A Shopify-scale stack might use PostgreSQL or Aurora for live orders, then pipe data through Airbyte or Fivetran into Snowflake or BigQuery for BI dashboards. Running heavy aggregations directly on the production Postgres would starve checkout queries — exactly the anti-pattern DDIA warns about.

Shopify at scale

Checkout and inventory run on low-latency OLTP (PostgreSQL/Aurora). Merchant analytics and revenue dashboards query Snowflake star schemas fed by nightly ETL — never the live order database.

typescript — OLTP → warehouse ETL pipeline

// Shopify OLTP → OLAP — never run heavy scans on production Postgres
// Step 1: CDC from Aurora PostgreSQL
const changes = debezium.stream("shopify.orders");
// Step 2: land in Snowflake star schema for BI dashboards
await snowflake.merge("fact_orders", changes, { key: "order_id" });

Diagram

Transaction Processing or Analytics?

Data Warehousing