Warehouse Architecture
The governed engine under every dashboard
A data warehouse is the governed storage layer BI reports query: columnar engines, explicit schemas, SQL access and orchestrated refreshes. Understanding its architecture explains why dashboards are fast and why they can break.
What you will learn
- Describe how a warehouse separates storage and compute
- Explain columnar storage and why it suits BI queries
- Map the data flows that keep a warehouse fresh
What is this?
A data warehouse is a managed analytical database that stores modeled data in columnar form, exposes SQL, and separates storage from compute so queries scale independently of cost. It is the serving brain of the BI stack: refresh jobs land data across the night, reports query it all day, and governance (schemas, roles, row-level policy) lives with the engine.
Why do you need it?
- Columnar storage makes aggregation over millions of rows fast and cheap.
- Separating storage and compute lets you pay for compute when you query, not when you store.
- A governed schema layer prevents the 'every report defines its own reality' problem.
- Concurrency-aware warehouses keep dashboards interactive during business hours.
Mental model
A centralized town granary
A data warehouse
Farmers (pipelines) deliver grain on a schedule; the granary stores it safely and hands out the exact amounts bakers (reports) request. Keep the granary tidy and the whole town is fed; let it rot and every bakery suffers.
How it works
- Day and event data is landed into staging, then transformed into modeled facts/dimensions by ELT.
- Columnar storage compresses and organizes columns so queries scan only needed ranges.
- The SQL engine plans queries, pushing filters into scans and aggregations into parallel units.
- Compute clusters serve concurrent reader queries while refresh transactions apply atomically.
- Virtual warehouses/databases and roles enforce access at the serving boundary.
Data entering and leaving the warehouse.
In production
- A revenue team refreshes fact tables at 2am rolling the previous day's orders into the warehouse's gold schema.
- Dashboards hit the serving layer all day while nightly ELT writes to staging tables — no reader sees a partial transaction.
- Role-based access lets finance see org-wide revenue while support reps see only their region's rows.
Best practices
- Separate bronze/silver/gold: raw, cleaned and model-consumable layers.
- Let compute scale for queries while keeping storage economics flat.
- Enforce schemas and ownership in the catalog; never let reports guess.
- Monitor freshness per table and page on staleness before symptoms reach users.
Common mistakes
- Treating the warehouse as a second production database and streaming 1M-row select * pulls.
- Mixing raw and modeled data in one schema, confusing which layer is truth.
- Ignoring partitions so every report scans the whole table history.
- Concurrency limits: too many dashboards on one warehouse race for compute.
Common issues
Dashboards slow at month-end closeHeavy refresh jobs and reporting queries competing for the same compute
Schedule refreshes off-hours, separate reporting and transform compute (if cost allows).
Query scans the whole historyNo partition predicates or un-partitioned tables
Partition by date and enforce WHERE on the partition key.
Debugging tips
- Slow dashboard? Check the warehouse's concurrency and the query plan before blaming the report.
- Wrong number? Verify the refresh status of the exact table the report reads.
- Expensive bill? Look at scan bytes — wide uncompressed scans are the usual spike.
Interview checkpoint
Hands-on lab
- Simulate raw import and modeled serving layers in SQLite
- Prove only needed rows/columns are scanned at the serving layer
- Apply row-level security at the serving layer
Model a Columnar View of Warehouse Reads
30 min · beginner
Quick quiz
Which property most explains a warehouse's BI speed?
Dashboards should generally query raw production staging tables directly.