Manage data retention

Optimization of storage lifecycle and data deletion keeps each database asset only as long as policy requires, then moves it to cheaper storage or securely deletes it. The lifecycle is controlled by retention metadata, carried out by automated purge and archive workflows, and extended by Cloud Storage (opens in a new tab) lifecycle rules that lower long-term cost and shrink the data footprint.

1.1Retention Governance Tags

Retention governance tags are metadata fields that state how long a table must be kept and what happens when that time ends. Knowledge Catalog stores these fields in the CDMC controls tag template as retention_period, the time in days to retain the table, and expiration_action, whether to archive or purge the table. Tag Engine applies these tags to data in the secured data warehouse based on corporate rules in the governance data warehouse, and Report Engine checks whether tags are applied and whether tables meet the retention policy.

1.2Default Retention Periods and Expiration Actions

Default retention periods and expiration actions set the starting governance policy by data category. Sensitive data with the highest risk is purged quickly, while less sensitive data is archived for continued low-cost access:

These tag values drive the automated workflow, so a table that reaches its retention period is handled according to its assigned purge or archive action.

1.3Purge Workflow for Secure Deletion

The purge workflow is the secure deletion path that removes the source table while keeping a short recovery window. When the workflow reads an expiration action of purge, it first creates a BigQuery table snapshot with an expiration time to support soft deletion and recovery, then it applies BigQuery table expiration or partition expiration policies to the source table. The snapshot lets the table be retrieved during the soft-deletion period, and expiration of the source table and then the snapshot completes the deletion. Record Manager, the open-source asset for BigQuery, follows the same pattern by setting an expiration date on the table and creating a snapshot with an expiration time defined in its configuration, 30 days by default.

1.4Archive Workflow for Cost-Effective Storage

The archive workflow is the preservation path that moves expired table data from BigQuery to Cloud Storage and then removes the source. When the workflow reads an expiration action of archive, data flows from the source BigQuery table to Cloud Storage and then to a governed external table in this order:

1. Export to Cloud Storage: The workflow exports the table data to Cloud Storage in Parquet format.
2. Register BigLake table: The workflow registers a BigLake table over the exported files to support access control and external metadata tagging.
3. Carry forward governance: The workflow copies metadata and policy tags from the source table to the BigLake table, or re-applies the tags using Tag Engine.
4. Remove the source: The workflow drops or expires the source BigQuery table so primary storage is freed.

Because the BigLake table remains taggable in Knowledge Catalog, archived data stays discoverable and governed after it leaves BigQuery.

1.5Cloud Storage Lifecycle Transition

Cloud Storage object lifecycle management is the rule system that automatically transitions archived files to lower-cost storage classes as they age. After data is archived to Cloud Storage, lifecycle rules move older Parquet objects from higher-cost to lower-cost classes without manual copying. This transition separates hot database storage in BigQuery from cold archive storage in Cloud Storage, so long-term retention for compliance and auditing does not pay primary-storage prices.

1.6Implementation Options for Enforcement

Implementation options for enforcement are the two supported ways to run purge and archive logic with serverless orchestration. Both options use two BigQuery data warehouses, one for confidential data and one for the retention policy, plus Knowledge Catalog for tags and Pub/Sub for publishing findings:

In the first option, Cloud Scheduler starts the workflow that enacts expiration or archival actions. In the second option, Record Manager automates purging and archiving directly, then drops the source table after the external table is created.

1.7Retention Compliance Findings

Retention compliance findings are the signals that show when lifecycle policy is missing or misaligned. Report Engine publishes these findings to Pub/Sub for review:

• The expiration action for a sensitive asset is not aligned with the policy for the asset's location.
• The retention period for a sensitive asset is not aligned with the policy for the asset's location.
• Sensitive data exists without retention period or expiration action tags.

Automated retention policies in Backup and DR (opens in a new tab) control how long database backups and transaction logs are kept to meet Recovery Point Objectives (RPOs) and compliance needs. A backup plan template combines snapshot policies, log protection settings, replication policies, and expiration jobs so backups are created, retained, and deleted without manual action. Database backups and log backups have separate frequencies and separate retention periods, and logs can be extended to remote sites for point-in-time recovery.

2.1Defining Backup Frequency to Meet RPOs

Backup frequency in Backup and DR is the number of jobs run per unit time, while period is the time between scheduled runs. A schedule that runs every 4 hours has a period of 4 hours and a frequency of 6 times per day, and the next run starts after the period elapses from the prior start, not from completion. The minimum recommended frequency for a Snapshot policy is 1 hour for local RPO, and a StreamSnap policy can point to any Snapshot policy with a frequency of 1 hour or longer for remote RPO. Database log frequency is set in minutes through the RPO setting, with a smallest value of 15 minutes, and log frequency must not exceed the database backup interval. For example, a database captured every 24 hours must have its logs captured more often than every 24 hours, such as every hour.

2.2Separating Database and Log Retention

Log protection in Backup and DR is enabled through the Enable Database Log Backup setting in the advanced settings of a backup plan snapshot policy. When Enable Database Log Backup is set to Yes, related options are enabled and log backups run on their own log backup jobs without regard to day boundaries, windows, or database frequency. Log Backup Retention Period is a mandatory setting that is defined separately from snapshot retention, which lets logs be kept in the snapshot pool long enough to cover database copies for point-in-time recovery.

Retention automation also manages production logs and staging storage for compliance and efficiency:

• Truncate or purge log after backup runs log purge at the end of each log backup when set to Yes, while the default is Do Not Truncate.
• Log Backup Retention Period defines how long log backups stay on the Backup and DR staging disk and can differ from snapshot retention.
• Log staging disk growth size defines automatic growth of the log staging disk from 5 to 100 percent.
• Estimated change rate defines daily data change from 0 to 100 percent so the appliance can size the staging disk for logs.
• Compress database log backup instructs the source database to compress logs using the database API before capture.

If Enable Database Log Backup is set to No but Truncate or purge log after backup is set to Yes, log purging runs at the end of each database backup and purges all logs.

2.3Extending Retention to Remote and OnVault Pools

Extended retention in Backup and DR replicates logs from the local snapshot pool to a remote location so point-in-time recovery is possible outside the primary site. StreamSnap replication moves log backups directly from the local snapshot pool to the snapshot pool on the remote backup/recovery appliance. OnVault replication moves logs continuously to one or more OnVault storage pools, such as object storage or Cloud Storage buckets, for long-term and off-site retention.

The two remote retention paths share prerequisites but use different policies and destinations:

Replicate Logs is enabled by default once prerequisites are met, and replicated logs can be used for any database backup within the retention range of those logs. Send Logs to OnVault Pool sends logs to every OnVault pool in the application profile, with retention similar to snapshot log retention, and replication follows log frequency, such as every 15 minutes. Daily, Weekly, Monthly, and Yearly OnVault policies apply only to database backups and do not apply to log backups.

2.4Keeping Automated Jobs Within RPO and Compliance

Automated retention in Backup and DR depends on the job system that creates, replicates, and expires backups on schedule. All activities run as jobs, and the policy scheduler places policies into a queue at the scheduled start time, but a job runs only when a job slot is available through pacing for each policy type. A backup/recovery appliance runs six snapshot jobs concurrently by default and queues additional jobs, and jobs with the same priority scheduled at the same time are selected randomly for fairness. Expiration jobs are fast, while snapshot jobs vary with application size and changed data, and recovery jobs such as mount or restore have the highest priority and take resources from ongoing backup jobs.

Failed jobs are retried automatically, with a 4-minute wait before the first retry and a Failed state after 3 failed attempts, and retries appear in Monitor with a, then b, then c appended to the job name. RPO compliance also depends on sizing and contention outside the schedule:

• Network congestion can slow data flow if provisioned bandwidth for RPO sizing is not consistently available.
• Other running jobs such as frequent virtual clones or restores create I/O load that delays backups.
• Higher than normal change rate from re-indexing, ETL, or workload moves can exceed planned ingest capacity.
• Loss of changed block tracking after events like a server crash forces slower scanning to rebuild changes.
• Distribution of change is uneven across days and within a day, so sizing only for the average rate causes violations for larger workloads.

Point-in-Time Recovery (PITR) windows define how far back in time a database can be restored to any specific second. The PITR window is determined by how long transaction logs are retained alongside database backups. A longer window gives more granular recovery and better coverage for recovery point objectives, but it increases storage costs and can affect performance because more log history must be stored and managed.

3.1Transaction Log Retention in Cloud SQL

Transaction log retention in Cloud SQL (opens in a new tab) is controlled by the transactionLogRetentionDays backup configuration setting, which sets how long PITR logs are kept in Cloud Storage. Limits and defaults for this setting depend on the instance edition, so the choice of edition directly sets the maximum recoverable window.

When retention is increased, the full new window is not available immediately. The window grows by one day per day until it reaches the new value, so an increase from 7 to 14 days takes 14 days to provide 14 days of history. An upgrade to Enterprise Plus edition also raises the default automated backup count from 8 to 15, but PITR cannot restore to a time before a major version upgrade even if the retention period covers that time.

3.2Where PITR Logs Are Stored and How Long They Last

PITR log storage in Cloud SQL uses Cloud Storage in the same region as the instance for long-term retention, plus a smaller duplicate copy on disk to allow replication of logs to Cloud Storage. New instances with PITR enabled store logs in Cloud Storage by default, and Cloud SQL automatically sets the expire_logs_days and binlog_expire_logs_seconds flags to the equivalent of one day of logs on disk. The effective on-disk retention is the minimum of transactionLogRetentionDays and the expire_logs_days or binlog_expire_logs_seconds flag value.

Flag values cannot be changed while log storage is being switched to Cloud Storage, and neither flag should be set to 0. For customer-managed encryption key (CMEK)-enabled instances, transaction logs are encrypted with the latest version of the key, and that latest key version is required for all retained days to perform a restore. If on-disk log size causes performance problems, deactivating and re-enabling PITR forces new logs to Cloud Storage, but this deletes all existing logs, prevents PITR to any time before re-enabling, and requires a database restart.

3.3Frequency and Retention in Backup and DR Log Protection

Log protection in Backup and DR is enabled through the Enable Database Log Backup setting in a backup plan snapshot policy for Oracle or Microsoft SQL Server databases. When set to Yes, database logs are captured by separate log backup jobs on their own schedule, independent of day boundaries, windows, or the associated database backup schedule. The log capture frequency defines the RPO, is set in minutes with a minimum of 15 minutes, and must not exceed the database backup interval, such as hourly logs for a database captured every 24 hours.

Separate log retention lets logs be combined with database copies in the snapshot pool to roll forward to a point in time. The appliance management console automatically evaluates typical log sizes and retention periods and adds space as needed, and the following settings control storage use:

• Log Backup Retention Period: mandatory separate retention for logs under staging disk, different from snapshot retention, that determines how long logs can be used with stored database copies.
• Truncate or purge log after backup: when set to Yes, runs log purge on production at the end of each log backup; if log backup is No but this is Yes, purging runs at the end of each database backup.
• Log Staging Disk Growth Size: percent from 5 to 100 used to automatically grow the staging disk that holds logs when more space is needed.
• Estimated Change Rate: daily percent of data change from 0 to 100 that helps the appliance calculate the staging disk size needed to hold logs.
• Compress Database Log Backup: instructs the source database to compress logs during backup using the database API before capture by the appliance.

3.4Remote PITR and Balancing Cost Against Performance

Remote PITR in Backup and DR moves logs from the local snapshot pool to a remote location so recovery can occur at another site within the retention range. Replicate Logs uses StreamSnap technology to copy logs directly from the local snapshot pool to the snapshot pool on the remote appliance, and it requires a StreamSnap replication policy in the template, a resource profile that specifies the remote appliance, and at least one successful replication of the database first. Send Logs to OnVault Pool continuously replicates logs to one or more OnVault storage pools, such as every 15 minutes when logs are backed up every 15 minutes, and it requires an OnVault policy and at least one database already sent to the pool.

Longer retention and more frequent log capture improve granularity but increase staging disk, Cloud Storage, and network use. To avoid unexpected storage issues when logs are on disk, enable automatic storage increases and avoid purging large amounts of history at once by reducing transactionLogRetentionDays, expire_logs_days, or binlog_expire_logs_seconds slowly, for example by one day each day over several days. Job scheduling also affects RPO compliance because snapshot jobs vary with data size and change rate, concurrent jobs are limited by job slots, and network congestion, competing mount or restore jobs, high change rates, and loss of changed-block tracking can delay log and backup jobs.

Key Facts

• Purge in BigQuery creates a table snapshot with an expiration time for recovery, then expires the source table, while archive exports the table to Cloud Storage in Parquet format as a BigLake table and then drops the source table.
• Knowledge Catalog stores retention governance as retention_period in days and expiration_action of archive or purge, with defaults such as 60 days purge for Sensitive Personal Identifiable Information.
• Backup and DR keeps Log Backup Retention Period separate and mandatory from snapshot retention, with log frequency in minutes that must be more frequent than the database backup interval and a minimum of 15 minutes.
• Cloud SQL Enterprise edition allows 1 to 7 days of transaction log retention by default 7 days, while Enterprise Plus edition allows 1 to 35 days by default 14 days.
• Effective on-disk PITR log retention in Cloud SQL is the minimum of transactionLogRetentionDays and the expire_logs_days or binlog_expire_logs_seconds flag value.