Home/Blog

Beyond Snowpipe: Use Kestra for Complete Snowflake Workflow Orchestration

December 17 2024Solutions

Martin-Pierre Roset

Table of Contents

OVERVIEW

Plugins have a Min. Compatible Kestra Version i.e. a Kestra version from where the corresponding plugin version is compatible. Learn more

Created by

Kestra Core Team

Managed by

Kestra Core Team

Table of Contents

Contribute

Snowflake is a leading cloud data platform that delivers scalable, real-time data solutions. Its Snowpipe feature automatically loads files into Snowflake from cloud storage. Though powerful, this is just the beginning. As data architectures become more complex, Snowpipe’s limitations emerge, leading teams to seek orchestration solutions.

This post explores Snowpipe’s limitations and highlights how Kestra helps you gain control, cut costs, and simplify your data operations.

Snowpipe: Great for Ingestion, but That’s It

Snowpipe efficiently handles automatic data ingestion into Snowflake. However, its scope ends at ingestion—it lacks tools for managing end-to-end workflows or system integration. Users typically need additional solutions for transformations, validations, and downstream processes.

Where Snowpipe Falls Short

• Limited Scope: Snowpipe handles ingestion only, not orchestration. After loading data, you’re on your own.
• Rigid Trigger Mechanisms: Snowpipe only triggers on file events (like files landing in S3 or GCS). This makes it unsuitable for API calls, time-based schedules, or event-driven processes.
• Minimal Debugging and Monitoring: Basic logging makes it hard to trace or debug multi-step pipelines or track data issues across stages.

While Snowpipe works well for simple ingestion tasks, it creates bottlenecks when your data ecosystem needs transformations, validations, or multi-platform integrations.

Kestra: Your Snowflake Workflow Orchestrator

Kestra fills Snowpipe’s gaps with a robust platform for orchestrating end-to-end workflows. It enables you to design comprehensive workflows—from data transformation to analytics—all through an intuitive interface.

Why Kestra?

Kestra turns static ingestion processes into dynamic, event-driven pipelines with features like:

• Multi-Stage Pipelines: Automate ingestion, transformations, validations, and downstream processes.
• Custom Triggers: Enable flexible automation with database events, API calls, and time-based schedules.
• Advanced Monitoring: Get real-time workflow visibility, historical execution tracking, and debugging in one dashboard.

Workflow Examples: From Ingestion to Transformation

Workflow 1: Ingestion, Transformation, and Validation in One Pipeline

Kestra lets you combine data ingestion, transformation, and validation in a single workflow, eliminating the need for multiple tools.

yaml
id: snowflake_pipeline
namespace: company.data

tasks:
  - id: ingest_data
    type: io.kestra.plugin.jdbc.snowflake.Upload
    stageName: my_stage
    prefix: raw_files
    fileName: data.csv

  - id: transform_data
    type: io.kestra.plugin.jdbc.snowflake.Query
    sql: |
      INSERT INTO transformed_data
      SELECT * FROM raw_data
      WHERE valid = true;

  - id: validate_data
    type: io.kestra.plugin.core.script.Script
    script: |
      echo "Validated records count: {{ outputs.transform_data.count }}"

This unified pipeline setup saves time and reduces error potential.

Workflow 2: Real-Time Trigger for Analytics

When your sales team needs instant insights from new Snowflake data, Kestra enables you to:

1. Monitor Snowflake tables with real-time triggers
2. Calculate campaign metrics from new data
3. Update business intelligence dashboards automatically

yaml
id: sales_insights
namespace: marketing.analytics

triggers:
  - type: io.kestra.plugin.jdbc.snowflake.trigger
    sql: SELECT MAX(updated_at) FROM sales_data;

tasks:
  - id: calculate_metrics
    type: io.kestra.plugin.jdbc.snowflake.Query
    sql: |
      SELECT campaign_id, SUM(revenue) AS total_revenue
      FROM sales_data
      GROUP BY campaign_id;

  - id: publish_dashboard
    type: io.kestra.plugin.core.http.Request
    url: "https://dashboard.company.com/api/update"
    method: POST
    body: "{{ outputs.calculate_metrics }}"

This automated workflow eliminates manual reporting steps.

Workflow 3: Advanced File Management

Kestra replaces ad hoc scripts with built-in tasks for conditional file processing, dynamic renaming, and archiving.

yaml
id: manage_files
namespace: company.data

tasks:
  - id: download_files
    type: io.kestra.plugin.jdbc.snowflake.Download
    stageName: raw_stage
    fileName: raw_data.csv

  - id: archive_files
    type: io.kestra.plugin.core.file.Move
    from: "{{ outputs.download_files.uri }}"
    to: "archive/raw_data_{{ execution.time }}"

This workflow ensures organized, automated file management.

Automating Git Workflows for dbt Projects in Snowflake

Kestra integrates Git and dbt to orchestrate version-controlled data transformations, enhancing team collaboration and consistency.

yaml
id: dbt_snowflake
namespace: company.team

tasks:
  - id: git
    type: io.kestra.plugin.core.flow.WorkingDirectory
    tasks:
      - id: clone_repository
        type: io.kestra.plugin.git.Clone
        url: https://github.com/kestra-io/dbt-example
        branch: main

      - id: dbt
        type: io.kestra.plugin.dbt.cli.DbtCLI
        docker:
          image: ghcr.io/kestra-io/dbt-snowflake:latest
        profiles: |
          my_dbt_project:
            outputs:
              dev:
                type: snowflake
                account: "{{ secret('SNOWFLAKE_ACCOUNT') }}"
                user: "{{ secret('SNOWFLAKE_USER') }}"
                password: "{{ secret('SNOWFLAKE_PASSWORD') }}"
                role: "{{ secret('SNOWFLAKE_ROLE') }}"
                database: "{{ secret('SNOWFLAKE_DATABASE') }}"
                warehouse: COMPUTE_WH
                schema: public
                threads: 4
                query_tag: dbt
        commands:
          - dbt deps
          - dbt build

This setup automates everything from repository cloning to dbt command execution.

Why Choose Kestra for Snowflake Workflows?

Kestra expands Snowflake’s possibilities by offering:

• Cost Efficiency: By orchestrating workflows end-to-end, Kestra minimizes the reliance on additional tools and custom scripts.
• Simplicity and Flexibility: Its declarative YAML-based configurations make it easy to build workflows, whether automating simple tasks or designing complex pipelines.
• Unified Ecosystem: Kestra supports an extensive range of plugins, allowing you to integrate Snowflake with cloud storage, message queues, APIs, and more—all within one platform.
• Cross-Team Collaboration: Non-developers can interact with workflows via Kestra’s intuitive UI, while developers retain full control over the underlying logic.

Conclusion: Kestra Complements Snowflake for Data Orchestration

Snowpipe remains a valuable tool for lightweight ingestion scenarios, but its scope is limited. For data teams managing complex workflows, Kestra provides the orchestration capabilities to scale, optimize, and simplify operations.

With Kestra, you move beyond basic ingestion to build dynamic, event-driven workflows that integrate with Snowflake. Whether it’s handling complex transformations, responding to real-time events, or managing downstream processes, Kestra ensures your data pipelines are both powerful and future-proof.