Snowflake Integration Guide

This guide covers how to connect a Snowflake warehouse to BurnLedger, configure query templates, and set up a read-only role. The connector is implemented and registered as connector type snowflake.

Connection Configuration

BurnLedger connects to Snowflake using a standard DSN with key-pair or password authentication.

Configuration fields:

*Provide either password or private_key. The private key must be PKCS8 (raw base64-encoded DER, or base64-encoded PEM); key-pair auth switches the driver to JWT.

These are the exact JSON keys the connector reads. private_key is snake_case in both SDKs — do not write privateKey. There is no read_only key for Snowflake: privileges are always introspected (see Read-Only User Setup).

TypeScript

const system = await bl.registerSystem({
  name: "analytics-users",
  connectorType: "snowflake",
  connectionConfig: {
    account: "xy12345.us-east-1",
    username: "BURNLEDGER_RO",
    password: process.env.SNOWFLAKE_PASSWORD,
    database: "ANALYTICS",
    schema: "PUBLIC",
    warehouse: "BURNLEDGER_WH",
    role: "BURNLEDGER_READONLY",
  },
  subjectQuery: "SELECT * FROM users WHERE email = ?",
});

Python

system = bl.register_system(
    name="analytics-users",
    connector_type="snowflake",
    connection_config={
        "account": "xy12345.us-east-1",
        "username": "BURNLEDGER_RO",
        "password": os.environ["SNOWFLAKE_PASSWORD"],
        "database": "ANALYTICS",
        "schema": "PUBLIC",
        "warehouse": "BURNLEDGER_WH",
        "role": "BURNLEDGER_READONLY",
    },
    subject_query="SELECT * FROM users WHERE email = ?",
)

Query Template Format

Standard SQL with the ? positional placeholder, which is what the gosnowflake driver binds (the same placeholder as MySQL — PostgreSQL and Redshift use $1 instead). Snowflake SQL syntax is supported.

Rules, all enforced at registration:

• Must be a single read-only statement: SELECT, or WITH … SELECT. Stacked statements (;), dollar-quoted strings, FOR UPDATE/FOR SHARE locking clauses, and any write/DDL keyword (INSERT, UPDATE, DELETE, MERGE, CREATE, INTO, CALL, SET, …) are rejected — including inside a CTE.
• Must contain a WHERE clause.
• Must contain the ? placeholder. A template without it is rejected; it would match every row and could certify a false "0 records" result for the subject.
• SELECT * is allowed but produces a warning — listing explicit columns keeps hashes stable across schema changes.

SELECT * FROM users WHERE email = ?

SELECT u.id, u.email, u.name, o.order_id
FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.email = ?

Read-Only User Setup

BurnLedger verifies the credential is read-only during connection validation — without ever writing. It runs SHOW GRANTS TO USER for the connecting user and SHOW GRANTS TO ROLE for the active role, for PUBLIC (which every role inherits), for every role granted to the user, and for every role reachable from those through USAGE ON ROLE — the whole hierarchy the credential can wield — and refuses the credential if any grant in it is write-capable: INSERT, UPDATE, DELETE, TRUNCATE, MODIFY, OWNERSHIP, WRITE, ALL [PRIVILEGES], or any CREATE <object> privilege.

One write privilege is deliberately not counted. New Snowflake accounts grant Snowflake's own SNOWFLAKE_LEARNING_ROLE to PUBLIC, and that role holds CREATE SCHEMA on Snowflake's tutorial database SNOWFLAKE_LEARNING_DB. A write privilege on SNOWFLAKE_LEARNING_DB or on an object inside it is ignored, because the credential cannot reach your data with it — there is nothing to revoke on a fresh account. The same privilege on any other database or on the account is still refused, whichever role carries it (including SNOWFLAKE_LEARNING_ROLE), and the exception is off entirely if SNOWFLAKE_LEARNING_DB is the registered database.

There is no read_only assertion escape hatch for Snowflake. The credential must be able to run SHOW GRANTS on the connecting user and on every role in that hierarchy — if any of those statements fails, BurnLedger cannot prove the credential is read-only and refuses the connection (fails closed).

-- Create a dedicated warehouse (X-Small to minimize cost)
CREATE WAREHOUSE BURNLEDGER_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

-- Create a read-only role
CREATE ROLE BURNLEDGER_READONLY;

-- Grant usage on the warehouse, database, and schema
GRANT USAGE ON WAREHOUSE BURNLEDGER_WH TO ROLE BURNLEDGER_READONLY;
GRANT USAGE ON DATABASE ANALYTICS TO ROLE BURNLEDGER_READONLY;
GRANT USAGE ON SCHEMA ANALYTICS.PUBLIC TO ROLE BURNLEDGER_READONLY;

-- Grant SELECT on specific tables
GRANT SELECT ON TABLE ANALYTICS.PUBLIC.USERS TO ROLE BURNLEDGER_READONLY;
GRANT SELECT ON TABLE ANALYTICS.PUBLIC.ORDERS TO ROLE BURNLEDGER_READONLY;

-- Or grant SELECT on all tables in the schema
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS.PUBLIC TO ROLE BURNLEDGER_READONLY;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS.PUBLIC TO ROLE BURNLEDGER_READONLY;

-- Create the user
CREATE USER BURNLEDGER_RO
  PASSWORD = 'your-secure-password'
  DEFAULT_ROLE = BURNLEDGER_READONLY
  DEFAULT_WAREHOUSE = BURNLEDGER_WH;

GRANT ROLE BURNLEDGER_READONLY TO USER BURNLEDGER_RO;

Snowflake-Specific Considerations

Warehouse costs

BurnLedger queries trigger warehouse compute. Use an X-Small warehouse with auto-suspend to minimize cost. Each attestation/verification is a single query — compute time is typically seconds.

Time Travel

Snowflake retains historical data via Time Travel. BurnLedger queries the current state only. If your compliance requires proving data was removed from Time Travel, set the table's DATA_RETENTION_TIME_IN_DAYS to 0 after deletion.

Fail-safe

Snowflake's 7-day fail-safe period retains data after Time Travel expires. BurnLedger cannot observe fail-safe data. Discuss with your compliance team whether fail-safe constitutes "storage" under your applicable regulation.

Troubleshooting