←BaldanAll skillsLet's talk

Skill 04 · Data Engineering

Pipelines that make messy data trustworthy.

Getting data out of systems and APIs, landing it safely, checking it, shaping it and serving it, on a schedule, without anyone watching. Shown here with pipelines that really run.

PythonSQLBigQueryGCPdbtAirflowData qualityStreaming
Open the pipeline lab

The pipeline on this page, in numbers

5

API requests

42,600

hourly slots landed

7

checks on every load

37,409

clean hours kept

1,559

daily averages

2

pages fed

9 pipelines behind this site46.0 s to rebuild the batch ones40.9 MB raw → 395 KB served

A pipeline that never sleeps

42,600 hours of Jakarta air, from the sky to this page.

The real pipeline behind this site's air pages, as one picture. Every square is five days of real hourly data. Scroll and watch it get fetched, landed, checked, cleaned, summarised and served.

The air above Jakarta, estimated every hourCAMS atmospheric model · published by Open-Meteo · −6.2°, 106.85°one request per year, no key neededGET air-quality-api.open-meteo.com/v1/air-quality?latitude=-6.2&longitude=106.85&hourly=pm2_5&timezone=Asia/Jakarta&start_date=YYYY-01-01&end_date=YYYY-12-315 requests · 42,600 hourly slotseach bar = 5 days of hours, coloured by PM2.5 · grey = the API sent empty values4 tries · waits grow · longer after 42920228,760 slots200 OK20238,760 slots200 OK20248,784 slots200 OK20258,760 slots200 OK20267,536 slots200 OKml/data/raw/live/pm25_jakarta.json1.1 MB · 42,600 hourly slots · each square = 5 days · landed exactly as it camethe real raw file, around the first value"time": ["08-04T05:00", "08-04T06:00", "08-04T07:00", "08-04T08:00", …]"pm2_5": [null, null, 94.3, 108.3, …]24 forecast hours✓Schema42,600 = 42,600✓Unique hours0 duplicates✓Hourly grid0 missing slots✓Rangemin 0.4, max 186.7✓Coverage start5,167 empty hours before …✓Gaps0 empty hours in 0 gaps, …✓Not the future24 hours set asideSilver: only the hours that are real−5,167 empty · −24 forecast → 37,409 hours, one typed row each312 squares · 4 Aug 2022 → 9 Nov 2026silver.pm25_hourlyhour (Asia/Jakarta)pm2_511-09 19:0040.511-09 20:0048.711-09 21:0064.711-09 22:0071.511-09 23:0075.4the newest rows, realGold: 1,559 daily and 52 monthly averagesevery bar is the average of 5 days, oldest on the left20232024202520262022May 2024: 72.5 µg/m³the worst month on recordServed small, to the pages that use itpages read a few kilobytes, never the 1.1 MB raw fileJakarta air dashboardair-dashboard.json · 25 KBLive Forecast Lab (LSTM)live-forecasts.json · 265 KBthe whole history, as the air dashboard shows itthe last 60 days, which the LSTM learns fromextractcheckloadservefetching the newest hour…–µg/m³ PM2.5every hour: extract → check → load → serveA pipeline that never sleeps312 squares of history around one fresh hour202220232024202520265,167 empty hours: expectedhistory starts 4 Aug 2022WHO 24-h guideline 15Indonesia 24-h standard 55

How I build them

Four rules every pipeline here follows.

What separates a script that worked once from a pipeline that can run every hour for years.

02

Idempotent

Run a job twice and get the same table: loads MERGE on a key instead of appending.

Duplicates, absorbed

03

Tested

Checks run before anything is published. A failing batch is held back, never half-published.

Live checks on three APIs

04

Observable

Freshness and volume are watched on every run, and someone hears about it when they drift.

Break the data, watch it caught

Explore

Pipelines you can watch run.

04Work case · pipelines and master data from real work, anonymised.

coming next