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; onROLLBACK, 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).
- FREESPACE reserves cylinder-level free space in the relevant storage context, not a fixed amount inside every block. Validate physical layout and release-specific behavior before tuning it.
2) Snowflake in a nutshell: immutable partitions + metadata commits
For standard Snowflake tables, data changes write new micro-partitions. Hybrid tables use different storage and access mechanisms; do not apply this standard-table description to every table type.
- 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.
For standard Snowflake tables, immutable micro-partitions avoid in-place data-block updates; undocumented recovery internals should not be inferred from that storage model.
Data files are immutable; transactions are metadata.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookWhy this matters for performance
- An
UPDATEis 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:
- Traditional block-storage updates modify blocks, but PI changes or storage type can require movement and other write paths.
- 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.
COMMITflips the active metadata pointers to the new version (atomic).ROLLBACKdiscards the candidate snapshot and keeps the old version active.
Snowflake manages rollback for the transaction and table type; immutable storage does not prove zero rollback latency or absence of internal logging and cleanup.
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 user-managed WAL replay in this workflow, and no DBC-like user quota for transaction state.
Rollback is metadata; uncommitted work simply never becomes visible.
Retained versions can increase storage costs. Resource constraints, locks and service failures can also affect operation.
- Lots of DML creates many new partitions.
- Old versions remain until retention expires (default is short).
- Retention affects cost, while limits and service failures remain operational concerns. Tune retention to recovery obligations rather than assuming it is harmless.
Snowflake does not expose Teradata’s DBC transient-journal quota. This difference is not a general guarantee against resource exhaustion, blocking or service failure.
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
CTAS followed by two renames is not a universally atomic replacement. Before a cutover, preserve grants and required attributes, handle dependencies and concurrent writes, and define a tested rollback procedure. The SQL below is an incomplete outline, not a production replacement transaction.
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;
/* Retain TGT_OLD until reconciliation, dependency checks and rollback retention are complete. */
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 → RENAMEinstead of in-placeUPDATEto 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 (the isolation, table type and explicit transaction boundaries on both systems).
- 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.
Planning a Teradata-to-Snowflake migration?
Review the assessment, conversion, reconciliation and cutover work needed for a controlled transition.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book