Snowflake Transactions for Atomic Publication

Why a publish step needs a transaction

A batch publication typically changes several tables: customer history, facts, a batch ledger and watermarks. If it stops halfway, dashboards show partial data and the watermark may claim work that never landed. Wrap the business changes in one explicit transaction:

BEGIN TRANSACTION;
 
  -- 1. idempotency: has this batch already committed?
  -- (runner checks batch_ledger and exits with ALREADY_PUBLISHED if so)
 
  -- 2. close changed customer versions, insert new ones
  UPDATE dim_customer d SET valid_to = :upper, is_current = FALSE
  FROM stg_customers s
  WHERE d.customer_id = s.customer_id AND d.is_current AND d.attr_hash <> s.attr_hash;
 
  INSERT INTO dim_customer (...) SELECT ... FROM stg_customers s WHERE ...;
 
  -- 3. facts
  MERGE INTO fact_sales t USING stg_sales s
    ON t.order_id = s.order_id AND t.line_id = s.line_id
  WHEN MATCHED THEN UPDATE SET ...
  WHEN NOT MATCHED THEN INSERT (...) VALUES (...);
 
  -- 4. control state
  INSERT INTO batch_ledger (batch_id, upper_bound, committed_at) VALUES (:batch, :upper, CURRENT_TIMESTAMP());
  UPDATE watermarks SET committed_upper = :upper WHERE pipeline_id = :pipeline;
 
COMMIT;

Gotchas

• DDL auto-commits. A CREATE TABLE inside your "transaction" commits everything before it. Create staging tables before BEGIN.
• A matched-row MERGE update is Type 1, not SCD2. History needs a close-and-insert step.
• Statement errors do not always roll back automatically. Have your runner issue ROLLBACK on any failure and verify the session state.
• Lost responses happen. If the client times out after sending COMMIT, check the ledger and QUERY_HISTORY by query ID before retrying — don't blindly resubmit.

What it does not give you

Files written to S3, notifications and BI refreshes are outside the database transaction. This is atomic within Snowflake, not distributed exactly-once.