# Snowflake Transactions for Atomic Publication

> How explicit transactions, DDL auto-commit and MERGE semantics affect a publish step that must update facts, history and watermarks together.

- Source: https://akshaythakare.com/notes/snowflake/snowflake-transactions
- Topic: Snowflake
- Level: advanced
- Reading time: 3 min
- Tags: snowflake, transactions, scd2, reliability
- Updated: 2026-10-11

## 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:

```sql
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.