Schema Drift Leaves a Paper Trail
Every Data-Flow Column Type Change Becomes A Dated Row You Can Query
August 7, 2026
Somewhere upstream, a DBA widens a column. CustomerName goes from 50 characters to 200 because sales kept hitting the limit. Nobody files a change request with the warehouse team, because nobody upstream remembers the warehouse team exists.
You find out weeks later, usually one of two ways. A load starts truncating, or an auditor asks when the column definition changed and everyone in the room looks at each other.
The truncation you will fix by Friday. The auditor's question is worse, because the honest answer at most shops is "we don't know." Schema drift happens between snapshots, and most teams don't keep snapshots.
Schema Drift Has No Memory
Think about what you actually have when a type change surfaces. Your own code is in version control, so you can prove what you changed and when. The source system's schema is a different story. You can query what it is right now. You cannot query what it was on March 4th.
DDL triggers only help on databases you administer, and the systems that drift the most are exactly the ones you don't: the ERP owned by another department, the SaaS export, the vendor database with its own release cycle. Comparison tools tell you two schemas differ today. None of this answers the question that matters in a governance review: what was this column's type at the time that data loaded, and when did it change?
That question needs a log, written at load time, kept somewhere you control.
The Table That Remembers
BimlFlex deploys its control framework, the BimlCatalog, into your environment as a database you own. For SSIS deployments, the catalog's ssis schema carries the execution-auditing surface, and one table in it does the remembering:
CREATE TABLE [ssis].[ColumnInfo](
[ColumnInfoID] BIGINT IDENTITY (1, 1) NOT NULL,
[PackageID] INT NOT NULL,
[LineageID] INT NOT NULL,
[ColumnName] NVARCHAR(500) NULL,
[CodePage] INT NOT NULL,
[DataType] NVARCHAR(50) NOT NULL,
[Length] INT NULL,
[Precision] INT NULL,
[Scale] INT NULL,
[EffectiveFromDate] DATETIME DEFAULT (GETDATE()) NOT NULL,
[EffectiveToDate] DATETIME NULL
);
The dating is the point. EffectiveFromDate is stamped automatically at insert, and the schema pairs it with an EffectiveToDate to complete the dated-range design. A type change over time doesn't overwrite anything. It shows up as new rows with a later date, sitting next to the old rows with an earlier one.
The table's unique index tells you how the history is keyed:
CREATE UNIQUE NONCLUSTERED INDEX [UIX_ssis_ColumnInfo]
ON [ssis].[ColumnInfo]([PackageID] ASC, [LineageID] ASC, [EffectiveFromDate] ASC);
Package, column, date. The same column can appear as many times as it was observed, but never twice at the same instant for the same package.
Five Fields, One Signature
A data type name alone doesn't identify a type. NVARCHAR(50) and NVARCHAR(200) share a DataType; they differ in Length. DECIMAL(18,2) and DECIMAL(18,4) differ only in Scale, which is precisely the kind of change that quietly reshapes financial aggregates. So the catalog records the full signature: data type, length, precision, scale, and code page.
Code page is the one everyone forgets. Two non-Unicode string columns with different code pages will round-trip characters differently, and almost no comparison tooling surfaces it. Here it's a first-class field, logged on every capture.
LineageID deserves a note too. It isn't a BimlFlex invention; it's the identifier SSIS itself assigns to a column flowing through a data-flow buffer. The catalog stores it alongside the column name, so history is anchored to the engine's own notion of a column, with the human-readable name kept next to it.
How the Rows Get There
At run time, the executing package logs its data-flow column metadata through the catalog procedure ssis.LogColumnInfo. The call passes the run's execution id plus a table-valued parameter of column metadata, one entry per column: lineage id, name, code page, data type, length, precision, scale.
The procedure then does something quietly sensible: it looks up which package the execution belongs to from bfx.Execution rather than trusting the caller to say so. Column history lands on the same identity spine the catalog already uses for executions, errors, and the <a href="https://www.varigence.com/blog/row-level-reconciliation-audit-evidence-bimlflex-writes-to-your-own-database">row-level reconciliation evidence</a> written during loads. One package identity, several kinds of audit hanging off it.
And the log is append-only. The procedure records what the run saw, stamped with the current date. It does not decide at write time what counts as a change. That keeps the write path simple and the record trustworthy: nothing gets interpreted before it gets stored. Collapsing repeated observations into distinct signatures is a query-time job, and a cheap one.
Querying the Paper Trail
This is your database, so the paper trail is a SELECT away. Distinct type signatures per column, with the window each was observed:
SELECT ColumnName, DataType, Length, Precision, Scale, CodePage,
MIN(EffectiveFromDate) AS FirstSeen,
MAX(EffectiveFromDate) AS LastSeen
FROM ssis.ColumnInfo
WHERE PackageID = @PackageID
GROUP BY ColumnName, DataType, Length, Precision, Scale, CodePage
ORDER BY ColumnName, FirstSeen;
A column that never drifted returns one row. CustomerName returns two: length 50, first seen in January, last seen March 3rd; length 200, first seen March 4th. That is the answer to the auditor's question, with timestamps, produced from a table in your own catalog.
Flip it around to hunt for drift you haven't noticed yet:
SELECT PackageID, ColumnName,
COUNT(DISTINCT CONCAT(DataType,'|',Length,'|',Precision,'|',Scale,'|',CodePage)) AS Signatures
FROM ssis.ColumnInfo
GROUP BY PackageID, ColumnName
HAVING COUNT(DISTINCT CONCAT(DataType,'|',Length,'|',Precision,'|',Scale,'|',CodePage)) > 1;
Every row is a column whose type signature changed at some point in its logged life. Wire that into the kind of <a href="https://www.varigence.com/blog/testing-observability-as-code-part-2-runtime-checks-logs-and-triage">scheduled runtime checks</a> you already run against the catalog, and drift stops being a forensic exercise you start after something breaks. It becomes a standing query that flags the change the day it appears.
Evidence You Own
We've written before about <a href="https://www.varigence.com/blog/data-lineage-best-practices-achieving-visibility-without-manual-work">data lineage best practices</a>, and that piece is mostly about design-time lineage: where a column comes from and where it goes, derived from the model. This table is the runtime companion. Lineage answers "where did this column come from." Column history answers "what was this column, and when did that stop being true."
Both answers come out of metadata you hold. The BimlCatalog schema deploys into your SQL Server from a DACPAC, or into <a href="https://www.varigence.com/blog/postgres-bimlcatalog-control-framework-you-own">the PostgreSQL BimlCatalog</a> if that's your control-plane platform. There is no vendor portal to export from and no retention policy you didn't set. When someone asks what CustomerName looked like on March 4th, the answer is a dated row, and the database it lives in is yours.
