Snowflake ETL: load files to internal stage, copy from stage to a table and run analytical SQL queries

A complete Snowflake ETL pipeline orchestrated by Kestra. The flow provisions a database and table, downloads a CSV from an external HTTP source, uploads it to a Snowflake internal stage, runs COPY INTO to load the staged file into the target table, executes an analytical aggregation, and exports the result as a downloadable CSV. It is a working pattern for teams who want a reproducible Snowflake load and transform job that runs on a schedule or on demand, without gluing together bash scripts, cron, and ad hoc SQL.

How it works

1. create_database runs io.kestra.plugin.jdbc.snowflake.Query to CREATE OR REPLACE DATABASE kestra.
2. create_table runs another Query task to create the KESTRA.PUBLIC.EMPLOYEES table with first name, last name, email, address, city, and start date columns.
3. extract uses io.kestra.plugin.core.http.Download to fetch a sample employees CSV from a public Hugging Face dataset.
4. load_to_internal_stage uses io.kestra.plugin.jdbc.snowflake.Upload to push the downloaded file into the @kestra.public.%employees internal stage with compress: true and a raw prefix.
5. load_from_stage_to_table runs COPY INTO KESTRA.PUBLIC.EMPLOYEES from the stage, using a CSV file format with header skip and ON_ERROR = 'skip_file'.
6. analyze runs a grouped aggregation on hires per year and month with fetchType: STORE, storing the result set in Kestra internal storage.
7. csv_report uses io.kestra.plugin.serdes.csv.IonToCsv to convert the Ion result into a CSV available from the Outputs tab.

Snowflake credentials are centralized via pluginDefaults for both Query and Upload tasks, so the JDBC URL, username, and password live in one place.

What you get

• A reproducible Snowflake load pattern: HTTP source, internal stage, COPY INTO, analytical SQL.
• A CSV export of the aggregated result, downloadable from the execution Outputs.
• Centralized Snowflake connection settings via pluginDefaults.
• A template you can adapt to any source file and any target table.

Who it's for

• Data engineers building Snowflake ingestion pipelines.
• Analytics engineers who need scheduled SQL transformations with logs and outputs.
• Platform teams standardizing how files land in Snowflake across projects.

Why orchestrate this with Kestra

Snowflake Tasks and Streams can schedule SQL inside the warehouse, but they cannot pull files from external HTTP endpoints, manage staging from outside Snowflake, or coordinate downstream steps such as CSV conversion and notifications. Kestra closes that gap with event and schedule triggers, retries, structured logs, output lineage between tasks ({{ outputs.extract.uri }}, {{ outputs.analyze.uri }}), and a declarative YAML definition you can review in Git.

Prerequisites

• A Snowflake account, warehouse (default COMPUTE_WH), and a user with privileges to create databases, tables, and stages.
• Kestra running locally or in your environment.

Secrets

• SNOWFLAKE_PASSWORD: password for the Snowflake user referenced in pluginDefaults.

Quick start

1. Replace accountID.snowflakecomputing.com and yourSnowflakeUser in pluginDefaults with your account and user.
2. Set the SNOWFLAKE_PASSWORD secret in Kestra.
3. Save the flow and run it.
4. Open the execution, inspect each task, and download the CSV report from the Outputs tab.

How to extend

• Swap the extract HTTP download for io.kestra.plugin.aws.s3.Download, GCS, Azure Blob, or an SFTP source.
• Replace the static CSV with a Kestra input or a schedule trigger that processes new files daily.
• Add a dbt task after the COPY INTO to model the raw table into marts.
• Push the CSV report to Slack, email, or object storage with a follow-up task.
• Add errors: handlers to alert on failed loads, and retries on transient JDBC errors.

Links

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