Systems and records
Dataset schema drift acceptance when two years hold three schemas
By SourceX Editorial · Updated
10 sections
Short answer
To reconcile several schemas used over two years, keep each era visible instead of flattening it: list every schema era with its dates and cause, map each era's fields to one canonical schema by meaning rather than by name, tag every record with its era, and validate each era separately for counts, codes, timestamps and nulls. Acceptance should fail on unmapped meaning, not only on files that will not open.
Checklist
Dataset schema drift
Key takeaways
- Keep schema eras visible: register each one with dates, cause and record counts.
- Map fields to canonical identifiers by meaning; never match on a reused name.
- Tag every record with its era and keep original values beside mapped ones.
- Use distinct markers for not collected, not applicable and unknown.
- Check time zones per era before any other conversion.
- Block acceptance on defects that change what a model learns, even if files load.
How should a dataset reconcile several schemas used over two years?
Treat the history as several datasets that share a purpose, and reconcile them on purpose. Build a register of schema eras, define one canonical target schema, write a crosswalk from each era to that target, keep an era tag on every record, and run the acceptance checks per era before combining them.
The aim is not to make old records look new. A buyer can work with a field that only exists from the second era onward, or a priority scale that changed halfway through, as long as the change is documented and the records say which side of it they sit on. What a buyer cannot work with is a column whose meaning silently changed while its name stayed the same.
This applies to a single historical delivery covering a period in which the source system changed. Handling schema changes in future deliveries of a recurring feed is a related but separate problem, covered in the recurring-delivery playbook linked below.
Why a dataset that loads cleanly can still fail acceptance
Most schema tooling checks structure, not meaning. Delta Lake's documentation, for example, describes write-time validation in terms of column existence, data types and names that differ only by case. A column called category that held product lines for sixteen months and customer segments for eight would pass every one of those checks.
Format-level schema resolution has the same limit. The Apache Avro specification resolves a writer's schema against a reader's schema by matching record fields by name, ignoring writer fields the reader does not know, and filling missing fields from the reader's default value, or signaling an error when there is no default. That is a sound rule for reading bytes; it says nothing about whether a default of none is true for records created before the field existed.
So acceptance for multi-era data needs checks a parser cannot make: did the field exist in this era, did its codes mean the same thing, was the time zone the same, and does a null mean not collected, not applicable or unknown.
Start with a schema era register
An era is a date range during which the export's structure and meanings were stable. List every era before mapping a single field, with the evidence that dates it, because every later check is run per era.
At a glance
Start with a schema era register
Good evidence includes release notes from the vendor, admin audit logs showing field or picklist edits, migration project records, and a simple profile of the data itself: the first and last date on which each field is populated usually shows an era boundary within a day or two.
- Era identifier, such as A, B and C, used as a column on every record.
- Start and end timestamps, with the rule for records that straddle the boundary.
- Source system and version, and whether the era came from a migration.
- The change that opened the era: field added, field renamed, picklist changed, system replaced.
- Evidence for the dates and the person who confirmed them.
- Record count per era, so later checks have a reconciliation target.
The schema drift mapping and validation checklist
Run every check per era and record the result in the delivery's acceptance log. A failure in one era does not have to block the whole delivery if that era can be excluded or delivered with a documented limitation, but it must be visible.
| Check | What to compare across eras | Pass condition | Failure example |
|---|---|---|---|
| Era coverage | Era register dates against the record timestamps | Every record falls in exactly one era; no gaps or overlaps | Records dated in the two weeks of a migration appear in neither era |
| Row and case totals | Counts per era in the source against counts per era in the delivery | Totals match, or every difference appears in the exception log | Era A loses records that were archived before the migration |
| Field identity | Each canonical field against the source field it came from in each era | One documented source field, or a documented absence, per era | A reused field name maps two meanings to one canonical field |
| Code sets | Picklist and status values per era against the canonical code list | Every source value maps to one canonical code or to an explicit unmapped code | A new status value in era C is dropped as null |
| Scale changes | Ordinal fields such as priority or severity per era | Each era keeps its original value plus a mapped value with the rule documented | A three-level scale is stretched onto four levels without a rule |
| Timestamps | Time zone and precision of each timestamp field per era | All canonical timestamps in one stated zone; original zone recorded | Era A local times are labeled as UTC, shifting every record by hours |
| Missing values | How each era represents not collected, not applicable and unknown | Distinct canonical markers for each meaning | Fields that did not exist before era B are filled with a default that looks real |
| Keys and links | Record identifiers and links to parent records across a migration | Old and new identifiers both kept; links resolve in every era | Comments from era A point to ticket numbers renumbered in era C |
| Attachments | Attachment references against delivered files, per era | Every reference resolves or is logged as missing | Attachments from the retired system were never exported |
| Outcome labels | Distribution of outcome fields per era | Shifts are explained by a documented change, not by mapping | Resolved rate jumps at the era boundary because a status was remapped |
Map fields by identity, not by name
Give every canonical field a stable identifier and map each era's source field to that identifier, with the source name kept as metadata. Names are labels that people change; identity is what the field measures.
Table formats built for long-lived data take the same approach. Apache Iceberg tracks each column with a unique ID and warns that formats tracking columns by name can inadvertently un-delete a column if a name is reused, while formats tracking by position cannot drop a column without changing other names. Delta Lake's column mapping assigns every column a physical name and a unique integer ID so columns can be renamed or dropped without rewriting data.
Protocol Buffers makes the cost of reuse explicit: its language guide says field numbers should never be reused, and that decoding a field with a different definition than it was written with can lead to parse errors, data corruption or leaked personal information. A business export that reuses a field name for a new meaning is the same defect, one level up, and the crosswalk is where you prevent it.
Codes, defaults and missing values across eras
Most drift in operational systems is in values, not columns: picklists gain and lose options, statuses are merged, and a priority scale is redesigned. Map every source value to a canonical code, and keep the original value beside it so a buyer can re-map if they disagree with your rule.
Defaults deserve special care. The Avro specification fills a missing field from the reader's default and, for enums, substitutes the reader's default symbol when the writer's symbol is unknown, so a careless default quietly converts unknown into a real-looking value. In a historical delivery, a field that did not exist yet should carry a marker meaning not collected, not a plausible value.
The Frictionless Table Schema standard offers a vocabulary for this: missingValues lists the strings that should be read as missing, categories restricts a field to a finite set of values, and primaryKey declares the fields that identify a row. Declaring these per era in the delivery's documentation turns the crosswalk into something a buyer can validate rather than trust.
Timestamps: the drift that shifts every record
Check the time zone of every timestamp field in every era before anything else, because a zone error moves every record and can push events across day, month or era boundaries. Migrations often change it: an old system stored local wall-clock time and the new one stores UTC.
The Iceberg table specification draws the distinction precisely: a timestamp without time zone represents a date and time of day regardless of zone, while a timestamp with time zone represents a point in time stored as UTC without retaining the source zone. Decide which one each canonical field is, convert each era accordingly, and keep the source zone in the era register so the conversion can be audited.
Worked example: one helpdesk, three schemas in two years
Illustrative example only: the company, fields and values are invented to show the method and are not customer data. A software company exports support tickets from October 2024 to September 2026. Era A runs to March 2025 on the original helpdesk; era B starts when a product area field is added and priority is renamed severity on a four-level scale; era C starts in February 2026 after a move to a new helpdesk that stores UTC and reuses the name category for customer segment.
A valid canonical record from era A reads: ticket_id A-10442, legacy_id 10442, schema_era A, created_at 2025-01-14T15:02:00Z converted from local time, severity_original high, severity_canonical S2, product_area not_collected, product_line from category Billing, segment not_collected. Every absent field carries a marker, every converted value keeps its original.
A failing record from era C reads: ticket_id 88310, schema_era missing, created_at 2026-03-02T09:15:00 with no zone, severity_canonical S2 with no original, product_line Enterprise. It fails four checks at once: no era tag, an unzoned timestamp, a mapped value without its source, and a customer segment loaded into the product line field because the crosswalk matched on the name category.
| Canonical field | Era A (Oct 2024 to Mar 2025) | Era B (Apr 2025 to Jan 2026) | Era C (Feb 2026 to Sep 2026) | Crosswalk rule |
|---|---|---|---|---|
| product_line | category | category | Not present; category now means segment | Map by meaning; era C gets not_collected |
| segment | Not present | Not present | category | Era C category maps here, never to product_line |
| severity | priority: low, normal, high | severity: S1 to S4 | severity: S1 to S4 | Keep original; map high to S2, normal to S3, low to S4 as documented |
| product_area | Not present | product_area | product_area | Era A gets not_collected |
| created_at | Local time, no zone | Local time, no zone | UTC | Convert A and B using the account's recorded zone; store UTC and source zone |
| ticket_id | Numeric, old system | Numeric, old system | New numbering after migration | Keep legacy_id; prefix era to avoid collisions |
Which defects should block acceptance even when every file opens?
Block, or deliver only with a written limitation, when the defect changes what a model would learn from the records rather than only how they load. These are the cases where a buyer could train on the data, get plausible results and only later discover they were wrong.
- A reused field name mapped to one canonical field across eras with different meanings.
- Outcome or status distributions that shift at an era boundary because of mapping, not because of the business.
- Timestamps in mixed zones within one canonical field.
- Defaults that turn not collected into a real-looking value.
- Era totals that do not reconcile with the source and have no exception entry.
- Broken links between records across a migration, such as comments orphaned from renumbered tickets.
Presenting a multi-era history to a licensing review
A history with documented eras is usually more useful than a shorter history with one schema, because buyers look for years of consistent records and the era register shows exactly where consistency holds. Deliver the register, the crosswalk and the per-era acceptance results together, and be explicit about eras you would rather exclude.
SourceX helps businesses turn selected records into licensed datasets, including the preparation work that reconciles exports like these. Since buyers usually look for at least two years of steady history from one system, an era register that shows where the history holds steady makes that check quicker. Before any export is shared, a short assessment tells a company whether its multi-era history qualifies.
Frequently asked questions
What is schema drift in a historical dataset?
It is any change over time in a dataset's structure or meaning: fields added, removed or renamed, picklist values changed, time zones switched or identifiers renumbered. In a historical export it usually follows system upgrades, admin edits and migrations, and it is often invisible in the files themselves.
Should older eras be converted to the newest schema?
Map them to one canonical schema, but keep the original values and an era tag on every record. Converting without keeping the source loses information a buyer may need to re-map, and hides where a rule was a judgment call.
Can a field that only exists in later eras be used for training?
Yes, if the earlier records carry an explicit not collected marker rather than a default. Buyers can then train on the subset that has the field or treat its absence as a feature, which is impossible if a default made the old records look complete.
How do you find era boundaries if nobody documented the changes?
Profile the data: plot when each field is first and last populated, when new code values first appear and when identifier formats change. Confirm those dates against admin audit logs or migration records where they exist, and note any boundary you could only infer.
Is a per-era validation report enough for buyer acceptance?
It is the core of it, alongside row totals reconciled to the source and an exception log. The buyer will still apply its own acceptance criteria, but per-era results let it decide quickly which eras meet them and which need a limitation.
Sources
- Iceberg tracks columns by unique ID; formats that track by name can un-delete a column when a name is reused, and position-based formats cannot drop columns without changing names. Source
- The Iceberg spec distinguishes timestamps without time zone (a wall-clock date and time) from timestamps with time zone (a point in time stored as UTC without the source zone). Source
- Avro schema resolution matches record fields by name, ignores unknown writer fields, uses the reader's default for missing fields or signals an error, and uses an enum default for unknown symbols. Source
- Delta Lake column mapping gives every column a physical name and a unique integer ID so columns can be renamed or dropped without rewriting data. Source
- Delta Lake's write-time schema validation checks that columns exist in the table, data types match and names do not differ only by case. Source
- The proto3 guide says field numbers should never be reused, because decoding with a different definition can cause parse errors, data corruption or leaked PII. Source
- Frictionless Table Schema defines missingValues, categories for finite value sets and primaryKey for identifying fields. Source
See if your company qualifies
A short company assessment. No data uploads are needed.