GCP Professional Data Engineer Practice Question

Your media company ingests roughly 5 TB of clickstream events into BigQuery each day. Analysts frequently aggregate metrics filtered by event_date and country and occasionally need to drill into a nested JSON payload per event. The user profile dimension changes less than 1 % daily, and the team wants to avoid the latency of runtime joins while keeping query costs low. Which BigQuery table design best meets these requirements?

Create a single BigQuery table partitioned on event_date, clustered on country, and store the user profile and event payload as nested STRUCT and ARRAY fields in each row.
Store events in individual daily sharded tables that contain only primitive columns; analysts combine shards using wildcard queries.
Implement a star schema with a partitioned fact_events table and separate dimension tables for users and countries, relying on joins at query time.
Build a snowflake schema with fully normalized dimensions and generate daily flattened materialized views to satisfy analyst queries.

Report Issue

Answer Description

Keeping analytics fast and inexpensive in BigQuery generally favors denormalized schemas that eliminate joins and take advantage of native column pruning. Storing each clickstream record together with the small, slowly changing user profile in a nested STRUCT allows analysts to access user attributes without an explicit join. Partitioning the table on the monotonically increasing event_date limits the amount of data scanned for time-bounded queries, and clustering on country further prunes blocks when analysts filter by that field. This combination maximizes performance and limits costs. The other designs either require joins (incurring additional latency and cost) or forgo partitioning/clustering efficiencies, so they do not deliver the best balance of speed and cost for the described workload.

Ask Bash

Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.