Snowpipe for event-driven loading in Snowflake - Commands & Configuration

Introduction

Loading data into Snowflake automatically when new files arrive can save time and reduce errors. Snowpipe helps by watching for new files and loading them right away without manual steps.

When you want new data files to be loaded into Snowflake as soon as they arrive in cloud storage.

When you have frequent small data files arriving and want to avoid batch loading delays.

When you want to automate data ingestion without writing custom scripts or manual commands.

When you want to reduce the time between data arrival and availability for queries.

When you want to integrate Snowflake with cloud storage event notifications for seamless loading.

Config File - pipe_creation.sql

pipe_creation.sql

CREATE OR REPLACE PIPE my_event_pipe
  AUTO_INGEST = TRUE
  AS
  COPY INTO my_table
  FROM @my_stage
  FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1);

-- This pipe listens for new files in the stage and loads them automatically.

This SQL file creates a Snowpipe named my_event_pipe that automatically loads CSV files from the stage my_stage into the table my_table.

AUTO_INGEST = TRUE enables event-driven loading using cloud storage notifications.

The COPY INTO command defines how files are loaded, including file format details.

Commands

This command creates or replaces the Snowpipe named 'my_event_pipe' that automatically loads CSV files from the stage 'my_stage' into 'my_table'.

Terminal

snowsql -q "CREATE OR REPLACE PIPE my_event_pipe AUTO_INGEST = TRUE AS COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1);"

Expected Output

Done. Snowpipe MY_EVENT_PIPE successfully created.

This command refreshes the pipe to ensure it is ready to receive event notifications and start loading new files.

Terminal

snowsql -q "ALTER PIPE my_event_pipe REFRESH;"

Expected Output

Done. Pipe MY_EVENT_PIPE refreshed.

This command checks the load history for 'my_table' to verify that Snowpipe has loaded new files in the last hour.

Terminal

snowsql -q "SELECT * FROM TABLE(INFORMATION_SCHEMA.LOAD_HISTORY(TABLE_NAME => 'MY_TABLE', START_TIME => DATEADD('hour', -1, CURRENT_TIMESTAMP())));"

Expected Output

LOAD_HISTORY_ID | FILE_NAME       | STATUS  | ROWS_LOADED | LOAD_TIME

123456789       | datafile1.csv   | LOADED  | 100         | 2024-06-01 12:00:00

123456790       | datafile2.csv   | LOADED  | 150         | 2024-06-01 12:05:00

Key Concept

If you remember nothing else from this pattern, remember: Snowpipe automatically loads new files into Snowflake as soon as they arrive, using cloud event notifications.

Common Mistakes

Not enabling AUTO_INGEST when creating the pipe.

Without AUTO_INGEST, Snowpipe will not listen for new file events and will not load files automatically.

Always set AUTO_INGEST = TRUE in the pipe creation SQL to enable event-driven loading.

Not configuring cloud storage event notifications to Snowflake.

Snowpipe relies on cloud storage events to trigger loading; without these notifications, files won't load automatically.

Set up cloud storage event notifications (e.g., AWS S3 event notifications) to notify Snowflake when new files arrive.

Using incorrect file format settings in the COPY INTO command.

If the file format does not match the actual files, loading will fail or produce incorrect data.

Define the correct file format options matching your data files in the COPY INTO command inside the pipe.

Summary

Create a Snowpipe with AUTO_INGEST = TRUE to enable automatic loading of new files.

Refresh the pipe to activate it and prepare for event-driven loading.

Check load history to verify that files are being loaded automatically by Snowpipe.

Practice

1. What is the main purpose of Snowpipe in Snowflake?

easy

A. To manually run SQL queries on data

B. To automatically load data when new files arrive in cloud storage

C. To create visual dashboards from data

D. To backup Snowflake databases

Solution

1. Step 1: Understand Snowpipe's role

   Snowpipe is designed to automate data loading triggered by new files in cloud storage.

2. Step 2: Compare options

   Options A, C, and D describe other tasks not related to Snowpipe's automatic loading feature.

3. Final Answer:

   To automatically load data when new files arrive in cloud storage -> Option B

4. Quick Check:

   Snowpipe = automatic data loading [OK]

Hint: Snowpipe triggers loading automatically on new files [OK]

Common Mistakes:

• Confusing Snowpipe with manual query execution
• Thinking Snowpipe creates dashboards
• Assuming Snowpipe is for backups

2. Which Snowpipe option enables event-driven automatic loading?

easy

A. MANUAL_TRIGGER = TRUE

B. AUTO_LOAD = FALSE

C. AUTO_INGEST = TRUE

D. EVENT_NOTIFY = OFF

Solution

1. Step 1: Identify the correct Snowpipe setting

   The setting AUTO_INGEST = TRUE enables event-driven automatic loading in Snowpipe.

2. Step 2: Eliminate incorrect options

   Options A, B, and D either disable loading or do not exist as valid Snowpipe settings.

3. Final Answer:

   AUTO_INGEST = TRUE -> Option C

4. Quick Check:

   AUTO_INGEST = TRUE enables event-driven loading [OK]

Hint: AUTO_INGEST = TRUE turns on event-driven loading [OK]

Common Mistakes:

• Using AUTO_LOAD instead of AUTO_INGEST
• Confusing manual trigger with automatic
• Turning event notifications off

3. Given this Snowpipe command:

CREATE PIPE my_pipe AUTO_INGEST = TRUE AS
 COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE = 'CSV');

What happens when a new CSV file is added to @my_stage?

medium

A. The file is automatically loaded into my_table

B. The file is ignored until manual load is triggered

C. An error occurs because AUTO_INGEST is not supported

D. The file is deleted from the stage

Solution

1. Step 1: Analyze the pipe creation

   The pipe is created with AUTO_INGEST = TRUE, meaning it listens for new files in the stage and loads them automatically.

2. Step 2: Understand the COPY INTO command

   The COPY INTO command loads data from the stage files into my_table using CSV format.

3. Final Answer:

   The file is automatically loaded into my_table -> Option A

4. Quick Check:

   New file + AUTO_INGEST = TRUE triggers load [OK]

Hint: AUTO_INGEST = TRUE pipes auto-load new files [OK]

Common Mistakes:

• Thinking manual load is needed
• Believing AUTO_INGEST is unsupported
• Assuming files get deleted automatically

4. You created a Snowpipe with AUTO_INGEST = TRUE but new files are not loading automatically. What is a likely cause?

medium

A. Cloud storage event notifications are not configured properly

B. The pipe was created without specifying a table

C. AUTO_INGEST must be set to FALSE for automatic loading

D. Snowpipe does not support CSV file formats

Solution

1. Step 1: Check event notification setup

   Snowpipe requires cloud storage event notifications to trigger automatic loading when AUTO_INGEST = TRUE.

2. Step 2: Review other options

   The pipe was created without specifying a table is invalid because pipe creation requires a target table. AUTO_INGEST must be set to FALSE for automatic loading is wrong since AUTO_INGEST = TRUE enables automatic loading. Snowpipe does not support CSV file formats is false; CSV is supported.

3. Final Answer:

   Cloud storage event notifications are not configured properly -> Option A

4. Quick Check:

   Missing event notifications block auto-load [OK]

Hint: Check cloud event notifications for AUTO_INGEST pipes [OK]

Common Mistakes:

• Setting AUTO_INGEST to FALSE expecting auto-load
• Ignoring cloud event notification setup
• Thinking CSV format is unsupported

5. You want to set up Snowpipe to load JSON files automatically into a table. Which sequence of steps is correct?

hard

A. Create a file format for CSV, create a pipe with AUTO_INGEST=TRUE, then upload JSON files

B. Create a pipe first, then create a stage and file format after uploading files

C. Upload files to the table directly, then create a pipe with AUTO_INGEST=FALSE

D. Create a file format for JSON, create a stage, create a pipe with AUTO_INGEST=TRUE, then upload files to the stage

Solution

1. Step 1: Prepare file format and stage

   Define a JSON file format and create a stage to hold files before loading.

2. Step 2: Create pipe with AUTO_INGEST=TRUE

   Create the pipe referencing the stage and file format, enabling automatic loading on new files.

3. Step 3: Upload JSON files to the stage

   Uploading files triggers Snowpipe to load data automatically into the target table.

4. Final Answer:

   Create a file format for JSON, create a stage, create a pipe with AUTO_INGEST=TRUE, then upload files to the stage -> Option D

5. Quick Check:

   File format + stage + pipe + upload = auto-load [OK]

Hint: Set file format and stage before pipe with AUTO_INGEST [OK]

Common Mistakes:

• Creating pipe before stage and file format
• Uploading files directly to table
• Using CSV format for JSON files