SQL & Data Engineering: the practice test

ROW_NUMBER is the only one of these that guarantees unique sequential numbers, so 'keep rows numbered 1 to 3' returns exactly three. RANK would return four rows when two tie for second, DENSE_RANK can return even more, and NTILE answers a different question entirely. The deterministic part matters in production: without a full tiebreaker in the ORDER BY, ROW_NUMBER can order tied rows differently on each run, and your 'top 3' quietly changes between refreshes. Interviewers use this trio to check you know what happens on ties, because real data always has them.

The fact table records events you measure, one row per event at a stated grain ('one row per completed trip'), carrying numeric measures and foreign keys. Dimensions answer who, what, where and when, and are joined in for slicing. Declaring the grain out loud is what separates practitioners: half of all star-schema bugs are grain confusion, like mixing trip-level and payment-level rows in one fact. Facts do tend to be huge and dimensions small, but that is a consequence of the definition, not the definition, and interviewers notice which one you lead with.