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.