EY·Data Scientist·Technical Phone Screen·Senior

Senior

May 2026

Summary

EY data scientist round, basically one big SQL problem that looked manageable until I started actually writing it. The deduplication and idempotency requirements together made it way more involved than a typical aggregation question.

Questions Asked (1)

Given a trades table with duplicate records per trade ID (late-arriving updates), accounts, customers, and risk limits, write SQL to build a daily exposure fact table at the (account, trade date) grain. Requirements include deduplicating to the latest ingested row per trade, computing gross and net notional, a limit utilization ratio, a breach flag, filtering out canceled trades and accounts with failed KYC, and making the query idempotent for backfills. Then explain one edge case your SQL intentionally ignores.

Data ModelingSystem DesignTechnical Trade-offs

Author's notes

This one took me a second to decompose.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by outlining the data model and the necessary transformations: deduplicate trades using a window function to keep the latest ingested row per trade ID, then join with accounts and customers to filter out canceled trades and failed KYC accounts. Aggregate to (account, trade_date) grain, compute gross/net notional and limit utilization, and flag breaches. Finally, ensure idempotency by using a MERGE or INSERT OVERWRITE pattern for backfills, and discuss an edge case like late-arriving data beyond the backfill window.

Pro tip: Demonstrate awareness of data quality and operational constraints by explicitly stating assumptions (e.g., ingestion timestamp is monotonic) and proposing a strategy to handle late-arriving data beyond the backfill window, such as a periodic full refresh or a lookback period.

1. Deduplicate trades

Use a window function (e.g., ROW_NUMBER() OVER (PARTITION BY trade_id ORDER BY ingest_ts DESC)) to select the latest record per trade ID, ensuring only the most recent update is used.

2. Filter and join

Join the deduplicated trades with accounts and customers to filter out canceled trades (status = 'CANCELED') and accounts with failed KYC (kyc_status = 'FAILED').

3. Aggregate and compute metrics

Group by account and trade_date, then calculate gross notional (SUM(ABS(notional))), net notional (SUM(notional)), limit utilization (net notional / limit), and breach flag (utilization > 1).

4. Ensure idempotency

Use a MERGE statement or INSERT OVERWRITE on a partition (e.g., trade_date) to make the query idempotent for backfills, so re-running for the same date produces the same result without duplicates.

5. Discuss edge case

Explain an edge case your SQL intentionally ignores, such as trades that arrive after the backfill window (late-arriving data beyond the processed date) or trades with missing account information.

Key Points to Mention

Use of window functions for deduplication (e.g., ROW_NUMBER, RANK) and handling ties in ingest timestamp.
Importance of filtering canceled trades and failed KYC accounts before aggregation to avoid incorrect exposure.
Calculation of gross vs. net notional and how they differ in risk assessment.
Limit utilization ratio and breach flag logic, including handling division by zero or null limits.
Idempotency techniques: MERGE, INSERT OVERWRITE, or DELETE+INSERT for backfills.
Edge cases: late-arriving data beyond backfill window, missing account mappings, or trades with null notionals.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.