« Back to Index

Data Warehouse

Definition

A Data Warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data organized to support management decision-making.

(Bill Inmon's original definition)

Key Characteristics

Warehouse Design Approaches

Kimball (Star Schema) — We Use This

Fact tables (measurements) surrounded by Dimension tables (context)

      dim_date    dim_airport
          │          │
          └─── fact_flights ────┘
                    │
          dim_airline    dim_weather

Focus: Fast queries, user-friendly, denormalized dimensions.
Creator: Ralph Kimball.
Best for: Reporting, dashboards, ad-hoc analytics (our use case).

Inmon (3NF Data Warehouse)

Normalized enterprise-wide data model, then departmental data marts

Focus: Enterprise integration, normalization, single source of truth.
Creator: Bill Inmon.
Best for: Large enterprises with multiple source systems (overkill for this project).

Data Vault

Hubs (business keys) + Links (relationships) + Satellites (descriptive attributes)

Focus: Auditability, historical tracking, agile schema evolution.
Creator: Dan Linstedt.
Best for: Complex source integrations, teams needing parallel development.

Why We Chose Kimball Star Schema

Warehouse Layers in This Project

┌────────────────────────────────────────────┐
│            Data Warehouse                    │
│                                              │
│  Bronze  —  Raw landing zone (Parquet)     │
│                                              │
│  Silver  —  Cleaned staging (Parquet)      │
│                                              │
│  Gold    —  Business models (Parquet + SQL) │
│     ├── fact_flights                        │
│     ├── fact_delays                         │
│     ├── dim_airport                         │
│     ├── dim_airline                         │
│     ├── dim_weather                         │
│     ├── dim_date                            │
│     └── dim_fuel                            │
│                                              │
│  Analytics — dbt models (materialized views)│
│     ├── airport_metrics                     │
│     ├── airline_rankings                    │
│     ├── delay_analysis                      │
│     ├── weather_impact                      │
│     └── seasonal_trends                     │
└────────────────────────────────────────────┘

Loading Patterns

Incremental Loading Logic

# Check last processed timestamp from checkpoint
checkpoint = load_checkpoint()

new_data = (
    pl.scan_parquet("bronze/flights/**/*.parquet")
    .filter(pl.col("extracted_at") > checkpoint["last_flight_timestamp"])
    .collect()
)

# Append to existing fact table
append_to_fact_table(new_data)

# Update checkpoint
update_checkpoint({"last_flight_timestamp": new_data["extracted_at"].max()})

Data Quality in the Warehouse

Warehouse Performance Optimization

Warehouse Query Examples

Simple: Top airports by delay

SELECT
    da.airport_code,
    da.airport_name,
    ROUND(AVG(ff.delay_minutes), 1) AS avg_delay,
    COUNT(*) AS total_flights
FROM fact_flights ff
JOIN dim_airport da ON ff.departure_airport_key = da.airport_key
GROUP BY da.airport_code, da.airport_name
HAVING COUNT(*) > 1000
ORDER BY avg_delay DESC
LIMIT 10;

Complex: Weather-delay matrix

SELECT
    dw.condition AS weather_condition,
    dd.year,
    dd.month,
    COUNT(*) AS flights,
    ROUND(AVG(ff.delay_minutes), 1) AS avg_delay,
    ROUND(SUM(CASE WHEN ff.cancelled THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS cancellation_pct
FROM fact_flights ff
JOIN dim_weather dw ON ff.weather_key = dw.weather_key
JOIN dim_date dd ON ff.date_key = dd.date_key
GROUP BY dw.condition, dd.year, dd.month
ORDER BY dd.year, dd.month, avg_delay DESC;

Tools Comparison for Warehouse Engine

« Back to Index