Snowpipe¶

Snowpipe continuously loads data from files into standard tables and Apache Iceberg™ tables as the files arrive in a stage. You define the load once in a pipe, a schema object that holds a COPY INTO statement, and Snowpipe runs that statement for every new file on serverless compute that Snowflake manages. New data is typically available for queries within minutes, and you pay a fixed number of credits for each GB of data that Snowpipe loads.

For example, the following pipe loads JSON files from an external stage into a table as the files arrive:

CREATE PIPE mydb.myschema.orders_pipe
  AUTO_INGEST = TRUE
  AS
  COPY INTO mydb.myschema.orders
    FROM @mydb.myschema.orders_stage
    FILE_FORMAT = (TYPE = 'JSON')
    MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

This pipe defines where the files come from (the orders_stage external stage), where the data goes (the orders table), and how to read the files. AUTO_INGEST = TRUE turns on automated loading (also called auto-ingest): Snowpipe loads each new file when your cloud storage service reports that the file arrived. To load files from your own application instead, create the pipe without AUTO_INGEST = TRUE (the default is FALSE), and then call the Snowpipe REST API with the names of the files to load.

Before you create a pipe like this one, you need a stage that points to your files, a target table, and a role with the required privileges. For automated loading, you also configure event notifications for your storage location so that Snowpipe learns about new files. The steps and their order depend on your cloud provider. To learn how automated loading works and what to plan, see Automate Snowpipe with cloud event notifications. For step-by-step instructions, see Automate Snowpipe for Amazon S3, Automate Snowpipe for Google Cloud Storage, or Automate Snowpipe for Microsoft Azure Blob Storage. To use the REST API instead, set up key pair authentication or workload identity federation for the user that calls the API. For step-by-step instructions, see Set up the Snowpipe REST API.

With automated loading, a new pipe loads only the files that arrive after you set up event notifications. To load files that are already in the stage, see Load historical or missed files.

When to use Snowpipe¶

Snowpipe is a good fit when:

• Your data already arrives as files. For example, your applications write logs or your partners deliver exports to Amazon S3, Google Cloud Storage, Microsoft Azure storage, or a Snowflake internal stage. The storage can be on a different cloud platform from the one that hosts your Snowflake account.
• You want each file loaded soon after it lands. Files arrive continuously or at unpredictable times, and you want them loaded as they arrive rather than in scheduled batches.
• You don’t want to manage compute for loading. Snowflake provides and scales the compute for every load, so there’s no warehouse to size, start, or suspend, and no load schedule to orchestrate.
• You load standard tables or Iceberg tables. The same pipe syntax works for standard tables, Snowflake-managed Iceberg tables, and externally managed Iceberg tables. Snowpipe support for partitioned Iceberg tables is in public preview.

Another option is a better fit when:

• Your data arrives as rows or events, and you need it within seconds. Use Snowpipe Streaming. To ingest Apache Kafka topics, use the Snowflake Connector for Kafka, which is built on Snowpipe Streaming.
• You’re running a large one-time or scheduled batch load. Use bulk loading with COPY INTO and a warehouse. Bulk loading also lets you reload files with FORCE and, for standard tables, use VALIDATION_MODE to check files for errors before you load them.
• Your source is a database or SaaS application rather than files. Use an Openflow connector to ingest the data directly from the source system.

Neither Snowpipe nor bulk loading can join, aggregate, or filter rows during a load. If you need that logic, use Snowpipe to land the raw data, and then transform it with dynamic tables or streams and tasks.

The following table compares Snowpipe with bulk loading and Snowpipe Streaming:

How Snowpipe works¶

Each pipe watches one location: the stage URL plus an optional path in the pipe’s FROM clause. That path is called the pipe path. The pipe learns about new files in that location and loads them into one target table with serverless compute.

What happens when a file arrives¶

When a new file arrives in a pipe’s location, Snowpipe processes it as follows:

1. Snowpipe learns about the file, either from a cloud storage event notification or from a call to the Snowpipe REST API.
2. Snowpipe adds the file to the pipe’s ingest queue.
3. Snowflake-provided compute runs the pipe’s COPY INTO statement on the queued files and commits the rows to the target table.
4. Snowpipe records the file in the pipe’s load metadata, whether the file loaded or failed, so that Snowpipe ignores the file if it’s staged again.

Snowpipe is designed to load new data typically within a minute after it learns about a file. Large files, and files that need significant compute to decompress, decrypt, or transform, take longer. Snowflake doesn’t guarantee load latency, so test with a representative set of files to estimate the latency for your workload.

How Snowpipe detects new files¶

With automated loading (AUTO_INGEST = TRUE), your cloud storage service sends Snowpipe an event notification when a file arrives. With the Snowpipe REST API, your application tells Snowpipe which files to load. The following table shows the options for each storage location:

Keep the following in mind:

• Automated loading from internal stages is in public preview and is available only for accounts hosted on AWS. For more information, see CREATE PIPE.
• To keep automated loading traffic from Microsoft Azure on a private network, see Private connectivity to external stages and Snowpipe automation for Microsoft Azure.

Key concepts¶

The following concepts explain how a pipe is defined, which files it loads, and how it handles errors.

Pipes¶

A pipe is a named schema object that holds one COPY INTO statement. The statement identifies the source stage, the target table, the file format, and any copy options or transformations. You can’t edit a pipe’s definition. To change it, you recreate the pipe, which drops its load metadata. To find out which changes require a new pipe and how to recreate one without losing or duplicating files, see Change or recreate a pipe.

Stages and integrations¶

A pipe reads from an internal stage, or from an external stage that typically accesses cloud storage through a storage integration. For automated loading, Snowpipe receives event notifications through a notification channel: the cloud messaging queue that delivers them, such as the Amazon SQS queue that’s listed in the SHOW PIPES output. For Google Cloud Storage or Microsoft Azure, the pipe references a notification integration that gives Snowflake access to the Pub/Sub subscription or Azure storage queue.

File formats¶

Snowpipe loads the same file formats as bulk loading: delimited files such as CSV and TSV, JSON, Avro, ORC, Parquet, and XML. For more information, see Preparing to load data.

Transformations¶

A pipe’s COPY INTO statement can include a simple SELECT statement that reorders, omits, or casts columns, or that loads file metadata through METADATA$ columns. You can also match columns by name with MATCH_BY_COLUMN_NAME, but not in the same statement as a SELECT transformation. With MATCH_BY_COLUMN_NAME, you can let the table add columns as they appear in the files by using schema evolution. As with bulk loading, WHERE clauses, FLATTEN, joins, and GROUP BY aren’t supported, so load the raw data first and transform it downstream when you need that logic. For examples, see Transform data during a load.

Error handling¶

By default, Snowpipe doesn’t load any file that contains an error (ON_ERROR = SKIP_FILE) and continues to load the other files. Files that aren’t loaded because of errors have the Load failed status in the copy history and a FAILED state in Snowpipe events. Bulk loading, in contrast, stops the load by default when a file contains an error (ON_ERROR = ABORT_STATEMENT), an option that pipes don’t support. To find files that failed to load and their errors, use the tools in Monitoring and management.

Transactions¶

Snowpipe combines or splits loads into one or more transactions, based on the number and size of the rows in each file. In contrast, each bulk COPY INTO statement runs in a single transaction.

Load metadata¶

Each pipe keeps load metadata, which is a record of the files that it processed in the last 14 days, including the path and name of each file. Snowpipe ignores any staged file whose path and name match a file in this record, even if the file was modified and has a different ETag. Because Snowpipe also records files that failed to load, it ignores a corrected file that you stage again with the same name. For workarounds, see Reload a file that Snowpipe already processed.

After a file’s record expires, Snowpipe loads a file that’s staged again with the same path and name, which can duplicate data. Truncating the target table doesn’t clear the pipe’s load metadata.

Bulk loads keep separate load metadata on the target table for 64 days.

Load order¶

Each pipe has a single queue of files that are waiting to load, but multiple processes pull files from that queue. Snowpipe generally loads older files first, but doesn’t guarantee that files load in the order that they were staged.

Cost¶

In all Snowflake editions, Snowpipe charges a fixed number of credits per GB of data loaded, with no charge for warehouse time or for the number of files. Text files, such as CSV and JSON, are billed on their uncompressed size. Binary files, such as Parquet, Avro, and ORC, are billed on their size in storage (observed size), regardless of compression.

For the current rate, a worked cost estimate, queries that show your actual usage, and ways to control spending, see Snowpipe costs.

Monitoring and management¶

The following table lists the tools that you can use to monitor pipes and the files that they load:

To pause, resume, or recreate a pipe, load files that it missed, reload a corrected file, or clean up loaded files, see Manage Snowpipe. For the SQL commands that create and manage pipes, see Pipe DDL commands.

Access control¶

The following table lists the minimum privileges for common Snowpipe tasks:

After you create a pipe, the role that owns it must keep the privileges in the Own a pipe column, or the pipe can’t load files. The exception is USAGE on the error notification integration: without it, the pipe keeps loading files but stops sending error notifications. To check a pipe’s status with SYSTEM$PIPE_STATUS or in Snowsight, a role needs the MONITOR or OWNERSHIP privilege on the pipe, or the MONITOR EXECUTION privilege on the account.

To call the Snowpipe REST API, a role needs the USAGE privilege on the database and schema that contain the pipe, the OPERATE privilege on the pipe for the insertFiles endpoint, and either the MONITOR privilege on the pipe or the MONITOR EXECUTION privilege on the account for the insertReport and loadHistoryScan endpoints. The role doesn’t need privileges on the stage or the target table, because Snowpipe loads files with the privileges of the role that owns the pipe. To grant or revoke these privileges, use GRANT <privileges> … TO ROLE and REVOKE <privileges> … FROM ROLE.

Snowpipe with Iceberg tables¶

Snowpipe loads files into Iceberg tables with the same pipe syntax that it uses for standard tables. It supports Iceberg v2 and Iceberg v3 tables of the following types:

• Snowflake-managed Iceberg tables, which use Snowflake as the catalog. Snowpipe loads them with either load mode.
• Externally managed Iceberg tables, which use an external Iceberg REST catalog that Snowflake can write to. Snowpipe loads them only with FULL_INGEST and commits each load to the external catalog. If you specify LOAD_MODE = ADD_FILES_COPY for these tables, CREATE PIPE fails.

If another engine changes an externally managed table while Snowpipe is committing a load, Snowpipe retries the commit. If the commit still fails, Snowpipe doesn’t record the files as loaded and tries to load them again later.

Load modes for Iceberg tables¶

The LOAD_MODE copy option controls how Snowpipe writes the data. The following table shows each load mode, the source files and Iceberg tables that it supports, and what it does:

To register Iceberg-compatible Parquet files without rewriting them, create the table with quoted column names that match the Parquet column names exactly, including case. For all of the requirements, see the usage notes for loading Iceberg-compatible Parquet files. The following example creates a pipe that registers Iceberg-compatible Parquet files with a Snowflake-managed Iceberg table as the files arrive:

CREATE PIPE mydb.myschema.events_iceberg_pipe
  AUTO_INGEST = TRUE
  AS
  COPY INTO mydb.myschema.events_iceberg
    FROM @mydb.myschema.events_parquet_stage
    FILE_FORMAT = (TYPE = PARQUET USE_VECTORIZED_SCANNER = TRUE)
    MATCH_BY_COLUMN_NAME = CASE_SENSITIVE
    LOAD_MODE = ADD_FILES_COPY;

If your Parquet files don’t meet these requirements, omit LOAD_MODE so that the pipe uses FULL_INGEST.

When you use ADD_FILES_COPY, keep the following in mind:

• Pipes don’t support the PURGE copy option, so Snowpipe doesn’t delete the source files after it copies them into the table’s base location. To avoid storing the data twice, delete the source files after they load. For more information, see Delete files after Snowpipe loads them.
• Don’t use ADD_FILES_COPY to register Parquet files that already belong to another Iceberg table. To convert an externally managed Iceberg table to a Snowflake-managed table without rewriting its files, see ALTER ICEBERG TABLE … CONVERT TO MANAGED.

Partitioned Iceberg tables¶

Both Snowflake-managed and externally managed Iceberg tables can be partitioned. Snowpipe support for partitioned Iceberg tables is in public preview. For the features that Snowflake supports for each type of partitioned table, see the partitioning support matrix in Iceberg partitioning.

You don’t need to change the pipe to load a partitioned table. Snowpipe writes the rows from each file into data files according to the table’s partition specification and path layout. To partition a table that you create in Snowflake, include a PARTITION BY clause. An externally managed table that another engine already partitioned needs no changes. For the partition transforms that you can use, see the partition expression parameters for CREATE ICEBERG TABLE (Snowflake as the Iceberg catalog) or CREATE ICEBERG TABLE (Iceberg REST catalog). For example, the following statements create a Snowflake-managed Iceberg table that’s partitioned by day and region, and a pipe that loads JSON files into it:

CREATE ICEBERG TABLE mydb.myschema.events_by_day (
  event_id STRING,
  event_time TIMESTAMP_NTZ(6),
  region STRING,
  payload STRING
)
  PARTITION BY (DAY(event_time), region)
  EXTERNAL_VOLUME = 'my_ext_vol'
  CATALOG = 'SNOWFLAKE'
  BASE_LOCATION = 'events_by_day';

CREATE PIPE mydb.myschema.events_by_day_pipe
  AUTO_INGEST = TRUE
  AS
  COPY INTO mydb.myschema.events_by_day
    FROM @mydb.myschema.events_stage
    FILE_FORMAT = (TYPE = 'JSON')
    MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Keep the following in mind when you load partitioned Iceberg tables:

• Pipes that load partitioned tables use FULL_INGEST, which supports transformations and MATCH_BY_COLUMN_NAME. LOAD_MODE = ADD_FILES_COPY isn’t supported, so CREATE PIPE fails if you specify it.
• If Snowflake can’t compute a partition value for a row, for example because a value overflows a TRUNCATE transform, the file fails to load, even if the pipe uses ON_ERROR = CONTINUE. Other errors, such as type conversion errors, follow the pipe’s ON_ERROR setting.
• For a Snowflake-managed table, if you change the table’s partition specification with ALTER ICEBERG TABLE … ADD | DROP | REPLACE PARTITION BY, the pipe starts writing with the new specification within about 15 minutes. You don’t need to recreate the pipe. For an externally managed table, Snowpipe writes with the partition specification in the latest table metadata that Snowflake has, including changes that another engine makes.
• Each load writes at least one data file for each partition that the load’s rows fall into. To avoid many small data files, choose partition transforms that produce a moderate number of partitions, and follow the file sizing recommendations.

Best practices and limitations¶

Follow these practices to keep pipes efficient and reliable, and keep the following limitations in mind when you design a pipe.

Best practices¶

• Size files for throughput. Aim for compressed files of roughly 100 to 250 MB. If your source accumulates data slowly, create a new file about once per minute. Staging smaller files more often doesn’t guarantee lower latency. For more information, see File sizing for Snowpipe.
• Filter events at the source. Configure your cloud provider to send Snowpipe notifications only for the files that Snowpipe should load. Filtering reduces event noise and latency. For more information, see Filter events at the source.
• End stage URLs and pipe paths with a forward slash. Without the slash, a path also matches folders whose names start with the same characters, such as orders_archive/ for orders, so a pipe can load files that you didn’t intend. If another pipe loads that folder, both pipes load the same files. For more information, see Give each pipe its own location.
• Track load times with row timestamps. Don’t record load times with CURRENT_TIMESTAMP or similar functions, such as SYSDATE, either in a pipe’s COPY INTO statement or as a column default, because their values can be a few hours earlier than when the rows are committed. Instead, enable row timestamps on the target table (ROW_TIMESTAMP = TRUE) and query the METADATA$ROW_LAST_COMMIT_TIME column. Row timestamps aren’t supported for Iceberg tables. For more information, see Load times recorded with CURRENT_TIMESTAMP are earlier than expected.
• Set up error notifications. Error notifications tell you about failed loads without polling. To get a notification for every file that contains an error, keep the default ON_ERROR = SKIP_FILE setting. To also get alerts when a pipe stops, or when Snowflake can’t read a pipe’s notification channel (Google Cloud Storage and Microsoft Azure only), see Get alerted about Snowpipe problems.
• Plan for region-wide outages. To keep loading through a region-wide outage, use a failover group to replicate pipes, their target tables, and their load metadata to another account. For more information, see Stage, pipe, and load history replication. Failover groups require Business Critical Edition or higher. To switch to a secondary storage location after a failover without reconfiguring every pipe, see Multi-Location Resilience for Data Pipelines.

Limitations¶

• A pipe’s COPY INTO statement can’t use the FILES or VALIDATION_MODE parameter, or the FORCE, LOAD_UNCERTAIN_FILES, ON_ERROR = ABORT_STATEMENT, PURGE, RETURN_FAILED_ONLY, or SIZE_LIMIT copy option.
• ALTER PIPE … REFRESH queues only files that were staged within the last 7 days. To load older files, see Files staged more than 7 days ago.
• Snowpipe doesn’t load files from user stages or temporary stages.
• Snowpipe ignores a staged file whose path and name match a file that the pipe processed in the last 14 days, even if the file changed or failed to load. For more information, see Load metadata.
• While a pipe is paused, files stay in its queue for up to 14 days. A pipe that stays paused longer becomes stale. For more information, see Resume a stale pipe.
• Snowpipe doesn’t guarantee load latency or the order in which files load.
• Automated loading doesn’t work between government regions and commercial regions. For more information, see Automated loading doesn’t work across government and commercial regions.

What’s next¶

• To plan automated loading, see Automate Snowpipe with cloud event notifications.
• To set up automated loading, see Automate Snowpipe for Amazon S3, Automate Snowpipe for Google Cloud Storage, or Automate Snowpipe for Microsoft Azure Blob Storage.
• To load files from your own application, see Load data with the Snowpipe REST API.
• To load data into Iceberg tables, see Load data into Apache Iceberg™ tables and, for externally managed tables, Write support for externally managed Apache Iceberg™ tables.
• To pause, resume, or recreate pipes, see Manage Snowpipe.
• To monitor pipes and get notified when loads fail, see Manage Snowpipe in Snowsight, Snowpipe error notifications, and Monitor events for Snowpipe.
• To protect pipelines against region-wide outages, see Multi-Location Resilience for Data Pipelines.
• To understand and monitor costs, see Snowpipe costs.
• To resolve load problems, such as missing files or duplicate data, see Troubleshooting Snowpipe.
• For hands-on walkthroughs, see the Getting Started with Snowpipe and AWS CloudTrail Ingestion guides.
• If you can’t resolve an issue, contact Snowflake Support.