PDE Designing Data Processing Systems Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  Create a materialized view that pre-aggregates data by user_id and date

  Why it's wrong here

  A materialised view pre-aggregates results but does not reduce the partitions scanned when the underlying query still reads raw event logs across the 30-day window. Materialised views suit repeated identical aggregations. The stem's problem is partition pruning, which the view does not address.

• ✗

  Remove partitioning and rely solely on clustering

  Why it's wrong here

  Dropping partitioning removes the ingestion-time pruning that limits scanned data to the last 30 days, forcing full-table scans filtered only by clustering. Clustering alone suits tables queried without a reliable time predicate. Here the date filter is the primary scan-reduction mechanism, so partitioning must stay.

• ✓

  Change the partition column to a DATE column based on event_timestamp and keep clustering on user_id

  Why this is correct

  Ingestion-time partitioning groups rows by load time, so a 30-day event query scans every partition loaded in that period regardless of event dates. Partitioning on a DATE derived from event_timestamp enables partition pruning to only the relevant event dates, while clustering on user_id still accelerates per-user filtering.

• ✗

  Add clustering on a second column like event_type

  Why it's wrong here

  Adding event_type as a second clustering column refines block pruning within each partition but does nothing to limit how many ingestion-time partitions the 30-day filter scans. Secondary clustering suits queries filtering on that column. The stem's excess partition scans stem from the date predicate, not event_type.

Go deeper

Related to this question