Build log
August 2026 · AZIMUTH

Phase 1: Bronze ingestion

All 7,598 launches from the Launch Library 2 API into a Delta table, and the problems along the way.

Phase 1 was getting all 7,598 launches out of the Launch Library 2 API and into a Delta table. Done meant running the same load twice with nothing changing the second time.

Splitting fetch from load

The API allows about 15 calls an hour, so pulling the full history takes around five hours. I split ingestion into two jobs instead of one.

Job one calls the API and saves each response to disk exactly as it arrived. Job two reads those files and merges them into the warehouse.

Reasons:

  • If load logic has a bug and both jobs are one script, fixing it costs another five hours of fetching. Split apart, I re-run the load against files already on disk in seconds.
  • Databricks Free Edition cuts off compute for the day if you exceed quota. Running a five hour job on it is a bad bet.
  • The raw files are a record of what the API returned on a given day, so if a number looks wrong later I can check whether the source was wrong or my code was.

Building against the dev mirror

The Space Devs run a development mirror without the rate limit. I built the whole pipeline against that first.

It has 354 records where production has 7,589, so it works for testing code but not for anything quantitative. Every null rate I checked in Phase 0 had to be rechecked against production.

Checkpointing

76 pages over five hours means the job has to survive interruption. It writes a state file after each page: the next URL, the page number, and the ingest date of the original run.

The ingest date is in there so that a run starting on the 11th and resuming on the 12th still stamps every file with the 11th. They belong to one run.

State is saved after the file is written, not before. That way a crash between those two lines costs me a repeated page rather than a skipped one.

The config mistake

PAGE_SIZE and SLEEP_SECONDS sit next to each other at the top of the file. Switching to the production API, I meant to set the sleep to 240 seconds. I set the page size to 240 instead.

Nothing errored. The API capped the page size silently and kept serving pages with no delay, because the sleep was still zero. Ten pages went through in about twenty seconds before I noticed and killed it, and the API returned a 429.

The fix was retry logic: catch a 429, wait ten minutes, retry the same page, give up after five attempts. I also want to add a startup check that validates the page size against the documented maximum, since the API will accept a value it has no intention of honouring.

The real run

At page 10 I closed the laptop lid by accident and the process died. Restarting picked up at page 11 from the state file.

At page 40 the API rate limited me again. The retry logic waited ten minutes and carried on; I only saw it afterwards in the log.

.venv in git history

Running git ls-files before pushing turned up several hundred megabytes of virtual environment tracked in the repo.

The folder had a .git in it from an earlier experiment and .venv had been committed then. Adding it to .gitignore afterwards does nothing, because .gitignore only stops git tracking files it is not already tracking. Nothing had been pushed, so I deleted the history and started clean.

What landed

76 pages, 7,598 records, all with unique launch ids.

The loader merges on launch id: update if the incoming record is newer, insert if it is new. Every column is stored as a string on purpose. If I cast dates and numbers in bronze, one malformed value fails the load and I lose the row. I would rather cast in silver, where a bad value shows up as a failed test.

There is a load audit table: one row per run with run id, timestamp, source file, rows in source, rows inserted, rows updated, duration, and status. It writes a row whether the merge succeeds or fails, so failed runs are still on the record.

Two runs, same result:

7,598run 1 · inserted
0run 2 · inserted
0run 2 · updated
7,598rows either way

What is still open

Matching row counts rule out duplicates. They do not rule out gaps.

The API paginates by offset. If records are inserted while a five hour run is in progress, offsets shift and a record can be pushed past a boundary already read. It would get skipped and the row count would not show it.

The count came back as 7,598 when the API reported 7,589 earlier the same day. My guess is launches that flew and were confirmed during the run, but I have not verified that. A completeness comparison against the source count is going on the data quality list.

Next

Phase 2 is the silver layer in dbt. First task is rerunning the Phase 0 null checks against all 7,598 records rather than the most recent hundred. Launches from the 1960s will be sparser than launches from last month, and that may change a scope decision I have already written down.