Snowflake Security: Best Practices for Stages

One of the toughest problems in DataOps is having robust access to data. We’ve discussed this in length, specifically to Snowflake roles and user management strategies. However, today, what I’d like to discuss is an earlier stage of the data operations, which is data ingestion to Snowflake and specifically Snowflake stages.

If you’re an organization that is either looking into or using Snowflake as your data warehouse, data lake, or data lakehouse solution, you probably know that the data you’re using comes from somewhere. Let’s look at the main sources from which data is imported to Snowflake:

• Data lake – Your data might come from your own data lake, holding a lot of data, and you may only import parts of it to Snowflake for different reasons.
• Other data warehouses – You may have databases close to your applications, which send aggregated data to Snowflake for further analytics.
• Applications – Sometimes applications send data to Snowflake as part of their operation.
• Telemetry – Results of telemetries may be sent from applications and devices.
• Security logs and other logs – You may use Snowflake as a security data lake or for analyzing operational logs.
• Manual loading of data – Sometimes teams upload their own data by preparing files and ingesting them.
• Data Sharing – Data may be ingested from shared data using a Snowflake secure data share or other datastores.

Let’s look at how data is loaded into Snowflake and some of the things you need to pay attention to for a secure operation of stages.

Loading data into Snowflake

Data is loaded to Snowflake from stages. Stages are either internal or external (public cloud storage). A stage is a Snowflake object that you can manage using SQL. This can be as simple as a CREATE STAGE <stagename> or customized with optional parameters.

Snowflake internal stage vs external stage

The Snowflake stages can either be internal (part of your Snowflake account storage) or external (using storage buckets). More on loading the data using stages can be found in Snowflake documentation.

Snowflake stage security best practices

Let’s discuss some of the important aspects of data operations security when it comes to loading data from stages.

Loading encrypted data to Snowflake

Use the following command to use AWS SSE (Server-Side Encryption) on the files in the stage:

CREATE STAGE sandwich_hr
url='s3://sandwich/hr/'
credentials=(aws_key_id='<KEY_ID>' aws_secret_key='<SECRET_KEY>')
encryption=(type = 'AWS_SSE_S3');

Eliminate stages available to the public role

Stages owned by the public role are accessible to all users. You can use the following command to find these stages:

SELECT stage_name, stage_catalog, stage_schema, stage_type, stage_url, created
FROM snowflake.account_usage.stages
WHERE STAGE_OWNER = 'PUBLIC'
AND DELETED ISNULL
ORDER BY CREATED;

Eliminate stages left open

Find stages that are open and potentially hold sensitive data:

SELECT stage_name, stage_catalog, stage_schema, stage_type, stage_url, created
FROM snowflake.account_usage.stages
WHERE DELETED ISNULL
ORDER BY CREATED;

Monitoring import and export operations

Monitor stages that can be used for exporting data. Example to monitor copy operations:

SELECT start_time, user_name, role_name, query_text
FROM snowflake.account_usage.query_history
WHERE query_type = 'COPY'
ORDER BY start_time DESC;

Conclusion

Data ingestion should be handled with care as part of the organization’s DataSecOps mindset. Similar to not leaving unattended S3 buckets, stages should not be left unattended.