Why the Same UPDATE Means Something Entirely Different in Teradata and Snowflake

DWH Pro Admin

October 5, 2025

minutes reading time

At first glance, an UPDATE looks universal.
In reality, it’s one of the most misleading similarities between Teradata and Snowflake.
The SQL is the same, but the storage, logging, recovery, and performance mechanics are completely different.

If you’re migrating from Teradata to Snowflake (or running both), understanding these differences prevents slow jobs, unnecessary costs, and avoidable outages.

1) Teradata in a nutshell: mutable blocks + journals

Teradata performs in-place updates on data blocks:

The AMP locks target rows.
Before-images are written to the Transient Journal (TJ) for rollback.
The in-memory block is modified.
Write-Ahead Logging (WAL) records the redo before the modified block is flushed.
On COMMIT, durability is guaranteed; on ROLLBACK, the TJ provides logical undo.

Block growth: if an updated row no longer fits its block, Teradata performs a block split (and may later merge underfilled blocks).
When the Transient Journal grows rapidly, it consumes perm space inside the DBC database.
If DBC becomes full, Teradata aborts and rolls back all active transactions system-wide until space is freed — a well-known failure scenario that must be monitored carefully.

Why this matters for performance

Updates that increase row length can be expensive (read-modify-write + split).
Concurrency is governed by locks; contention on the same AMP or rowhash can slow everything.
Spool and journal space must be managed (and failures here can stop the system — see “Operational risks” below).
DBAs can mitigate split frequency with FREESPACE settings to leave headroom in blocks.

2) Snowflake in a nutshell: immutable partitions + metadata commits

Snowflake never edits data “in place.” Every DML (including UPDATE) is copy-on-write:

The engine reads the affected micro-partitions.
It writes new partitions containing the changed rows.
A metadata transaction atomically switches table pointers from old to new partitions.
Old partitions remain available for Time Travel (retention depends on table type/settings). Fail-safe applies only to permanent tables.

No WAL to replay. No TJ to undo. No block splits.
Data files are immutable; transactions are metadata.

Why this matters for performance

An UPDATE is effectively a small rebuild of the touched partitions.
Frequent tiny updates create lots of partition churn; batching is your friend.
Pruning / clustering determines how many partitions get rewritten, not “row growth.”
Automatic services may recluster data later, still using copy-on-write — never in-place merges.

3) Durability & recovery — the mental model shift

Teradata:

Updates occur in place on mutable data blocks.
Rollbacks depend on the Transient Journal (TJ) for logical undo.
Crash recovery uses the Write-Ahead Log (WAL) + TJ to restore consistency.
When rows grow, Teradata may perform block splits or merges.
Post-commit undo requires manual restore or reprocessing.

Snowflake:

Updates are copy-on-write: new partitions are written, and metadata swaps the pointers.
Rollback simply discards the uncommitted metadata snapshot — no physical undo.
No user-visible log replay is needed; the metadata service is authoritative, and uncommitted changes never become visible.
Row growth is irrelevant, since data is immutable.
Post-commit undo is handled by Time Travel, which can restore previous table versions.

How Snowflake rollbacks actually work

Each transaction creates a candidate snapshot in the metadata service.
DML operations write new partitions to cloud storage.
COMMIT flips the active metadata pointers to the new version (atomic).
ROLLBACK discards the candidate snapshot and keeps the old version active.

Because data files are immutable, rollback in Snowflake is instantaneous — no log replay, no journal cleanup, and no block rewriting.

4) Performance implications you’ll feel on day one

Teradata

High-touch updates or updates that increase row size can be pricey (I/O + possible splits).
Contention on the same AMPs or rowhashes can serialize updates and reduce throughput.
Spool and TJ space must be monitored closely.
Single-AMP updates (PI-based) perform best; all-AMP updates can stress journaling and spool.

Snowflake

Partition rewrite cost dominates; small, scattered updates are inefficient.
Batching (MERGE / CTAS patterns) usually beats row-by-row updates.
Pruning / clustering determines how much gets rewritten — more pruning means fewer partitions touched.
Limiting projection reduces scan and CPU, but any partition containing updated rows will still be rewritten.

Rule of thumb:
• On Teradata, optimize for AMP distribution and journal pressure.
• On Snowflake, optimize to touch fewer partitions and batch writes.

Write-path latency: SSD vs Object Storage

Teradata writes modified data blocks to a local SSD-based file system, providing extremely low-latency and predictable I/O once the data block is locked and cached.
Snowflake, by contrast, persists new micro-partitions to cloud object storage (S3, Azure Blob, GCS), which has higher write latency than local SSDs. Modern cloud providers offer strong consistency, but latency remains higher than a local flush.

At the physical layer, Teradata’s block flush is faster — but Snowflake’s architecture compensates through:

Asynchronous, parallelized writes across many partitions.
Append-style, highly parallel object writes — no in-place overwrites.
Massive horizontal scaling that hides object-store latency.

So while an individual write operation may take longer in Snowflake, overall throughput remains high due to parallelism and immutability, and recovery is simpler because no journal replay or block rewriting is required.

5) Operational risks — what breaks in practice

Teradata’s classic failure mode: TJ fills DBC

You’ve probably seen it: the TJ (stored under database DBC) grows until DBC perm space is full. Result:

New transactions can’t allocate journal space.
In-flight work aborts and rolls back.
You get a rollback storm that can take ages to clear.

Snowflake’s analogue? There isn’t one

Snowflake has no transient journal, no WAL replay, and no DBC-like user quota for transaction state.
Rollback is metadata; uncommitted work simply never becomes visible.

The only thing that can “grow”: storage from Time Travel history.

Lots of DML creates many new partitions.
Old versions remain until retention expires (default is short).
This is a cost consideration, not a failure mode — no system-wide block, no rollback storm.
Mitigate by reducing retention on transient/staging tables and batching updates into set-based operations.

Takeaway: A whole class of “journal is full → system meltdown” incidents does not exist in Snowflake.

6) Migration patterns that actually work

Pattern A — Frequent small updates

Teradata: often fine (single-AMP updates on PI-filtered sets).
Snowflake: inefficient (many partition rewrites).
Rewrite: accumulate changes and MERGE in batches; or CTAS + rename for larger changes.

MERGE INTO TARGET t
USING STAGING s
ON (t.key = s.key)
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...);

Pattern B — Large percentage of table changes

Teradata: INSERT-SELECT to a new table, then RENAME.
Snowflake: CTAS + RENAME sequence — simple and effective.

CREATE OR REPLACE TABLE TGT_NEW AS
SELECT * FROM TGT WHERE ...;

ALTER TABLE TGT RENAME TO TGT_OLD;
ALTER TABLE TGT_NEW RENAME TO TGT;
DROP TABLE TGT_OLD;

7) Tuning the slower side — pragmatic checklists

Make Teradata faster (when it’s the bottleneck)

Target single-AMP or few-AMP updates (predicate on Primary Index).
Avoid skewed update patterns where many requests hit the same AMP or rowhash — distribute work evenly.
For wide or high-touch changes, use INSERT-SELECT → RENAME instead of in-place UPDATE to minimize journaling and spool.
Monitor DBC space and Transient Journal usage closely; when DBC fills, all transactions can abort and roll back.
Use smaller update batches to shorten TJ chains.

Make Snowflake faster (when it’s the bottleneck)

Prefer MERGE or CTAS + rename over many tiny UPDATEs.
Design predicates and joins to prune aggressively (touch fewer partitions).
Right-size Time Travel retention on transient/staging tables.
Limit projection to required columns to reduce scan and CPU, understanding that touched partitions are still rewritten.

8) Migration checklist (paste this into your runbook)

Inventory UPDATE-heavy jobs; tag small-frequent vs large-batch.
Convert row-by-row patterns to MERGE batches or CTAS + rename.
Review predicates/joins for partition pruning in Snowflake.
Set appropriate Time Travel retention for transient/staging tables.
Replace SELECT * with explicit columns (reduces scan cost).
Validate transaction semantics (Teradata row-level vs Snowflake snapshot-atomic).
Monitor storage growth during cut-over (expect temporary bumps from Time Travel).

Final thought

On Teradata, UPDATE means editing what’s there and keeping journals so you can undo it.
On Snowflake, UPDATE means writing a new version and flipping metadata so you don’t need to undo anything.

If you take only one idea into your migration, make it this:

Every UPDATE in Snowflake is a small rewrite, not an in-place edit.
Optimize for fewer partitions touched, batches over trickles, and metadata-level recovery — and you’ll get the performance and resilience Snowflake was built to deliver.

✅ Written by Roland Wenzlofsky — author of “Teradata Query Performance Tuning” (Amazon 4.7★), helping enterprises modernize data warehouses from Teradata to Snowflake efficiently.