Backfilling five years of history when every edition has a different schema

The request sounds like one sentence. Load the last five years so we can see trends. What arrives is a folder of exports from three registration platforms, two badge vendors and one system that was decommissioned in 2023, and the 2022 file has a column called reg_type holding single letters that nobody currently employed can decode.

Backfilling five years of history is the job that turns a warehouse into something worth querying, and it is almost always underestimated, because the estimate is made by looking at the newest export and multiplying. The newest export is the one that matches your model. The other 39 are the work.

What actually changes between editions

Run a column inventory across 40 editions, meaning eight shows with five years each, and the shape of the problem becomes measurable rather than anecdotal.

A portfolio of that size typically produces a union of around 78 distinct column names across its registration exports, of which perhaps 14 appear in all 40 files. That is 17.9 per cent of the columns present everywhere, and it is the honest ceiling on what a single transformation can handle without special cases. Everything else exists in some editions and not others.

The differences come in four flavours, and each needs a different answer.

• Renames. The same meaning under a new label. registration_category in 2024 is reg_type in 2022. Cheap to fix, easy to miss, and the usual cause of a column that is silently null for three years.
• Code changes. The 2022 file uses A, B, C and D. The 2024 file uses the words Visitor, Buyer, Press and Exhibitor Staff. Nobody wrote down that C means Press, and the person who knew has left.
• Granularity changes. A single job_title field becomes job_function plus seniority. Splitting forward is impossible and mapping backward is lossy, so the conformed model has to hold both and be explicit about which editions can populate which.
• Genuine additions. Consent flags, accessibility fields, a company identifier the new platform issues. These have no history and should stay null for the years before they existed, with the reason recorded.

Profile before you write any transformation

The first subsystem in the Kimball ETL architecture is not extraction. Bob Becker, writing for the Kimball Group in October 2007, lists Data Profiling as subsystem 1 and describes it as the subsystem that "Explores a data source to determine its fit for inclusion", ahead of change data capture at 2 and the extract system itself at 3. That ordering is a deliberate argument about sequence, and a backfill is where ignoring it hurts most.

Ralph Kimball and Joe Caserta's The Data Warehouse ETL Toolkit, published by Wiley in 2004, treats the back room as four operations: extracting, cleaning, conforming and delivering. A backfill exercises all four at once across sources that were never designed to agree, which is why it deserves a profiling pass of its own before any modelling starts.

The profiling pass is not sophisticated and it is not optional. For each of the 40 files, record the row count, the column list, and for every coded column the distinct values with their frequencies. An afternoon of that produces the thing you actually need, which is a table of every code that has ever appeared, the editions it appeared in, and how many rows carried it. When you find that code C accounts for 38 rows in one edition and 2,100 in another, you have learned that it means two different things and you have learned it before you built anything on top of it.

Why load newest first?

Because the value of a loaded edition is not uniform, and the order you choose decides when the project starts paying.

Load newest first and pass one covers the most recent edition of all eight shows. At that point every show in the portfolio has current data. Pass two covers the second most recent edition of all eight, and at 16 loads out of 40 every show in the portfolio has a year-on-year comparison. That is the first milestone where a commercial team can do anything, and it arrives at 40 per cent of the work.

Load oldest first and 16 loads produce five year old data for eight shows with nothing to compare it to. The same effort, the same cost, and no answer to any question anyone has asked.

There is a second reason that matters more over the life of the project. Each edition you load teaches you the next one, and the teaching is more useful in the newest-first direction. The 2026 file is closest to your target model, so the first transformation you write is the simplest one you will write. Every step backward adds one layer of difference to code that already works, which is a manageable sequence of small changes. Starting in 2021 means writing the hardest transformation first, against the least documented file, with no working model to compare against.

The mapping layer that absorbs the drift

The temptation on a backfill is to put the differences in the load, with a case expression per edition. It works for three editions and becomes unreadable at ten.

The alternative is to make the differences data. One table holds the column mapping: source system, edition, source column, target column. Another holds the code mapping: source system, edition, source code, conformed value, and a note saying who decided. The load reads those tables. Adding the 2021 edition of a show then means adding rows to two tables and running the same code.

That structure has a property worth the effort on its own. The mappings are reviewable. A show director who knows perfectly well what code C meant in 2022 can read a table of 40 rows and correct one of them, which they will never do by reading a case expression inside a SQL model. Industry and sector codes deserve the same treatment on a permanent basis, and that particular mapping is large enough to be its own table with its own owner.

Keep the raw file untouched underneath all of it. The mapping layer is a claim about what the old codes meant, and claims get revised. A backfill built on immutable raw exports can be rerun against a corrected mapping in an hour, and one that transformed on the way in cannot be rerun at all, which is also the argument for keeping the raw layer long after the load is finished.

How far back is worth loading?

The five year figure in the request is usually inherited from somewhere rather than reasoned about, and it is worth testing before you commit the last two years of effort.

Ask what decision the oldest editions support. Year-on-year growth needs two editions. A trend anyone will believe needs three or four. Cyclical behaviour in a show that moves venue every other year might need five. Beyond that, the marginal edition supports one chart that appears in one deck once a year, and it costs the same to load as the recent one that supports the rebooking model.

There is also a data quality gradient running the wrong way. The oldest exports have the most schema drift, the least documentation and the fewest people who remember them, so cost per edition rises exactly as value per edition falls. Plotting those two lines against each other, even roughly, is usually enough to turn a five year mandate into a three year backfill plus an archive of raw files that can be loaded later if anyone ever asks. The raw files cost almost nothing to keep, and keeping them means the decision stays reversible.

Budget two passes, then a third

A five year backfill gets run more than once. The first pass is a discovery exercise wearing the costume of a load, because the profiling never catches everything and the first real transformation surfaces the rest.

Plan for it explicitly. Pass one loads everything and produces a list of defects. Pass two fixes them and reloads. A third pass is common, usually triggered by someone recognising a mapped code as wrong once they see the numbers it produces. Teams that budget for one pass do not stop after one pass, they simply run the second one without having planned it, in the middle of show season.

What that repetition costs, and how to keep it bounded with partition pruning and a byte estimate before each attempt, is a separate discipline covered in historical backfill cost control.

Where this stops

Some history cannot be recovered at any price, and the honest move is to say so in the model.

If the 2022 registration platform never captured job seniority, no backfill invents it. The conformed table holds null for that edition, and every report that segments by seniority has to state which editions it covers. The failure mode is a chart that shows seniority mix moving sharply in 2023, when what moved was the number of editions that had the field at all.

The same applies to counting rules. An edition where verified attendance was computed from turnstile counts and an edition where it came from badge scans produce numbers that are both correct and not comparable. Backfilling both into one column with one name creates a trend line that nobody should trust, and the fix is a measurement method column on the edition dimension so a query can filter to comparable editions.

Start with the profiling table. Take the registration exports for the five most recent editions you can lay hands on, list every column and every distinct value in every coded column, and put it in one spreadsheet. That single artefact will tell you more about the size of the backfill than any estimate, and it is the first thing the rest of the data platform will be built against.