Transactions and upserts

Make several changes succeed or fail together, and insert-or-update rows in one statement.

The last lesson in the track covers two tools for changing data safely: transactions, which make a group of changes all-or-nothing, and upserts, which insert new rows and update existing ones in a single statement.

Transactions: all or nothing

Moving a deal between reps might take two statements: update the deal, then log the change. If the first runs and the second fails, the data is half-changed. A transaction prevents that:

BEGIN;                                           -- start: nothing is permanent yet
UPDATE opportunities SET owner_id = 13 WHERE opportunity_id = 1003;
INSERT INTO opportunity_history (opportunity_id, stage, changed_at)
VALUES (1003, 'Qualification', '2026-11-02 09:00:00');
COMMIT;                                          -- make both changes permanent together

ROLLBACK: the undo button

BEGIN;
UPDATE accounts SET segment = 'Enterprise';                    -- oops: no WHERE
SELECT COUNT(*) FROM accounts WHERE segment = 'Enterprise';    -- 14: every account
ROLLBACK;
SELECT COUNT(*) FROM accounts WHERE segment = 'Enterprise';    -- 3: back to normal

Lesson 16 said UPDATE without WHERE usually has no undo. Inside a transaction, it does, as long as you ROLLBACK before you COMMIT. A safe habit for risky changes: BEGIN, run the change, check it with a SELECT, then COMMIT if it’s right or ROLLBACK if it isn’t.

If anything fails inside a transaction, roll back. Most tools do it for you when a statement errors.

Upserts: insert or update

Loading data often means “add these rows, but if a row already exists, update it instead”. A plain INSERT fails on the existing rows:

INSERT INTO accounts VALUES (4, 'Birch Dental Group', 'Healthcare', 'Mid-Market', 'South');
-- error: account_id 4 already exists

An upsert (update + insert) handles both in one statement:

INSERT INTO accounts (account_id, name, industry, segment, region)
VALUES (4,  'Birch Dental Group',  'Healthcare', 'Mid-Market', 'South'),
       (15, 'Juniper Vet Clinics', 'Healthcare', 'SMB',        'North')
ON CONFLICT (account_id) DO UPDATE
   SET segment = excluded.segment,
       region  = excluded.region;

Account 15 is new, so it’s inserted. Account 4 already exists, so instead of an error, its segment is updated from SMB to Mid-Market.

Only the columns you list in SET change. Here the name and industry of account 4 stay as they were.

Why not DELETE then INSERT?

Deleting everything and reloading works, but anything the delete removes is gone if the insert fails halfway, and other tables pointing at those rows can break. An upsert only touches what changed. On big tables, it’s also far less work.

Common mistakes

Dialect notes

MERGE is the standard, longer form. It matches a source to a target and says what to do in each case. SQLite doesn’t have it, so this one won’t run in the sandbox:

MERGE INTO accounts AS tgt
USING staging_accounts AS src
   ON tgt.account_id = src.account_id
WHEN MATCHED THEN
     UPDATE SET segment = src.segment, region = src.region
WHEN NOT MATCHED THEN
     INSERT (account_id, name, industry, segment, region)
     VALUES (src.account_id, src.name, src.industry, src.segment, src.region);

Snowflake notes

Snowflake uses MERGE for upserts, with the syntax above. It has no ON CONFLICT, partly because it doesn’t enforce primary keys (lesson 37). So make sure the source has one row per key, or MERGE can fail or update a row twice. Transactions use BEGIN / COMMIT / ROLLBACK. Snowflake also has Time Travel: SELECT ... FROM accounts AT(OFFSET => -3600) reads a table as it was an hour ago, and UNDROP TABLE restores a dropped table. In dbt, incremental models generate the MERGE for you.

More in the field guide: MERGE (upsert), Transactions and the MERGE breakdown.

Try it

Load two account records with one upsert. Account 4, Birch Dental Group, already exists and has moved up to Mid-Market, still in the South region. Account 15 is new: Juniper Vet Clinics, Healthcare, SMB, North. Then return account_id, name, segment and region for those two accounts, sorted by account_id.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson