Articles · Amazon

Amazon Data Engineer SQL Interview: Query Exercises and Follow-Up Reasoning

Practice Amazon Data Engineer SQL reasoning with deduplication, composite joins, late records, and reconciliation using a worked query exercise.

An Amazon Data Engineer SQL interview answer should explain more than how to make a query return rows. If the source resends an order, a newer version cancels it, or a late record changes yesterday's total, the query needs a defensible data contract. Otherwise, valid SQL can produce unreliable data.

This guide uses one original order-state exercise to connect query writing with ingestion, deduplication, joins, and reprocessing.

Evidence boundary: Amazon's official role guidance supports the skill emphasis below. The schemas and follow-up exercises are original practice material, not leaked questions or a reconstruction of a specific interview loop. Two independent same-cycle candidate reports have not been established here, so no fixed round count, timing, or question frequency is claimed.

What does Amazon officially emphasize for Data Engineers?

Official facts: Amazon's university data-role preparation describes Data Engineers as working with SQL, ETL, data modeling, programming, and scalable data infrastructure. It also emphasizes data accuracy and availability for customers. This is the scope of that preparation page, not a promise that every hiring team uses an identical interview. See Amazon's data-role guidance.

A current Amazon Fuse Data Engineer posting explicitly mentions SQL, scripting, ETL pipelines, data modeling, and resolving data-quality issues while collaborating with business owners and BI Engineers. Those responsibilities make a useful preparation target; the posting does not establish an interview question list. See the official role description.

Preparation inference: Practice a query and its operating contract together. For every result, explain the source grain, unique key, treatment of corrections, and validation rule. Keep those engineering concerns separate from a BIE-style business recommendation or a Data Scientist's statistical interpretation, even when the roles use similar SQL syntax.

Exercise 1: build the current state before filtering paid orders

This original exercise uses a fictional source table called order_versions. Each row is a complete snapshot of one order, not an additive payment event. The business key is (marketplace_id, order_id). Higher version wins; ingest_id is a unique arrival identifier used to choose one copy of an otherwise identical version.

Conflicting payloads for the same business key and version are a data-quality error, not a reason to choose whichever row arrived last. Assume this small fixture has no such conflicts.

Market	Order	Version	Ingest ID	Order date	Status	Amount cents
US	1	1	11	2026-02-01	paid	1000
US	1	2	12	2026-02-01	canceled	0
US	2	1	21	2026-02-01	paid	2000
US	2	1	22	2026-02-01	paid	2000
CA	2	1	31	2026-02-01	paid	3000
US	3	1	41	2026-01-31	paid	500

The last row arrived late, but its business date is January 31. All amounts are illustrative integer cents. The metric is current-state paid order value by order date, not recognized revenue or an accounting ledger.

WITH ranked AS (
    SELECT marketplace_id, order_id, version, ingest_id,
           order_date, status, amount_cents,
           ROW_NUMBER() OVER (
               PARTITION BY marketplace_id, order_id
               ORDER BY version DESC, ingest_id DESC
           ) AS rn
    FROM order_versions
)
SELECT marketplace_id, order_id, order_date, amount_cents
FROM ranked
WHERE rn = 1 AND status = 'paid'
ORDER BY marketplace_id, order_id;

The result contains CA order 2 for 3,000 cents, US order 2 for 2,000, and US order 3 for 500. US order 1 is absent because its current state is canceled. The two deliveries of US order 2 contribute once.

Official SQL behavior: Redshift's ROW_NUMBER assigns positions within partitions, but equal ordering values can produce nondeterministic choices. That is why the complete ordering and the meaning of ties matter. See the Redshift ROW_NUMBER reference.

Follow-up: why not filter paid rows first?

Filtering status = 'paid' inside the CTE would remove the cancellation before ranking. The older paid version of US order 1 would then appear to be the newest eligible row, overstating the result by 1,000 cents in this fixture.

Engineering inference: Apply a current-state predicate after resolving current state. If the requested metric were instead historical payment events, the source model and query would change. A snapshot table is not an immutable transaction ledger just because it has multiple versions.

Also distinguish version order from arrival order. A late retry of version 1 must not replace version 2. Ordering only by ingest_id would make that possible. The tie-breaker selects among equivalent copies; it must not overrule the authoritative source version.

Redshift also supports filtering window results with QUALIFY. The CTE form here makes the two logical steps visible and is portable to more engines. Syntax convenience does not resolve an ambiguous source contract. See the official QUALIFY documentation.

Exercise 2: preserve the composite key through joins

Suppose a downstream detail table contains two line items for US order 2 and three for CA order 2. Joining on order_id alone mixes marketplaces. Joining on both key columns fixes that collision, but it still creates multiple rows per order.

If you then sum the order-level amount, US order 2 contributes 4,000 cents and CA order 2 contributes 9,000. Correct join keys and correct metric grain are separate requirements.

Original prompt: Return paid order count and value by marketplace without multiplying orders. You do not need the detail table for that result:

SELECT marketplace_id,
       COUNT(*) AS paid_orders,
       SUM(amount_cents) AS paid_value_cents
FROM current_paid_orders
GROUP BY marketplace_id
ORDER BY marketplace_id;

Here, current_paid_orders is a view or CTE containing the first query's selected rows. Expected output is CA: one order, 3,000 cents; US: two orders, 2,500 cents.

If the follow-up asks whether an order contains a particular item category, use an existence test or reduce the detail table to one row per order before joining. If it asks for item-level value, use item-level measures and define how they reconcile with the order amount. SUM(DISTINCT amount_cents) is not a repair: two legitimate orders may have the same amount.

Official behavior: Outer joins retain unmatched rows from the preserved side and fill missing matches with nulls. Predicate placement can change that preservation, as the Redshift join examples illustrate. State whether unmatched dimensions should be retained, excluded, or reported as a data-quality exception.

Exercise 3: rebuild the right day when a late record arrives

The fixture's US order 3 belongs to January 31 even if ingestion occurred on February 2. A job that processes only the current business date misses it. A job that appends its aggregate on every retry can count it repeatedly.

Original prompt: Produce the current-state paid value by marketplace and business date:

SELECT marketplace_id, order_date,
       COUNT(*) AS paid_orders,
       SUM(amount_cents) AS paid_value_cents
FROM current_paid_orders
GROUP BY marketplace_id, order_date
ORDER BY marketplace_id, order_date;

Expected rows are CA/February 1: one order and 3,000 cents; US/January 31: one order and 500 cents; US/February 1: one order and 2,000 cents.

Engineering inference: Track which business keys changed and which date partitions their old and new states affect. Recompute those partitions from canonical state, then replace or merge the results using a clearly defined destination key. Retrying the same input must converge to the same table rather than add the same total again.

A fixed lookback window is a policy, not a guarantee against every late event. Explain the permitted lateness, how older corrections are detected, and how a backfill is initiated. If an order's business date changes, both the old date and new date may need correction.

An additional subtlety is an empty partition. If the only paid order on a date becomes canceled, the new aggregate may return no row. A merge that only updates returned rows can leave the old total behind. Define whether the destination deletes that old row or stores an explicit zero through a date-and-market scaffold.

Follow-up: how do you verify replay and corrections?

Use this original validation plan to test the transformation's contract. Each check targets a specific plausible failure.

First, ingest US order 2 twice and confirm the output still contains one current order. Next, add the cancellation for US order 1 and confirm that its earlier paid value disappears from this current-state metric. Then replay the entire batch and compare destination keys and values, not just the total row count.

Test the shared order number across US and CA to catch an incomplete key. Test two different orders with identical amounts to expose a mistaken SUM(DISTINCT ...) repair. Finally, remove the last eligible order from a date to verify that the destination does not retain a stale aggregate.

For this article, the displayed queries were executed against the six-row fixture in SQLite using its supported window functions. The resulting rows matched the stated expectations. Separate mutations demonstrated the filter-before-ranking error and repeated order amounts after a detail join. This checks the example's relational logic; it is not a Redshift performance benchmark or a validation of every dialect feature.

A useful additional reconciliation is the sum of canonical eligible order values versus the sum of all daily aggregates over the same scope. Equality is necessary but not sufficient: two incorrect partitions can offset each other. Compare by marketplace and date, and inspect missing or unexpected keys.

How should you answer the scaling follow-up?

Do not start by naming a sort key. Establish the expensive stage and the correctness boundary first. Ask how many versions exist per order, how much history each run scans, whether the source supplies changed keys, and which dimensions are joined.

Preparation inference: An incremental design can reduce work by resolving only affected orders, provided it can still compare incoming versions with stored canonical state. Filtering the raw table to today's arrivals and ranking only those rows is insufficient if the destination already holds a newer version from yesterday.

For Redshift, inspect the actual plan and distribution of work before recommending physical changes. The Redshift optimization interview guide covers that separate performance discussion. Here, the first obligation is preserving the result across incremental runs.

Conclude the answer with an operational observation: freshness lag, invalid-version conflicts, unmatched dimension rate, or reconciliation mismatches. Choose the signal that would detect the failure you described. “Add monitoring” is incomplete until you identify what a violation looks like and who repairs it.