Advanced
Prepare and test your skills
Advanced
Prepare and test your skills
BigQuery Partition Expiration Hierarchy Rules
Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
A lead data engineer is designing a data lifecycle and cost-optimization architecture for an enterprise BigQuery data warehouse. The requirements are:
- All newly created partitioned event tables in the
telemetry_eventsdataset must automatically delete individual daily partitions once they are older than 60 days, while retaining the base table structure for ongoing streaming ingestion. - Any non-partitioned lookup tables created in the same dataset must be preserved indefinitely by default and automatically transition to BigQuery's lower-cost long-term storage tier after remaining unmutated for 90 days.
- High-priority analytics tables must be able to override the default partition retention to keep partitions for 180 days upon table creation.
Which configuration should the data engineer implement?
Explanations
Set default_table_expiration_ms to 60 days and default_partition_expiration_days to 60 on the dataset, and configure partition_expiration_days = 180 on high-priority partitioned tables upon creation.
Set default_table_expiration_ms to 60 days and default_partition_expiration_days to 60 on the dataset, and configure partition_expiration_days = 180 on high-priority partitioned tables upon creation.
What the Option Is
This configuration attempts to configure both a default table expiration (table TTL) and a default partition expiration (partition TTL) simultaneously at the dataset level.
Why It Doesn't Meet the Requirement
- Deletes non-partitioned tables: Setting
default_table_expiration_msto 60 days at the dataset level causes every newly created non-partitioned lookup table to be permanently deleted 60 days after creation. - Fails indefinite retention: Because non-partitioned tables are deleted after 60 days, they will never reach the 90 consecutive days without modification milestone necessary to transition to BigQuery's long-term storage tier.
- Unnecessary table TTL: The requirement specifies that only partition expiration is desired for event data while non-partitioned lookup tables should persist indefinitely.
Key Characteristics and Limitations
- Table TTL vs Partition TTL: Table expiration deletes the entire table and all underlying metadata, whereas partition expiration only purges individual qualifying partition slices.
- Dataset level interaction: When both default table expiration and default partition expiration are configured at the dataset level, partitioned tables only receive partition expiration, but standard non-partitioned tables still inherit the 60-day full-table deletion lifetime.
This Answer Would Be Correct If
This option would be appropriate if all tables across the entire dataset—both partitioned event tables and standard tables—were intended to be completely destroyed after 60 days.
Configure a scheduled Dataform workflow running daily SQL DML DELETE statements for partitions older than 60 days, and export inactive tables to Cloud Storage Coldline buckets after 90 days.
Configure a scheduled Dataform workflow running daily SQL DML DELETE statements for partitions older than 60 days, and export inactive tables to Cloud Storage Coldline buckets after 90 days.
What the Option Is
This approach uses an external transformation workflow (Dataform) to issue periodic SQL DELETE operations on partitioned tables and manually exports stale data to Cloud Storage Coldline.
Why It Doesn't Meet the Requirement
- Incurs unnecessary compute and scanning costs: Running recurring scheduled queries with mutating DML
DELETEstatements consumes BigQuery slots or on-demand query bytes, whereas BigQuery built-in partition expiration deletes expired partitions automatically in the background at zero analysis compute cost. - Destroys native long-term tier eligibility: BigQuery natively manages data tiering. Modifying or exporting data out of BigQuery to Cloud Storage removes tables from active querying capabilities, whereas BigQuery's native long-term storage keeps tables fully queryable at half the storage price.
- Complex pipeline maintenance: Introduces unnecessary orchestration overhead compared to simple declarative schema and table options.
Key Characteristics and Limitations
- Automated background deletion: Native partition expiration requires no scheduled queries or custom cron jobs.
- Transparent tiering: BigQuery long-term storage does not require moving files to Cloud Storage; BigQuery tables in long-term storage are queried transparently using standard SQL without retrieval fees or restoration steps.
This Answer Would Be Correct If
Data needed to be archived outside of BigQuery into raw object storage for cold compliance retention without requiring interactive SQL querying via BigQuery compute engines.
Set default_partition_expiration_days = 60 on the dataset using ALTER SCHEMA SET OPTIONS, leave default table expiration unset at the dataset level, and set partition_expiration_days = 180 on high-priority partitioned tables upon creation.
Set default_partition_expiration_days = 60 on the dataset using ALTER SCHEMA SET OPTIONS, leave default table expiration unset at the dataset level, and set partition_expiration_days = 180 on high-priority partitioned tables upon creation.
What the Solution Is
This configuration applies automatic partition expiration at the BigQuery dataset level via SQL DDL (ALTER SCHEMA SET OPTIONS) while allowing table-level overrides and relying on BigQuery's native, zero-maintenance long-term storage pricing model.
How It Meets the Requirements
- Automatic partition deletion after 60 days: Setting
default_partition_expiration_days = 60on the dataset ensures every newly created partitioned table inherits a 60-day Time-To-Live (TTL) for each individual partition without dropping the table itself or interrupting streaming writes. - Indefinite non-partitioned tables & long-term storage: Leaving the dataset's default table expiration (
default_table_expiration_ms) unset guarantees that standard, non-partitioned tables never expire. When any table or partition in BigQuery is left unedited for 90 consecutive days, BigQuery automatically transitions that data to long-term storage, reducing the storage price by approximately 50% with zero read performance degradation. - Granular table-level override: Setting
partition_expiration_days = 180in theOPTIONSclause during table creation explicitly overrides the dataset-level default for those specific tables.
Key Advantages and Capabilities
- Hierarchical precedence: BigQuery evaluates expiration settings in a defined hierarchy where table-level partition settings always supersede dataset-level defaults.
- Continuous ingestion: Partition expiration drops aged slices of data automatically in the background without dropping table metadata or schemas.
- Automated cost reduction: Inactive historical tables automatically transition from active to long-term storage pricing without operational overhead or manual ETL export pipelines.
- Declarative infrastructure: Expiration rules can be managed entirely through SQL DDL,
bqCLI, Terraform, or client libraries.
Why This Is Optimal
This approach leverages native BigQuery metadata lifecycle policies and automated storage tiering, requiring zero scheduled queries, external workflow triggers, or Cloud Storage export processes.
Set the dataset storage_billing_model to PHYSICAL and configure max_time_travel_hours = 48 to enforce partition drops, and create snapshots of historical tables to transition them to long-term storage.
Set the dataset storage_billing_model to PHYSICAL and configure max_time_travel_hours = 48 to enforce partition drops, and create snapshots of historical tables to transition them to long-term storage.
What the Option Is
This option alters the dataset's storage billing model to physical compressed bytes, reduces the time travel window to 48 hours (2 days), and relies on manual table snapshots for historical retention.
Why It Doesn't Meet the Requirement
- Does not purge data: The
storage_billing_modelandmax_time_travel_hourssettings govern how BigQuery bills physical compressed bytes and how long historical states are preserved for point-in-time queries; they do not purge or expire partition data after 60 days. - Misunderstands long-term storage: Long-term storage tiering in BigQuery is an automatic billing transition that occurs after a table or partition is untouched for 90 days. It does not require manual table snapshot creation.
- Operational overhead: Relying on snapshots introduces unnecessary snapshot management complexity rather than utilizing native BigQuery storage tiering.
Key Characteristics and Limitations
- Time travel window: BigQuery supports time travel windows between 48 hours (2 days) and 168 hours (7 days) for recovering modified/deleted data, but this is distinct from partition TTL.
- Physical billing: Physical storage charges for physical compressed bytes plus time travel and fail-safe bytes, but does not dictate partition lifecycles.
This Answer Would Be Correct If
The organization specifically wanted to optimize physical storage billing overhead from recent bulk deletions and required the minimum allowable fail-safe and time travel recovery window.
Try 10 interactive questions like this one, free
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
- 1,353 practice questions across all 5 exam domains
- Every answer explained: why it is right, and why the others are not
- Scout, an AI tutor that answers your follow-up questions
- Readiness tracking by domain, so you know when to book the exam
Related questions
Keep the momentum going with these hand-picked practice scenarios
AdvancedProfessional Data Engineer
Cloud Storage Bucket Lock and Object Lifecycle Management
Lifecycle management of data
AdvancedProfessional Data Engineer
Google Cloud Tiered Storage Lifecycle Policies
Lifecycle management of data
AdvancedProfessional Data Engineer
Cloud Monitoring Log-Based Anomaly Detection
Data validation
AdvancedProfessional Data Engineer
BigQuery Reservations Idle Slot Sharing
Minimizing costs per required business need for data
AdvancedProfessional Data Engineer
Cloud Logging Sink Writer Identity Permissions
Observability of data processes (e.g., Cloud Monitoring, Cloud Logging, BigQuery admin panel)
AdvancedProfessional Cloud DevOps Engineer
GKE HPA VPA Cluster Autoscaler Coexistence
Autoscaling (e.g., managed instance groups, Cloud Run, GKE)
Last updated Validated