Source Rows → Match on Business Key → Update Changed → Insert Missing → Validate
Complete 12 of 24 practices (50%) and enter your name to unlock the Certificate of Participation.
An upsert solves a common pipeline problem: some incoming rows already exist in the target and need updates, while others are new and need inserts.
Think of a daily customer load: some customers are already in the master table and changed their status or city; others are brand new. One process must handle both cases safely.
-- Source rows arrive
-- Match rows on the chosen key
-- Update matched rows if values changed
-- Insert rows that do not exist
-- Validate row counts and duplicatesThe real value of an upsert is controlled synchronization, not just compact syntax.
Most upsert problems are really key problems. If the match key is weak, duplicated or unstable, the load can update the wrong row or create duplicates that look legitimate.
The source often arrives with a business key like EmployeeNumber, OrderNumber or CustomerCode. That is usually where the matching logic should begin.
SELECT CustomerCode, COUNT(*) AS Qty
FROM StagingCustomers
GROUP BY CustomerCode
HAVING COUNT(*) > 1;If the match key is wrong, the upsert logic is wrong even when the SQL syntax looks perfect.
In SQL Server, many teams prefer a straightforward two-step pattern: first update existing rows that match, then insert rows that do not exist.
When the load is easy to reason about, support and audit, production risk usually goes down.
UPDATE t
SET t.Status = s.Status,
t.City = s.City
FROM dbo.Customers t
JOIN dbo.StagingCustomers s
ON t.CustomerCode = s.CustomerCode;
INSERT INTO dbo.Customers (CustomerCode, Status, City)
SELECT s.CustomerCode, s.Status, s.City
FROM dbo.StagingCustomers s
LEFT JOIN dbo.Customers t
ON t.CustomerCode = s.CustomerCode
WHERE t.CustomerCode IS NULL;A simple two-step pattern is often easier to trust than one overly clever statement.
SQL Server offers MERGE to handle matched and unmatched logic in one statement. It can be elegant, but teams should use it carefully and test thoroughly.
MERGE can look attractive because it handles update and insert branches together, but careless MERGE logic can be harder to troubleshoot than a simple two-step pattern.
MERGE dbo.Customers AS t
USING dbo.StagingCustomers AS s
ON t.CustomerCode = s.CustomerCode
WHEN MATCHED THEN
UPDATE SET t.Status = s.Status,
t.City = s.City
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerCode, Status, City)
VALUES (s.CustomerCode, s.Status, s.City);MERGE is not bad by definition, but it deserves discipline, testing and simplicity.
Not every matched row needs an update. In many loads, you should update only when tracked values changed.
If the source and target values are the same, updating again may add transaction log activity without business value.
UPDATE t
SET t.Status = s.Status,
t.City = s.City,
t.ModifiedDate = GETDATE()
FROM dbo.Customers t
JOIN dbo.StagingCustomers s
ON t.CustomerCode = s.CustomerCode
WHERE ISNULL(t.Status,'') <> ISNULL(s.Status,'')
OR ISNULL(t.City,'') <> ISNULL(s.City,'');A quieter load is often a healthier load: update what truly changed, not everything you matched.
A safe upsert is not complete when the statement finishes. You should confirm that inserted rows, updated rows and exceptions align with expectations.
Data engineering discipline means checking counts, duplicate keys and strange anomalies instead of assuming the load was perfect.
SELECT CustomerCode, COUNT(*) AS Qty
FROM dbo.Customers
GROUP BY CustomerCode
HAVING COUNT(*) > 1;Professional loading includes post-load validation, not only SQL execution.
Production upserts often need transaction control, error handling and audit thinking. If a load partially fails, you want a controlled way to understand what happened and recover responsibly.
The point is not just to write rows. The point is to manage change safely in a real system with accountability.
BEGIN TRAN;
-- UPDATE step
-- INSERT step
-- validation logic
COMMIT TRAN;
/* or ROLLBACK if required */A mature upsert process protects the load before, during and after execution.
The safest long-term pattern is not just about syntax. It is a discipline: clean source data, reliable match keys, explicit logic, cautious use of MERGE, change-aware updates, and post-load validation.
If you can explain the key, the update rule, the insert rule, and the validation rule, you are already thinking like a data engineer.
1. Validate staging keys
2. Match on the right business key
3. Update only the intended rows
4. Insert only missing rows
5. Validate counts and duplicates
6. Audit and document the loadSafe upserts come from disciplined design and validation more than from any single SQL statement.
Open each item after answering it in your own words. The 24 interactive practices above drive certificate progress.
To synchronize incoming source rows.
Because it determines which row is considered the same business entity across source.
Because it is explicit, easier to reason about and often simpler to validate. Keep the logic simple, test carefully and make sure the matching behavi.
Validation of counts, duplicates, changes and operational impact, plus auditing.
2. Why is the match key so important?
3. Why do many teams prefer UPDATE then INSERT?
4. What is the key caution with MERGE?
5. What completes a professional upsert?
| Layer | Purpose |
|---|---|
| Staging Check | Validate source keys and duplicates |
| Match Rule | Use the correct business key to identify the same entity |
| Update Rule | Update existing rows, ideally only if values changed |
| Insert Rule | Insert source rows missing in the target |
| Validation | Check row counts, duplicates and expected impact |
Complete at least 12 of the 24 practice cases (50%) and enter your name.
Safe upserts are less about fancy syntax and more about controlled synchronization. The best designs use reliable keys, explicit rules, careful testing and strong validation after the load.