Reference Architecture: Data warehouse / analytics
Analytical queries (reporting, aggregation, wide scans) are fundamentally different from OLTP transactions: few but very wide, long-running queries. Starting from the decision to separate analytics from OLTP, this tree walks step by step through components such as the load method (ETL vs ELT), columnar/columnstore storage, star schema modeling, partitioning + compression and precomputation with materialized views, while weighing the trade-off between concurrency and throughput.
Does your analytical workload run on the same database as OLTP?
The most common mistake is running heavy reports on the live transactional DB and degrading OLTP latency. The first decision is separation: is analytics still running on the OLTP instance, are you building a separate analytical environment, or are you deciding how to move the data there?
- Reports still run on live OLTP; they slow OLTP down
- A separate warehouse exists; how should I load data?
- A warehouse exists; queries are still slow, how should I model?
Where will you transform: before or after loading?
When moving data from sources to the warehouse, will you do cleansing/transformation in a separate processing layer before loading (ETL), or load the raw data into the warehouse first and transform it with SQL inside the powerful warehouse engine (ELT)? The decision depends on the warehouse's processing power and the need for schema flexibility.
- Transformation is complex/external; do it before loading
- The warehouse is powerful; load raw, transform inside with SQL
Is the query bottleneck in the storage layout or the data model?
If analytics is slow there are two main levers. Wide scans read the entire row in row-based storage; columnar storage reads only the needed columns and compresses well. On the model side, excessive normalization means many joins slow queries down; a star schema (fact + dimension) simplifies things for analytics.
- Wide aggregation scans are slow; few columns are read
- Too many joins, complex normalized schema
- The same heavy report produces the same result repeatedly
Recommendation: Separate analytics from OLTP
Transactional and analytical workloads choke each other on the same engine: long reports hold locks/IO and delay short transactions. Moving analytics to a separate target (a read replica or a separate analytical warehouse) relieves both sides.
Recommendation: ETL (transform before load)
In ETL, data is pulled from sources (Extract), cleansed and transformed in a separate processing layer (Transform), and only then loaded into the warehouse (Load). Only clean, model-conformant data enters the warehouse.
Recommendation: ELT (transform inside the warehouse)
In ELT, raw data is loaded into the warehouse first and transformation happens inside the warehouse's own powerful SQL engine. Because modern columnar warehouses have high processing power, you move the data once and process it inside instead of taking the transformation to the data.
Recommendation: Columnar / columnstore storage + compression
Analytical queries typically scan a few columns across all rows. Columnar storage keeps data column by column; it reads only the needed columns, compresses very well because similar values sit next to each other, and IO drops dramatically.
Recommendation: Star schema (fact + dimension)
In an analytical model, a fact table holding the measures sits in the center, surrounded by descriptive dimension tables. Compared with an over-normalized schema, the number of joins drops, queries become simpler and the engine produces better plans.
Recommendation: Precompute with materialized views
If the same heavy aggregation (daily revenue, monthly summary) runs repeatedly and produces the same result, physically store the result in a materialized view and have reports read the precomputed result. The heavy computation runs once and queries return instantly.