Loading from S3, Azure Blob, GCS in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Understanding Snowflake External Stage Behavior with S3

You create an external stage in Snowflake pointing to an S3 bucket with the following command:

CREATE STAGE mystage URL='s3://mybucket/data/' CREDENTIALS=(AWS_KEY_ID='AKIA...' AWS_SECRET_KEY='SECRET...');

You then run:

LIST @mystage;

What will this command output?

A A list of all files and folders under s3://mybucket/data/ including their sizes and timestamps.

B An error because credentials are missing.

C An empty list because Snowflake cannot list files in S3 buckets.

D Only files with .csv extension will be listed.

💡 Hint

Think about what the LIST command does on an external stage.

The LIST command on an external stage shows all files and folders under the specified URL in the cloud storage, including metadata like size and timestamp. Snowflake requires valid credentials to access the bucket, but if provided, it can list all files regardless of extension.

❓ Architecture

intermediate

Choosing Cloud Storage for Snowflake Data Loading

Your company uses Snowflake and wants to load large datasets from cloud storage. You have options: AWS S3, Azure Blob Storage, and Google Cloud Storage (GCS). Which factor is most important when choosing the cloud storage service for loading data into Snowflake?

A The geographic region of the storage service matching Snowflake's region to reduce latency.

B The cheapest storage cost regardless of location or compatibility.

C The number of files stored in the bucket.

D The color of the cloud provider's logo.

💡 Hint

Think about what affects data transfer speed and cost when loading data.

Choosing a cloud storage service in the same geographic region as your Snowflake account reduces latency and data transfer costs, improving load performance. Storage cost alone or unrelated factors like logo color do not impact loading efficiency.

❓ security

advanced

Securing Snowflake External Stage Access to Azure Blob Storage

You want to create an external stage in Snowflake to load data from Azure Blob Storage securely. Which method provides the best security practice for authentication?

A Embed the Azure storage account key directly in the stage definition without restrictions.

B Use a Shared Access Signature (SAS) token with limited permissions and expiration time.

C Use anonymous public access to the blob container.

D Store the storage account key in a plain text file on your local machine.

💡 Hint

Consider the principle of least privilege and temporary credentials.

Using a SAS token with limited permissions and expiration time limits access and reduces risk if credentials are compromised. Embedding full account keys or using public access exposes data to unauthorized users. Storing keys in plain text locally is insecure.

❓ Configuration

advanced

Configuring Snowflake Stage for Google Cloud Storage with IAM Role

You want to create an external stage in Snowflake to load data from Google Cloud Storage (GCS) using an IAM role for authentication. Which configuration is correct?

A CREATE STAGE mystage URL='gcs://mybucket/data/' CREDENTIALS=(AZURE_SAS_TOKEN='token');

B CREATE STAGE mystage URL='gcs://mybucket/data/' CREDENTIALS=(GCS_KEYFILE='path/to/key.json');

C CREATE STAGE mystage URL='gcs://mybucket/data/' CREDENTIALS=(AWS_KEY_ID='id' AWS_SECRET_KEY='key');

D CREATE STAGE mystage URL='gcs://mybucket/data/' STORAGE_INTEGRATION = my_gcs_integration;

💡 Hint

Think about how Snowflake integrates with GCS using IAM roles.

Using STORAGE_INTEGRATION with a configured GCS integration allows Snowflake to authenticate using IAM roles securely. Providing key files directly is less secure and not recommended. AWS or Azure credentials do not apply to GCS.

🧠 Conceptual

expert

Behavior of Snowflake COPY INTO with Multiple Cloud Storage Sources

You have three external stages in Snowflake pointing to S3, Azure Blob, and GCS respectively. You want to load data from all three into a single Snowflake table using the COPY INTO command. Which statement is true about this operation?

A COPY INTO automatically detects and merges data from all cloud providers if stages have the same name.

B COPY INTO can load from multiple external stages simultaneously by listing all stage URLs in one command.

C You must run separate COPY INTO commands for each external stage; COPY INTO cannot load from multiple stages at once.

D COPY INTO only supports loading from one cloud provider per Snowflake account.

💡 Hint

Consider how COPY INTO references external stages.

COPY INTO commands in Snowflake load data from one external stage at a time. To load from multiple cloud storage sources, you must run separate COPY INTO commands for each stage. Snowflake does not merge or combine multiple stages automatically in one command.

Practice

1. What is the main purpose of using COPY INTO in Snowflake when loading data from S3, Azure Blob, or GCS?

easy

A. To load data files from cloud storage into Snowflake tables

B. To export data from Snowflake to cloud storage

C. To create a new cloud storage bucket

D. To delete files from cloud storage

Solution

1. Step 1: Understand the role of COPY INTO

   The COPY INTO command is used in Snowflake to load data from external cloud storage into Snowflake tables.

2. Step 2: Differentiate from other operations

   Exporting data, creating buckets, or deleting files are not done by COPY INTO. It specifically loads data into tables.

3. Final Answer:

   To load data files from cloud storage into Snowflake tables -> Option A

4. Quick Check:

   Loading data = COPY INTO [OK]

Hint: COPY INTO loads data from cloud storage to tables [OK]

Common Mistakes:

• Confusing COPY INTO with export commands
• Thinking COPY INTO manages cloud storage buckets
• Assuming COPY INTO deletes files

2. Which of the following is the correct syntax to load data from an S3 bucket into a Snowflake table named my_table?

easy

A. COPY INTO my_table FROM @my_s3_stage FILE_FORMAT = (TYPE = 'CSV');

B. LOAD DATA INTO my_table FROM 's3://mybucket/data.csv';

C. INSERT INTO my_table SELECT * FROM s3://mybucket/data.csv;

D. IMPORT INTO my_table FROM @my_s3_stage FORMAT = CSV;

Solution

1. Step 1: Identify correct Snowflake COPY INTO syntax

   Snowflake uses COPY INTO table_name FROM @stage FILE_FORMAT = (TYPE = 'format') to load data.

2. Step 2: Eliminate incorrect options

   LOAD DATA INTO is not valid Snowflake syntax. Direct INSERT INTO ... FROM s3:// paths are not supported. IMPORT INTO does not exist. The correct syntax is COPY INTO my_table FROM @my_s3_stage FILE_FORMAT = (TYPE = 'CSV');.

3. Final Answer:

   COPY INTO my_table FROM @my_s3_stage FILE_FORMAT = (TYPE = 'CSV'); -> Option A

4. Quick Check:

   COPY INTO + stage + file format = correct syntax [OK]

Hint: COPY INTO + @stage + FILE_FORMAT is the right pattern [OK]

Common Mistakes:

• Using LOAD DATA instead of COPY INTO
• Trying to SELECT directly from S3 path
• Using IMPORT INTO which is invalid

3. Given the following Snowflake command:

COPY INTO sales FROM @azure_blob_stage FILE_FORMAT = (TYPE = 'JSON') ON_ERROR = 'CONTINUE';

What happens if one file in the Azure Blob storage has invalid JSON data?

medium

A. The entire load fails and no data is loaded

B. Snowflake automatically fixes the invalid JSON and loads all data

C. Only the invalid file is skipped, and loading continues for others

D. The command ignores the error and loads all files including invalid data

Solution

1. Step 1: Understand ON_ERROR = 'CONTINUE'

   This option tells Snowflake to skip files or rows with errors and continue loading the rest.

2. Step 2: Apply to invalid JSON file

   The invalid JSON file will be skipped, but other valid files will load successfully.

3. Final Answer:

   Only the invalid file is skipped, and loading continues for others -> Option C

4. Quick Check:

   ON_ERROR = CONTINUE skips errors, loads rest [OK]

Hint: ON_ERROR = CONTINUE skips bad files, loads others [OK]

Common Mistakes:

• Assuming entire load fails on one bad file
• Thinking Snowflake auto-fixes JSON errors
• Believing invalid data is loaded anyway

4. You run this command to load data from Google Cloud Storage:

COPY INTO customers FROM @gcs_stage FILE_FORMAT = (TYPE = 'CSV');

But you get an error saying 'Storage integration not authorized'. What is the most likely cause?

medium

A. The GCS bucket is empty

B. The CSV file format is incorrect

C. The Snowflake table does not exist

D. The storage integration lacks permission to access the GCS bucket

Solution

1. Step 1: Analyze the error message

   'Storage integration not authorized' means Snowflake cannot access the cloud storage due to permission issues.

2. Step 2: Identify cause

   The storage integration must have proper permissions to read from the GCS bucket. Other options do not cause authorization errors.

3. Final Answer:

   The storage integration lacks permission to access the GCS bucket -> Option D

4. Quick Check:

   Authorization error = permission issue [OK]

Hint: Authorization errors usually mean permission problems [OK]

Common Mistakes:

• Blaming file format for authorization errors
• Assuming table existence causes storage errors
• Ignoring permission setup for storage integration

5. You want to load multiple CSV files from an S3 bucket into Snowflake, but only files with the prefix 2024/. Which COPY INTO command correctly filters these files?

hard

A. COPY INTO sales FROM @s3_stage FILE_FORMAT = (TYPE = 'CSV') WHERE filename LIKE '2024/%';

B. COPY INTO sales FROM @s3_stage FILE_FORMAT = (TYPE = 'CSV') PATTERN = '^2024/.*';

C. COPY INTO sales FROM @s3_stage FILE_FORMAT = (TYPE = 'CSV') FILES = ('2024/');

D. COPY INTO sales FROM @s3_stage FILE_FORMAT = (TYPE = 'CSV') PATTERN = '2024/.*';

Solution

1. Step 1: Understand file filtering in COPY INTO

   Snowflake uses the PATTERN parameter with a regular expression to filter files by name or prefix.

2. Step 2: Check regex correctness

   PATTERN = '^2024/.*' matches files starting exactly with '2024/'. PATTERN = '2024/.*' without the ^ may match files where '2024/' appears elsewhere in the path. The other options use invalid parameters like WHERE or FILES.

3. Final Answer:

   COPY INTO sales FROM @s3_stage FILE_FORMAT = (TYPE = 'CSV') PATTERN = '^2024/.*'; -> Option B

4. Quick Check:

   PATTERN with ^ prefix filters files correctly [OK]

Hint: Use PATTERN with ^ prefix to filter file names [OK]

Common Mistakes:

• Omitting ^ in regex causing wrong files to load
• Using WHERE or FILES incorrectly for filtering
• Confusing file prefix with file list