Snowflake

Snowflake requires the optional snowflake-connector-python dependency:

pip install 'sqlbuild[snowflake]'
# or
uv pip install 'sqlbuild[snowflake]'

Connection config

adapter = "snowflake"
default_target = "dev"

[connections.warehouse]
account = "my_org-my_account"
user = "my_user"
password = "my_password"
role = "TRANSFORM_ROLE"
warehouse = "TRANSFORM_WH"

[targets.dev]
connection = "warehouse"
database = "ANALYTICS"
schema = "RAW"

Connection fields are passed directly to snowflake.connector.connect(). See the Snowflake Connector documentation for all available options, including key-pair authentication, OAuth, and SSO. Put the authoritative database and schema on the target.

SQLBuild turns off the connector’s platform detection by setting SNOWFLAKE_DISABLE_PLATFORM_DETECTION=true when the variable is unset. Detection runs on every connect: it probes cloud metadata endpoints, calls AWS to identify the caller for client telemetry, and can print botocore warnings when an AWS SSO session has expired. To keep it on, set the variable yourself before running sqb, for example SNOWFLAKE_DISABLE_PLATFORM_DETECTION=false.

Session initialization

On connect, SQLBuild applies the connection’s role and warehouse, then applies the active target’s authoritative database and schema. These ensure the session context is set correctly regardless of the user’s default settings.

The warehouse can differ by command. A target’s [targets.<name>.warehouses] section selects one warehouse for build-style commands and another for ad-hoc commands such as sqb query and sqb diff, and --warehouse overrides both for one invocation. See command-group warehouses.

Case-sensitive column names

Snowflake stores an unquoted column name in upper case, so order_id and ORDER_ID name the same column. A column created with a quoted name in mixed or lower case, such as "orderId", keeps its exact case, and only the quoted name reads it.

Model settings that name such a column (unique_key, updated_at, observed_at, check_columns, cursor and merge_exclude_columns) take the quoted name inside a string:

MODEL (
  description 'Order history.',
  materialized snapshot,
  unique_key ['"orderId"'],
  snapshot_strategy timestamp,
  updated_at '"updatedAt"',
);

A snapshot or microbatch build stops when an unquoted setting names a case-sensitive column, with an error such as unique_key 'orderId' names the case-sensitive column "orderId"; write it with its quotes, as '"orderId"'.

Planning metadata

Planning reads relation and column metadata with SHOW TABLES, SHOW VIEWS, and SHOW COLUMNS once per schema, several schemas at a time. SHOW commands are served by Snowflake’s metadata service, so they don’t wait for, or resume, a warehouse. Two reads still use INFORMATION_SCHEMA and need a running warehouse:

• LAST_ALTERED for source freshness, read only for the sources the plan observes.
• Columns for a schema whose SHOW COLUMNS output is exactly 10,000 rows, the documented limit at which the output may be cut off.

Run sqb plan -v to see each metadata query with its elapsed time and row count.

Build metadata

Builds and scenario runs read relation metadata with SHOW commands too, so these reads don’t need a running warehouse either:

Read	Statement
Whether one relation exists	SHOW TERSE TABLES and SHOW TERSE VIEWS
A second relation in the same schema	SHOW TABLES and SHOW VIEWS for the whole schema, once
A table’s time travel retention	SHOW TABLES LIKE, or the schema listing
One relation’s columns	SHOW COLUMNS
A scenario’s leftover relations	SHOW TERSE OBJECTS for the scenario’s name prefix

Each run reuses these answers until it changes that relation, for example by replacing a delta table or adding a column, and then reads it again.

State tables

SQLBuild records its state in _sqlbuild_* tables, such as _sqlbuild_fingerprints, _sqlbuild_source_freshness, and _sqlbuild_microbatches. On Snowflake, SQLBuild creates every state table as a permanent table with DATA_RETENTION_TIME_IN_DAYS = 90, the maximum time travel, so the table also has Fail-safe. On editions that allow only 1 day of time travel, Snowflake rejects 90 and SQLBuild creates the table with 1 day instead.

The retention is set only in the statement that creates the table. SQLBuild never alters an existing state table, so a state table that already exists keeps its current table type and retention. Models are not affected: their table type follows the model table_type setting.

Shared connections across targets

Use multiple targets to reuse one Snowflake connection while selecting different authoritative databases and schemas:

adapter = "snowflake"

[connections.warehouse]
account = "my_org-my_account"
user = "my_user"
password = "my_password"
role = "TRANSFORM_ROLE"
warehouse = "TRANSFORM_WH"

[targets.prod]
connection = "warehouse"
database = "PROD_DB"
schema = "prod"

[targets.dev]
connection = "warehouse"
database = "DEV_DB"
schema = "dev"

Cost estimates

Snowflake builds show a compact per-run busy-compute estimate. SQLBuild attributes visible overlapping query intervals fairly across active queries, converts attributed seconds using the warehouse-size credit rate, and estimates USD from the configured rate:

[cost]
usd_per_credit = 3.00

The default is 3.00 USD per credit and is visibly marked as a default. Set it to your Snowflake contract rate. Use sqb cost, sqb cost latest, sqb cost <run_id>, or sqb cost history --since 7d to inspect persisted records. --json and --json-output PATH provide a versioned, decimal-safe output contract. Pending detail records are refreshed from Snowflake when inspected again.

SQLBuild reads INFORMATION_SCHEMA.QUERY_HISTORY for the whole run in one query when the run finishes. Each read has a 10-second statement timeout; a window that reaches it, or that returns more rows than one read allows, is split in half and read again. Collection at the end of a command has a 30-second budget so the command exits promptly. If history cannot be read within it, the build is unaffected and its cost is recorded as pending, with a message naming the timeout. sqb cost <run_id> collects it later with a 5-minute budget, which completes long runs. Any other failure is recorded as collection_failed with the Snowflake error that caused it.

These values are attributed compute credits and estimated cost, not Snowflake-billed credits or invoice reconciliation. The estimate uses only query history visible to the executing role and does not reconstruct invisible concurrent work, warehouse resume or idle tail, the 60-second minimum, cloud-services credits, contract adjustments, or multi-cluster billing. A statement run with a QUERY_TAG of your own is not recognised as a metadata-only CREATE, so it may report missing warehouse metadata. Run metadata and query IDs are stored under target/executions/<run_id>/; that statement ledger stores only an SQL digest, not SQL text. Executed SQL artifacts are stored separately under the sensitive target/run/ tree.

Functions in unit tests

sqb test creates each UDF and table function the selected tests call, SQL or Python, as a TEMPORARY FUNCTION __SQB_TEST__<NAME> in the session’s current schema. The session needs a current schema and permission to create functions there; otherwise tests call the built function. See Functions in sqb test.