BigQuery seven-year transaction retention using partition expiration

ADP By islever Editorial Last updated

Configure lifecycle management.

Short answer

A Partition the BigQuery transaction table by transaction date and set partition expiration to seven years so aged partitions are removed automatically.

Question

You work for a financial organization that stores transaction data in BigQuery. Your organization has a regulatory requirement to retain data for a minimum of seven years for auditing purposes. You need to ensure that the data is retained for seven years using an efficient and cost-optimized approach. What should you do?
  1. Create a partition by transaction date, and set the partition expiration policy to seven years. Correct Answer
  2. Set the table-level retention policy in BigQuery to seven years.
  3. Set the dataset-level retention policy in BigQuery to seven years.
  4. Export the BigQuery tables to Cloud Storage daily, and enforce a lifecycle management policy that has a seven-year retention rule.

Key Takeaways

This tests the difference between rolling partition-level expiration and table or dataset expiration that deletes an entire table, with the trap being to choose a broad expiration policy.

BigQuery retention for seven-year regulatory audits is best implemented with date partitioning and a seven-year partition expiration policy, not whole-table or dataset expiration. This page establishes why Option A preserves a rolling audit window efficiently.

Common mistake: Option B is the most plausible wrong answer because it sounds like a retention policy, but BigQuery table expiration deletes the whole table at a fixed time rather than expiring only transaction data older than seven years.

Expert Analysis

What the requirement actually needs The organization needs a rolling seven-year retention window over transaction rows that continue to arrive. In BigQuery, the native control that acts on a date boundary inside a table is partition expiration. If the transaction table is partitioned by transaction date, each daily partition can be kept until it is older than seven years; then BigQuery removes that partition and stops billing its storage. Recent partitions remain queryable for audit, and no daily export job is required. This is why Option A is the correct design.

Why B and C are the wrong granularity Option B sounds like a retention policy, but BigQuery table expiration deletes the whole table at a fixed time, not only rows or partitions older than seven years. For a continuously loaded transaction table, that would eventually delete current data too. Option C's dataset-level expiration has the same problem: it applies to tables, not transaction dates. Comments on this question make the distinction clearly: table retention 'means the entire table will be deleted,' and partition expiration is needed so 'only 7 year old data' expires. Those comments support ruling out Options B and C.

Why D is not cost-optimized Option D can create an archive, but exporting tables to Cloud Storage daily duplicates data, adds export and job costs, and complicates audit queries that must read from the external copies. Cloud Storage Object Lifecycle Management deletes objects by age; it does not replace BigQuery's partition lifecycle for the active table. It is only justified if you need an immutable archival copy, not as the primary seven-year retention mechanism.

The caveat the comments raise Partition expiration does not prevent a principal with delete permissions from running DELETE or dropping partitions. If the regulation demands immutability or legal hold, add IAM separation plus locked Cloud Storage retention. Among the choices, however, the BigQuery-native rolling retention is Option A.

Official Reference

These links open the official vendor documentation in a new tab.

Exam Strategy

When a retention question says 'minimum N years' and data arrives continuously, look for time-based partitioning plus partition expiration, not table or dataset expiration. Trace what gets deleted: partition expiration removes only old partitions, table expiration removes the whole table, and dataset expiration removes tables. Also check cost: exporting to Cloud Storage can duplicate data and add job and storage charges.

Frequently Asked Questions

Does BigQuery partition expiration meet a seven-year retention requirement?

Yes, when the table is partitioned by transaction date and partition expiration is set to seven years; BigQuery keeps the rolling seven years of partitions and drops older ones. Confirm that deletion exactly at seven years is acceptable for your audit policy.

Why not use BigQuery table expiration for seven-year retention?

Table expiration deletes the entire table at a fixed time, so it cannot keep a rolling seven years of transaction data in a continuously loaded table.

How do I stop users from deleting data before the seven-year period ends?

Partition expiration does not enforce immutability. Restrict delete and drop permissions with IAM, and if required, export immutable audit copies to a locked Cloud Storage bucket with a retention policy.

More ADP FAQ →

Community Votes

A
86%
B
14%

86% of anonymous learners picked answer A. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 5 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 5

  1. 8e50960 👍 2 Answered A
    When you set table-level retention (an expiration time) for a BigQuery table, it means the entire table will expire and be deleted at that specified time. This includes all data within the table, regardless of when that specific data was added. In order for only 7 year old data to expire we need to set up partitions and expire partitions
  2. n2183712847 👍 1 Answered B
    In the context of the question, both are efficient and cost-optimized. If the regulatory requirement applies to all transaction data which is logically grouped into a dataset, dataset-level retention (Option C) might be slightly more practical for management. However, if the requirement is specifically for this BigQuery table, table-level (Option B) is equally valid and directly applicable. In a multiple choice scenario, both are strong candidates. However, for the most direct answer and often simpler approach when the requirement is somewhat broad ("transaction data"), dataset level policy (Option C) might be slightly favoured for ease of management if the retention is consistent for all transaction data. But for a single table scenario implied by "BigQuery table", table level is also perfectly valid. Given the options, and aiming for the most directly applicable and efficient, table-level retention is a very strong and valid choice. Given the wording of the question is slightly less about dataset context and more about "the data" in BigQuery, table-level retention is a very direct and correct answer.
  3. pico 👍 1 Answered D
    Not A because does not prevent a user with the necessary permissions from running a DELETE Not B because there ir no table-level retention policy (different than table expiration and fail-safe period) Not C because there ir no dataset-level retention policy
  4. pico 👍 1
    and how, in option A, do prevent a user with the necessary permissions from running a DELETE?
  5. [email protected] 👍 1 Answered A
    Table retention means the entire table will be deleted

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Automating Legacy ETL Pipelines with Cloud Composer

S-grade

Cleaning Large Datasets in Google Cloud Using BigQuery SQL

S-grade

Forecasting Product Demand with BigQueryML Time Series Models

A-grade

Streaming Data Pipeline Design with Dataflow and Pub/Sub

A-grade

Practice All ADP Questions

Access 65 questions with complete answers and detailed explanations.

View Full ADP Practice Test →

← Back to ADP Study Guide