Snowflake Storage and Time Travel FinOps Audit | Kestra Blueprint

id: snowflake-storage-and-failsafe-cost-audit
namespace: company.team
description: |
  Automated Snowflake FinOps and storage audit workflow. Queries TABLE_STORAGE_METRICS to detect tables
  with runaway Time Travel and Fail-safe storage overhead exceeding active data size, alerting FinOps on Slack.

concurrency:
  limit: 1

inputs:
  - id: min_table_size_gb
    type: INT
    displayName: Minimum Table Size (GB)
    description: Only flag tables whose total physical footprint exceeds this size
      in gigabytes.
    defaults: 50

  - id: max_overhead_ratio
    type: FLOAT
    displayName: Maximum Protective Overhead Ratio
    description: Flag tables where Time Travel and Fail-safe storage exceed active
      data by this multiple.
    defaults: 2.0

  - id: storage_cost_per_tb_usd
    type: FLOAT
    displayName: Monthly Storage Price per TB ($)
    description: Estimated monthly cost per terabyte of Snowflake storage (standard
      list price is $23 to $40).
    defaults: 23.00

  - id: slack_channel
    type: STRING
    displayName: Alert Channel
    description: Slack channel destination for FinOps storage cost alerts.
    defaults: "#finops-alerts"

triggers:
  - id: weekly_finops_audit
    type: io.kestra.plugin.core.trigger.Schedule
    description: Automated storage audit executed weekly on Sunday at 06:00 UTC.
    cron: "0 6 * * 0"

tasks:
  - id: audit_storage_overhead
    type: io.kestra.plugin.jdbc.snowflake.Query
    description: Query Snowflake account usage storage metrics to identify tables
      with disproportionate Time Travel bloat.
    url: "{{ secret('SNOWFLAKE_JDBC_URL') }}"
    username: "{{ secret('SNOWFLAKE_USER') }}"
    password: "{{ secret('SNOWFLAKE_PASSWORD') }}"
    fetchType: FETCH
    sql: |
      SELECT
        table_catalog AS database_name,
        table_schema AS schema_name,
        table_name,
        is_transient,
        ROUND(active_bytes / (1024 * 1024 * 1024), 2) AS active_gb,
        ROUND(time_travel_bytes / (1024 * 1024 * 1024), 2) AS time_travel_gb,
        ROUND(failsafe_bytes / (1024 * 1024 * 1024), 2) AS failsafe_gb,
        ROUND((time_travel_bytes + failsafe_bytes) / (1024 * 1024 * 1024), 2) AS protective_storage_gb,
        ROUND(
          ((time_travel_bytes + failsafe_bytes) / NULLIF(active_bytes, 0)), 1
        ) AS protection_overhead_ratio,
        ROUND(((time_travel_bytes + failsafe_bytes) / (1024 * 1024 * 1024 * 1024)) * {{ inputs.storage_cost_per_tb_usd }}, 2) AS estimated_monthly_waste_usd
      FROM snowflake.account_usage.table_storage_metrics
      WHERE deleted = FALSE
        AND (active_bytes + time_travel_bytes + failsafe_bytes) >= ({{ inputs.min_table_size_gb }} * 1024 * 1024 * 1024)
        AND (time_travel_bytes + failsafe_bytes) >= (active_bytes * {{ inputs.max_overhead_ratio }})
      ORDER BY protective_storage_gb DESC;

  - id: evaluate_storage_anomalies
    type: io.kestra.plugin.core.flow.If
    description: Check if any tables breach the protective storage overhead ratio.
    condition: "{{ outputs.audit_storage_overhead.rows | length > 0 }}"
    then:
      - id: notify_finops_slack
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        description: Broadcast high-priority Snowflake storage cost alert with
          remediation guidance to Slack.
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        messageText: |
          *Snowflake Storage Bloat Detected*
          • *Offending Table*: `{{ outputs.audit_storage_overhead.rows[0].database_name }}.{{ outputs.audit_storage_overhead.rows[0].schema_name }}.{{ outputs.audit_storage_overhead.rows[0].table_name }}`
          • *Active Data*: `{{ outputs.audit_storage_overhead.rows[0].active_gb }} GB`
          • *Time Travel + Fail-safe*: `{{ outputs.audit_storage_overhead.rows[0].protective_storage_gb }} GB`
          • *Overhead Ratio*: `{{ outputs.audit_storage_overhead.rows[0].protection_overhead_ratio }}x` (Threshold: `{{ inputs.max_overhead_ratio }}x`)
          • *Is Transient*: `{{ outputs.audit_storage_overhead.rows[0].is_transient }}`
          • *Estimated Monthly Spend on Overhead*: `${{ outputs.audit_storage_overhead.rows[0].estimated_monthly_waste_usd }}`
          • *Remediation*: If this is an ETL staging or high-churn scratch table, recreate it as `TRANSIENT` or reduce retention: `ALTER TABLE {{ outputs.audit_storage_overhead.rows[0].table_name }} SET DATA_RETENTION_TIME_IN_DAYS = 1;`

    else:
      - id: log_compliant_storage
        type: io.kestra.plugin.core.log.Log
        description: Confirm that all Snowflake tables have proportionate Time Travel
          and Fail-safe allocations.
        message: "Snowflake storage footprint is optimized. No tables exceeding {{
          inputs.max_overhead_ratio }}x protective storage ratio."

  - id: export_finops_manifest
    type: io.kestra.plugin.core.debug.Return
    description: Output structured execution audit manifest for cloud spend tracking
      and historical FinOps reporting.
    format: |
      {
        "pipeline": "snowflake-storage-and-failsafe-cost-audit",
        "audited_at": "{{ execution.startDate }}",
        "flagged_tables_count": {{ outputs.audit_storage_overhead.rows | length }},
        "largest_overhead_table": "{{ outputs.audit_storage_overhead.rows | length > 0 ? outputs.audit_storage_overhead.rows[0].table_name : 'NONE' }}",
        "status": "{{ outputs.audit_storage_overhead.rows | length > 0 ? 'ACTION_REQUIRED' : 'OPTIMIZED' }}"
      }

In Snowflake cloud data warehouses, physical storage costs encompass three distinct layers: active data bytes, Time Travel retention bytes, and 7-day Fail-safe protection bytes. For high-churn ETL staging tables, scratch tables, or daily full-refresh data pipelines, default 90-day retention policies cause historical versions and fail-safe copies to swell to 5x to 10x the size of the underlying active table.

Organizations often incur thousands of dollars in monthly cloud storage fees for unneeded historical snapshots of temporary tables that are completely overwritten each morning.

This blueprint establishes an automated FinOps guardrail for Snowflake storage. Running weekly or on-demand, it queries SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS, calculates the protective storage overhead ratio ((time_travel + failsafe) / active_data), and flags tables larger than your threshold that exceed configured limits. When runaway bloat is detected, it alerts your data platform team on Slack with dollar waste estimates and exact SQL remediation commands.

How it works

Scheduled FinOps Audit: The weekly_finops_audit trigger (io.kestra.plugin.core.trigger.Schedule) executes every Sunday at 06:00 UTC, or runs on-demand via the Kestra UI.
Storage Metrics Query: The audit_storage_overhead task (io.kestra.plugin.jdbc.snowflake.Query) queries snowflake.account_usage.table_storage_metrics, joining active, time travel, and fail-safe bytes to compute protection overhead ratios and dollar impacts.
Anomaly Gate: The evaluate_storage_anomalies flowable task (io.kestra.plugin.core.flow.If) branches based on whether any tables breach max_overhead_ratio.
Slack Alert Dispatch: If wasteful tables are discovered, notify_finops_slack (io.kestra.plugin.slack.notifications.SlackIncomingWebhook) delivers an actionable Slack card detailing table names, active vs. protective gigabytes, and ALTER TABLE commands.
Optimized Logging: When all tables exhibit healthy storage ratios, log_compliant_storage records compliant status in the execution log.
Audit Manifest: The export_finops_manifest task records telemetry data for lineage logs and FinOps cost dashboards.

What you get

Automated discovery of Snowflake tables with runaway Time Travel and Fail-safe storage fees.
Identification of transient ETL tables misconfigured as permanent tables.
Estimated monthly dollar waste calculations directly in Slack notifications.
Immediate remediation SQL statements to reclaim storage capacity.

Who it is for

FinOps Practitioners and Cloud Economists governing Snowflake warehouse and storage spend.
Data Platform Engineers auditing multi-terabyte data warehouse assets.
Analytics Engineering leads configuring dbt model materializations and retention policies.

Why orchestrate this with Kestra

Snowflake provides storage views in Account Usage, but built-in alerts only notify on hard credit quotas rather than storage overhead ratios. Kestra provides declarative, scheduled orchestration: it handles secure Snowflake JDBC connectivity, computes statistical overhead ratios, evaluates threshold breaches, and dispatches rich formatted notifications to your team.

Inputs

Name	Type	Default	Description
`min_table_size_gb`	INT	`50`	Only flag tables whose total physical footprint exceeds this size in gigabytes.
`max_overhead_ratio`	FLOAT	`2.0`	Flag tables where Time Travel and Fail-safe storage exceed active data by this multiple.
`storage_cost_per_tb_usd`	FLOAT	`23.00`	Estimated monthly cost per terabyte of Snowflake storage.
`slack_channel`	STRING	`#finops-alerts`	Slack channel destination for FinOps storage cost alerts.

Expected outputs

{{ outputs.audit_storage_overhead.rows }}: Array of table storage records containing database_name, schema_name, table_name, active_gb, time_travel_gb, failsafe_gb, protection_overhead_ratio, and estimated_monthly_waste_usd.
{{ outputs.audit_storage_overhead.size }}: Total count of tables exceeding the protective storage ratio.
{{ outputs.evaluate_storage_anomalies }}: Result of conditional branch evaluation.
{{ outputs.export_finops_manifest.value }}: Structured JSON audit manifest recording execution timestamp and compliance status.

Prerequisites

A Snowflake account with access to SNOWFLAKE.ACCOUNT_USAGE schema views.
A Snowflake service account with a role granted IMPORTED PRIVILEGES on the SNOWFLAKE database.
A Slack Incoming Webhook URL targeting your FinOps notification channel.

Secrets

SNOWFLAKE_JDBC_URL: Snowflake JDBC connection string (e.g. jdbc:snowflake://<account_locator>.snowflakecomputing.com?warehouse=COMPUTE_WH&db=SNOWFLAKE&schema=ACCOUNT_USAGE).
SNOWFLAKE_USER: Username of the service account authorized to query account usage.
SNOWFLAKE_PASSWORD: Password for the Snowflake service account.
SLACK_WEBHOOK_URL: Slack Incoming Webhook endpoint URL used for sending alert notifications.

Quick start

Configure SNOWFLAKE_JDBC_URL, SNOWFLAKE_USER, SNOWFLAKE_PASSWORD, 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 initial audit of your Snowflake storage metrics.
Review execution outputs to inspect table storage breakdowns and overhead ratios.

Common pitfalls and troubleshooting

Account Usage View Latency: Views in SNOWFLAKE.ACCOUNT_USAGE such as TABLE_STORAGE_METRICS experience 45 to 120 minutes of data latency. Schedule the flow weekly or off-peak for reconciled metrics.
Dropped Tables: By default, TABLE_STORAGE_METRICS retains dropped tables during Time Travel and Fail-safe retention. The query filters WHERE deleted = FALSE to target currently active table assets.
Transient Tables: Transient tables do not incur Fail-safe storage fees and support a maximum of 1 day of Time Travel. Converting high-churn staging tables to TRANSIENT immediately prevents future bloat.

How to extend

Add an automated remediation task executing ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = 1; for approved non-production schemas.
Email weekly executive summaries to engineering managers using io.kestra.plugin.notifications.mail.MailSend.
Integrate with Jira or GitHub Issues to create automated optimization tickets for data engineers.

Snowflake Table Storage and Time Travel FinOps Audit

How it works

What you get

Who it is for

Why orchestrate this with Kestra

Inputs

Expected outputs

Prerequisites

Secrets

Quick start

Common pitfalls and troubleshooting

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

Snowflake Table Storage and Time Travel FinOps Audit

How it works

What you get

Who it is for

Why orchestrate this with Kestra

Inputs

Expected outputs

Prerequisites

Secrets

Quick start

Common pitfalls and troubleshooting

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Data Blueprints

Fan Out a Sifflet Rule Pack with Loop

Run a Sifflet Rule After a Warehouse Load

Incremental Postgres to S3 Parquet replication with Sling and Kestra watermarks

Daily Snowflake Digest as a Slack Canvas

Schedule a Daily Sifflet Observability Sweep

Recheck a Sifflet Rule After an Incident Fix

More Business Blueprints

Convert Natural Language Prompts to Structured Todoist Tasks with Deepseek AI

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

AI Customer Call Compliance and Regulatory Disclosure Auditor

Parallel Multilingual Markdown Translation with OpenAI

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

Invoice Math Discrepancy Gate with In-Memory DuckDB Reconciliation

New to Kestra?