Task 3.2: Planning for using a data warehouse.

• AStore the lines as a repeated STRUCT column inside the orders table

  Correct

  Nested and repeated fields keep each order's lines inside its row, which keeps the relationship without the cost of joining two large tables.

• BCluster both tables on order_id so that the join reads fewer blocks

  Incorrect

  Clustering prunes blocks for filters on the clustered column; the queries filter by date, so most of the join work remains.

• CCreate a materialized view that joins the orders with their lines

  Incorrect

  A materialized view adds a stored copy that must be refreshed; it works around the model instead of fixing it.

• DDeclare primary and foreign keys on order_id in both of the tables

  Incorrect

  Unenforced keys can help the optimizer in some joins, but the queries still read and join both large tables.

BigQuery performs best on denormalized data. When child rows belong to one parent, are written with it and are read with it, a repeated STRUCT column stores them together and removes the join.

• APartition the table by day on event_time

  Correct

  Three years of daily partitions is about 1,100 partitions, well within the 10,000-partition limit that hourly partitioning would pass.

• BPartition by integer range on device_id

  Incorrect

  The queries select time ranges first; partitioning on device_id loses time pruning entirely.

• CCluster the table on event_time and device_id

  Correct

  Clustering inside each daily partition lets queries that ask for a few hours and some devices read only the matching blocks.

• DShard into one table per month with a date suffix

  Incorrect

  Monthly tables each carry their own schema and metadata, and a query spanning them goes through a wildcard instead of pruning partitions.

• ESet a 365-day partition expiration on the table

  Incorrect

  An expiration deletes data older than a year, which breaks the three-year retention requirement.

A partitioned table can have at most 10,000 partitions, so hourly partitioning fits only about 13 months. Google's guidance is to move to a coarser granularity when a table may reach the limit and to cluster for the finer-grained filters, which gives queries back most of the pruning.

• AQuery the table with FOR SYSTEM_TIME AS OF the timestamp of the close

  Incorrect

  Time travel reaches back at most seven days, far short of two years.

• BCreate a table clone at each close for finance to query

  Incorrect

  A clone is storage-efficient too, but it is writable, so the saved state could be altered.

• CCreate a table snapshot at each close, expiring after two years

  Correct

  A snapshot is a read-only copy of the table at a point in time, billed only for bytes that differ from the base table, and it can carry an expiration.

• DExport the table to Parquet files in Cloud Storage at each close

  Incorrect

  Each export writes a full 30 TB copy, and reports would have to be rerun against external data.

Table snapshots preserve a table's contents at a moment for as long as needed, they are read-only, and BigQuery stores only the bytes that differ from the base table. Time travel covers only the last seven days, and clones are writable copies meant for experiments.

• AKeep ingestion-time partitioning and cluster the table on event_date

  Incorrect

  Clustering helps, but partition pruning still follows ingestion time, so a single-day query reads every partition where late events of that day landed.

• BSwitch to hourly ingestion-time partitions to make each one smaller

  Incorrect

  Finer ingestion partitions still do not line up with event_date, so the queries cannot prune on it.

• CUse integer-range partitioning on the number of days since 1970

  Incorrect

  An integer stand-in for the date works only if every query filters on the new integer column instead of event_date.

• DPartition the table by day on its event_date column instead

  Correct

  With time-unit column partitioning on event_date, each row lands in the partition of its event day, however late it arrives, and filters on event_date prune directly.

Partition on the column that queries filter on. Ingestion-time partitioning assigns rows by load time, which drifts from event time when data arrives late; time-unit column partitioning on a DATE, TIMESTAMP or DATETIME column keeps each row in its event's partition.

• AClustering of the table on customer_id

  Correct

  Clustering sorts the data in each partition by customer_id, so a filter on one customer reads only the blocks that hold it.

• BInteger-range partitioning on customer_id

  Incorrect

  A table has one partitioning column, and 40 million customers would far exceed the partition limit anyway.

• CHourly partitions instead of daily ones

  Incorrect

  Smaller time partitions do not help a filter on customer, since every partition still holds the rows of all customers.

• DA primary key declared on customer_id

  Incorrect

  BigQuery does not enforce primary keys, and declaring one does not prune storage blocks.

Partitioning and clustering work together: partitions prune by date, and clustering orders each partition by up to four columns so that filters on a high-cardinality column such as a customer ID read only the relevant blocks.

• AA custom daily query quota for each user in the project

  Incorrect

  A quota does not look at whether a query filters on the date, so a forgotten filter still scans the whole table while the quota allows it.

• BA maximum bytes billed value in each analyst's query settings

  Incorrect

  Maximum bytes billed is set per query by whoever runs it, so it does not enforce anything for everyone.

• CThe require partition filter option on the table

  Correct

  With this option, BigQuery rejects any query on the table that does not filter on the partitioning column.

• DAn authorized view that adds a filter for the last 30 days

  Incorrect

  A view limits what it exposes, but analysts who query the table itself can still scan it in full.

A partitioned table can require a partition filter: every query against it must include a predicate on the partitioning column that can be used for pruning, or BigQuery returns an error. It turns a cost guideline into a rule.

• ACreate a view over the wildcard so that analysts use a single name

  Incorrect

  A view hides the shards but keeps their overhead and per-shard schema changes.

• BCopy the shards into a new dataset with a shorter table prefix

  Incorrect

  Renaming the shards changes nothing about the design.

• CBuild one table partitioned by event date from the shards' data

  Correct

  Google recommends partitioning over date sharding: one partitioned table has one schema and less metadata to process.

• DChange the queries to filter on the partitioning column

  Correct

  Pruning in a partitioned table comes from filters on the partitioning column, which replaces the _TABLE_SUFFIX filter.

• ECluster each shard on the date column before querying it

  Incorrect

  Each shard already holds one date, so clustering it on the date does nothing useful.

Date-sharded tables were common before partitioning. BigQuery must keep a schema and metadata for each shard and check permissions on each, so Google recommends a single time-partitioned table instead; queries then prune with filters on the partitioning column.