Card 1 of 20
Front. Join Multiply. An order has three line items and two payments. What happens when both child tables are joined before aggregation?
These cards are original study material. They are not questions from a named employer or a live hiring loop.
All 20 cards in this deck
1.Join Multiply
An order has three line items and two payments. What happens when both child tables are joined before aggregation?
The join produces six combinations and can multiply both amounts. Aggregate each child to order grain before joining, or calculate measures independently at their intended grain.
2.Outer Filter
Why does WHERE payment.status = 'paid' after a LEFT JOIN remove orders with no payment?
Unmatched rows have null payment fields and fail the predicate. Put the restriction in the join condition when unmatched orders must remain.
3.Anti Null
Why can NOT IN (SELECT customer_id ...) unexpectedly return no unmatched rows?
A null in the subquery can make comparisons unknown. Prefer a correctly correlated NOT EXISTS or explicitly exclude nulls under the intended semantics.
4.Window Ties
Why does a running sum ordered only by date sometimes jump for all rows on the same date?
A default peer-aware frame can include all rows tied on the ordering value. Specify a ROWS frame and deterministic tie-breaker when a row-by-row accumulation is intended.
5.Latest Row
How should ROW_NUMBER choose one latest record when several records share updated_at?
Order by updated_at plus a stable authoritative tie-breaker such as source sequence. Without a total order, reruns may select different winners.
6.Window Filter
Why must filtering on a window rank usually happen in an outer query in PostgreSQL?
Window functions are computed after WHERE. Compute the rank in a subquery or CTE, then filter it; other engines may provide QUALIFY.
7.Scd Range
How should an event join to a type-2 customer dimension without matching two versions at a boundary?
Use half-open intervals: event_time >= valid_from and event_time < valid_to, with an explicit open-ended policy. Test that intervals for one business key never overlap.
8.Late Dimension
A corrected customer tier arrives today but was effective last month. What must a historical model reconsider?
The affected validity intervals and facts whose event times fall within them. Merely appending a current version preserves the wrong historical attribution.
9.Valid Versus Recorded
Why might a model need both effective time and recorded time?
One answers when a fact was true in the business; the other answers when the system knew it. Keeping both supports corrected history and reproducing past reports.
10.Nonadditive
Why can summing daily distinct users overstate monthly distinct users?
The same user can appear on several days. Recompute distinct users over the month or combine a compatible mergeable sketch with an understood approximation error.
11.Weighted Rate
Two regions have conversion rates of 10% and 50%. Why is their combined rate not necessarily 30%?
The denominators may differ. Sum conversions and eligible opportunities, then divide; averaging ratios is valid only under the corresponding equal-weight definition.
12.Snapshot Measure
Why should end-of-day account balances not be summed across days to report a month-end balance?
Balances are snapshots, not additive flows over time. Select the intended endpoint or compute an explicitly defined average balance.
13.Missing Day
A sales query contains no row for Tuesday. How can a report distinguish zero sales from missing ingestion?
Join a calendar spine to aggregates and separately check source completeness. Filling absence with zero before verifying ingestion can hide a failed load.
14.Incremental Lookback
Why does filtering updated_at > last_run miss some corrections?
Late transactions, equal timestamps, clock skew, or historical updates can fall outside the boundary. Use a stable cursor or overlap-and-merge policy with a recovery path for older corrections.
15.Delete Incremental
Why does an upsert-only incremental model retain records deleted at the source?
No surviving row signals the deletion. Consume tombstones, compare authoritative snapshots, or apply a defined deletion feed before claiming synchronization.
16.Merge Duplicate
Why must a staging dataset be deduplicated by the merge key before MERGE?
Several source rows may target one destination row, producing an error or engine-specific ambiguity. Choose the winner using authoritative ordering before merging.
17.Partition Correction
A corrected record changes its business date. Why can rewriting only its new date partition leave a duplicate?
The old version still exists in the original partition. Track both old and new locations or use a key-aware mutation that removes the previous row.
18.Unknown Member
What is the advantage of an explicit unknown dimension member over dropping a fact with a missing dimension?
It preserves measurable facts while making unresolved attribution visible. Backfill the relationship later and monitor unresolved counts; the placeholder must not conceal permanent quality defects.
19.Partition Evolution
Does changing an Iceberg partition specification automatically rewrite old files into the new layout?
No. Old and new specifications can coexist, and readers use metadata to plan them. Rewriting old data is a separate maintenance decision.
20.Metric Contract
What should a revenue table specify beyond its column names?
Row grain, currency and conversion policy, recognition date, refund treatment, uniqueness, and completeness expectations. Matching schemas do not guarantee matching business meaning.