Architecture
Back to Articles

Data Warehouse

25 Sept 2026 · 2 min read

On this page

Platform Architectures

The Data Warehouse, Data Lake, and Data Lakehouse are platform architectures that decide where data is stored, in what format, and which component guarantees correctness.

Data Warehouse#

A centrally managed store of structured data, optimised for SQL analytics and Business Intelligence (BI).

  • Schema-on-write: data must conform to a predefined schema before it is stored
    • Traditional (on-premises): Extract-Transform-Load (ETL), where a dedicated tool (e.g., Informatica, DataStage) transforms data before loading it into the warehouse (e.g., Teradata, Oracle)
    • Cloud warehouses (e.g., Snowflake, BigQuery, Redshift): Extract-Load-Transform (ELT)
      • Load raw extracts first, then transform inside the warehouse with SQL (e.g., dbt, the "data build tool")
  • Data is stored in a proprietary format and accessed only through the engine's interfaces (SQL over JDBC/ODBC, vendor APIs)

Strengths#

  • Full ACID transactions, including multi-statement transactions
  • Mature cost-based query optimisers
  • High concurrency for dashboards
  • Fine-grained governance, e.g., row-level and column-level security

Weaknesses#

  • Unstructured data (images, audio, free text) has no natural home
  • Semi-structured data is supported only through special types (e.g., Snowflake VARIANT, BigQuery JSON, Redshift SUPER), which are less natural to query and optimise than relational columns
  • ML is awkward because ML frameworks cannot read the proprietary storage files directly, so training data must be pulled through the SQL engine or exported, which is slow and costly at scale and creates extra copies
  • Full raw history is rarely kept, because storage was historically expensive and schema-on-write discards data that does not fit the model

Classic Design#

  1. Staging area: raw extracts land here temporarily before cleaning
  2. Integration layer: cleaned, conformed, integrated data (e.g., an Inmon-style normalised enterprise data warehouse)
  3. Presentation layer: dimensional models / data marts serving BI (e.g., Kimball-style star schemas)

Trade-off#

Warehouses deliver fast, well-governed SQL over structured data. The trade-off is that they lock data into one engine's format and handle unstructured data and ML workloads poorly.