I built a free Data Engineering pipeline with dbt, DuckDB and GitHub Actions (no AWS)

Can you build a "real" data pipeline (ingestion, warehouse, transformations, data quality tests, orchestration, deployment and a public dashboard) without paying a single dollar and without touching AWS?

Yes. In this article I'll show you how I did it with Python, DuckDB, dbt, GitHub Actions and GitHub Pages. The result is a website that updates itself every day with the weather of 10 Latin American capitals.

• 🔗 Live demo: https://dayan-18.github.io/clima-latam-pipeline/
• 📚 Documentation and lineage (dbt docs): https://dayan-18.github.io/clima-latam-pipeline/docs/
• 💻 Repository: https://github.com/Dayan-18/clima-latam-pipeline
• 🎥 Video (under 5 min):

The problem

I wanted to answer a few simple but interesting questions:

• Which Latin American capital got the most rain in the last 60 days?
• How much does altitude affect temperature? (spoiler: La Paz sits at 3,600 m)
• Will the next 7 days be warmer or colder than the recent average?

To answer them I need data that arrives every day, that must be cleaned and validated, and that has to be published for anyone to see. In other words: a classic Data Engineering problem.

The architecture

Open-Meteo API ─▶ Python (extract + load) ─▶ DuckDB ─▶ dbt (staging → marts + tests) ─▶ GitHub Pages ▲ │ └─────────── GitHub Actions: daily cron + every push ◀──────────────┘ 

The key idea: each piece does one thing. Python moves data, dbt transforms and validates it, Actions orchestrates, Pages publishes.

Step 1 · Extraction: one call, ten cities

The cities live in a CSV file (dbt/seeds/cities.csv). It is the single source of truth: the extractor reads it to know which coordinates to request, and dbt loads it as a table for joins.

city_id,city_name,country,country_code,latitude,longitude lima,Lima,Peru,PE,-12.0464,-77.0428 bogota,Bogotá,Colombia,CO,4.7110,-74.0721 la_paz,La Paz,Bolivia,BO,-16.4897,-68.1193 ... 

Open-Meteo accepts several comma-separated coordinates, so a single request is enough. I also added retries with exponential backoff, because APIs fail:

def fetch_weather(cities, retries=3): params = { "latitude": ",".join(c["latitude"] for c in cities), "longitude": ",".join(c["longitude"] for c in cities), "daily": "temperature_2m_max,temperature_2m_min,precipitation_sum," "wind_speed_10m_max,weather_code", "past_days": 60, "forecast_days": 7, "timezone": "auto", } for attempt in range(1, retries + 1): try: resp = requests.get(API_URL, params=params, timeout=30) resp.raise_for_status() return resp.json() except requests.RequestException: if attempt == retries: raise time.sleep(2 ** attempt) 

The response is stored as is in data/raw/open_meteo_<timestamp>.json, together with the extraction time and the source URL. That's our landing zone: if I change a transformation tomorrow, I can reprocess without calling the API again.

Step 2 · Loading into DuckDB (idempotent)

The JSON contains arrays per city (time[], temperature_2m_max[]…). The loader "flattens" them into one row per city per day and inserts them into raw.weather_daily:

def flatten(document): rows = [] for city_id, resp in zip(document["city_ids"], document["responses"]): daily = resp["daily"] for i, day in enumerate(daily["time"]): rows.append((city_id, day, *(daily[v][i] for v in DAILY_VARS), resp["latitude"], resp["longitude"], resp.get("elevation"), resp.get("timezone"), document["extracted_at"])) return rows 

One important detail: I use create or replace table. Since every extraction brings the full window (60 days + 7 days of forecast), running the pipeline twice never duplicates data. That's called idempotency, and it has its own pytest test:

def test_load_is_idempotent(tmp_path): load(raw, db) load(raw, db) # twice assert con.execute("select count(*) from raw.weather_daily").fetchone()[0] == 4 

Note: the loader has no business logic. No rounding, no renaming. That's dbt's job.

Step 3 · Transformation with dbt

dbt lets you write transformations as SQL SELECTs, with dependencies, tests and documentation. With the dbt-duckdb adapter it runs locally, with no server.

I organized the project in layers:

Staging only cleans:

select city_id, cast(weather_date as date) as weather_date, round(temperature_2m_max, 1) as temp_max_c, round(temperature_2m_min, 1) as temp_min_c, round(coalesce(precipitation_sum, 0), 1) as precipitation_mm, cast(weather_code as integer) as weather_code, cast(elevation as integer) as elevation_m, cast(extracted_at as timestamp) as extracted_at from {{ source('open_meteo', 'weather_daily') }} where temperature_2m_max is not null 

The fact table adds derived metrics and joins each WMO code with its description:

select weather.city_id || '_' || strftime(weather.weather_date, '%Y%m%d') as weather_day_id, weather.city_id, weather.weather_date, round((weather.temp_max_c + weather.temp_min_c) / 2, 1) as temp_avg_c, round(weather.temp_max_c - weather.temp_min_c, 1) as temp_range_c, coalesce(codes.description, 'Unknown') as weather_description, weather.precipitation_mm >= 1.0 as is_rainy_day, weather.weather_date >= cast(weather.extracted_at as date) as is_forecast from {{ ref('stg_open_meteo__weather_daily') }} as weather left join {{ ref('weather_codes') }} as codes using (weather_code) 

The mart mart_city_summary summarizes each city and compares the observed period with the forecast. DuckDB ships very handy functions such as arg_max (which day was the hottest?) and mode (what weather was most common?):

select city_id, round(avg(temp_avg_c), 1) as avg_temp_c, round(sum(precipitation_mm), 1) as total_precipitation_mm, count(*) filter (where is_rainy_day) as rainy_days, arg_max(weather_date, temp_max_c) as hottest_day, mode(weather_category) as dominant_category from {{ ref('fct_weather_daily') }} where not is_forecast group by city_id 

Because I use ref() instead of table names, dbt builds the dependency graph by itself. And with dbt docs generate that graph is published as browsable lineage:

Step 4 · Data quality: if a test fails, nothing gets published

This is my favorite part. dbt build builds the models and runs 23 tests in the right order. If one fails, the pipeline stops and the previous version of the dashboard stays online. Broken data is never published.

- name: weather_day_id data_tests: [unique, not_null] # one row per city per day - name: city_id data_tests: - relationships: # every city exists in the dimension  arguments: {to: ref('dim_cities'), field: city_id} - name: weather_category data_tests: - accepted_values: arguments: values: [clear, cloudy, fog, rain, snow, thunderstorm, other] 

I also wrote a custom generic test for physically possible ranges:

{% test value_between(model, column_name, min_value, max_value) %} select {{ column_name }} from {{ model }} where {{ column_name }} < {{ min_value }} or {{ column_name }} > {{ max_value }} {% endtest %} 

…and a singular test for an obvious business rule that is still worth checking:

-- the daily maximum can never be lower than the minimum select * from {{ ref('fct_weather_daily') }} where temp_max_c < temp_min_c 

Result:

Finished running 2 seeds, 3 table models, 23 data tests, 1 view model in 1.01s Done. PASS=29 WARN=0 ERROR=0 SKIP=0 

Step 5 · Orchestration and deployment with GitHub Actions

The whole pipeline runs with a single command: python -m pipeline.run. GitHub Actions triggers it in three cases: every day at 11:00 UTC (cron), on every push to main, and manually with a button.

on: push: branches: [main] schedule: - cron: "0 11 * * *" workflow_dispatch: jobs: build: runs-on: ubuntu-latest steps: - uses: actions/checkout@v5 - uses: actions/setup-python@v6 with: { python-version: "3.12", cache: pip } - run: pip install -r requirements.txt - run: pytest -q # Python unit tests  - run: python -m pipeline.run # extract → load → dbt build → publish  - uses: actions/upload-pages-artifact@v4 with: { path: public } deploy: needs: build runs-on: ubuntu-latest environment: github-pages steps: - uses: actions/deploy-pages@v4 

Notice needs: build: deployment only happens if everything before it passed. That's the pipeline's "quality gate". Pull requests run the tests but don't deploy.

The only manual setup is a one-time step: Settings → Pages → Source: GitHub Actions.

Step 6 · Serving: dashboard, docs and open data

The last step reads the marts and generates the public/ folder:

• index.html → dashboard built with Chart.js (temperature per city, comparisons and a summary table)
• docs/ → dbt documentation with the lineage graph
• data/*.csv and data/*.parquet → the data, so anyone can download it

Exporting to Parquet with DuckDB is a one-liner:

con.execute("copy marts.fct_weather_daily to 'public/data/fct_weather_daily.parquet' (format parquet)") 

What the data says

Run of November 10, 2026 (last 60 days):

• Altitude rules: La Paz is the coldest capital (11.1 °C average and a −3 °C minimum), even though it is closer to the equator than Buenos Aires.
• Quito is the rainiest: almost 536 mm in 60 days.
• Lima barely gets any rain, yet its "dominant" weather comes out as rain. That's the famous garúa: a drizzle so fine it doesn't reach 1 mm (22 of its 60 days were "light drizzle"). Data has its own stories.

Lessons learned

1. dbt docs generate overwrites run_results.json. If you want to show test results, copy that file right after dbt build.
2. DuckDB doesn't allow two connections with different configurations to the same file within the same process. When you invoke dbt from Python (dbtRunner), open your own connection with the same configuration (I was opening it as read_only and it failed).
3. Windows paths in YAML: C:\Users\... inside double quotes is parsed as escape sequences. Use Path.as_posix().
4. Idempotency first. Designing the load so it can be repeated without side effects makes everything else simpler.

Next steps

• Incremental dbt models to keep history beyond 60 days.
• Move the warehouse to MotherDuck (DuckDB in the cloud) to query it from anywhere.
• Orchestrate with Airflow or Dagster if the pipeline grows to more sources.
• Alerts (for example, to Telegram) when a data quality test fails.

Try it yourself

git clone https://github.com/Dayan-18/clima-latam-pipeline cd clima-latam-pipeline python -m venv .venv && source .venv/bin/activate # on Windows: .venv\Scripts\activate pip install -r requirements.txt python -m pipeline.run python -m http.server 8000 --directory public 

If you found this useful, leave a ❤️ or a comment. Which public data source would you plug into this pipeline?