Chapter 18: Data Warehousing & Data Lakes

Modern organizations collect data from hundreds of sources—transactional databases, event streams, APIs, logs, third-party vendors. The challenge isn't just storing this data, but transforming it into a unified, queryable asset that powers analytics, ML, and business intelligence.

This chapter explores three fundamental architectures for analytical data: Data Warehouses (structured, schema-first, optimized for BI), Data Lakes (schema-on-read, raw data preservation, flexibility), and Lakehouses (combining the best of both). We'll cover ETL vs ELT, dimensional modeling, modern formats, orchestration tools, and how to choose the right approach for your scale and use case.

1. Data Warehouse vs Data Lake vs Lakehouse

1.1 Definitions

Data Warehouse

A data warehouse is a centralized repository optimized for analytical queries. Data is extracted from source systems, transformed into a consistent schema (often dimensional models like star or snowflake schemas), and loaded for BI tools to query.

• Schema: Schema-on-write (defined before loading)
• Data Type: Structured (tables with fixed columns)
• Storage: Columnar formats, optimized for SQL queries
• Tools: Snowflake, BigQuery, Redshift, Synapse Analytics
• Primary Use: Business intelligence, dashboards, reports

Data Lake

A data lake stores raw data in its native format—structured, semi-structured, or unstructured. Schema is applied at query time (schema-on-read), enabling maximum flexibility.

• Schema: Schema-on-read (defined when querying)
• Data Type: All types (JSON, CSV, Parquet, images, logs, etc.)
• Storage: Object storage (S3, ADLS, GCS)
• Tools: AWS S3 + Athena, Azure Data Lake + Databricks, GCS + BigQuery
• Primary Use: Data science, ML, exploratory analytics, archival

Lakehouse

A lakehouse combines data lake flexibility with data warehouse reliability. It uses open table formats (Delta Lake, Iceberg, Hudi) to bring ACID transactions, schema enforcement, and time travel to data lakes.

• Schema: Schema enforcement with evolution support
• Data Type: All types, with transactional guarantees
• Storage: Object storage + metadata layer (transaction log)
• Tools: Databricks (Delta Lake), Snowflake (Iceberg), Dremio
• Primary Use: Unified analytics (BI + ML), real-time + batch

1.2 Comparison Table

1.3 Evolution Diagram

graph LR
    A[2000s: Data Warehouses] -->|Too rigid for ML| B[2010s: Data Lakes]
    B -->|Quality issues, no ACID| C[2020s: Lakehouses]

    A -->|Schema-on-write| D[Snowflake, Redshift]
    B -->|Schema-on-read| E[S3 + Athena]
    C -->|Best of both| F[Delta Lake, Iceberg]

    style C fill:#2d5016
      

2. ETL vs ELT

2.1 Extract-Transform-Load (ETL)

ETL is the traditional approach: data is extracted from sources, transformed on a staging server or ETL tool, then loaded into the target warehouse.

graph LR
    A[Source DB] -->|Extract| B[Staging Area]
    B -->|Transform| C[ETL Server]
    C -->|Load| D[Data Warehouse]

    style C fill:#4a235a
      

When to Use ETL

• Limited warehouse capacity: On-premise warehouses with expensive compute
• Complex transformations: Heavy aggregations that would overload the warehouse
• Data privacy: PII must be masked/encrypted before reaching the warehouse
• Legacy systems: Existing ETL tools (Informatica, Talend, SSIS)

Pros & Cons

2.2 Extract-Load-Transform (ELT)

ELT is the modern approach: raw data is loaded directly into the warehouse/lake, then transformed using the warehouse's compute power (SQL, dbt).

graph LR
    A[Source DB] -->|Extract| B[Ingestion Tool]
    B -->|Load Raw| C[Data Warehouse]
    C -->|Transform| D[Analytics Layer]

    style C fill:#1a5490
      

When to Use ELT

• Cloud warehouses: Snowflake, BigQuery, Redshift (elastic compute)
• Fast iteration: Data teams can redefine transformations without re-ingesting
• Raw data preservation: Keep original data for auditing or re-processing
• Modern tools: Fivetran, Airbyte, dbt

Pros & Cons

2.3 ETL vs ELT Pipeline Comparison

flowchart TB
    subgraph ETL_Pipeline[ETL Pipeline - Traditional]
        A1[MySQL] --> B1[Extract]
        A2[PostgreSQL] --> B1
        A3[API] --> B1
        B1 --> C1[Transform on ETL Server]
        C1 --> D1[Load to Warehouse]
        D1 --> E1[Query Layer]
    end

    subgraph ELT_Pipeline[ELT Pipeline - Modern]
        A4[MySQL] --> B2[Fivetran]
        A5[PostgreSQL] --> B2
        A6[API] --> B2
        B2 --> C2[Load Raw to Snowflake]
        C2 --> D2[dbt Transform in Warehouse]
        D2 --> E2[Query Layer]
    end

    style ETL_Pipeline fill:#4a235a,stroke:#fff
    style ELT_Pipeline fill:#1a5490,stroke:#fff
      

3. Data Warehouse Architecture

3.1 Kimball (Dimensional Modeling)

The Kimball approach (Ralph Kimball, 1996) organizes data into fact tables and dimension tables, optimized for BI queries. It's denormalized for read performance.

Key Concepts

• Fact Table: Measurements/metrics (sales, clicks, revenue)
• Dimension Table: Context (customer, product, time, location)
• Star Schema: Fact table at center, dimensions radiate out
• Conformed Dimensions: Shared dimensions across multiple fact tables

erDiagram
    FACT_SALES {
        int sale_id PK
        int customer_id FK
        int product_id FK
        int date_id FK
        decimal amount
        int quantity
    }

    DIM_CUSTOMER {
        int customer_id PK
        string name
        string email
        string segment
    }

    DIM_PRODUCT {
        int product_id PK
        string name
        string category
        decimal price
    }

    DIM_DATE {
        int date_id PK
        date full_date
        int year
        int quarter
        int month
    }

    FACT_SALES }o--|| DIM_CUSTOMER : "customer_id"
    FACT_SALES }o--|| DIM_PRODUCT : "product_id"
    FACT_SALES }o--|| DIM_DATE : "date_id"
      

Pros & Cons

3.2 Inmon (Normalized/Enterprise Data Warehouse)

The Inmon approach (Bill Inmon, 1990s) advocates a normalized, 3NF (Third Normal Form) data warehouse. Data is stored in a clean, consistent format, then transformed into data marts for specific business units.

Key Concepts

• Enterprise Data Warehouse (EDW): Central, normalized repository
• Data Marts: Department-specific subsets (sales mart, finance mart)
• Single Source of Truth: All data flows through EDW

flowchart TB
    A[Source Systems] --> B[Staging Area]
    B --> C[Enterprise Data Warehouse - Normalized]
    C --> D1[Sales Data Mart]
    C --> D2[Finance Data Mart]
    C --> D3[Marketing Data Mart]
    D1 --> E[BI Tools]
    D2 --> E
    D3 --> E

    style C fill:#4a235a
      

Pros & Cons

3.3 Kimball vs Inmon Comparison

3.4 Medallion Architecture (Bronze/Silver/Gold)

The medallion architecture (popularized by Databricks) organizes data lakes and lakehouses into three layers of increasing quality:

• Bronze: Raw data, as-is from sources (no transformation)
• Silver: Cleaned, deduplicated, validated (schema enforcement)
• Gold: Business-level aggregations, curated datasets for analytics

flowchart LR
    A[Data Sources] --> B[Bronze Layer - Raw]
    B --> C[Silver Layer - Cleansed]
    C --> D[Gold Layer - Curated]

    B --> E[Schema-on-read, All data]
    C --> F[Schema-enforced, Validated]
    D --> G[Star schema, Aggregated]

    D --> H[BI Tools]
    D --> I[ML Models]

    style B fill:#8b4513
    style C fill:#c0c0c0
    style D fill:#ffd700
      

When to Use Medallion

• Data lakes or lakehouses (not traditional warehouses)
• Need to preserve raw data for auditing or re-processing
• Incremental transformation (ELT pattern)
• Support both BI and ML workloads

4. Star Schema Deep Dive

4.1 Fact and Dimension Tables

Fact Table

A fact table stores quantitative measurements (metrics) at a specific grain. Each row represents a business event or transaction.

• Grain: The level of detail (e.g., "one row per order line item")
• Foreign Keys: Link to dimension tables
• Measures: Numeric values (quantity, revenue, duration)

FACT_ORDERS:
order_id (PK)
customer_id (FK)
product_id (FK)
date_id (FK)
quantity (measure)
unit_price (measure)
total_amount (measure)

Dimension Table

A dimension table provides descriptive context. Dimensions are typically denormalized (contain hierarchies flattened into one table).

DIM_PRODUCT:
product_id (PK)
product_name
category
subcategory
brand
supplier

4.2 Grain

Grain is the most important decision in fact table design. It defines what each row represents. Common grains:

• Transaction grain: One row per order line (most detailed)
• Daily snapshot grain: One row per product per day (inventory levels)
• Periodic snapshot grain: One row per account per month (balances)

4.3 Conformed Dimensions

A conformed dimension is shared across multiple fact tables. For example, DIM_CUSTOMER is used by FACT_SALES, FACT_RETURNS, and FACT_SUPPORT_TICKETS.

graph TD
    A[DIM_CUSTOMER] --> B[FACT_SALES]
    A --> C[FACT_RETURNS]
    A --> D[FACT_SUPPORT_TICKETS]

    style A fill:#2d5016
      

Why Conformed Dimensions Matter

• Consistency: Customer #123 means the same thing across all facts
• Drill-across: Combine metrics from different fact tables (sales vs support)
• Single source of truth: Update customer attributes in one place

4.4 Role-Playing Dimensions

A role-playing dimension is a single physical dimension referenced multiple times by a fact table in different roles.

Example: FACT_ORDERS has order_date_id, ship_date_id, and delivery_date_id, all pointing to DIM_DATE. Create views to clarify the role:

-- Views for role-playing dimensions
CREATE VIEW dim_order_date AS SELECT * FROM dim_date;
CREATE VIEW dim_ship_date AS SELECT * FROM dim_date;
CREATE VIEW dim_delivery_date AS SELECT * FROM dim_date;

4.5 Star Schema vs Snowflake Schema

graph LR
    subgraph Star[Star Schema - Denormalized]
        FS[FACT_SALES] --> DP[DIM_PRODUCT - product, category, brand]
    end

    subgraph Snowflake[Snowflake Schema - Normalized]
        FSN[FACT_SALES] --> DPN[DIM_PRODUCT - product]
        DPN --> DC[DIM_CATEGORY - category]
        DC --> DB[DIM_BRAND - brand]
    end

    style Star fill:#1a5490
    style Snowflake fill:#4a235a
      

5. Data Lake Architecture

5.1 Core Principles

A data lake stores raw data in its native format on cheap object storage (AWS S3, Azure Data Lake Storage, Google Cloud Storage). Schema is applied at query time.

Key Characteristics

• Schema-on-read: No predefined schema; define structure when querying
• All data types: JSON, CSV, Parquet, Avro, images, videos, logs
• Decoupled storage and compute: Store in S3, query with Athena/Spark/Presto
• Cost-effective: S3 costs ~$0.023/GB/month vs warehouse storage ~$40/TB/month

5.2 Lake Zones (Raw/Cleansed/Curated)

Organize data lakes into zones to separate raw data from processed data:

• Raw Zone: Immutable source data, as-is from ingestion
• Cleansed Zone: Validated, deduplicated, schema-enforced
• Curated Zone: Business-ready datasets, often in star schema

flowchart LR
    A[Data Sources] --> B[s3://lake/raw/]
    B --> C[Spark Job: Clean, Validate]
    C --> D[s3://lake/cleansed/]
    D --> E[Spark Job: Aggregate, Join]
    E --> F[s3://lake/curated/]

    F --> G[Athena Queries]
    F --> H[ML Models]

    style B fill:#8b4513
    style D fill:#4682b4
    style F fill:#2d5016
      

5.3 File Formats

5.4 Partitioning Strategies

Partitioning divides data into subdirectories, allowing query engines to skip irrelevant files (predicate pushdown).

Common Partitioning Schemes

-- By date (most common)
s3://lake/orders/year=2025/month=05/day=12/part-0000.parquet

-- By region and date
s3://lake/orders/region=us-east/year=2025/month=05/part-0000.parquet

-- By entity
s3://lake/events/event_type=page_view/date=2025-05-12/part-0000.parquet

Partitioning Best Practices

• Partition by query filters: If you always filter by date, partition by date
• Avoid high cardinality: Don't partition by user_id (millions of folders)
• Target 128MB-1GB per file: Avoid small files (listing overhead)
• Use Hive-style partitions: key=value format for tool compatibility

5.5 Data Lake Challenges

6. Lakehouse Architecture

6.1 The Lakehouse Vision

A lakehouse combines the flexibility of data lakes with the performance and reliability of data warehouses. It brings ACID transactions, schema enforcement, and time travel to object storage.

graph TB
    A[Data Sources] --> B[Object Storage - S3/ADLS/GCS]
    B --> C[Metadata Layer - Delta Lake/Iceberg]
    C --> D1[BI Tools - Tableau, Looker]
    C --> D2[ML Tools - Spark, Databricks]
    C --> D3[SQL Engines - Athena, Presto]

    style C fill:#ffd700
      

6.2 Delta Lake

Delta Lake (by Databricks, open-source) adds a transaction log on top of Parquet files, enabling ACID guarantees.

Key Features

• ACID Transactions: Atomic commits, isolation, consistency
• Time Travel: Query previous versions (AS OF timestamp)
• Schema Enforcement: Reject writes with incompatible schemas
• Schema Evolution: Add columns without rewriting data
• Upserts/Deletes: MERGE INTO for CDC, updates, deletes
• Audit Trail: DESCRIBE HISTORY shows all versions

-- Create Delta table
CREATE TABLE orders USING DELTA
LOCATION 's3://lake/orders';

-- Time travel
SELECT * FROM orders VERSION AS OF 5;
SELECT * FROM orders TIMESTAMP AS OF '2025-05-10';

-- Merge (upsert)
MERGE INTO orders AS target
USING updates AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

-- Vacuum old versions (delete files older than 7 days)
VACUUM orders RETAIN 168 HOURS;

6.3 Apache Iceberg

Apache Iceberg (by Netflix, donated to Apache) is an open table format designed for large-scale analytics. It's engine-agnostic (works with Spark, Trino, Flink).

Key Features

• Hidden Partitioning: Users don't specify partitions in queries (automatic)
• Partition Evolution: Change partitioning scheme without rewriting data
• ACID: Optimistic concurrency control
• Time Travel: Snapshot isolation
• Schema Evolution: Add, drop, rename columns
• Multi-engine: Spark, Trino, Flink, Hive, Presto

-- Create Iceberg table
CREATE TABLE orders (
  order_id BIGINT,
  customer_id BIGINT,
  order_date DATE
) USING iceberg
PARTITIONED BY (days(order_date));

-- Time travel
SELECT * FROM orders VERSION AS OF 12345678;

-- Partition evolution (change partitioning without rewrite)
ALTER TABLE orders SET PARTITION SPEC (months(order_date));

6.4 Apache Hudi

Apache Hudi (Hadoop Upserts Deletes Incrementals, by Uber) optimizes for streaming data and incremental processing.

Key Features

• Upserts at Scale: Efficient merge on large datasets
• Incremental Queries: Read only changed data since last query
• Time Travel: Query historical snapshots
• Two Table Types: Copy-on-Write (fast reads) vs Merge-on-Read (fast writes)

6.5 Delta Lake vs Iceberg vs Hudi

6.6 Lakehouse Architecture Diagram

flowchart TB
    subgraph Sources[Data Sources]
        A1[Transactional DBs]
        A2[Event Streams]
        A3[SaaS APIs]
    end

    subgraph Ingestion[Ingestion Layer]
        B1[Kafka/Kinesis]
        B2[Fivetran/Airbyte]
    end

    subgraph Storage[Object Storage]
        C1[S3/ADLS/GCS]
    end

    subgraph Lakehouse[Lakehouse Metadata Layer]
        D1[Delta Lake/Iceberg]
        D2[Transaction Log]
        D3[Schema Registry]
    end

    subgraph Compute[Compute Engines]
        E1[Spark - Batch ETL]
        E2[Trino - Ad-hoc SQL]
        E3[Flink - Streaming]
    end

    subgraph Consumption[Consumption Layer]
        F1[BI Tools - Tableau]
        F2[ML Platforms - Databricks]
        F3[Data Science - Jupyter]
    end

    A1 --> B1
    A2 --> B1
    A3 --> B2
    B1 --> C1
    B2 --> C1
    C1 --> D1
    D1 --> E1
    D1 --> E2
    D1 --> E3
    E1 --> F1
    E2 --> F1
    E1 --> F2
    E3 --> F3

    style Lakehouse fill:#ffd700
      

7. Orchestration & Workflow

7.1 Apache Airflow

Apache Airflow is the most popular open-source orchestration tool. Workflows are defined as Directed Acyclic Graphs (DAGs) in Python code.

Key Concepts

• DAG: Workflow definition (tasks + dependencies)
• Task: A unit of work (Python function, SQL query, Spark job)
• Operator: Template for tasks (BashOperator, PythonOperator, SparkSubmitOperator)
• Scheduler: Triggers tasks based on schedule or dependencies
• Executor: Runs tasks (LocalExecutor, CeleryExecutor, KubernetesExecutor)

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime

def extract():
    print("Extracting data...")

def transform():
    print("Transforming data...")

def load():
    print("Loading data...")

dag = DAG(
    'etl_pipeline',
    start_date=datetime(2025, 5, 1),
    schedule_interval='@daily'
)

extract_task = PythonOperator(task_id='extract', python_callable=extract, dag=dag)
transform_task = PythonOperator(task_id='transform', python_callable=transform, dag=dag)
load_task = PythonOperator(task_id='load', python_callable=load, dag=dag)

extract_task >> transform_task >> load_task

graph LR
    A[extract] --> B[transform]
    B --> C[load]

    style A fill:#1a5490
    style B fill:#4a235a
    style C fill:#2d5016
      

Pros & Cons

7.2 Dagster

Dagster is a modern orchestration framework emphasizing data assets (not just tasks). It tracks data lineage and dependencies.

Key Concepts

• Asset: A data product (table, model, dashboard)
• Op: A unit of computation (like Airflow tasks)
• Graph: Connects ops (like Airflow DAG)
• Software-defined Assets: Declare assets, Dagster builds DAG automatically

from dagster import asset

@asset
def raw_orders():
    return extract_orders()

@asset
def cleaned_orders(raw_orders):
    return clean(raw_orders)

@asset
def sales_report(cleaned_orders):
    return aggregate(cleaned_orders)

Dagster auto-generates a DAG: raw_orders → cleaned_orders → sales_report.

Why Dagster?

• Asset-centric: Focus on data products, not plumbing
• Type-safe: Catch errors before runtime
• Lineage: Track which assets depend on others
• Testable: Unit test pipelines locally

7.3 Prefect

Prefect is a workflow orchestration tool with a focus on observability and dynamic workflows.

Key Features

• Dynamic workflows: Generate tasks at runtime (vs static DAGs)
• Hybrid execution: Run locally or in cloud
• Observability: Real-time dashboards, logs, alerts

7.4 dbt (Data Build Tool)

dbt is a transformation framework for analytics. It's not a full orchestrator, but handles the "T" in ELT.

Key Concepts

• Models: SQL SELECT statements (transformed into tables/views)
• Sources: Raw tables ingested by Fivetran/Airbyte
• Tests: Data quality checks (not_null, unique, relationships)
• Documentation: Auto-generated lineage graphs and data dictionary

-- models/staging/stg_orders.sql
SELECT
  order_id,
  customer_id,
  order_date,
  total_amount
FROM {{ source('raw', 'orders') }}
WHERE status != 'cancelled'

-- models/marts/sales_by_customer.sql
SELECT
  customer_id,
  SUM(total_amount) AS total_sales
FROM {{ ref('stg_orders') }}
GROUP BY customer_id

graph LR
    A[source: raw.orders] --> B[stg_orders]
    B --> C[sales_by_customer]

    style A fill:#8b4513
    style B fill:#4682b4
    style C fill:#2d5016
      

Why dbt?

• SQL-first: Analysts can build pipelines without Python
• Version control: Models in Git, peer review
• Testing: Built-in data quality checks
• Documentation: Generates lineage and docs automatically

7.5 Orchestration Comparison

8. Data Quality & Governance

8.1 Great Expectations

Great Expectations is a Python library for data validation, testing, and profiling.

Key Concepts

• Expectation: A testable assertion (e.g., "column X is not null")
• Expectation Suite: Collection of expectations for a dataset
• Checkpoint: Runs validation and triggers actions (alerts, blocks pipeline)

import great_expectations as gx

# Create expectation suite
suite = context.create_expectation_suite("orders")

# Define expectations
batch.expect_column_values_to_not_be_null("order_id")
batch.expect_column_values_to_be_unique("order_id")
batch.expect_column_values_to_be_between("total_amount", min_value=0, max_value=1000000)

# Validate
result = context.run_checkpoint("orders_checkpoint")
if not result.success:
    raise Exception("Data quality check failed!")

8.2 Data Contracts

A data contract is an agreement between data producers and consumers about schema, SLAs, and semantics.

Example Contract (YAML)

name: orders_table
schema:
  - name: order_id
    type: bigint
    nullable: false
    unique: true
  - name: customer_id
    type: bigint
    nullable: false
  - name: total_amount
    type: decimal(10,2)
    nullable: false
    min: 0
sla:
  freshness: 1 hour
  availability: 99.9%
owner: payments-team
consumers:
  - analytics-team
  - ml-platform

Why Data Contracts?

• Prevent breaking changes: Producers can't change schema without notice
• Clear ownership: Who to contact when data breaks
• SLA enforcement: Alert if data is stale

8.3 Schema Registries

A schema registry (e.g., Confluent Schema Registry, AWS Glue Data Catalog) stores schemas centrally and enforces compatibility rules.

Key Features

• Versioning: Track schema changes over time
• Compatibility: Enforce backward/forward compatibility
• Validation: Reject writes with incompatible schemas

8.4 Data Catalogs

A data catalog is a searchable inventory of datasets, helping users discover and understand data.

Popular Catalogs

• Amundsen (by Lyft): Search engine for data, metadata from Hive/Presto
• DataHub (by LinkedIn): Metadata platform, lineage graphs, ownership
• AWS Glue Data Catalog: Metadata store for Athena, Redshift, EMR

Example Catalog Entry

Dataset: analytics.sales_summary
Description: Daily sales aggregated by product
Owner: analytics-team
Freshness: Updated daily at 2 AM UTC
Schema:
  - product_id (bigint)
  - date (date)
  - total_sales (decimal)
Lineage:
  Upstream: raw.orders, raw.products
  Downstream: dashboard.executive_sales
Tags: [pii, gdpr]

8.5 Data Governance Framework

flowchart TB
    A[Data Producers] --> B[Schema Registry - Enforce Schema]
    B --> C[Data Pipeline - Validate Quality]
    C --> D[Data Catalog - Metadata & Lineage]
    D --> E[Access Control - Row/Column-level]
    E --> F[Data Consumers]

    G[Data Contracts] --> B
    H[Great Expectations] --> C
    I[Amundsen/DataHub] --> D
    J[IAM/RBAC] --> E

    style D fill:#ffd700
      

9. Modern Data Stack

9.1 Components

The modern data stack is a set of cloud-native tools optimized for ELT workflows:

• Ingestion: Fivetran, Airbyte, Stitch
• Storage: Snowflake, BigQuery, Databricks, Redshift
• Transformation: dbt (SQL), Dataform
• Orchestration: Airflow, Dagster, Prefect
• BI: Looker, Tableau, Metabase, Mode
• Reverse ETL: Hightouch, Census (warehouse → SaaS apps)

9.2 Architecture Diagram

flowchart LR
    subgraph Sources[Data Sources]
        A1[PostgreSQL]
        A2[Salesforce]
        A3[Stripe]
    end

    subgraph Ingestion[Ingestion - Fivetran]
        B1[Connectors]
    end

    subgraph Storage[Data Warehouse - Snowflake]
        C1[Raw Schema]
        C2[Staging Schema]
        C3[Analytics Schema]
    end

    subgraph Transform[Transformation - dbt]
        D1[Models]
        D2[Tests]
        D3[Docs]
    end

    subgraph BI[Business Intelligence]
        E1[Looker]
        E2[Metabase]
    end

    subgraph Reverse[Reverse ETL - Hightouch]
        F1[Sync to Salesforce]
        F2[Sync to HubSpot]
    end

    A1 --> B1
    A2 --> B1
    A3 --> B1
    B1 --> C1
    C1 --> D1
    D1 --> C2
    C2 --> D1
    D1 --> C3
    C3 --> E1
    C3 --> E2
    C3 --> F1
    C3 --> F2

    style Storage fill:#1a5490
    style Transform fill:#2d5016
      

9.3 Modern Data Stack Benefits

9.4 Modern Stack vs Traditional

10. Choosing the Right Architecture

10.1 Decision Framework

Choosing between warehouse, lake, or lakehouse depends on:

• Data Types: Structured only → warehouse; all types → lake/lakehouse
• Use Cases: BI only → warehouse; BI + ML → lakehouse
• Query Patterns: Known reports → warehouse; exploratory → lake
• Budget: Cost-sensitive → lake; performance-critical → warehouse
• Team Skills: SQL-only → warehouse; Python/Spark → lake/lakehouse

10.2 Decision Flowchart

flowchart TD
    A[Start: Choose Data Architecture] --> B{Data Types?}
    B -->|Structured only| C[Data Warehouse]
    B -->|All types - structured, semi-, unstructured| D{Primary Use Case?}

    D -->|BI dashboards only| E[Data Warehouse with external tables to lake]
    D -->|ML + Data Science| F{Need ACID guarantees?}

    F -->|Yes - consistency critical| G[Lakehouse - Delta/Iceberg]
    F -->|No - can tolerate eventual consistency| H[Data Lake]

    C --> I{Query Volume?}
    I -->|High - 1000s concurrent| J[Snowflake or BigQuery]
    I -->|Medium - 100s concurrent| K[Redshift or Synapse]
    I -->|Low - batch queries| L[Athena on S3]

    G --> M{Ecosystem?}
    M -->|Databricks-centric| N[Delta Lake]
    M -->|Multi-engine - Trino, Spark, Flink| O[Apache Iceberg]
    M -->|Streaming CDC heavy| P[Apache Hudi]

    H --> Q{File Format?}
    Q -->|Analytics queries| R[Parquet]
    Q -->|Schema evolution| S[Avro]
    Q -->|Legacy exports| T[CSV/JSON]

    style G fill:#ffd700
    style J fill:#1a5490
    style N fill:#2d5016
      

10.3 Sizing Guidelines

10.4 Cost Comparison (Rough Estimates)

10.5 Real-World Examples

Scenario 1: SaaS Startup (50 employees, 500GB data)

Stack: Fivetran (ingest from Stripe, Salesforce) → Snowflake (warehouse) → dbt (SQL transforms) → Metabase (BI dashboards)

Why: Fast setup, no data team yet, BI-only (no ML)

Scenario 2: E-commerce (500 employees, 50TB data, ML-heavy)

Stack: Kafka → S3 (raw) → Delta Lake (lakehouse) → Spark (ETL) → Databricks (ML platform) + Tableau (BI)

Why: Need both BI and ML, streaming data, large scale

Scenario 3: Financial Services (10,000 employees, 500TB data, strict governance)

Stack: On-premise + cloud hybrid. Teradata (EDW) for regulatory reports, S3 + Iceberg (lakehouse) for analytics, Airflow (orchestration), DataHub (governance)

Why: Compliance requires on-premise, but want cloud for innovation

Summary

Further Reading

• Kimball Group - Dimensional Modeling
• Databricks - Medallion Architecture
• Apache Iceberg Documentation
• dbt Documentation
• Apache Airflow Documentation
• Great Expectations
• DataHub - Metadata Platform