Design Incremental Load Process for Large Relational Table
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates understanding of incremental loading, change-data-capture, idempotent upsert logic, deduplication, partitioning, and data quality controls for large relational tables within the Data Manipulation (SQL/Python) domain.
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.
Tables
orders_daily_load(load_date DATE, order_id INTEGER, customer_id INTEGER, amount DECIMAL(10,2))
orders(order_id INTEGER, customer_id INTEGER, amount DECIMAL(10,2), first_load_date DATE, last_load_date DATE)
Hints
- Treat order_id as the business key and ensure the target table has a primary key on it.
- Use window functions (MIN/MAX over order_id) to compute first and last load dates in the staging data.