Economic Indicators Dashboard

Why I built it

I had been taking data engineering courses and wanted to put the whole thing into practice in one place instead of as isolated exercises: consuming public APIs that agree on nothing, loading them into a database, modelling them into something queryable, and wiring it all into something that runs on a schedule without me. Macroeconomic indicators were a good excuse. They are free, genuinely messy, and spread across two APIs with different formats, frequencies and rate limits. What I did not expect was how much of the difficulty would turn out to be economic rather than technical.

The approach

The first version was ETL: clean the data in Python, then store it. I rebuilt it as ELT, and that single change is what the project is really about. Raw payloads now land untouched in Postgres, and every transformation happens in the warehouse with dbt, where it is versioned, tested and visible. The reason is not fashion. Cleaning before persisting had been quietly writing values the sources never published, and I only found that once the transformations were somewhere I could test them.

How it works

1. 01

   Extract

   Python clients pull 13 series from FRED and Alpha Vantage, caching raw JSON with a 24-hour TTL and throttling calls to stay inside Alpha Vantage's 25-a-day free tier. A response that fails validation is never cached, so a rate-limit reply cannot poison the next run.

2. 02

   Load

   Observations are batch-upserted into Postgres via ON CONFLICT, one atomic transaction per indicator. The upsert only writes when a value actually changed, so the load timestamp marks source revisions rather than pipeline runs. Every run refetches full history on purpose: FRED restates published figures backwards, and an incremental load would silently never see it.

3. 03

   Model

   dbt turns the raw tables into staging views and two marts. Each series is stacked at four resampling levels in a grain column, with change metrics against the previous period and against the same period a year earlier already computed. The dashboard's resampling control stopped being a parameter and became data.

4. 04

   Test

   28 dbt tests, split between structural assertions and the ones a schema cannot express: accepted values on the indicator catalog, no future dates, no stale feeds, no scale jumps, and non-negative values on the series where a negative is impossible. Findings that could be a real event warn; findings that cannot be real fail the build.

5. 05

   Schedule

   A GitHub Actions workflow runs the pipeline and dbt build every morning against Supabase. The run fails loudly: the pipeline records every indicator it could not load and exits non-zero, so a partial failure shows red instead of green while the series that did load stay committed.

6. 06

   Serve

   Streamlit Community Cloud reads only the marts. Selecting a series, a resampling level and a metric is a WHERE clause over precomputed rows, not a recalculation, and the options are constrained by what each series actually has — quarterly GDP is never offered a daily view.

Stack

Language

Python (pandas, psycopg2)

Modelling

dbt (Postgres adapter)

Storage

Supabase · PostgreSQL 16 locally

Dashboard

Streamlit + Plotly

Orchestration

GitHub Actions

Local stack

Docker Compose

Sources

FRED · Alpha Vantage APIs

What I learned

• — Consuming public APIs is less about HTTP than about their failure modes. Two sources, three different payload shapes, and a rate-limit reply that arrives as a perfectly valid 200 with a different key — it looks like data until you validate it, and if you cache it blindly it poisons the next 24 hours of runs. Caching raw responses with a TTL is what makes a 25-calls-a-day quota workable in the first place.
• — Modelling with dbt was the largest thing I picked up here: sources, staging views, marts, window functions, generic and singular tests, and documentation that renders into a lineage graph. The idea I keep is that a percentage change means nothing until you know what one row represents. Stacking every series at four grains and letting the lag follow the grain turned an ambiguous button into a filter over data that already knows what it is.
• — On the Python side — pandas for parsing, psycopg2 for batch upserts — the lesson was about where transformation belongs. The pipeline used to forward-fill gaps before storing, and because one API returns rows newest-first, the fill propagated backwards in time: twelve years of 1980s corn prices were being written as the price from January 1992. Moving transformation into the warehouse is what made it visible.
• — Getting something into production is its own skill set, separate from building it. Supabase for managed Postgres, GitHub Actions for scheduling, Streamlit Community Cloud for the app and Docker Compose for a reproducible local stack. The piece that took the most thought was the least glamorous: credentials arriving from a file, from CI secrets or from the host's own store, resolved through one code path so nothing branches on where it happens to be running.
• — A test that cannot fail is not a test. Restating primary keys the database already enforces looks like coverage and proves nothing. The ones that earn their place are the ones no schema can express, and calibrating them meant measuring the real data instead of guessing: a quarterly series can be seven months 'behind' and be perfectly healthy, and WTI really did settle at −36.98 in April 2020.
• — Economic judgement showed up in places I expected to be purely technical. Unemployment rising from 5% to 10% is +5 percentage points, not +100%, and which one you display depends on what the series measures. Deciding that gold cannot go negative but crude can is a domain call, not an engineering one.

Limitations & next steps

• ○ The dashboard is the endpoint of a pipeline, not an analysis tool. The 13 indicators were picked to exercise two very different APIs, not because they answer a question together. Base 100 and percentage change make them plottable on shared axes, but plottable is not comparable, and the app says so when the units disagree.
• ○ Source revisions are detectable through the load timestamp but not versioned, so the previous value of a restated figure is lost. dbt snapshots on real GDP are the obvious next step.
• ○ Data quality is covered; the code is not. There are 28 dbt tests and no Python tests, and the parsing layer that handles three different JSON shapes is exactly where a silent break would come from.
• ○ The Alpha Vantage free tier is a hard ceiling at about 25 calls a day, and a CI runner starts with an empty cache every time. Adding commodity sources means paying for it or rethinking the cadence.
• ○ What I want to build next is the dashboard this one is not: a coherent set of indicators chosen to answer real questions, with the comparisons that follow from those questions, putting the analysis first and letting the pipeline serve it rather than the other way round.

Outcome

• ✓ A public dashboard refreshed daily without intervention, reading from Supabase through marts that are rebuilt and tested on every run.
• ✓ About 59,000 raw observations across 13 indicators, expanded to roughly 64,000 modelled rows at four resampling levels, with history back to 1947.
• ✓ One `docker compose up` reproduces the entire stack locally — database, load, dbt models and dashboard — and the same code runs unchanged in production, where the two dbt targets differ by a single line.
• ✓ Removing the ingestion-time cleaning stopped roughly 1,500 invented market-holiday observations and twelve years of fabricated commodity history from entering the database.