Ingesting Data from Snowflake | Vendia
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,
PUBLICschema 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
ACCOUNTADMINcannot be used with OAuth 2.0 authentication
- Note: Certain account-level roles like
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.
2. Obtain the client ID and client secret from the OAuth integration. You can do this by running the following SQL command:
```sql
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.
3. 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.
4. 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 | |
| NUMBER | |
| NUMERIC | |
| 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:
1. **Account Identifier**: Verify the account identifier format includes the region (e.g., `abc12345.us-east-1`)
2. **OAuth Setup**: Ensure the OAuth integration is properly configured with the correct redirect URI
3. **Role Permissions**: Check that the specified role has access to the warehouse, database, and schema
4. **Warehouse State**: Verify the warehouse is running and not suspended
5. **Client Credentials**: Confirm OAuth client ID and secret are correct and not expired
6. **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