Write-Audit-Publish Pattern in Modern Data Pipelines

A Brief History of "Trust Me, the Data Is Fine"

ETL was the original pattern: extract data, transform it, load it into the warehouse. Then ELT flipped the order because cloud warehouses made it cheap to load raw data first and transform later. Along the way, databases gave us constraints (NOT NULL, UNIQUE, FOREIGN KEY) to catch bad data at write time.

But here is the thing. Most modern data warehouses do not enforce constraints. Snowflake has them, but they are informational only. BigQuery has them, but they are not enforced. You can declare a NOT NULL column and still insert NULLs. The safety net looks real, but it has holes.

So the question becomes: if the database will not protect you, who will?

Write, Audit, Publish

The pattern is three steps. Write: load your data into a staging area. Not the production table. A separate location where nobody is querying it yet. Audit: run your quality checks. Row counts, null checks, schema validation, business rules, whatever matters for this dataset. Publish: if the checks pass, promote the data to the production table. If they fail, stop. Alert someone. Do not let bad data through.

Simple concept. The tricky part is implementing it in a way that does not double your pipeline complexity.

The DIY Approach

Building WAP yourself with SQL: write to a staging table, run audit queries against it (row counts match expectations, no nulls in required columns, no duplicate keys, values within expected ranges), and if all checks pass, swap the staging table into production using a table rename or INSERT INTO ... SELECT.

You can orchestrate this in Airflow, Dagster, or any scheduler. The audit step is just a set of SQL queries that return pass/fail. If any query fails, the DAG stops. The production table stays untouched.

It works. It is not fancy. But it works, and you control every part of it.

Snowflake Dynamic Tables and Streams

Snowflake gives you tools that make WAP easier without building everything from scratch. Dynamic Tables automatically refresh when upstream data changes. Streams track changes to a table (inserts, updates, deletes) so you can process only the delta.

Combine these and you get a pattern where data lands in a raw table, a Stream captures the new rows, a Dynamic Table applies transformations, and your audit logic runs before the data is visible to consumers. Snowflake handles the orchestration of refreshes. You handle the quality rules. A good middle ground between fully DIY and fully managed.

Apache Iceberg: Branch, Audit, Merge

Apache Iceberg supports table branching, similar to how git works with code. You create a branch of your table, write new data to the branch, run audits against the branch, and if everything passes, merge it into the main branch. If audits fail, drop the branch. The main table never sees the bad data.

This is the cleanest implementation of WAP available today. The branch is isolated. Consumers querying the main branch see consistent data throughout. And you get time travel for free, so you can always go back to a previous state if something slips through.

The downside: you need to be running Iceberg. If you are on a traditional data warehouse without Iceberg support, this option is not available to you.