Design Incremental Load Process for Large Relational Table

orders_daily_load

+------------+-----------+-------------+--------+
| load_date  | order_id  | customer_id | amount |
+------------+-----------+-------------+--------+
| 2024-05-20 | 1001      | 501         | 58.90  |
| 2024-05-20 | 1002      | 743         | 12.50  |
| 2024-05-21 | 1003      | 501         | 35.00  |
| 2024-05-22 | 1004      | 888         | 77.10  |
| 2024-05-22 | 1002      | 743         | 12.50  |
+------------+-----------+-------------+--------+

Scenario

Designing an incremental daily load process for a large relational table while ensuring data quality and idempotency.

Question

Provide an example of loading daily data for a large table—what steps did you take? What challenges did you encounter and how did you overcome them? How would you identify if you have already loaded a specific row before?

Hints

Discuss change-data-capture, primary keys, upsert logic, partitioning, dedup checks, and automation/monitoring.

Idempotent Incremental Daily Load for Orders

You have a landing (staging) table orders_daily_load that receives rows loaded each day, including possible repeats of the same order_id on different load_date values. You need to maintain a target table orders that contains exactly one row per order_id, tracking when each order was first and last seen.

Using the schema and sample data below, write SQL to implement an incremental, idempotent daily load that:

1. Deduplicates the landing data by order_id.
2. Derives first_load_date and last_load_date for each order across all loads seen in the staging table.
3. Upserts into the target orders table so that:
   • New order_ids are inserted.
   • Existing order_ids are updated only if the customer_id, amount, or last_load_date has changed.
   • The process is idempotent (re-running it for the same landing data does not create duplicate rows or incorrect dates).

Return (or materialize) the upserted result in the orders table matching the expected output. For validation, return the final orders table state that the idempotent upsert would produce.

Table orders_daily_load

column	type	constraints
load_date	DATE	NOT NULL
order_id	INTEGER	NOT NULL
customer_id	INTEGER	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	load_date	order_id
58.9	501	2024-05-20	1001
12.5	743	2024-05-20	1002
35	501	2024-05-21	1003
77.1	888	2024-05-22	1004
12.5	743	2024-05-22	1002

Table orders

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
customer_id	INTEGER	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
first_load_date	DATE	NOT NULL
last_load_date	DATE	NOT NULL

Sample data:

amount	customer_id	first_load_date	last_load_date	order_id
58.9	501	2024-05-20	2024-05-20	1001
12.5	743	2024-05-20	2024-05-22	1002
35	501	2024-05-21	2024-05-21	1003
77.1	888	2024-05-22	2024-05-22	1004

Expected output

After running the incremental, idempotent load from orders_daily_load into orders, there should be exactly one row per order_id in orders with the earliest (first_load_date) and latest (last_load_date) dates the order appeared in the landing table. For the given sample data, the upserted content of orders should be:

amount	customer_id	first_load_date	last_load_date	order_id
58.9	501	2024-05-20	2024-05-20	1001
12.5	743	2024-05-20	2024-05-22	1002
35	501	2024-05-21	2024-05-21	1003
77.1	888	2024-05-22	2024-05-22	1004

Hints

1. Treat order_id as the business key and ensure the target table has a primary key on it.
2. Use window functions (MIN/MAX over order_id) to compute first and last load dates in the staging data.
3. Pre-deduplicate the landing data so that only one row per order_id is used in the MERGE or upsert.
4. Implement an upsert (MERGE or INSERT ... ON CONFLICT) so that re-running the job with the same data is idempotent.
5. Update existing rows only when customer_id, amount, or last_load_date changes.
6. Consider adding a duplicate check (COUNT(*) by order_id) in the landing table for data quality monitoring.