You've built a data pipeline that loads customer records from an upstream CRM into your analytics warehouse every night. The first load works perfectly. Then the second run hits — and suddenly you have duplicate rows, missed updates, and a support ticket from the BI team wondering why the revenue numbers doubled. Sound familiar? This is the classic bulk loading problem: getting data from a source into a target table when that target already has data, some of which may need to be updated, some inserted fresh, and some left completely alone.
The naive fix — truncate and reload — works at 10,000 rows. It stops working at 10 million. At that scale, you need upsert patterns: the ability to merge incoming data intelligently with what already exists. SQL gives you several tools for this: the ANSI-standard MERGE statement, PostgreSQL's INSERT ... ON CONFLICT, MySQL's INSERT ... ON DUPLICATE KEY UPDATE, and the incremental load patterns that frame all of these in a production-ready architecture. By the end of this lesson, you'll understand not just the syntax of each approach, but when to reach for each one, what the failure modes look like, and how to build pipelines that handle tens of millions of rows without breaking a sweat.
What you'll learn:
MERGE works under the hood and how to write defensible, production-ready MERGE statementsINSERT ... ON CONFLICT — its advantages over MERGE and its sharp edgesYou should be comfortable with:
SELECT, INSERT, UPDATE, and DELETE statementsWe'll be using PostgreSQL syntax as our primary dialect for INSERT ON CONFLICT examples, and T-SQL (SQL Server) syntax for MERGE examples where dialect differences matter. The patterns themselves translate across databases.
Before we touch MERGE or ON CONFLICT, we need to talk about staging tables, because every production upsert pattern you'll write should flow through one.
A staging table is a temporary holding area — typically a real database table, not a TEMP TABLE — where you land raw incoming data before reconciling it against your production target. Here's why this matters:
Without staging, your ETL tool or script is inserting rows one at a time (or in small batches), making decisions row-by-row against the live table. This means locks held for longer, retries on failure that may have partially succeeded, and no clean place to inspect what actually arrived before committing it.
With staging, your pipeline becomes a two-phase operation:
Here's what a typical staging setup looks like for a customer data pipeline:
-- Production target table
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
full_name VARCHAR(255),
status VARCHAR(50),
annual_revenue NUMERIC(15, 2),
updated_at TIMESTAMPTZ NOT NULL,
loaded_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Staging table: mirrors the source, plus pipeline metadata
CREATE TABLE customers_staging (
customer_id BIGINT NOT NULL,
email VARCHAR(255),
full_name VARCHAR(255),
status VARCHAR(50),
annual_revenue NUMERIC(15, 2),
updated_at TIMESTAMPTZ,
source_file VARCHAR(500), -- which file/batch this came from
staged_at TIMESTAMPTZ DEFAULT NOW(),
row_hash CHAR(64) -- SHA-256 of business columns, explained below
);
The row_hash column is something we'll use later to skip unchanged records during reconciliation — a crucial optimization at scale.
Your pipeline's load phase then becomes a simple COPY or bulk insert:
-- PostgreSQL: bulk load from a CSV extract
TRUNCATE customers_staging;
COPY customers_staging (customer_id, email, full_name, status, annual_revenue, updated_at, source_file)
FROM '/data/crm_export_20240915.csv'
WITH (FORMAT CSV, HEADER TRUE, DELIMITER ',', NULL '');
COPY in PostgreSQL is dramatically faster than INSERT statements for bulk loads — we're talking 10–50x throughput improvement for large files. In SQL Server, you'd use BULK INSERT or bcp. In Snowflake, you'd use COPY INTO from a stage.
After the bulk load, compute the row hash:
UPDATE customers_staging
SET row_hash = encode(
sha256(
(customer_id::TEXT || '|' ||
COALESCE(email, '') || '|' ||
COALESCE(full_name, '') || '|' ||
COALESCE(status, '') || '|' ||
COALESCE(annual_revenue::TEXT, '') || '|' ||
COALESCE(updated_at::TEXT, ''))::bytea
),
'hex'
);
Why hash the row? When you're loading 5 million customer records and only 12,000 actually changed since yesterday, applying updates to all 5 million rows is enormously wasteful — it generates write amplification, blows out indexes, and can trigger unnecessary downstream processing. The hash lets you skip the 4,988,000 unchanged rows entirely.
Now we have clean, enriched staging data. Let's merge it.
MERGE (also called "upsert" from "update/insert") was introduced in SQL:2003 and is supported in SQL Server, Oracle, DB2, and (as of PostgreSQL 15) in PostgreSQL. The syntax looks imposing at first, but it follows a clear logical structure.
Here's a complete, production-grade MERGE statement for our customer pipeline:
-- SQL Server / T-SQL syntax
MERGE customers AS target
USING (
SELECT
s.customer_id,
s.email,
s.full_name,
s.status,
s.annual_revenue,
s.updated_at,
s.row_hash
FROM customers_staging s
-- Only process rows where something actually changed
-- or where the customer doesn't exist yet
LEFT JOIN customers t ON t.customer_id = s.customer_id
WHERE t.customer_id IS NULL -- new customers
OR t.row_hash != s.row_hash -- changed customers
-- Note: if you're not storing row_hash on target,
-- compare updated_at instead:
-- OR t.updated_at < s.updated_at
) AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
UPDATE SET
target.email = source.email,
target.full_name = source.full_name,
target.status = source.status,
target.annual_revenue = source.annual_revenue,
target.updated_at = source.updated_at,
target.row_hash = source.row_hash,
target.loaded_at = GETUTCDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT (customer_id, email, full_name, status, annual_revenue,
updated_at, row_hash, loaded_at)
VALUES (source.customer_id, source.email, source.full_name,
source.status, source.annual_revenue,
source.updated_at, source.row_hash, GETUTCDATE())
WHEN NOT MATCHED BY SOURCE THEN
-- Customers in target that weren't in staging
-- Careful here! See the warning below.
UPDATE SET target.status = 'SOFT_DELETED',
target.loaded_at = GETUTCDATE();
Let's break down the three WHEN clauses:
WHEN MATCHED: The join key (customer_id) exists in both source and target. Here you write your update logic. Notice we filtered in the USING subquery to only include rows that actually changed — this means WHEN MATCHED only fires for genuinely changed records.
WHEN NOT MATCHED BY TARGET: The source has a record the target doesn't — this is a net-new customer. Fire an insert.
WHEN NOT MATCHED BY SOURCE: The target has a record the source doesn't. This is the dangerous clause.
⚠️ Warning — The
NOT MATCHED BY SOURCEtrap: This clause only makes sense when your source represents a complete picture of reality (a full extract). If your staging table contains only changed records (a delta extract), this clause will soft-delete or hard-delete every customer who happened not to change since the last run. This has destroyed production data more than once. If you're running incremental loads, either omit this clause entirely or add a filter likeAND target.status != 'SOFT_DELETED'— and even then, be very careful.
To make the hash comparison work in the USING subquery, you need to store row_hash on the target. Add it to the customers table:
ALTER TABLE customers ADD COLUMN row_hash CHAR(64);
-- Backfill for existing rows (run once during migration)
UPDATE customers
SET row_hash = encode(
sha256(
(customer_id::TEXT || '|' ||
COALESCE(email, '') || '|' ||
COALESCE(full_name, '') || '|' ||
COALESCE(status, '') || '|' ||
COALESCE(annual_revenue::TEXT, '') || '|' ||
COALESCE(updated_at::TEXT, ''))::bytea
),
'hex'
);
PostgreSQL took a different path than ANSI MERGE. Before PostgreSQL 15 added MERGE, the community relied on INSERT ... ON CONFLICT, introduced in PostgreSQL 9.5. Even with MERGE now available, ON CONFLICT remains the preferred tool for many patterns because it's simpler, more predictable, and has better concurrency behavior in high-write environments.
INSERT INTO customers (customer_id, email, full_name, status,
annual_revenue, updated_at, loaded_at)
SELECT
customer_id, email, full_name, status,
annual_revenue, updated_at, NOW()
FROM customers_staging
ON CONFLICT (customer_id) DO UPDATE
SET email = EXCLUDED.email,
full_name = EXCLUDED.full_name,
status = EXCLUDED.status,
annual_revenue = EXCLUDED.annual_revenue,
updated_at = EXCLUDED.updated_at,
loaded_at = NOW()
WHERE customers.updated_at < EXCLUDED.updated_at;
The EXCLUDED pseudo-table refers to the row that was proposed for insertion but conflicted. The WHERE clause on DO UPDATE is gold: it means "only apply the update if the incoming record is newer than what we already have." This is exactly how you handle late-arriving data — we'll come back to this.
Sometimes you just want to insert records that don't exist and skip the rest without error:
INSERT INTO events (event_id, user_id, event_type, occurred_at)
SELECT event_id, user_id, event_type, occurred_at
FROM events_staging
ON CONFLICT (event_id) DO NOTHING;
This is perfect for event/fact tables where records are immutable once written. You're loading a batch that might overlap with yesterday's batch on the edges — DO NOTHING makes the operation fully idempotent.
ON CONFLICT can target a partial index, which is a genuinely powerful feature. Say you have a table of product SKUs where uniqueness is only enforced for active products:
CREATE UNIQUE INDEX uix_products_sku_active
ON products (sku)
WHERE status = 'ACTIVE';
INSERT INTO products (sku, name, price, status)
VALUES ('GADGET-4200', 'Quantum Gadget', 299.99, 'ACTIVE')
ON CONFLICT ON CONSTRAINT uix_products_sku_active DO UPDATE
SET price = EXCLUDED.price,
name = EXCLUDED.name,
updated_at = NOW();
Tip:
ON CONFLICTrequires that the conflict target (the column or index it checks against) have a unique constraint or unique index on it. You can't use it against arbitrary column combinations without that index in place. This is a common gotcha.
The staging-and-merge pattern answers how to load. Incremental load strategies answer what to load. Getting this wrong means either loading too much (slow, expensive) or too little (missing changes).
The most common pattern: query the source for records where updated_at is greater than the last successful load's high-water mark.
-- Step 1: Retrieve the current watermark
SELECT MAX(updated_at) AS last_loaded
FROM pipeline_watermarks
WHERE pipeline_name = 'crm_customers';
-- Step 2: Extract from source using the watermark
-- (This runs on the source system or via a linked server / FDW)
INSERT INTO customers_staging (customer_id, email, full_name,
status, annual_revenue, updated_at, source_file)
SELECT customer_id, email, full_name, status, annual_revenue, updated_at,
'incremental_20240916' AS source_file
FROM crm.customers
WHERE updated_at > '2024-09-15 02:00:00'::TIMESTAMPTZ -- the retrieved watermark
AND updated_at <= NOW() - INTERVAL '5 minutes'; -- safety buffer
-- Step 3: Merge (as shown above)
-- Step 4: Advance the watermark (only on success)
UPDATE pipeline_watermarks
SET last_loaded = (SELECT MAX(updated_at) FROM customers_staging),
updated_at = NOW()
WHERE pipeline_name = 'crm_customers';
The "safety buffer" on the upper bound (NOW() - INTERVAL '5 minutes') prevents a race condition where records are being written to the source as you're reading it. Without this, you might see the same record in two consecutive loads with different values.
⚠️ The watermark gotcha: Watermark-based loads silently miss deletes. If a customer is deleted from the source, their
updated_atdoesn't advance — they just disappear. You need a separate delete tracking mechanism (a soft-delete flag, a deletion log table, or a CDC feed) to catch these. We'll address this in the next section.
Change Data Capture (CDC) reads the database transaction log to capture inserts, updates, and deletes as they happen. Most modern databases support this: PostgreSQL's logical replication slots, SQL Server's CDC feature, MySQL's binlog. Tools like Debezium, Airbyte, and Fivetran expose CDC as a stream.
What arrives from a CDC feed looks like this:
-- CDC events landing in a staging table
CREATE TABLE customers_cdc_staging (
event_id BIGSERIAL PRIMARY KEY,
operation CHAR(1) NOT NULL, -- 'I' (insert), 'U' (update), 'D' (delete)
customer_id BIGINT NOT NULL,
email VARCHAR(255),
full_name VARCHAR(255),
status VARCHAR(50),
annual_revenue NUMERIC(15, 2),
updated_at TIMESTAMPTZ,
cdc_timestamp TIMESTAMPTZ NOT NULL,
lsn VARCHAR(100) -- log sequence number for ordering
);
Applying CDC events requires ordering matters — you need the last operation on each key within a batch, not just any operation:
-- Get the final state of each customer from this CDC batch
WITH ranked_events AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY cdc_timestamp DESC, lsn DESC
) AS rn
FROM customers_cdc_staging
)
SELECT * FROM ranked_events WHERE rn = 1;
Then merge the final states:
-- PostgreSQL: apply CDC batch
WITH final_states AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY cdc_timestamp DESC
) AS rn
FROM customers_cdc_staging
),
deduped AS (
SELECT * FROM final_states WHERE rn = 1
)
-- Handle deletes separately
DELETE FROM customers
WHERE customer_id IN (
SELECT customer_id FROM deduped WHERE operation = 'D'
);
-- Handle inserts and updates
INSERT INTO customers (customer_id, email, full_name, status,
annual_revenue, updated_at, loaded_at)
SELECT customer_id, email, full_name, status, annual_revenue, updated_at, NOW()
FROM deduped
WHERE operation IN ('I', 'U')
ON CONFLICT (customer_id) DO UPDATE
SET email = EXCLUDED.email,
full_name = EXCLUDED.full_name,
status = EXCLUDED.status,
annual_revenue = EXCLUDED.annual_revenue,
updated_at = EXCLUDED.updated_at,
loaded_at = NOW()
WHERE customers.updated_at <= EXCLUDED.updated_at;
Raw data arriving from external systems is almost never perfectly deduplicated. You might receive the same customer_id twice in the same file because an export ran twice, a retry happened, or the source system has its own bugs. Feeding duplicates into your merge operation causes non-deterministic results.
Deduplicate in staging before merging:
-- Option 1: Delete-based deduplication (keeps the latest by updated_at)
DELETE FROM customers_staging
WHERE staged_at NOT IN (
SELECT MAX(staged_at)
FROM customers_staging
GROUP BY customer_id
);
-- Option 2: Use a CTE with ROW_NUMBER (more explicit and auditable)
WITH dupes AS (
SELECT ctid, -- PostgreSQL physical row ID
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY updated_at DESC, staged_at DESC
) AS rn
FROM customers_staging
)
DELETE FROM customers_staging
WHERE ctid IN (SELECT ctid FROM dupes WHERE rn > 1);
Tip: Run your deduplication query with
SELECTfirst (replacingDELETEwith a count) so you can see how many duplicates you're dealing with. If it's more than a few percent, investigate the source — you may have an upstream pipeline issue.
Here's a counterintuitive rule: don't index your staging table aggressively. When you're bulk-loading millions of rows via COPY or BULK INSERT, every index you have on the staging table gets updated for every row. Fewer indexes on staging = dramatically faster loads.
Practical approach:
customer_id) if needed for the merge queryFor the target table, make sure your merge join column is your primary key or has a unique index — otherwise your MERGE or ON CONFLICT won't be able to do an efficient index lookup for each incoming row.
Merging 50 million rows in a single transaction is a recipe for lock escalation, transaction log bloat (in SQL Server), and a rollback that takes longer than the forward run. Batch it:
-- PostgreSQL: process staging in batches of 500,000
DO $$
DECLARE
batch_size INT := 500000;
offset_val INT := 0;
rows_processed INT;
BEGIN
LOOP
WITH batch AS (
SELECT customer_id, email, full_name, status,
annual_revenue, updated_at
FROM customers_staging
ORDER BY customer_id
LIMIT batch_size OFFSET offset_val
)
INSERT INTO customers (customer_id, email, full_name, status,
annual_revenue, updated_at, loaded_at)
SELECT customer_id, email, full_name, status, annual_revenue,
updated_at, NOW()
FROM batch
ON CONFLICT (customer_id) DO UPDATE
SET email = EXCLUDED.email,
full_name = EXCLUDED.full_name,
status = EXCLUDED.status,
annual_revenue = EXCLUDED.annual_revenue,
updated_at = EXCLUDED.updated_at,
loaded_at = NOW()
WHERE customers.updated_at < EXCLUDED.updated_at;
GET DIAGNOSTICS rows_processed = ROW_COUNT;
EXIT WHEN rows_processed = 0;
offset_val := offset_val + batch_size;
COMMIT; -- commit each batch
RAISE NOTICE 'Processed batch at offset %, % rows', offset_val, rows_processed;
END LOOP;
END;
$$;
Warning: Batching means partial success is possible. If batch 7 of 20 fails, batches 1–6 are already committed. Your pipeline needs to either track which batches succeeded (resumable loads) or be designed to be safe to re-run from the beginning. The
ON CONFLICT DO UPDATE WHERE updated_at < EXCLUDED.updated_atguard makes re-running safe for updates — already-applied updates won't fire again.
For very large datasets, split your staging data into segments and load them in parallel. The simplest partition: split on the last digit of the key.
-- Process segment 0 of 4 (keys ending in 0, 4, 8)
INSERT INTO customers (...)
SELECT ...
FROM customers_staging
WHERE customer_id % 4 = 0 -- segment assignment
ON CONFLICT (customer_id) DO UPDATE ...;
Run four such statements in parallel connections. Since they operate on non-overlapping key ranges, there's no row-level lock contention between them.
Let's build a complete end-to-end pipeline that handles inserts, updates, deletes (via soft-delete tracking), and deduplication. You'll need PostgreSQL 14+ (or 15+ if you want to test the ANSI MERGE syntax).
-- Target table
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
full_name VARCHAR(255),
tier VARCHAR(50) DEFAULT 'STANDARD',
mrr NUMERIC(12, 2),
is_deleted BOOLEAN DEFAULT FALSE,
updated_at TIMESTAMPTZ NOT NULL,
loaded_at TIMESTAMPTZ DEFAULT NOW(),
row_hash CHAR(64)
);
-- Staging table
CREATE TABLE customers_staging (
customer_id BIGINT,
email VARCHAR(255),
full_name VARCHAR(255),
tier VARCHAR(50),
mrr NUMERIC(12, 2),
updated_at TIMESTAMPTZ,
is_deleted BOOLEAN DEFAULT FALSE,
staged_at TIMESTAMPTZ DEFAULT NOW(),
row_hash CHAR(64)
);
-- Watermark tracking
CREATE TABLE pipeline_watermarks (
pipeline_name VARCHAR(100) PRIMARY KEY,
last_loaded_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ DEFAULT NOW()
);
INSERT INTO pipeline_watermarks (pipeline_name, last_loaded_at)
VALUES ('crm_customers', '2000-01-01 00:00:00+00');
-- Simulate the first full load
INSERT INTO customers_staging
(customer_id, email, full_name, tier, mrr, updated_at, is_deleted)
VALUES
(1001, 'amara.diallo@techcorp.com', 'Amara Diallo', 'ENTERPRISE', 4500.00, '2024-08-01 10:00:00+00', FALSE),
(1002, 'jason.park@mediahouse.io', 'Jason Park', 'GROWTH', 890.00, '2024-08-03 14:22:00+00', FALSE),
(1003, 'priya.mehta@cloudfive.net', 'Priya Mehta', 'STANDARD', 199.00, '2024-08-05 09:10:00+00', FALSE),
(1004, 'carlos.ruiz@buildfast.co', 'Carlos Ruiz', 'GROWTH', 450.00, '2024-08-07 16:45:00+00', FALSE),
(1005, 'sasha.okonkwo@datasync.ai', 'Sasha Okonkwo', 'ENTERPRISE', 7200.00, '2024-08-10 11:30:00+00', FALSE);
-- Compute row hashes
UPDATE customers_staging
SET row_hash = encode(sha256(
(customer_id::TEXT || email || COALESCE(full_name,'') ||
COALESCE(tier,'') || COALESCE(mrr::TEXT,'') ||
updated_at::TEXT || is_deleted::TEXT)::bytea
), 'hex');
INSERT INTO customers
(customer_id, email, full_name, tier, mrr, is_deleted, updated_at, row_hash, loaded_at)
SELECT
customer_id, email, full_name, tier, mrr, is_deleted, updated_at, row_hash, NOW()
FROM customers_staging
ON CONFLICT (customer_id) DO UPDATE
SET email = EXCLUDED.email,
full_name = EXCLUDED.full_name,
tier = EXCLUDED.tier,
mrr = EXCLUDED.mrr,
is_deleted = EXCLUDED.is_deleted,
updated_at = EXCLUDED.updated_at,
row_hash = EXCLUDED.row_hash,
loaded_at = NOW()
WHERE customers.updated_at < EXCLUDED.updated_at;
-- Advance watermark
UPDATE pipeline_watermarks
SET last_loaded_at = (SELECT MAX(updated_at) FROM customers_staging),
updated_at = NOW()
WHERE pipeline_name = 'crm_customers';
-- Verify
SELECT customer_id, email, tier, mrr, is_deleted, loaded_at
FROM customers
ORDER BY customer_id;
-- Simulate what arrives in the next delta extract:
-- Customer 1002 upgraded their tier
-- Customer 1003 cancelled (soft delete)
-- Customer 1006 is brand new
TRUNCATE customers_staging;
INSERT INTO customers_staging
(customer_id, email, full_name, tier, mrr, updated_at, is_deleted)
VALUES
(1002, 'jason.park@mediahouse.io', 'Jason Park', 'ENTERPRISE', 2400.00, '2024-09-15 08:00:00+00', FALSE),
(1003, 'priya.mehta@cloudfive.net', 'Priya Mehta', 'STANDARD', 0.00, '2024-09-15 09:00:00+00', TRUE),
(1006, 'finn.larsen@nordic-saas.dk','Finn Larsen', 'GROWTH', 750.00, '2024-09-15 10:00:00+00', FALSE);
UPDATE customers_staging
SET row_hash = encode(sha256(
(customer_id::TEXT || email || COALESCE(full_name,'') ||
COALESCE(tier,'') || COALESCE(mrr::TEXT,'') ||
updated_at::TEXT || is_deleted::TEXT)::bytea
), 'hex');
-- Apply the incremental merge
INSERT INTO customers
(customer_id, email, full_name, tier, mrr, is_deleted, updated_at, row_hash, loaded_at)
SELECT
customer_id, email, full_name, tier, mrr, is_deleted, updated_at, row_hash, NOW()
FROM customers_staging
ON CONFLICT (customer_id) DO UPDATE
SET email = EXCLUDED.email,
full_name = EXCLUDED.full_name,
tier = EXCLUDED.tier,
mrr = EXCLUDED.mrr,
is_deleted = EXCLUDED.is_deleted,
updated_at = EXCLUDED.updated_at,
row_hash = EXCLUDED.row_hash,
loaded_at = NOW()
WHERE customers.updated_at < EXCLUDED.updated_at;
-- Verify the results
SELECT customer_id, email, tier, mrr, is_deleted, updated_at
FROM customers
ORDER BY customer_id;
Expected output: Customer 1001, 1004, 1005 are unchanged. Customer 1002 shows ENTERPRISE tier and mrr = 2400.00. Customer 1003 shows is_deleted = TRUE. Customer 1006 is new.
MERGE operations can partially succeed mid-statement if the source query is large and something times out. Always wrap in an explicit transaction:
BEGIN TRANSACTION;
MERGE customers AS target
USING customers_staging AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN UPDATE ...
WHEN NOT MATCHED BY TARGET THEN INSERT ...;
UPDATE pipeline_watermarks SET ...;
COMMIT;
If the merge succeeds but the watermark update fails, you'll reprocess records next run. That's recoverable. If the merge partially applies, your data is inconsistent.
MERGE in SQL Server (and ANSI) throws an error if multiple source rows match the same target row. This is the "duplicate key in MERGE source" error, and it's catastrophically unfriendly in production.
The MERGE statement attempted to UPDATE or DELETE the same row more than once.
Prevent this by always deduplicating your source before the USING clause:
MERGE customers AS target
USING (
SELECT DISTINCT ON (customer_id) -- PostgreSQL 15+
customer_id, email, full_name, status, annual_revenue, updated_at
FROM customers_staging
ORDER BY customer_id, updated_at DESC
) AS source
ON target.customer_id = source.customer_id
...
In T-SQL, use ROW_NUMBER() in a CTE instead of DISTINCT ON.
If annual_revenue is NULL for some rows, NULL::TEXT is NULL, and string concatenation with NULL returns NULL in SQL. Your hash becomes NULL for any row with a null column. Always use COALESCE:
-- Wrong: NULL propagates through the concatenation
sha256((customer_id::TEXT || annual_revenue::TEXT)::bytea)
-- Right: Replace NULLs with a sentinel value
sha256((customer_id::TEXT || COALESCE(annual_revenue::TEXT, 'NULL'))::bytea)
Without the WHERE customers.updated_at < EXCLUDED.updated_at guard on ON CONFLICT DO UPDATE, a re-run of the same batch will re-write every row, even unchanged ones. At scale, this tanks performance. More importantly, it can overwrite a newer value with an older one if your pipeline is running out of order.
PostgreSQL's MERGE (15+) has some documented concurrency edge cases when multiple sessions run MERGE against the same target simultaneously. In high-concurrency environments, INSERT ON CONFLICT is still the safer choice — it uses more targeted locking and is better optimized in PostgreSQL's execution engine.
You now have a complete mental model for production bulk loading in SQL:
The staging table pattern is the foundation. Always land data in a staging table first — it gives you a place to inspect, deduplicate, and hash-filter before touching production.
Row hashing is the key optimization for large incremental loads. Computing a hash of business columns lets you skip unchanged rows entirely, turning a full-table update into a selective operation.
MERGE is the ANSI standard and gives you the cleanest syntax for insert/update/delete in one statement. Watch out for duplicate source rows and always wrap in a transaction. Use it confidently in SQL Server, Oracle, and PostgreSQL 15+.
INSERT ... ON CONFLICT is PostgreSQL's more battle-tested approach. The WHERE clause on DO UPDATE is your late-arrival guard. DO NOTHING gives you idempotent inserts for fact tables.
Incremental load strategies — watermark-based and CDC-based — control what gets loaded. Watermark-based is simpler but misses deletes. CDC is more complete but requires infrastructure.
Batching and indexing are your primary performance levers. Fewer indexes on staging tables, batch large merges to control transaction size, and parallelize across non-overlapping key ranges when you need maximum throughput.
Learning Path: Advanced SQL Queries