Schema drift detection for the exports you did not write and cannot control

The registration export lands at 06:00 every morning and nobody looks at it. On a Tuesday in March it arrives carrying 36 columns instead of 34, the load succeeds, and nothing anywhere goes red, because the loader reads by column name and quietly ignores anything it does not recognise. Six weeks later a show director asks why the job function breakdown has been empty since March. Schema drift detection is the check that would have caught that on the Tuesday, and it costs about a day to build.

You did not write that export and you cannot control it. What you can do is treat its shape as an asset you monitor, the same way you monitor row counts and arrival times.

What drift looks like on a feed you do not own

Schema is one of the five pillars of data observability Barr Moses set out for Monte Carlo in December 2020, alongside freshness, volume, distribution and lineage. Her description of it is about knowing who changed the data and when, and she puts schema changes among the frequent causes of what she calls data downtime. On event feeds that lands exactly right, because almost every schema change you will meet was made by somebody who has never seen your reports.

The changes arrive in four shapes. A column appears, usually because the vendor added a question to the registration form. A column disappears, usually because a field was retired in a product release. A column is renamed, which your loader experiences as one disappearance and one appearance in the same file. A column keeps its name and changes its type, which is the worst of the four, because a badge id that used to be an integer and now arrives as a zero padded string will join to nothing while looking perfectly healthy.

Only one of those four is safe by default, and even that one has a condition attached.

How do you turn a schema into one number you can compare?

Take the columns in the order the file presents them, pair each name with its type, and join them into a single canonical string.

badge_id:string|registered_at:timestamp|email:string|company:string|
country:string|job_title:string|reg_type:string| ... 34 pairs total

Hash that string. Store the first 12 characters of the hash next to the load record, along with the full column list. On a 34 column registration export the string is a few hundred characters and the hash comes out as something like 9f2c1a7b40de, which is a value a person can eyeball in a log line.

The next load computes the same hash. Identical means the shape did not move and there is nothing to think about. Different means something moved, and now you diff the two stored column lists to find out what, which takes a set difference in both directions: names present today and absent yesterday, names present yesterday and absent today, and names present in both whose type changed.

Store three things per load and resist storing more. The hash, the full ordered column list as text, and the identifier of the load itself so you can find the file again. The column list is what makes the diff possible, and a hash on its own tells you something changed while leaving you to work out what by opening two files by hand at six in the morning.

The first load of any new feed has nothing to compare against, and this is where teams quietly break the whole mechanism by auto-accepting whatever arrives as the baseline. Make the first baseline a deliberate act with a person's name on the commit. A baseline that adopts itself will happily adopt a broken file, and every subsequent load will then be measured against the day the vendor's export job half failed.

The ordering matters more than it looks. If you hash a sorted list you lose the ability to notice that a vendor reordered the file, and column order is exactly what breaks a positional loader reading a CSV without headers. Great Expectations ships an expectation for this, expect_table_columns_to_match_ordered_list, whose whole description is that the columns must exactly match a specified list, with the parameter documented as the column names in the correct order. Order is part of the shape.

Which changes should stop the load?

The useful vocabulary already exists in streaming systems, and it transfers cleanly.

Confluent's Schema Registry documentation defines compatibility types that decide which schema changes are allowed and who has to upgrade first. Under BACKWARD, which its documentation names as the default, consumers using the new schema can read data produced with the last one, and the permitted changes are adding optional fields and deleting fields. Under FORWARD, data produced with a new schema can be read by consumers on the old one, and you may add fields and remove optional ones. FULL allows both, so producers and consumers can upgrade independently.

Your report queries are the consumers. Your registration vendor is the producer, and the producer will not be consulting you. So the question for every drift alert is whether the queries you already have will still return what they returned yesterday.

That gives three outcomes.

• Added column, nothing reads it. Accept and log. The one condition is that no transform in the chain does a select star into a table with a fixed definition, because then a new column at the source becomes a load failure or a silently widened table depending on your warehouse.
• Type change on any column. Stop. A widened type is usually harmless and occasionally not, and you cannot tell which from the schema alone.
• Dropped or renamed column. Stop, unconditionally, unless you have already established that nothing reads it.

The renaming case deserves the stop even when it looks harmless, because a rename is indistinguishable from a drop plus an add at the moment of arrival, and the two have opposite consequences. Grading changes by what actually reads them is a separate piece of work with its own payoff, which K23 covers.

Counting a year of drift on nine feeds

The reason to build this is that the alert volume is small, and small alert volumes get read.

Nine feeds, loading daily, gives 3,285 loads in a year. Across a year of that portfolio you might see 41 loads whose hash differed from the previous one: 29 pure additions, 7 drops or renames, 5 type changes. Those are the numbers I would expect on a portfolio where two of the nine sources are actively developed products and the rest are stable exports.

Of the 41, only 12 need a person, since the 29 additions clear themselves once the classification step confirms nothing reads the new column. Twelve interruptions in a year against 3,285 loads is 0.37 per cent, which is roughly one a month.

Compare that with the alternative, which still costs you 12 interruptions. They arrive months later, as the same 12 events found by somebody reading a report, at a point where the fix needs a backfill and an explanation. The March job function breakdown from the opening costs one afternoon to diagnose and a week of somebody's trust. The Tuesday alert would have cost four minutes.

The log is worth keeping for a second reason, which shows up at renewal. Twelve months of dated schema changes per vendor is a table with two columns, and the vendor at the top of it is the one to raise the notice period with. On the portfolio above, if 26 of the 41 changes came from one platform, that platform is generating 63 per cent of your schema work while holding perhaps a fifth of your registrations, and that ratio is the argument. Nobody in procurement can act on a complaint about data quality. A count of undeclared schema changes by quarter is a different conversation.

Where hashing stops

A hash tells you the shape moved. It cannot tell you the meaning moved, and on event data the meaning moves constantly.

The registration type column keeps its name and its type, and the vendor changes the code for a complimentary badge from COMP to CX in a release. Your file still has 34 columns, still hashes to 9f2c1a7b40de, and every query that filters on COMP now returns zero rows. Schema drift detection is blind to this by construction, because nothing about the shape changed. Catching it needs a distribution check on the set of distinct values in each categorical column, which is a different monitor with a different failure mode and a much higher false alarm rate.

The second blind spot is partial change. A vendor rolling out a platform migration show by show will send you the old shape for six of your events and the new shape for the seventh, and if you load them into one table the hash will flap between two values and look like an unstable feed. The fix is to key the baseline on the source and the event instance together, not the source alone, which is worth deciding before you have eight shows and two platforms.

The third is that a shape check says nothing about whether the numbers inside are right. A file can hash correctly and be last week's file resent, or this week's file with half the rows missing. Those are volume and freshness questions, and they need their own checks.

Start with one feed and one afternoon. Take the last thirty daily exports you already have sitting in storage, compute the ordered name and type hash for each, and count how many distinct values you get. If the answer is one, you have a stable feed and a baseline to store. If the answer is four, you have had four schema changes in a month that nobody logged, and you now know which of your reports to go and check. Either way, the next step is deciding what a mismatch does to the load, which belongs with the enforcement rules you write down in advance in K29, and getting the vendor's own change notice into your hands before the file arrives, which is the manifest and the conversation behind it in K22. Both sit under the same unified data problem: a record you can trust needs a feed whose shape you can describe.