Snowpipe for event-driven loading in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a Snowpipe that loads data automatically from a stage.

Snowflake

CREATE PIPE my_pipe AUTO_INGEST = [1] AS COPY INTO my_table FROM @my_stage;

Drag options to blanks, or click blank then click option'

AFALSE

BON

CTRUE

DOFF

Attempts:

3 left

💡 Hint

Common Mistakes

Using FALSE disables automatic loading.

Using ON or OFF are invalid values for AUTO_INGEST.

Setting AUTO_INGEST to TRUE enables event-driven loading with Snowpipe.

2fill in blank

medium

Complete the code to create a notification integration for Snowpipe event notifications.

Snowflake

CREATE NOTIFICATION INTEGRATION my_integration TYPE = [1] ENABLED = TRUE;

Drag options to blanks, or click blank then click option'

AQUEUE

BAWS_SNS

CGCP_PUBSUB

DAWS_SQS

Attempts:

3 left

💡 Hint

Common Mistakes

Using AWS_SQS or AWS_SNS instead of QUEUE.

Using unsupported types like GCP_PUBSUB.

TYPE = QUEUE is used for notification integrations with Snowpipe on AWS SQS.

3fill in blank

hard

Fix the error in the COPY statement to load data from the stage correctly.

Snowflake

COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE = [1]);

Drag options to blanks, or click blank then click option'

ACSV

BJSON

CPARQUET

DTXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using TXT which is not a recognized file format type.

Using JSON or PARQUET if the files are CSV.

CSV is a valid file format type for Snowflake COPY INTO commands.

4fill in blank

hard

Fill both blanks to configure Snowpipe to listen to the correct cloud storage bucket and notification integration.

Snowflake

ALTER PIPE my_pipe SET (NOTIFICATION_CHANNEL = '[1]', NOTIFICATION_INTEGRATION = [2]);

Drag options to blanks, or click blank then click option'

Aarn:aws:sqs:us-west-2:123456789012:my-queue

Bmy_integration

Carn:aws:sns:us-west-2:123456789012:my-topic

Ddefault_integration

Attempts:

3 left

💡 Hint

Common Mistakes

Using SNS ARN instead of SQS ARN for notification channel.

Using default or wrong integration names.

The NOTIFICATION_CHANNEL must be the ARN of the SQS queue, and NOTIFICATION_INTEGRATION must be the integration name.

5fill in blank

hard

Fill all three blanks to create a stage, pipe, and grant usage for Snowpipe event-driven loading.

Snowflake

CREATE STAGE my_stage URL='s3://my-bucket/data/' STORAGE_INTEGRATION=[1];
CREATE PIPE my_pipe AUTO_INGEST=TRUE AS COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE=[2]);
GRANT USAGE ON INTEGRATION [3] TO ROLE my_role;

Drag options to blanks, or click blank then click option'

Amy_storage_integration

BCSV

Dmy_other_integration

Attempts:

3 left

💡 Hint

Common Mistakes

Using different integration names in stage and grant statements.

Using unsupported file format types.

The storage integration name must be consistent for the stage and grant statements, and CSV is the file format type.