The audit lands years after the query ran
Retention mandates run to six years or more. Examination lookbacks stretch across months or years. Reconstruction deadlines, once a request arrives, run to about 72 hours. By the time an auditor asks how a number was produced, the schema has changed, the metric definition has been rewritten, and the people who could run the query have moved teams.
The penalties are not theoretical. The SEC’s off-channel communications initiative has brought charges against more than 100 firms and over $2 billion in penalties since December 2021, on the SEC’s own tally. The landmark actions are dated and specific: 16 firms and $1.1 billion on 27 September 2022, 26 firms and more than $390 million on 14 August 2024, and 12 firms and $63 million on 13 January 2025. Higher aggregate figures circulate depending on whether CFTC and FINRA actions are included, so the safe number to quote is the SEC’s own.
Point-in-time query reproducibility asks one specific question. Can you show what a query meant, what it read, and who was allowed to run it, as of a moment long since passed? Restoring a backup does not answer it. Our semantic layer buyer’s guide scores exactly this, and rates a layer highest when it can re-run a historical query and prove it used the definitions in force at that moment.
Valid time and transaction time: what was true versus what we knew
Reproducing the past precisely needs two clocks, not one. This is the core of bitemporal modeling, and the distinction between valid time and transaction time is where most audit gaps begin.
Valid time is when a fact was true in the reality you are modeling. Transaction time is when your database recorded that fact. They come apart whenever a correction is backdated. A trade booked on Monday whose counterparty is corrected in the system on Wednesday has a valid time of Monday and a transaction time of Wednesday. A record that keeps both lets you ask what was true and what the system believed, as of any instant, without overwriting the earlier version. Martin Fowler calls these two clocks actual history and record history; the terminology itself traces to Snodgrass and is carried into the SQL:2011 standard.
A plain snapshot or an audit table captures transaction time alone. It records what changed and when it was stored. It cannot distinguish a retroactive correction from a fresh fact, and it cannot answer what the organisation believed to be true on a date before the correction was entered. Bitemporality keeps both clocks, which is the difference between restoring a backup and asking, in one query, what was true and what was known at the same past instant. For the mechanics of enforcing meaning at query time, see what a semantic compiler does.
What the regulations actually require
The pull toward reproducibility is concrete, and it comes from several regimes at once. Each pairs a long retention window with a demand to reconstruct a faithful historical record.
| Regulation | Retention mandate | Reconstruction requirement |
|---|---|---|
| SEC Rule 17a-4 / FINRA 4511 | 6 years, first 2 easily accessible; WORM storage | Produce the records on examination |
| MiFID II Article 16(7) | 5 years, extendable to 7; tamper-proof | Reconstruct order communications, about 72 hours after a request |
| BCBS 239 | Traceability of risk data, no fixed year | Field-level lineage and governed critical data elements |
| SR 11-7 (Fed / OCC) | Model documentation retained | A knowledgeable third party can recreate the model |
| FDA 21 CFR Part 11 / ALCOA+ | Validated retention of records | Secure, time-stamped audit trail: who, what, when, why |
| GDPR Art. 30 / SOC 2 Type II | Processing record; 3 to 12 month control window | Reconstruct who had access to what across the period |
Banking and finance. SEC Rule 17a-4 sets a core six-year retention on non-rewriteable, non-erasable media, with FINRA Rule 4511 preserving the same format. MiFID II Article 16(7) requires recording all communications relating to client orders, with a typical reconstruction deadline of about 72 hours after a request, a figure reported by compliance vendor SteelEye. SR 11-7, the Fed and OCC model-risk guidance from April 2011, requires that a knowledgeable third party be able to recreate a model without the development code. That is a reproducibility requirement in all but name, now stressed for AI and machine-learning models. BCBS 239, published by the Basel Committee in January 2013 and applied to global systemically important banks from January 2016, demands full traceability of risk data. A January 2026 Basel Committee newsletter noted that implementation gaps persist a decade on. We covered the query-level side of this in auditable SQL for BFSI and the sector view in AI analytics for banking.
Pharma and life sciences. FDA 21 CFR Part 11 requires validated systems, access controls and secure time-stamped audit trails that capture the who, what, when and why of every change. The ALCOA+ principles, Attributable, Legible, Contemporaneous, Original and Accurate, plus Complete, Consistent, Enduring and Available, set the data-integrity bar. Between 2017 and 2022, the FDA issued more than 160 warning letters to pharmaceutical manufacturers citing data-integrity deficiencies under good manufacturing practice and Part 11.
General governance. GDPR Article 5(2) makes the controller accountable for demonstrating compliance, and Article 30 requires a record of processing activities to be produced to the supervisory authority on request. SOC 2 Type II, under the AICPA Trust Services Criteria, assesses controls over a period of typically 3 to 12 months. Its logical-access criteria CC6.1 to CC6.3 require reconstructing who had access to what across that window, and auditors sample access grants from each month. Enforcing that access at the point of compilation is the subject of fine-grained data access control.
Why existing tools miss the gap
Three categories of tooling each version one slice of the problem, and the audit needs all three at once.
| Tool category | What it versions | What it leaves out |
|---|---|---|
| Warehouse time travel (Snowflake, Delta Lake) | Historical data states | Retention-limited; no semantic definitions, no permission state |
| Git-versioned metrics (dbt, Looker, Cube) | Metric definitions, going forward | No permission state; no as-of replay against a past definition |
| Bitemporal databases (XTDB, Datomic) | Data with both valid and transaction time | Not applied to the governed semantic or metric layer |
Warehouse time travel is a short-window feature. Snowflake retains up to 90 days on higher editions, Delta Lake defaults to 30 days of log retention, and both replay data rather than meaning. Git-versioned metrics give forward consistency. Tools like dbt MetricFlow exist to standardise them after years of divergent definitions across departments. What Git cannot do is replay what a definition was on a past date, together with the permissions then in force. Bitemporal databases such as XTDB apply both clocks to records. Their own documentation frames the core use case as regulated industries answering what did you know and when did you know it. What they do not version is the governed compilation layer that turns a question into SQL.
Stated plainly: warehouse time travel versions the data. Git versions the metric definitions. Nothing versions the permission state and the compiled meaning together. That is the underserved space, and it is where a semantic control plane has to sit. Permission state is only meaningful at the moment a query is compiled and authorised, a point we develop in data authorization.
Fix the Context, Not the Model. A well-governed semantic layer that understands business context creates more reliable AI-driven analytics than fine-tuning the model itself. An audit does not ask which model wrote the SQL. It asks what the query meant and who could run it, and that lives in the context, not the weights.
Why probabilistic generation makes it worse
A generated query you cannot reproduce is an audit liability, and raw text-to-SQL on real enterprise schemas is far from reproducible. On Spider 2.0 (Lei et al., ICLR 2025, arXiv:2411.07763), 632 enterprise problems over databases that often exceed 1,000 columns, an o1-preview code agent solved 21.3%, against 91.2% on the simpler Spider 1.0. On BEAVER (Chen et al., MIT, arXiv:2409.02038), built from real private warehouses averaging 869 columns per database, off-the-shelf models score near zero end to end. Even ReFoRCE paired with Claude 4.5 Sonnet reached only 11.4% in a later evaluation, against 62.9% for the same method on Spider 2.0. If a probabilistic generator can return a different query for the same question on two different days, point-in-time reproducibility is impossible before the audit even begins. The full set of numbers, with sources, sits in our text-to-SQL benchmark.
Where Colrows fits, honestly
Colrows is a semantic execution layer that compiles a question into governed, deterministic SQL, proves the join path, and applies role, row and column rules before the query runs. Deterministic compilation is the precondition for reproducibility: the same question and the same context produce the same SQL every time, which a probabilistic generator cannot promise.
Bitemporal reproducibility of the semantic layer itself, replaying a past definition and the permission state that surrounded it as of any date, is a direction Colrows is building toward rather than a finished feature. We are honest about that line. What exists today is deterministic, governed compilation with a reproducible audit record per query. What we are building toward is a bitemporal semantic memory graph that versions meaning and permission the way a bitemporal database versions data. The governance mechanics underneath are described in governing AI agents that query enterprise data, and the wider category sits in our governance and security hub.
Any vendor claiming a finished, complete answer to point-in-time reproducibility of the semantic layer is ahead of what the field has actually shipped. The honest position is that the gap is real, the regulatory pull is expensive and dated, and the controls that can assist in meeting these requirements are being built now.
A note on the sources
Figures here carry named primary sources. Penalty totals are the SEC’s own count from its off-channel initiative, with the three landmark actions dated individually. Retention and reconstruction requirements are from the cited rules: SEC 17a-4, FINRA 4511, MiFID II Article 16(7), SR 11-7, BCBS 239, FDA 21 CFR Part 11 and the AICPA Trust Services Criteria. The bitemporal foundations are from Snodgrass, the SQL:2011 standard, Martin Fowler’s bitemporal history article, and the XTDB documentation. Benchmark scores are leaderboard snapshots and drift as evaluations update; the 11.4% BEAVER figure is specifically ReFoRCE with Claude 4.5 Sonnet from a later evaluation, not a general model ceiling. This page is not legal advice.
Frequently asked questions
What is the difference between valid time and transaction time?
Valid time is when a fact was true in the world being modeled. Transaction time is when the database recorded that fact. They differ whenever a correction is backdated. If a trade is booked on Monday but a system only records the corrected counterparty on Wednesday, the valid time is Monday and the transaction time is Wednesday. A bitemporal record keeps both, so you can ask what was true and what the system believed, as of any instant, without destroying the earlier version. The terminology is from Snodgrass and is used in the SQL:2011 standard.
How do you reproduce a query from six months ago?
You need three things preserved as of that date, not just one. The data the query read, which warehouse time travel can provide within its retention window. The metric definitions and join logic in force then, which Git can provide for code that was committed. And the permission state, meaning which roles and row or column rules applied to the caller at that moment, which most stacks do not version at all. Point-in-time reproducibility means replaying all three together against the exact question, so the answer comes out the same and can be defended.
Can Snowflake Time Travel go back more than 90 days?
Standard Snowflake Time Travel retains up to 90 days on higher editions, and Delta Lake defaults to 30 days of log retention with VACUUM removing unreferenced files after 7 days. For retention measured in years, which SEC Rule 17a-4 and MiFID II require, Microsoft Fabric guidance is explicit that you should create a full copy rather than depend on time travel. Time travel is a short-window recovery feature, not a multi-year audit substrate, and it replays data rather than the meaning of a query.
How do you prove who had access to data at a past date?
You version the permission state the same way you version the data. Most access-control systems store only the current grants, so a question like which analysts could see this row last March has no recorded answer. A bitemporal governance model records each role, row-level rule and column-level rule with its own valid time and transaction time, so an as-of query can reconstruct the exact access picture on any date. SOC 2 Type II audits sample access grants from each month of the review period, which assumes this history exists.
What does BCBS 239 require for data lineage?
BCBS 239, the Basel Committee principles published in January 2013 and applied to global systemically important banks from January 2016, requires that risk data be accurate, complete, timely and adaptable, with full traceability. In practice that means field-level lineage and governed critical data elements, so a risk figure can be traced back through every transformation to its source. A January 2026 Basel Committee newsletter noted that implementation gaps persist a decade on. Static lineage that maps structure is not the same as a re-executable, permission-aware snapshot of what a number meant.
How long must financial firms retain records?
SEC Rule 17a-4 and FINRA Rule 4511 require broker-dealer records to be kept for at least six years, with the first two years in an easily accessible place, on non-rewriteable, non-erasable storage. MiFID II Article 16(7) sets a minimum of five years, extendable to seven, with tamper-proof storage. FDA 21 CFR Part 11 and the ALCOA+ principles require secure, time-stamped audit trails for regulated life-sciences data. The retention window is years, while schemas and permissions change in weeks, which is the core of the reproducibility problem.
