This guide provides specific instructions for preparing a Snowflake database for connection to elvex.

Before You Begin

Before connecting elvex to your Snowflake database, you should:

Have administrator access to your Snowflake account or work with someone who does
Understand which databases, schemas, and tables you want to make accessible
Identify which users or groups will need access to this data through elvex
Consider any data security or compliance requirements that apply to your data

Step 1: Create a Dedicated Service Account

For optimal security, create a dedicated service account that elvex will use to connect to your Snowflake database:

CREATE USER elvex_service PASSWORD = 'strong_password_here';

Step 2: Set Appropriate Permissions

Restrict the service account to only the permissions it needs by creating a role with limited permissions:

-- Create a role with limited permissions
CREATE ROLE elvex_reader;

-- Grant permissions to the role
GRANT USAGE ON WAREHOUSE warehouse_name TO ROLE elvex_reader;
GRANT USAGE ON DATABASE database_name TO ROLE elvex_reader;
GRANT USAGE ON SCHEMA database_name.schema_name TO ROLE elvex_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA database_name.schema_name TO ROLE elvex_reader;
GRANT SELECT ON ALL VIEWS IN SCHEMA database_name.schema_name TO ROLE elvex_reader;
GRANT SELECT ON FUTURE TABLES IN SCHEMA database_name.schema_name TO ROLE elvex_reader;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA database_name.schema_name TO ROLE elvex_reader;

-- Assign the role to the user
GRANT ROLE elvex_reader TO USER elvex_service;

-- Set the default role for the user
ALTER USER elvex_service SET DEFAULT_ROLE = elvex_reader;

-- Set the default warehouse for the user
ALTER USER elvex_service SET DEFAULT_WAREHOUSE = warehouse_name;

Step 3: Set Up Key Pair Authentication (Recommended)

Snowflake is transitioning to key pair authentication as the preferred method over password authentication. To set up key pair authentication:

Generate a public and private key pair for your Snowflake account. Read about how to set up a public and private key on the Snowflake help docs here: https://docs.snowflake.com/en/user-guide/key-pair-auth

Assign the public key to your Snowflake user:

ALTER USER elvex_service SET RSA_PUBLIC_KEY='your_public_key_here';

Keep the private key secure, as you'll need it when configuring the connection in elvex.

Step 4: Create Views for Complex Data Models (Optional)

If your data model is complex or you want to simplify access, consider creating views:

-- Create a view for customer summary
CREATE VIEW database_name.schema_name.customer_summary AS
SELECT
    c.customer_id,
    c.name,
    c.email,
    SUM(o.total_amount) AS total_spent,
    COUNT(o.order_id) AS order_count
FROM
    database_name.schema_name.customers c
JOIN
    database_name.schema_name.orders o ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id, c.name, c.email;

-- Grant access to the view
GRANT SELECT ON VIEW database_name.schema_name.customer_summary TO ROLE elvex_reader;

Step 5: Add Descriptions to Tables and Columns (Recommended)

Adding descriptions to your tables and columns will help elvex's AI better understand your data:

-- Add a comment to the table
COMMENT ON TABLE database_name.schema_name.customers IS 'Customer information including contact details';

-- Add comments to the columns
COMMENT ON COLUMN database_name.schema_name.customers.customer_id IS 'Unique identifier for each customer';
COMMENT ON COLUMN database_name.schema_name.customers.name IS 'Customer full name';

Step 6: Prepare Connection Information

Gather the following information which you'll need when setting up the connection in elvex:

Account identifier (e.g., xy12345.us-east-1 or org-account)
- To find this, look at your Snowflake URL. For example, in: https://app.snowflake.com/avtxyxg/rl12344/
- The org is "avtxyxg" and the account is "rl12344"
- You would enter: avtxyxg-rl12344 in the Account field
Database name
Schema name
Username (service account)
Password

Step 7: Test the Connection (Optional)

Before configuring elvex, test that your service account can connect to the database and access the intended tables:

Use the Snowflake web interface:
- Log in with the service account credentials
- Run a test query: SELECT * FROM database_name.schema_name.table_name LIMIT 5;

Or use SnowSQL CLI:

snowsql -a account_identifier -u elvex_service -P -d database_name -s schema_name -q "SELECT * FROM table_name LIMIT 5;"

This will prompt you for the service account password.

Make sure to replace database_name, schema_name and table_name with the correct values.

Step 8: Configure the Connection in elvex

Once you've completed the preparation steps, you're ready to configure the connection from elvex to Snowflake:

In elvex, navigate to the Datasources section
Click Create New Datasource
Select Database Connection as the datasource type
Choose Snowflake as the database type
Enter the connection details you gathered in Step 5:
- Account identifier
- Database name
- Schema name
- Username (service account)
- Password
Select the tables you want to include in your datasource
Review the schema and add any additional descriptions if needed
Configure access permissions for the datasource
Save the datasource

Important: Once saved, you will not be able to view these credentials again in the UI. They are encrypted in the backend and only decrypted when querying the database.

Snowflake-Specific Considerations

Key Pair Authentication

Snowflake is transitioning to key pair authentication as the preferred method over password authentication. While elvex currently supports password authentication, key pair authentication support is planned for future updates.

Zero-Copy Cloning

Snowflake's zero-copy cloning feature can be useful for creating read-only copies of production data for analysis. Consider creating a clone of your production database with only the necessary tables for your elvex integration:

-- Create a cloned database
CREATE DATABASE elvex_analytics CLONE production_db;

-- Grant access to the cloned database
GRANT USAGE ON DATABASE elvex_analytics TO ROLE elvex_reader;

Troubleshooting Snowflake Connections

Common Issues

Connection failures: Verify the account identifier format is correct
Authentication failures: Verify the service account username and password
Permission errors: Ensure the service account has the necessary roles and permissions
Warehouse suspended: Ensure the warehouse is running and the service account has usage permissions

Checking Permissions

To verify the permissions of your service account:

-- Check roles granted to the user
SHOW GRANTS TO USER elvex_service;

-- Check permissions granted to the role
SHOW GRANTS TO ROLE elvex_reader;

-- Check table access
SHOW GRANTS ON TABLE database_name.schema_name.table_name;

Next Steps

After successfully connecting elvex to your Snowflake database, you can:

Create assistants that use this datasource
Test natural language queries against your database
Share the assistants with appropriate users in your organization

How to connect to Snowflake