BigQuery Table Partition Governance Audit | Kestra Blueprint

id: bigquery-table-partition-expiry-audit
namespace: company.team
description: |
  Automated data warehouse governance and FinOps audit for Google BigQuery. Queries INFORMATION_SCHEMA
  to identify large unpartitioned tables, missing partition expiry dates, and tables lacking partition
  filter enforcement, and alerts the data engineering team on Slack.

concurrency:
  limit: 1

inputs:
  - id: dataset_id
    type: STRING
    displayName: BigQuery Dataset ID
    description: Dataset name to audit for partition and expiration compliance.
    defaults: "analytics_prod"

  - id: max_allowed_unpartitioned_tables
    type: INT
    displayName: Maximum Allowed Violations
    description: Violation tolerance threshold before triggering an alert.
    defaults: 0

  - id: min_table_size_mb
    type: INT
    displayName: Minimum Table Size to Audit (MB)
    description: Only flag unpartitioned tables larger than this size threshold.
    defaults: 500

  - id: slack_channel
    type: STRING
    displayName: Alert Channel
    description: Slack channel destination for governance compliance reports.
    defaults: "#data-governance"

triggers:
  - id: weekly_governance_schedule
    type: io.kestra.plugin.core.trigger.Schedule
    description: Weekly automated storage and cost audit executed Mondays at 08:00 UTC.
    cron: "0 8 * * 1"

tasks:
  - id: audit_partition_metadata
    type: io.kestra.plugin.gcp.bigquery.Query
    description: Query BigQuery INFORMATION_SCHEMA table storage and metadata to
      detect cost risks.
    projectId: "{{ secret('GCP_PROJECT_ID') }}"
    serviceAccount: "{{ secret('GCP_SERVICE_ACCOUNT_JSON') }}"
    fetchType: FETCH
    sql: |
      WITH table_storage_stats AS (
        SELECT
          table_schema,
          table_name,
          ROUND(total_logical_bytes / (1024 * 1024), 2) AS size_mb,
          ROUND(total_logical_bytes / (1024 * 1024 * 1024), 2) AS size_gb,
          total_rows
        FROM `{{ secret('GCP_PROJECT_ID') }}.{{ inputs.dataset_id }}.INFORMATION_SCHEMA.TABLE_STORAGE`
        WHERE total_logical_bytes >= ({{ inputs.min_table_size_mb }} * 1024 * 1024)
      ),
      table_partition_metadata AS (
        SELECT
          table_schema,
          table_name,
          is_partitioned,
          require_partition_filter
        FROM `{{ secret('GCP_PROJECT_ID') }}.{{ inputs.dataset_id }}.INFORMATION_SCHEMA.TABLES`
      )
      SELECT
        s.table_schema,
        s.table_name,
        s.size_mb,
        s.size_gb,
        s.total_rows,
        COALESCE(m.is_partitioned, 'NO') AS is_partitioned,
        COALESCE(m.require_partition_filter, FALSE) AS require_partition_filter,
        CASE
          WHEN COALESCE(m.is_partitioned, 'NO') = 'NO' THEN 'UNPARTITIONED_LARGE_TABLE'
          WHEN COALESCE(m.require_partition_filter, FALSE) = FALSE THEN 'MISSING_PARTITION_FILTER'
          ELSE 'COMPLIANT'
        END AS violation_type
      FROM table_storage_stats s
      LEFT JOIN table_partition_metadata m
        ON s.table_schema = m.table_schema AND s.table_name = m.table_name
      WHERE COALESCE(m.is_partitioned, 'NO') = 'NO'
         OR COALESCE(m.require_partition_filter, FALSE) = FALSE
      ORDER BY s.size_mb DESC;

  - id: evaluate_governance_violations
    type: io.kestra.plugin.core.flow.If
    description: Check if the number of non-compliant tables exceeds the allowed threshold.
    condition: "{{ outputs.audit_partition_metadata.rows | length >
      inputs.max_allowed_unpartitioned_tables }}"
    then:
      - id: notify_governance_slack
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        description: Dispatch high-priority Slack card alerting the data platform team
          of compliance risks.
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        messageText: |
          *BigQuery Table Partition Governance Violation* ⚠️
          • *Dataset*: `{{ inputs.dataset_id }}`
          • *Non-Compliant Tables*: `{{ outputs.audit_partition_metadata.rows | length }}`
          • *Largest Offender*: `{{ outputs.audit_partition_metadata.rows[0].table_name }}` (`{{ outputs.audit_partition_metadata.rows[0].size_gb }} GB`)
          • *Primary Violation*: `{{ outputs.audit_partition_metadata.rows[0].violation_type }}`
          • *Impact*: Unpartitioned tables or tables without mandatory partition filters risk full-table query scans and runaway cloud compute costs.
          • *Remediation*: Enforce `require_partition_filter = true` and migrate large tables to daily/hourly date partitioning.

    else:
      - id: log_compliant_status
        type: io.kestra.plugin.core.log.Log
        description: Confirm that all audited tables satisfy partition governance policies.
        message: "BigQuery dataset {{ inputs.dataset_id }} is 100% compliant with table
          partition and cost control guidelines."

  - id: export_audit_manifest
    type: io.kestra.plugin.core.debug.Return
    description: Record compliance audit manifest for execution tracking and lineage
      records.
    format: |
      {
        "pipeline": "bigquery-table-partition-expiry-audit",
        "dataset_audited": "{{ inputs.dataset_id }}",
        "violations_detected": {{ outputs.audit_partition_metadata.rows | length }},
        "compliance_status": "{{ outputs.audit_partition_metadata.rows | length <= inputs.max_allowed_unpartitioned_tables ? 'PASSED' : 'ACTION_REQUIRED' }}",
        "audited_at": "{{ execution.startDate }}"
      }

In Google BigQuery, analysis costs are directly tied to the number of bytes scanned by queries. Unpartitioned tables or tables that do not require partition filters represent a major financial vulnerability: an inexperienced user or runaway dashboard query can trigger full-table scans across multi-terabyte datasets, generating surprise cloud bills within minutes.

This blueprint establishes an automated Data Warehouse governance guardrail for Google BigQuery. Running on a weekly schedule or on-demand, it queries INFORMATION_SCHEMA.TABLE_STORAGE and INFORMATION_SCHEMA.TABLES across your specified dataset, flags tables larger than your threshold that lack date partitioning or mandatory partition filters, and delivers a prioritized remediation digest to your data team on Slack.

How it works

Scheduled FinOps Audit: The weekly_governance_schedule trigger (io.kestra.plugin.core.trigger.Schedule) executes every Monday at 08:00 UTC, or runs on-demand via the Kestra UI.
Storage and Metadata Audit: The audit_partition_metadata task (io.kestra.plugin.gcp.bigquery.Query) executes an analytical query against BigQuery INFORMATION_SCHEMA. It joins physical storage bytes with table partition flags to classify violations (UNPARTITIONED_LARGE_TABLE, MISSING_PARTITION_FILTER).
Violation Gate: The evaluate_governance_violations flowable task (io.kestra.plugin.core.flow.If) evaluates whether the total count of offending tables exceeds max_allowed_unpartitioned_tables.
Slack Governance Digest: If non-compliant tables are discovered, notify_governance_slack (io.kestra.plugin.slack.notifications.SlackIncomingWebhook) broadcasts an alert detailing the dataset, violation count, and largest offender with remediation steps.
Audit Trail: The export_audit_manifest task records execution audit details and compliance status for data platform tracking.

What you get

Automated discovery of expensive unpartitioned tables before they cause costly query scans.
Enforcement of the require_partition_filter best practice to prevent accidental full-table scans.
Direct Slack notifications detailing the largest offenders by gigabytes.
Zero human overhead for continuous BigQuery storage and schema governance.

Who it's for

Data Platform Engineers and BigQuery Administrators governing multi-tenant projects.
FinOps teams auditing cloud data warehouse compute and storage bills.
Analytics Engineering leads enforcing table design and query optimization contracts.

Why orchestrate this with Kestra

Manual table audits are rarely performed consistently, and native GCP alerts do not easily cross-reference table storage size with table partition schema flags. Kestra automates this end-to-end: it securely handles Google Cloud service account authentication, schedules regular audits, branches conditionally based on violation thresholds, and notifies the right team in Slack.

Inputs

Name	Type	Default	Description
`dataset_id`	STRING	`analytics_prod`	BigQuery dataset name to audit for partition and expiration compliance.
`max_allowed_unpartitioned_tables`	INT	`0`	Violation tolerance threshold before triggering a Slack alert.
`min_table_size_mb`	INT	`500`	Only flag unpartitioned tables larger than this size threshold (MB).
`slack_channel`	STRING	`#data-governance`	Slack channel destination for governance compliance reports.

Expected outputs

{{ outputs.audit_partition_metadata.rows }}: Array of non-compliant table records containing table_schema, table_name, size_mb, size_gb, total_rows, is_partitioned, and violation_type.
{{ outputs.audit_partition_metadata.size }}: Total number of non-compliant tables discovered during the scan.
{{ outputs.evaluate_governance_violations }}: Result of conditional evaluation determining whether Slack notification was dispatched.
{{ outputs.export_audit_manifest.value }}: Structured JSON audit manifest containing execution timestamp, dataset ID, and compliance status (PASSED or ACTION_REQUIRED).

Prerequisites

A Google Cloud Platform (GCP) project with BigQuery enabled.
A GCP Service Account JSON with roles/bigquery.metadataViewer and roles/bigquery.jobUser permissions.
A Slack Incoming Webhook URL targeting your data governance channel.

Secrets

GCP_PROJECT_ID: Your Google Cloud Project ID (used to query dataset metadata).
GCP_SERVICE_ACCOUNT_JSON: Full JSON key file content of the GCP service account with BigQuery query and metadata permissions.
SLACK_WEBHOOK_URL: Slack Incoming Webhook endpoint URL used for sending alert notifications.

Quick start

Configure GCP_PROJECT_ID, GCP_SERVICE_ACCOUNT_JSON, and SLACK_WEBHOOK_URL in your Kestra namespace secrets.
Import this flow YAML into your Kestra instance.
Click Execute in the UI to run an audit against your target dataset.
Check the execution outputs to view table compliance metrics and size breakdowns.

Common pitfalls & troubleshooting

BigQuery IAM Permissions: Ensure the GCP Service Account is granted roles/bigquery.metadataViewer and roles/bigquery.jobUser. Without metadataViewer, querying INFORMATION_SCHEMA.TABLE_STORAGE will fail with an Access Denied error.
Regional Location: BigQuery INFORMATION_SCHEMA queries must target the exact region where your dataset resides. If your dataset is hosted in a non-US multi-region (e.g. europe-west1), ensure the query job execution location matches.
Empty Table Storage: Newly created tables might take several minutes to reflect in INFORMATION_SCHEMA.TABLE_STORAGE.

How to extend

Add io.kestra.plugin.notifications.mail.MailSend inside the then: block to email weekly compliance reports to engineering managers.
Add a io.kestra.plugin.github.issues.Create task to automatically file tracking tickets for unpartitioned tables exceeding 100 GB.
Loop over multiple datasets using io.kestra.plugin.core.flow.Loop to audit entire enterprise GCP projects.

BigQuery Table Partition and Cost Governance Audit

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Inputs

Expected outputs

Prerequisites

Secrets

Quick start

Common pitfalls & troubleshooting

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

BigQuery Table Partition and Cost Governance Audit

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Inputs

Expected outputs

Prerequisites

Secrets

Quick start

Common pitfalls & troubleshooting

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Data Blueprints

Fetch JSON from an API in Python and load it into MongoDB

Generate and Execute SQL Queries from Natural Language Using OpenAI GPT-4o

AI Data Quality Anomaly Advisor and SQL Remediation Generator

Validate and Reshape a Webhook Order Payload with JSONata

AI Document Data Extraction: Parse PDFs and Web Pages into Structured JSON with OpenAI

Invoice Math Discrepancy Gate with In-Memory DuckDB Reconciliation

More Business Blueprints

AI Contract Clause Risk Analyzer and Legal Sign-Off Gate

AI-Powered Inventory Demand Forecasting, Auto-Generate Purchase Orders with OpenAI

AI Inbound Lead Enrichment, Scoring and VIP Routing Gate

Generate AI Blog Posts in Your Brand Voice and Publish to WordPress as Draft

Automate Customer Feedback Triage, Sentiment Analysis, and Escalation with Google Gemini and Slack

AI Customer Call Compliance and Regulatory Disclosure Auditor

New to Kestra?