The Audit Trail Lives in Your Database, Not a PDF
Per-Load Row Counts And Control Totals, Queryable In Tables You Own
June 19, 2026
Ask most data teams to prove a load was correct and you get a screenshot. A row count from the source system, a row count from the target, both pasted into a ticket, both true on the day they were captured and unverifiable forever after. When an auditor asks the same question six months later, nobody can reconstruct it. The numbers were never stored anywhere you could query them.
That is the gap between "we have logging" and "we have an audit trail." Logging tells you a job ran. An audit trail lets someone who wasn't in the room reconstruct exactly what moved, count it independently, and reconcile it against the source. The difference is whether the evidence is durable and queryable, or whether it scrolled past in a log window and is gone.
This is a governance problem that does not get solved by a dashboard. A dashboard reads evidence; it does not create it. If the load did not write down what it did, in a place you can run SQL against, there is nothing for the dashboard to show and nothing for the auditor to check. So the real question is not "do you have observability." It is "where does the reconciliation evidence physically live, and can you join it." We have argued before that lineage should be generated, not mapped by hand after the fact; this is the same principle one level down, at the grain of a single load.
The evidence has to be a row, not a log line
Here is the distinction that matters. A log line is prose: "Loaded 48,213 rows into Customer." You can read it, but you can't sum it, group it, diff it against last night, or join it to the source count. A row in a table is evidence: it has a key, a type, a number, and a timestamp, and it sits next to every other load's row so you can compare them.
BimlFlex takes the second path. Every execution writes its audit facts into a small set of tables that ship with the BimlCatalog database you deploy and own. You run the loads; the loads populate the audit tables; the tables stay in your database under your retention policy. Nothing leaves your environment, and nothing depends on a vendor runtime staying online to read it later. That ownership posture is the same one that makes regenerating to a new target a property of the metadata, not a migration project: the artifacts are yours, in your tenant, on your stack.
What gets written, at three grains
The audit tables capture reconciliation evidence at three levels of detail, all keyed to the same execution so you can move between them.
Row counts, per object, per load. A control table records, for each component and target object in a run, how many rows it handled and what kind of count it was:
CREATE TABLE [bfx].[RowCount] (
[RowCountID] BIGINT IDENTITY (1, 1) NOT NULL,
[ExecutionID] BIGINT NOT NULL,
[ComponentName] NVARCHAR(200) NOT NULL,
[ObjectName] NVARCHAR(200) NOT NULL,
[CountType] NVARCHAR(20) NOT NULL,
[RowCount] INT NOT NULL,
[ColumnSum] DECIMAL(38, 4) NULL,
[ColumnName] NVARCHAR(500) NULL,
[AuditDate] DATETIME DEFAULT (GETDATE()) NULL
);
Two columns on that table do the heavy lifting. CountType lets one load record more than one number for the same object, so a source count and a target count for the same table can sit side by side and be subtracted. ColumnSum, a DECIMAL(38,4), is the column control total: pick a numeric column, and the load records its running sum. A row count catches a missing row. The control total catches the subtler failure, the one where the count matches but a value got truncated, defaulted, or silently coerced on the way in. Matching counts reconcile to zero. The control total is what proves the money actually added up.
Captured rows, column by column. When you need more than a count, the audit captures the actual row content. One table holds the header for a captured set, and a child table holds it broken out to the individual cell:
CREATE TABLE [bfx].[AuditRow] (
[AuditRowID] BIGINT IDENTITY (1, 1) NOT NULL,
[ExecutionID] BIGINT NOT NULL,
[ComponentName] NVARCHAR(200) NOT NULL,
[ObjectName] NVARCHAR(200) NOT NULL,
[AuditType] NVARCHAR(20) NOT NULL,
[RowCount] INT NULL,
[AuditRowSchema] XML NOT NULL,
[AuditDate] DATETIME DEFAULT (GETDATE()) NULL
);
CREATE TABLE [bfx].[AuditRowData] (
[AuditRowID] BIGINT NOT NULL,
[RowID] INT NOT NULL,
[ColumnName] NVARCHAR(128) NOT NULL,
[ColumnValue] NVARCHAR(4000) NULL
);
The AuditRowData shape, one row per cell with a column name and its value as text, is what makes a captured row queryable without knowing the source table's schema in advance. A load can capture the rows it rejected, the rows it inferred, or a sample of what it wrote, and you can read them back later by column name with a plain join. The AuditType label on the header says which kind of capture it was, so error captures and informational captures do not get confused for each other.
The execution they all hang off. Every count and every captured row carries an ExecutionID, and that id is the spine of an execution record that knows its own parentage, its start and end time, and a persisted Duration. Batch runs and the child loads underneath them are distinguishable, so "this number came from last night's full batch" is a fact you can filter on, not an assumption you have to make. This is the same execution backbone behind orchestrating mapping data flows through BimlCatalog; the audit tables are what that orchestration leaves behind as evidence.
Reading it back is one query
Because the evidence is rows keyed to an execution, reconciliation is a query you write once and keep. Take a single run, pull its counts, and pivot the source and target CountType against each other:
SELECT
rc.ObjectName,
SUM(CASE WHEN rc.CountType = 'Source' THEN rc.[RowCount] END) AS SourceRows,
SUM(CASE WHEN rc.CountType = 'Target' THEN rc.[RowCount] END) AS TargetRows,
SUM(CASE WHEN rc.CountType = 'Source' THEN rc.[RowCount] END)
- SUM(CASE WHEN rc.CountType = 'Target' THEN rc.[RowCount] END) AS RowDelta,
SUM(CASE WHEN rc.CountType = 'Source' THEN rc.ColumnSum END)
- SUM(CASE WHEN rc.CountType = 'Target' THEN rc.ColumnSum END) AS SumDelta
FROM bfx.[RowCount] rc
WHERE rc.ExecutionID = @ExecutionID
GROUP BY rc.ObjectName
HAVING SUM(CASE WHEN rc.CountType = 'Source' THEN rc.[RowCount] END)
<> SUM(CASE WHEN rc.CountType = 'Target' THEN rc.[RowCount] END)
OR SUM(CASE WHEN rc.CountType = 'Source' THEN rc.ColumnSum END)
<> SUM(CASE WHEN rc.CountType = 'Target' THEN rc.ColumnSum END);
That returns only the objects in a run where the source and target disagree, by row or by control total. Run it after every batch and it is a reconciliation report. Run it across a date range and it is a trend. Hand it to an auditor and it is reproducible evidence they can execute themselves against your database, which is the only kind that survives the meeting. The product ships report procedures over these same tables so the monitoring view and your own query read identical numbers, because they read the same rows.
Why generating the trail beats bolting it on
The reason hand-built reconciliation drifts is that it is optional at write time and mandatory at read time. The pressure to ship a pipeline is real and immediate; the audit logging is the part that gets stubbed out "for now" and never filled in, because nothing breaks when it is missing. You find the gap the day someone asks the question, which is the worst possible day to find it.
When the audit write is generated from the same metadata that generates the load, it is not optional and it is not a separate task. Define the object, generate the pipeline, and the count logging, the control total, and the row capture come with it, identical in shape across every target you generate for. The reconciliation table looks the same whether the load ran in SSIS, Azure Data Factory, or against a Fabric or Snowflake target, because the same metadata emitted it. That consistency is the whole point: an audit trail is only useful if every load writes it the same way, and the only reliable way to get that is to stop asking humans to remember. Embedding the contracts and gates at design time rather than after the fact is the same instinct applied earlier in the pipeline; the audit tables are where that discipline leaves a durable record.
Audit-ready is not a binder you assemble before a review. It is a property of how the data got there: every load wrote down what it did, in your database, in a shape you can query, the moment it happened. The screenshot in the ticket was never the evidence. The rows are.
