Data modeling. Performance tuning. Bias for Action. Three worked models, checked queries and decisions you can defend.
Round 3 is a preparation label. The recruiter assigned modeling, performance tuning and Bias for Action to this bundle; the calendar order is not confirmed.
Declare the grain. Prove the answer. Measure the change.
125 min reading 4-hour core route Full ~11h 02m
TOTAL STUDY TIME / FULL GUIDE~11h 02m
125 min reading + 8h 57m practice, including one 60-min mock.
Short route: 4 hoursSelected essentials + one mock.All three mocks: ~13h 02mFull guide + two additional mocks. Estimates include practice time; breaks are extra.
LOCAL & OFFLINEPRIMARY-SOURCE NOTESTHREE MOCK LOOPSREVISION 1.2
FOCUS TIME00:00
Estimated remaining: ~11h 02mStart a focus session to track pace.Open mock runner
01 / Start 2 min read · 5 min practice
Your route through Round 3
A strong answer connects the customer decision to a row-level contract, a schema, an actual query and a measurable operating choice. You must defend correctness before optimizing speed. This guide uses three fully worked hypothetical domains and executable miniature data to make those decisions concrete.
240 minutes. Selected essentials, not every section.
Full guide: ~11h 02m
125 min reading + 8h 57m practice. Budgets include learning, recall and one full mock.
All three models, SQL fixtures, diagnosis cases and both behavioral rehearsals.
Mastery route: ~13h 02m
Full guide plus Mock B and Mock C, 60 minutes each.
No unresolved repairs, correct knowledge checks and explainable designs. Breaks and extra repairs are additional.
Use Pass 1 after you can trace the example. Use Pass 2 after you can reconstruct and defend it with the solution hidden. Left navigation shows reading, practice and total minutes. Progress, notes and ratings save locally. Export before moving the file or changing browsers.
Which decision, reporting cuts, freshness, history and correctness requirement?
Two or three target queries and an explicit scope.
2. Business processes
Usage, eligibility, order, payment and refund are different observations.
One declared grain for each fact.
3. Identity and relationships
What identifies a real entity/event? Which relationships are 1:1, 1:N or M:N?
Business and surrogate keys; multiplicity written beside joins.
4. Time and measures
Event time, source change time, arrival time or publication time? Which measures add?
History policy, units, cutoff and aggregation rules.
5. Prove the queries
Can the model answer the target questions without fanout or lost zero-activity entities?
A worked query and a small counterexample.
6. Operate and tune
How do corrections, deletes, schema changes and failures work? What workload is slow?
Load/recovery boundary, physical choices and validation plan.
SAY IT / Opening
How would you approach a new data-modeling problem?
Reveal worked answer
I would start with the decisions and two or three representative queries, including their freshness and historical interpretation. Then I would separate the business processes and declare what one row means for each fact. I would define business identity, dimensions, relationship cardinality, time and units before physical layout. I would test the model with a normal example and an edge case such as a correction, zero-activity account or many-to-many relationship. Finally I would choose storage and tuning options from the workload and explain how the data is loaded, validated and published.
Pressure test: If the interviewer gives you only ten minutes, prioritize grain, keys, measures and one proof query.
Avoid: Starting with table names or AWS services before the business question.
An illustrative 20-minute design allocation
3m requirements, 4m grains and keys, 5m schema/history, 4m query and edge case, 4m physical/load trade-offs. This is a practice budget. If a key requirement changes, revise the affected choice explicitly instead of defending the first diagram.
03 / Model foundations 3 min read · 20 min practice
Fact grain, measures and aggregation contracts
A grain is a business sentence: one posted order line, one observed usage event, one account-service-day eligibility record. Listing foreign keys is not enough. A surrogate row ID can be unique while the same business event is loaded twice.
Pattern
Appropriate row meaning
Critical boundary
Transaction fact
One immutable measurement or business event, or one current source event with explicit correction history elsewhere.
Separate multiple deliveries from multiple business events. Decide whether adjustments are new events or replacements.
Periodic snapshot
One entity at a fixed reporting interval, including eligible inactive entities when required.
A missing source cannot silently become a zero. Snapshot balances are usually not additive through time.
Accumulating snapshot
One process instance or attempt, updated as milestones complete.
Keep transition events separately if repeat cycles and intermediate history matter. Multiple date roles have distinct meanings.
Factless fact
An eligibility, membership or occurrence relationship without an additive numeric measure.
It can define the denominator that an activity fact cannot supply.
Aggregate fact
A derived summary at a declared coarser grain.
Preserve the components needed for valid rollups and a route back to relevant detail.
Measure
Safe aggregation
Counterexample
Usage quantity
Sum compatible units for disjoint events under the same meter contract.
Do not add vCPU-hours to storage bytes or API calls.
Monetary amount
Sum signed amounts in the same currency and accounting interpretation.
Capture, refund, booked order amount and recognized revenue are not interchangeable.
Balance or active-resource snapshot
Sum across compatible entities at one time.
Summing daily balances gives neither current balance nor cash flow.
Rate / average
Combine valid component numerators and denominators, or sums and counts.
An average of regional percentages is not usually the global rate.
Distinct customers
Union the eligible entity sets or use a justified approximation with stated error.
Summing daily or regional distinct counts double-counts customers present in several groups.
SAY IT / Modeling
Why would one giant fact table be dangerous?
Reveal worked answer
It can mix observations at different grains, such as an order header, lines, captures and refunds. Repeated header or payment values then look additive when they are not. I would separate the processes, use conformed dimensions and compare them at a deliberately shared grain. A wide table can be useful if it retains one coherent row meaning and the aggregation rules remain clear; width alone is not the defect.
Pressure test: What if two facts are genuinely one-to-one on a validated key? A direct join may be valid; prove the relationship.
Avoid: Treating every denormalized table as wrong or every fact-to-fact join as safe.
I would choose by purpose. A normalized foundation can manage integrated entities and dependencies, while a dimensional serving model makes recurring analytical questions easier to use. I would declare fact grain and conformed dimensions, then justify any prejoin or hierarchy normalization with workload and maintenance evidence. Neither shape makes bad identity or history rules correct.
Pressure test: Can both exist in one architecture? Yes, with explicit transformations and ownership between them.
Avoid: Treating normalization and dimensional modeling as mutually exclusive beliefs.
Choice
Use
What to defend
Natural/business key
Stable identity meaningful in a source domain.
Include source/tenant scope when IDs are not globally unique. Normalization of identifiers must not collapse real entities.
Surrogate version key
Identity of one warehouse dimension version.
Retain the business key and valid-time range. A generated integer does not establish business uniqueness.
Conformed dimension
Shared definition and key domain used by multiple facts.
Joining on a similarly named column does not prove the same business entity or time interpretation.
Role-playing dimension
The same date/entity dimension used for different roles.
Order date, capture date and refund date answer different questions. Name the role in the query.
Degenerate dimension
A useful business identifier such as order_id retained in the fact without a separate descriptive dimension.
It is not a reason to repeat order-header amounts on every line.
Junk dimension
A controlled grouping of low-cardinality descriptive flags.
Do not combine unrelated fast-changing attributes if the combination space or update semantics becomes unmanageable.
Bridge / allocation table
Explicit many-to-many membership or attributed value between entities.
Full-credit membership and allocated attribution are different products. Weights must sum to the agreed total for the relevant entity/time.
Hierarchy
Parent/child or versioned hierarchy for rollups.
Avoid double-counting across overlapping paths; define valid-time, cycles and whether multiple parents allocate or receive full credit.
SAY IT / Modeling
An account belongs to two cost centers. How do you report its usage?
Reveal worked answer
I would ask whether each center receives full membership credit or an allocated portion. A membership join can intentionally show the account in both groups, but summing those groups would double-count global usage. If attribution is required, I would use a bridge with an agreed time-valid allocation rule and validate weights per account and period. I would preserve unallocated or ambiguous cases explicitly instead of silently dropping them.
Pressure test: What if allocation weights change mid-month? Apply the agreed event-time or reporting-period rule and retain its version.
Avoid: Adding a bridge without explaining how measures roll up.
05 / Model foundations 3 min read · 25 min practice
History, late facts and two different time axes
Requirement
Model choice
Failure if confused
Correct current descriptive label
Type 1 overwrite with appropriate audit controls.
Past reports can change if they always join to current attributes.
Classify events by attributes then valid
Type 2 versions with a surrogate key and half-open validity interval.
Joining every fact to the current row rewrites its historical classification.
Keep a small previous/alternate attribute
Type 3-style explicit attribute columns.
This is not unlimited event history.
Late-arriving fact
Look up the dimension version valid at event time, not load time.
An old transaction gets today’s account plan.
Late or corrected dimension history
Resolve gaps/overlaps, identify affected facts and apply the agreed restatement policy.
A one-time key lookup remains wrong after history changes.
What was true versus what we knew
Track valid/business time and recorded/system time when required, or retain published immutable versions.
Ordinary SCD2 alone does not reproduce every prior knowledge state.
Temporal join contract
-- Half-open historical lookup. Engine syntax/type details may differ.
e.account_id = d.account_id
AND e.event_at >= d.valid_from
AND (e.event_at < d.valid_to OR d.valid_to IS NULL)
-- Validate at most one matching version, then define missing handling.
Use one documented timestamp convention. UTC instants avoid ambiguity in the storage boundary, but a business day can still be a named local timezone with daylight-saving changes. Use the business calendar to derive the intended reporting boundaries; do not assume every local day is exactly 24 hours.
SAY IT / Modeling
A January account-plan change is discovered in March. What happens to January facts?
Reveal worked answer
I would distinguish a true January-effective change from a March-only change and ask whether consumers want restated history or the values originally published. For restatement I would repair the valid-time intervals, find affected event-time facts, re-resolve their dimension association and rebuild dependent summaries. For reproducibility I would retain the prior publication version or appropriate system-time history. I would validate boundaries and explain which reports changed.
Pressure test: What if the old source evidence no longer exists? Do not claim an exact reconstruction without it.
Avoid: Assuming a current_flag or load timestamp resolves both business time and knowledge time.
SAY IT / Modeling
An event arrives before its account dimension. Do you drop it?
Reveal worked answer
Not automatically. I would preserve the business identity and source evidence, choose an explicit unknown/inferred-member or quarantine policy based on the consumer contract, and track the unresolved population. When the dimension arrives, I would resolve the correct version and repair affected facts or published results as required. A placeholder must not make missing identity disappear from quality reporting.
Pressure test: Can all unknown entities share one business identity? No; keep the unresolved natural key separately.
Avoid: Silently dropping facts or turning unknown measures into zero.
Daily adoption: distinct eligible accounts with at least one qualifying event / distinct eligible accounts, at account-service-day grain. For this exercise, eligibility is defined at the UTC day’s start and the whole day is the observation window.
Usage by service, region, meter and event-time plan. Keep units separate and exclude deleted/replaced source versions.
Show eligible accounts with no qualifying activity. Preserve correction history and identify provisional publication status.
Clarify real-world eligibility changes, qualifying event semantics, late-correction horizon, permitted use and required freshness before production design.
2. Logical schema
Table / grain
Keys and important fields
Why it exists
dim_account: one business account
account_id; source/tenant namespace if needed
Stable account identity shared by usage and eligibility.
dim_account_version: one valid-time account version
An independent denominator including zero-activity accounts.
fact_account_service_day: one account-service-day
Stable grain; has_qualifying_activity; eligible flag; publication version
Reusable adoption components. Derive account-day flags, not counts of raw event deliveries.
aggregate_usage_day: one day-service-region-meter-plan group
Dimensional group plus compatible quantity sums; definition and publication version
A workload-specific acceleration layer. Retain the detail path needed for investigation.
Selected key relationships. Eligibility and usage are independently reduced to account-service-day before combination; arrows are not an instruction to join raw facts.
3. Load and correction behavior
Land source changes; reject same-key/version payload conflicts; canonicalize versions; retain tombstones; resolve event-time dimensions; compute affected account-service-days and aggregate groups; reconcile; publish a coherent version. A correction that changes account, day or region requires repairing both its old and new contribution. An append-only aggregate update would leave the old group inflated.
4. Physical starting point
For a large columnar warehouse, begin with measured query patterns and automatic physical choices where supported. A time-oriented sort/pruning strategy can help bounded date scans. Account distribution can help some joins but can create hot-tenant skew. A tiny service dimension and a large account history need different distribution decisions. Keep the logical contract independent of any one physical choice.
SAY IT / Worked model
Design daily adoption and usage reporting for AWS products.
Reveal worked answer
I would separate usage observations from eligibility, because activity alone cannot tell us how many accounts could have used a service. The atomic usage fact would have a stable scoped event identity, source version, event time, account, service, region, meter and compatible quantity. I would keep changes and deletion knowledge so corrections can be replayed safely. Account history would use valid-time versions for event-time plan analysis.
The eligibility fact would be one account-service-day under an explicit eligibility rule. I would reduce qualifying usage to that same grain before joining it, preserving eligible accounts with no activity. Global distinct adoption must be recomputed from account identities, not summed across regions. Usage quantities would be grouped only with compatible meters and units.
I would prove the model with a duplicate event, a corrected event, a deleted event, an account changing plan at a boundary and an eligible inactive account. Corrections must rebuild old and new affected groups. I would then choose physical layout from representative query plans, date filters, join sizes and skew, and publish a validated version with a clear cutoff.
Pressure test: The PM asks for adoption by region. Does your global eligibility denominator support that? Not without a region-eligibility definition or a differently named metric.
Avoid: Using only the activity table for both numerator and denominator.
These SQLite exercises were executed during the build. Expand a solution only after predicting its rows. They teach grain and semantics; they are not Redshift performance measurements. The fixture has one compatible usage unit and UTC timestamps in sortable ISO form. In real data, include the meter/unit contract and source/tenant namespace.
Input
What happened
Correct interpretation
e1, account A
Version 1, quantity 10, delivered twice
One event, quantity 10.
e2, account B
Version 1 quantity 20; version 2 quantity 30
The replacement is 30, not 50.
e3, account C
Version 1 quantity 5; version 2 deleted
No active usage, but preserve deletion version knowledge.
e4, account A
Quantity 2, EU; e1 was US
A is one global adopter in two regions.
Eligibility
A, B and C eligible on January 1
Denominator 3, including inactive C.
01 / Current usage
Resolve latest versions before filtering deletions. Why does filtering deleted rows before ranking resurrect e3?
Before ranking / reject conflicting payloads
Run this invariant before accepting the canonical result. Any returned key/version is ambiguous under this source contract. The fixture view assumes this check passes; it is not a production conflict-resolution engine.
SQL
SELECT event_id, version
FROM (SELECT DISTINCT * FROM changes)
GROUP BY event_id, version
HAVING COUNT(*) > 1;
02 / Adoption with a real denominator
Reduce activity to account-service-day, then retain all eligible accounts. Explain every join cardinality.
03 / Distinct counts do not add
Predict EU, US and global adopter counts. Regional counts 1 + 2 do not imply 3 global adopters.
04 / Event-time history
Account A changes from basic to pro at noon. e1 at 11:00 stays basic; e4 at exactly noon is pro.
05 / The deliberately wrong current-only join
Observe how joining to the current dimension reclassifies e1. This is acceptable only for an explicitly current-segmentation question.
06 / Validate allocation weights
Weights for each account sum to 10,000 basis points. This fixture is static; real weights need a time/version contract.
07 / Allocate without inventing usage
A allocates 60% to engineering and 40% to sales; B allocates 100% to engineering. Expected totals: 37.2 and 4.8, conserving 42.
Runnable fixture / SQLite setup
Run the setup once in an empty SQLite database, then any solution query below. The Python build separately runs repeated and shuffled deliveries, checks allocations, and compares query plans.
SQL
CREATE TABLE changes(event_id TEXT,version INTEGER,account_id TEXT,service_id TEXT,region TEXT,event_at TEXT,units INTEGER,deleted INTEGER);
CREATE VIEW canonical AS WITH exact AS (SELECT DISTINCT * FROM changes), ranked AS
(SELECT *,ROW_NUMBER() OVER(PARTITION BY event_id ORDER BY version DESC) rn FROM exact)
SELECT event_id,version,account_id,service_id,region,event_at,units,deleted FROM ranked WHERE rn=1;
CREATE VIEW active AS SELECT * FROM canonical WHERE deleted=0;
CREATE TABLE eligibility(day TEXT,account_id TEXT,service_id TEXT,PRIMARY KEY(day,account_id,service_id));
INSERT INTO eligibility VALUES('2026-01-01','a','s1'),('2026-01-01','b','s1'),('2026-01-01','c','s1');
CREATE TABLE history(account_sk INTEGER PRIMARY KEY,account_id TEXT,plan TEXT,valid_from TEXT,valid_to TEXT);
INSERT INTO history VALUES(1,'a','basic','2025-01-01T00:00:00','2026-01-01T12:00:00'),(2,'a','pro','2026-01-01T12:00:00',NULL),(3,'b','basic','2025-01-01T00:00:00',NULL);
CREATE TABLE weights(account_id TEXT,cost_center TEXT,bps INTEGER,PRIMARY KEY(account_id,cost_center));
INSERT INTO weights VALUES('a','engineering',6000),('a','sales',4000),('b','engineering',10000);
CREATE TABLE order_line(line_id TEXT PRIMARY KEY,order_id TEXT,net_cents INTEGER,currency TEXT);
INSERT INTO order_line VALUES('l1','o1',9000,'USD'),('l2','o1',4500,'USD');
CREATE TABLE capture(capture_id TEXT PRIMARY KEY,order_id TEXT,cents INTEGER,currency TEXT);
INSERT INTO capture VALUES('p1','o1',8000,'USD'),('p2','o1',5500,'USD');
CREATE TABLE refund(refund_id TEXT PRIMARY KEY,capture_id TEXT,line_id TEXT,cents INTEGER,currency TEXT);
INSERT INTO refund VALUES('r1','p2','l2',3000,'USD');
CREATE TABLE allocation(capture_id TEXT,line_id TEXT,cents INTEGER,PRIMARY KEY(capture_id,line_id));
INSERT INTO allocation VALUES('p1','l1',8000),('p2','l1',1000),('p2','l2',4500);
CREATE TABLE subscription(subscription_id TEXT PRIMARY KEY,account_id TEXT,started_at TEXT,ended_at TEXT);
INSERT INTO subscription VALUES('a1','a','2026-01-03','2026-02-10'),('a2','a','2026-01-20',NULL),('b1','b','2026-01-05','2026-01-31');
CREATE TABLE balance(day TEXT,account_id TEXT,cents INTEGER,PRIMARY KEY(day,account_id));
INSERT INTO balance VALUES('2026-01-01','a',10000),('2026-01-01','b',5000),('2026-01-02','a',8000),('2026-01-02','b',5000);
INSERT INTO changes VALUES
('e1',1,'a','s1','us','2026-01-01T11:00:00',10,0),
('e1',1,'a','s1','us','2026-01-01T11:00:00',10,0),
('e2',1,'b','s1','us','2026-01-01T13:00:00',20,0),
('e2',2,'b','s1','us','2026-01-01T13:00:00',30,0),
('e3',1,'c','s1','us','2026-01-01T14:00:00',5,0),
('e3',2,'c','s1','us','2026-01-01T14:00:00',0,1),
('e4',1,'a','s1','eu','2026-01-01T12:00:00',2,0);
SAY IT / Query proof
Why is SELECT DISTINCT not a complete correction strategy?
Reveal worked answer
It removes identical rows, but a corrected event contains different values and remains a second row. I need stable business identity, source ordering and delete semantics. I would select the latest valid version for each scoped identity, retain tombstone knowledge, and then filter deleted entities from the serving view. I would reject conflicting payloads with the same identity and version. Finally I would repair contributions in both the previous and new aggregation groups.
Pressure test: What if versions are only ordered inside a source partition? Retain that source ordering scope and do not invent a cross-partition total order.
Avoid: Deduplicating on all selected columns or choosing the latest arrival when the source version is authoritative.
08 / Design transfer 4 min read · 25 min practice
Worked model 2 / Orders, captures and refunds
Hypothetical question: explain order value and collected cash by product without double-counting. Define the question before naming the metric. An order is a commitment; a successful capture is collected payment; a refund is a reversal of some collected amount. None alone establishes recognized revenue.
Report order date, capture date and refund date explicitly. A net-cash-by-order-cohort view differs from cash movement on a day.
Selected relationships. Capture-to-line is a controlled allocation, not a raw order_id join across all lines and captures.
08 / Demonstrate the fanout
Two lines and two captures create four rows when joined directly on order_id. Both 13,500-cent totals become 27,000.
09 / Compare processes at order grain
Aggregate each fact independently to order and currency, then combine. Net collected is 13,500 minus 3,000 = 10,500 cents. The fixture uses one currency; production SQL must include currency or explicit versioned conversion.
10 / Product-level cash needs attribution
The explicit allocation yields 9,000 net cents for line 1 and 1,500 for line 2. Explain rounding residue, partial refunds and missing attribution.
SAY IT / Worked model
Model orders, payments and refunds for product reporting.
Reveal worked answer
I would clarify whether the report concerns order value, cash movements, net collected by order cohort or recognized revenue. Then I would define separate order-line, successful-capture and refund-event facts with stable identities, currency and role-specific dates. I would canonicalize retries and source status changes before loading their current interpretation.
For order-level comparison, I would aggregate each process independently to order and currency, then combine at that grain. Joining raw lines to raw captures on order creates a many-to-many fanout. For product-level cash I need an explicit capture-to-line allocation and a refund attribution rule; I would preserve unallocated amounts rather than assign them silently.
I would validate capture conservation, refund references, allocation totals, signs, currency and temporal policy. The two-line, two-capture example should remain 13,500 cents collected before its 3,000-cent refund. Physical aggregates would follow the reporting workload while retaining detail for reconciliation.
Pressure test: A refund has no line reference. Keep it unallocated or apply an approved allocation rule; do not fabricate product attribution.
Avoid: Calling collected cash revenue or joining every fact on order_id.
Worked model 3 / Subscription cohorts and snapshots
Hypothetical question: of accounts first activated in January, how many were active at the start of February? This is a fixed account cohort and a point-in-time retention definition. It is not subscription retention and not activity at any point during February.
Structure
Grain / rule
Why it matters
subscription_transition
One source transition event per subscription, with event and recorded time
Preserve cancellations, reactivations and corrections. Current status alone cannot rebuild historical state.
subscription_interval
One active interval for one subscription under the chosen status rule
Use [active_from, active_to). Account is active if at least one interval covers the observation instant.
account_cohort
One account plus cohort_definition_version
First observed activation is only true first activation if earlier history is complete. Flag left-censored accounts or apply a documented cohort rule.
cohort_observation
One cohort, observation instant and definition version
Store retained count and eligible cohort count. Do not label an unobserved future period as zero retention.
account_balance_day
One account/currency at a snapshot cutoff
Balances add across accounts at one instant, not across snapshot dates.
onboarding_attempt
One process attempt with milestone timestamps
An accumulating snapshot can expose time to activation. Retain transition events for retries and repeated milestones.
Account and subscription retention intentionally have different denominators. Reconstruct active intervals from the ordered lifecycle contract.
Fixture: account A has two January subscriptions, both active on February 1; account B has one ending January 31. Account retention is 1/2 = 50%. Subscription retention is 2/3 = 66.67%. For this exercise reactivation never resets first-activation account cohort. A different business definition needs a differently versioned metric.
11 / Account retention
Collapse qualifying subscriptions to account identity before counting retained accounts.
12 / Subscription retention
Keep the subscription denominator. The different result is intentional, not a SQL discrepancy.
13 / A balance is not a flow
January 2 total balance is 13,000 cents. Adding both days gives 28,000, which is not the January 2 balance.
SAY IT / Worked model
Design a subscription retention model with reactivations.
Reveal worked answer
I would define the retained entity, cohort assignment and observation rule first. For first-activation account cohorts, each account has one cohort under a versioned definition, and it is active at an observation instant when at least one subscription is active. I would retain lifecycle events and derive half-open active intervals so cancellations, reactivations and corrections remain explainable.
I would distinguish account retention from subscription retention and fixed-instant retention from activity during a period. Reactivation would not change the first-activation cohort in this definition. I would flag incomplete historical coverage and exclude or mark unobservable future periods rather than count them as churn.
The proof example is two accounts with three subscriptions: both of A’s subscriptions are active at the observation instant and B’s is not. Account retention is one of two; subscription retention is two of three. I would version the metric definition and publication cutoff so late corrections can be restated without silently changing the as-reported view.
Pressure test: What if an account has overlapping subscriptions? Use existence at account grain, not one retained account per active subscription.
Avoid: Averaging monthly retention percentages or turning future observations into zero.
10 / Operate the model 3 min read · 20 min practice
Loading, corrections and coherent publication
Failure
Mechanism
Proof before publication
A source delivery repeats
Stable scoped identity and source ordering; idempotent merge or deterministic partition rebuild
Replay the same batch twice and shuffle allowed delivery order. Current business output is unchanged.
A correction moves an event to yesterday
Track source-change progress separately from event-time partitions; repair old and new affected groups
Change date/account/region and verify the old group loses exactly the previous contribution.
An older record follows a deletion
Retain the deletion version until older deliveries can no longer reappear under retention guarantees
Replay an old version after the tombstone. It must not resurrect.
A dimension correction splits an interval
Validate nonoverlap and affected fact alignment; distinguish real change from correction
Boundary instants match exactly one permitted version; affected history is restated under policy.
Schema adds a new meter/unit
Version the contract; stage unknown units or columns; preserve raw evidence
Do not add unlike quantities or silently turn unknown measures into zero.
Only some aggregates complete
Build a versioned candidate; reconcile components; switch a publication manifest or transaction boundary
Readers see one coherent cutoff/definition across related outputs, not a mixture of old and new.
A published version is wrong
Keep a known-good version and correction lineage; assess what consumers already did
Rollback exposure and rebuild affected outputs. A withdrawn dataset cannot undo an irreversible customer action.
SAY IT / Operations
How do you handle a correction that arrives a month late?
Reveal worked answer
I would ingest it using source-change progress rather than reject it because its business timestamp is old. I would locate the previous and corrected contributions, update the canonical event under source ordering, and rebuild affected historical groups. If classification history also changes, I would update the affected fact-to-dimension alignment. I would validate source totals and entity-level differences, then publish a new coherent version. I would preserve the as-reported version if that is part of the reporting contract.
Pressure test: What if raw evidence has expired? State the recovery limitation, recover from the source if available, and do not invent the missing history.
Avoid: Assuming a rolling seven-day event-time filter catches every correction.
SAY IT / Operations
What does atomic publication mean for several warehouse tables?
Reveal worked answer
It means a reader consumes related results from the same accepted cutoff and definition. I can use a supported transaction boundary, versioned tables with an atomic manifest pointer, or a controlled serving-layer switch. I must verify the chosen engine and readers actually honor that boundary. A collection of individually successful jobs is not sufficient if consumers can mix versions.
Pressure test: How do you roll back when the schema changed too? Preserve compatible serving contracts or version the reader contract with the data.
Avoid: Calling an orchestrator DAG transactional.
Choose retention and access controls for the data contract: least-privilege readers, restricted raw identifiers, encrypted storage where supported, auditable changes and deletion propagation to derived copies. A legal deletion requirement may constrain replay retention; preserve non-sensitive version/tombstone metadata only as allowed. These are design constraints, not a claim about this team’s implementation.
11 / Performance foundations 3 min read · 20 min practice
Write and storage cost; selectivity and key order matter. A scan may beat many random lookups for a broad result.
Columnar layout and encoding
Large scans using a subset of columns, compression-friendly data
Project only needed columns. An analytical warehouse does not acquire Oracle-style B-tree behavior because a key is declared.
Sort / clustering / pruning
Repeated filters constrain a useful range
A date filter helps only if the engine can exploit physical metadata and the predicate. Sorting is not distribution.
Partitioning
Bounded reads, lifecycle management or independent rebuilds
Too many tiny partitions/files create metadata and scheduling overhead. Partition keys should match workload and operational boundaries.
Distribution / broadcast
Reduce movement for expensive joins or aggregation
Account keys may be skewed; replication costs storage and refresh work. Measure filtered sizes and actual movement.
Materialized aggregate
Repeated expensive queries with a stable, compatible grain
Refresh lag, correction propagation, dimension changes and nonadditive metrics. Store numerator/denominator components where valid.
Cache
Repeated work with reusable stable data
Warm results do not establish cold-query speed. Invalidation, memory pressure and concurrency can erase the benefit.
SQL
-- Equality plus time range is a candidate composite-index workload.
SELECT SUM(units)
FROM perf_events
WHERE account_id = 42
AND event_day >= '2026-01-08'
AND event_day < '2026-01-15';
For this workload, (account_id, event_day) is a plausible row-store index order. A query filtering only date may need a different access path. Engine/version features such as skip scans mean that “only the leftmost prefix can ever be used” is too absolute. Validate an executed plan and representative workload rather than memorizing one rule.
SAY IT / Performance
How do you choose a partition key, sort key and distribution key?
Reveal worked answer
I would separate the jobs of those choices. Partitioning can bound reads and lifecycle work; sorting or clustering can improve pruning; distribution controls where rows live and how joins move data. I would use representative filters, joins, cardinalities, skew and update patterns to evaluate each. Automatic choices are a reasonable starting point when the engine supports them, but I would inspect plans and runtime behavior before overriding them.
Pressure test: What if the best join key is also the hottest tenant? Compare the reduction in movement against skew and consider preaggregation or another distribution.
Avoid: Using account_id for every key because it is a common join column.
SAY IT / Performance
When would you add a materialized aggregate?
Reveal worked answer
When repeated queries spend substantial work on a stable grouping and the refresh/correction cost is justified. I would define its grain, freshness, history and metric components first. Additive usage may roll up by compatible units, while distinct accounts usually require recomputation or a suitable approximation. I would keep a detail path, validate equivalence at the same cutoff, and repair the aggregate when late source or dimension corrections change prior groups.
Pressure test: Can you sum daily distinct adopters to get monthly adopters? No; account overlap invalidates that rollup.
Avoid: Building an aggregate that is fast but cannot answer the actual metric.
12 / Performance diagnosis 3 min read · 20 min practice
Read a plan as a chain of evidence
First separate queue delay, compilation, execution, data transfer and client rendering. Then locate the expensive operator inside execution. Estimated cost is not elapsed milliseconds, and parent timings may include their children. In PostgreSQL executed plans, rows and time may be reported per loop; use loops to understand repeated work. A logical fanout can increase runtime and corrupt the result at once.
Observation
Hypothesis
Next evidence
Estimated 100 rows, actual millions
Stale/missing statistics, correlated predicates or unexpected multiplicity
Check input distributions, filtered cardinalities and join keys before forcing an algorithm.
Many inner-loop index probes
Nested loop is repeating a lookup far more than expected
Outer rows, loops, heap fetches and total buffer work. A nested loop can still be best for a small selective outer input.
Hash join spills
Build side exceeds available working memory, skew or excessive rows
Actual build bytes, batches/spill, join grain and concurrent memory use before increasing memory.
Large sort
Required ordering/window/grouping over too much input
Predicate/projection pushdown, existing order, aggregation grain and spill. Removing ORDER BY changes the contract if ordering is required.
Network redistribution dominates
Inputs are not suitably located or are larger than estimated
Filtered sizes, distribution labels, skew and whether preaggregation preserves semantics.
One worker dominates
A hot key/partition, large file or pathological task
Task input distribution and key frequencies; average task duration hides the bottleneck.
Executed local demonstration
The build creates 50,000 deterministic SQLite rows and compares EXPLAIN QUERY PLAN before and after an index. Both queries return 284. This proves an access-path change on this fixture. It does not claim a latency improvement, a cloud benchmark or a Redshift index capability.
SAY IT / Performance
A dashboard query became slow. Where do you start?
Reveal worked answer
I would define the affected query, output contract, parameter set and latency breakdown. If execution is slow, I would compare plans and actual operator evidence: filtered rows, estimate error, join multiplicity, movement, skew, spill and repeated work. I would also check data growth, statistics, deployment changes and concurrency. I would test one evidence-based change against the same cutoff and representative parameters, proving equivalent outputs and measuring both query and system effects.
Pressure test: What if execution is only 20 seconds of a 120-second request? Investigate the other 100 seconds before claiming query tuning solves the user experience.
Avoid: Adding indexes, compute or hints before locating the bottleneck.
Good for movement, but still inspect fanout, scan volume and skew.
DS_DIST_ALL_NONE
Inner table is replicated with ALL; no redistribution needed for this join
Useful for an appropriate small dimension; account for replication and refresh cost.
DS_BCAST_INNER
Inner input is broadcast to compute nodes
May be sensible when filtered inner input is small. Do not call every broadcast bad.
DS_DIST_BOTH
Both inputs are redistributed
Evaluate compatible distribution, filtered preaggregation or a smaller input; compare total workload effects.
DS_DIST_ALL_INNER
An ALL-distributed outer table causes the inner input to be sent to a single slice
This is not the favorable ALL-inner case above. Inspect the outer/inner roles carefully.
Uneven work / spill
A distribution key or join result may create skew or excessive working data
Measure key distribution and per-step work. More compute may preserve the same hot-key bottleneck.
Long queue with short execution
Workload/concurrency scheduling dominates observed time
Inspect workload configuration and resource saturation for the actual deployment. Do not assume Serverless and provisioned controls are identical.
Use SYS_QUERY_HISTORY to distinguish query timing and status, then inspect the appropriate detailed execution evidence. SYS query identifiers are not interchangeable with STL query identifiers. A successful query status alone does not prove that its surrounding transaction committed. This guide avoids pretending to have run queries on Amazon’s warehouse.
Start table design with AUTO where appropriate and inspect automatic choices against the workload. Date-oriented sort/pruning can help a bounded scan; distribution can reduce movement. They solve different problems. Redshift primary-key, foreign-key and unique declarations are informational, so validate their truth in the pipeline before relying on them. NOT NULL is enforced. False informational constraints can mislead optimization.
SAY IT / Redshift
Tune an account-by-service daily usage query that redistributes both inputs and has one hot account.
Reveal worked answer
I would verify the output grain and date/cutoff first, then separate queue from execution. Within execution I would inspect filtered input sizes, redistribution, skew and spills. DS_DIST_BOTH indicates movement of both inputs, but distributing everything by account may concentrate the hot account on one slice.
I would compare measured alternatives: reduce rows before the join at a semantically valid grain, use automatic layout as a baseline, colocate an appropriate large join only if skew remains acceptable, or replicate a genuinely small dimension. I would validate source and joined cardinalities so a many-to-many mistake is not disguised as a tuning problem.
For each candidate I would compare equivalent outputs at the same cutoff, execution and queue time, representative parameter sets and concurrent workloads. I would keep a rollback path and reject a faster plan that drops late corrections or rewrites historical classification.
Pressure test: One account has 60% of the input. A distribution key on account may reduce network movement but create a long single-slice tail.
Avoid: Claiming a single distribution key, sort key or larger cluster is always best.
The application mentions Oracle. Prepare the mechanics without inventing a duration of hands-on Oracle work. A defensible bridge is experience with relational modeling, SQL, plans and distributed data systems, followed by the exact Oracle feature you can explain or demonstrate. The resume lists Oracle; it does not establish every task in these examples.
ORACLE SQL
-- Oracle 19c: illustrative read-only diagnostic, not executed here.
-- Run a SELECT in an authorized test session with runtime statistics collected.
SELECT /*+ gather_plan_statistics */ account_id, SUM(units)
FROM usage_fact
WHERE account_id = :account_id
AND event_time >= :start_ts
AND event_time < :end_ts
GROUP BY account_id;
-- Capture the relevant SQL_ID and child cursor; privileges are required.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
:sql_id, :child_number, 'ALLSTATS LAST'));
Compare estimated and actual rows, starts/repeated work, predicates, buffer reads and available runtime statistics. An estimated EXPLAIN PLAN is not proof of what a cached cursor actually executed. Bind values, data skew, statistics and cursor selection can matter. Runtime collection adds overhead; choose the scope deliberately.
Candidate
Why it can help
What can invalidate it
Half-open timestamp predicate
Can support range access and precise boundaries without wrapping each stored timestamp in a function
Business local-day bounds must be converted correctly to the stored time basis, including daylight-saving transitions.
Composite (account_id, event_time) index
Fits equality on account then bounded time for selective queries
Broad scans, low selectivity, key order, table lookups and write cost may make another plan better.
Function-based index
May support an intentional expression predicate
Its expression and workload must match; not every function-wrapped predicate is automatically unindexable.
Updated statistics
Improves cardinality estimates when current statistics poorly represent the data
It does not repair wrong joins or guarantee a particular plan. Validate regressions across parameter sets.
Covering / narrow projections
Can reduce table access or bytes where supported and useful
Extra index width has maintenance cost; SELECT * may force unnecessary data access.
For a PostgreSQL comparison, EXPLAIN (ANALYZE, BUFFERS) executes the statement. Use an authorized read-only test query and understand side effects before running analysis on writes. PostgreSQL behavior is an analogy, not Oracle syntax. The SQLite index lab is a third distinct engine, explicitly labeled.
SAY IT / Oracle
How would you investigate a query whose estimated plan looks fine but runtime is poor?
Reveal worked answer
I would inspect the executed cursor and collect appropriate runtime statistics in a controlled session, then compare estimated versus actual cardinalities and repeated work. In Oracle, DBMS_XPLAN.DISPLAY_CURSOR can show cached cursor plans and available execution statistics; I would identify the right SQL_ID and child cursor. I would examine predicates, bind selectivity, skew, buffer work and access paths before changing indexes or hints. I would test representative parameters and verify the same result contract.
Pressure test: Why not force an index immediately? A broad query may be faster with a scan, and the apparently selective estimate may be wrong.
Avoid: Claiming EXPLAIN PLAN alone measured actual runtime.
Better size estimates can improve broadcast or shuffle choices
A small compressed file can expand into a large in-memory relation. Measure broadcast size and executor memory.
Adaptive Query Execution
Can adapt joins, coalesce shuffle partitions and handle eligible skewed joins using runtime statistics
Configuration, join type and plan eligibility matter. AQE cannot fix an incorrect many-to-many business join.
Predicate / projection pushdown
Reduces input data and columns before expensive work
Filtering the optional side of a left join in WHERE can remove unmatched rows and change semantics.
Repartition vs coalesce
Repartition redistributes with shuffle; coalesce commonly reduces partitions without a full shuffle
Too few partitions can serialize work. Partition count alone does not eliminate hot-key skew.
Preaggregate
Reduces input at a valid shared grain
Retain the components needed for the result; distinct counts and non-decomposable operations need special care.
Targeted salting
Splits a hot key across synthetic buckets
Replicate the appropriate other-side rows or implement a correct two-stage aggregate; validate allocation and duplicates. Do not salt blindly.
File layout / compaction
Avoids excessive file-open and task overhead while supporting pruning
Choose size from the storage format and workload; there is no universal optimal file size.
Cache reused computation
Avoids repeated expensive work
One-use caching can waste memory and add work. Eviction, stale interpretation and concurrency affect benefit.
SAY IT / Spark
A large Spark join has one task far slower than the rest. What would you do?
Reveal worked answer
I would inspect the stage and task distribution to confirm whether the long tail comes from a hot key, oversized partition, spill or another issue. I would check join cardinality and key frequencies first because a wrong many-to-many join can cause both skew and incorrect results.
If the join is correct, I would evaluate filtering and projection, a safe preaggregation, AQE behavior and whether the smaller side is truly small enough to broadcast in memory. For a concentrated hot key I could consider targeted salting or isolating that key, with explicit duplication and recombination semantics. Changing only the number of partitions may leave all rows for the hot key together.
I would compare output equivalence, shuffle bytes, spills, task tails and end-to-end time under representative loads. The best candidate must preserve late corrections, null semantics and optional-side rows, not just reduce the reported stage duration.
Pressure test: When could broadcasting make things worse? The relation may be larger in memory than estimated or compete with concurrent executor workloads.
Avoid: Increasing executor memory or calling repartition without proving the source of the tail.
SAY IT / Spark
How would you salt a skewed join without corrupting the result?
Reveal worked answer
I would first establish the required join cardinality and isolate the hot key. For a large side split across salt buckets, the matching rows on the smaller side may need replication across those buckets so every large-side row retains its intended matches. I would remove the salt after the join and verify no extra business multiplicity was introduced. For decomposable aggregates I can instead partially aggregate by key and salt, then combine by key using valid components. This does not make summing partial distinct counts valid.
Pressure test: What if both sides are large and many-to-many by design? Salting redistributes work but does not remove the genuine output size.
Avoid: Salting both sides independently and losing matches.
Write the acceptance contract before the experiment: same source cutoff, metric definition, population, time zone, null rules, currency/unit, duplicate semantics, output grain and required ordering. Two totals can match while individual accounts are wrong. Compare keys and values, counts and business invariants; use tolerances only for measures whose numerical contract permits them.
14 / Detect overlapping history
This intentionally invalid account history has two versions covering January 15. The query must return the conflicting pair.
15 / Null-safe comparison
Old a=NULL and new a=2 are different. A plain old.value <> new.value predicate evaluates to unknown for nulls. SQLite IS NOT is used here; use the target engine’s supported null-safe syntax or explicit cases. Also compare missing keys in both directions.
Benchmark control
Why it matters
Record
Representative data and parameters
A selective single-account query does not represent every dashboard
Dates, hot/cold accounts, broad/narrow scans, data sizes and distributions.
Same result and cutoff
A query that omits corrections can look impressively fast
Key/value parity, invariants, source completeness and publication version.
Separate cache conditions
Result cache or warm pages can dominate apparent speed
Cold and warm experiments clearly separated; do not mix them into one average.
Repeated runs and order
Background load and run order can distort a single observation
Several trials, comparable conditions and interleaved candidate/baseline order where practical.
Latency distribution and cost
Median improvement can hide tail or resource regression
Appropriate median/tail metrics, queue/execution separately, bytes, CPU/resource usage and workload impact. Few runs do not support a credible p99 estimate.
Deployment boundary
The experiment may not survive concurrency or changing parameters
The optimized query is 40% faster and returns the same grand total. Is it ready?
Reveal worked answer
Not yet. I would compare the same population and cutoff at the declared output grain, including missing keys, nulls, duplicates and historical classification. Equal totals can conceal offsetting errors. I would verify business invariants and representative edge cases, then repeat comparable performance measurements under relevant parameter and concurrency conditions. Only a semantically equivalent candidate with an acceptable operational trade-off should progress through a bounded rollout.
Pressure test: How do you compare floating-point measures? Use a documented tolerance suited to the business measure, not a tolerance chosen to hide discrepancies.
Avoid: Treating matching SUM values as complete equivalence proof.
17 / Leadership principle 3 min read · 15 min practice
Bias for Action / Decide what can safely move now
Amazon describes a preference for timely action where decisions are reversible and risks are calculated. Show a decision made under incomplete information, the information you did require, the downside you bounded and what happened after you acted. A fast implementation with no meaningful uncertainty is a weaker example of this principle.
Decision
Minimum evidence
Boundary that makes action reasonable
Pilot a new product metric
Customer decision, source meaning, identity, accepted scope and owner
Isolated read-only version, small reviewer set, no automated decisions, visible provisional status and withdrawal path.
Test a tuning candidate
Same output contract, representative sample and known-good baseline
Shadow or canary execution, measured resource limits, key-level comparison and stop conditions before serving.
Contain a data incident
Verified affected scope and the harm of current publication
Revert to an accepted version or pause the affected output with a clear owner; do not invent a clean state.
Change a consequential shared definition
Who depends on it and how an error could propagate
The need for review increases when decisions or customer actions cannot readily be undone. Separate a reversible experiment from broad adoption.
A compact behavioral structure
Decision needed → uncertainty → minimum evidence → options → bounded action → observed outcome → what changed in your judgment. State your own action and the actual result. Use the two constructed scripts below to learn the shape, then replace the details with your real evidence in the workspace.
SAY IT / Bias for Action
How do you decide whether to act now or gather more information?
Reveal worked answer
I would identify the decision deadline, the cost of delay and the downside of being wrong. I would distinguish missing information that can change the decision from information about future possibilities. If the next step is genuinely reversible and bounded, I would define the minimum evidence, owner, acceptance and stop conditions, then act and learn. If it can create irreversible customer consequences, I would narrow the action or obtain the evidence needed for that risk.
Pressure test: What makes the action reversible after someone has used the metric to make a decision? Withdrawing a dashboard alone may not undo that consequence.
Avoid: Equating Bias for Action with bypassing necessary controls.
SAY IT / Bias for Action
A deadline is today and the model has a known gap. What do you ship?
Reveal worked answer
I would first identify which customer decision the gap can distort. I would assess a validated subset, a clearly scoped provisional version, an accepted older version or a short delay. I would act on the option whose limitations the decision owner understands and whose use stays within the evidence. I would specify the unresolved population, response owner and next checkpoint. I would not hide the gap simply to call the deadline met.
Pressure test: What if the gap affects only 0.1% of rows but the largest customers? Count share alone does not establish materiality.
Avoid: Calling a known incomplete result complete or blocking every useful bounded action.
18 / Constructed rehearsal 4 min read · 20 min practice
A reversible pilot while requirements were incomplete
SAY IT / Bias for Action
Tell me about a time you acted without complete information.
Reveal worked answer
A product team needed to compare an emerging service’s usage before the broader reporting requirements were settled. I owned the data foundation. Waiting for a complete cross-product specification would have delayed the immediate decision, but publishing a loosely defined metric to everyone could create a misleading standard.
I separated the reversible part from the consequential part. I proposed an isolated, read-only pilot for one service and five named reviewers, using two understood sources and a written eligibility rule. I explicitly excluded automated decisions and broader publication. Before starting, I confirmed the source cutoff, the business key, who would accept the result and how we could withdraw the pilot.
I built the smallest useful model and tested twelve representative boundary cases, including duplicate input, a correction and an account with no usage. I reviewed the values with the product owner and kept the definitions visible. The stop conditions were concrete: conflicting current identities, an unexplained reconciliation difference or a customer use outside the agreed scope would stop the pilot.
In this exercise, the reviewers had a validated comparison within two days. Their feedback changed one requested breakdown before we committed to the broader model. I would call that a useful learning and decision outcome, not a complete platform launch or measured revenue gain.
The calculated risk was investing in a bounded model before every future requirement was known. The guardrails made that choice reversible. My lesson was to define the minimum evidence and rollback boundary first, then act rather than wait for certainty about decisions we had not yet needed to make.
Pressure test: What information did you need before acting?
Avoid: Reciting the assumed numbers as personal experience. Replace them with your own defensible incident.
Eight follow-ups with answer direction
What information did you need before acting?
The immediate decision, source/metric meaning, stable identity, consumer scope, acceptance owner and a withdrawal path. I could defer future breakdowns; I could not defer whether the result represented the right population.
Why was this Bias for Action rather than simply doing your job?
The decision was to proceed with a useful bounded test while wider requirements remained unresolved. I explained what I chose to learn through action and why its downside was controlled.
What made the action reversible?
It used an isolated version, restricted reviewers and no automated downstream decision. Withdrawal removed access to the pilot without mutating the canonical source. If customers had already acted irreversibly on its output, that would weaken the reversibility claim.
Why not wait for full approval of the final platform?
The pilot answered a narrow accepted need and did not pre-commit the final design. I still used the authority and controls required for that bounded action. Bias for Action is not permission to bypass access or governance requirements.
What if twelve fixtures passed but the source population was incomplete?
Fixture success would not prove completeness. I would reconcile the expected source population at the agreed cutoff and expose any unsupported scope. The tests and completeness evidence protect different risks.
What did you learn that changed the next step?
In this constructed incident, reviewer feedback changed a breakdown before broader modeling. In a real answer I would name the actual change and why it mattered, rather than claim learning without a changed decision.
What was the cost if the pilot was discarded?
A small bounded implementation and review cost. I would state the actual effort and avoid presenting a discarded pilot as free. The value was reducing a larger uncertain commitment.
How would you scale the approach?
Promote only the validated contract, add owners and production acceptance criteria, and address the new source, history, performance and governance obligations. A successful pilot does not automatically satisfy the production operating bar.
Delivery: give the first answer in about two minutes. Let follow-ups reveal detail; do not squeeze every control into the opening. Keep the same facts when challenged.
19 / Constructed rehearsal 4 min read · 20 min practice
A fast canary exposed a bad optimization
SAY IT / Bias for Action
Tell me about a fast decision that turned out to be wrong.
Reveal worked answer
A historical reporting job was threatening its delivery window, and I had a plausible optimization: restrict processing to a smaller recent event-time range. I did not yet have evidence that it preserved every required correction. Rather than roll the change across the full history or spend days debating the plan, I proposed a bounded shadow run on one partition.
I kept the current production output active and wrote the candidate result to a separate version. Before the run, I defined two acceptance conditions: equivalent key-level results at the same cutoff and a useful reduction in runtime. I also named the rollback owner and limited the extra workload so the experiment would not delay the live job.
The candidate ran in seventy seconds instead of one hundred and twenty in this rehearsal, but the comparison found two missing late corrections. My event-time assumption was wrong: a recent source change could apply to an older business event. I rejected the candidate and kept the prior path. Faster execution was not success when it changed the answer.
I then changed the proposed repair boundary to follow source changes and affected historical partitions, and added those late-correction examples to the regression checks. I would only claim a performance improvement after a new controlled comparison passed; this incident itself establishes a failed experiment caught before publication.
The useful action was running a quick, isolated test that could falsify my idea. The lesson was to define the failure and reversal conditions before looking at attractive timing numbers. I moved quickly on a reversible experiment while preserving the customer’s result.
Pressure test: Was this a failure?
Avoid: Reciting the assumed numbers as personal experience. Replace them with your own defensible incident.
Eight follow-ups with answer direction
Was this a failure?
Yes. My candidate optimization did not preserve the result. The guardrail worked, but that does not make the candidate correct. I should own the mistaken event-time assumption.
Why does a failed experiment show Bias for Action?
It replaced speculation with a bounded test quickly and prevented a wider incorrect change. The action had a defined learning goal, acceptable cost and a protected publication boundary.
What exactly was wrong with the filter?
It selected by business event time when the incremental repair contract needed to include newly observed corrections to older events. Source-change progress and event time answer different questions.
Was the seventy-second run a benchmark?
It was one illustrative run of a candidate that failed correctness. It is not evidence of a production optimization or a stable latency improvement. A valid benchmark needs equivalent output and representative repeated conditions.
Why not deploy and roll back if someone complained?
The mismatch was detectable before consumer exposure. Reversing a dataset later cannot undo decisions users already made from it. A shadow canary was the safer reversible action.
What if the shadow test consumed too much capacity?
I would cap its work, isolate or schedule it appropriately, monitor impact and stop at the agreed limit. The experiment must not undermine the delivery window it is supposed to improve.
How did you communicate the result?
I reported that the candidate was faster but incorrect, kept the existing release, explained the late-correction gap and proposed the next bounded test. I did not selectively report the latency number.
What changed in your future behavior?
I began each optimization with the data-change contract, correctness counterexamples and rollback conditions, then measured runtime. The durable change is the evaluation process, not a claim that future experiments never fail.
Delivery: give the first answer in about two minutes. Let follow-ups reveal detail; do not squeeze every control into the opening. Keep the same facts when challenged.
20 / Pressure drills 5 min read · 20 min practice
Changed assumptions and hard questions
SAY IT / Model change
The PM now asks for regional adoption. What changes?
Reveal worked answer
I would ask whether an account is eligible in every region, only enabled regions or a home region. The original global account-service-day denominator does not answer that. I would define region eligibility independently, version the metric and reduce qualifying activity to account-service-region-day before combining. I would label global and regional measures distinctly and avoid summing regional distinct accounts into a global count.
Pressure test: Could one account count in two regional rates? Yes, if each region has its own legitimate eligible population, but the union needs distinct identity.
Avoid: Reusing the global denominator and silently calling it regional adoption.
SAY IT / Model change
Finance wants reports as originally published, while product wants corrected history. Which is right?
Reveal worked answer
Both can be valid products. I would retain versioned publications for the as-reported view and define how a restated view incorporates later source or dimension corrections. Event-valid time and system-recorded time can help explain what was true versus known, but they do not alone preserve the exact metric code, cutoff and publication. I would version those too and label the views clearly.
Pressure test: What if the metric definition changes, not just the data? Record the definition version and avoid silently comparing incompatible series.
Avoid: Saying Type 2 dimensions alone reproduce every prior report.
SAY IT / Scale trade-off
Your model is correct at one million events. What changes at ten billion?
Reveal worked answer
The logical grain and metric meaning remain, but physical layout and operational boundaries need workload evidence. I would estimate event size, growth, retention, scan volume, cardinality, skew and concurrency. I would partition or cluster for useful pruning and recovery, reduce unnecessary movement, compact files where relevant and build selected aggregates with correct refresh semantics. I would test representative distributions and hot keys, not merely multiply a uniform toy dataset.
Pressure test: What if one tenant supplies most events? Model skew explicitly; average tenant size is not an adequate sizing input.
Avoid: Assuming linear scaling or changing business semantics to improve a benchmark.
SAY IT / SQL semantics
Why did moving a filter make a left join faster but reduce the account count?
Reveal worked answer
A condition on the optional side placed in WHERE can reject the null-extended rows and effectively remove accounts without a match. If the requirement is to preserve eligible inactive accounts, the qualifying activity filter belongs in the right-side input or an appropriate ON clause. I would check the intended population, not treat the row reduction as a free optimization.
Pressure test: Does moving every filter into ON always preserve the original result? No; identify which side and which business population each predicate controls.
Avoid: Accepting fewer rows as evidence of performance success.
SAY IT / Validation
Why can NOT IN or a simple inequality miss the discrepancy?
Reveal worked answer
SQL uses three-valued logic for nulls. A null in a NOT IN comparison set can make a nonmatching comparison unknown; a value inequality against null is also unknown. For an anti-match I would use a correctly correlated NOT EXISTS and define null-key treatment. For value parity I would use supported null-safe comparison or explicit null cases, and compare missing keys in both directions.
Pressure test: Are two null business keys duplicates? That is a data-contract decision; do not let the operator choose entity identity for you.
Avoid: Assuming null means zero, empty string or false.
SAY IT / Closing
Ask two questions that reveal the team’s modeling and operating needs.
Reveal worked answer
I would ask which product decisions depend most on this team’s data, and where correctness, freshness and cost are currently hardest to balance. I would follow up on the grain, customer or workload they describe. I would connect my interest to building explainable product data and diagnosing performance from evidence, rather than list every technology I have used.
Pressure test: What did the answer change about your understanding of the role? Listen for the decision and operating constraint.
Avoid: Asking only which AWS services they use or requesting an interview score.
One-minute follow-up ladder
First challenge
Then defend
Finish with proof
State the grain in one sentence.
What duplicates or changes that entity?
Show one normal record and one correction.
Name the denominator.
Who is missing from the activity data?
Show an eligible zero-activity account.
Name the slow operator.
Why is it slow in this workload?
State the runtime evidence and a falsifiable hypothesis.
Name your fast decision.
Which downside did you bound?
State the observed result and your own lesson.
21 / Active recall 2 min read · 25 min practice
Reconstruct three models and two real incidents
Close the worked answers. Draw each model and explain one proof query, then write a short reconstruction here. The design examples remain hypothetical. For the two behavioral entries, use real personal incidents; the prepared scripts supply structure, not facts. Confirmations are your self-assessments, not automatic verification. Editing notes clears the associated confirmation.
Model reconstruction
AWS product adoption and usage
Model reconstruction
Orders, captures and refunds
Model reconstruction
Subscription cohorts and snapshots
Bias for Action
A reversible pilot while requirements were incomplete
Bias for Action
A fast canary exposed a bad optimization
22 / Knowledge check 7 min read · 20 min practice
Thirty checks before the cold mock
Answer with the worked explanations hidden. Require all answers correct and explain why the tempting alternative fails. Resetting this check clears only quiz answers; it keeps your notes, timers and other progress.
23 / Rehearse 2 min read · 60 min practice
Three full mocks and a targeted repair runner
Three complete interviews, with first-person scripts, explicit follow-ups and checked technical answers.
Read the scripts in the next three chapters, then rehearse cold. Use the 60-minute mode for a full interview or the short drill for concise recall. The continuous clock pauses and resumes; moving stages does not reset it.
Nailed: correct mechanism and a defensible follow-up. Wobbled: right direction with a missing condition or weak evidence. Blank: no usable solution. Repair each gap, then repeat cold.
24 / Complete interview 13 min read · 0 min practice
Full mock A / Product adoption, Redshift movement and a reversible pilot
Six stages, one 60-minute interview. Every question has a complete first-person answer and two answered follow-ups.
Evidence boundary: Technical cases are worked examples. Personal incidents and their specific outcomes are constructed rehearsals anchored to resume programs, not verified autobiographical claims. The introduction reflects the supplied background.
Read the complete transcript on pass 1. On pass 2, keep answers closed and use the runner to speak, code and defend your decisions. Reading is counted in this chapter; one live mock is counted in the runner section. The all-three-mocks total adds two more hours.
STAGE 1 / 00:00 TO 05:00 / 5 MIN
Shafi, introduce yourself and connect your experience to this interview.
Resume-based introduction
Shafi’s full answer + 2 answered follow-ups
I am a data engineer with 16-plus years of experience across enterprise data platforms and product analytics. My background includes telecom data migration at Sprint, the Citi analytics platform and Meta product and payments data. At Meta, my work included Automotive and Travel data foundations, first-party data pipelines, shared metrics and cross-functional delivery. I have led technical work without treating leadership as a reporting-line title.
For modeling and performance, I start from the customer decision and make the grain, identity, history and measure semantics explicit. I then use a representative query and runtime evidence to defend the physical design. I treat faster execution with a changed answer as a failed optimization.
For this role, I am interested in turning complex service and product data into reliable decisions. I would bring the same discipline I use in production: define what the data means, make the implementation understandable, test failure cases and own the operating outcome.
Interviewer follow-ups, fully answered
1. What did you personally own rather than the broader team?
I owned data-engineering decisions and implementation within the programs I worked on: defining transformations and models, resolving data-quality questions, coordinating dependencies and validating outputs. Product managers set product priorities and partner engineers owned other systems. I distinguish those contributions when I describe a result; the size of a product does not make all of its revenue my personal impact.
2. Why are you interested in this Data Engineer II role?
The work described in the posting is close to problems I enjoy: scalable data products, analytics foundations and engineering decisions that serve real consumers. I would evaluate the role through its ownership, customer problems and opportunity to contribute. My experience can help me work independently and mentor through technical judgment, while I remain hands-on with the implementation.
STAGE 2 / 05:00 TO 25:00 / 20 MIN
Design account-level product adoption and usage by day, service and region. Include eligibility, corrections, account-plan history and a global adoption query.
Technical scenario
Complete input fixture
Run this fixture before the answer query
CREATE TABLE changes(event_id TEXT,version INTEGER,account_id TEXT,service_id TEXT,region TEXT,event_at TEXT,units INTEGER,deleted INTEGER);
INSERT INTO changes VALUES('e1',1,'a','s1','us','2026-01-01T11:00:00',10,0);
INSERT INTO changes VALUES('e1',1,'a','s1','us','2026-01-01T11:00:00',10,0);
INSERT INTO changes VALUES('e2',1,'b','s1','us','2026-01-01T13:00:00',20,0);
INSERT INTO changes VALUES('e2',2,'b','s1','us','2026-01-01T13:00:00',30,0);
INSERT INTO changes VALUES('e3',1,'c','s1','us','2026-01-01T14:00:00',5,0);
INSERT INTO changes VALUES('e3',2,'c','s1','us','2026-01-01T14:00:00',0,1);
INSERT INTO changes VALUES('e4',1,'a','s1','eu','2026-01-01T12:00:00',2,0);
CREATE VIEW canonical AS WITH exact AS (SELECT DISTINCT * FROM changes), ranked AS
(SELECT *,ROW_NUMBER() OVER(PARTITION BY event_id ORDER BY version DESC) rn FROM exact)
SELECT event_id,version,account_id,service_id,region,event_at,units,deleted FROM ranked WHERE rn=1;
CREATE VIEW active AS SELECT * FROM canonical WHERE deleted=0;
CREATE TABLE eligibility(day TEXT,account_id TEXT,service_id TEXT,PRIMARY KEY(day,account_id,service_id));
INSERT INTO eligibility VALUES('2026-01-01','a','s1');
INSERT INTO eligibility VALUES('2026-01-01','b','s1');
INSERT INTO eligibility VALUES('2026-01-01','c','s1');
CREATE TABLE history(account_sk INTEGER PRIMARY KEY,account_id TEXT,plan TEXT,valid_from TEXT,valid_to TEXT);
INSERT INTO history VALUES(1,'a','basic','2025-01-01T00:00:00','2026-01-01T12:00:00');
INSERT INTO history VALUES(2,'a','pro','2026-01-01T12:00:00',NULL);
INSERT INTO history VALUES(3,'b','basic','2025-01-01T00:00:00',NULL);
Shafi’s full answer + 2 answered follow-ups
I would separate canonical usage events, eligibility and account history. Canonical usage is one logical event at its authoritative source version; it carries account, service, region, event time, units and deletion status. The reporting aggregate is account-service-region-day. Eligibility is account-service-day because an eligible account with no event still belongs in the denominator.
I would keep a stable account business key and a versioned account dimension with half-open validity intervals. An event-time plan breakdown uses the version valid at the event instant. A current-plan report is a different question and should be named differently. Corrections can affect old business dates, so I would resolve source versions before deriving affected aggregates. A latest delete removes the event from active usage rather than exposing an earlier version.
For global adoption, I deduplicate account-service-day across regions and left join that set to eligibility. In the fixture, three accounts are eligible and two have positive canonical usage, giving 66.67 percent. The same account can use two regions, so I cannot sum regional distinct-account counts into the global result.
I would publish a coherent metric version with definition, cutoff and coverage. The schema supports the query, but the operating contract must also specify late-data handling, rejected identities, backfill ownership and how consumers distinguish as-reported from restated history.
SQL / SQLite proof query
WITH used AS (SELECT DISTINCT substr(event_at,1,10) day,account_id,service_id FROM active WHERE units>0)
SELECT e.day,e.service_id,COUNT(*) eligible,COUNT(u.account_id) adopted,
ROUND(100.0*COUNT(u.account_id)/NULLIF(COUNT(*),0),2) adoption_pct
FROM eligibility e LEFT JOIN used u ON e.day=u.day AND e.account_id=u.account_id AND e.service_id=u.service_id
GROUP BY e.day,e.service_id ORDER BY e.day,e.service_id;
Checked result
day
service_id
eligible
adopted
adoption_pct
2026-01-01
s1
3
2
66.67
Interviewer follow-ups, fully answered
1. What if the account changes plan at noon?
I would join event_time >= valid_from and event_time < valid_to, with a single valid interval per account at that instant. In the fixture, an event before noon remains basic while the event at noon is pro. A current-state lookup would incorrectly relabel the earlier event.
2. How would you model a shared account split across cost centers?
I would use a bridge with an explicit allocation rule, such as weights summing to 10,000 basis points for the applicable period. I would distinguish allocated units from unallocated usage and verify that the chosen allocation preserves the intended total. A many-valued join without weights would multiply the fact.
STAGE 3 / 25:00 TO 40:00 / 15 MIN
The daily adoption query is slow in Redshift. How do you choose a distribution, sort strategy and tuning experiment?
Technical scenario
Shafi’s full answer + 2 answered follow-ups
I would start with the actual workload and plan, not a universal key recommendation. I would separate queue time from execution, inspect scanned rows/bytes, data movement, join cardinality, skew and spill, and compare the estimates with actual behavior. A query that is waiting for resources needs a different intervention from one that is redistributing a large intermediate.
If a frequent large join benefits from colocating data by account and account distribution is reasonably balanced, that can support a distribution choice. A small dimension may be suitable for replication, subject to size and update cost. I would also check automatic table optimization and current physical design before overriding it. For time-bounded reads, a useful sort arrangement can improve block elimination; it does not substitute for distribution or fix a fanout join.
My first experiment would target the evidenced bottleneck, such as preaggregating to account-service-day before a compatible join or reducing unnecessary columns. I would compare equivalent keys, values and distinct populations, then benchmark representative volume, skew and concurrency. I would report p50/p95, cost and regression risk, and roll out with a reversal path.
Interviewer follow-ups, fully answered
1. Can preaggregation change adoption?
Yes. If I aggregate away account identity before computing distinct adopters, I can lose the information needed for global deduplication. I would retain the identity components required by the measure and verify the resulting grain before treating fewer rows as an optimization.
2. What if one account is extremely hot?
I would measure the skew and determine whether the join or aggregation can be safely decomposed. I would not choose the hot key solely because it appears in a join. The remedy could involve a different physical layout or a semantically valid partial aggregation, and I would test the effect on both the hot case and the normal workload.
STAGE 4 / 40:00 TO 50:00 / 10 MIN
Tell me about a time you acted before all requirements were known.
Constructed personal incident; resume program anchor
Shafi’s full answer + 2 answered follow-ups
A product team needed to compare an emerging service’s usage before the broader reporting requirements were settled. I owned the data foundation. Waiting for a complete cross-product specification would have delayed the immediate decision, but publishing a loosely defined metric to everyone could create a misleading standard.
I separated the reversible part from the consequential part. I proposed an isolated, read-only pilot for one service and five named reviewers, using two understood sources and a written eligibility rule. I explicitly excluded automated decisions and broader publication. Before starting, I confirmed the source cutoff, the business key, who would accept the result and how we could withdraw the pilot.
I built the smallest useful model and tested twelve representative boundary cases, including duplicate input, a correction and an account with no usage. I reviewed the values with the product owner and kept the definitions visible. The stop conditions were concrete: conflicting current identities, an unexplained reconciliation difference or a customer use outside the agreed scope would stop the pilot.
In this exercise, the reviewers had a validated comparison within two days. Their feedback changed one requested breakdown before we committed to the broader model. I would call that a useful learning and decision outcome, not a complete platform launch or measured revenue gain.
The calculated risk was investing in a bounded model before every future requirement was known. The guardrails made that choice reversible. My lesson was to define the minimum evidence and rollback boundary first, then act rather than wait for certainty about decisions we had not yet needed to make.
Interviewer follow-ups, fully answered
1. What made the pilot reversible?
The pilot used an isolated version, five named reviewers and no automated downstream decision. I could withdraw the view without mutating the canonical source. I also defined stop conditions for identity conflicts, unexplained reconciliation differences and use outside the agreed scope.
2. What did you refuse to defer?
I needed the immediate decision, source meaning, stable identity, cutoff, acceptance owner and withdrawal path before proceeding. I could defer future breakdowns. I could not defer whether the output represented the right population.
STAGE 5 / 50:00 TO 55:00 / 5 MIN
The PM wants global adoption by adding all regional adoption counts. How do you respond?
Technical scenario
Shafi’s full answer + 2 answered follow-ups
I would show the smallest counterexample: account a appears in both EU and US, while b appears only in US. Regional counts are one and two, but the global distinct count is two, not three. I would compute the global set at account-service-day grain and use the global eligibility population for the denominator.
If the PM actually wants region-account engagements, summing can be a valid different measure. I would name that measure explicitly so a convenient aggregation does not change the business meaning unnoticed.
Interviewer follow-ups, fully answered
1. Can you approximate the distinct count?
Only if an approximation is acceptable for the decision and the error contract is explicit. I would not substitute an approximate sketch for an exact requirement without agreement. I would also consider whether the chosen sketch can be merged consistently across regions and time.
2. What would you put in the metric definition?
I would state the entity, eligibility, qualifying activity, time zone, observation cutoff, cross-region deduplication and exact-versus-approximate behavior. That definition lets another engineer reproduce the answer rather than infer meaning from a dashboard label.
STAGE 6 / 55:00 TO 60:00 / 5 MIN
What questions do you have for me, and how would you close?
Role-focused closing
Shafi’s full answer + 2 answered follow-ups
I have two questions. Which analytical workloads are hardest to keep correct as product definitions change? How does the team compare performance improvements while protecting correctness and operating cost?
The reason I ask is that I want to understand the decisions the data supports and where the team’s operating burden sits. My experience is most useful when I can connect a data contract to a customer outcome and then carry the implementation through validation and operation. Thank you for the discussion; the problems we covered reinforce my interest in doing that work here.
Interviewer follow-ups, fully answered
1. What would you focus on during your first month?
I would learn one important consumer workflow end to end, trace its data sources and definitions, and understand the team’s release and incident practices. I would pair with an engineer on a small, useful change rather than propose a redesign before understanding the constraints. By the end of the month I would aim to own a bounded improvement with an agreed acceptance test and a clear operational handoff.
2. What would you do if the team’s approach differed from your previous company?
I would first understand which constraints the approach solves: scale, staffing, correctness, cost or customer expectations. I would compare mechanisms rather than brand names. If I saw a material issue, I would bring a concrete example and an alternative, then work through the accountable owner. Once a legitimate decision was made, I would help implement it well.
25 / Complete interview 12 min read · 0 min practice
Full mock B / Payments, row-store tuning and a fast wrong optimization
Six stages, one 60-minute interview. Every question has a complete first-person answer and two answered follow-ups.
Evidence boundary: Technical cases are worked examples. Personal incidents and their specific outcomes are constructed rehearsals anchored to resume programs, not verified autobiographical claims. The introduction reflects the supplied background.
Read the complete transcript on pass 1. On pass 2, keep answers closed and use the runner to speak, code and defend your decisions. Reading is counted in this chapter; one live mock is counted in the runner section. The all-three-mocks total adds two more hours.
STAGE 1 / 00:00 TO 05:00 / 5 MIN
Shafi, introduce yourself and connect your experience to this interview.
Resume-based introduction
Shafi’s full answer + 2 answered follow-ups
I am a data engineer with 16-plus years of experience across enterprise data platforms and product analytics. My background includes telecom data migration at Sprint, the Citi analytics platform and Meta product and payments data. At Meta, my work included Automotive and Travel data foundations, first-party data pipelines, shared metrics and cross-functional delivery. I have led technical work without treating leadership as a reporting-line title.
For modeling and performance, I start from the customer decision and make the grain, identity, history and measure semantics explicit. I then use a representative query and runtime evidence to defend the physical design. I treat faster execution with a changed answer as a failed optimization.
For this role, I am interested in turning complex service and product data into reliable decisions. I would bring the same discipline I use in production: define what the data means, make the implementation understandable, test failure cases and own the operating outcome.
Interviewer follow-ups, fully answered
1. What did you personally own rather than the broader team?
I owned data-engineering decisions and implementation within the programs I worked on: defining transformations and models, resolving data-quality questions, coordinating dependencies and validating outputs. Product managers set product priorities and partner engineers owned other systems. I distinguish those contributions when I describe a result; the size of a product does not make all of its revenue my personal impact.
2. Why are you interested in this Data Engineer II role?
The work described in the posting is close to problems I enjoy: scalable data products, analytics foundations and engineering decisions that serve real consumers. I would evaluate the role through its ownership, customer problems and opportunity to contribute. My experience can help me work independently and mentor through technical judgment, while I remain hands-on with the implementation.
STAGE 2 / 05:00 TO 25:00 / 20 MIN
Model order lines, multiple captures and partial refunds. Produce order-level line amount, captured amount and net collected without fanout.
Technical scenario
Complete input fixture
Run this fixture before the answer query
CREATE TABLE order_line(line_id TEXT PRIMARY KEY,order_id TEXT,net_cents INTEGER,currency TEXT);
INSERT INTO order_line VALUES('l1','o1',9000,'USD');
INSERT INTO order_line VALUES('l2','o1',4500,'USD');
CREATE TABLE capture(capture_id TEXT PRIMARY KEY,order_id TEXT,cents INTEGER,currency TEXT);
INSERT INTO capture VALUES('p1','o1',8000,'USD');
INSERT INTO capture VALUES('p2','o1',5500,'USD');
CREATE TABLE refund(refund_id TEXT PRIMARY KEY,capture_id TEXT,line_id TEXT,cents INTEGER,currency TEXT);
INSERT INTO refund VALUES('r1','p2','l2',3000,'USD');
CREATE TABLE allocation(capture_id TEXT,line_id TEXT,cents INTEGER,PRIMARY KEY(capture_id,line_id));
INSERT INTO allocation VALUES('p1','l1',8000);
INSERT INTO allocation VALUES('p2','l1',1000);
INSERT INTO allocation VALUES('p2','l2',4500);
Shafi’s full answer + 2 answered follow-ups
I would keep each business process at its natural grain. An order line records the purchased item amount and currency. A capture records a payment transaction against an order. A refund records a refund transaction tied to its capture and, when required, the affected line. I would not join all three raw facts and assume order_id makes the resulting row meaningful.
For order-level reporting, I would aggregate each process to order and currency before combining the results. In the fixture, line value and captures are both 13,500 cents and the refund is 3,000 cents, so net collected is 10,500 cents. The query below preserves that relationship. If I need line-level payment attribution, I would use an allocation table whose amounts reconcile to each capture, not multiply a capture across every line.
I would define authorized, captured, settled, refunded and disputed states separately because they answer different questions. Currency conversion also needs a rate source and effective-time rule; I would not sum unlike currencies. Stable transaction IDs and source versions support replay and corrections.
The acceptance tests would cover multiple lines, multiple captures, partial and repeated refunds, a refund without a valid capture, rounding and deletes. Publication would include a coherent cutoff so a capture table from one release is not combined with a refund table from another.
SQL / SQLite proof query
WITH l AS (SELECT order_id,currency,SUM(net_cents) cents FROM order_line GROUP BY order_id,currency),
c AS (SELECT order_id,currency,SUM(cents) cents FROM capture GROUP BY order_id,currency),
r AS (SELECT c.order_id,r.currency,SUM(r.cents) cents FROM refund r JOIN capture c ON r.capture_id=c.capture_id GROUP BY c.order_id,r.currency)
SELECT l.order_id,l.currency,l.cents order_net,c.cents captures,COALESCE(r.cents,0) refunds,
c.cents-COALESCE(r.cents,0) net_collected
FROM l LEFT JOIN c ON l.order_id=c.order_id AND l.currency=c.currency
LEFT JOIN r ON l.order_id=r.order_id AND l.currency=r.currency;
Checked result
order_id
currency
order_net
captures
refunds
net_collected
o1
USD
13500
13500
3000
10500
Interviewer follow-ups, fully answered
1. Why does joining facts first inflate the result?
Two lines joined to two captures produce four combinations before refunds are considered. Each amount can appear multiple times. SUM(DISTINCT amount) is not a repair because two legitimate captures can have equal amounts. I align grains before combining the measures.
2. How do you prove line allocation?
I would check uniqueness of the capture-line allocation key, currency consistency and that allocations sum to the capture amount under the rounding rule. I would reconcile line-level attributed payments back to order-level captured totals and make any unallocated balance explicit.
STAGE 3 / 25:00 TO 40:00 / 15 MIN
The payment report runs on an Oracle-style row store and scans too much data. What do you do?
Technical scenario
Shafi’s full answer + 2 answered follow-ups
I would inspect the actual predicates, selected population, execution plan and row counts. I would determine whether the cost comes from a broad scan, repeated table access, a poor join estimate or an accidental many-to-many intermediate. The grain repair comes before physical tuning.
For a selective order or time-range lookup, an index aligned with the access pattern may help. Composite key order should reflect how predicates constrain the leading columns and how the engine can use the remaining range. I would not assume the same index helps a report reading most of the table. I would also check statistics, partition pruning and whether functions or conversions prevent the intended access path.
I would test one change on representative inputs and compare exact financial identities and amounts before comparing runtime. I would include write overhead, index storage and maintenance in the decision. A production rollout would preserve a rollback path and monitor both query latency and ingestion cost.
Interviewer follow-ups, fully answered
1. What if the plan looks better but runtime is worse?
I would treat the measured workload as the evidence and investigate cache state, concurrency, actual rows and I/O. An estimated cost or appealing operator name is not the outcome. I would repeat representative runs and reject a change that does not meet the agreed latency and cost goal.
2. Can a function on a timestamp prevent pruning?
It can interfere with an otherwise useful predicate depending on the engine and expression. I would prefer a semantically equivalent explicit range when appropriate, verify the plan and test boundary behavior. I would not rewrite time logic in a way that changes the time zone or cutoff just to obtain a scan reduction.
STAGE 4 / 40:00 TO 50:00 / 10 MIN
Tell me about a quick action that exposed your own wrong assumption.
Constructed personal incident; resume program anchor
Shafi’s full answer + 2 answered follow-ups
A historical reporting job was threatening its delivery window, and I had a plausible optimization: restrict processing to a smaller recent event-time range. I did not yet have evidence that it preserved every required correction. Rather than roll the change across the full history or spend days debating the plan, I proposed a bounded shadow run on one partition.
I kept the current production output active and wrote the candidate result to a separate version. Before the run, I defined two acceptance conditions: equivalent key-level results at the same cutoff and a useful reduction in runtime. I also named the rollback owner and limited the extra workload so the experiment would not delay the live job.
The candidate ran in seventy seconds instead of one hundred and twenty in this rehearsal, but the comparison found two missing late corrections. My event-time assumption was wrong: a recent source change could apply to an older business event. I rejected the candidate and kept the prior path. Faster execution was not success when it changed the answer.
I then changed the proposed repair boundary to follow source changes and affected historical partitions, and added those late-correction examples to the regression checks. I would only claim a performance improvement after a new controlled comparison passed; this incident itself establishes a failed experiment caught before publication.
The useful action was running a quick, isolated test that could falsify my idea. The lesson was to define the failure and reversal conditions before looking at attractive timing numbers. I moved quickly on a reversible experiment while preserving the customer’s result.
Interviewer follow-ups, fully answered
1. Why was 70 seconds not a successful result?
The candidate omitted two late corrections that the 120-second baseline included. That changed the answer, so the speed improvement failed the acceptance contract. I kept the prior path and corrected the event-time versus source-change assumption before considering another optimization.
2. What did the canary teach you that a large rollout would not?
It made the failure visible on a bounded representative scope with a clear comparison and rollback. The useful learning was the missing correction case. A broad rollout could have propagated the wrong historical output before we understood that mechanism.
STAGE 5 / 50:00 TO 55:00 / 5 MIN
What exactly do you compare to establish query equivalence?
Technical scenario
Shafi’s full answer + 2 answered follow-ups
I compare the requested keys, column values, nulls, multiplicities, units and rounding at the same source cutoff. I include records that are absent from one result, because matching only intersecting keys can hide population loss. For floating calculations I use a justified tolerance; for integer cents I expect exact values.
I use both targeted counterexamples and a representative population comparison. Grand totals are one useful check but can conceal offsetting errors or wrong attribution. I would record any intentional semantic change separately instead of labeling a changed result an optimization.
Interviewer follow-ups, fully answered
1. What if both outputs have the same row count?
Equal row count does not prove equal identity or values. One missing key and one extra key can cancel in the count. I would perform key-aware reconciliation and compare multiplicities where duplicates are meaningful.
2. How do you handle nondeterministic ties?
I would first decide whether output order or winner choice is part of the contract. If exactly one winner is required, I would add a documented total tie-break order. I would not let an unstable input order masquerade as semantic equivalence.
STAGE 6 / 55:00 TO 60:00 / 5 MIN
What questions do you have for me, and how would you close?
Role-focused closing
Shafi’s full answer + 2 answered follow-ups
I have two questions. Which analytical workloads are hardest to keep correct as product definitions change? How does the team compare performance improvements while protecting correctness and operating cost?
The reason I ask is that I want to understand the decisions the data supports and where the team’s operating burden sits. My experience is most useful when I can connect a data contract to a customer outcome and then carry the implementation through validation and operation. Thank you for the discussion; the problems we covered reinforce my interest in doing that work here.
Interviewer follow-ups, fully answered
1. What would you focus on during your first month?
I would learn one important consumer workflow end to end, trace its data sources and definitions, and understand the team’s release and incident practices. I would pair with an engineer on a small, useful change rather than propose a redesign before understanding the constraints. By the end of the month I would aim to own a bounded improvement with an agreed acceptance test and a clear operational handoff.
2. What would you do if the team’s approach differed from your previous company?
I would first understand which constraints the approach solves: scale, staffing, correctness, cost or customer expectations. I would compare mechanisms rather than brand names. If I saw a material issue, I would bring a concrete example and an alternative, then work through the accountable owner. Once a legitimate decision was made, I would help implement it well.
26 / Complete interview 12 min read · 0 min practice
Full mock C / Subscription cohorts, Spark skew and a bounded incident response
Six stages, one 60-minute interview. Every question has a complete first-person answer and two answered follow-ups.
Evidence boundary: Technical cases are worked examples. Personal incidents and their specific outcomes are constructed rehearsals anchored to resume programs, not verified autobiographical claims. The introduction reflects the supplied background.
Read the complete transcript on pass 1. On pass 2, keep answers closed and use the runner to speak, code and defend your decisions. Reading is counted in this chapter; one live mock is counted in the runner section. The all-three-mocks total adds two more hours.
STAGE 1 / 00:00 TO 05:00 / 5 MIN
Shafi, introduce yourself and connect your experience to this interview.
Resume-based introduction
Shafi’s full answer + 2 answered follow-ups
I am a data engineer with 16-plus years of experience across enterprise data platforms and product analytics. My background includes telecom data migration at Sprint, the Citi analytics platform and Meta product and payments data. At Meta, my work included Automotive and Travel data foundations, first-party data pipelines, shared metrics and cross-functional delivery. I have led technical work without treating leadership as a reporting-line title.
For modeling and performance, I start from the customer decision and make the grain, identity, history and measure semantics explicit. I then use a representative query and runtime evidence to defend the physical design. I treat faster execution with a changed answer as a failed optimization.
For this role, I am interested in turning complex service and product data into reliable decisions. I would bring the same discipline I use in production: define what the data means, make the implementation understandable, test failure cases and own the operating outcome.
Interviewer follow-ups, fully answered
1. What did you personally own rather than the broader team?
I owned data-engineering decisions and implementation within the programs I worked on: defining transformations and models, resolving data-quality questions, coordinating dependencies and validating outputs. Product managers set product priorities and partner engineers owned other systems. I distinguish those contributions when I describe a result; the size of a product does not make all of its revenue my personal impact.
2. Why are you interested in this Data Engineer II role?
The work described in the posting is close to problems I enjoy: scalable data products, analytics foundations and engineering decisions that serve real consumers. I would evaluate the role through its ownership, customer problems and opportunity to contribute. My experience can help me work independently and mentor through technical judgment, while I remain hands-on with the implementation.
STAGE 2 / 05:00 TO 25:00 / 20 MIN
Design subscription cohort retention and daily balances. An account can own multiple subscriptions. What can be summed, and what is the denominator?
Technical scenario
Complete input fixture
Run this fixture before the answer query
CREATE TABLE subscription(subscription_id TEXT PRIMARY KEY,account_id TEXT,started_at TEXT,ended_at TEXT);
INSERT INTO subscription VALUES('a1','a','2026-01-03','2026-02-10');
INSERT INTO subscription VALUES('a2','a','2026-01-20',NULL);
INSERT INTO subscription VALUES('b1','b','2026-01-05','2026-01-31');
CREATE TABLE balance(day TEXT,account_id TEXT,cents INTEGER,PRIMARY KEY(day,account_id));
INSERT INTO balance VALUES('2026-01-01','a',10000);
INSERT INTO balance VALUES('2026-01-01','b',5000);
INSERT INTO balance VALUES('2026-01-02','a',8000);
INSERT INTO balance VALUES('2026-01-02','b',5000);
Shafi’s full answer + 2 answered follow-ups
I would distinguish account retention from subscription retention. A subscription fact has subscription_id, account_id, start and end semantics. An account is retained if the agreed qualifying condition holds for at least one relevant subscription at the observation point; counting subscriptions can overcount accounts with multiple active contracts.
I would define cohort entry explicitly, such as the first qualifying subscription start, and state whether reactivation creates a new cohort or returns to the original one. I would also define the observation cutoff. A cohort whose next observation period has not yet occurred is unobservable, not churned.
Daily balance is a periodic snapshot at account-day grain. It can be summed across accounts for the same day and compatible currency, but summing a balance across days changes its meaning. An end-of-period balance uses the appropriate snapshot, while an average daily balance is a separately defined calculation.
The proof query below computes the fixture’s account retention using account-level deduplication. The history and load contract would preserve late corrections and distinguish what was known at a prior reporting cutoff from restated results. I would validate cohort size, observation eligibility, duplicate subscriptions and exact boundary dates before publishing a rate.
SQL / SQLite proof query
WITH first_activation AS (SELECT account_id,MIN(started_at) first_at FROM subscription GROUP BY account_id),
cohort AS (SELECT account_id FROM first_activation WHERE first_at>='2026-01-01' AND first_at<'2026-02-01'),
retained AS (SELECT DISTINCT account_id FROM subscription WHERE started_at<='2026-02-01' AND (ended_at>'2026-02-01' OR ended_at IS NULL))
SELECT COUNT(*) cohort_accounts,COUNT(r.account_id) retained_accounts,ROUND(100.0*COUNT(r.account_id)/NULLIF(COUNT(*),0),2) pct
FROM cohort c LEFT JOIN retained r ON c.account_id=r.account_id;
Checked result
cohort_accounts
retained_accounts
pct
2
1
50.0
Interviewer follow-ups, fully answered
1. One account has two active subscriptions. Is it two retained customers?
No, not for an account-level customer metric. I would reduce the qualifying subscriptions to one account-level indicator before forming the numerator. If the requested measure is subscription retention, then subscription identity is the appropriate unit and the denominator must match it.
2. How do you report a cohort that has not reached month three?
I would mark month three as not yet observable and exclude it from an observed-retention denominator. I would not fill the future with zero, because that confuses missing evidence with an observed customer departure.
STAGE 3 / 25:00 TO 40:00 / 15 MIN
A Spark aggregation is slow because a few tasks run much longer than the rest. How do you diagnose and repair it?
Technical scenario
Shafi’s full answer + 2 answered follow-ups
I would inspect the stage and task evidence: input size, shuffle read/write, spill, executor time and the distribution of records by key. I would verify whether the long tasks reflect skew, a fanout join, oversized partitions, slow input or another bottleneck. Adding executors does not necessarily split a single hot key’s work.
If the business operation supports it, I could preaggregate before a join or use a two-stage aggregation that preserves the measure. A small dimension may be a broadcast candidate, but I would validate its size and executor memory impact rather than force a broadcast based on a name. I would consider adaptive execution behavior and the actual partition sizes in the environment.
I would avoid collecting a large result to the driver for convenience. I would use native expressions where they express the transformation and measure any UDF cost. After a change, I would compare result keys, values and multiplicities and benchmark skewed as well as typical inputs. The goal is lower end-to-end latency without changing cohort or balance semantics.
Interviewer follow-ups, fully answered
1. Can you salt an exact distinct count and sum the partial counts?
Not generally. The same entity can appear in multiple salted groups, so summing partial distinct counts can double count it. I would preserve the identity needed for a correct second-stage deduplication or use an explicitly approved mergeable approximation.
2. What if the fastest option uses much more memory?
I would evaluate spill, failure risk and concurrency under representative load. A faster isolated run that causes executor failures or reduces overall throughput may be a poor production choice. I would state the latency, resource and reliability trade-off explicitly.
STAGE 4 / 40:00 TO 50:00 / 10 MIN
Tell me about a time you acted quickly during an incident without waiting for the perfect fix.
Constructed personal incident; publication/recovery anchor
Shafi’s full answer + 2 answered follow-ups
During an analytics incident, one source partition stopped arriving before an important review. I did not yet know the complete upstream cause, but I knew that publishing the current aggregate as final would misrepresent a specific customer segment. I owned the downstream publication decision.
I checked the manifest and traced representative missing identities to establish the affected scope. I retained the last validated version with visible staleness and gave the consumer a bounded alternative for the unaffected population. I named the incident owner and the next recovery checkpoint, rather than wait to communicate until I had a reassuring explanation.
I then worked with the source owner on recovery, replayed the missing partition through the normal validation path and reconciled the corrected version before switching readers. The action was reversible: it protected the consumer from an incomplete release while preserving the raw evidence and the prior valid output.
The result was a controlled publication decision and a verified recovery. I learned that speed can mean making the safest useful decision with the evidence available, while continuing the investigation. It does not require claiming that the root cause is already known.
Interviewer follow-ups, fully answered
1. What evidence was sufficient for the immediate action?
I had confirmed the expected partition, its absence from the accepted input and the consumer segment it represented. That was enough to avoid publishing the result as complete. I did not need the entire upstream root cause to make that bounded publication decision.
2. What would make the action irreversible?
If customers had already made consequential decisions from an unlabeled partial result, withdrawing the file would not undo those effects. That is why I kept the status and scope explicit before publication and avoided automated downstream use of the incomplete view.
STAGE 5 / 50:00 TO 55:00 / 5 MIN
A late correction arrives for last month after the current report has shipped. What changes?
Technical scenario
Shafi’s full answer + 2 answered follow-ups
I would apply the correction to canonical history using the source’s ordering rule, determine which derived periods and measures depend on it, and recompute the affected outputs. I would publish a new coherent version and expose the revision, rather than silently change a number whose prior version supported a decision.
If the customer needs as-reported history, I preserve the prior reporting version or system-time record. If they want the best current understanding of last month, I serve the restated version. Those are different questions and should remain distinguishable.
Interviewer follow-ups, fully answered
1. Can event-time partitioning alone locate the change?
The source change can arrive now while referring to an older business event. I need the source-change information and the affected event identity to locate the relevant historical partition. Filtering only recent event dates can miss it.
2. What do you tell the consumer?
I would state the affected period and population, the reason for the revision, the previous and current release identifiers, and whether the interpretation of the decision changes. I would distinguish a technical backfill from a meaningful correction to their prior report.
STAGE 6 / 55:00 TO 60:00 / 5 MIN
What questions do you have for me, and how would you close?
Role-focused closing
Shafi’s full answer + 2 answered follow-ups
I have two questions. Which analytical workloads are hardest to keep correct as product definitions change? How does the team compare performance improvements while protecting correctness and operating cost?
The reason I ask is that I want to understand the decisions the data supports and where the team’s operating burden sits. My experience is most useful when I can connect a data contract to a customer outcome and then carry the implementation through validation and operation. Thank you for the discussion; the problems we covered reinforce my interest in doing that work here.
Interviewer follow-ups, fully answered
1. What would you focus on during your first month?
I would learn one important consumer workflow end to end, trace its data sources and definitions, and understand the team’s release and incident practices. I would pair with an engineer on a small, useful change rather than propose a redesign before understanding the constraints. By the end of the month I would aim to own a bounded improvement with an agreed acceptance test and a clear operational handoff.
2. What would you do if the team’s approach differed from your previous company?
I would first understand which constraints the approach solves: scale, staffing, correctness, cost or customer expectations. I would compare mechanisms rather than brand names. If I saw a material issue, I would bring a concrete example and an alternative, then work through the accountable owner. Once a legitimate decision was made, I would help implement it well.
The mastery target requires 30/30 knowledge answers, every chapter recalled, three reconstructed designs, two personally supported incidents, all eighteen full-mock stage ratings marked nailed, all nine gates below and zero unresolved repairs. These are self-recorded preparation conditions, not Amazon’s scoring criteria. The full-study estimate includes one mock; all-three-mock time adds two hours.
28 / References 3 min read · 2 min practice
Sources, scope and revision record
Prepared November 12, 2026. The recruiter transcript establishes the competency bundle. The technical references establish general mechanics. All model domains, numerical fixtures, interview prompts and incident details are teaching examples unless explicitly identified as resume-reported anchors. They are not Amazon’s internal architecture, question bank or evaluation rubric.
Local source
Used for
Boundary
Recruiter Call for next role.txt
Data modeling and performance tuning; Bias for Action.
Prep label only; no confirmed scheduled order.
MShafi_ResumeDE (1).pdf
Meta product/data foundation and Citi tuning anchors.
Constructed incidents and assumed numbers are not verified accomplishments.
Supplied role context; not the actual team schema.
Build evidence
Fifteen SQLite query fixtures, one actual scan-to-index plan comparison on 50,000 deterministic rows, and 100 shuffled repeated-delivery trials are executed during the build. SQL results are embedded so the file stays offline. The browser displays checked solutions; it does not run an in-browser SQL engine. Oracle, Redshift and Spark examples are teaching guidance, not executed production benchmarks.
Revision log
Revision
Date
Change
1.0
2026-11-12
Three worked models, history/correction contracts, engine-specific tuning, fifteen query proofs, two constructed Bias for Action rehearsals with sixteen answered follow-ups, thirty knowledge checks, three full mocks, timing, recall workspace and persistent progress.
Two content passes: scope and worked-answer construction, followed by edge-case, arithmetic, source and query review. Browser checks cover controls, storage, import/export, timing, responsiveness and print layout. Verification details are stored with the build source. This file embeds its fonts and makes no automatic network requests. External references open only when chosen.
Revision 1.2 / 2026-11-13
Added three complete 60-minute interview transcripts: 18 first-person primary answers and 36 fully answered follow-ups. Added complete technical fixtures and checked solutions where relevant, linked all stages to the existing timer and repair queue, and recalculated sidebar and full-guide study times. Preserved existing saved notes and migrated prior mock ratings into individual practice.