Data Warehouse

What it is

A data warehouse is a centralized repository designed for analytical query workloads — aggregations, joins across large datasets, and historical trend analysis — as opposed to the transactional read/write workloads of an OLTP database. Data from multiple operational source systems is extracted, transformed, and loaded (ETL/ELT) into the warehouse, where it is organized using dimensional modeling to support intuitive querying by business analysts. Modern cloud data warehouses (Amazon Redshift, Google BigQuery, Snowflake) separate compute from storage, allowing teams to scale query capacity independently of data volume and pay only for queries run.

The dominant modeling approaches are Ralph Kimball's dimensional modeling (star and snowflake schemas optimized for query performance and self-service BI) and Bill Inmon's enterprise data warehouse (highly normalized 3NF model used as a single source of truth from which data marts are derived). Most modern practitioners use a hybrid: a staging layer in raw format, a transformation layer using dbt producing dimensional models, and presentation-layer data marts for specific business domains.

Why it exists

OLTP databases are optimized for transactional throughput: they use row-oriented storage, B-tree indexes, and normalized schemas to support fast single-row reads and writes. Analytical queries — "total revenue by product category by month for the last 3 years" — require full column scans across billions of rows, which are orders of magnitude slower on OLTP storage. A data warehouse uses columnar storage (where each column is stored contiguously on disk), allowing the query engine to read only the columns needed for an aggregation, achieving 10–100x better performance for analytical workloads. Running heavy analytics on the OLTP database also risks degrading production transaction performance, making the separation architecturally necessary as data volumes and query complexity grow.

When to use

• Business intelligence and reporting requiring aggregations across large historical datasets that would degrade OLTP performance.
• Multi-source analytics: combining data from CRM, ERP, marketing platforms, and operational databases into a single unified view for cross-functional reporting.
• Regulatory reporting requiring point-in-time historical snapshots and auditability of data lineage.
• Self-service BI: dimensional models (star schemas) are optimized for BI tools (Tableau, Power BI, Looker) that generate SQL aggregation queries.
• When data volumes in source systems exceed what OLTP can handle for analytical queries (typically beyond tens of millions of rows for complex aggregations).
• Training ML models on large historical datasets where the feature engineering queries require full dataset scans.

When not to use

• For real-time operational data needs — a data warehouse loaded hourly or daily is not appropriate when sub-minute freshness is required.
• For transactional workloads — data warehouses are read-optimized; high-frequency INSERT/UPDATE/DELETE operations are poorly suited to columnar storage.
• For unstructured data (images, video, documents) or semi-structured data with highly variable schemas — a data lake or object storage is more appropriate.
• When the analytics use case is simple enough to be served by read replicas of the OLTP database — adding a warehouse adds ingestion complexity that may not be justified.

Typical architecture

  Modern Cloud Data Warehouse (Snowflake / BigQuery):

  Source Systems            ELT Pipeline              Warehouse Layers
  ┌──────────────┐         ┌────────────┐            ┌───────────────────┐
  │  CRM (SFDC)  │────────▶│            │            │  RAW / STAGING    │
  │  ERP (SAP)   │────────▶│  Fivetran  │───────────▶│  (raw replicas)   │
  │  App DB (PG) │────────▶│  or Airbyte│            ├───────────────────┤
  │  Events (S3) │────────▶│            │            │  TRANSFORM (dbt)  │
  └──────────────┘         └────────────┘            │  - dim_customers  │
                                                     │  - dim_products   │
                                                     │  - fact_orders    │
                                                     ├───────────────────┤
                           BI / Analytics            │  DATA MARTS       │
                           ┌──────────┐             │  - finance_mart   │
                           │ Tableau  │◀────────────│  - marketing_mart │
                           │ Looker   │             └───────────────────┘
                           │ Power BI │
                           └──────────┘
          

Pros and cons

Implementation notes

Use dbt (data build tool) for all SQL transformations in the warehouse. dbt enforces software engineering practices (version control, testing, documentation) on warehouse SQL, replacing ad-hoc scripts with a reproducible, testable transformation layer. Define sources for raw ingested tables, staging models for light cleaning and type casting, intermediate models for business logic, and mart models as the presentation layer. Add dbt tests on primary keys (uniqueness, not-null) and referential integrity (foreign key relationships) to catch data quality issues before they reach dashboards.

For Slowly Changing Dimensions (SCDs), choose the SCD type based on the business requirement: SCD Type 1 overwrites the old value (no history), SCD Type 2 adds a new row with an effective date range (full history), and SCD Type 3 adds a "previous value" column (limited history). SCD Type 2 is most common for customer and product dimensions where historical accuracy matters. In Snowflake, cluster tables by frequently filtered columns (date, region, customer segment) to minimize data scanned per query. In BigQuery, partition tables by date and cluster by commonly filtered dimensions.

Common failure modes

• Metric inconsistency: Two BI dashboards show different "total revenue" numbers because they use different transformation logic; there is no single agreed-upon definition of revenue in the warehouse model.
• Data quality propagation: A null value in the source CRM propagates through ETL into the fact table; no NOT NULL constraint or dbt test catches it; revenue reports undercount because joins silently drop nulls.
• SCD Type 2 explosion: A dimension with high churn (e.g., product price changes) accumulates millions of historical rows; queries join against the dimension and slow dramatically without effective date range filtering.
• Cost overrun on full table scans: BigQuery queries scan entire tables because the developer omits a WHERE date partition filter; costs multiply 100x compared to partition-pruned queries.
• Stale data without alerting: The nightly ETL pipeline fails silently; BI dashboards show yesterday's data; business users make decisions based on stale data for 24 hours before anyone notices.

Decision checklist

• Have you chosen a dimensional modeling approach (Kimball star schema vs Inmon 3NF vs Data Vault) and documented the rationale?
• Are dbt tests configured for primary key uniqueness, not-null constraints, and referential integrity on all dimension and fact tables?
• Is data freshness monitored with alerting when the pipeline has not loaded within the expected window?
• Are tables partitioned and clustered (Snowflake) or partitioned and clustered (BigQuery) to minimize query costs on large datasets?
• Have you defined a single, documented business metric layer (dbt metrics, LookML, or semantic layer) to prevent metric inconsistency across dashboards?
• Is PII masked or tokenized in the warehouse, or is access controlled via column-level security?

Example use cases

• E-commerce analytics: A Snowflake data warehouse ingests orders, inventory, and customer data via Fivetran from Shopify and Salesforce. dbt transforms the raw data into a star schema (fact_orders, dim_customers, dim_products, dim_dates). Tableau connects to Snowflake for executive revenue dashboards and marketing attribution analysis.
• Financial reporting: A BigQuery warehouse stores 10 years of transactional data partitioned by date. Monthly regulatory reports are generated by SQL queries against materialized fact tables, with dbt tests ensuring no transactions are double-counted.
• SaaS product analytics: Mixpanel event data is exported nightly to S3, loaded into Redshift, and transformed by dbt into usage fact tables. Product managers query cohort retention, feature adoption rates, and churn risk signals directly in SQL.

Related patterns

• Data Lake — The complementary pattern for raw, unstructured data storage; often feeds the warehouse via ELT.
• Data Lakehouse — Combines lake storage with warehouse semantics; may replace a traditional warehouse tier.
• OLTP vs OLAP — The fundamental distinction driving the need for a separate analytical store.

Further reading

• Kimball Dimensional Modeling Techniques
• dbt Documentation — Introduction
• Snowflake Key Concepts & Architecture