Build log
August 2026 · AZIMUTH

Phase 3: Gold layer

The star schema, a "Not published" row for launches with no orbit, six more Moon launches, and the same float bug again.

Phase 3 was the star schema that the app reads from.

What got built

Five dimensions and a fact table, all in a gold schema, all under tests, all rebuilt by CI on every push:

  • dim_date, generated from SQL, 1957 through 2030, with the end date hardcoded and a note saying so.
  • dim_provider, 99 providers, enriched with country, sector, and founding year from the agencies endpoint.
  • dim_vehicle, 485 configurations, carrying the classification seed, plus full names and variants from the configurations endpoint.
  • dim_orbit, with the source's 25+ orbit values grouped into analytic classes.
  • dim_launch_site, 59 sites with countries, coordinates, and celestial bodies.
  • fct_launches, one row per launch attempt, five dimension keys, the outcome flags, and net_precision riding along so time-series views can exclude coarse historical dates on purpose instead of by accident.

The referential integrity is tested: every fact row must point at an existing row in every dimension, and the build fails if one does not. The date test also answers what happens to a launch after 2030: the build fails instead of the launch silently disappearing from joins.

Launches with no orbit

The orbit dimension needed some thought, because 530 launches, about 7%, have no orbit. Phase 2 showed why: it depends on what the provider publishes, and the gap is mostly Chinese, Russian and classified missions.

A dimension where 7% of the keys are missing would silently drop those launches from every orbit view through the join. The standard fix is an unknown member: the dimension gets a row for "Not published", and the fact table maps null orbits to it. The gap then shows up as its own category in every orbit breakdown.

For contrast, zero launches are missing a launch site.

Six more Moon launches

The scope document said the dataset contained five launches from the lunar surface: the Luna and Chang'e sample-return ascent stages, found during the seed review.

Phase 3 added the pad's location and celestial body to bronze. No new API calls were needed: the raw JSON files from Phase 1 already had everything, so it was a re-flatten of files on disk and a rebuild of the bronze table.

The new column said eleven Moon launches, not five.

The other six are the Apollo Lunar Module ascents. The seed had classified Apollo LM as not_a_launch_vehicle, reasoning that it is a spacecraft. But the LM ascent stage is a launch vehicle; it just launches from the Moon.

After reclassifying it, the two derivations agree exactly: 11 launches where vehicle_class = 'non_earth_launch', 11 launches where the pad's celestial body is the Moon. Each can now be used to test the other.

The float bug again

In Phase 2, orbit id 15 arrived in bronze as "15.0" because pandas turns integer columns with nulls into floats before my flatten stringifies them. I fixed it in staging and thought I understood it.

Building the reference ingestion, I assumed converting the DataFrame to the pandas string dtype would prevent it at the source. It did not: the provider dimension failed on a founding year of "2020.0". The conversion to float happens when the DataFrame is constructed, before any later conversion runs, unless you use the nullable integer dtype explicitly.

It is now documented in the scope document as a pattern: any nullable numeric field flattened through pandas gets checked for it, and the fix lives in staging.

Reference data

The provider and vehicle enrichment needed two more endpoints: agencies and launcher configurations. Production is rate limited to about 15 calls an hour, and the two endpoints together are about 18 pages.

I fetched them from the development mirror instead, which has no rate limit, and checked that every one of the 99 provider ids and 485 configuration ids in the launch data was present. Zero missing. The shortcut is in the scope document, and if a future check finds a gap the missing records get fetched from production.

One simplification: an agency's country is a list in the source, because agencies can be multinational. The flatten keeps the first country plus a count.

Vehicle names

Phase 2 left an open item: the name "Falcon 9" covers five separate configurations in the source, and "Delta II" covers twenty-five. The configurations endpoint resolved it, because the source already has full names. The vehicle dimension now shows Falcon 9 Block 5 with 619 launches, Full Thrust with 25, v1.1 with 15, Block 4 with 12, and v1.0 with 5.

The true duplicates, Soyuz U and Soyuz-U as two records for one vehicle, are still two rows. Collapsing them needs an id mapping, and I am leaving that until the analysis needs it, because the analysis also has to decide whether a vehicle is a configuration or a family. That is Phase 4's first decision.

Next

Phase 4 is the analysis layer: cadence, market share, and reliability by flight number.