Connecting from DataGrip to Snowflake using Azure AD Single Sign On

  1. Set Authentication to Authenticator, and Authenticator to externalbrowser. When choosing something other than externalbrowser I’ve gotten “The specified authenticator is not accepted by your Snowflake account configuration. Please contact your local system administrator to get the correct URL to use.”
  2. Set User to your full AD email user i.e. <yourname>@<yourdomain>
  3. Test connection, allow access from your browser, and you should be good to go.
  4. If you need to also set a role, choose More Options and check Role – you’ll get a new field in which you can set the role name. Same thing for Schema.

– via DataGrip and Snowflake docs

Create entities, grant permissions

USE ROLE ACCOUNTADMIN;

CREATE DATABASE "<DATABASE>";

CREATE SCHEMA "<DATABASE>"."<SCHEMA>";

CREATE ROLE "<ROLE>"; 

GRANT USAGE ON DATABASE "<DATABASE>" TO ROLE "<ROLE>";

GRANT CREATE SCHEMA ON DATABASE "<DATABASE>" TO ROLE "<ROLE>";

GRANT ALL ON SCHEMA "<DATABASE>"."<SCHEMA>" TO ROLE "<ROLE>"; 

CREATE USER <USER> PASSWORD='<PASSWORD>' DEFAULT_ROLE = "<ROLE>"; 

GRANT ROLE "<ROLE>" TO USER <USER>; 

GRANT ALL ON WAREHOUSE "<WAREHOUSE_NAME>" TO "<ROLE>";

– via Alooma Docs

Import Snowflake data using Azure Data Factory

Note that for direct copy you need an Azure Blob Storage linked service with SAS token auth, as highlighted here. If you’re using ADLS Gen 2, you can create a Blob linked service with SAS auth and take it from there.

Snowflake copy command payload is invalid

If you encounter errors such as Snowflake Export Copy Command validation failed: 'The Snowflake copy command payload is invalid. Cannot specify property: column mapping: Just reset the mapping from the copy activity. That’s it. That’s what worked for me.

– via Stack Overflow

Max file size (16777216) exceeded for unload single file mode

For errors such as ErrorCode=UserErrorOdbcOperationFailed, 'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [22000] Max file size (16777216) exceeded for unload single file mode. We recommend that you increase the max file size parameter, or disable single-file mode in the unload command and combine the unloaded files into a single file after you download them.:

Edit the Additional Snowflake copy options, and add property name SINGLE, with the value FALSE. Alternatively, add the MAX_FILE_SIZE property and set it to something like 268435456. But no more than 5368709120, otherwise you’ll get a compilation error: Max file size limit (5,368,709,120) for unload command has been exceeded.

– via Microsoft Q&A

View the source code for a Snowflake view

Use show views.

Specifically,

show views in schema your_schema;

– via Snowflake Docs