From a 100-Call API to a Public Dashboard: A Production Weather ELT Pipeline for Bucaramanga

I wanted a small project that behaves like a real one: data arrives on a schedule, gets transformed and tested, lands in a dashboard people can use, and the whole thing runs on my own server without me babysitting it. The subject is the weather in Bucaramanga, Colombia, the city where I live. The constraint that shaped every decision: a free API that allows 100 calls per month.

This article walks through what I built, the decisions behind it, the bugs I hit, and the data engineering skills each part demonstrates. The live dashboard is embedded at the end.

The stack at a glance

                                           

Layer	Tool	What it does
Source	Weatherstack API (free plan)	Current conditions, air quality and astronomy for one city
Ingestion	Python + Apache Airflow 3	Calls the API twice a day and stores the raw response
Storage	PostgreSQL	Raw JSONB payloads and the dbt models, with least-privilege roles
Transformation	dbt (run by Airflow in its own container)	Typed staging model, four marts, data tests and unit tests
BI	Apache Superset 6.1	Dashboard defined as code, branded, public and embeddable
AI access	MCP servers (Superset, Airflow, dbt, Postgres)	Let an AI assistant query and operate the stack through a private gateway
Platform	Docker Compose on a VPS, deployed with Coolify	Merge to GitHub, the server redeploys
Quality	GitHub Actions, Trivy, Dependabot	Lint, tests, dbt parse, security scan and a full end-to-end run on every push

1. Start with the constraint: audit the API before writing code

The first version of the pipeline only stored six fields for New York. Before expanding it, I tested what the free plan really allows with a handful of real requests instead of trusting the pricing page:

• The /current endpoint works over HTTPS and returns about 30 fields, including air quality (PM2.5, PM10, ozone, US EPA index) and astronomy (sunrise, sunset, moon phase). We were throwing most of them away.
• Bulk queries (several cities in one call) return error 604. Forecasts return 609. Two calls back to back return 106 rate_limit_reached.

That audit turned "we need a paid plan" into "we are wasting 80% of what the free plan already gives us". It also set the budget: one city, twice a day, 7 a.m. and 7 p.m. local time, about 62 calls a month, leaving room for retries.

2. Ingestion: store everything, decide later

Instead of adding 25 new columns to the raw table, the ingestion job now stores the complete API response in a JSONB column, plus a source flag (api or mock). The migration is additive and idempotent, so the existing production table upgrades itself on the next run and the old rows stay valid:

ALTER TABLE dev.raw_weather_data ADD COLUMN IF NOT EXISTS payload JSONB;
ALTER TABLE dev.raw_weather_data ADD COLUMN IF NOT EXISTS source TEXT;

This is schema-on-read: if tomorrow I want visibility or wind direction in a chart, it is a change in dbt, not in the ingestion code, and the history is already there.

Two guards protect the quota:

• A monthly budget check. Before each real call, the job counts this month's successful API rows. At 90, it logs a warning and skips the call instead of failing. Failed calls insert nothing, which is why the limit sits below 100.
• A pause between calls when more than one city is configured, because the free plan rejects back-to-back requests.

A mock mode returns a real Bucaramanga response with the current timestamp. CI, local development and the first deploy all run the full pipeline without spending a single API call.

3. Transformation with dbt: typed, tested, documented

The staging model turns the JSON into typed columns. Weatherstack sends some numbers as strings ("7.2") and some times as text ("05:41 AM", or "No moonrise" on some days). Two small macros parse them defensively and return NULL instead of breaking the run:

{{ safe_numeric("payload #>> '{current,air_quality,pm2_5}'") }} as aq_pm2_5,
{{ safe_time("payload #>> '{current,astro,moonrise}'") }}      as moonrise,

Rows from before the change have no payload, so the model falls back to the legacy columns and the New York history keeps working. On top of staging there are four marts: observations, daily averages (temperature, humidity, rain, UV, PM2.5), daily air quality and daily astronomy (day length, moon phase).

Testing is where dbt pays off:

• Unit tests feed a real JSON payload and a legacy row into the staging model and check the exact output: strings converted to numbers, "yes" to a boolean, "No moonrise" to NULL.
• Data tests check ranges (humidity and cloud cover between 0 and 100, EPA index between 1 and 6, UV not negative) and that each city has one row per day in the daily marts.
• Source freshness runs after every build and fails if the newest observation is too old.

4. Orchestration and CI: prove it works before it ships

Airflow runs three tasks: ingest, dbt build, then the freshness check. A failed task sends a webhook alert. On every push, GitHub Actions runs:

1. Ruff and 57 pytest tests (ingestion, budget guard, migration, dashboard definitions).
2. dbt parse.
3. A Trivy scan for vulnerable dependencies, committed secrets and container misconfigurations.
4. An integration job that starts Postgres and Airflow from a fresh checkout, runs the whole DAG in mock mode, checks that every mart has data, then takes a backup and restores it to prove the backup is usable.

CI caught two real bugs on the way. A pair of double quotes inside a YAML description broke dbt parse. A unit test compared 7.13 with the numeric 7.130, which dbt treats as different. When I could not run Docker locally, I reproduced the CI run with an embedded PostgreSQL and dbt in an isolated virtual environment: 43 of 43 dbt nodes passing before pushing again.

5. The dashboard as code, without taking it away from the UI

Superset is configured by a script that registers the database, datasets, charts and dashboard through its REST API. It runs automatically after every deploy as a one-shot container. Three design choices made it practical:

• Idempotent and rename-aware. When I translated every title to Spanish, existing charts were renamed in place instead of duplicated.
• The UI wins after creation. The script creates what is missing but does not overwrite charts, layout or colors edited by hand. A reset flag restores the repository version when that is what I want.
• Labels for humans. Columns get readable Spanish names with units, such as "Lluvia total (mm)" or "PM2.5 promedio (µg/m³)".

6. Data storytelling: the Atlético Bucaramanga theme

A weather dashboard for Bucaramanga should feel local, so the theme borrows the identity of the city's football club, Atlético Bucaramanga. Superset 6 themes are Ant Design tokens, so the brand became a design system rather than a pile of CSS:

• Color roles, not just colors. The crest yellow #FFED00 is unreadable as text or as a chart line on a light background, so it became the brand accent (header band, card edges). The away-shirt green #00522C is the primary color for actions and titles. Ink #1A1A2C is for text.
• A chart palette that stays legible, with the yellow darkened to gold, plus the crest green, olive and gray.
• Sports typography: Bebas Neue for the headline and big numbers, Oswald for chart titles and Montserrat for everything else.
• The crest is served from our Directus CMS, and the Content Security Policy allows exactly that host.

7. Public, embeddable and still locked down

To publish the dashboard in this article, I avoided the common shortcut of giving anonymous users the broad "Gamma" role:

• Anonymous visitors get Superset's built-in minimal Public role.
• Dashboard-level access (DASHBOARD_RBAC) means only dashboards tagged with that role, and the data behind their charts, are visible without logging in. SQL Lab, other dashboards and raw datasets stay private.
• A CSP frame-ancestors rule lets only this website embed it.
• On the website side, the article HTML is sanitized, and iframes are allowed only from an explicit list of hosts and paths.

8. Running it like production

• Least privilege: separate Postgres roles for the pipeline, Airflow's and Superset's own metadata, and a read-only reader that both the dashboards and the AI tools use.
• Private by default: only the dashboard has a public domain. Airflow, Postgres and the MCP servers are reachable only through an SSH tunnel. The server accepts SSH keys only, and unused ports are closed.
• Recoverable: daily database backups kept for 7 days, plus a restore test in CI.
• Maintained: Dependabot opens patch-only image updates, and resource limits keep one service from starving the others.
• Debugging from evidence. When the dashboard showed "ChunkLoadError", the server logs proved the browser never requested the failing file. It was a stale cache after a redeploy, not a broken release.

9. Working with AI agents, with a human in charge

I built this with Claude Code as a pair. I used parallel agents for ingestion, dbt and Superset, each working from an explicit contract of column names. MCP servers let the assistant query Postgres, trigger Airflow and edit Superset charts directly. AI sped up the typing. The engineering work stayed the same: define the constraints, write the contracts, review every diff, insist on tests, and refuse to ship until CI was green. When an agent's work broke the build, the tests caught it, not the dashboard's users.

Skills this project demonstrates

                                               

Skill	Evidence in this project
API integration under constraints	Audited the free plan with real calls; budget guard, rate-limit pause, mock mode
Data modeling	Raw JSONB plus typed staging and four marts; schema-on-read; backward-compatible migration
dbt	Macros, unit tests, generic and singular data tests, source freshness, documentation
Orchestration	Airflow 3 DAG with retries, timeouts, failure alerts and dbt in an isolated container
Testing and CI/CD	57 Python tests, an end-to-end DAG run and a backup restore test on every push
BI as code and data storytelling	Idempotent Superset setup, design tokens, accessible palette, localized labels
Security	Least-privilege roles, CSP, dashboard-level RBAC, HTML sanitization, Trivy, private services
DevOps	Docker Compose, Coolify deploys from GitHub, VPS hardening, backups
AI-assisted engineering	MCP servers for the data stack, parallel agents, human review and test gates

The live dashboard

This is the real dashboard, updated at 7 a.m. and 7 p.m. Bogotá time. Use the filter to explore, or open it on its own page.

   

The code is open on GitHub, including the dbt models, the Airflow DAG, the Superset setup script and the CI pipeline: henrymarinho90/weather-data-project.