Skip to content
dbprint

Snowflake

Terminal window
$ pip install 'dbprint[snowflake]'

The extra carries the Snowflake Connector for Python and cryptography, which key-pair authentication needs. Nothing else — DDL comes from GET_DDL rather than an external binary.

Fully-qualified names are database.schema.table.

Credentials

account, user, warehouse and role are required; database, schema (which requires database), password, private_key_file and private_key_file_pwd are optional, and exactly one of password or private_key_file is supplied — both, or neither, is an error. private_key_file_pwd decrypts an encrypted key. A relative private_key_file resolves against the project root, and ~ expands. See Configuration.

Privileges

CREATE ROLE dbprint_ro;


GRANT USAGE ON WAREHOUSE profiling_wh TO ROLE dbprint_ro;
GRANT USAGE ON DATABASE arboretum TO ROLE dbprint_ro;
GRANT USAGE ON SCHEMA arboretum.seedbank TO ROLE dbprint_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA arboretum.seedbank TO ROLE dbprint_ro;
GRANT SELECT ON ALL VIEWS IN SCHEMA arboretum.seedbank TO ROLE dbprint_ro;


GRANT ROLE dbprint_ro TO USER dbprint_service;
PrivilegeOnNeeded for
USAGEthe warehouserunning any query at all
USAGEthe databasereaching anything inside it
USAGEeach schema profiledreaching anything inside it
SELECTeach table and materialized view profiledDDL and statistics
SELECTeach plain view profiledDDL only under the default read.views.mode: catalog, which issues no statement against a view; a view read in full or filter mode is queried like a table

Querying a table takes SELECT on the table together with USAGE on the database and the schema containing it — operating on an object requires at least one privilege on each of its parents. SELECT on a view is sufficient to read that view; the underlying objects do not also need it.

The warehouse grant is not optional even though most of the introspection could do without it. SHOW commands run without a warehouse, but the INFORMATION_SCHEMA views require one running and in use, and the adapter reads both.

ALL TABLES IN SCHEMA covers what exists when the grant runs. Use GRANT SELECT ON FUTURE TABLES IN SCHEMA ... to cover tables created afterwards.

What an under-privileged role does — read this one

Snowflake fails differently from most of the other engines, and the difference is the thing most likely to waste an afternoon.

Both surfaces the adapter enumerates from are filtered to what the role can see. INFORMATION_SCHEMA returns only objects the current role has been granted access to, and SHOW returns only objects the role holds at least one privilege on. A role missing SELECT therefore does not get an error — it gets an empty result. On a first run dbprint writes a print with no tables in it; against an existing print, a listing that shares none of its tables exits 4 and names the grants.

So a first run that reports 0 objects and exits cleanly is the symptom of a missing grant, not of an empty database. PostgreSQL and MySQL would have reported a permission failure or refused the connection outright; Snowflake reports success over nothing.

Check the role's grants before concluding the selectors are wrong.

The undocumented grant behind depends_on

The view-dependency read issues two statements: information_schema.tables to seed every view, then snowflake.account_usage.object_dependencies filtered to the connected database — an account-wide catalog that lags up to three hours behind a newly created view. Reading it needs a grant no other statement on this page requires:

GRANT DATABASE ROLE SNOWFLAKE.OBJECT_VIEWER TO ROLE dbprint_ro;

Issued by ACCOUNTADMIN. This is narrower than IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE and is the route Snowflake itself recommends. Without it, a role without access to that share does not fail the run: the catalog read raises, the run degrades with one warning, and every view and matview in the connection omits depends_on for that run. Do not expect a view created moments ago to resolve — the three-hour lag is Snowflake's own published figure, not a dbprint limitation.

Sampling

ConstructSAMPLE SYSTEM (p) SEED (s)
Seededyes, from the table's own name
looks_like sub-drawSAMPLE ROW (n ROWS), cannot be seeded

Snowflake's seed applies only to SYSTEM/BLOCK sampling, so that is what a fraction uses. Two restrictions come with it, and both are properties of the engine rather than choices dbprint made:

  • A seed is not supported on fixed-size sampling. The extra distinct-value draw that inferred.looks_like takes is SAMPLE ROW (n ROWS), which is fixed-size, so it cannot be seeded at all.
  • A seed is not supported on a view or a subquery. A fraction binds to a base table.

The consequence for a sampled Snowflake print: coherence between a shape claim and the rest of the profile is population-level only, never row for row. A test or a consumer asserting that inferred.sampled describes the same rows as null_count is asserting something this engine cannot deliver. PostgreSQL and duckdb are the two engines that cohere row for row; Databricks reproduces the sampled fraction but not that row-level agreement.

When dbprint writes

Only one thing makes dbprint write, and only under one condition: profile.read.tables.materialize is on by default, and it fires only for a table read in sample mode — by a fraction, or by a max_rows ceiling that resolves below the table's size, at whatever level the ceiling is set. A project that narrows nothing, which is what dbprint init scaffolds, never writes. A filter is a predicate rather than a fraction and never materializes.

Where it does fire, the drawn rows are copied once into a session-lifetime temporary table created in the profiled table's own schema, and every statistics statement for that table reads the copy. That is what makes the numbers in one file describe one set of rows, and it is why the fraction is not simply re-evaluated: two evaluations of one seeded expression are not documented to read the same rows.

No extra grant is required for it. Snowflake exempts temporary tables from the CREATE TABLE privilege, and the schema the copy lands in is one the role already holds USAGE on.

Where the write is refused anyway — a managed-access schema, or a policy that blocks it — the run does not fail the connection, but it does refuse that table. A fixed-size sample cannot be seeded on Snowflake, so an unmaterialized draw can never be trusted to agree across statements, and the table is refused rather than degraded:

table 'arboretum.seedbank.germination_trial': connection 'primary' (snowflake) could not
materialize its sample of 0.25 (<cause>), and this adapter's per-statement fallback cannot be
seeded into agreement across statements. Narrow with mode: filter instead of a sample.

Setting read.tables.materialize: false on a sample-mode table is refused the same way, before any statement runs.

Column types

TypeProfiled asWhat to know
ARRAYcompositeIt declares no element type: the elements take the one scalar type they share, or VARIANT when they mix
structured MAP(K, V)composite—
VARIANT, OBJECTjsontypes keys are what TYPEOF returns
GEOGRAPHY, GEOMETRYspatialEvery value is xy; an empty value is one with no points
VECTOR(type, N)vectordimension is N

Table kinds

An event table is profiled as a table. A temporary table is not listed.

An external table is listed with external: true (SPEC 2.2.20) and, unless a matching rule writes its read.tables.mode, described from the catalog alone. Read that way, every statement scans the stage's files on warehouse time, and a file Snowflake cannot read is skipped without an error. An Iceberg table is an ordinary table.

Namespaces

database is optional. Omitted, the session has no current database; the connection reads every database SHOW DATABASES lists for the role — which needs no extra grant and no warehouse — less SNOWFLAKE, and reads each through its own "<database>".INFORMATION_SCHEMA. A configured name is matched to the spelling Snowflake stores. With no database, an unqualified name in dbprint check --online SQL assertions has nothing to resolve against. The grants below are per database, and a database the role cannot read is skipped with a warning. View dependencies are read per database holding a selected object; one whose dependency read fails is named in a warning and its views omit depends_on for that run.

The row-count estimate

A table's size, and a sampled table's row_count, come from information_schema.tables.row_count, the number of rows Snowflake documents for the table; a sampled table publishes row_count_method: approximate.

Statement timeout

run.statement_timeout becomes the session's STATEMENT_TIMEOUT_IN_SECONDS, which covers queueing, compilation and execution of every statement, and a statement that exceeds it is cancelled by Snowflake. A lower non-zero limit set on the warehouse still wins. The vendor's ceiling is 7 days; a larger value is refused when the connection opens.

Parallelism

run.parallelism: N opens N sessions and profiles up to N tables at once, all on the connection's warehouse. A warehouse runs a limited number of statements concurrently and queues the rest; a multi-cluster warehouse may instead scale out and bill for the extra clusters.

Reference