Phase 2: set up dbt, build the first silver model, add tests, wire up CI. The first task was re-running the Phase 0 checks against the full history instead of the 100 most recent launches, and the result changed a scope decision.
The missing orbits
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 in the early history, as I expected. The 1950s are complete. The 2010s are missing 28%, the worst decade in the dataset.
- By provider: 55% of CASC's 2010s launches have no orbit. ILS 61%. Kosmotras 100%. Sea Launch 78%. Arianespace 3%.
The gap depends on how much the provider publishes. Chinese, Russian and classified military missions do not publish orbital parameters. ULA sits at 15%, mostly NRO payloads.
The scope document used the mission's orbit field to decide what is orbital. That filter would have dropped half of China's launches in the 2010s, which is exactly the market share the platform is meant to measure. The field reflects what providers publish, not what they flew.
Classifying by vehicle
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 from the configurations endpoint and the data did not support it. leo_capacity = 0 covers both New Shepard (suborbital) and GSLV Mk II (orbital; nobody typed the LEO number for a GTO vehicle). The apogee field has Ariane 5 ECA at 40,000 km because someone entered its GTO apogee. Any rule built on those fields inherits the same problems.
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, and two categories appeared that I had not expected:
- 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 and 6, sample return missions whose ascent stages are tracked as launches, dated at lunar liftoff. They got their own class and are excluded from the orbital figures.
- One record called "Mir (3 stage)" is South Korea's military solid-fuel launcher, not the space station.
So the classification is AI-assisted and human-reviewed. Without the review, six errors would be in the warehouse now.
One config, both regimes
The seed alone is not enough, because a few configurations 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.
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 is in the staging model: 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 are the checks I had been running by hand all week. Now they 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. 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 leading slash fixed it. git check-ignore -v finds this kind of thing, and git ls-files shows what the repo actually contains.
Next
Phase 3 is the gold layer: the star schema. One open question already: "Falcon 9" covers five configurations in the source, so the analysis has to decide whether a vehicle is a configuration or a family.