Exam Professional Data Engineer topic 1 question 169 discussion

A. Partition by transaction time; cluster by state first, then city, then store ID.

A Partitioning is obvious Clustering is already mentioned in the question past 30 days and to look at purchasing trends by state, city, and individual store

go to A.

over the past 30 days -> partitioning by state, city, and individual store -> cluster order

Partition by ingest time Partition by specified data column (Id, State and City)

Partition by transaction time would lead to too many partitions - if it was a date, it would have made sense.

It should be C. not A

I think it should be C. The fact that we partition the table with the time of the transaction will result many transactions in each day, so it will affect negatively the query performance. i.e : by the end of the day I will have many partitions if I use the transaction time. A would be correct if the partition was by date and not by time. Response: C.

Partitioning for time is obvious to improve performance and costs of querying only the last 30 days of the table. So, the answer is A or B. https://cloud.google.com/bigquery/docs/querying-clustered-tables "... To get the benefits of clustering, include all of the clustered columns or a subset of the columns in left-to-right sort order, starting with the first column." This means that it is a better choice to sort the table rows by region-province-city (region-state-city in the US case). So, the answer is A.

Should be B The clustering should be according to the filtering needs

A is the answer. https://cloud.google.com/bigquery/docs/partitioned-tables This page provides an overview of partitioned tables in BigQuery. A partitioned table is a special table that is divided into segments, called partitions, that make it easier to manage and query your data. By dividing a large table into smaller partitions, you can improve query performance, and you can control costs by reducing the number of bytes read by a query. You can partition BigQuery tables by: - Time-unit column: Tables are partitioned based on a TIMESTAMP, DATE, or DATETIME column in the table. https://cloud.google.com/bigquery/docs/clustered-tables Clustered tables in BigQuery are tables that have a user-defined column sort order using clustered columns. Clustered tables can improve query performance and reduce query costs.

https://cloud.google.com/bigquery/docs/querying-clustered-tables

A The question mention that the query is 30 days recently