Choosing Storage Across Workloads
Most real datasets serve several access patterns at once. Take a grocery delivery marketplace with 1 orders dataset and 3 groups of users. The shopper app reads and updates single orders at up to 3,000 writes per second and needs answers in under 20ms. Finance and data science need 4 years of history and must reproduce month-end numbers and training sets exactly. A group of 300 analysts refresh revenue dashboards by store and category and expect charts in under a second.
| Access pattern | Query shape | Latency | Store |
|---|---|---|---|
| Shopper app | 1 row by order_id, read and update | under 20ms | Operational database |
| Finance, data science | full scans of history, pinned to a snapshot | minutes | Lakehouse archive |
| Analyst dashboards | aggregates by store and category, 300 users | under 1s | Warehouse mart |
No single store meets all 3. The operational database is a row store with indexes, fast for single-row reads and writes and slow for scans. The lakehouse is versioned and built for scans, but each query spends seconds reading manifests and opening files, while 300 analysts expect charts in under 1 second. The warehouse mart answers those charts because it holds a small, pre-aggregated table shaped for 1 query pattern.
How data moves between the 3 stores
- Shopper app (source, 3,000 writes/s)
- Operational DB (storage, row store, 90 days); freshness: live
- Hourly extract (transform, batch job)
- orders_raw (storage, lakehouse, append only); freshness: 1 hour
- Nightly curate (transform, batch job)
- orders_curated (storage, lakehouse, daily overwrite); freshness: daily
- Finance and DS (consumer, time travel queries)
- Mart build (transform, pins 1 snapshot id)
- revenue_mart (storage, warehouse table); freshness: by 06:00
- Dashboards (consumer, 300 analysts)
- Shopper app -> Operational DB
- Operational DB -> Hourly extract
- Hourly extract -> orders_raw
- orders_raw -> Nightly curate
- Nightly curate -> orders_curated
- orders_curated -> Finance and DS
- orders_curated -> Mart build
- Mart build -> revenue_mart
- revenue_mart -> Dashboards
- Serving: Shopper app, Operational DB
- Archive: Hourly extract, orders_raw, Nightly curate, orders_curated, Finance and DS
- Mart: Mart build, revenue_mart, Dashboards
Each store owns a different part of the data. The operational database owns the present and keeps 90 days, so its indexes stay small. The lakehouse owns history: every order ever placed, plus each recent version within retention. The mart owns nothing: it is a derived copy that can be dropped and rebuilt from orders_curated, which is what makes it safe to tune hard for the dashboards.
The September revenue report was published on 2026-10-01 with its snapshot id recorded. On 2026-11-03 finance rebuilds it from orders_curated and gets a total 0.4% higher, because October corrections changed September rows. Retention is 45 days. What reproduces the published number?
- Query orders_curated pinned to the recorded snapshot id, which is 33 days old and still retained
- Query September in the operational database, since it still holds those original order rows
- Rebuild from orders_raw with today's code, since raw appends are never corrected afterwards
- Compact orders_curated first so the September partition returns to its original files
Where it breaks, and what prevents it
| Stress | What breaks | Property that prevents it |
|---|---|---|
| Mart build runs while the nightly job commits | revenue mixes 2 versions of a day | snapshot isolation: the build pins 1 snapshot id |
| Mart build rerun after a failure | rows appended twice, revenue doubled | the build replaces the mart from 1 pinned snapshot |
| Operational database adds tip_amount | the extract drops the new field | ADD COLUMN in lakehouse metadata, old rows read NULL |
| Upstream renames store to store_id | a rename looks like a drop plus an add | extract maps names; lakehouse renames by field id |
| 24 hourly extracts a day into orders_raw | small files slow every history scan | nightly compaction of yesterday's partition |
| 300 analysts query orders_curated directly | per-file costs on every chart refresh | the mart, shaped for the dashboard query |
When to drop a layer
Every store is another copy to keep in sync and another place to fail. With 10 analysts happy with 10-second charts, querying orders_curated directly beats building a mart. With 30 days of history and no reproduction need, the operational database plus a warehouse could cover everything. Add a store only when an access pattern needs lower latency or more concurrency than the existing stores give, or more history than they keep, and record which pattern justified it.
A new design replaces the hourly extract with a streaming job that commits to orders_raw every 30 seconds using 40 writer tasks, and the mart build switches to reading orders_raw at 06:00. Which 2 problems does this create?
- Commit conflicts among the 40 tasks, and orders lost from the mart when a swap fails
- Columns going missing in the mart, and snapshots expiring partway through the 06:00 build
- Over 100,000 small files a day, and a mart built from raw rows that skip the nightly corrections
- Torn reads in the dashboards, and extra load on the operational database at 06:00
About this lesson
This section is part of the Storage Layers: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.