PDE Preparing and Using Data for Analysis 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 TABLE new_logs PARTITION BY DATE(timestamp) AS SELECT * FROM logs WHERE DATE(timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)

  Why it's wrong here

  This statement filters using DATE(timestamp) and DATE_SUB, which is valid, but it may not include all logs from the last 7 days if the timestamp has time components. For example, logs from 7 days ago at 23:00 might be excluded if CURRENT_DATE() is used without time. The correct filter should use TIMESTAMP_SUB to capture the full 7-day period. However, the partitioning is correct, but the filter is less precise.

• ✗

  CREATE TABLE new_logs PARTITION BY timestamp AS SELECT * FROM logs WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

  Why it's wrong here

  This statement attempts to partition by the raw TIMESTAMP column. BigQuery requires partitioning by a DATE or TIMESTAMP_TRUNC expression for time-unit partitioning. Partitioning directly by a TIMESTAMP column is not supported; you must use DATE(timestamp) or TIMESTAMP_TRUNC(timestamp, DAY). Therefore, this statement will fail with an error.

• ✗

  CREATE TABLE new_logs AS SELECT * FROM logs WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

  Why it's wrong here

  This statement creates a new table with the filtered data but does not partition it. The CREATE TABLE AS SELECT without a PARTITION BY clause results in an unpartitioned table. While it filters the last 7 days, it does not meet the partitioning requirement. Partitioning is essential for query performance and cost management on large datasets.

• ✓

  CREATE TABLE new_logs PARTITION BY DATE(timestamp) AS SELECT * FROM logs WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

  Why this is correct

  This statement creates a new table partitioned by day on the 'timestamp' column and populates it with data from the last 7 days. The PARTITION BY DATE(timestamp) clause ensures that the table is partitioned by day, which improves query performance and reduces cost. The WHERE clause filters the data appropriately. This is the correct syntax for creating a partitioned table from a query.