← Back to Home
CLOUD ELT • DATA ENGINEERING • CASE STUDY

Automated End-to-End Cloud ELT Pipeline & Dimensional Modeling

Role: Data Engineer Timeline: Jun 2026 Stack: Snowflake Data Cloud, dbt Core, Apache Airflow, Astronomer Cosmos, Astro CLI, Docker, Python, SQL

1. Dataset, Scope & Objectives

Project Type Personal Data Engineering Project (Cloud ELT Architecture)
Dataset Source Snowflake Sample Data (TPC-H Benchmark Schema)
Modeling Layers Medallion Staging Views → Kimball Star Schema Marts
Quality Scope Schema & referential tests (unique, not_null, FK integrity)

In production environments, disparate source data often suffers from schema fragmentation, redundant records, and recurrent manual script execution. This leads to fragile operational reporting and inconsistent metrics across downstream consumers.

The objective of this project was to architect a fully automated, containerized cloud ELT platform decoupling Snowflake storage/compute from Apache Airflow orchestration, transforming raw TPC-H operational records into clean Kimball Star Schema Data Marts.

2. System Architecture & Workflow

TPC-H Raw Data → Snowflake Cloud Data Warehouse → Airflow + Astronomer Cosmos DAGs (dbt_venv) → dbt Core (Staging → Kimball Star Schema Marts) → BI Readiness
  • Storage & Compute (Snowflake Data Cloud): Serves as the central data warehouse, decoupling compute from storage and isolating raw TPC-H landing zones from clean analytical marts.
  • Transformation (dbt Core & dbt_utils): Implements Medallion Staging (stg_tpch_orders) and Kimball Star Schema Mart layers (fct_orders) via modular SQL models.
  • Orchestration (Apache Airflow & Astronomer Cosmos): Dynamically parses dbt lineage into granular Airflow DAG tasks inside an isolated dbt_venv, triggering runs and schema checks sequentially.
  • Reproducibility (Docker & Astro CLI): Ensures identical runtime dependencies across local development and containerized deployment.

3. Data Quality Assurance & Testing

To guarantee the principle of "Trustworthy data before reporting", continuous automated testing was integrated into the pipeline:

  • Enforced Primary Key integrity (unique and not_null constraints) across staging and mart entities.
  • Validated Referential Integrity relationships between Fact and Dimension tables.
  • Automated task halting upon detecting anomalous schema changes or ingestion discrepancies.

4. Key Outcomes & Scope Notes

The pipeline eliminated manual script execution via containerized Airflow scheduling, materialized pre-joined Kimball Star Schema marts (fct_orders) to reduce repetitive multi-table joins in downstream BI workflows, and enforced automated dbt schema/referential quality gates prior to analytical consumption. Note: Timing claims were omitted because no controlled cross-warehouse cache benchmark was logged.