Batch ELT Pipeline with Airflow, dbt and a Cloud Warehouse

The problem this pipeline solves

Plenty of organisations depend on public data that changes every day: exchange rates, air quality readings, fuel prices, public procurement notices, transport schedules. A newsroom wants to chart them, an NGO wants to track them, a small importer wants yesterday's rates in a spreadsheet. Usually one person downloads a file every morning and pastes it somewhere. When they are sick, the chart goes stale; when the source changes a column name, the numbers silently break.

That is also why data engineering is hard to learn from tutorials. Loading one file once is easy. Keeping a pipeline correct every day, rerunning a missed week without duplicates, and letting someone else understand it, is the actual job.

What already exists and the gap

• Fivetran and Airbyte handle ingestion from common sources; Airbyte has an open source edition, but niche public APIs often need custom connectors.
• dbt is the standard way to model data in SQL with tests and documentation.
• Managed Airflow (Astronomer, Google Cloud Composer, Amazon MWAA) removes operations work but costs money while idle.
• Data Engineering Zoomcamp teaches a similar stack for free and ends with a course project.

The gap for a small NGO or newsroom is a cheap, understandable pipeline for their specific source, with tests that stop bad data before it reaches a chart. For you, the gap is a portfolio project that proves judgement (grain, idempotency, cost), not just tool names.

What you build

Pick one real user and one source first, for example a local newsroom that tracks daily air quality across cities, or a research group that follows public procurement.

• Ingestion: a Python task that pulls the source daily, handles pagination, rate limits and retries, and writes raw responses to object storage partitioned by date.
• Warehouse model with dbt: staging models per source, a fact table with a written grain ("one row per station per hour"), at least three dimensions, one Type 2 slowly changing dimension via a snapshot, and one incremental model.
• Tests: keys, relationships, accepted values and source freshness.
• Orchestration: an Airflow DAG that runs ingestion then dbt for each data interval, with retries, timeouts, alerts and a documented 30-day backfill.
• Infrastructure and CI: Terraform for storage, warehouse and a least-privilege identity; GitHub Actions that lint, test, parse the DAG and run dbt build against a CI schema.
• Dashboard: a simple one on the gold tables, built with your user.

The technically interesting part

The core problem is idempotency across time. A run for 12 March must produce the same result whether it runs once, three times, or as part of a backfill two months later. That means partition-overwrite or MERGE loads keyed on the data interval, not "append whatever arrived". Show your supervisor a backfill of 30 days, then rerun it, and prove row counts and checksums do not change. The second problem is deciding the grain of the fact table and defending it: it drives every join and every metric downstream.

How to evaluate it

• Backfill test: 30-day backfill run twice with identical results.
• Failure drills: simulate an API outage, a schema change and a late file; record what the pipeline did and how long recovery took.
• Data quality: number of test failures caught before they reached the dashboard over a month of daily runs.
• Cost: measured monthly cost of storage, compute and scheduling.
• User check: your newsroom or NGO contact uses the dashboard for two weeks and reports what they trusted and what they did not.

Suggested plan

1. Weeks 1 to 2: choose the user and source, sketch the model, set up Terraform and a budget alert.
2. Weeks 3 to 5: ingestion with retries and raw storage, staging models.
3. Weeks 6 to 8: fact and dimensions, snapshot, incremental model, tests.
4. Weeks 9 to 11: Airflow DAG, backfill, alerts, CI.
5. Weeks 12 to 15: dashboard, month of daily runs, cost measurement, write-up.

Path to a product

The realistic path is a small service: set up and run pipelines for organisations that rely on public or partner data, charged as a setup fee plus a monthly maintenance retainer. Your first customer is the newsroom, NGO or research group you built it with. The project is also a strong portfolio centrepiece for data engineering roles because it matches the tools in most job descriptions.

Scope warning

Do not add streaming, machine learning or a second data source before the first one is reliable. Do not build your own orchestrator. Never commit credentials or Terraform state. Run Airflow locally in Docker unless you have credits, and pause or tear down anything that bills while idle.

Learn what you need on Sefism

• DevOps and cloud roadmap : Terraform, CI and cloud identity basics.
• Data analytics cheat sheet : SQL patterns you will use in dbt models.
• Apache Airflow Documentation : DAGs, data intervals, retries and backfills.
• dbt Learn : free courses on models, tests and snapshots.
• Data Engineering Zoomcamp : a free course with a very similar stack.
• Kimball Dimensional Modeling Techniques : grain, facts and slowly changing dimensions.

Get help with this idea

The usual failure is trying every tool and finishing none of them properly. Tauseef can help you choose one source and one user, cut the stack to what you can run on a budget, and design an evaluation examiners will respect. If you are new here, create a free account first.

• Discuss this idea with a mentor
• Send a query to Tauseef
• Book a 1:1 session