The short answer
In Azure Synapse Dedicated SQL Pool, all transactions are logged to guarantee ACID compliance. However, because Synapse is designed for analytical bulk processing rather than row-by-row updates, running massive single transactions can overwhelm the transaction log and stall the cluster.
What causes transaction log bottlenecks
- Massive UPDATE or DELETE statements: Running
DELETE FROM fact_sales WHERE year = 2021;on a table with 200 million rows writes individual delete markers and log records across all 60 distributions. If the transaction log space fills up before the command finishes, Synapse aborts the query and initiates a rollback. - Rollback penalties: Rolling back a massive distributed transaction can take 2 to 3 times longer than the time the query originally ran. During rollback, the table remains locked, preventing other queries from executing.
The CTAS partition swap pattern
To avoid transaction log bottlenecks, experienced data engineers replace large DELETE and UPDATE statements with minimally logged operations using CREATE TABLE AS SELECT (CTAS):
-- Step 1: Create a new table containing only the rows you want to retain
CREATE TABLE dbo.fact_sales_new
WITH (
DISTRIBUTION = HASH(customer_id),
CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT * FROM dbo.fact_sales
WHERE order_date >= '2022-01-01'; -- Excludes the 2021 data!
-- Step 2: Swap the tables using metadata renames
RENAME OBJECT dbo.fact_sales TO fact_sales_old;
RENAME OBJECT dbo.fact_sales_new TO fact_sales;
-- Step 3: Drop the old table
DROP TABLE dbo.fact_sales_old;CTAS is a minimally logged bulk operation. It writes clean, pre-compressed columnar segments directly to storage, bypassing the row-by-row transaction log and completing in a fraction of the time.