Redshift

A 2-billion-row events table on a Redshift cluster is distributed DISTKEY(country_code). SVV_TABLE_INFO shows a skew_rows ratio that's wildly off — one slice holds roughly 60% of the rows because most traffic is from one country. Queries are slow and CloudWatch shows one node pegged while the others idle. The interviewer wants you to explain Redshift's distribution styles, why this DISTKEY choice caused the skew, and how you'd redistribute the table.

fct_events holds 5 years of data. A dashboard query with WHERE event_ts >= current_date - 1 should touch one day, but SVL_QUERY_SUMMARY shows it reading nearly every block in the table. The table's SORTKEY is (customer_id, event_ts), and SVV_TABLE_INFO reports it's ~40% unsorted after months of nightly loads with no maintenance. The interviewer wants you to explain sort keys and zone maps, why this query can't prune, and how compound vs interleaved sort keys change the answer.

A fct_subscriptions table gets heavy daily UPDATE/DELETE churn (status changes, cancellations). Over six months it's grown to 3x the disk footprint its live row count justifies, queries that used to take 2s now take 20s, and the planner keeps picking bad join orders. SVV_TABLE_INFO shows high unsorted and a large tbl_rows vs the real count. No one has run maintenance. The interviewer wants you to explain why DELETE/UPDATE don't reclaim space in Redshift, what VACUUM and ANALYZE actually do, and a maintenance plan.

A join between two large tables — fct_orders (3B rows) and fct_shipments (2B rows) — takes ~15 minutes and saturates the network. EXPLAIN shows a `DS_BCAST_INNER` step, and STL_ALERT_EVENT_LOG is full of "very selective query filter / large broadcast" warnings. fct_orders is DISTKEY(order_id) and fct_shipments is DISTKEY(shipment_id) — but they're joined on order_id. The interviewer wants you to explain how Redshift executes joins in an MPP cluster, decode the EXPLAIN labels, and redesign distribution so the join co-locates.

One Redshift cluster runs everything: heavy nightly ETL (big transforms, COPYs, VACUUMs) and interactive BI dashboards for 300 analysts. When the ETL overruns into the 9am peak, dashboards queue for minutes, some queries go "disk-based" with memory errors, and execs complain. There's a single default WLM queue. STV_WLM_QUERY_STATE shows everything fighting for the same slots. The interviewer wants you to redesign workload management so ETL and BI stop starving each other, and to bring in the right scaling/architecture levers.