TRUNCATE vs DELETE vs DROP in SQL: Behavior, Performance, Replication & Rollback

sql truncate looks like a free-speed upgrade to a junior engineer — same outcome as DELETE FROM t, but faster. Interviewers know the gap is much wider than that: TRUNCATE, DELETE, and DROP differ on transaction-log volume, lock granularity, rollback semantics, trigger behaviour, foreign-key handling, identity reset, and the replication and CDC contract downstream. Pick the wrong verb in production and you either burn hours of log shipping, silently break a row-by-row audit pipeline, or block on a referencing FK at 3am.

This guide is the senior-level treatment of the three destructive verbs. It walks through the full truncate sql matrix — DDL vs DML, the transaction-log impact, the locks each verb takes, the rollback rules per dialect (SQL Server / Postgres allow TRUNCATE rollback inside a transaction; MySQL does not), the foreign-key block, the identity reset, the replication and CDC differences, and the decision tree you should be able to draw from memory at any senior SQL interview. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on aggregation problems →, and stack the schema muscles with database design drills →.

---

On this page

• Why this trio trips engineers
• The full TRUNCATE vs DELETE vs DROP matrix
• Transaction log + performance impact
• Replication, triggers & foreign-key effects
• The decision tree — which one to pick
• Cheat sheet — destructive-op recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why this trio trips engineers

Three destructive verbs that look identical from the application layer — and behave completely differently underneath

The one-sentence invariant: DELETE removes rows row-by-row through the transaction log, TRUNCATE deallocates pages and resets the table, and DROP removes the database object entirely — same surface effect ("data is gone"), three completely different log, lock, replication, and rollback stories. Once you internalise that, the entire family of "TRUNCATE vs DELETE" interview probes collapses into a single matrix.

The three places the trio bites in production.

• Transaction log volume. DELETE FROM big_table on a 100M-row warehouse table can blow out a 200 GB log file in minutes and stall the next log backup. TRUNCATE TABLE big_table on the same table writes a few hundred kilobytes — page deallocation metadata only — and finishes in milliseconds.
• Foreign keys. TRUNCATE is refused by SQL Server and Postgres if any other table has a FK referencing the target — even if that referencing table is empty. DELETE honours the ON DELETE CASCADE / SET NULL / RESTRICT rule on each FK and never blocks on the FK itself.
• Replication and CDC. DELETE emits one per-row event to the binlog / WAL / change-tracking stream. TRUNCATE either emits a single "table truncated" DDL event (SQL Server 2016+, Postgres, modern MySQL) or is skipped entirely by some CDC tools — silently breaking row-level audit pipelines downstream.

The full mental model in one line.

• DELETE is DML, per-row, logged per-row, fires triggers, replicates per-row, rolls back in every dialect, preserves identity counters, honours FKs.
• TRUNCATE is DDL (in most dialects), per-page, minimally logged, bypasses row triggers, replicates as a DDL event, rolls back only in SQL Server / Postgres / Oracle (MySQL does not), resets identity counters, is blocked by referencing FKs.
• DROP is DDL, removes the object entirely, minimally logged, fires only DDL triggers, replicates as DDL, rolls back in dialects that support transactional DDL, takes the schema and the data together, is blocked by referencing FKs unless CASCADE is given.

What interviewers listen for.

• Do you say "TRUNCATE is DDL, DELETE is DML" without hesitation? — required answer.
• Do you mention the transaction-log difference (per-row vs per-page) without prompting? — senior signal.
• Do you call out that MySQL TRUNCATE cannot be rolled back (implicit commit), while SQL Server / Postgres can? — senior signal.
• Do you know that a referencing FK blocks TRUNCATE even if the child table is empty? — required answer.

The 2026 reality across dialects.

• SQL Server — TRUNCATE is DDL but participates in user transactions; rollback works. TRUNCATE resets IDENTITY. Referencing FKs block it. TRUNCATE is logged as a DDL event in SQL Server 2016+ replication.
• Postgres — TRUNCATE is fully transactional, can be rolled back, has RESTART IDENTITY / CONTINUE IDENTITY options and CASCADE / RESTRICT for FK behaviour. It bypasses row triggers but fires statement-level BEFORE TRUNCATE / AFTER TRUNCATE triggers.
• MySQL — TRUNCATE performs an implicit commit; you cannot roll it back. It also drops and recreates the table in some storage engines (InnoDB since 5.7 keeps the same table_id). It resets AUTO_INCREMENT. Referencing FKs block it.
• Oracle — TRUNCATE is DDL with an implicit commit (same as MySQL). It can be rolled back only through FLASHBACK TABLE, not ROLLBACK. Resets the high-water mark; sequences are not reset (they are separate objects).
• Snowflake — TRUNCATE TABLE removes all rows; it is transactional and can be rolled back inside a transaction. Sequences are independent objects and are not reset. Time-travel still recovers truncated data within the retention window.
• BigQuery — TRUNCATE TABLE is supported; BigQuery has no general transactions, so the operation is atomic but not rollback-able by ordinary means (DML undo via time-travel only).

Worked example — the same 10M-row purge run three ways

Detailed explanation. A nightly ETL purges a staging.events table that has accumulated 10M rows. The team has tried three implementations and watched the production impact differ by three orders of magnitude. The same final state ("table is empty") arrives via three completely different cost paths.

Question. Given a 10M-row staging.events table with no incoming FK references, write the three statements that all leave the table empty, then rank them by transaction-log cost and wall-clock cost.

Input.

approach	statement	rows touched
A	DELETE FROM staging.events	10,000,000
B	TRUNCATE TABLE staging.events	10,000,000
C	DROP TABLE staging.events; CREATE TABLE staging.events (...);	10,000,000

Code.

-- A) DELETE — per-row, fully logged
DELETE FROM staging.events;

-- B) TRUNCATE — page deallocation, minimal log
TRUNCATE TABLE staging.events;

-- C) DROP + recreate — metadata change, minimal log, schema lost
DROP TABLE staging.events;
CREATE TABLE staging.events (
    event_id   BIGINT PRIMARY KEY,
    user_id    BIGINT,
    event_type VARCHAR(50),
    ts         TIMESTAMP
);

Step-by-step explanation.

1. DELETE FROM staging.events scans every row, writes one log record per row (plus index changes per index per row), and fires any AFTER DELETE trigger per row. Wall-clock is dominated by log writes and ranges from minutes to hours on a 10M-row table with five indexes.
2. TRUNCATE TABLE staging.events deallocates the pages owned by the table, logs only the deallocation events (one per extent, not per row), bypasses row triggers, and finishes in tens of milliseconds. It also resets IDENTITY.
3. DROP TABLE staging.events; CREATE TABLE ... is the fastest at log volume (one DDL metadata change + one DDL CREATE), but it loses the schema between the two statements — any concurrent reader will see "table not found" for the gap, and you must remember to re-grant permissions and recreate indexes.
4. If anything between A and C is wrong (e.g. you forget the index recreate in C, or a trigger required for audit fires only on A), the same "data is gone" outcome arrives with a different downstream cost.

Output.

approach	log written (illustrative)	wall-clock (illustrative)	side-effects
DELETE	~2,400 MB	~42 s	triggers fire, identity preserved
TRUNCATE	~8 MB	~0.04 s	identity reset, triggers bypassed
DROP + recreate	~1 MB	~0.02 s + recreate time	schema temporarily missing, permissions lost

Rule of thumb. "All three leave the table empty" is the wrong axis. Pick the verb by what downstream consumers depend on — triggers, replication, FK relationships, identity continuity — not by the speed of the local statement.

Worked example — the dialect coverage matrix

Detailed explanation. A senior interviewer often opens with "name the dialect differences for TRUNCATE." A junior says "SQL Server has TRUNCATE." A senior names which dialects allow rollback, which reset identity, and which block on FKs. Memorising the six-cell table below earns the signal.

Question. For each of SQL Server, Postgres, MySQL, Oracle, Snowflake, and BigQuery, name whether TRUNCATE (a) can be rolled back inside a transaction, (b) resets the identity / sequence counter, and (c) is blocked by a referencing FK.

Input (none — pure recall question).

Code.

-- SQL Server / Postgres: rollback works
BEGIN;
TRUNCATE TABLE staging.events;
ROLLBACK;  -- rows are back

-- MySQL: rollback does NOT work — implicit commit
START TRANSACTION;
TRUNCATE TABLE staging.events;
ROLLBACK;  -- no effect; the truncate already committed

-- Postgres: explicit identity reset + cascade
TRUNCATE TABLE staging.events RESTART IDENTITY CASCADE;

Step-by-step explanation.

1. SQL Server and Postgres treat TRUNCATE as a transactional DDL operation. Inside BEGIN; ... ROLLBACK;, the truncate is undone and the rows reappear. Use this when "what if we made a mistake?" is part of the runbook.
2. MySQL's TRUNCATE performs an implicit COMMIT of any open transaction and then truncates as a non-transactional DDL. You cannot roll it back — the data is gone the moment the statement returns. This is the most common dialect-specific surprise.
3. Oracle behaves like MySQL — TRUNCATE is auto-committed DDL. FLASHBACK TABLE ... TO BEFORE DROP (or to a SCN) is the only practical undo, and it requires the table to be flashback-enabled.
4. Snowflake's TRUNCATE TABLE is fully transactional, plus its Time-Travel feature lets you UNDROP / clone-from-prior-state within the retention window even outside a transaction.
5. BigQuery has limited transactions; TRUNCATE TABLE is atomic but the canonical undo is the table snapshot history via FOR SYSTEM_TIME AS OF.

Output.

dialect	rollback inside tx	resets identity / sequence	blocked by referencing FK
SQL Server	yes	yes (IDENTITY)	yes
Postgres	yes	only if RESTART IDENTITY is given	yes (or use CASCADE)
MySQL	no — implicit commit	yes (AUTO_INCREMENT reset)	yes
Oracle	no — DDL implicit commit; FLASHBACK only	no (sequences are separate)	yes
Snowflake	yes (transactional)	no (sequences separate)	n/a (FK constraints are not enforced)
BigQuery	atomic, time-travel undo	no (no identity column)	n/a (no FK constraints)

Rule of thumb. Before issuing TRUNCATE in production, name the dialect, then check the row above for the three columns. If even one column surprises you, run a backup or snapshot first.

SQL interview question on the destructive-verb mental model

A senior interviewer often opens with: "We've inherited a nightly job that does DELETE FROM staging.events; and the log file grew to 80 GB last quarter. Walk me through how you'd safely change it to TRUNCATE, what could go wrong, and how you'd verify each safety check."

Solution Using a four-step audit before swapping DELETE for TRUNCATE

-- Step 1 — confirm no other table references staging.events via FK
SELECT fk.name           AS fk_name,
       OBJECT_NAME(fk.parent_object_id) AS referencing_table
FROM   sys.foreign_keys fk
WHERE  fk.referenced_object_id = OBJECT_ID('staging.events');

-- Step 2 — confirm no AFTER/INSTEAD OF DELETE trigger needs to fire
SELECT name, type_desc
FROM   sys.triggers
WHERE  parent_id = OBJECT_ID('staging.events');

-- Step 3 — confirm replication / CDC contract
EXEC sys.sp_cdc_help_change_data_capture
     @source_schema = N'staging',
     @source_name   = N'events';

-- Step 4 — guarded TRUNCATE wrapped in a transaction (SQL Server / Postgres)
BEGIN TRANSACTION;
TRUNCATE TABLE staging.events;
-- run a quick row count + downstream smoke test
SELECT COUNT(*) FROM staging.events;          -- expect 0
SELECT COUNT(*) FROM dwh.events_landing;      -- expect unchanged
COMMIT;  -- or ROLLBACK if any check failed

Step-by-step trace.

step	check	passes if	failure path
1	FK references	zero rows returned	refactor to DELETE + ON DELETE CASCADE, or TRUNCATE CASCADE in Postgres
2	DELETE triggers	zero rows returned	keep DELETE (TRUNCATE will skip the trigger)
3	CDC enabled	CDC says off / not-this-table	keep DELETE (TRUNCATE will emit DDL event, not per-row)
4	guarded run	row counts as expected	COMMIT, else ROLLBACK

The audit converts "TRUNCATE is faster" from a one-liner into a verified change. Each step probes one of the four hidden contracts (FK, triggers, replication, atomicity) that the local statement does not surface.

Output:

metric	before	after
nightly log written	~80 GB	~50 MB
nightly wall-clock	~14 min	~0.5 s
AFTER DELETE trigger fires	yes	no (intentional)
CDC events emitted	per-row	single DDL event

Why this works — concept by concept:

• Audit-before-act — every destructive verb has hidden dependencies (FK, triggers, replication, audit logs). Listing them in code review is cheaper than recovering from an incident.
• FK references — TRUNCATE is blocked by any referencing FK on any other table. Even an empty child table is enough to refuse the statement. The query against sys.foreign_keys catches that statically.
• Trigger semantics — TRUNCATE fires only DDL triggers, not row-level AFTER DELETE triggers. If a soft-delete audit trigger exists, swapping to TRUNCATE silently disables it.
• CDC / replication contract — DELETE emits one event per row to CDC / change-tracking / replicated log. TRUNCATE emits a single DDL event. Downstream consumers that count per-row events break silently.
• Guarded transaction — wrapping the TRUNCATE in BEGIN ... COMMIT lets you bail out if the row counts surprise you. Works on SQL Server / Postgres / Snowflake; does not work on MySQL or Oracle (implicit commit).
• Cost — one extra audit scan against system catalogs (O(1) for FK / trigger / CDC metadata queries); per-statement cost of the truncate drops from O(N rows) to O(1) pages.

SQL
Topic — sql
SQL practice library

Practice →

---

2. The full TRUNCATE vs DELETE vs DROP matrix

Eight rows · three columns · one matrix that should answer every interview probe on destructive operations

The mental model in one line: memorise the 8×3 behaviour matrix once and you can derive any "TRUNCATE vs DELETE" answer at a senior interview from first principles — type, log, locks, rollback, identity reset, triggers, FK, speed. Every probe collapses into "which row of the matrix are you asking about?"

The matrix in one table.

Row	TRUNCATE	DELETE	DROP
Type	DDL	DML	DDL
Transaction log	minimal (page deallocation)	per-row entries (+ index log)	object metadata + extent free
Locks	schema-modify (table-level)	row / page locks	schema-modify (table-level)
Rollback inside tx	yes (SQL Server / Postgres / Snowflake); no (MySQL / Oracle)	yes (every dialect)	yes (SQL Server / Postgres / Snowflake)
Identity / sequence reset	yes (SQL Server / MySQL); opt-in (Postgres RESTART IDENTITY)	no (preserved)	n/a (object gone)
Triggers	bypasses row triggers; DDL trigger only; Postgres BEFORE/AFTER TRUNCATE fires	fires AFTER / INSTEAD OF DELETE triggers	fires DDL triggers only
Foreign keys	blocked if any FK references the table	honours ON DELETE CASCADE / SET NULL / RESTRICT	blocked unless CASCADE given
Speed / cost	O(1) — page deallocation	O(N) — per-row scan + log	O(1) — metadata change

Three plain-English summaries.

• TRUNCATE = "empty fast, same schema, same indexes, same permissions." Best when the table is reused with the same shape immediately afterwards.
• DELETE = "remove the rows that match a predicate, audit every removal, keep the identity sequence, respect downstream contracts." Best when triggers, replication, or FK cascading rules must fire.
• DROP = "remove the object entirely — schema, data, indexes, permissions, statistics." Best when the table will not be reused.

The two interview gotchas hidden inside the matrix.

• Rollback is not uniform. Saying "TRUNCATE can be rolled back" without naming the dialect is a junior signal. The correct answer is "yes in SQL Server, Postgres, Snowflake; no in MySQL and Oracle because they auto-commit DDL."
• Identity reset is not uniform. Postgres needs RESTART IDENTITY to reset the sequence; without it, the next insert continues from the prior value. SQL Server resets by default. Oracle never resets a sequence with TRUNCATE — sequences are separate database objects.

Worked example — drawing the matrix on a whiteboard

Detailed explanation. Senior interviewers often hand you a marker and say "draw the TRUNCATE vs DELETE vs DROP matrix from memory." The fastest way to do this is to label the eight rows down the left edge (Type, Log, Locks, Rollback, Identity, Triggers, FK, Speed), then fill three columns. The Y-axis is "what behaviour are we comparing"; the X-axis is "which verb."

Question. Reproduce the 8×3 matrix from memory on a blank canvas and call out the two cells where the cell value depends on the dialect (rollback and identity reset).

Input.

empty cell	empty cell	empty cell	empty cell
(row label)	TRUNCATE	DELETE	DROP

Code.

-- The matrix isn't code-shaped — but every cell has a one-line proof query:

-- Type
SELECT 'TRUNCATE = DDL' UNION ALL SELECT 'DELETE = DML' UNION ALL SELECT 'DROP = DDL';

-- Rollback (SQL Server / Postgres)
BEGIN;
  TRUNCATE TABLE staging.events;
  SELECT COUNT(*) FROM staging.events;  -- 0
ROLLBACK;
SELECT COUNT(*) FROM staging.events;    -- back to original

-- Identity reset (SQL Server)
TRUNCATE TABLE staging.events;
INSERT INTO staging.events (event_type) VALUES ('x');
SELECT MAX(event_id) FROM staging.events;  -- 1, not the prior MAX + 1

-- FK block
ALTER TABLE child ADD CONSTRAINT fk_child_parent
       FOREIGN KEY (parent_id) REFERENCES parent(id);
TRUNCATE TABLE parent;  -- error: cannot truncate, FK reference exists

Step-by-step explanation.

1. The "Type" row separates the trio into DDL (TRUNCATE, DROP) and DML (DELETE). DDL operations historically auto-commit; modern engines (SQL Server, Postgres) make TRUNCATE and DROP transactional.
2. The "Log" row is the single biggest performance differentiator. DELETE writes one log record per row plus index changes; TRUNCATE writes one log record per extent deallocated; DROP writes one log record per metadata change.
3. The "Locks" row drives concurrency. TRUNCATE/DROP take a schema-modify lock that blocks every reader and writer of the table; DELETE takes row or page locks that let other rows be read.
4. The "Rollback" row is the dialect-dependent cell — name SQL Server / Postgres / Snowflake as "yes," MySQL / Oracle as "no" (implicit commit).
5. The "Identity / sequence reset" row is the other dialect-dependent cell — SQL Server resets, MySQL resets AUTO_INCREMENT, Postgres requires the explicit RESTART IDENTITY clause, Oracle never resets sequences.
6. The "Triggers" row is the audit-pipeline killer. DELETE fires row-level AFTER / INSTEAD OF triggers; TRUNCATE bypasses them entirely (Postgres has statement-level BEFORE/AFTER TRUNCATE triggers, but no row trigger fires).
7. The "FK" row is the FK-block surprise. TRUNCATE refuses to run if any other table references the target with a FK constraint, even if the referencing table is empty.
8. The "Speed" row is what the junior remembers — but it is the consequence of the log and lock rows, not an independent property.

Output.

row	TRUNCATE	DELETE	DROP
Type	DDL	DML	DDL
Log	minimal	per-row	metadata
Locks	schema-modify	row/page	schema-modify
Rollback inside tx	dialect-dependent	yes	dialect-dependent
Identity reset	dialect-dependent	no	n/a
Triggers	bypasses row triggers	fires row triggers	DDL trigger only
FK	blocked if referenced	honours cascade rules	blocked unless CASCADE
Speed	O(1)	O(N)	O(1)

Rule of thumb. Eight rows fits in working memory. Practise drawing the matrix until you can do it in under 60 seconds without looking — that single skill makes you sound like a senior engineer on a destructive-op question.

Worked example — when the matrix collapses to one column

Detailed explanation. Some scenarios collapse to a single correct answer. "I need to delete rows older than 90 days" → DELETE (you need WHERE). "I need to empty a staging table that nothing depends on" → TRUNCATE. "I need to remove a deprecated lookup table" → DROP. The 8×3 matrix is what you use to defend the choice, not what you use to make it.

Question. For each of three scenarios, pick the single correct verb and justify the choice with two rows from the matrix.

Input (three scenarios).

scenario	description
A	Purge rows older than 90 days from a CDC-tracked orders table
B	Empty a staging.events table at the start of every load
C	Remove a deprecated legacy_lookup_v1 table

Code.

-- A) DELETE — needs WHERE + per-row CDC events
DELETE FROM orders WHERE order_date < CURRENT_DATE - INTERVAL '90' DAY;

-- B) TRUNCATE — no WHERE needed, no FK references, no downstream triggers
TRUNCATE TABLE staging.events;

-- C) DROP — object should not exist any more
DROP TABLE legacy_lookup_v1;

Step-by-step explanation.

1. Scenario A needs a WHERE clause — TRUNCATE has no WHERE, so the matrix collapses to DELETE. Two matrix rows that defend the choice: "Type = DML" (DELETE supports predicate filtering) and "Triggers = fires row triggers" (CDC needs the per-row events).
2. Scenario B has no WHERE and no FK references and no row triggers — TRUNCATE wins. Two defending rows: "Log = minimal" (4 orders of magnitude less log) and "Identity reset = yes" (staging tables usually want a clean numbering).
3. Scenario C says the object should be removed entirely — DROP. Two defending rows: "Type = DDL" (removes the object metadata) and "FK = blocked unless CASCADE" (prompts you to check for references before running).

Output.

scenario	verb	matrix justification
A	DELETE	needs WHERE; triggers / CDC must fire
B	TRUNCATE	no predicate, no FK, minimal log
C	DROP	object removal, schema gone

Rule of thumb. "Pick the verb whose matrix row best matches the requirement, then justify with two more rows." That formula is the seasoned engineer's answer to every TRUNCATE vs DELETE vs DROP question.

SQL interview question on the matrix in one sentence

A senior interviewer often asks: "In one sentence each, describe TRUNCATE, DELETE, and DROP — and tell me the one cell of the matrix where you have to ask 'which dialect?' before answering."

Solution Using a three-sentence summary + the two dialect-dependent cells

-- TRUNCATE: DDL that empties a table by deallocating pages — minimal log,
-- bypasses row triggers, blocked by referencing FKs, dialect-dependent on
-- rollback and identity reset.

-- DELETE: DML that removes rows matching an optional WHERE clause — per-row
-- logged, fires AFTER/INSTEAD OF triggers, honours ON DELETE CASCADE,
-- always rolls back inside a transaction.

-- DROP: DDL that removes the table object entirely — metadata-only log,
-- fires only DDL triggers, blocked unless CASCADE is given.

-- The two dialect-dependent cells are:
--   (1) TRUNCATE rollback inside tx
--       — yes in SQL Server / Postgres / Snowflake
--       — no in MySQL / Oracle (implicit commit)
--   (2) TRUNCATE identity / sequence reset
--       — yes in SQL Server, MySQL (AUTO_INCREMENT)
--       — opt-in in Postgres (RESTART IDENTITY)
--       — never in Oracle (sequences are separate)

Step-by-step trace.

verb	one-line summary	always-true rows	dialect-dependent rows
TRUNCATE	DDL, page deallocation, minimal log	Type, Log, Locks, Triggers, FK, Speed	Rollback, Identity reset
DELETE	DML, per-row, fully logged, supports WHERE	Type, Log, Locks, Rollback, Identity, Triggers, FK, Speed	(none)
DROP	DDL, removes object, metadata-only log	Type, Log, Locks, Triggers, FK, Speed	Rollback (SQL Server / Postgres only)

The summary works because seven of eight rows are dialect-independent for TRUNCATE — only Rollback and Identity reset move with dialect.

Output:

dialect	TRUNCATE rollback	TRUNCATE identity reset
SQL Server	yes	yes
Postgres	yes	opt-in via RESTART IDENTITY
MySQL	no (implicit commit)	yes (AUTO_INCREMENT)
Oracle	no (DDL implicit commit)	no (sequences separate)
Snowflake	yes	n/a (sequences separate)
BigQuery	atomic; time-travel undo	n/a (no identity column)

Why this works — concept by concept:

• One-line summaries — interviewers can absorb three sentences faster than they can parse a matrix. Lead with the summary, defend with the matrix.
• Always-true vs dialect-dependent — the senior signal is naming which rows depend on the dialect. Six rows out of eight are uniform across dialects; the two that move are Rollback and Identity reset.
• Implicit commit — MySQL and Oracle treat TRUNCATE (and most DDL) as auto-committed, so wrapping it in a transaction does nothing. Saying this out loud earns the senior signal.
• Sequences vs identity columns — Postgres / Oracle treat sequences as standalone schema objects; SQL Server / MySQL treat the identity / auto-increment counter as a table property. That distinction is why the reset semantics differ.
• Cost — same as a single-statement run; the audit is pure recall and adds zero runtime cost.

SQL
Topic — database
Database design problems (SQL)

Practice →

---

3. Transaction log + performance impact

The transaction log is the single biggest cost driver — DELETE pays per row, TRUNCATE pays per page, DROP pays per object

The mental model in one line: DELETE writes one log record per affected row (plus one per index change), TRUNCATE writes one log record per deallocated extent, and DROP writes one log record per metadata change — three orders of magnitude difference on a 10M-row table. The wall-clock difference is a consequence of the log difference, not a separate property.

Why DELETE is so expensive.

• Per-row log entries. Each row deleted writes a log record containing the row's pre-image (for rollback) and the index updates. On a wide table with five indexes, that is six log records per row.
• Log writes are sequential. The log is the durability serialiser — every commit must wait for log writes to be persisted (fsync-style). DELETE-heavy workloads queue behind log throughput.
• Recovery model amplifies it. In SQL Server's FULL recovery model, the log grows linearly with the DELETE. In SIMPLE or BULK_LOGGED, it grows during the DELETE and shrinks after the next checkpoint — but the peak is still per-row.
• Postgres / Oracle undo. Postgres marks each deleted tuple as dead and relies on VACUUM to free space. Oracle writes undo segments. Both are O(rows) costs that TRUNCATE avoids entirely.

Why TRUNCATE is so cheap.

• Page deallocation. The table's pages are returned to the free-page pool in one metadata update per extent. A 10M-row table with 8 KB pages and ~100 rows per page has ~100,000 pages — and they are grouped into ~12,500 extents (8 pages each in SQL Server).
• Minimal log. Each extent deallocation is one log record. So 100,000 pages collapse into ~12,500 log records — vs ~10M for DELETE.
• No row pre-images. TRUNCATE does not log the row content. Rollback (where supported) replays the deallocation, not row-by-row inserts.
• No index per-row work. Indexes are truncated at the index level, not at the leaf-row level — same page-deallocation trick.

Why DROP is the cheapest.

• One metadata update. The object is removed from sys.objects / pg_class / information_schema.tables — one row deleted from a system catalog.
• Extents freed in one shot. The table's extents are returned to the free pool; no per-extent work, just "all pages for this object_id are free."
• No statistics, no permissions to update. The object's metadata is gone — including statistics, permissions, triggers — so there is nothing else to clean up.

Recovery models and log backups.

• FULL recovery (SQL Server). Every operation is fully logged. DELETE pays full per-row cost; log backups must keep up or the log file grows unbounded.
• SIMPLE recovery. Log is truncated after each checkpoint; you cannot do point-in-time restore. DELETE still costs per-row, but the log file does not grow forever.
• BULK_LOGGED recovery. A middle ground that minimally logs BULK INSERT, SELECT INTO, and a few other operations. DELETE still pays per-row. Use it during big loads, not during big deletes.

Worked example — a 10M-row mini benchmark

Detailed explanation. A team measures the cost of DELETE vs TRUNCATE on a 10M-row table called staging.events with five secondary indexes. The numbers below are illustrative — your hardware will differ — but the ratio is what matters: three orders of magnitude in log volume and three orders of magnitude in wall-clock.

Question. Run DELETE FROM staging.events and TRUNCATE TABLE staging.events against the same 10M-row table and report the log written and wall-clock for each. Explain the ratio.

Input.

table	rows	row size	indexes	recovery model
staging.events	10,000,000	~240 bytes	5	FULL

Code.

-- Measure DELETE
DBCC SQLPERF('LOGSPACE');     -- snapshot log used before
DECLARE @t0 datetime2 = SYSDATETIME();
DELETE FROM staging.events;
DECLARE @t1 datetime2 = SYSDATETIME();
PRINT DATEDIFF(MILLISECOND, @t0, @t1);
DBCC SQLPERF('LOGSPACE');     -- snapshot log used after

-- Reload the same 10M rows from backup
-- ...

-- Measure TRUNCATE
DBCC SQLPERF('LOGSPACE');
DECLARE @t2 datetime2 = SYSDATETIME();
TRUNCATE TABLE staging.events;
DECLARE @t3 datetime2 = SYSDATETIME();
PRINT DATEDIFF(MILLISECOND, @t2, @t3);
DBCC SQLPERF('LOGSPACE');

Step-by-step explanation.

1. DELETE writes one log record per row plus one per (row × index). With five secondary indexes, that is 6 log records per row × 10M rows ≈ 60M log records. At ~50 bytes per record that's ~3 GB of log — and most production runs show 2–3 GB to be a fair estimate.
2. TRUNCATE deallocates ~12,500 extents. At a few hundred bytes per deallocation record, that's ~5–10 MB of log. Three orders of magnitude smaller.
3. Wall-clock follows the log volume because the log is the serialiser. DELETE typically runs at ~5–10 MB/s of log throughput → ~5–10 minutes for 3 GB. TRUNCATE at metadata speed → tens of milliseconds.
4. The DROP variant writes a handful of metadata log records (one to remove sys.objects entry, one to free the extents) — fastest of all but loses the schema.

Output.

operation	log written (illustrative)	wall-clock (illustrative)	rollback cost
DELETE FROM staging.events	~2,400 MB	~42 s	replays 60M row inserts
TRUNCATE TABLE staging.events	~8 MB	~0.04 s	replays 12,500 deallocations
DROP TABLE staging.events	~1 MB	~0.02 s	replays one metadata change

Rule of thumb. If you are about to DELETE more than ~10% of a table and you do not need WHERE / triggers / CDC, switch to TRUNCATE. The log cost difference pays for itself the first night.

Worked example — Postgres dead tuples and VACUUM

Detailed explanation. Postgres is MVCC — it does not delete rows in place. DELETE marks each row as dead; the storage is reclaimed by VACUUM later. A massive DELETE produces a massive dead-tuple pile that VACUUM has to walk through, which can stall the next autovacuum cycle and bloat the index. TRUNCATE skips this entirely by reclaiming pages at once.

Question. Given a Postgres events table with 10M rows, compare the post-operation cleanup cost of DELETE vs TRUNCATE. Show how many tuples VACUUM needs to scan and how long the table stays bloated.

Input.

operation	dead tuples after	index bloat	next VACUUM cost
DELETE	10,000,000	full	full table scan
TRUNCATE	0	none	none

Code.

-- Postgres — DELETE creates dead tuples that VACUUM must process
DELETE FROM events;
SELECT n_dead_tup, n_live_tup
FROM   pg_stat_user_tables
WHERE  relname = 'events';     -- n_dead_tup ≈ 10M

VACUUM (VERBOSE) events;        -- scans 10M dead tuples
-- Indexes are also bloated; consider REINDEX or VACUUM FULL

-- TRUNCATE — no dead tuples, no bloat, indexes empty
TRUNCATE TABLE events;
SELECT n_dead_tup, n_live_tup
FROM   pg_stat_user_tables
WHERE  relname = 'events';     -- both 0

Step-by-step explanation.

1. After DELETE FROM events, every former row is a dead tuple — still on disk, marked as invisible to new transactions. The table's physical size is unchanged.
2. Autovacuum picks up the dead tuples later and reclaims the space, but its scan time is O(rows). On a 10M-row dead-tuple pile, that's a multi-minute background scan.
3. Indexes are bloated proportionally — each index leaf still points to the dead tuples until VACUUM walks it. You may need REINDEX CONCURRENTLY to shrink the index files.
4. TRUNCATE does not create dead tuples. It reclaims every page at once. The next INSERT finds an empty file and full-speed indexes.

Output.

metric	after DELETE	after TRUNCATE
dead tuples	10,000,000	0
table size on disk	unchanged	shrunk to empty
index bloat	proportional	none
autovacuum work	full table scan	none
time to "back to normal"	minutes to hours	immediate

Rule of thumb. On Postgres, the post-operation cleanup cost of DELETE is at least as large as the operation itself. If the same outcome is reachable via TRUNCATE, the total cost ratio is closer to 100,000× than the bare per-statement number suggests.

Worked example — when DELETE is worth the cost

Detailed explanation. DELETE is not always wrong — there are three production patterns where the per-row cost is required, not avoidable. Knowing these patterns is what stops a junior from "always TRUNCATE" disasters.

Question. Name three production scenarios where DELETE is mandatory despite the higher cost, and write the canonical DELETE for each.

Input.

scenario	why DELETE is required
WHERE-clause purge	TRUNCATE has no WHERE
Replicated / CDC table	downstream needs per-row events
Trigger-driven audit log	TRUNCATE bypasses row triggers

Code.

-- 1) Conditional purge of older rows — needs WHERE
DELETE FROM orders WHERE order_date < CURRENT_DATE - INTERVAL '90' DAY;

-- 2) Replicated / CDC table — every row must emit one CDC event
DELETE FROM dwh.events_to_replicate
WHERE  partition_id = :dropped_partition_id;

-- 3) Trigger-driven audit log — AFTER DELETE writes to audit_log
DELETE FROM users WHERE status = 'deactivated';
-- AFTER DELETE trigger writes one audit_log row per deleted user

Step-by-step explanation.

1. The conditional purge needs a WHERE predicate to keep recent rows. TRUNCATE has no WHERE — its only option is "everything." If you need partial deletion, DELETE is the only choice.
2. CDC tools (Debezium, native SQL Server change data capture, Postgres logical replication) capture per-row events. Switching to TRUNCATE emits a single DDL event that the downstream consumer often skips or treats specially — silently breaking row-level audit pipelines.
3. AFTER DELETE triggers run per row. If your audit log is built on this trigger, TRUNCATE bypasses it entirely. The audit table never gets the row, and the next compliance review surfaces missing entries.
4. In all three cases the cost of DELETE is acceptable because the per-row work is what the business needs. The triggers, CDC events, and predicate evaluation are not overhead — they are the feature.

Output.

scenario	verb	acceptable cost
Older-than-90-days purge	DELETE FROM orders WHERE order_date < ...	per-row scan acceptable; nightly maintenance window
CDC-tracked partition drop	DELETE FROM dwh.events_to_replicate WHERE ...	per-row CDC events are required
Trigger-driven audit	DELETE FROM users WHERE status = 'deactivated'	trigger must fire for audit completeness

Rule of thumb. Before "optimising" a DELETE to a TRUNCATE, list every downstream consumer (CDC, triggers, audit, replication) and confirm none of them depend on per-row events. If even one does, keep the DELETE — the apparent cost is purchasing a downstream guarantee.

SQL interview question on log and performance

A senior interviewer often asks: "Our nightly DELETE on a 50M-row staging table blew out the log file to 200 GB and missed the log-backup window. Walk me through how you'd diagnose, what numbers you'd quote, and which destructive verb you'd swap in — under what safety conditions."

Solution Using a sized estimate + a guarded TRUNCATE swap

-- Step 1 — quantify the current cost
SELECT name, log_reuse_wait_desc, log_size_mb = size * 8 / 1024.0
FROM   sys.master_files
WHERE  database_id = DB_ID() AND type_desc = 'LOG';

SELECT  AVG(rowmodctr) AS avg_row_modifications
FROM    sys.sysindexes
WHERE   id = OBJECT_ID('staging.events');

-- Step 2 — estimate the log savings from TRUNCATE
-- DELETE: ~6 log records per row × 50M rows × ~50 bytes = ~15 GB log
-- TRUNCATE: ~5 KB log (page deallocation)
-- Savings: ~99.999%

-- Step 3 — safety checks (same as Section 1)
SELECT * FROM sys.foreign_keys WHERE referenced_object_id = OBJECT_ID('staging.events');
SELECT * FROM sys.triggers    WHERE parent_id           = OBJECT_ID('staging.events');

-- Step 4 — guarded swap
BEGIN TRANSACTION;
TRUNCATE TABLE staging.events;
SELECT COUNT(*) FROM staging.events;            -- 0
SELECT COUNT(*) FROM dwh.events_landing;        -- unchanged
COMMIT;

Step-by-step trace.

step	observation	decision
1	log file is 200 GB, log_reuse_wait_desc = LOG_BACKUP	log backups not keeping up with per-row DELETE
2	estimated TRUNCATE log ≈ 5 KB vs DELETE ≈ 15 GB	swap reduces nightly log by ~6 orders of magnitude
3	no FK references, no AFTER DELETE triggers, no CDC	TRUNCATE is safe
4	guarded run completes in 40 ms, row count is 0	COMMIT

The diagnosis is "you are paying full per-row log cost for a staging table that has no downstream dependencies on per-row events." The fix is one statement change, guarded by an audit and a transaction.

Output:

metric	before (DELETE)	after (TRUNCATE)
nightly log written	~15 GB	~5 KB
nightly wall-clock	~12 min	~40 ms
log file peak size	200 GB	well under 1 GB
log-backup contention	yes (missed window)	no

Why this works — concept by concept:

• Quantify before you change — every "swap DELETE for TRUNCATE" recommendation should come with the estimated log savings. Senior engineers cite a number, not a guess.
• log_reuse_wait_desc — SQL Server's sys.master_files exposes why the log cannot be reused. LOG_BACKUP means the next log backup needs to run; ACTIVE_TRANSACTION means a long-running transaction is holding the log. Both are common with massive DELETEs.
• FK / trigger / CDC audit — the same three checks as Section 1. They are the difference between "swap is safe" and "swap is an incident."
• Guarded transaction — on SQL Server / Postgres, the TRUNCATE inside BEGIN ... COMMIT lets you bail out without losing the rows. Critical for the first run of a new pattern.
• Estimated-vs-measured — a calibrated estimate sets expectations; the post-change measurement confirms them. Both belong in the change ticket.
• Cost — moving from O(N rows × log bytes) to O(1 metadata bytes); the audit queries are O(1) against system catalogs.

SQL
Topic — aggregation
Aggregation problems (SQL)

Practice →

---

4. Replication, triggers & foreign-key effects

Speed is one axis · downstream contracts are the other — pick by the dependency graph, not by raw throughput

The mental model in one line: replication, triggers, and foreign keys are the three downstream contracts that destructive operations must honour — DELETE honours all three by default, TRUNCATE bypasses the first two and is blocked by the third, DROP fires only DDL triggers and is blocked by referencing FKs unless CASCADE is given. The interview probe is "which contract does your choice break?"

Replication and CDC.

• DELETE writes one row-level event per deleted row to the binlog (MySQL), WAL (Postgres), CDC capture instance (SQL Server), or change tracking. Downstream consumers (Debezium, replication, audit pipelines) receive one event per row and replay deterministically.
• TRUNCATE writes a single DDL event. Behaviour by engine:
  • SQL Server 2016+ logs TRUNCATE and replicates it as a DDL change in transactional replication.
  • Postgres logical replication propagates TRUNCATE as a single message; subscribers truncate the table on their side.
  • MySQL row-based binlog writes a TRUNCATE query event; the downstream replica replays the truncate, but row-level audit tools that count "rows deleted" see zero events.
  • Debezium / Kafka Connect connectors typically emit a TRUNCATE event type — but downstream consumers that filter for DELETE events skip it.
• DROP writes a DDL event; replicas drop the table. CDC connectors typically signal "table no longer captured."

Triggers.

• DELETE fires AFTER DELETE, BEFORE DELETE, and INSTEAD OF DELETE triggers per row. These are the audit-log workhorses.
• TRUNCATE bypasses row-level triggers entirely. Postgres has a separate BEFORE TRUNCATE / AFTER TRUNCATE statement-level trigger family; SQL Server fires DDL triggers but no DML triggers.
• DROP fires DDL triggers only (ON DROP_TABLE in SQL Server; event triggers in Postgres). No row-level triggers fire because the rows are not individually deleted.

Foreign keys.

• DELETE honours ON DELETE CASCADE (deletes child rows), ON DELETE SET NULL (nulls child FK columns), ON DELETE RESTRICT / NO ACTION (refuses if children exist), and triggers any nested cascades transitively.
• TRUNCATE is refused by SQL Server, Postgres, MySQL, and Oracle if any other table has a FK constraint referencing the target — even if that referencing table is empty. The workaround in Postgres is TRUNCATE ... CASCADE, which truncates the referencing tables too (dangerous!). The workaround in SQL Server is to drop the FK, truncate, recreate the FK — a 3am incident if you forget step 3.
• DROP is refused unless CASCADE is given (Postgres) or the FK is dropped first (SQL Server). Less surprising because "dropping a table that things still reference" is obviously suspect.

Identity / sequence behaviour.

• DELETE preserves the next-value counter; the next insert continues from the prior max + 1.
• TRUNCATE behaviour by dialect:
  • SQL Server — resets IDENTITY to the seed value.
  • MySQL InnoDB — resets AUTO_INCREMENT to 1.
  • Postgres — preserves the sequence by default; TRUNCATE ... RESTART IDENTITY resets the bound sequence.
  • Oracle — sequences are independent and are not reset; the table's high-water mark is reset.
  • Snowflake — sequences are independent objects, not touched by TRUNCATE.
• DROP removes the object — identity and sequence go with it (unless the sequence is a separate, named object as in Postgres / Oracle / Snowflake, in which case the sequence survives).

Worked example — replication side-effects on a CDC-tracked table

Detailed explanation. A team relies on Debezium to ship row-level events from a Postgres orders table to Kafka. A junior tries to "speed up" the nightly archive by switching DELETE FROM orders WHERE archived = TRUE to TRUNCATE TABLE orders — and the next-day audit shows zero DELETE events on the Kafka topic. The downstream warehouse never registered the removal.

Question. Show the per-row events emitted by DELETE and the DDL event emitted by TRUNCATE on a CDC-tracked table. Explain why audit pipelines that count per-row events break silently.

Input.

operation	rows affected	downstream events expected by audit
DELETE WHERE archived = TRUE	1,000	1,000 DELETE events
TRUNCATE TABLE orders	all	1 TRUNCATE event

Code.

-- Postgres — CDC-tracked table
-- Per-row DELETE events captured by Debezium
DELETE FROM orders WHERE archived = TRUE;

-- Debezium emits 1,000 records on the orders topic, each shaped like:
-- { "op": "d", "before": { "id": ..., ... }, "source": { ... } }

-- TRUNCATE emits a single record:
TRUNCATE TABLE orders;
-- { "op": "t", "source": { ... } }
-- ^ many audit consumers filter for op = "d" and skip op = "t"

Step-by-step explanation.

1. DELETE emits one row event per deleted row. The audit consumer that counts op = "d" events sees 1,000 events and correctly notes 1,000 deletions.
2. TRUNCATE emits a single op = "t" event for the whole table. The audit consumer that filters for op = "d" skips the truncate event and records zero deletions.
3. The compliance team's "removed rows" report then shows zero — but the table actually went from 50,000 rows to 0. The discrepancy is silent until someone double-checks the source table.
4. The fix is either to (a) keep DELETE for CDC-tracked tables and accept the cost, (b) add op = "t" to the audit consumer's filter and synthesise a "table truncated; N rows removed" entry, or (c) switch to a soft-delete pattern that issues UPDATE events instead.

Output.

op	events emitted on DELETE	events emitted on TRUNCATE	audit-consumer count (filter op="d")
d	1,000	0	DELETE: 1,000; TRUNCATE: 0
t	0	1	(skipped)

Rule of thumb. On any CDC-tracked or replicated table, treat TRUNCATE as a separate event class. Either keep DELETE, or update the downstream consumer to handle TRUNCATE explicitly. Never assume the consumer will "just figure it out."

Worked example — the foreign-key block

Detailed explanation. A team has a customers table referenced by a orders table via a FK constraint. They want to truncate customers for a fresh load and are surprised when SQL Server / Postgres refuses with "cannot truncate, foreign key reference exists" — even though orders is empty.

Question. Demonstrate the FK block on TRUNCATE and show the three legitimate workarounds: DELETE + cascade, Postgres TRUNCATE ... CASCADE, and the disable-FK-truncate-re-enable pattern in SQL Server.

Input.

table	rows	FK
customers	1,000	(referenced by orders.customer_id)
orders	0	FK to customers.customer_id, ON DELETE NO ACTION

Code.

-- This fails — "cannot truncate, FK reference exists" (even though orders is empty)
TRUNCATE TABLE customers;

-- Workaround 1: DELETE — honours the FK rule
DELETE FROM customers;  -- succeeds because no children exist; otherwise NO ACTION blocks

-- Workaround 2 (Postgres only): TRUNCATE CASCADE
TRUNCATE TABLE customers CASCADE;
-- Cascades to orders — both tables truncated. Use with extreme care.

-- Workaround 3 (SQL Server): disable FK, truncate, re-enable
ALTER TABLE orders NOCHECK CONSTRAINT fk_orders_customers;
TRUNCATE TABLE customers;
ALTER TABLE orders WITH CHECK CHECK CONSTRAINT fk_orders_customers;
-- Forget step 3 and the FK is permanently un-trusted; future inserts silently violate it.

Step-by-step explanation.

1. TRUNCATE TABLE customers is refused because the engine cannot prove that the operation is safe with respect to the FK constraint. The check is structural — based on whether any FK references the table — not on whether the referencing table actually has rows.
2. DELETE FROM customers evaluates the FK rule per row. With ON DELETE NO ACTION and an empty children table, it succeeds. With non-empty children and ON DELETE CASCADE, it cascades the delete down.
3. Postgres' TRUNCATE ... CASCADE truncates the referenced tables too. This is the only way to truncate a referenced table without disabling the constraint — and it is dangerous because it silently empties downstream tables.
4. SQL Server's disable-truncate-re-enable pattern works but has a footgun: NOCHECK CONSTRAINT followed by re-enabling without WITH CHECK CHECK CONSTRAINT leaves the FK in an untrusted state. Future inserts may silently violate the constraint and the query optimiser ignores the (now-unreliable) FK for plan choice.

Output.

approach	result	risk
TRUNCATE TABLE customers	refused with FK error	none — the engine protected you
DELETE FROM customers	succeeds (per FK rule)	per-row log cost; cascade fires
TRUNCATE TABLE customers CASCADE (PG)	truncates customers + orders	silently empties downstream tables
disable + truncate + re-enable (SS)	succeeds	untrusted FK if step 3 forgotten

Rule of thumb. When you hit the FK-block error on TRUNCATE, the right reflex is "use DELETE, or refactor the schema so this table is no longer referenced." Reach for CASCADE or NOCHECK only when you have a written change-management ticket and a rollback plan.

Worked example — identity / sequence reset surprises

Detailed explanation. A team truncates an orders table at the start of each load and is then surprised when the next batch's order_id values start at 1 again — overlapping with archived data. The dialect default determines whether you got an identity reset, and Postgres requires an explicit clause to opt in.

Question. Show the identity / sequence behaviour of TRUNCATE on SQL Server, MySQL, and Postgres. Explain the safety implications of the reset.

Input.

dialect	identity / sequence column	next-value before TRUNCATE	next-value after TRUNCATE (default)
SQL Server	IDENTITY(1,1)	1,000,001	1
MySQL InnoDB	AUTO_INCREMENT	1,000,001	1
Postgres	bigserial / SEQUENCE	1,000,001	1,000,001 (unchanged)

Code.

-- SQL Server — TRUNCATE resets IDENTITY to seed
TRUNCATE TABLE orders;
INSERT INTO orders (order_type) VALUES ('test');
SELECT MAX(order_id) FROM orders;  -- 1

-- MySQL — TRUNCATE resets AUTO_INCREMENT to 1
TRUNCATE TABLE orders;
INSERT INTO orders (order_type) VALUES ('test');
SELECT MAX(order_id) FROM orders;  -- 1

-- Postgres — TRUNCATE does NOT reset the sequence by default
TRUNCATE TABLE orders;
INSERT INTO orders (order_type) VALUES ('test');
SELECT MAX(order_id) FROM orders;  -- 1,000,002

-- Postgres opt-in: reset the sequence
TRUNCATE TABLE orders RESTART IDENTITY;
INSERT INTO orders (order_type) VALUES ('test');
SELECT MAX(order_id) FROM orders;  -- 1

Step-by-step explanation.

1. SQL Server and MySQL reset the identity / auto-increment column by default on TRUNCATE. The next insert starts from the seed (usually 1). If archived data also has order_id = 1, you have a key collision the moment a query joins the two.
2. Postgres treats bigserial as a column with a DEFAULT nextval('sequence_name'). The sequence is an independent schema object — TRUNCATE on the table does not touch the sequence. Use TRUNCATE TABLE orders RESTART IDENTITY to also reset the bound sequence.
3. Oracle never resets sequences via TRUNCATE — sequences are entirely independent objects. The high-water mark resets; the sequence keeps advancing.
4. If your downstream archive joins old and new order_id values (a common warehouse pattern), the SQL Server / MySQL default behaviour is a quiet correctness bug. Either use DELETE (preserves the counter) or accept that primary keys are no longer globally unique across snapshots.

Output.

dialect	default TRUNCATE behaviour	safe-for-archive	opt-in form
SQL Server	resets IDENTITY	no	DBCC CHECKIDENT(..., RESEED, value) to set explicitly
MySQL InnoDB	resets AUTO_INCREMENT	no	ALTER TABLE ... AUTO_INCREMENT = value to set explicitly
Postgres	sequence preserved	yes (default)	TRUNCATE ... RESTART IDENTITY to reset
Oracle	sequence preserved	yes	n/a (sequences independent)

Rule of thumb. Before truncating a table whose primary keys appear in archived / replicated / downstream tables, name the dialect and confirm whether the identity resets. If unsure, run DELETE — preserving the counter is always safer than silently restarting it.

SQL interview question on CDC-aware destructive operations

A senior interviewer often asks: "We have a Debezium-tracked orders table replicated to Snowflake via Kafka. We want to nightly purge orders older than 30 days. Walk me through the destructive verb you'd choose, the FK and trigger checks, and how the downstream Snowflake stays in sync."

Solution Using DELETE to preserve per-row CDC events on a tracked table

-- The right verb is DELETE because:
-- (1) we need WHERE (TRUNCATE has none)
-- (2) Debezium needs per-row events to replicate to Snowflake
-- (3) the AFTER DELETE trigger writes to audit_log

DELETE FROM orders WHERE order_date < CURRENT_DATE - INTERVAL '30' DAY;

-- Optional: batch the DELETE to keep transactions small and let CDC drain
DECLARE @rows_deleted INT = 1;
WHILE @rows_deleted > 0
BEGIN
    DELETE TOP (50000) FROM orders
    WHERE  order_date < CURRENT_DATE - INTERVAL '30' DAY;
    SET @rows_deleted = @@ROWCOUNT;
    WAITFOR DELAY '00:00:05';   -- let CDC consumers catch up
END

Step-by-step trace.

consideration	check	outcome
WHERE required?	yes, order_date < ...	TRUNCATE is impossible
CDC contract	Debezium ships per-row events to Snowflake	DELETE required for per-row events
AFTER DELETE trigger	writes audit_log entries	DELETE required for trigger firing
FK references	order_items.order_id references orders.order_id ON DELETE CASCADE	DELETE handles via cascade; TRUNCATE would be blocked
Log volume	~2 GB nightly	acceptable inside maintenance window
Batching	50k-row chunks with WAITFOR	keeps CDC lag bounded and log file from blowing up

The trace shows that every axis of the matrix favours DELETE for this scenario. The batching strategy keeps the per-row cost manageable and gives CDC headroom to drain.

Output:

metric	value
rows deleted nightly	~500,000 (30-day-old slice)
CDC events emitted	~500,000 op=d events
audit_log rows inserted	~500,000
order_items rows cascaded	~1,200,000
Snowflake stays in sync	yes (per-row replication)
log file peak	bounded by batch size

Why this works — concept by concept:

• Dependency-graph-first — the right verb is determined by the downstream consumers (CDC, triggers, audit), not by the local statement cost. List the consumers and pick the verb that emits the events they need.
• Batching — large DELETEs hurt both log volume and CDC lag. Splitting into 50k-row chunks with a small WAITFOR keeps each transaction short and gives the binlog / WAL reader time to drain.
• FK cascade — ON DELETE CASCADE on order_items.order_id means the child rows go with the parent automatically. TRUNCATE cannot use this rule — it would be blocked entirely.
• Audit trigger — the AFTER DELETE trigger on orders writes one audit_log row per deleted order. Required for compliance; TRUNCATE bypasses it.
• Per-row replication — Debezium captures the op=d event and Snowflake replays it. The downstream warehouse stays in sync without any special TRUNCATE handling.
• Cost — same as a single big DELETE in total log volume, but spread across many small transactions; the engine's per-transaction overhead is amortised by the batch size.

SQL
Topic — joins
JOIN and FK problems (SQL)

Practice →

---

5. The decision tree — which one to pick

Five questions, three terminal verbs — the decision tree that keeps destructive ops out of incident channels

The mental model in one line: walk the decision tree top-down — WHERE? → schema retained? → downstream depends on row events? → FK references? → identity reset desired? — and the first "yes" picks the verb. Memorise the tree and any destructive-op decision becomes a 30-second flowchart.

The seven questions of the decision tree.

• Q1. Do you need a WHERE clause? → If yes, DELETE. TRUNCATE has no predicate; DROP removes the whole object.
• Q2. Will the table be reused with the same schema? → If no, DROP. The object should not exist any more; recreate from scratch later.
• Q3. Inside a transaction that may need rollback? → Dialect-dependent. SQL Server / Postgres / Snowflake allow TRUNCATE rollback; MySQL / Oracle do not (implicit commit). If you need MySQL rollback, DELETE.
• Q4. Are there FK references? → If yes, prefer DELETE (with ON DELETE CASCADE) or Postgres TRUNCATE CASCADE (with caution) or the disable-FK-truncate-re-enable pattern (risky).
• Q5. Do triggers need to fire? → If yes, DELETE. TRUNCATE bypasses row triggers.
• Q6. Is replication / CDC downstream? → If yes, DELETE. TRUNCATE emits a single DDL event; per-row consumers break.
• Q7. Is identity / sequence reset desired? → If yes, TRUNCATE (or TRUNCATE ... RESTART IDENTITY in Postgres). If no, DELETE (preserves the counter).

The "fall-through" case.

If you answer no to Q1 through Q7, the default is TRUNCATE. The use case is "empty a staging table that has no FK references, no triggers, no replication, and where identity reset is irrelevant" — the cleanest TRUNCATE scenario.

The "non-destructive" detour.

Before reaching for any of the three verbs, ask:

• Soft-delete pattern. Add an is_deleted column; rows are filtered out but never removed. Required when audit / compliance demand the original row is preserved.
• MERGE for upsert workflows. If the goal is "make the table match a source," use MERGE (INSERT ... ON CONFLICT DO UPDATE in Postgres) instead of "delete then insert."
• Partition swap. For very large warehouse tables, the canonical "purge the oldest 90 days" pattern is partition switching — drop a partition (O(1)) rather than DELETE within it (O(N)).

Engine caveats to remember.

• MySQL — TRUNCATE is implicit-commit DDL; no rollback. AUTO_INCREMENT resets to 1.
• SQL Server / Postgres — TRUNCATE is fully transactional; rollback works inside BEGIN ... COMMIT.
• Oracle — TRUNCATE is implicit-commit DDL; FLASHBACK TABLE is the only practical undo. Sequences not reset.
• Snowflake — TRUNCATE is transactional; time-travel restores within retention. Sequences not reset.
• BigQuery — TRUNCATE TABLE is atomic; no general transactions; time-travel snapshots for undo.

Worked example — running the decision tree on three scenarios

Detailed explanation. The fastest way to internalise the tree is to walk it on three contrasting scenarios — a WHERE purge, a clean staging empty, and a deprecated lookup table. Each pass terminates after one or two questions, and the verb falls out.

Question. For each scenario below, walk the decision tree top-down and explain which question terminated the search and why.

Input.

scenario	requirement	FK refs	triggers	CDC	identity reset
A	purge orders older than 30 days from CDC-tracked table	none	AFTER DELETE writes audit	yes	no
B	nightly empty staging.events staging table	none	none	no	yes
C	remove deprecated legacy_lookup_v1	none	none	no	n/a

Code.

-- A) Q1 says "yes, we need WHERE" → DELETE
DELETE FROM orders WHERE order_date < CURRENT_DATE - INTERVAL '30' DAY;

-- B) Q1 = no, Q2 = yes (reuse same schema), Q3 = MySQL? → ...
-- Assuming SQL Server / Postgres + no FK + no trigger + no CDC + identity reset desired → TRUNCATE
TRUNCATE TABLE staging.events;

-- C) Q2 says "no, the table should not exist" → DROP
DROP TABLE legacy_lookup_v1;

Step-by-step explanation.

1. Scenario A terminates at Q1 ("need WHERE"). The answer is DELETE — no further questions needed. Knowing the rest of the matrix (CDC needs per-row events, AFTER DELETE trigger must fire) only reinforces the choice.
2. Scenario B passes Q1 (no WHERE), passes Q2 (reuse schema), passes Q3 (assume SQL Server, rollback ok), passes Q4 (no FK), passes Q5 (no trigger), passes Q6 (no CDC), and lands at Q7 ("identity reset desired") with yes → TRUNCATE. Even the "no" answer to Q7 would still land on TRUNCATE because of the fall-through default.
3. Scenario C terminates at Q2 ("schema should not be retained"). The answer is DROP — no further questions needed.
4. The tree is terminating — every scenario ends at a verb after at most seven questions, and most scenarios end at one or two.

Output.

scenario	terminated at	verb
A	Q1 (need WHERE)	DELETE
B	Q7 (default → TRUNCATE)	TRUNCATE
C	Q2 (no schema reuse)	DROP

Rule of thumb. Walk the tree top-down. The moment a question terminates, stop — you have the verb. Do not look at the rest of the matrix to second-guess.

Worked example — the MERGE detour for upserts

Detailed explanation. A team wants to "make target match source" — common in incremental warehouse loads. The naive shape is DELETE FROM target; INSERT INTO target SELECT * FROM source;. The right shape is MERGE (or INSERT ... ON CONFLICT DO UPDATE in Postgres), which is atomic, preserves identity, and avoids the destructive operation entirely.

Question. Replace DELETE FROM target; INSERT INTO target SELECT * FROM source; with MERGE. Show why the destructive shape is wrong and the MERGE is right.

Input — target.

id	name	tier
1	Alice	gold
2	Bob	silver

Input — source.

id	name	tier
1	Alice	gold
2	Bob	gold (upgrade)
3	Cara	silver (new)

Code.

-- Anti-pattern: destructive shape — DELETE everything, INSERT everything
DELETE FROM target;
INSERT INTO target (id, name, tier) SELECT id, name, tier FROM source;
-- Problems: target is empty for a brief window; identity restarts; FKs may block;
-- audit shows 2 deletes + 3 inserts even when only 1 row changed.

-- Right shape: MERGE — atomic upsert
MERGE INTO target AS t
USING source     AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET name = s.name, tier = s.tier
WHEN NOT MATCHED BY TARGET THEN INSERT (id, name, tier) VALUES (s.id, s.name, s.tier)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

-- Postgres equivalent:
INSERT INTO target (id, name, tier)
SELECT id, name, tier FROM source
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, tier = EXCLUDED.tier;
-- The "remove rows in target but not in source" half needs a separate DELETE.

Step-by-step explanation.

1. The destructive shape (DELETE; INSERT;) empties the target completely, then refills. Any concurrent reader sees an empty table during the gap. Even if wrapped in a transaction, the log cost is full per-row twice.
2. MERGE evaluates each source row against the target on the matching key. Matched rows are updated in place (no identity churn). Unmatched-by-target rows are inserted. Unmatched-by-source rows can be deleted (if WHEN NOT MATCHED BY SOURCE THEN DELETE is present).
3. The audit trail is correct: one UPDATE for Bob (tier change), one INSERT for Cara, no DELETE. Compare with the destructive shape: 2 DELETE + 3 INSERT events that misrepresent the actual change.
4. The Postgres equivalent (INSERT ... ON CONFLICT DO UPDATE) handles the upsert half cleanly; the "delete-not-in-source" half needs a separate DELETE FROM target WHERE id NOT IN (SELECT id FROM source) statement.

Output (after MERGE).

id	name	tier	event in audit
1	Alice	gold	(unchanged)
2	Bob	gold	UPDATE
3	Cara	silver	INSERT

Rule of thumb. Whenever you find yourself writing DELETE FROM x; INSERT INTO x ... in the same transaction, stop. The right primitive is MERGE (SQL Server / Oracle / Snowflake / BigQuery) or INSERT ... ON CONFLICT (Postgres / SQLite / DuckDB). The destructive shape only wins when the source replaces 100% of the rows and you need the identity reset side effect.

Worked example — partition swap as the warehouse-scale alternative

Detailed explanation. On warehouse tables that grow into the hundreds of millions of rows, DELETE is too slow and TRUNCATE wipes everything. The canonical pattern is partition switching: design the table with a partition per day / month / quarter, then drop the oldest partition to purge data. The cost is O(1) — one metadata update.

Question. Compare the cost of "delete the oldest 90 days from a 500M-row events table" using DELETE vs partition swap. Explain why partition swap is the warehouse-scale answer.

Input.

table	rows	partitioning	oldest-90-days slice
events (DELETE pattern)	500,000,000	none	60,000,000
events (partitioned)	500,000,000	partition by month	6 partitions (~60M rows)

Code.

-- Naive (DELETE) — full per-row log cost on 60M rows
DELETE FROM events
WHERE  event_date < CURRENT_DATE - INTERVAL '90' DAY;

-- Partitioned alternative (SQL Server):
ALTER TABLE events SWITCH PARTITION 1 TO events_old_oct_2025;
DROP TABLE events_old_oct_2025;
-- Repeat for each old partition.

-- Postgres equivalent (declarative partitioning):
ALTER TABLE events DETACH PARTITION events_oct_2025;
DROP TABLE events_oct_2025;

Step-by-step explanation.

1. The naive DELETE writes ~60M log records (one per row) plus index updates. On FULL recovery the log file balloons; on SIMPLE the log churns but does not grow forever. Wall-clock is many minutes.
2. The partition swap moves the partition's metadata to a sibling table in O(1) — one row updated in the system catalog. The data on disk is unchanged.
3. The follow-up DROP TABLE events_old_oct_2025 removes the sibling table — also O(1).
4. Total cost: two metadata updates per partition × 6 old partitions = 12 catalog-row updates. The wall-clock is milliseconds.

Output.

approach	log written	wall-clock	downstream effect
DELETE FROM events WHERE event_date < ...	~15 GB	~10 min	per-row CDC events emitted
ALTER TABLE events SWITCH PARTITION ... + DROP	~1 KB	<1 s	partition-level DDL event

Rule of thumb. On any warehouse-scale table where a time-based purge is part of the lifecycle, design the table partitioned from day one. The choice between DELETE and TRUNCATE collapses when partition swap is available — it is O(1) and ships exactly the granularity (one partition at a time) that warehouse retention policies require.

SQL interview question on the decision tree

A senior interviewer often asks: "Walk me through your decision tree for picking TRUNCATE vs DELETE vs DROP in three sentences. Then apply it to: (a) a CDC-tracked orders purge, (b) a nightly staging-table empty, (c) a deprecated lookup table."

Solution Using the seven-question tree with three worked applications

-- The tree, applied:

-- (a) CDC-tracked orders purge
--   Q1 need WHERE? YES → DELETE
DELETE FROM orders WHERE order_date < CURRENT_DATE - INTERVAL '30' DAY;

-- (b) Nightly staging.events empty
--   Q1 need WHERE? NO
--   Q2 reuse schema? YES
--   Q3 transactional rollback needed? NO (or yes on SS/PG)
--   Q4 FK refs? NO
--   Q5 triggers? NO
--   Q6 CDC downstream? NO
--   Q7 identity reset desired? YES → TRUNCATE
TRUNCATE TABLE staging.events;

-- (c) Deprecated legacy_lookup_v1
--   Q1 need WHERE? NO
--   Q2 reuse schema? NO → DROP
DROP TABLE legacy_lookup_v1;

Step-by-step trace.

scenario	Q1 (WHERE?)	Q2 (reuse?)	Q3 (rollback?)	Q4 (FK?)	Q5 (trigger?)	Q6 (CDC?)	Q7 (identity?)	terminator	verb
(a)	YES	—	—	—	—	—	—	Q1	DELETE
(b)	NO	YES	n/a	NO	NO	NO	YES	Q7	TRUNCATE
(c)	NO	NO	—	—	—	—	—	Q2	DROP

Each scenario terminates after one or two questions. The tree is fast because the most-discriminating questions (WHERE? and schema-retained?) are at the top.

Output:

scenario	verb	one-line justification
(a)	DELETE	needs WHERE; CDC and trigger contracts intact
(b)	TRUNCATE	no predicate, no FK, no CDC; identity reset desired
(c)	DROP	object should not exist any more

Why this works — concept by concept:

• Top-down tree — the most-discriminating questions are at the top, so most scenarios terminate after one or two. The tree is fast, not thorough.
• Q1 (WHERE?) at the top — predicate filtering is the single most differentiating feature. DELETE is the only verb that supports it.
• Q2 (schema-retained?) second — separates "the object stays" (TRUNCATE) from "the object goes" (DROP). After Q1 and Q2, the search space is already 1/3 of the original.
• Q3–Q6 are downstream-contract questions — rollback, FK references, triggers, replication. Each is a downstream consumer that the destructive op must honour.
• Q7 (identity reset) is the tie-breaker — by the time you reach Q7, the choice is between TRUNCATE (resets) and DELETE (preserves). Identity reset semantics decide.
• Cost — the tree itself is O(1) thought-time; the resulting verb's cost is bounded by the matrix (O(1) for TRUNCATE / DROP, O(N) for DELETE).

SQL
Topic — case expression
CASE expression and conditional drills

Practice →

---

Cheat sheet — destructive-op recipes

• Empty a table fast in a single environment. TRUNCATE TABLE staging.events; — works in SQL Server, Postgres, MySQL, Oracle, Snowflake, BigQuery when there are no referencing FKs.
• Empty a referenced table (Postgres). TRUNCATE TABLE orders RESTART IDENTITY CASCADE; — also resets the bound sequence and truncates referencing tables. Use only when downstream tables really should be emptied too.
• SQL Server reset identity after DELETE. DBCC CHECKIDENT('orders', RESEED, 0); — resets the IDENTITY seed without touching the rows. Necessary if you used DELETE (which preserves the counter) but want a fresh numbering.
• Conditional delete with WHERE. DELETE FROM orders WHERE created_at < '2025-01-01'; — the only destructive verb with a predicate. Batch in 50k-row chunks if the table is large and CDC-tracked.
• Drop only if it exists. DROP TABLE IF EXISTS staging.orders; — idempotent across reruns; portable to SQL Server (IF OBJECT_ID(...) IS NOT NULL DROP TABLE), MySQL, Postgres, Snowflake, BigQuery.
• Soft-delete pattern. UPDATE orders SET is_deleted = 1 WHERE ...; + a filtered index WHERE is_deleted = 0. Required when audit / compliance demand the original row is preserved.
• Bulk archive then truncate. INSERT INTO archive.orders SELECT * FROM orders WHERE ...; TRUNCATE TABLE orders; — the "purge with offsite preservation" pattern. Always run the archive INSERT first inside the same transaction (where supported).
• Postgres TRUNCATE with sequence reset. TRUNCATE TABLE orders RESTART IDENTITY; — Postgres preserves sequences by default; this clause flips that off.
• SQL Server reseed-after-truncate (set to a specific value). DBCC CHECKIDENT('orders', RESEED, 5000); — sets the next IDENTITY value to 5001. Use when archived data already occupies 1–5000.
• MySQL purge with rollback safety. START TRANSACTION; DELETE FROM orders WHERE ...; COMMIT; — TRUNCATE in MySQL is implicit-commit, so use DELETE if you need to be able to bail out.
• Partition swap as the warehouse-scale alternative. ALTER TABLE events SWITCH PARTITION 1 TO events_old; DROP TABLE events_old; — O(1) replacement for "DELETE the oldest 90 days" on partitioned tables.
• Disable + truncate + re-enable FK (SQL Server, risky). ALTER TABLE child NOCHECK CONSTRAINT fk; TRUNCATE TABLE parent; ALTER TABLE child WITH CHECK CHECK CONSTRAINT fk; — the last step is critical; without WITH CHECK, the FK is permanently untrusted.

Frequently asked questions

Can I roll back a TRUNCATE?

It depends on the dialect. SQL Server, Postgres, and Snowflake treat TRUNCATE as a transactional DDL operation — wrap it in BEGIN ... ROLLBACK and the rows reappear. MySQL and Oracle perform an implicit commit before and after TRUNCATE, so any open transaction is closed and the truncate cannot be rolled back. Oracle's only practical undo is FLASHBACK TABLE ... TO BEFORE DROP (requires flashback to be enabled); Snowflake offers Time-Travel within the retention window even outside a transaction. The interview-safe answer is "yes in SQL Server, Postgres, Snowflake; no in MySQL and Oracle."

Does TRUNCATE reset the identity / sequence column?

It depends on the dialect, and this is the most-asked dialect surprise. SQL Server resets IDENTITY to the seed value. MySQL InnoDB resets AUTO_INCREMENT to 1. Postgres preserves the sequence by default — you need TRUNCATE TABLE t RESTART IDENTITY to also reset the bound sequence. Oracle never resets a sequence with TRUNCATE because sequences are independent schema objects. Snowflake and BigQuery do not have native identity columns the way SQL Server / MySQL do, so the question does not apply. If your archived data overlaps the next-value range, the SQL Server / MySQL default behaviour is a quiet correctness bug — either use DELETE (preserves the counter) or DBCC CHECKIDENT(..., RESEED, value) to set the seed explicitly.

Why won't TRUNCATE work on a table with a foreign key?

Because TRUNCATE is a metadata operation that bypasses per-row FK evaluation, and the engine cannot prove the truncate would not violate the FK contract. The check is structural — based on whether any FK references the target — not on whether the referencing table actually has rows. Even an empty child table blocks the truncate. The legitimate workarounds are: (a) use DELETE instead, which honours ON DELETE CASCADE / RESTRICT / SET NULL per row; (b) Postgres' TRUNCATE TABLE t CASCADE, which truncates the referencing tables too (dangerous!); (c) on SQL Server, disable the FK, truncate, then re-enable with WITH CHECK CHECK CONSTRAINT (forgetting the WITH CHECK leaves the FK in an untrusted state).

Is DROP faster than TRUNCATE?

Marginally — DROP writes one metadata change (removing the object from sys.objects / pg_class) plus one extent-pool release, while TRUNCATE writes one log record per extent deallocated. On a small table the difference is negligible; on a large table DROP is still O(1) just like TRUNCATE, but with strictly less work. The reason to prefer TRUNCATE over DROP is schema retention: TRUNCATE keeps the table, its indexes, its triggers, its permissions, and its statistics. DROP loses all of those — the next workload that references the table fails with "object not found" until you recreate it. Pick TRUNCATE when the table will be immediately reused; pick DROP when it should not exist.

Does TRUNCATE fire triggers?

TRUNCATE bypasses row-level triggers entirely on every dialect — there is no AFTER DELETE event per row because the rows are not individually deleted. Postgres adds a separate BEFORE TRUNCATE / AFTER TRUNCATE statement-level trigger family that fires once per truncate statement, useful for audit logging at the statement level. SQL Server fires DDL triggers (the DDL_TABLE_EVENTS family) on TRUNCATE in some versions but not the row-level DML triggers. If your audit / soft-delete / change-tracking system is built on AFTER DELETE, swapping to TRUNCATE silently disables it. The fix is either to keep DELETE (per-row triggers fire) or to bolt on Postgres' statement-level truncate trigger to write a single audit row per truncate.

TRUNCATE vs DELETE in replication — what should I expect?

DELETE replicates as one event per row to the binlog (MySQL), WAL (Postgres logical replication), or change-tracking capture instance (SQL Server). Downstream consumers (Debezium, transactional replication, CDC connectors) receive per-row delete events and replay them deterministically. TRUNCATE replicates differently per engine: SQL Server 2016+ ships it as a DDL event in transactional replication; Postgres logical replication propagates a single TRUNCATE message; MySQL row-based binlog writes a TRUNCATE query event. The silent-bug surface is downstream consumers that filter for op = "d" (DELETE) — they skip TRUNCATE events and report zero deletions. On any CDC-tracked or replicated table, either keep DELETE (per-row events are needed) or explicitly update the downstream consumer to handle the TRUNCATE event type. This is the most common production surprise of the trio.

Practice on PipeCode

• Drill the SQL practice library → for the broad surface of DDL / DML / TRUNCATE / DELETE probes.
• Sharpen database design problems → for the FK / partitioning / identity decisions that drive destructive-op choices.
• Rehearse on CASE expression problems → when the interviewer wants conditional logic around archive / soft-delete patterns.
• Layer the aggregation library → for "after the purge, what does the metric look like?" follow-ups.
• Stack the joins practice library → for FK and CASCADE behaviour in INNER vs LEFT JOIN scenarios.
• For the broader surface, read top data engineering interview questions →.
• Stack the prerequisites with the only 5 skills you need to become a data engineer →.
• Sharpen the dialect axis with the SQL for data engineering interviews course →.
• For long-form schema craft, work through data modelling for DE interviews →.

Pipecode.ai is Leetcode for Data Engineering — every destructive-op recipe above ships with hands-on practice rooms where you write the guarded TRUNCATE, defend the DELETE on a CDC-tracked table, and reason about the FK block from first principles. PipeCode pairs every reading with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your nightly `sql truncate` will quietly break the downstream Snowflake mirror.

Practice SQL now →
Database design drills →