skip to content

How do you apply a daily batch of inserts and updates to a large Redshift fact table?

level: seniorimportance: must knowfreq 65%

answer

  1. land it somewhere else first
  2. one row per key before you apply
  3. readers must never see the gap
  4. there is no in-place update here
  5. declared keys are hints, not rules

basics

~20 s

COPY the batch into a staging table, reduce it to one row per key, then apply it in a single transaction with MERGE, or with DELETE ... USING followed by INSERT. Never update the fact table row by row.

solid answer

~50 s

The standard shape is: `COPY` the batch from S3 into a staging table that shares the target's distribution key, deduplicate it to exactly one row per business key, then apply it as one atomic step. Modern Redshift has `MERGE INTO target USING staging ON ...` with `WHEN MATCHED THEN UPDATE` and `WHEN NOT MATCHED THEN INSERT`; the older, equally valid pattern is `BEGIN; DELETE FROM target USING staging WHERE key matches; INSERT INTO target SELECT * FROM staging; END;`. Wrapping it in one transaction matters because readers must never see the target with rows deleted and not yet reinserted. Physically, Redshift has no in-place update: every matched row is marked deleted and rewritten, so the batch leaves deleted rows occupying blocks and new rows in the unsorted region, which the cluster's vacuum and sort maintenance later reclaims. Redshift also does not enforce `PRIMARY KEY`, so a merge key bug silently duplicates rows.

code

sql · 22 lines
sql
-- 1. Land the batch beside the target, same distribution key
CREATE TABLE stg_sales (LIKE sales);

COPY stg_sales FROM 's3://landing/sales/2026-01-01/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoad'
FORMAT AS PARQUET;

-- 2. One row per key wins
CREATE TABLE stg_sales_dedup AS
SELECT sale_id, sale_ts, customer_id, channel, net_amount, updated_at
FROM (
  SELECT s.*, ROW_NUMBER() OVER (PARTITION BY sale_id ORDER BY updated_at DESC) AS rn
  FROM stg_sales s
)
WHERE rn = 1;

-- 3. Apply atomically
MERGE INTO sales
USING stg_sales_dedup s ON sales.sale_id = s.sale_id
WHEN MATCHED THEN UPDATE SET net_amount = s.net_amount, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT VALUES
  (s.sale_id, s.sale_ts, s.customer_id, s.channel, s.net_amount, s.updated_at);

go deeper

for a junior

Know the shape: load the batch into a staging table, then apply it to the target in one statement or one transaction. Never issue per-row updates against a large fact table.

for a middle

Explain why an update is physically a delete plus an append, why the staging table should share the target's distribution key, and why the apply must be atomic for concurrent readers.

for a senior

Own the whole batch: dedupe rules for an at-least-once feed, the cost left behind in deleted rows and unsorted data, when a rebuild beats a merge, and refreshing statistics afterwards.

for a principal

Set the freshness policy against its true cost. Argue the tradeoff between micro-batch merges and one daily apply, and decide where deduplication and idempotency live — in the pipeline or in the warehouse.

## Why upserts are a design question here, not a syntax question In an OLTP database an update rewrites a row in place. Redshift cannot: its 1 MB column blocks are immutable. An `UPDATE` is physically a delete plus an append — the old row is flagged as deleted and stays on disk consuming space, and a new version is written at the end of the table. That single fact drives everything about how batches are applied. ## The pattern **1. Land the batch.** `COPY` the day's file set from S3 into a staging table. Give the staging table the same distribution key as the target, so the join in the merge step is collocated and does not have to redistribute a large table across the network. ```sql CREATE TABLE stg_sales (LIKE sales); COPY stg_sales FROM 's3://landing/sales/2026-01-01/' IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoad' FORMAT AS PARQUET; ``` **2. Deduplicate the staging table.** Source feeds are usually at-least-once and often contain several versions of the same key within one batch. If two staging rows match one target row, the outcome of the merge is not well defined. Reduce first: ```sql CREATE TABLE stg_sales_dedup AS SELECT * FROM ( SELECT s.*, ROW_NUMBER() OVER (PARTITION BY sale_id ORDER BY updated_at DESC) AS rn FROM stg_sales s ) WHERE rn = 1; ``` **3. Apply atomically.** Either the merge statement: ```sql MERGE INTO sales USING stg_sales_dedup s ON sales.sale_id = s.sale_id WHEN MATCHED THEN UPDATE SET net_amount = s.net_amount, updated_at = s.updated_at WHEN NOT MATCHED THEN INSERT VALUES (s.sale_id, s.sale_ts, s.customer_id, s.channel, s.net_amount, s.updated_at); ``` or the classic delete-then-insert, which predates it and is still everywhere: ```sql BEGIN; DELETE FROM sales USING stg_sales_dedup s WHERE sales.sale_id = s.sale_id; INSERT INTO sales SELECT sale_id, sale_ts, customer_id, channel, net_amount, updated_at FROM stg_sales_dedup; END; ``` The transaction boundary is not optional. Run the delete and the insert as separate transactions and every concurrent reader in between sees a fact table missing exactly the rows you are updating — a silent, intermittent under-count in dashboards. ## What it costs physically - **Deleted rows linger.** Matched rows are marked deleted, not removed. They keep occupying blocks and are still read past during scans until space is reclaimed by vacuum maintenance, which Redshift runs automatically in the background but which still has to do the work. - **New rows land unsorted.** Appended rows sit outside the table's sorted region until a sort pass moves them, so block-skipping on the sort key degrades between maintenance runs. Sort key mechanics belong to the table-design conversation, but the batch process is what creates the pressure. - **Batch size beats batch frequency.** Because each application pays these costs, one large daily merge is far cheaper in total work than 288 five-minute merges of the same data. - **Wide updates can be worse than a rebuild.** If a batch touches a large share of the table, recreating the affected slice — `CREATE TABLE AS` over the union of surviving rows and new ones, then swapping names — can beat merging, because it produces a clean, sorted, fully compressed table in one pass. ## Constraints are not enforced Redshift accepts `PRIMARY KEY`, `UNIQUE` and `FOREIGN KEY` declarations and uses them as **planner hints only**. Nothing stops a duplicate from being stored. This means correctness of the batch rests entirely on your dedupe and merge keys, and a bug produces duplicated facts that no error message announces. Declaring the keys is still worthwhile — the optimizer trusts them — but declaring a key you do not actually guarantee will produce wrong query results, not just slow ones. ## Finishing the batch After a large apply, refresh the optimizer's statistics with `ANALYZE` so the planner's row estimates match reality; stale statistics after a big load are a common cause of a plan that was fine yesterday and terrible today. Then let the automatic maintenance reclaim space and restore sort order, or schedule it deliberately if your load window allows. ## What interviewers are listening for The staging table, the dedupe, the single transaction, and an understanding that an update is a rewrite. A candidate who reaches for a correlated `UPDATE ... FROM` row by row, or who runs delete and insert in separate transactions, has not operated one of these.

  • Why deduplicate the staging table before merging?
    Because a source feed is usually at-least-once and can carry several versions of one key in a single batch. If two staging rows match the same target row, which one wins is not something you should be relying on. Collapse to one row per key first — typically ROW_NUMBER() partitioned by the business key, ordered by an event or update timestamp — so the merge has a single well-defined outcome.
  • What happens if the DELETE and the INSERT run in separate transactions?
    Every concurrent reader between the two statements sees the fact table with the affected rows simply missing. Dashboards under-report intermittently and nothing errors, so the bug surfaces as untrustworthy numbers rather than a failure. Wrapping both in one transaction makes the swap atomic — readers see either the old rows or the new ones.
  • A table declares PRIMARY KEY (sale_id) but contains duplicates. How?
    Redshift does not enforce PRIMARY KEY, UNIQUE or FOREIGN KEY constraints; they are informational hints the optimizer trusts when planning. A load that inserted the same key twice will store both rows silently. Worse, the planner may eliminate work on the assumption of uniqueness, so an unenforced key you have actually violated can produce wrong results, not merely duplicates.
  • When would you rebuild the table instead of merging into it?
    When the batch touches a large fraction of the rows. At that point the merge marks a large share of the table deleted and appends an equally large unsorted region, leaving a table that needs heavy maintenance. Building a replacement with CREATE TABLE AS over surviving plus new rows, then swapping names, gives you a sorted, compact, freshly compressed table in one pass.

saying these in an interview costs you the question

  • Updating the fact table row by row from application code
  • Running the DELETE and INSERT in separate transactions
  • Assuming Redshift enforces PRIMARY KEY or UNIQUE
  • Believing an UPDATE rewrites the value in place
  • Merging every few minutes instead of batching the day

context

resolving links…