Automated End-to-End Cloud ELT Pipeline & Dimensional Modeling
1. Dataset, Scope & Objectives
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
- 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 (
uniqueandnot_nullconstraints) 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.