This repository adds an additional Airflow orchestration option for the existing DBT SQL-Based Data Warehouse project by using Astronomer Cosmos.
The dbt SQL models are kept unchanged. Cosmos is used only to improve Airflow orchestration, model-level visibility, debugging, and rerun control.
The previous Airflow implementation used BashOperator tasks grouped by layer:
run_bronze_layer
run_silver_layer
run_gold_layer
This works, but each layer appears as one large task in the Airflow UI.
With Astronomer Cosmos, dbt models and tests are rendered as individual Airflow tasks. This makes the pipeline easier to monitor and debug.
Example task structure:
br_crm_cust_info
├── run
└── test
sl_crm_prd_info
├── run
└── test
dim_customers
├── run
└── test
Cosmos improves the Airflow experience for dbt projects by providing:
- Model-level task visibility
- Separate run and test tasks
- Better Graph view in Airflow
- Easier debugging when a single model fails
- More flexible rerun control
- No changes to the existing dbt SQL logic
In short, Cosmos makes the dbt pipeline easier to observe and operate inside Airflow.
The Airflow DAGs are located in:
airflow/dags/
├── dbt_sql_based_dwh_dag.py
├── dbt_sql_based_dwh_cosmos_dag.py
└── dbt_sql_based_dwh_cosmos_layered_dag.py
dbt_sql_based_dwh_cosmos_dag.py
This file contains the main Cosmos-based DAG definitions.
dbt_sql_based_dwh_cosmos_layered_dag.py
This DAG is mainly for learning and demonstration purposes. It explicitly shows the pipeline flow:
Bronze Layer -> Silver Layer -> Gold Layer
dbt_sql_based_dwh_cosmos_full_refresh
Use this DAG for the first run or when a full rebuild is required.
It runs dbt with:
full_refresh=True
This is equivalent to a first full load.
dbt_sql_based_dwh_cosmos_incremental
Use this DAG for normal scheduled runs.
It runs dbt with:
full_refresh=False
Bronze and Silver models use incremental logic. Gold models remain as views.
dbt_sql_based_dwh_cosmos_bronze_only
Use this DAG when only the Bronze layer needs to be rerun.
dbt_sql_based_dwh_cosmos_silver_only
Use this DAG when a Silver transformation has been fixed and Bronze does not need to be rerun.
dbt_sql_based_dwh_cosmos_gold_only
Use this DAG when only the Gold views need to be rebuilt.
dbt_sql_based_dwh_cosmos_layered_pipeline
This DAG shows the pipeline in a more traditional layer-by-layer structure:
Bronze -> Silver -> Gold
It is useful for learning, documentation, and visualizing the warehouse flow clearly.
For the initial run, trigger the full refresh DAG:
dbt_sql_based_dwh_cosmos_full_refresh
Recommended steps:
- Open the Airflow UI.
- Trigger the full refresh DAG.
- Open the Graph view.
- Expand the
dbt_project_graphtask group. - Monitor the dbt model-level tasks.
- Check the
preview_gold_outputstask log after the run finishes.
For regular scheduled runs, use:
dbt_sql_based_dwh_cosmos_incremental
This runs the same dbt project using incremental logic.
One benefit of Cosmos is that a failed dbt model can be rerun without restarting the whole pipeline.
Steps:
- Open the failed DAG run in Airflow.
- Go to the Graph tab.
- Expand the Cosmos task group.
- Select the failed model task.
- Click Clear.
- Choose whether to clear only that task or include downstream tasks.
This is useful when only one model or test fails.
To rerun only one warehouse layer, use one of the layer-specific DAGs:
dbt_sql_based_dwh_cosmos_bronze_only
dbt_sql_based_dwh_cosmos_silver_only
dbt_sql_based_dwh_cosmos_gold_only
Example:
If the model silver.crm_prd_info fails, fix the dbt model and trigger:
dbt_sql_based_dwh_cosmos_silver_only
This avoids rerunning Bronze when it is not needed.
The Docker image includes the following main components:
Apache Airflow 2.10.5
Python 3.12
dbt-core 1.9.10
dbt-sqlserver 1.9.0
Astronomer Cosmos 1.10.1
Microsoft ODBC Driver 18 for SQL Server
git
git is included because dbt and Cosmos may use it for commands such as:
dbt deps
dbt debug
graph rendering
After changing the Dockerfile or DAG files, rebuild the environment:
docker compose --env-file .env down
docker compose --env-file .env build --no-cache
docker compose --env-file .env up airflow-init
docker compose --env-file .env up -dOpen the Airflow UI:
http://127.0.0.1:8081
Run:
docker compose --env-file .env exec airflow-webserver python -c "import cosmos; print('Cosmos is installed')"Expected output:
Cosmos is installed
Run:
docker compose --env-file .env exec airflow-webserver dbt debug --project-dir /opt/airflow/dbt_project --profiles-dir /opt/airflow/dbt_profilesExpected result:
Connection test: [OK connection ok]
All checks passed!
| Approach | Airflow Visibility | Rerun Control |
|---|---|---|
| BashOperator | One task per layer | Rerun the whole layer |
| Astronomer Cosmos | One task per dbt model/test | Rerun a failed model or selected layer |
Astronomer Cosmos improves the way Airflow displays and manages the dbt project.
Instead of showing only large Bronze, Silver, and Gold tasks, Cosmos exposes each dbt model and test as separate Airflow tasks. This makes the pipeline easier to monitor, debug, and rerun while keeping the existing dbt SQL logic unchanged.