AZIMUTH · Launch Market Intelligence Platform

Phase 3: The gold layer, and finding the Moon twice

Phase 3 was the star schema: the dimensional model that every dashboard view will sit on. Most of the hard decisions were already made in Phases 0 through 2, so this phase was about building what those decisions implied, and finding out where they were still wrong.

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. That test on the date key is also the answer to a question I asked myself early: what happens to a launch after 2030? It goes red instead of silently vanishing from joins.

The unknown member

The interesting design problem was the orbit dimension, because 530 launches, about 7%, have no orbit at all. Phase 2 established why: the gap tracks provider transparency, and it concentrates in 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 called an unknown member: the dimension gets an explicit row for "Not published," and the fact table maps null orbits to it. The result is that the gap shows up as a visible category in every orbit breakdown by default. The transparency finding is now a row in a table.

For contrast: zero launches are missing a launch site. Where a launch happens is never a secret. Where it goes often is.

Finding the Moon twice

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. This required no new API calls: the raw JSON archive from Phase 1 already contained everything, so it was a re-flatten of files on disk plus a rebuild of the bronze table. The immutable raw archive is a design decision that has now paid for itself twice.

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. That was imprecise: the LM ascent stage is a launch vehicle, it just launches from the Moon. July 21, 1969, in this dataset, is Eagle lifting off from Tranquility Base.

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. Two independent paths to the same boundary, one from human judgment in the seed, one from the source's own geography, and each can now serve as a test of the other.

The float artifact's third bite

A recurring bug earned a name this phase. In Phase 2, orbit id 15 arrived in bronze as "15.0" because pandas promotes integer columns containing nulls to float before my flatten stringifies them. I fixed it in staging and thought I understood it.

Building the reference ingestion, I claimed converting the DataFrame to pandas string dtype would prevent the artifact at the source. Wrong, and the provider dimension failed on a founding year of "2020.0" to prove it. The promotion happens when the DataFrame is constructed, before any conversion runs. Nullable integers become floats at birth unless you reach for pandas' nullable integer dtype explicitly.

So the artifact is now a documented pattern in the scope document rather than a bug I fix wherever it appears: any nullable numeric field flattened through pandas gets checked for it, and the normalization lives in staging.

Reference data, and admitting a shortcut

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 then verified the shortcut: every one of the 99 provider ids and 485 configuration ids appearing in launch data was checked against the mirror snapshots. Zero missing on both counts. The scope document records the shortcut and its justification together, and if a future check finds a gap, the missing records get fetched from production.

One simplification came along with the agencies data: an agency's country is a list in the source, because agencies can be multinational. The flatten keeps the first country plus a count, and any agency where the count is greater than one is carrying a known simplification.

Where the display names ended

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 without any string surgery, because the source already maintains disambiguated 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 a canonical id mapping, and I am deferring that until the maturation analysis actually needs it, because that analysis also has to answer a harder version of the same question: whether a vehicle is a configuration or a family. Five Falcon 9 curves and one Falcon 9 curve tell different stories, and choosing is Phase 4's first decision.

Next

Phase 4 is the analysis layer: cadence, market share, and the maturation dataset with reliability by flight number in a vehicle's life. The plumbing is done. What comes next is the part the plumbing was for.

Phase 2: The audit that overturned my own scope document

Phase 2 was supposed to be plumbing: set up dbt, build the first silver model, add tests, wire up CI. All of that happened. But the first task of the phase was re-running the Phase 0 data checks against the full history instead of the 100 most recent launches, and what came back forced me to rewrite a scoping decision I had already committed to the repo.

The numbers that did it

Three SQL queries against bronze:

• 517 of 7,598 launches have no mission record at all. 530 have no orbit. About 7% of the dataset. My scope document said zero and zero, because on the 100 most recent launches it was zero and zero.
• The missing orbits are not concentrated in early history, which is what I expected. The 1950s are complete. The 2010s are missing 28%, the worst decade in the entire dataset and three times worse than anything around it.
• Broken down by provider, the pattern is obvious: 55% of CASC's 2010s launches have no orbit. ILS 61%. Kosmotras 100%. Sea Launch 78%. Arianespace: 3%.

The gap tracks how transparent the launching organization is. Chinese, Russian, and classified military missions do not publish orbital parameters. Even ULA sits at 15%, which fits: NRO payloads.

The scope document drew the orbital/suborbital boundary on the mission's orbit field. That filter would have silently deleted half of China's launch activity in the 2010s, which is exactly the market share the platform exists to measure. The field reflects what organizations publish, not what they flew.

The fix, and the fix for the fix

The replacement rule: classify by vehicle. A Long March with no published orbit is still an orbital launch, because Long March is an orbital-class vehicle.

I tried to derive the classification automatically from the configurations endpoint and the data would not support it. leo_capacity = 0 covers both New Shepard (genuinely suborbital) and GSLV Mk II (obviously orbital, zero apparently meaning "nobody typed the LEO number for a GTO-focused vehicle"). The apogee field records Ariane 5 ECA at 40,000 km because someone entered its GTO apogee. Any rule built on those fields inherits their inconsistency silently.

So the classification is a curated seed: a CSV in the repo, one row per launcher configuration that appears in the launch data, 485 rows. I generated the initial classification with AI assistance, flagged 25 rows as uncertain, and verified every flagged row against the actual launch records in bronze.

Six of the 25 changed on review. Two whole categories appeared that neither I nor the model anticipated:

• The BOR-4 flights on K65M-RB were orbital spaceplane tests, not suborbital probes.
• Blue Scout II's single flight was Mercury-Scout 1, an orbital attempt.
• Two Scout variants went the other way: their flights were reentry tests the API itself marks Suborbital.
• Five records are launches from the surface of the Moon. Luna 16, 20, 24 and Chang'e 5, 6: sample return missions whose ascent stages lifting off from the lunar surface are tracked as launches, dated at lunar liftoff. The dataset contains a launch market I was not modeling. They got their own class and an exclusion.
• One record called "Mir (3 stage)" turned out to be South Korea's military solid-fuel launcher, now named Mir, no relation to the space station.

The classification is honest to describe as AI-assisted and human-reviewed. The review is what makes the second half true: six errors in 25 flags would all be in the warehouse now if I had skipped it.

One config, both regimes

The seed alone is not enough, because a handful of configurations genuinely flew both orbital and suborbital missions. Scout X-1 flew five orbital attempts and one suborbital probe. Electron may carry HASTE's suborbital flights. The earliest Mercury-Atlas flights were suborbital.

So the silver rule is two steps: the vehicle class from the seed is the default, and the per-launch orbit field overrides it where it explicitly says Suborbital. When the first version of the model ran, the override moved zero launches, which was wrong. The reason was a bug worth writing down.

The 15.0 bug

Bronze stores every column as a string, and my flatten goes through pandas. The orbit_id column has nulls, and pandas silently promotes an integer column with nulls to float. So orbit id 15 was stringified as "15.0", and the model's comparison against "15" never matched. The status_id column, which has no nulls, stayed integer and stringified cleanly. Same pipeline, different null profile, different format.

The fix lives in the staging model, which is the layer whose job is absorbing source formatting: cast the string to double, the double to int, the int back to string. After the fix, the override moved 48 launches: suborbital flights on orbital-class vehicles that would otherwise have counted toward orbital reliability.

The rest of the phase

dbt is set up against Databricks, the seed loads as a dbt seed, and the staging model casts types, derives the outcome flags from Phase 0's censoring split, and joins the classification.

Six tests now run on every build: launch ids unique and never null, the scope class always one of five allowed values, every config id present in the seed. They passed first try, because they encode checks I had been running by eye all week. The point is that they now run without me.

CI runs the same build on GitHub Actions on every push, into an isolated schema, with credentials in repo secrets. It went green on the first real run.

Two smaller catches along the way, both variations on lessons I apparently need to learn twice. The first seed load pulled a stale pre-review copy of the CSV. The load reported success, and only the verification query showed three classes instead of four. And the staging models turned out to have never been committed at all: the staging/ line in my Phase 1 gitignore, written for the parquet folder, matches a folder of that name at any depth, so it was silently swallowing models/staging/ too. One anchoring slash fixed it. git check-ignore -v is the diagnostic, and git ls-files is the difference between a commit message that claims something and a repo that contains it.

Next

Phase 3 is the gold layer: the star schema, for real. The first open question is already interesting: the "Falcon 9" display name covers five separate configurations in the source, so the maturation analysis has to decide whether a vehicle is a configuration or a family, and the answer changes what the reliability curves mean.

Phase 1: Bronze ingestion

Phase 1 was getting all 7,598 historical launches out of the Launch Library 2 API and into a Delta table. The test for "done" was not that the script ran. It was running the same load twice and having nothing change.

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. Then I got 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.

Both safety nets fired during 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, unattended. The retry logic waited ten minutes and carried on. I found out afterwards by scrolling back through 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.

That is how I can show the pipeline is idempotent rather than assert it:

What I have not proven

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.

Phase 0: Scoping what the data can honestly support

Before writing a single transformation, I wanted to know what this dataset can and cannot honestly support. Phase 0 was entirely about scope: reading the Launch Library 2 API closely, sketching the model, and making the judgment calls in writing so I am not tempted to hand-wave them later.

The grain is one row per launch, keyed on the launch UUID. Everything else hangs off that. Five nested objects came out of the API cleanly: provider, rocket configuration, mission, pad, and status. Which of these become conformed dimensions, and which end up as fact attributes, is a Phase 3 decision. Status in particular is more likely a degenerate attribute than a dimension. Two relationships are genuinely many-to-many (program and configuration families), so I parked them for a later version rather than forcing them into v1.

A few decisions were less obvious and worth writing down:

• Vehicle identity. A "rocket" in the API is an instance; the thing I actually want to compare is the configuration. So vehicle identity is rocket.configuration.id, and configurations get their own load.
• Outcomes and censoring. There are nine statuses. They collapse into four real outcomes and five schedule states. That split is the censoring boundary for any later reliability or maturation analysis, so I keep it explicit rather than flattening everything to success or failure.
• Partial failures. These are ambiguous by design. My working call is to keep partial failure as its own outcome rather than force it into success or failure, and to document the reasoning so the choice is defensible.
• Dates. net is the launch date, and its precision varies for older records, so I anchor on net rather than a window start.
• Orbital scope. I scope to orbital launches using mission.orbit, and I treat Unknown as its own third category instead of silently assuming orbital. Suborbital is cleanly excluded.

Then I ran a data-integrity check on the most recent 100 launches to see how bad the missing-data problem is:

Every record had a mission and an orbit, so mission.orbit is a clean filter with no missing-data judgment call on this sample. The orbit split: 60 Low Earth, 11 Sun-Synchronous, 10 Polar, 7 Geostationary Transfer, 2 Elliptical, and 1 Medium Earth, plus 4 suborbital and 5 Unknown.

One honest caveat I am carrying forward: this is the most recent 100 launches, which is the best case. Historical records from the 1960s and 70s are far more likely to have gaps, so I will re-run this same check against the full backfill in Phase 1 rather than assuming it holds.

The deliverable for this phase is a written scope document in the repo: the grain, which statuses count as success, how partial failures are handled and why, how the orbital scope is drawn, and what I am deliberately not modeling in v1. If someone asks me one question about this project, it will probably be answered there.

Next: Phase 1, the full production backfill from Launch Library 2, landing into bronze.