Data Analyst · intermediate
End-to-End ELT Pipeline with dbt and Airflow
A scheduled pipeline that extracts from an API, loads to a warehouse, transforms with tested dbt models, and orchestrates the whole thing in Airflow — with data quality tests that fail loudly.
Project overview
Analyst portfolios are saturated with dashboards, which means dashboards no longer differentiate you. The scarce skill is building the pipeline that feeds them — and dbt plus Airflow is the stack that most data teams actually run, so this maps directly onto a data engineering job description.
Real-world use case
A company pulling sales data from three sources needs it cleaned, joined, and refreshed nightly so that every dashboard in the business reads from one trusted set of tables.
Tools and technologies
- Apache Airflow
- dbt
- Snowflake/Postgres
- Python
- Docker
Skills you can demonstrate
Data Modeling, Pipeline Orchestration, Data Quality Testing, SQL, ELT Architecture
Build roadmap
- Set up the warehouse
Get a Snowflake trial or run Postgres locally in Docker. Create the database and schema your pipeline will write into. - Write the extract and load
Pick a free public API and write a Python script that pulls the data and lands it raw in the warehouse. Do not clean anything yet — raw stays raw in ELT. - Build the dbt project
Initialize dbt and build your staging, intermediate, and mart layers. Use the AI prompt below to get the structure right before writing SQL. - Add data quality tests
Write schema tests for uniqueness, not-null, and accepted values. Deliberately break your data and watch a test fail — then screenshot that. It proves the tests do something. - Orchestrate with Airflow
Build a DAG that runs extract, then load, then dbt run, then dbt test — with the dependencies wired so a failed test stops the pipeline instead of publishing bad data. - Document and schedule
Generate dbt docs to get the lineage graph. Schedule the DAG nightly and let it run for a few days so you have real run history to show.
Ways to extend the project
Add dbt snapshots to track slowly changing dimensions, so you can answer "what did this customer record look like last quarter" — a question dashboards alone cannot handle.
How to showcase your work
- GitHub: Repo with your dbt project, Airflow DAG, a dbt docs lineage graph screenshot, and a README explaining the layered model design.
- LinkedIn: Post the lineage graph — it makes the sophistication of the work visible at a glance in a way SQL screenshots never do.
- Resume: Focus on "ELT pipeline development", "dbt data modeling", and "Airflow orchestration".