{"id":604,"date":"2025-01-31T06:17:06","date_gmt":"2025-01-31T06:17:06","guid":{"rendered":"https:\/\/datadandies.nl\/?p=604"},"modified":"2025-01-31T06:17:06","modified_gmt":"2025-01-31T06:17:06","slug":"creating-a-stage-in-snowflake-with-and-without-a-storage-integration","status":"publish","type":"post","link":"https:\/\/datadandies.nl\/index.php\/2025\/01\/31\/creating-a-stage-in-snowflake-with-and-without-a-storage-integration\/","title":{"rendered":"Creating a Stage in Snowflake with and without a Storage Integration"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Is there someone you trust, but really don\u2019t trust? Meaning, you would like to give him access to e.g. an S3 Bucket, but you don\u2019t want to share authentication data with him?<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Storage Integrations are a great way of giving untrustworthy individuals access in such a manner!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Kidding aside, Storage Integrations are a great way of handling authentication when you want to access files in an AWS S3 Bucket from Snowflake.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro padding-bottom-disabled cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1.3rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * 1.3rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:16px 0 0 16px;width:100%;text-align:left;background-color:#1e1e1e\"><span style=\"background:#c7c7c7;padding:0.3rem 0.5rem 0.2rem;border-radius:1rem;font-size:0.8em;line-height:1;height:1.25rem;text-align:center;display:inline-flex;align-items:center;justify-content:center;color:#1e1e1e\">SQL<\/span><\/span><span role=\"button\" tabindex=\"0\" data-code=\"-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n-- -- -- -- CREATING A STAGE WITHOUT STORAGE INTEGRATION -- -- -- --\n-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n\n-- Creating a stage WITHOUT a Storage Integration (not a best practise, but just to show you it is possible). First you will have to:\n    -- Create an IAM policy for a bucket in AWS\n    -- Create a user and attach the IAM policy to that user\n    -- Create an access key for that user and record the key-value pair\n\n-- Creating the stage using the new user created in AWS and the key-value pair to authenticate\nCREATE OR REPLACE STAGE MY_S3_STAGE\n    URL = 's3:\/\/mybucket'\n    CREDENTIALS = (AWS_KEY_ID = 'KEYID' AWS_SECRET_KEY = 'UNCRACKABLEKEY');\n\n-- Showing the files present in the stage\nLIST @MY_S3_STAGE;\n\n-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n-- -- -- --  CREATING A STAGE WITH A STORAGE INTEGRATION -- -- -- --\n-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n\n-- Creating a stage WITH a Storage Integration. First you will have to:\n    -- Create an IAM policy for a bucket in AWS\n    -- Create an IAM role in AWS\n\n-- Creating the Storage Integration; an IAM user will be created automatically by Snowflake\nCREATE OR REPLACE STORAGE INTEGRATION MY_S3_INTEGRATION\n    TYPE = EXTERNAL_STAGE\n    ENABLED = TRUE\n    STORAGE_PROVIDER = 'S3'\n    STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::1337:role\/snowflake_rol'\n    STORAGE_ALLOWED_LOCATIONS = ('s3:\/\/mybucket');\n\n-- After creating the Storage Integration, retrieve values STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID with DESCRIBE INTEGRATION\n    -- These properties are metadata about the IAM user automatically created by Snowflake when creating the Storage Integration\n-- Insert the STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID in the Trust policy of the role in AWS\nDESCRIBE INTEGRATION MY_S3_INTEGRATION;\n\n-- Validate Storage Integration using the system information function SYSTEM$VALIDATE_STORAGE_INTEGRATION\nSELECT SYSTEM$VALIDATE_STORAGE_INTEGRATION('MY_S3_INTEGRATION', 's3:\/\/mybucket', 'COPYINTOAppendtest1.csv', 'all');\n\n-- Creating the stage using the Storage Integration to authenticate\nCREATE OR REPLACE STAGE MY_S3_STAGE_STORAGE_INTEGRATION\n    URL = 's3:\/\/mybucket'\n    STORAGE_INTEGRATION = MY_S3_INTEGRATION;\n\n-- Checking out what is in the stage\nLIST @MY_S3_STAGE_STORAGE_INTEGRATION;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Kopieer\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #6A9955\">-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- -- -- -- CREATING A STAGE WITHOUT STORAGE INTEGRATION -- -- -- --<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- <\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- Creating a stage WITHOUT a Storage Integration (not a best practise, but just to show you it is possible). First you will have to:<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #6A9955\">-- Create an IAM policy for a bucket in AWS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #6A9955\">-- Create a user and attach the IAM policy to that user<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #6A9955\">-- Create an access key for that user and record the key-value pair<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- Creating the stage using the new user created in AWS and the key-value pair to authenticate<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">CREATE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">OR<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">REPLACE<\/span><span style=\"color: #D4D4D4\"> STAGE MY_S3_STAGE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">URL<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #CE9178\">&#39;s3:\/\/mybucket&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    CREDENTIALS = (AWS_KEY_ID = <\/span><span style=\"color: #CE9178\">&#39;KEYID&#39;<\/span><span style=\"color: #D4D4D4\"> AWS_SECRET_KEY = <\/span><span style=\"color: #CE9178\">&#39;UNCRACKABLEKEY&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- Showing the files present in the stage<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">LIST @MY_S3_STAGE;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- -- -- --  CREATING A STAGE WITH A STORAGE INTEGRATION -- -- -- --<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- <\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- Creating a stage WITH a Storage Integration. First you will have to:<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #6A9955\">-- Create an IAM policy for a bucket in AWS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #6A9955\">-- Create an IAM role in AWS<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- Creating the Storage Integration; an IAM user will be created automatically by Snowflake<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">CREATE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">OR<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">REPLACE<\/span><span style=\"color: #D4D4D4\"> STORAGE INTEGRATION MY_S3_INTEGRATION<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">TYPE<\/span><span style=\"color: #D4D4D4\"> = EXTERNAL_STAGE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">ENABLED<\/span><span style=\"color: #D4D4D4\"> = TRUE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    STORAGE_PROVIDER = <\/span><span style=\"color: #CE9178\">&#39;S3&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    STORAGE_AWS_ROLE_ARN = <\/span><span style=\"color: #CE9178\">&#39;arn:aws:iam::1337:role\/snowflake_rol&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    STORAGE_ALLOWED_LOCATIONS = (<\/span><span style=\"color: #CE9178\">&#39;s3:\/\/mybucket&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- After creating the Storage Integration, retrieve values STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID with DESCRIBE INTEGRATION<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #6A9955\">-- These properties are metadata about the IAM user automatically created by Snowflake when creating the Storage Integration<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- Insert the STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID in the Trust policy of the role in AWS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">DESCRIBE INTEGRATION MY_S3_INTEGRATION;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- Validate Storage Integration using the system information function SYSTEM$VALIDATE_STORAGE_INTEGRATION<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">SYSTEM<\/span><span style=\"color: #D4D4D4\">$VALIDATE_STORAGE_INTEGRATION(<\/span><span style=\"color: #CE9178\">&#39;MY_S3_INTEGRATION&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;s3:\/\/mybucket&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;COPYINTOAppendtest1.csv&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;all&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- Creating the stage using the Storage Integration to authenticate<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">CREATE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">OR<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">REPLACE<\/span><span style=\"color: #D4D4D4\"> STAGE MY_S3_STAGE_STORAGE_INTEGRATION<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">URL<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #CE9178\">&#39;s3:\/\/mybucket&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    STORAGE_INTEGRATION = MY_S3_INTEGRATION;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-- Checking out what is in the stage<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">LIST @MY_S3_STAGE_STORAGE_INTEGRATION;<\/span><\/span><\/code><\/pre><span style=\"display:flex;align-items:flex-end;padding:10px;width:100%;justify-content:flex-end;background-color:#1E1E1E;color:#c7c7c7;font-size:12px;line-height:1;position:relative\">SQL<\/span><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Is there someone you trust, but really don\u2019t trust? Meaning, you would like to give him access to e.g. an S3 Bucket, but you don\u2019t want to share authentication data with him? Storage Integrations are a great way of giving untrustworthy individuals access in such a manner! Kidding aside, Storage Integrations are a great way&hellip;<\/p>\n<p class=\"more-link\"><a href=\"https:\/\/datadandies.nl\/index.php\/2025\/01\/31\/creating-a-stage-in-snowflake-with-and-without-a-storage-integration\/\" class=\"themebutton\">Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[40,4],"class_list":["post-604","post","type-post","status-publish","format-standard","hentry","category-blog","tag-snowflake","tag-sql"],"_links":{"self":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/604","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/comments?post=604"}],"version-history":[{"count":1,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/604\/revisions"}],"predecessor-version":[{"id":605,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/604\/revisions\/605"}],"wp:attachment":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/media?parent=604"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/categories?post=604"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/tags?post=604"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}