Prepare and test your skills
Prepare and test your skills
Free sample question, answered and explained below. The Associate Data Practitioner practice bank has 1,156 more like it. Try 10 free: instant score, no account.
A data engineering team manages a daily-partitioned BigQuery table named telemetry_logs. To optimize storage costs and adhere to data retention guidelines, the team needs to ensure that individual partitions are automatically deleted 60 days after their partition date, while keeping the table definition and more recent partitions intact.
Which SQL statement should the team execute to configure this automated retention rule?
table_expiration_days does:In BigQuery, table_expiration_days (or setting an absolute table expiration timestamp) sets a lifetime for the entire table entity. Once the specified duration passes after table creation, BigQuery deletes the whole table, including all of its partitions, schema definitions, and metadata.
telemetry_logs table once the expiration threshold is reached.The requirement asked to automatically destroy a temporary staging table 60 days after its creation to clean up short-lived ETL resources.
ALTER SCHEMA ... default_partition_expiration_days does:In BigQuery, ALTER SCHEMA (which is synonymous with ALTER DATASET) updates dataset-level metadata. The default_partition_expiration_days option establishes a default partition expiration lifetime that is inherited by newly created partitioned tables within that dataset.
telemetry_logs.default_partition_expiration_days at the dataset level only applies to newly created tables and does not automatically update the partition expiration of existing partitioned tables.ALTER TABLE.The requirement asked to configure a dataset policy so that all future partitioned tables created inside that dataset automatically inherit a 60-day partition expiration by default.
partition_expiration_days is:In Google BigQuery, partition_expiration_days is a table-level option that defines the lifetime of individual partitions within a partitioned table. When configured, BigQuery automatically tracks the date or timestamp associated with each partition and deletes partitions that exceed the defined age limit.
partition_expiration_days = 60 ensures that BigQuery automatically removes partition slices older than 60 days without requiring scheduled manual DELETE jobs or custom scripts.ALTER TABLE ... SET OPTIONS syntax.Modifying the table options via SQL DDL provides a native, serverless, and declarative approach to lifecycle management. It eliminates the computational costs of running daily deletion queries and ensures reliable enforcement of organizational retention policies.
This option represents an invalid SQL DDL statement. While some relational database engines support explicit partition dropping commands, Google BigQuery does not provide an ALTER TABLE ... DROP PARTITION WHERE DDL syntax for automated lifecycle management.
DROP PARTITION clause inside ALTER TABLE.DELETE FROM ...), it would act as a manual, one-time operation rather than a persistent, automated retention policy.DELETE statements consumes query processing slots and compute resources, unlike native partition expiration which is managed automatically by BigQuery at no additional query cost.partition_expiration_days).DELETE statements consumes query processing quotas and leaves audit log overhead.BigQuery supported procedural partition-dropping DDL syntax and the goal was a one-time manual administrative task rather than setting up an automated retention rule.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated