Snowflake CLI with Embucket

Connect and query Embucket using the official Snowflake CLI

The Snowflake CLI (snow) provides a command-line interface for interacting with Embucket’s Snowflake-compatible API. This guide walks through installation, configuration, and common workflows.

Installation

Install the Snowflake CLI using pip:

pip install snowflake-cli

Verify the installation:

snow --version

Connection Configuration

Locate Configuration File

First, find where the Snowflake CLI stores its configuration:

snow --info | grep "config_file_path" -A 1

This outputs the path to your config file (e.g., ~/.snowflake/config.toml).

CONFIG=<your_config_file_path>

Configure Local Connection

Add a connection profile for your local Embucket instance:

echo '
[connections.local]
host = "localhost"
region = "us-east-2"
port = 3000
protocol = "http"
database = "embucket"
schema = "public"
warehouse = "em.wh"
password = "embucket"
account = "acc.local"
user = "embucket"
' >> $CONFIG

The default credentials are user=embucket and password=embucket. These are demo credentials for local development.

Test the Connection

Verify your connection is working:

snow connection test -c local

Expected output:

+-----------------------------+
| key             | value     |
|-----------------+-----------|
| Connection name | local     |
| Status          | OK        |
| Host            | localhost |
| Account         | acc       |
| User            | embucket  |
| Role            | not set   |
| Database        | embucket  |
| Warehouse       | em.wh     |
+-----------------------------+

Running Queries

Basic Query Execution

Execute SQL queries using the snow sql command:

snow sql -c local -q "SELECT 1 as result;"

Output:

+--------+
| RESULT |
|--------|
|      1 |
+--------+

Date/Time Functions

Embucket supports Snowflake’s date and time functions:

snow sql -c local -q "SELECT dateadd(day, -1, current_timestamp()) as yesterday;"

Output:

+----------------------------------+
| yesterday                        |
|----------------------------------|
| 2025-01-02 03:04:05.040000+00:00 |
+----------------------------------+

Query External Catalogs

If you’ve configured external catalogs in your metastore config, query them:

# List schemas in a catalog
snow sql -c local -q "SHOW SCHEMAS IN demo;"

# List tables in a schema
snow sql -c local -q "SHOW TABLES IN demo.tpch_10;"

# Query table data
snow sql -c local -q "SELECT * FROM demo.tpch_10.customer LIMIT 10;"

Common Commands and Workflows

Schema Management

Create Schema

snow sql -c local -q "CREATE SCHEMA IF NOT EXISTS my_schema;"

Table Operations

Create Table

snow sql -c local -q "
CREATE TABLE IF NOT EXISTS users (
  id INT,
  name VARCHAR,
  created_at TIMESTAMP
);"

Data Manipulation

Insert Data

snow sql -c local -q "
INSERT INTO users VALUES 
  (1, 'Alice', CURRENT_TIMESTAMP()),
  (2, 'Bob', CURRENT_TIMESTAMP());"

Interactive SQL Session

Start an interactive SQL session instead of running one-off queries:

snow sql -c local

This opens an interactive prompt where you can run multiple queries:

embucket.public> SELECT 1;
embucket.public> SHOW TABLES;
embucket.public> exit;

Remote Connections

Lambda Deployment

For Embucket deployed on AWS Lambda, create a new connection profile:

snow connection add

Provide the following details:

Enter connection name: lambda
Enter account: acc.lambda
Enter user: embucket
Enter password: embucket
Enter role: em.role
Enter warehouse: em.wh
Enter database: demo
Enter schema: public
Enter host: https://<lambda-url>.lambda-url.us-east-2.on.aws
Enter port: (leave blank)
Enter region: us-east-2
Enter authenticator: (leave blank)

Test the Lambda connection:

snow sql -c lambda -q "SELECT dateadd(day, -1, current_timestamp()) as yesterday;"

Tips and Best Practices

Use Connection Aliases

Create multiple connection profiles for different environments (local, staging, production).

Query History

Access your query history with the up/down arrow keys in interactive mode.

Output Formats

The CLI outputs results in table format by default, making them easy to read.

Error Messages

Embucket returns Snowflake-compatible error codes for debugging failed queries.

Troubleshooting

Connection timeout errors

• Verify Embucket is running: docker ps or check the process
• Ensure the port matches your configuration (default: 3000)
• Check firewall rules if connecting to a remote instance

Authentication failed

• Verify username and password match your Embucket configuration
• Default credentials are embucket/embucket for demo environments
• For production, ensure proper credentials are configured

Database or schema not found

• Check available databases: SHOW DATABASES;
• Verify schema exists: SHOW SCHEMAS IN database_name;
• Use fully qualified names: database.schema.table

Next Steps

dbt Integration

Use Embucket as a dbt target for local development

Querying Guide

Learn about supported SQL syntax and query patterns

Security

Configure authentication and secure your deployment

External Catalogs

Connect to AWS S3 Table Buckets and Iceberg tables