Catalog My learning Blog
System Design Roadmap: 100-Day Course for Engineers · Phase 3 · Lesson 29 · 5 min read

Polyglot Persistence, Data Lakes and Warehouses

On this page
  1. The report that slowed checkout
  2. OLTP vs OLAP
  3. What is a data warehouse?
  4. What is a data lake?
  5. ETL vs ELT
  6. What is change data capture (CDC)?
  7. Worked example: an e-commerce analytics setup
  8. Polyglot persistence: when several databases are worth it
  9. Check yourself

The report that slowed checkout

Every morning at 9, finance runs a sales report: total revenue by region and category for the last 18 months. It scans millions of rows and runs for minutes, on the same database that handles checkout. Checkout latency spikes every morning at 9. The report isn't wrong; it's in the wrong place.

OLTP vs OLAP

OLTP (online transaction processing) is what application databases do: many small reads and writes, each touching a few rows by key. "Place this order." "Show this customer's cart." Optimized for low latency and concurrency, with normalized schemas and row-oriented storage.

OLAP (online analytical processing) is what reports do: few queries, each scanning huge numbers of rows and aggregating them. "Revenue by month and region since 2024." Optimized for throughput over large scans.

Running OLAP on an OLTP database hurts both: reports are slow, and they steal resources from users.

You don't always need a warehouse to fix this. The cheapest first step is a reporting replica (Day 25): an asynchronous copy where long queries can't slow checkout. It works until reports need history the app doesn't keep, data from several systems, or scans the row-based database is simply too slow for. That's when a warehouse earns its cost.

What is a data warehouse?

A data warehouse is a database built for analytics: Snowflake, BigQuery, Amazon Redshift, Azure Synapse, or ClickHouse. Data is usually reshaped into a star schema: a central fact table of events with measures (fact_orders: amount, quantity) surrounded by dimension tables describing them (dim_customer, dim_product, dim_date). Storage is typically columnar: each column stored together and compressed, so a query summing amount reads only that column.

What is a data lake?

A data lake is cheap object storage (Amazon S3, Azure Data Lake Storage, Google Cloud Storage) holding data in raw form: JSON event logs, CSV exports, images, Parquet files. Store first, decide how to use it later. Query engines such as Spark, Trino and Athena read it directly. Many teams now combine both ideas in a lakehouse, using open table formats such as Apache Iceberg or Delta Lake on top of object storage.

The OLTP database feeds a data lake and a data warehouse through ETL or ELT pipelines, kept separate from live production traffic.
The OLTP database feeds a data lake and a data warehouse through ETL or ELT pipelines, kept separate from live production traffic.

ETL vs ELT

  • ETL (extract, transform, load): pull data out, reshape it, then load it into the warehouse.
  • ELT (extract, load, transform): load raw data into the warehouse or lake first, then transform it there with SQL, often using a tool like dbt. Common today, because warehouses are powerful and raw data stays available for new questions.

Pipelines run nightly in batches or continuously with change data capture (CDC).

What is change data capture (CDC)?

Nightly exports with SELECT * WHERE updated_at > yesterday miss deletes, depend on every write updating the timestamp, and load the database during the copy. CDC reads the database's own write log instead, the same log replication uses (Day 25), and turns every committed insert, update and delete into an event, in commit order. The application doesn't change at all.

Debezium reads PostgreSQL's logical decoding, MySQL's binlog or SQL Server's built-in CDC tables, and publishes changes to Kafka. From there, the warehouse, a search index (Day 23) and caches (Day 36) can all stay in sync. Two things to plan for: an initial snapshot of existing rows before streaming starts, and schema changes, which downstream consumers must handle.

Worked example: an e-commerce analytics setup

  • Orders, customers and inventory stay in PostgreSQL (OLTP).
  • CDC streams order changes into the warehouse, where dbt builds fact_orders and dimension tables. Finance's report now runs there in seconds and never touches checkout.
  • Clickstream events (every page view and search) land as raw JSON in the data lake. When product wants to study search behavior, analysts shape what they need from the lake into the warehouse.

Polyglot persistence: when several databases are worth it

Using several data stores, each chosen for a job, is called polyglot persistence: PostgreSQL for transactions, Redis for sessions, a search engine for product search, a warehouse for analytics. It's powerful, and each store adds backups, monitoring, upgrades, security and expertise. Add one when a clear workload needs it, not because a diagram looks complete.

Check yourself

Large scans compete for CPU, memory and I/O with user transactions, slowing the product, and OLTP storage is not designed for fast large aggregations.

Raw clickstream JSON. It's high volume and its future uses aren't known yet, so it's stored cheaply in raw form and shaped into warehouse tables only when a question needs it.

A deleted row has no updated_at to find, so the query never sees it. Change data capture reads deletes from the database log along with inserts and updates.

ETL transforms data before loading it into the warehouse. ELT loads raw data first and transforms it inside the warehouse, using its compute. Modern cloud warehouses favor ELT because they scale transformation easily and keep the raw data for reprocessing.

CDC reads the database's transaction log and streams every insert, update and delete as it happens. Unlike updated_at queries, it captures deletes, doesn't miss rows with skewed clocks and doesn't load the source with heavy queries.