Loading from S3, Azure Blob, GCS in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a stage for loading data from an S3 bucket.

Snowflake

CREATE OR REPLACE STAGE my_s3_stage URL='s3://mybucket/data/' [1] = (AWS_KEY_ID='your_key' AWS_SECRET_KEY='your_secret');

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

AENCRYPTION

BSTORAGE_INTEGRATION

CFILE_FORMAT

DCREDENTIALS

Attempts:

3 left

💡 Hint

Common Mistakes

Using FILE_FORMAT instead of CREDENTIALS for AWS keys.

Using ENCRYPTION keyword incorrectly here.

The CREDENTIALS keyword is used to provide AWS access keys for the S3 stage.

2fill in blank

medium

Complete the code to load data from an Azure Blob storage stage into a Snowflake table.

Snowflake

COPY INTO my_table FROM @my_azure_stage/[1] FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',');

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

Adata.csv

Bdata.parquet

Cfolder/

Ddata.json

Attempts:

3 left

💡 Hint

Common Mistakes

Using a JSON or Parquet file name when the format is CSV.

Using a folder path instead of a file name.

The file data.csv matches the CSV file format specified in the COPY command.

3fill in blank

hard

Fix the error in the Snowflake stage creation for Google Cloud Storage by completing the missing keyword.

Snowflake

CREATE OR REPLACE STAGE gcs_stage URL='gcs://mybucket/data/' [1] = (GCS_ACCESS_TOKEN='your_token');

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

AFILE_FORMAT

BENCRYPTION

CCREDENTIALS

DSTORAGE_INTEGRATION

Attempts:

3 left

💡 Hint

Common Mistakes

Using ENCRYPTION or STORAGE_INTEGRATION instead of CREDENTIALS.

Omitting the keyword entirely.

The CREDENTIALS keyword is required to provide the GCS access token for authentication.

4fill in blank

hard

Fill both blanks to create a file format for loading JSON files from cloud storage.

Snowflake

CREATE OR REPLACE FILE FORMAT my_json_format TYPE = '[1]' STRIP_OUTER_ARRAY = [2];

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

AJSON

BTRUE

CFALSE

DCSV

Attempts:

3 left

💡 Hint

Common Mistakes

Using CSV as the file format type for JSON files.

Setting STRIP_OUTER_ARRAY to FALSE when JSON arrays need to be stripped.

The file format type for JSON files is JSON, and STRIP_OUTER_ARRAY = TRUE helps parse JSON arrays correctly.

5fill in blank

hard

Fill all three blanks to copy data from an S3 stage into a table with a specific file format and error handling.

Snowflake

COPY INTO my_table FROM @my_s3_stage/[1] FILE_FORMAT = (TYPE = '[2]') ON_ERROR = '[3]';

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

Adata.csv

BCSV

CCONTINUE

Ddata.json

Attempts:

3 left

💡 Hint

Common Mistakes

Using a JSON file name with CSV file format.

Using ON_ERROR = 'ABORT_STATEMENT' instead of CONTINUE when skipping errors is desired.

The file data.csv matches the CSV file format, and ON_ERROR = 'CONTINUE' tells Snowflake to skip errors and continue loading.