How shubhamdalvi1999/data-transformation-using-dbt works
Project Type and Purpose
• This is a production-ready data pipeline (ETL/ELT) built with Apache Airflow (orchestration), DBT (transformations & tests), and PostgreSQL (data storage), all containerized via Docker Compose.
• It ingests weekly CFTC JSON and Eurostat Excel feeds, persists raw data, applies DBT SQL models, enforces data quality, and surfaces analytics-ready tables.
Key File-Structure Insights
• Airflow/
• dags/01_ingest_cftc_report.py & 02_ingest_eurostat_prices.py – two ingestion DAGs
• PostgreSQL/
• Dockerfile & Schema_definitions/Baseline_Schema.sql – raw & public schemas
• dsec_dbt/
• models/First_Transform/*.sql – raw→refined models
• dbt_project.yml, profiles.yml, macros/, seeds/, tests/
• docker-compose.yml – service orchestration for Airflow, Worker, Scheduler, DBT, PostgreSQL
• Vagrant-setup – optional local provisioning
Core Components
• CFTC API (JSON)
• Eurostat Excel feed
b. Orchestration Layer (rectangle grouping)
• Airflow Webserver & Scheduler (Gunicorn)
• Airflow Worker(s) / Celery Executors
• DAG definitions: CFTC Loader, Eurostat Loader, DBT run, Quality checks
• PostgreSQL Raw Schema (“raw.05_COT_…”, “raw.07_Eurostat_…” tables)
• PostgreSQL Public Schema (refined tables)
d. Transformation Layer (oval or rectangle)
• DBT Service container running dbt run & dbt test
• Models: 01_Refined_COT_Report (incremental), 02_Refined_Eurostat (full refresh)
• Data quality tests: not null, range checks, unique keys
• Docker Network linking Airflow, DBT, PostgreSQL
• Environment variables & profiles
Relationships and Data Flows
• Task → PostgresHook → Raw tables in “raw” schema
• Trigger DBT job → reads raw tables → writes refined tables to “public” schema
• DBT test suite → data quality results → if pass, surface final tables
• Airflow orchestrates weekly schedules; dependencies DAG→DBT→tests
Architectural Patterns & Principles
• Modular DAGs implementing hook-operator pattern (PostgresHook + pandas)
• Incremental vs full refresh modeling in DBT
• Separation of concerns: ingestion vs transformation vs testing
• Container isolation for reproducible environments
Essential Elements
• Component labels: service names, table names, model names
• Boundaries for Docker containers / network
• Scheduling annotations (weekly triggers)
• Materialization strategies (incremental vs table)