BigQuery Idle Dataset Storage FinOps Pruner

In Google Cloud BigQuery, enterprise analytics teams frequently generate temporary staging tables, sandbox copies, and materialized ETL snapshots. Over time, these datasets are abandoned by developers as project requirements evolve, but continue to reside in active logical storage.

Because BigQuery bills continuously for both active ($0.02/GB/month) and long-term ($0.01/GB/month) storage, unmanaged multi-terabyte datasets generate thousands of dollars of recurring monthly billing waste. Furthermore, redundant tables clutter data catalogs and confuse downstream analysts.

This blueprint provides an automated Cloud FinOps auditor that inspects INFORMATION_SCHEMA.TABLE_STORAGE across BigQuery datasets on a weekly schedule. It identifies tables exceeding 10 GB in size that have remained dormant past your retention threshold (default: 90 days), calculates monthly dollar costs, and dispatches structured optimization recommendations to Slack.

How it works

1. Weekly Scheduled Audit: The weekly_storage_audit trigger (io.kestra.plugin.core.trigger.Schedule) executes every Monday at 09:00 UTC.
2. Metadata Table Storage Query: The audit_idle_tables task (io.kestra.plugin.gcp.bigquery.Query) queries INFORMATION_SCHEMA.TABLE_STORAGE, filters tables older than inactive_days_threshold, and computes monthly storage fees.
3. Condition Branching: The evaluate_idle_storage flowable task (io.kestra.plugin.core.flow.If) branches based on whether any large dormant tables were identified.
4. Slack Alert Dispatch: When dormant tables are found, notify_slack_finops_team (io.kestra.plugin.slack.notifications.SlackIncomingWebhook) delivers an actionable triage card detailing dataset schemas, gigabytes, and estimated monthly dollar waste.
5. Compliant Logging: When all datasets are actively maintained, log_clean_storage_status records nominal status in execution logs.
6. Audit Manifest: The export_storage_manifest task records execution state for cloud budget dashboards.

What you get

• Automated discovery of dormant multi-gigabyte BigQuery tables across datasets.
• Exact calculation of storage gigabytes and monthly cost estimates.
• Non-intrusive read-only execution preventing accidental data deletion.
• Actionable recommendations including partition expiration and cold archival.

Who it is for

• Cloud FinOps practitioners optimizing Google Cloud Platform spending.
• Data Platform Engineers managing enterprise BigQuery data warehouses.
• Analytics Engineering Leads enforcing data lifecycle and cleanup policies.

Why orchestrate this with Kestra

Building custom BigQuery scheduled queries or Cloud Functions for cost governance requires maintaining separate GCP resources, IAM role bindings, and notification pipelines. Kestra unifies native BigQuery SQL execution, conditional logic, and enterprise Slack alerting into a declarative workflow that runs reliably.

Inputs

Name	Type	Default	Description
gcp_project_id	STRING	my-gcp-project	Target Google Cloud project ID.
gcp_region	STRING	region-us	BigQuery regional qualifier (e.g. region-us).
inactive_days_threshold	INT	90	Minimum age in days before evaluation.
storage_price_per_gb_month	FLOAT	0.02	Standard BigQuery active storage pricing per GB.
slack_channel	STRING	#data-finops	Slack channel destination for alerts.

Expected outputs

• {{ outputs.audit_idle_tables.rows }}: Array of table records containing schema, table name, gigabytes, and estimated cost.
• {{ outputs.audit_idle_tables.rows | length }}: Count of tables breaching the retention threshold.
• {{ outputs.export_storage_manifest.value }}: Structured JSON telemetry manifest recording execution timestamp and status.

Prerequisites

• A Google Cloud service account with permissions: bigquery.jobs.create, bigquery.tables.get.
• GCP service account JSON key stored in Kestra secrets.
• Slack Incoming Webhook configured for your FinOps notification channel.

Secrets

• GCP_SERVICE_ACCOUNT_KEY: Google Cloud service account JSON key string.
• SLACK_WEBHOOK_URL: Slack Incoming Webhook endpoint URL.

Quick start

1. Configure GCP_SERVICE_ACCOUNT_KEY and SLACK_WEBHOOK_URL in your Kestra namespace secrets.
2. Import this flow YAML into your Kestra instance.
3. Click Execute in the UI to perform an initial storage inspection.
4. Review execution outputs to inspect dormant table sizes and monthly cost estimates.

Common pitfalls and troubleshooting

• Region Qualification: BigQuery INFORMATION_SCHEMA.TABLE_STORAGE views are regional; ensure gcp_region matches your dataset location (e.g., region-us or region-eu).
• Physical vs Logical Storage: BigQuery allows choosing physical or logical storage billing; adjust storage_price_per_gb_month if using physical compressed storage.
• Long-Term Storage Pricing: Tables unmodified for 90 consecutive days automatically drop to long-term storage pricing ($0.01/GB); factor this discount into budget evaluations.

How to extend

• Add io.kestra.plugin.gcp.bigquery.Query to automatically set partition_expiration_days or expiration_timestamp on non-compliant staging tables.
• Export dormant datasets to compressed Parquet files in Google Cloud Storage Coldline buckets before dropping tables.
• Correlate dormant tables with INFORMATION_SCHEMA.JOBS_BY_PROJECT to verify that zero queries have accessed the table over the past 90 days.

Links

• Kestra BigQuery Plugin Documentation: https://kestra.io/plugins/plugin-gcp/io.kestra.plugin.gcp.bigquery.Query
• Kestra Slack Plugin Documentation: https://kestra.io/plugins/plugin-notifications
• BigQuery Table Storage Pricing Documentation: https://cloud.google.com/bigquery/pricing#storage