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 ROLE IDENTIFIER(CURRENT_ROLE()) and SHOW GRANTS TO USER IDENTIFIER(CURRENT_USER()) and refuses the credential if any grant is write-capable: INSERT, UPDATE, DELETE, TRUNCATE, MODIFY, OWNERSHIP, WRITE, ALL [PRIVILEGES], or any CREATE <object> privilege.

There is no read_only assertion escape hatch for Snowflake. The role must be able to run SHOW GRANTS on itself and on the connecting user — if those statements fail, 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