If you think your data warehouse’s "point-in-time" restore is sufficient for a financial audit, you are one bad join away from a regulatory nightmare.
We have all seen it: the auditor asks for the state of a loan portfolio as it existed on the 14th of last month at 2:00 PM. You point them to your Snowflake AT clause or your Databricks AS OF timestamp, and you think you’ve won. Then they ask for the schema definition, the underlying transformation code version, and the proof that the data wasn't mutated by a rogue UPDATE statement before the snapshot was taken. Suddenly, your "Time Travel" feature isn't a silver bullet; it’s just a way to look at a point in history without knowing if the history itself was forged.
When you’re dealing with FINRA, HIPAA, or Basel III requirements, "it’s in the database" is not an audit trail. It’s a liability.
The contenders
You are choosing between two fundamentally different architectures. The first is Database-Native Versioning, which relies on the built-in temporal capabilities of modern cloud data warehouses like Snowflake (Time Travel) or Databricks (Delta Lake Time Travel). It’s the "easy" path.
The second is Event-Sourced Immutable Logs, where you treat your data lake like a ledger. You never update a row; you only append changes. This is the "hard" path, usually involving Apache Iceberg or Hudi sitting on top of S3, combined with a strictly enforced immutable schema registry.
Photo by Kelly Sikkema on Unsplash
The burden of operational maintenance
Database-native versioning feels free until you hit the storage bill. If you have a high-churn table—say, a daily interest calculation engine—and you set your Snowflake DATA_RETENTION_TIME_IN_DAYS to 90 days, you aren't just paying for the current state. You are paying for every single block change generated by every MERGE statement. In a high-volume financial environment, this adds 30% to 50% to your monthly storage costs.
More importantly, the failure mode is silent. If your retention period expires, the data is gone. There is no recovery. When the auditor comes knocking 91 days later, you’re explaining why your "immutable" history has a hole in it.
Event-sourced logs are more work to build, but they are operationally safer. By using Apache Iceberg with a long-term object storage policy (S3 Intelligent-Tiering), you store the raw truth as a series of immutable Parquet files. You aren't "relying" on a database engine's internal retention counter. You own the files. If you need to re-process an audit from three years ago, you point your engine at the manifest files. It’s slower to query, but it’s mathematically impossible to accidentally "expire" the evidence.
Reproducibility and the cost of truth
Let’s talk about the "reproducible report" trap. If you run a report today and get $10M in risk exposure, can you run the same query tomorrow and get the exact same number? In a standard warehouse, maybe. But if your underlying dimension tables were updated—even if you have time travel—your join logic might pull in a different version of a "customer status" flag than what was visible during the original run.
To solve this, I’ve moved away from standard SQL views for regulatory reporting. Instead, we use "Snapshot Tables." Every time a critical report is generated, we write the input dataset—the exact subset of the ledger used for that calculation—into a dedicated audit_snapshots schema.
The cost here is compute, not storage. You’re essentially doubling the amount of data you move through your ETL pipeline. In a production environment with a 20-node Databricks cluster, this adds up. But when you’re arguing with a regulator about why an interest payment was calculated at 4.2% instead of 4.3%, having the exact input state stored as a snapshot is the difference between a "minor finding" and a "cease and desist."
Photo by Marjan Blan on Unsplash
Failure modes and the "oops" factor
The biggest failure mode in database-native time travel is the DDL change. If someone drops a column or renames a table in Snowflake, your ability to "time travel" back to a previous state can become extremely brittle. I once watched a junior engineer rename a column in a production table on a Friday afternoon. The AS OF query failed because the underlying table schema no longer matched the historical metadata. We lost four hours of audit availability while we scrambled to rebuild the schema from the information schema history.
Immutable logs don't care about DDL changes in the same way. Because you are versioning the files, not just the rows, you can use schema evolution tools like Iceberg’s add-column or rename-column without breaking the ability to read the historical data files. You decouple the data from the query engine's current state.
If you're using a standard SQL database, your biggest enemy is the UPDATE or DELETE statement. Even with Time Travel, you are one TRUNCATE away from a bad day. If you don't have explicit RESTRICT policies on your production schemas, it’s not a matter of if someone deletes history, but when.
What I'd pick, and why
If you are a startup or a small shop, stay in the Snowflake/Databricks sandbox. Use their Time Travel features, but for the love of all that is holy, set your retention to the maximum allowed by your tier and put an explicit ALERT on storage costs so you don't get blindsided by the bill.
But if you are operating in a regulated space—banking, fintech, or healthcare—you need to move to an Iceberg-based architecture.
Here is my recommendation: Use a "Medallion Architecture" where your Silver layer is strictly append-only. No updates. No deletes. If a record needs to be corrected, you insert a new record with a valid_from and valid_to timestamp. This turns your entire data platform into a slowly changing dimension (SCD Type 2) ledger.
The caveat? It makes your query code significantly more complex. Your developers will have to write WHERE CURRENT_TIMESTAMP BETWEEN valid_from AND valid_to on every single join. They will hate it. They will complain about performance.
Tell them they are free to complain to the auditors when the system fails to account for a change in a customer's risk profile during a mandatory review. The performance tax is the cost of compliance. If you aren't paying it in code, you're paying it in fines.
Tags: #data #engineering #finance #audit
Cover photo by Albert Stoynov on Unsplash.
Top comments (0)