Stages (internal and external) in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Internal vs External Stages in Snowflake

Which statement correctly describes the difference between an internal stage and an external stage in Snowflake?

A Internal stages are used only for temporary data, while external stages permanently store data inside Snowflake tables.

B Internal stages store data within Snowflake's managed storage, while external stages reference data stored outside Snowflake, such as in AWS S3 or Azure Blob Storage.

C External stages store data inside Snowflake's managed storage, and internal stages reference external cloud storage services.

D Internal stages require manual data encryption, while external stages automatically encrypt data stored in Snowflake.

💡 Hint

Think about where the data physically resides when using internal versus external stages.

Internal stages keep data inside Snowflake's own storage system. External stages point to data stored outside Snowflake, like in cloud storage services.

❓ Configuration

intermediate

Creating an External Stage for AWS S3

You want to create an external stage in Snowflake that points to an AWS S3 bucket named mybucket in the us-west-2 region. Which command correctly creates this stage assuming you have the necessary AWS credentials?

A CREATE STAGE mystage URL='s3://mybucket/' STORAGE_INTEGRATION = my_s3_integration;

B CREATE STAGE mystage URL='gcs://mybucket/' STORAGE_INTEGRATION = my_s3_integration;

C CREATE STAGE mystage URL='s3://mybucket/' CREDENTIALS = (AWS_KEY_ID='key' AWS_SECRET_KEY='secret');

D CREATE STAGE mystage URL='azure://mybucket/' STORAGE_INTEGRATION = my_s3_integration;

💡 Hint

Remember the URL prefix for AWS S3 and how Snowflake uses storage integrations for credentials.

The URL for AWS S3 starts with s3://. Using a storage integration is the recommended way to provide credentials securely.

❓ Architecture

advanced

Choosing Between Internal and External Stages for Data Loading

Your company has large datasets stored in AWS S3 and wants to load data into Snowflake efficiently. Which architecture choice is best to minimize data transfer costs and maximize performance?

A Use an external stage pointing directly to the AWS S3 bucket to load data, avoiding copying data into Snowflake storage first.

B Always copy data from S3 into an internal stage before loading to benefit from Snowflake's managed storage caching.

C Load data directly from local files on user machines to Snowflake without using any stage.

D Use an external stage but configure Snowflake to replicate the data into Azure Blob Storage first.

💡 Hint

Consider data transfer costs and where the data is stored.

Using an external stage pointing directly to S3 avoids extra data copying and reduces transfer costs. Copying into internal stages adds extra steps and storage costs.

❓ security

advanced

Securing Access to External Stages in Snowflake

Which method provides the most secure way to grant Snowflake access to an external stage in AWS S3 without exposing AWS keys directly in the stage definition?

A Use public read permissions on the S3 bucket to avoid authentication.

B Embed AWS access keys directly in the stage URL for quick access.

C Use a Snowflake storage integration that leverages an AWS IAM role with limited permissions.

D Store AWS keys in a Snowflake table and reference them in the stage definition.

💡 Hint

Think about best practices for credential management and least privilege.

Storage integrations use IAM roles to securely grant Snowflake access without exposing keys. Embedding keys or making buckets public is insecure.

❓ service_behavior

expert

Behavior of Data Retention in Internal Stages

Consider a Snowflake internal stage used for temporary file storage during data loading. What happens to files in this internal stage if they are not explicitly removed after 14 days?

A Files are automatically moved to an external stage after 14 days.

B Files remain indefinitely until manually deleted by the user.

C Snowflake archives files older than 14 days but charges extra storage fees.

D Snowflake automatically removes files older than 14 days from internal stages to free up storage.

💡 Hint

Think about Snowflake's automatic cleanup policies for internal stages.

Snowflake automatically removes files older than 14 days from internal stages to manage storage efficiently.