Joins and aggregations at scale

6 h
requires SPK-05

From your world. Redshift DISTSTYLE ALL ≈ broadcast; distribution keys and skew

Practice quiz with instant feedback. For a scored run that counts toward your local progress, use uv run de quiz SPK-06.

1You join a 2 billion-row fact to a 500-row dimension on company_id. Which join strategy is ideal and why? ASort-merge join — it always scales best. BBroadcast hash join — the tiny dimension is shipped to every executor, so the fact streams through with no shuffle. CCross join then filter. DShuffle hash join on the fact side only.

3A developer wraps the 1-billion-row fact in F.broadcast() to 'speed up' the join. What happens? AIt is the fastest possible join. BSpark tries to collect the huge table to the driver and broadcast it, risking a driver OOM and huge network cost. CSpark silently converts it to a sort-merge join with no downside. DThe result is wrong.

4You need a left join of orders to payments. You must keep every order, even if it has no payment. Someone used an inner join. What is the bug? ANo bug; inner and left are equivalent here. BInner join drops orders that have no matching payment, so unpaid orders disappear from the result. CLeft join is slower, so inner is correct. DInner join duplicates rows.

5One company_id value owns 80% of the fact rows, so one join task runs far longer than the others. What is this, and which fix does NOT change the result? ASpill; raise executor memory. BData skew; broadcast the small side, or salt the hot key on both sides and aggregate back, or enable AQE skew-join handling. CA memory leak; restart the driver. DSkew; just use an inner join.

6When is a broadcast VARIABLE (sc.broadcast) the right tool, as opposed to a broadcast JOIN? ATo shuffle two large tables. BTo ship a read-only lookup object (dict/model) to every executor once, e.g. for use inside mapPartitions or a UDF. CTo count bad rows. DTo force a sort-merge join.

7Why should an accumulator not be used as business logic (e.g. to compute a final total that must be exact)? AAccumulators are too slow. BIn transformations that can be re-executed (speculation, retries), accumulator updates can be applied more than once, so the value is only reliable inside actions and is best treated as approximate monitoring. CAccumulators cannot hold integers. DThey are write-only on the driver.

8In this multi-company dataset, order_id is unique within a company but can repeat across companies. You join orders to order_items on order_id alone. What can go wrong? ANothing; order_id is globally unique. BLine items can match the wrong company's order, inflating or misattributing rows — an intercompany double-count. Join on the full business key (company_id, order_id). CThe join becomes a broadcast join. DSpark raises an error.