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 ◀──────────────┘

Stage	Tool	Why?
Source	Open-Meteo	Free weather API, no API key
Ingestion	Python + requests	Simple, and a single call returns all 10 cities
Warehouse	DuckDB	Analytical database in a single file; zero servers
Transformation	dbt	Version-controlled SQL, data tests, documentation and lineage
Orchestration + CI/CD	GitHub Actions	Free daily cron and automated deployment
Serving	GitHub Pages	Free public hosting for the dashboard

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:

Schema	Contents	Materialization
raw	Data exactly as it arrives (Python)	table
seeds	cities.csv and weather_codes.csv (WMO codes → "Light drizzle", "Thunderstorm"…)	table
staging	Cleaning: types, names, rounding, nulls	view
marts	Business-ready models	table

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):

City	Elevation	Avg. temp	Total rain	Rainy days
Quito	2,920 m	14.4 °C	535.8 mm	49
Mexico City	2,230 m	18.3 °C	375.8 mm	43
La Paz	3,627 m	11.1 °C	108.8 mm	17
Lima	139 m	22.7 °C	20.6 mm	6

• 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?