Picking the Right Storage Shape
Picking a shape for a new workload comes down to 3 questions, asked in order: how will the data be read, how fresh does it need to be, and how much of it is there. The answers usually point at 1 shape, and when they point at 2, the second is a copy fed by a pipeline.
| Question | If the answer is | Points toward |
|---|---|---|
| Access pattern | 1 row by key, read and written by the app | Operational database |
| Access pattern | Many rows, few columns, summed or counted | Data warehouse |
| Access pattern | Whole files reread, raw fields kept for years, formats mixed | Data lake |
| Freshness | The instant it is written | Operational database, where the write happens |
| Freshness | Minutes to hours behind the write | Warehouse or lake, fed by a batch or streaming copy |
| Freshness | Days old is fine; read once or rarely | Data lake |
| Scale | Gigabytes of hot, changing rows | Operational database |
| Scale | Terabytes of typed tables | Data warehouse |
| Scale | Terabytes to petabytes, mixed formats, each byte read rarely | Data lake |
The operational database is the freshest copy by definition, which tempts people to read it for everything. Ask instead whether the consumer can tell the difference. A dashboard opened at 9 am and compared with yesterday can't tell whether the orders are 10 seconds or 10 hours old, so it belongs on a warehouse loaded overnight. A courier deciding which door to knock on can tell, so that read stays on the operational database.
Placing 5 workloads
| Workload | Access | Freshness | Scale | Shape |
|---|---|---|---|---|
| Courier app shows 1 order | 1 row by id, then an update | Now | Gigabytes, hot | Operational database |
| Morning revenue dashboard | Sum 3 columns over 60 million rows | Daily | Terabytes | Warehouse |
| Train a delivery time model on 2 years of events | Every field of every file | Weekly | Tens of terabytes | Lake, raw and cleaned |
| Month-end finance close | Typed joins across 3 tables | Monthly | Terabytes | Warehouse |
| Fix a cleaning rule and rebuild 3 days | Reread the original files | Once | Terabytes | Lake, raw zone |
- Checkout app (source, mobile and web)
- Operational database (storage, row layout, ACID)
- Lake raw zone (bronze, JSON files)
- Lake cleaned zone (silver, Parquet files)
- Warehouse (gold, columnar tables)
- Revenue dashboard (consumer, every morning)
- Model training (consumer, weekly)
- Checkout app -> Operational database
- Operational database -> Lake raw zone
- Lake raw zone -> Lake cleaned zone
- Lake cleaned zone -> Warehouse
- Warehouse -> Revenue dashboard
- Lake cleaned zone -> Model training
- Application: Checkout app, Operational database
- Lake: Lake raw zone, Lake cleaned zone
- Serving: Warehouse, Revenue dashboard, Model training
Read the diagram left to right. The operational database is the only place a person writes an order. Everything to its right is a copy carried by a nightly batch or a stream, and each reader takes the copy whose shape and freshness fit its need.
A support agent has a customer on the phone and needs to see the customer's 5 most recent orders, each with its current status. Where should the support tool read?
- The warehouse, because it holds every order with typed columns the tool can trust.
- The lake's cleaned zone, because the Parquet files hold every order event in order.
- The lake's curated zone, because a per-customer table could be built for support.
- The operational database, because it has the current status and finds rows by customer_id in milliseconds.
3 placement mistakes
Each layer you add is also a pipeline you own, so add a shape when its question arrives. A new app with 2 GB of orders can run its morning report on a nightly copy of the operational database for a year. The warehouse arrives when that report takes minutes, and the lake when someone needs events a cleaning rule threw away or untyped events pile up. Pipeline Anatomy's test for when a pipeline is not needed applies to storage layers too.
The app team wants to capture every tap and scroll, about 50 billion events a month with no agreed fields yet. For the first quarter, only 1 exploring team reads them. Where should the events land first?
- A warehouse table with every field declared as VARCHAR, so the schema can be fixed later.
- The lake's raw zone as files, in whatever format the app emits them.
- An events table in the operational database, next to orders, so the app writes once.
- The lake's curated zone as Parquet aggregates, counting taps per screen per day.
About this lesson
This section is part of the Storage Layers: Beginner 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.