design·lab

Data & storage

Data Warehouse

One consolidated, query-optimized store that pulls data out of every operational system so analytics never has to touch — or wait on — production databases.

✗ The problem

Your data is siloed — and prod DBs aren't built for analytics

Orders live in one database, users in another, ad spend in a third-party SaaS. No single place holds them all together.

🧾 Orders DB
Postgres · OLTP
👤 Users DB
MySQL · OLTP
📢 Ads spend
3rd-party SaaS
✗ no shared query surface ✗
❓ "Revenue per campaign, per region?"
Each system is tuned for fast transactions, not cross-source joins. Pointing analytics queries straight at prod risks locking tables and slowing down checkout.
✓ How it works

Consolidate everything into one analytical store

An ETL pipeline extracts from every source, conforms it, and loads it into a warehouse built for scans, not single-row lookups (OLAP).

// nightly ETL job
extract(orders, users, ads)
  |> transform(clean, conform)
  |> load(warehouse)   // columnar, denormalized
Orders
Users
Ads
↓ ETL
Staging
raw landing zone
↓
Warehouse
conformed, query-optimized
↓
Marketing mart
Finance mart
✓ See it live

Load sources, then run one cross-source query

Click a source to load it into the warehouse, then run the analytical query. Revenue-by-campaign needs both Orders and Ads loaded.

🧾 Orders
click to load
👤 Users
click to load
📢 Ads
click to load
↓
🏬 Warehouse
0 sources loaded
no query run yet
✓ Takeaway

One place analytics can trust

  • Single source of truth for cross-team analytics — no more "which DB has this?"
  • Decoupled from operational databases — heavy reporting queries never touch prod.
  • Columnar + denormalized layout, built to scan millions of rows, not fetch one.
  • Marts per team narrow the warehouse into shapes each team actually queries.
  • Caution: it's a copy, loaded on a schedule — expect freshness lag, not real-time truth.
🎯 Related: loaded via ETL pipelines, modeled with a star/snowflake schema, queried with OLAP engines.
Back to all topics →