Snowflake Database Management in VS Code

Overview

Snowflake is a cloud-based data warehousing platform designed for the modern data needs with key characteristics:

• Cloud-native architecture: Built from the ground up for the cloud
• Separation of storage and compute: Scale resources independently as needed
• Multi-cluster shared data: Query the same data concurrently without conflicts
• Cross-cloud compatibility: Available on AWS, Azure, and Google Cloud
• Data sharing: Securely share data without moving or copying it

Snowflake is ideal for data warehousing, data lakes, data engineering, data science, data application development, and secure data sharing.

Connecting

To connect to Snowflake in DBCode, you’ll need:

1. Open the DBCode Extension: Launch Visual Studio Code and open the DBCode extension.
2. Add a New Connection: Click on the “Add Connection” icon.
3. Complete connection form: Select Snowflake as the database type and enter:
   • Account identifier (e.g., xy12345.us-east-1.snowflakecomputing.com)
   • Authentication type (Username and Password, Key Pair, or Single sign-on) and the matching credentials
   • Role (optional)
   • Warehouse (the compute resource to use)
   • Database and schema (optional)
4. Connect: Click save to connect to your Snowflake account.
5. Start Managing Your Data: Explore databases, schemas, tables, and run queries.

For detailed instructions on connecting to Snowflake, refer to the Connect article.

Authentication

DBCode supports three authentication types for Snowflake. Pick one from the Authentication Type dropdown when you create or edit the connection.

Username and Password

Enter your Snowflake username and password. If your account enforces MFA, you are prompted on the first connection and DBCode caches the MFA token so you are not challenged again on every query.

For the cached token to persist, your Snowflake account must allow MFA token caching:

ALTER ACCOUNT SET ALLOW_CLIENT_MFA_CACHING = TRUE;

If caching is disabled on the account, each new connection has to re-authenticate.

Key Pair

Key pair authentication uses a private key instead of a password, so there is no browser prompt and no MFA challenge per query. This is the best option when MFA or SSO would otherwise interrupt every statement.

1. Generate a key pair and assign the public key to your Snowflake user. See Snowflake’s key-pair authentication guide.
2. Set Authentication Type to Key Pair.
3. Enter your Username.
4. Select your Private Key file (the PEM-encoded private key, usually a .p8 file).
5. If the key is encrypted, enter the Private Key Passphrase.

Single Sign-On (SSO)

DBCode supports Snowflake’s browser-based SSO authentication, including identity providers such as Okta. When using SSO, the extension opens your default browser for authentication.

DBCode caches the SSO token so you are not signed in repeatedly across connections. For the token to persist, your Snowflake account must allow ID token caching:

ALTER ACCOUNT SET ALLOW_ID_TOKEN = TRUE;

If this is disabled on the account, every new connection has to sign in through your identity provider again. This account setting is controlled by your Snowflake administrator, not DBCode.

Dev Container Support

When using SSO authentication in dev containers or remote environments, you may want to use a fixed port for the authentication callback instead of a random port. This simplifies port forwarding configuration.

Set the SF_AUTH_SOCKET_PORT environment variable to specify a fixed port:

export SF_AUTH_SOCKET_PORT=8765

Then configure your dev container to forward this port in .devcontainer/devcontainer.json:

"forwardPorts": [8765]

If SF_AUTH_SOCKET_PORT is not set, the Snowflake SDK uses a random available port (default behavior).

Snowflake Features in DBCode

DBCode enhances your Snowflake development experience with:

• Query profiling: Visualize query execution plans and performance metrics
• Data preview: Quickly view sample data from large tables
• Schema browsing: Navigate through databases, schemas, tables, and views

By using Snowflake with DBCode, you can efficiently develop and test data pipelines, analytical queries, and data transformations directly within Visual Studio Code.

For more information about Snowflake, check out Snowflake.