A data engineer wants to create a BigQuery table that is partitioned by day and clustered by user_id and product_id. Which SQL statement should they use?
Trap 1: CREATE TABLE mydataset.table (user_id INT64, product_id INT64,…
Incorrect because it unnecessarily wraps event_date in DATE(), which is only needed for TIMESTAMP columns.
Trap 2: CREATE TABLE mydataset.table (user_id INT64, product_id INT64,…
Incorrect because it is a duplicate of option B but both cannot be correct in a single-select question.
Trap 3: CREATE TABLE mydataset.table (user_id INT64, product_id INT64,…
Incorrect because CLUSTER BY must come after PARTITION BY.
- A
CREATE TABLE mydataset.table (user_id INT64, product_id INT64, event_date DATE) PARTITION BY DATE(event_date) CLUSTER BY user_id, product_id;
Why wrong: Incorrect because it unnecessarily wraps event_date in DATE(), which is only needed for TIMESTAMP columns.
- B
CREATE TABLE mydataset.table (user_id INT64, product_id INT64, event_date DATE) PARTITION BY event_date CLUSTER BY user_id, product_id;
Correct. The syntax PARTITION BY event_date followed by CLUSTER BY user_id, product_id is the proper order.
- C
CREATE TABLE mydataset.table (user_id INT64, product_id INT64, event_date DATE) PARTITION BY event_date CLUSTER BY user_id, product_id;
Why wrong: Incorrect because it is a duplicate of option B but both cannot be correct in a single-select question.
- D
CREATE TABLE mydataset.table (user_id INT64, product_id INT64, event_date DATE) CLUSTER BY user_id, product_id PARTITION BY event_date;
Why wrong: Incorrect because CLUSTER BY must come after PARTITION BY.