Load Strategies: Truncate-Reload, Upsert, and Incremental Load

Load Strategies: Truncate-Reload, Upsert, and Incremental Load

Once data has been extracted and transformed, it must be written into the target database correctly. Choosing the wrong load strategy can corrupt data, create duplicates, or cause unacceptable downtime. This lesson covers the three patterns you will use most often as a database administrator: truncate-reload, upsert, and incremental load.

---

Why Load Strategy Matters

Every load operation must satisfy two properties. First, it must be correct: the target table should contain exactly the rows the business expects after the load completes. Second, it must be idempotent where possible: running the same load twice should not leave the table worse off than running it once. Checking row counts, checksums, and duplicate queries after each load gives you objective evidence that both properties hold.

---

Pattern 1 – Truncate-Reload

Truncate-reload is the simplest strategy. You delete every row from the target table and replace it with a fresh full copy of the source data. It is appropriate for dimension tables or small reference tables that are republished in full every cycle — for example, a daily product-category snapshot.

How it works in PostgreSQL:

1. Open a transaction with `BEGIN`.

2. Issue `TRUNCATE target_table;` — this removes all rows but keeps the table structure. Inside the same transaction, the truncated rows are invisible to other sessions until you commit, so the table is never empty from the outside if you hold the transaction open.

3. Load the clean file with `COPY target_table FROM '/path/to/file.csv' CSV HEADER;`.

4. Verify the row count with `SELECT COUNT(*) FROM target_table;`.

5. `COMMIT`.

Because the TRUNCATE and COPY share a transaction, a failure at step 3 causes the whole operation to roll back, leaving the old data intact. This is a critical safety property.

Idempotency test: Rerun the exact same script. You should see the same row count and the same checksum. If you see double the rows, the table is missing its TRUNCATE — a logic error you must catch before production.

---

Pattern 2 – Upsert

An upsert merges new and changed rows without touching rows that have not changed, and without creating duplicates. It is the right choice for master data tables (customers, employees, accounts) that receive delta files containing both new records and corrections to existing ones.

PostgreSQL implements upsert with `INSERT … ON CONFLICT`. The general form is:

```sql

INSERT INTO customer (customer_id, name, email, updated_at)

VALUES (%s, %s, %s, %s)

ON CONFLICT (customer_id)

DO UPDATE SET

name = EXCLUDED.name,

email = EXCLUDED.email,

updated_at = EXCLUDED.updated_at;

```

The `ON CONFLICT` clause targets the primary key or a unique constraint. `EXCLUDED` is a special table alias that refers to the row that was just rejected by the conflict. Setting columns equal to `EXCLUDED.column` applies the incoming values.

Validation: After loading the initial batch and then the delta batch, query for duplicates with `SELECT customer_id, COUNT() FROM customer GROUP BY customer_id HAVING COUNT() > 1;`. An empty result set means no duplicates exist. Also spot-check a changed row to confirm the update was applied.

---

Pattern 3 – Incremental Load

For large fact tables (sales transactions, sensor readings, log events), loading a full extract every cycle is too slow. An incremental load appends only the rows that are new since the last load.

How it works:

1. Query the target table for the latest loaded record date: `SELECT MAX(record_date) FROM sales_fact;`. Call this value `last_load_date`.

2. Extract only source rows where `record_date > last_load_date`.

3. Append those rows with `COPY` or a batch `INSERT`.

4. Validate with `SELECT COUNT(*)` and a `SUM` checksum (e.g., `SELECT SUM(sale_amount) FROM sales_fact WHERE record_date > last_load_date`) and compare against the same aggregation run on the source extract.

Key risk: If the source system allows late-arriving data — records that carry an older `record_date` but arrive after your cutoff — a strict `MAX`-based watermark will miss them. Always confirm with the source system owner whether late arrivals are possible, and add an appropriate buffer or use a separate audit column if needed.

---

Data Integrity Checks

For all three patterns, run these checks after every load:

• Row count: `SELECT COUNT(*) FROM target_table;` — compare to expected.
• Checksum: `SELECT SUM(numeric_column)` or `SELECT MD5(string_agg(...))` — confirms the values loaded correctly, not just the count.
• Duplicate check: `GROUP BY primary_key HAVING COUNT(*) > 1` — must return zero rows.

Document expected values before the load and compare programmatically. Manual eyeballing is not sufficient for production pipelines.

---

Safety and Professional Practice

Always run destructive operations (TRUNCATE, DELETE) inside a transaction so a failure can be rolled back. Keep the transaction as short as possible to avoid locking the table for long periods, which blocks other users. Never run a load directly against a production table until you have tested it in the student schema. Back up or snapshot any table you are about to truncate in a production context — your organization's change-control policy will specify the exact procedure. Protect connection credentials: do not hard-code passwords in scripts; use environment variables or a secrets manager.