During this post we will discuss about the primary differences among Snowflake Internal stage and External stage. As we know there is snowflake internal staging area , specially named stages to store the feed files, then why opt external storage like AWS s3 buckets. For external storage we are paying the cost to the cloud provider . So it would be better if we can store all types of data into the snowflake internal staging area rather than opting Cloud storage.
There can be different prospective and use cases for using external stages. The named internal stage can be used for most of the tasks similar to the external stage but still there are a few points due to which customers prefer to use external stages.
- External stages are easier to manage in practice since there is a user interface to S3 to easily create folders and look at files vs always having to use SnowSQL or LIST commands.
- You can setup auto archiving features in S3 if you want while having to build your own Stored proc to do it on an internal stage.
- Snowpipe auto-ingest is a the one that makes ingesting data a lot easier with external stages vs trying to manage the REST API calls in internal stages.
- Many other ELT/ETL tools make it easy to dump the file to S3 and data engineering teams use this by using boto3, etc.
- For data we don’t intend to keep as flat-files, we use internal stages which are then loaded and deleted. For data where we need more auditing, we use external stages.
- you could still keep the files in an S3 bucket as an external stage but register the file as an external table to Snowflake and be able to run SQL on it.
- Encryption and Compression can be manage by user in case of external stages, while Snowflake does all that management if it is internally.
- Snowflake end-users can load data into their own tables without knowing s3/azure blob/GCS etc. Every user gets their own internal stage area like Table stage/User stage.
Thanks to Snowflake support team who helped me to make the clear understanding between these stages.
Refer the highlighted link for External and Internal stages overview. External and Internal.