Ingesting Data from Snowflake

Connecting to Snowflake

To connect to Snowflake, you need to provide the following information:

Name: A friendly name for your connection to easily identify and reuse it for ingesting additional tables
Snowflake Account Identifier: The first part of your Snowflake account’s URL (i.e., <account-identifier>.snowflakecomputing.com).
Warehouse: The warehouse that should be used to execute queries
Database: The database containing the tables you want to ingest
Authentication Type: The authentication method to use when connecting to Snowflake. Options include:
- Username/Password: Use a username and password for authentication
  - Note: Snowflake is phasing out password-based authentication and recommends using OAuth 2.0 instead
- OAuth 2.0: Use OAuth 2.0 for authentication
  - Client ID: The client ID for OAuth 2.0 authentication
  - Client Secret: The client secret for OAuth 2.0 authentication
Schema: The schema where your tables are located. If not specified, PUBLIC schema will be used by default, as it is the default schema created by Snowflake for new databases.
Role: The name of the role to use when connecting to Snowflake. If not provided, the user’s default role will be used.
- Note: Certain account-level roles like ACCOUNTADMIN cannot be used with OAuth 2.0 authentication

Prerequisites

Before connecting to Snowflake, ensure that:

Your Snowflake account is accessible and active
You have valid credentials or OAuth 2.0 setup configured
The specified warehouse, database, and schema exist
Your user account has the necessary permissions for the target objects

OAuth 2.0 Authentication

OAuth 2.0 authentication requires a client ID and client secret, which can be obtained from your Snowflake account settings. For more information on how to set up OAuth 2.0 authentication, refer to the Snowflake documentation.

OAuth 2.0 Authentication Steps for Vendia

The following steps outline how to set up OAuth 2.0 authentication in Snowflake to allow Vendia to connect to your Snowflake account:

Create an OAuth integration in Snowflake. The following SQL command can be used to create an OAuth integration:

 CREATE OR REPLACE SECURITY INTEGRATION MY_OAUTH_FOR_VENDIA
 TYPE = OAUTH
 ENABLED = TRUE
 OAUTH_CLIENT = CUSTOM
 OAUTH_CLIENT_TYPE = CONFIDENTIAL
 OAUTH_REDIRECT_URI = 'https://share.vendia.net/oauth/callback'
 OAUTH_ISSUE_REFRESH_TOKENS = TRUE;

Note: Replace MY_OAUTH_FOR_VENDIA with a name of your choice.

Obtain the client ID and client secret from the OAuth integration. You can do this by running the following SQL command:

SELECT
 CAST(
     GET_PATH(
     PARSE_JSON(
         SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('MY_OAUTH_FOR_VENDIA')
     ),
     'OAUTH_CLIENT_ID'
     ) AS TEXT
 ) AS CLIENT_ID,
 CAST(
     GET_PATH(
     PARSE_JSON(
         SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('MY_OAUTH_FOR_VENDIA')
     ),
     'OAUTH_CLIENT_SECRET'
     ) AS TEXT
 ) AS CLIENT_SECRET

Note: Replace MY_OAUTH_FOR_VENDIA with the name you used in step 1.

After you input the required information in the connection settings and click “Next”, Vendia will redirect you to the Snowflake login page. Log in with your Snowflake credentials and authorize Vendia to access your Snowflake account.
If the connection is successful, you will see a list of tables available in your Snowflake account. You can then select the tables you want to ingest into Vendia.

Required Permissions

The user account connecting to Snowflake must have the following permissions:

USAGE privilege on the warehouse
USAGE privilege on the database and schema
SELECT privilege on the tables you want to ingest
SHOW privilege to list available databases, schemas, and tables

Example Configuration

Here’s an example of a typical Snowflake connection configuration:

Field	Example Value
Name	Production Snowflake Data Warehouse
Account Identifier	abc12345.us-east-1
Warehouse	COMPUTE_WH
Database	ANALYTICS_DB
Schema	PUBLIC
Authentication Type	OAuth 2.0
Client ID	(from OAuth integration)
Client Secret	********
Role	ANALYST_ROLE

Vendia Supported and Unsupported Snowflake Data Types

Vendia Supported Snowflake Data Types	Vendia Unsupported Snowflake Data Types
BIGINT	ARRAY
BINARY	FILE
BOOLEAN	GEOGRAPHY
BYTEINT	GEOMETRY
CHAR	MAP
CHARACTER	OBJECT
DATE	VARIANT
DATETIME	VECTOR
DECIMAL
DOUBLE
DOUBLE PRECISION
FLOAT
FLOAT4
FLOAT8
INT
INTEGER
NUMERIC
NUMBER
REAL
SMALLINT
STRING
TEXT
TIME
TIMESTAMP
TIMESTAMP_LTZ **
TIMESTAMP_NTZ
TIMESTAMP_TZ **
TINYINT
VARBINARY
VARCHAR

** Note: Values of timezone-aware types are converted to the equivalent UTC value on ingestion.

Best Practices

Authentication: Use OAuth 2.0 instead of username/password authentication for enhanced security
Role Management: Use specific roles with minimal required permissions rather than account admin roles
Warehouse Sizing: Choose an appropriate warehouse size for your data ingestion workloads

Troubleshooting

If you encounter connection issues:

Account Identifier: Verify the account identifier format includes the region (e.g., abc12345.us-east-1)
OAuth Setup: Ensure the OAuth integration is properly configured with the correct redirect URI
Role Permissions: Check that the specified role has access to the warehouse, database, and schema
Warehouse State: Verify the warehouse is running and not suspended
Client Credentials: Confirm OAuth client ID and secret are correct and not expired
Database Access: Verify the database and schema exist and are accessible with the current role

Next Steps

After successfully connecting to your Snowflake account, you can:

Select specific tables to ingest
Configure data transformations and mappings
Set up incremental data ingestion jobs
Schedule regular data synchronization tasks