Data Warehouses: Separating Analysis from Live Transactions
📑 On this page
- A concrete example: customer retention
- OLTP and OLAP
- Workload isolation
- Integrated data
- Historical state
- Columnar storage
- Compression
- Fact tables
- Dimension tables
- Slowly changing dimensions
- Data loading
- Change data capture
- ETL and ELT
- Freshness
- Query engines
- Partitioning and clustering
- Materialized views
- Semantic layer
- Data marts
- Concurrency and workload management
- Security
- Cost
- Knowledge check
- The one idea to remember
Operational databases are designed to keep applications running:
- create orders,
- update accounts,
- reserve inventory,
- answer small indexed queries.
Analytical questions scan and combine much larger histories.
A data warehouse separates integrated historical analysis from live transaction processing.
It copies and reshapes data so reporting does not overload the systems serving customers.
A concrete example: customer retention
An organization combines:
- orders from commerce,
- campaigns from marketing,
- tickets from support,
- subscriptions from billing.
The warehouse models them around shared customer and time definitions so analysts can study retention across several years without joining production systems live.
OLTP and OLAP
Operational transaction processing, or OLTP, favors:
- small reads and writes,
- low latency,
- concurrency,
- and current state.
Online analytical processing, or OLAP, favors:
- large scans,
- aggregation,
- joins,
- and historical comparison.
One database design rarely optimizes both equally.
Workload isolation
A broad report can consume CPU, memory, I/O, and locks.
Running it on the checkout database risks user latency. Copying data to a warehouse isolates analytical demand and allows different scaling.
Isolation is one of the warehouse's main values.
Integrated data
Source systems use different:
- identifiers,
- timestamps,
- currencies,
- statuses,
- and definitions.
The warehouse creates consistent models so "active customer" or "net revenue" means the same thing across reports.
Integration requires governance, not only file movement.
Historical state
Operational tables often keep current values.
Analysis may need:
- price at purchase,
- customer segment at that time,
- previous address,
- or subscription history.
Warehouse models preserve snapshots or change history deliberately.
Columnar storage
Analytical queries often read a few columns across millions of rows.
Columnar storage keeps values by column, improving:
- compression,
- scan efficiency,
- and vectorized calculation.
Updating one individual row may be less efficient than in a row-oriented transaction database.
Compression
Values in one column often repeat or share type.
Compression techniques reduce storage and I/O. Reading less data from disk can improve query speed even when decompression uses CPU.
Sort order and encoding influence compression.
Fact tables
A fact table records measurable events:
- order line,
- page view,
- payment,
- shipment,
- support interaction.
It includes measures and keys to descriptive dimensions.
Define its grain precisely: one row per what?
Dimension tables
Dimensions describe:
- customer,
- product,
- date,
- location,
- campaign.
They support filtering and grouping. A star schema connects a central fact table to dimensions.
Consistent dimensions reduce duplicated business logic.
Slowly changing dimensions
Dimension values change.
If a customer moves region, historical sales may need the region at purchase time rather than current region.
Slowly changing dimension strategies either overwrite, preserve versions, or store selected previous values.
Data loading
Warehouses receive data through:
- batch files,
- database change capture,
- APIs,
- events,
- and streams.
Pipelines validate, transform, deduplicate, and record progress.
Loading is often incremental after an initial backfill.
Change data capture
CDC reads database change logs and turns inserts, updates, and deletes into a downstream stream.
It reduces repeated full exports but introduces:
- ordering,
- schema evolution,
- retention,
- and duplicate handling.
The source log reflects database changes, not always complete business meaning.
ETL and ELT
ETL transforms before warehouse load.
ELT loads source-shaped data first, then transforms inside the analytical platform.
Modern warehouses often support ELT because scalable compute can run SQL transformations while preserving raw history.
Freshness
Not every dashboard needs real-time updates.
Define:
- source delay,
- pipeline delay,
- model delay,
- dashboard cache.
A "live" label should state the actual freshness objective.
Query engines
Warehouse engines optimize:
- distributed scans,
- parallel aggregation,
- join planning,
- caching,
- and workload management.
Large queries still need partition pruning and sensible modeling.
Partitioning and clustering
Tables can organize data by:
- date,
- tenant,
- region,
- or commonly filtered fields.
Queries that filter those dimensions scan less data. Too many small partitions create metadata overhead.
Use real query patterns.
Materialized views
A materialized view precomputes a result.
It speeds repeated dashboards at the cost of:
- storage,
- refresh,
- and freshness delay.
The warehouse must know when the result is stale.
Semantic layer
A semantic layer defines reusable business measures and dimensions above physical tables.
Instead of every dashboard implementing net revenue differently, one governed measure specifies included orders, refunds, fees, currency conversion, and time grain. Version and test these definitions because a metric change can alter many decisions without changing source data.
Data marts
A data mart presents a focused governed subset for one domain such as finance, marketing, or operations.
Marts can improve usability and access isolation, but duplicated independent marts can recreate conflicting definitions. Build them from shared integrated foundations and preserve lineage back to source.
Concurrency and workload management
Analysts, dashboards, pipelines, and machine learning can compete for capacity.
Use:
- queues,
- priorities,
- separate compute pools,
- limits,
- and schedules.
One accidental cross join should not block executive reporting.
Security
Warehouses combine data, increasing sensitivity.
Apply:
- least privilege,
- row and column controls,
- masking,
- audit,
- encryption,
- and separate roles for transformation and consumption.
Copying data expands its access boundary.
Cost
Costs may come from:
- stored bytes,
- scanned bytes,
- compute time,
- concurrency,
- materialization,
- and data transfer.
Partitioning, caching, retention, and query review influence cost directly.
Knowledge check
- How do OLTP and OLAP workloads differ?
- Why is workload isolation valuable?
- What does the grain of a fact table define?
- Why preserve historical dimension versions?
- How can partitioning reduce analytical query cost?
The one idea to remember
A data warehouse copies and integrates operational data into a system optimized for historical scans, joins, and reporting. Its value comes from workload isolation, consistent definitions, preserved history, analytical storage, governance, and measurable freshness.