Event-driven workflow based on a Snowflake query condition

Turn a Snowflake table into an event source. This blueprint polls a Snowflake query on a fixed interval and starts a Kestra workflow only when the query returns rows, so you can react to new or unprocessed records (new hires, new orders, pending events) without writing custom pollers or wiring up an external scheduler. It is a practical pattern for event-driven data pipelines built directly on top of your Snowflake warehouse.

How it works

1. A io.kestra.plugin.jdbc.snowflake.Trigger named wait runs every PT1M (one minute) and executes the SQL SELECT * FROM KESTRA.PUBLIC.EMPLOYEES WHERE START_DATE = CURRENT_DATE() and UPDATE_TIMESTAMP IS NULL. With fetchType: FETCH, all matching rows are pulled into the execution context.
2. The flow only starts when rows are returned. The matched rows are exposed as {{ trigger.rows }}.
3. A io.kestra.plugin.core.flow.ForEach task named each iterates over {{ trigger.rows }} and, for every row, runs an automated_process task (io.kestra.plugin.scripts.shell.Commands on a Process runner) that echoes the row payload and a welcome message built from FIRST_NAME and LAST_NAME.
4. A final io.kestra.plugin.jdbc.snowflake.Query task named update runs an UPDATE that stamps UPDATE_TIMESTAMP = SYSDATE() on the processed rows, so the same rows do not retrigger the flow on the next poll.

What you get

• A reusable event-driven trigger pattern on top of Snowflake.
• Built-in idempotency: the UPDATE clears the trigger condition so each row is processed once.
• Per-row fan-out via ForEach for clean, parallelizable processing.
• Shared connection settings via pluginDefaults, so the trigger and query reuse one URL, username, and password secret.

Who it's for

• Data engineers building event-driven pipelines on Snowflake.
• Analytics teams that need to act on newly arrived or changed rows.
• Ops teams automating onboarding, notifications, or downstream loads from a warehouse table.

Why orchestrate this with Kestra

Snowflake tasks and streams can detect change, but they cannot orchestrate multi-step downstream work, fan out per row, call shell scripts, or notify external systems. Kestra fills that gap: the SQL trigger turns a query result into a real event, ForEach parallelizes processing, retries and full execution logs give you observability and lineage, and everything is declared in version-controlled YAML instead of glue code. You also avoid double processing by combining the trigger predicate with a matching UPDATE (or DELETE).

Prerequisites

• A Snowflake account with a reachable account URL and warehouse.
• A table matching the trigger query (the example uses KESTRA.PUBLIC.EMPLOYEES with START_DATE and UPDATE_TIMESTAMP columns).

Secrets

• SNOWFLAKE_PASSWORD: the password for the Snowflake user, referenced as {{ secret('SNOWFLAKE_PASSWORD') }} in the trigger and query pluginDefaults.

Quick start

1. Add the SNOWFLAKE_PASSWORD secret to your Kestra instance.
2. Update the url and username in pluginDefaults to point at your account and warehouse.
3. Adjust the trigger sql, the WHERE clause, and the interval to match your table and polling needs.
4. Replace the automated_process commands with your real downstream logic.
5. Deploy the flow and insert a matching row to watch it trigger.

How to extend

• Swap the UPDATE for a DELETE or a status-flag change to mark rows processed.
• Replace the shell task with a load to another table, a notification, or an API call.
• Tune interval for tighter or looser polling, and add retries on the query and process tasks.
• Use the fetched rows to drive conditional branching or downstream subflows.

Links

• Plugin docs: https://kestra.io/plugins/plugin-jdbc-snowflake
• Snowflake orchestration: https://kestra.io/orchestration/snowflake