Warehouse adapter — operations guide
Operational reference for users of signalforge.warehouse. Companion to
docs/manifest-loader-ops.md and the design
record in plans/super/3-bigquery-adapter.md.
v0.1 ships the BigQuery adapter only. Snowflake and Postgres are tracked
for v0.2; the public ABC (WarehouseAdapter) and the from_profile
factory are warehouse-agnostic so adding a sibling adapter is purely
additive.
Snowflake skeleton (v0.2, issue #119). from_profile now dispatches
type: snowflake to a SnowflakeAdapter skeleton — dialect() returns the
Snowflake Dialect (quote_char='"', identifier_case='upper',
supports_qualify=True), but the sampling / profiling / test-running methods
raise NotImplementedError naming epic #118 until #122–#124 land. The
connector is an optional extra — install it with
pip install "signalforge-dbt[snowflake]" (or uv pip install "signalforge-dbt[snowflake]");
the base install never pulls snowflake-connector-python.
Snowflake profile parsing (v0.2, issue #120). load_profile parses a
type: snowflake target into a DbtProfileTarget carrying account,
user, role, warehouse, database, schema, threads, and the auth
fields. Required keys are account / user / warehouse — a missing one
raises IncompleteProfileError listing every missing key. Auth methods:
v0.2 supports password, key-pair (private_key_path +
private_key_passphrase), and SSO (authenticator: externalbrowser); OAuth
(authenticator: oauth + token), inline private_key, and MFA
(username_password_mfa) are deferred and fail loud with a remediation
naming the supported set. Identifier validation: warehouse,
database, schema, and role are validated as strict SQL identifiers at
load time (they become SQL when #122 opens the connection). Known
limitation: the strict identifier grammar rejects Snowflake's legal $ in
warehouse/database/schema/role names (e.g. WH$PROD) — a documented
v0.x deferral. The account locator uses a separate permissive grammar
(accepts org-account myorg-account1 and region-suffixed xy12345.us-east-1
forms; rejects quotes, ;, whitespace, control chars).
Quick start
One-time, on a fresh machine:
gcloud auth application-default login
Application Default Credentials (ADC) is the only supported auth method in v0.1 (see §2 dbt profile resolution).
Then, from Python:
from pathlib import Path
from signalforge.warehouse import (
WarehouseAdapter,
TableRef,
load_profile,
)
profile = load_profile(Path("my_dbt_project"))
with WarehouseAdapter.from_profile(profile) as adapter:
sample = adapter.sample_rows(
TableRef(project="my-gcp-project", dataset="analytics", name="dim_users"),
n=100,
)
WarehouseAdapter.from_profile dispatches on profile.type.
profile.type == "bigquery" is fully implemented; profile.type ==
"postgres" (v0.2 stub, #53), profile.type == "snowflake" (v0.2, #119),
and profile.type == "databricks" dispatch to their adapters. The
Databricks adapter implements its sampling surface as of #224 —
sample_rows, get_row_count, materialise_sample, run_test_sql, and
column_stats — plus estimate_query_bytes via EXPLAIN COST as of #225
and run_stats_query as of #227 (so row_count_anomaly_by_period
evaluates on Databricks rather than degrading); no Databricks op inherits
the ABC's typed *NotSupportedError degrade any longer. Any
other profile.type raises UnsupportedProfileTypeError with a
remediation pointing at the roadmap entry.
A type: databricks target parses end-to-end (#222) into a
DbtProfileTarget carrying host, http_path, token (or the OAuth-M2M
auth_type: oauth + client_id + client_secret), catalog, and
schema. Required keys are auth-conditional: host + http_path always,
plus token for PAT (the default / auth_type: pat) or
client_id + client_secret for OAuth. A missing required key (incl. an
empty-string credential, e.g. an unset env_var('DATABRICKS_TOKEN', ''))
raises IncompleteProfileError; an unsupported auth_type raises
UnsupportedAuthMethodError (PAT + OAuth-M2M are supported in v0.x; the
OAuth connection is deferred — the connector uses PAT). A BigQuery-only
(location:) or Snowflake-only (account:) field on a databricks
target fails loud, and vice versa.
The cross-cutting sections below (sampling, materialised sampling, estimation, error reference) carry the BigQuery / Snowflake / Databricks detail inline; the consolidated § Databricks adapter section gathers everything an operator running SignalForge against Databricks needs in one place.
dbt profile resolution
load_profile(project_dir, target=None) resolves a profiles.yml in
this order (DEC-009):
$DBT_PROFILES_DIR/profiles.yml— user-trusted; honoured first when the env var is set.<project_dir>/profiles.yml— symlink-hardened via the same path gate the manifest loader uses (canonicalise_path); a symlink that escapes the project tree raisesProfileNotFoundErrorrather than silently falling through to the home-dir path.~/.dbt/profiles.yml— user-trusted.
ProfileNotFoundError lists every path searched in its remediation, so
"why didn't you find my profile?" answers itself from the exception
message.
Active-target resolution. Within the resolved profile, the active
output is selected as: explicit target= argument → the profile's own
target: field → ProfileTargetNotFoundError. ProfileTargetNotFoundError
inherits from ProfileNotFoundError, so a single except clause covers
both "no profile" and "wrong target" if the caller does not need to
distinguish them.
Auth-method support (v0.1, DEC-017). Only method: oauth (or unset,
which means "let dbt-bigquery default to ADC") is accepted. Every other
documented dbt-bigquery method raises UnsupportedAuthMethodError from
the Pydantic field validator, with the remediation pointing at
gcloud auth application-default login:
method value |
v0.1 behaviour |
|---|---|
oauth / unset |
accepted; uses ADC |
service-account |
UnsupportedAuthMethodError |
service-account-json |
UnsupportedAuthMethodError |
oauth-secrets |
UnsupportedAuthMethodError |
impersonate-service-account |
UnsupportedAuthMethodError |
Service-account methods land in v0.2; the v0.1 surface is intentionally narrow so the auth path has one well-tested branch.
DbtProfileTarget is a strict (extra="forbid") Pydantic v2 model.
Unknown profile keys raise ValidationError; this is a deliberate
divergence from the manifest reader's extra="ignore" posture (DEC-017),
because silently dropping an auth-config key could mean SignalForge
falls back to ADC when the user thought they had configured something
else. Forward-compat against new dbt-bigquery fields is the drift-detector
test's responsibility (tests/warehouse/test_profiles.py).
Cost defaults
The BigQuery adapter is opinionated about cost on every query.
maximum_bytes_billed = 100 MBby default (DEC-005).BigQueryAdaptertakes amax_bytes_billed=kwarg; the dbt profile'smaximum_bytes_billedfield flows throughload_profileandfrom_profileand overrides the default. Queries that exceed the cap raiseBytesBilledExceededError. The exception'slimitfield always carries the configured cap;job_idandbytes_billedare populated only when BigQuery'sBadRequestexposes them (it usually doesn't on the pre-execution rejection path) and are otherwiseNone. The error message and remediation are sufficient to act on without those fields; v0.2 may revisit by surfacing the failedQueryJobso the IDs flow through.use_query_cache=Falseon every query (DEC-015). Architectural Commitment #5 — explainable diffs — requires that the same input produce the same prune decision; cached results break that contract. v0.2 may re-enable caching behind an explicit opt-in; in v0.1 it is unconditionally off.- Per-call
timeout_ms(DEC-013 of issue #6): pass an integer to_default_job_config(stage="...", timeout_ms=...)to setQueryJobConfig.job_timeout_ms; BigQuery cancels the job server-side at expiry. Bytes-scanned through the cancellation point still bill — set conservatively. DefaultNone(no timeout). Reserved for v0.2 prune layer integration (issue #6 ships withtotal_budget_secondsenforcement only; v0.1 has no publicWarehouseAdapter.run_test_sqlkwarg for per-test timeouts). - BigQuery job labels are auto-set on every query:
signalforge_stage— the pipeline stage that issued the query. Values arewarehouse_sample(fromsample_rows),warehouse_sample_materialise(frommaterialise_sample, v0.2 — see Materialised sampling),warehouse_stats(fromcolumn_stats),warehouse_test(fromrun_test_sql), andwarehouse_session_abort(from the__exit__cleanupCALL BQ.ABORT_SESSION()query — DEC-013 of #22).signalforge_version— the package version (with.rewritten to_to satisfy BigQuery's label-character constraint).
Both are filterable in INFORMATION_SCHEMA.JOBS_BY_PROJECT for v0.2
cost analysis. Stage labels are warehouse_sample,
warehouse_sample_materialise (v0.2), warehouse_stats,
warehouse_test, and warehouse_session_abort (v0.2; one per
pipeline stage that issues a query):
SELECT job_id, total_bytes_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE labels.signalforge_stage = 'warehouse_sample'
Sampling strategy
adapter.sample_rows(table, n, partition_filter=None) returns up to
n rows from table, deterministically.
Default: hash-mod (DEC-006). Every call wraps the table in:
SELECT * FROM <quoted> AS t
WHERE MOD(ABS(FARM_FINGERPRINT(TO_JSON_STRING(t))), bucket) < 1
ORDER BY FARM_FINGERPRINT(TO_JSON_STRING(t))
LIMIT n
The trailing ORDER BY makes the LIMIT truncation deterministic when
the bucket filter retains more than n rows; without it BigQuery's
LIMIT picks an arbitrary subset and breaks the same-input →
same-output prune contract.
bucket is sized from Table.num_rows so the expected sample size lands
near n. The hash-mod approach is deterministic across runs, works on
views, materialised views, wildcard tables, and CTEs, and behaves
correctly when TABLESAMPLE does not.
The TABLESAMPLE cost-asterisk. TABLESAMPLE SYSTEM is documented in
BigQuery as the canonical sampling primitive, but it does not
proportionally reduce bytes billed on un-clustered tables — it scans the
whole table and then drops blocks. It is only cost-effective on
clustered tables or in conjunction with a partition filter. Hash-mod
has the same cost story (it scans everything too) without the
determinism downside, so it is the v0.1 default. v0.2 will add an
opt-in TABLESAMPLE strategy for clustered tables where the bytes-billed
math works out.
PartitionFilter (DEC-014). Scope a sample to a specific partition
to actually reduce bytes billed:
from datetime import date
from signalforge.warehouse import PartitionFilter
adapter.sample_rows(
table,
n=100,
partition_filter=PartitionFilter(
column="event_date",
op=">=",
value=date(2024, 1, 1),
),
)
Each adapter renders its own SQL for PartitionFilter. The typed
column/op/value triple — op is a Literal["=", ">", ">=", "<",
"<=", "!="] and column is identifier-validated at construction time —
removes a SQL-injection seam and prevents cross-warehouse SQL leaks
(DEC-018).
Fail-loud thresholds (DEC-024). Sampling refuses to silently over-spend in two cases:
| Condition | Exception |
|---|---|
Table.num_rows is None/0 and no partition filter |
UnknownTableSizeError |
Table.num_rows >= 100_000_000 and no partition filter |
SamplingRequiresPartitionFilterError |
Both carry the offending table (and num_rows, where known) and a
remediation that names the fix. Fail-loud is preferred over a guessed
bucket size because the worst case — a terabyte-scale unscoped scan —
is silent on the user's side and very loud on the bill.
column_stats access pattern
adapter.column_stats(table, column) returns a ColumnStats object
with count, distinct, nulls, min, max, and data_type. The
call MUST be inside a with adapter: block (DEC-025); calling it
outside one raises RuntimeError.
Flush semantics in v0.1. Inside an active with adapter: block,
calls to adapter.column_stats(table, col) accumulate per table. The
first call for a given table flushes every column queued for that
table in a single batched aggregate query, populating the cache;
subsequent column_stats(table, ...) calls for columns already in the
cache return without issuing another query. Columns queued after a
flush are batched into the next flush when the first uncached column is
requested.
The returned ColumnStats is a fully-populated typed value (no lazy
proxy). v0.2 may add a lazy proxy that defers the flush until a field is
read, but the v0.1 contract is "first call flushes the queued batch" —
predictable and easy to reason about at a with-block boundary.
Use the recommended pattern below to keep call sites cheap to refactor when the lazy form lands:
with WarehouseAdapter.from_profile(profile) as adapter:
refs = {col: adapter.column_stats(table, col) for col in ["a", "b", "c"]}
for col, stats in refs.items():
print(col, stats.count, stats.distinct, stats.nulls)
In v0.1 this issues three queries (one per column_stats call); v0.2
will collapse all three into a single batched query at first field
read.
Complex types (DEC-016). For BigQuery types where ordering is not
meaningful — GEOGRAPHY, JSON, BYTES, ARRAY<...>, STRUCT<...>,
RANGE<...> — min and max are None. count, distinct, and
nulls are populated for every type. The prune layer keys decisions on
data_type (the raw BigQuery type string) without re-reading the
catalog.
Materialised sampling (v0.2, issue #22)
The WarehouseAdapter ABC ships a materialise_sample method in v0.2
that pre-computes a deterministic sample into a session-scoped temp
table, so every candidate test's per-test query reads from the
narrow materialised sample rather than re-running the full-row hash
filter against the source table for every test (see
docs/prune-ops.md § Cost model
for the cost story).
ABC signature (signalforge.warehouse.base):
def materialise_sample(
self,
table: TableRef,
n: int,
*,
partition_filter: PartitionFilter | None = None,
ttl_seconds: int = 3600,
) -> TableRef: ...
The default ABC implementation raises MaterialisationNotSupportedError
with a remediation pointing at prune.sample_strategy: oneshot in
signalforge.yml. The BigQueryAdapter overrides; non-BQ adapters
inherit the default until v0.3.
BigQueryAdapter session-state pattern. The first call to
materialise_sample runs CREATE TEMP TABLE _sf_sample_<run_id> AS SELECT ...
(the CTAS itself uses the bare _sf_sample_<run_id> name — no
_SESSION. prefix) with QueryJobConfig(create_session=True, ...)
against the warehouse_sample_materialise stage label. BigQuery
assigns the session_id server-side; the adapter captures it from
job.session_info.session_id and stores it on the adapter instance
as self._active_session_id for the duration of the prune run.
Subsequent run_test_sql calls automatically attach
ConnectionProperty(key="session_id", value=self._active_session_id)
so the per-test query resolves _SESSION._sf_sample_<run_id> against
the same session. The returned TableRef carries
project=None, dataset="_SESSION", name="_sf_sample_<run_id>" —
two-part qualified_name _SESSION._sf_sample_<run_id>. project=None
is load-bearing because BigQuery rejects the three-part
<project>._SESSION.<name> form even inside the owning session.
The run_id is OUR derivation —
blake2b(table.qualified_name + signalforge_version + str(n) + canonical_json(partition_filter), digest_size=8).hexdigest()
(inputs joined with NUL separator; 16 hex chars) — so the temp-table
name is deterministic across runs and the compiled_sql_hash
reproducibility invariant on PruneEvent (DEC-005 of issue #6) is
preserved.
ttl_seconds is OUR-side hint, not a BQ knob. BigQuery sessions
have a server-enforced max lifetime (~24h regardless of activity)
plus a BQ-default idle timeout. The ttl_seconds=3600 parameter is
NOT passed to BigQuery — it's a hint to the cleanup-WARNING text
(the "auto-expire in Ns" line below). Don't go looking for a BQ SDK
call to set it; there isn't one in v0.2.
The session-state pattern mirrors column_stats's batching state
(DEC-008 / DEC-025 of issue #3): adapter-instance state scoped to a
with adapter: block; cleanup driven by __exit__.
v0.2 → v0.3 migration story for non-BQ adapters. Snowflake and
Postgres adapters in v0.2 inherit the default materialise_sample →
MaterialisationNotSupportedError raise. Operators on those
warehouses opt in to the v0.1 oneshot path via
prune.sample_strategy: oneshot in signalforge.yml. Each
non-BigQuery adapter then ships its own session-equivalent in v0.3
(Snowflake: temporary tables tied to the session; Postgres:
CREATE TEMP TABLE inside a transaction). The ABC default-raise is
the v0.2 stop-gap, not a permanent surface.
Snowflake update (#122/#124/#139): Snowflake no longer inherits the
MaterialisationNotSupportedErrordefault —materialise_sampleis implemented, and (since #139) emits a projection-subquery sample shape (SELECT * EXCLUDE (_sf_sample_hash) FROM (SELECT t.*, ABS(HASH(*)) AS _sf_sample_hash …)) so theHASH(*)row-hash sits in the SELECT projection rather than the rejectedWHERE/ORDER BYposition.materialisedsample-mode prune now works on live Snowflake. Theoneshotstrategy works too since #140 routed its sample row-count through the vendor-neutralWarehouseAdapter.get_row_countseam (it previously reached a BigQuery-only_get_client); see "Live Snowflake (v0.2) — all safety modes supported" below.
Query-bytes estimation (v0.2, issue #36)
The WarehouseAdapter ABC ships an estimate_query_bytes method in
v0.2 so the signalforge generate --estimate cost-preview flow can
estimate how many bytes a candidate query would process WITHOUT
actually scanning the source table. The BigQuery override uses
QueryJobConfig(dry_run=True) and reads job.total_bytes_processed
off the returned job; the Snowflake override (issue #130) runs
EXPLAIN USING JSON and parses GlobalStats.bytesAssigned from the
returned plan; the Databricks override (issue #225) runs EXPLAIN COST
and parses the maximum Spark CBO Statistics(sizeInBytes=...) across
plan nodes. Adapters without their own primitive inherit the ABC's
default EstimateNotSupportedError raise.
ABC signature (signalforge.warehouse.base):
def estimate_query_bytes(self, sql: str) -> int: ...
The default ABC implementation raises EstimateNotSupportedError with
the locked remediation: "Use --estimate with a BigQuery profile, or
wait for v0.3 multi-warehouse estimation support." Concrete adapters
override; v0.2 ships the BigQuery override (dry_run), the Snowflake
override (EXPLAIN USING JSON, issue #130), and the Databricks override
(EXPLAIN COST, issue #225). The Postgres stub still
inherits the default raise pending its own EXPLAIN override.
BigQuery override mechanism. A dry_run=True query asks BigQuery
to validate the SQL server-side and return the estimated bytes
processed, without committing to any actual scan or row return.
BigQuery does NOT bill bytes for a dry_run, so the production
QueryJobConfig deliberately omits maximum_bytes_billed — a cap on
something that never bills would be dead config; worse, it could
mislead a reader into thinking the dry_run was guarded against
runaway cost. The job is tagged with the warehouse_estimate_query_bytes
stage label for INFORMATION_SCHEMA.JOBS_BY_PROJECT cost attribution.
The same _sql_safety.validate_test_sql cheap-reject pass that
run_test_sql applies fires before the SDK call: a SQL with a
top-level ;, a -- comment, a /* */ block comment, or unbalanced
parens raises QuerySyntaxError and never reaches BigQuery.
Snowflake override mechanism (issue #130). The Snowflake adapter
validates the caller SQL through the same _sql_safety.validate_test_sql
pass, then prepends the literal EXPLAIN USING JSON prefix (with a single
trailing space) and runs the EXPLAIN through its connection cursor. Snowflake has no BigQuery-style
dry_run (bytes-without-billing); EXPLAIN is the closest primitive,
reporting the query planner's estimated partitions and bytes in a single
JSON cell. The override parses GlobalStats.bytesAssigned from that plan
as the int-bytes estimate. EXPLAIN is planner-only — it scans no
partitions and bills no bytes.
Planner-estimate accuracy caveat. The figure EXPLAIN USING JSON
returns is the planner's estimate, not a measured scan. It can differ
from the bytes a real query ultimately processes, and the planner's
output may vary across Snowflake releases (the same caveat that applies
to Snowflake's HASH() row-sampling expression — see
prune-engine.md). It is a cost preview, calibrated for "is this
roughly cheap or roughly expensive," not a billing guarantee.
Estimation-unavailable degrade (EstimateUnavailableError). When the
Snowflake EXPLAIN succeeds but the returned plan carries no parseable
GlobalStats.bytesAssigned — a metadata-only query, a plan-shape change
across Snowflake versions, or a malformed cell — the adapter raises
EstimateUnavailableError rather than fabricating a number (a return 0
would silently report $0 cost on a future plan-shape change). This is
distinct from EstimateNotSupportedError: the adapter supports
estimation, it just couldn't extract the figure for THIS query. The
--estimate engine catches it at the supplementary-source boundary
(issue #36 DEC-005) and renders <unavailable: EstimateUnavailableError>,
falling back to a price-only preview rather than aborting the run.
Databricks override mechanism (issue #225). Databricks has no
BigQuery-style dry_run; the closest primitive is Spark's EXPLAIN COST,
which annotates each optimized-logical-plan node with cost-based-optimizer
Statistics(sizeInBytes=...). The override validates the caller SQL
through the same _sql_safety.validate_test_sql cheap-reject pass, then
prepends the trusted literal EXPLAIN COST prefix and runs it through the
connection cursor. Unlike Snowflake's EXPLAIN USING JSON, EXPLAIN
COST returns the plan as multi-line text in a single cell, not JSON.
The pure _parse_explain_cost_bytes parser regex-extracts every
Statistics(sizeInBytes=<num> <unit>), converts the 1024-based binary
units (B / KiB / MiB / GiB / TiB / PiB / EiB) to bytes —
<num> may be an integer, decimal, or scientific notation — and takes the
maximum across plan nodes. The max is almost always the leaf table
scan, the closest analogue to BigQuery's total_bytes_processed /
Snowflake's bytesAssigned; the root reflects output size and
understates scan cost. EXPLAIN COST is planner-only — it scans no
partitions and bills no DBUs beyond planning.
Databricks no-stats 8.0 EiB sentinel → EstimateUnavailableError.
When a plan node has no CBO statistics, Spark prints
spark.sql.defaultSizeInBytes (= Long.MaxValue = 8 * 1024**6 ≈
8.0 EiB) instead of a real figure. The parser detects a maximum
at-or-above that sentinel and raises the reused EstimateUnavailableError
(no new error class — DEC-006 of #225) with a detail naming
ANALYZE TABLE <table> COMPUTE STATISTICS; it NEVER reports the
~9-exabyte figure, which would conflate "no table statistics" with "a
genuinely huge scan." A plan carrying no parseable sizeInBytes at all
(a plan-shape change across Databricks runtimes, a metadata-only query)
routes to the same EstimateUnavailableError rather than fabricating a
0. As with Snowflake, the --estimate engine catches it at the
supplementary-source boundary (issue #36 DEC-005) and renders
<unavailable: EstimateUnavailableError>.
Databricks planner-estimate accuracy caveat. Like Snowflake's
EXPLAIN, the EXPLAIN COST figure is a CBO estimate, not a measured
scan. Its accuracy depends on table-statistics freshness — Spark's CBO
reads the stats written by ANALYZE TABLE … COMPUTE STATISTICS (and the
Delta transaction-log size), which can be stale or absent. It is a cost
preview — "roughly cheap or roughly expensive" — not a billing
guarantee. Certified live (#226). The EXPLAIN COST plan-text shape was
certified for shape by #225 (maintainer-captured fixture + synthetic cases); the #226
gated estimate_live test confirms a live Databricks SQL warehouse
accepts EXPLAIN COST, returns the plan-text shape the parser reads, and holds
the single-row-result assumption (asserting a positive int). The sizeInBytes
accuracy still depends on CBO / ANALYZE TABLE stats freshness (the
planner-estimate caveat above).
v0.2 → v0.3 migration story for remaining adapters. The Postgres stub
still inherits the default estimate_query_bytes →
EstimateNotSupportedError raise until it grows its own override
(Postgres's EXPLAIN is the natural primitive). The CLI's --estimate
flow surfaces the typed error with the locked remediation so operators
see the expansion plan inline. Snowflake's --estimate path is no longer
a degrade: it returns a real EXPLAIN-based estimate (issue #130), having
graduated from the issue #123 <unavailable: EstimateNotSupportedError>
placeholder once the connection seam landed (#122). Databricks likewise
graduated to a real EXPLAIN COST estimate (issue #225); only Postgres
remains a degrade.
Session cleanup & manual recovery
Sessions opened by materialise_sample need to be torn down so
their _SESSION._sf_sample_<run_id> temp tables don't linger until
BigQuery's server-side timeout reaps them (~24h). The adapter
implements a three-layer cleanup model — explicit close on the happy
path, swallow-and-warn on cleanup failure, BQ's own session timeout
as the durable fallback (issue #22 DEC-013 / DEC-014).
Layer 1 — explicit __exit__ close (happy path). When an
operator wraps the adapter in a with block (the recommended pattern
that the CLI's cmd_generate always uses), __exit__ checks
self._active_session_id; if non-None, it issues
CALL BQ.ABORT_SESSION(); on the same session via
ConnectionProperty(key="session_id", value=self._active_session_id).
On success, the adapter emits one INFO log (session_id_hash,
ttl_remaining_seconds) and resets _active_session_id = None in a
finally clause so subsequent __exit__ calls are no-ops.
Layer 2 — swallow-and-warn (cleanup failure). If
CALL BQ.ABORT_SESSION(); itself raises (network blip, session
already revoked, quota issue), the adapter swallows the exception
and emits a single multi-line WARNING — cleanup must never block the
user's actual work, which already succeeded. The WARNING contains
the raw session_id (deliberate exception to the otherwise-strict
session-id redaction rule, see DEC-003 / DEC-014 of issue #22) and
the manual bq query command the operator can run to clean up
immediately. State is reset in finally so a second __exit__ call
is a no-op.
Layer 3 — BigQuery server-side session timeout (durable
fallback). Hard process death (SIGKILL, OOM, host failure, the
operator forgetting to use a with block in a notebook session)
cannot fire __exit__. BigQuery's own session timeout (BQ-managed,
~24h max regardless of activity) reaps the orphan automatically.
The operator pays a small cost in temp-table storage until the
timeout fires, but no human intervention is required.
Manual recovery command. When the cleanup-failure WARNING fires, the operator copy-pastes the manual command verbatim from the WARNING body. The exact form is (per DEC-014 of issue #22 — this is the text the WARNING emits, do not paraphrase):
bq query --connection_property=session_id=<raw> --use_legacy_sql=false "CALL BQ.ABORT_SESSION();"
<raw> is the same raw session_id printed in the WARNING's
Session ID: line; the manual command is the only remediation
that doesn't wait for the BQ timeout. Authorisation: BigQuery
rejects BQ.ABORT_SESSION() calls from any identity other than the
session's owner, so only the operator who started the prune run can
execute the manual command — hence the raw session_id in the WARNING
is bounded in surface (read-only to the principal who already owned
the session).
Reading the cleanup-failure WARNING. See
docs/cli-ops.md § Stderr shapes (WARNING)
for the full WARNING shape and the --quiet interaction (the
cleanup-failure WARNING is operator-actionable and is NOT suppressed
by --quiet).
Edge case: SDK returns session_info=None
If materialise_sample's first query succeeds server-side but the
BigQuery SDK returns job.session_info=None (or session_id=None),
the adapter cannot stash the id and __exit__ will not fire
BQ.ABORT_SESSION() — _active_session_id is None, so the cleanup
short-circuits to a no-op. The server-side session lives until BQ's
own timeout (~24h max). This is the SDK contract violating its own
documented behaviour and is not expected in practice; the
Spotting orphaned sessions query below catches it. If you see
materialisation jobs in INFORMATION_SCHEMA.JOBS_BY_PROJECT whose
session_info.session_id is set but no matching BQ.ABORT_SESSION
job ever ran for that session, this is the path that produced them.
Spotting orphaned sessions
When a maintainer wants to audit a project for orphan sessions
(e.g., after a known-bad release that bypassed __exit__, or as a
periodic cleanup hygiene task), the adapter's signalforge_stage
job label is the durable signal. Run this INFORMATION_SCHEMA.JOBS_BY_PROJECT
query to list materialisation jobs older than 2× the expected TTL
(default ttl_seconds=3600 → look for jobs older than 2h that may
have leaked sessions):
SELECT
job_id,
user_email,
creation_time,
session_info.session_id,
state,
TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), creation_time, MINUTE) AS age_minutes
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE labels.signalforge_stage = 'warehouse_sample_materialise'
AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
AND TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), creation_time, MINUTE) > 120
ORDER BY creation_time DESC;
Adjust region-us to your dataset's region and the
INTERVAL 24 HOUR window to your retention if INFORMATION_SCHEMA.JOBS_BY_PROJECT
is set up differently. Each row identifies one session that was
opened but whose __exit__-driven CALL BQ.ABORT_SESSION(); never
fired (or fired and failed silently in v0.2 if the WARNING was
suppressed). Operators with permission to abort each session can
reuse the manual recovery command above with the per-row
session_info.session_id.
Snowflake adapter (v0.2, epic #118)
The Snowflake seam ships across issues #119 (skeleton), #120 (profile), #121 (compiler dialect), #122 (sampling + session), #130 (EXPLAIN estimate), and #124 (test harness + ops docs). This section consolidates what an operator running SignalForge against Snowflake needs; the cross-cutting sections above (sampling, materialised sampling, estimation, error reference) carry the BigQuery + Snowflake detail inline.
Install. pip install "signalforge-dbt[snowflake]" (or uv pip install
"signalforge-dbt[snowflake]"). The base install never pulls
snowflake-connector-python; it lives only under the [snowflake] extra.
Profile keys. A type: snowflake target requires account, user,
and warehouse; database / schema / role are optional (dbt allows
them at model level / via the user's default role). Auth scope:
password, key-pair (private_key_path + optional private_key_passphrase),
and SSO (authenticator: externalbrowser). oauth /
username_password_mfa are deferred (raise UnsupportedAuthMethodError).
Known limitation: the strict identifier grammar rejects Snowflake's legal
$ in identifiers.
Dialect. SNOWFLAKE_DIALECT sets quote_char='"',
identifier_case='upper' (Snowflake folds unquoted identifiers to
upper-case, so the compiler folds-then-quotes — "CUSTOMER_ID" resolves
against conventional unquoted DDL), per-component quoting
("DB"."SCHEMA"."T"), ABS(HASH(*)) as the deterministic row-sample
hash, '{value}'::TIMESTAMP/::DATE partition-filter literals, and a
quoted "sample" CTE alias (SAMPLE is a Snowflake reserved word, so an
unquoted WITH sample AS … is a syntax error). HASH() is deterministic
only within a Snowflake release — sufficient for within-run prune
determinism, not cross-time stable (mirrors the EXPLAIN planner-estimate
caveat). Since #139 SNOWFLAKE_DIALECT also sets
sample_hash_in_projection=True + sample_hash_alias="_sf_sample_hash":
HASH(*) is rejected as a WHERE/ORDER BY predicate (002079), so the
shared render_sample_select helper computes the hash once in an inner
SELECT t.*, ABS(HASH(*)) AS _sf_sample_hash projection and references the
_sf_sample_hash alias in the outer WHERE MOD(…)/ORDER BY, with
SELECT * EXCLUDE (_sf_sample_hash) stripping the helper column so sampled
rows carry only original columns. BigQuery keeps the default
(sample_hash_in_projection=False) — its FARM_FINGERPRINT row hash is legal
inline, so its emitted bytes are unchanged.
Connection-bound session. Unlike BigQuery (which threads a server-side
session_id on every query), the Snowflake connection holds the session
— materialise_sample creates a session-scoped
CREATE TEMPORARY TABLE "<SRC DB>"."<SRC SCHEMA>"."_SF_SAMPLE_<RUN_ID>"
colocated with the source (each component fold-to-UPPER then quoted, per
_quote), and __exit__ closes the connection (reaping its temp tables).
Cost-relevant consequence: the source table must be writable —
materialised sampling against a read-only shared database (e.g.
SNOWFLAKE_SAMPLE_DATA) fails the CTAS. The materialised-sample CTAS uses the
projection-subquery shape from #139 (SELECT * EXCLUDE (_sf_sample_hash) FROM
(SELECT t.*, ABS(HASH(*)) AS _sf_sample_hash …)), so prune.scope: sample +
prune.sample_strategy: materialised works on live Snowflake (against a
writable source). The oneshot strategy also works since #140 routed its
sample row-count through the vendor-neutral WarehouseAdapter.get_row_count
seam (no CTAS, so no writable source needed) — see "Known limitations" below.
Session cleanup is fail-soft with no manual command. A Snowflake temp
table is unreachable outside its owning session, so there is no
bq-style abort command and no auto-expire in <N>s countdown — the
honest durable fallback is Snowflake's server-side idle-session reap. On a
cleanup failure the adapter swallows the error and emits one
operator-actionable WARNING quoting the raw session_id (the only path
where the raw id appears; success logs hash it). --quiet does not
suppress this WARNING.
Estimate. signalforge generate --estimate runs EXPLAIN USING JSON
and parses GlobalStats.bytesAssigned (see § Query-bytes
estimation). EXPLAIN figures are
planner estimates — a calibration signal, not a billing guarantee — and
may be absent for metadata-only queries (EstimateUnavailableError →
the engine degrades to a price-only preview).
Error taxonomy (#124). map_snowflake_exception mirrors
map_bq_exception: a connector ProgrammingError for "object does not
exist" (errno 002003) → TableNotFoundError; "invalid identifier"
(errno 000904) → ColumnNotFoundError; residual ProgrammingError →
QuerySyntaxError; ForbiddenError / auth-flavoured
DatabaseError/OperationalError → WarehouseAuthError; everything else
passes through unchanged. No BytesBilledExceededError equivalent —
Snowflake has no bytes-billed cap (cost is governed by warehouse size +
auto-suspend, see below).
Live Snowflake (v0.2) — all safety modes supported. After #139 fixed the
HASH(*)-in-predicate bug, #140 added the vendor-neutral row-count seam, and
issue #258 implemented column_stats, every safety × scope ×
sample_strategy combination is functional. The combinations certified by the
maintainer-run gated live e2e suite (opt-in — deselected from normal CI) are
safety: schema-only + prune.scope: full, or prune.scope: sample with
either prune.sample_strategy: materialised or oneshot; the aggregate-only
column_stats path is live-certified too (#258, via key-pair auth — see below):
safety: aggregate-only— supported as of #258. Profiles columns viaadapter.column_stats, now implemented onSnowflakeAdapter(parity with Databricks): a catalog pre-filter overINFORMATION_SCHEMA.COLUMNSresolves each column's declared type, then a full BigQuery-style per-table batched aggregate computescount/distinct/nulls/min/max, withMIN/MAXskipped (→None) for unorderable Snowflake types (ARRAY/OBJECT/VARIANT/GEOGRAPHY/GEOMETRY). Types that are SQL-orderable but whose connector return type is outside theColumnStats.min/maxunion (BINARY→bytearray,TIME→datetime.time) have theirmin/maxnulled on read-back rather than raising.generatewithsafety.mode: aggregate-onlynow runs on Snowflake.- Known limitation (#258): the aggregate emits
COUNT(DISTINCT <col>)for every column (mirroring the BigQuery adapter). Snowflake forbidsDISTINCTonGEOGRAPHY/GEOMETRY, so a model carrying such a column cannot be profiled viaaggregate-only— the aggregate fails with a typedWarehouseError. Usesafety: schema-onlyfor models with geospatial columns.DISTINCTonVARIANT/ARRAY/OBJECTdoes not raise — confirmed by the gated live complex-type cert (tests/warehouse/test_snowflake_columnstats_live.py), which profiles those three types and assertsmin=max=Nonewithout error against a real warehouse (run 2026-07-03 via key-pair auth). TheGEOGRAPHY/GEOMETRYCOUNT(DISTINCT)limit is by inspection of Snowflake's documented restriction, not exercised by the cert (no geospatial column in the fixture).
Fixed by #140: prune.scope: sample + prune.sample_strategy: oneshot on a
non-BigQuery adapter no longer raises at the engine seam. The sample row-count is
now fetched through the vendor-neutral WarehouseAdapter.get_row_count seam
(BigQuery wraps its cached get_table; Snowflake wraps
_get_num_rows → INFORMATION_SCHEMA.TABLES.ROW_COUNT) rather than a
BigQuery-only _get_client. Certified by the gated live
test_prune_drops_always_passes_not_null_live_oneshot_sample
(bd_1-scaffolding-tft).
Fixed by #139: the deterministic row-hash sampling SQL no longer emits
MOD(ABS(HASH(*)), n) < 1 in WHERE/ORDER BY (which Snowflake rejected with
002079). The shared render_sample_select helper now emits the
projection-subquery form (SELECT * EXCLUDE (_sf_sample_hash) FROM (SELECT t.*,
ABS(HASH(*)) AS _sf_sample_hash …)), so safety: sample and
prune.scope: sample + prune.sample_strategy: materialised work on live
Snowflake (against a writable source — materialise_sample colocates its temp
table in the source db/schema).
schema-only (redacted column names/types to the LLM) runs the full
draft → prune → grade → diff pipeline against Snowflake today; run_test_sql
(test execution), materialise_sample (materialised sampling), and
estimate_query_bytes are implemented and certified live.
Cost guidance — read before running any live Snowflake test. Snowflake
bills compute by warehouse-second, so an unbounded or forgotten run costs
real money. Set a resource monitor on your account FIRST (a hard
credit ceiling with a suspend action), use an XS warehouse, and
configure aggressive auto-suspend (e.g. 60s) so an idle warehouse
stops billing promptly. The gated live tests (below) sample with LIMIT
and target the tiny TPCH_SF1 / a small engineered table to keep scans
minimal, but the resource monitor is the load-bearing guardrail.
Offline test harness. The adapter's emitted SQL is validated offline
two ways without a live warehouse: (1) fakesnow (in-memory DuckDB
Snowflake emulator) executes the non-HASH SQL (run_test_sql
COUNT-wrapper, INFORMATION_SCHEMA.TABLES.ROW_COUNT sizing) with
rule-semantic assertions — never HASH() value-equality; (2) the
hash-mod sample-mode SQL (which fakesnow's DuckDB HASH cannot execute)
is asserted to parse under sqlglot's Snowflake dialect — the syntax
gate that caught the "sample" reserved-word bug. A hand-rolled
FakeSnowflakeConnection (expect_execute / assert_all_expectations_met)
covers session/cleanup/error-mapping behaviour. Real HASH execution +
case-folding are certified only by the gated live tests.
Databricks adapter (v0.x, epic #219)
The Databricks seam ships across issues #221 (skeleton), #222 (profile), #223 (compiler dialect), #224 (sampling + session + profiling), #225 (EXPLAIN estimate), and #226 (test harness + gated live cert + ops docs). This section consolidates what an operator running SignalForge against Databricks needs; the cross-cutting sections above (sampling, materialised sampling, estimation, error reference) carry the BigQuery / Snowflake / Databricks detail inline.
Install. pip install "signalforge-dbt[databricks]" (or uv pip install
"signalforge-dbt[databricks]"). The base install never pulls
databricks-sql-connector; it lives only under the [databricks] extra.
Profile keys. A type: databricks target requires host + http_path
always, plus the auth-conditional credential: token for PAT (the default,
or explicit auth_type: pat) or client_id + client_secret for
auth_type: oauth (OAuth-M2M). catalog (the Unity Catalog catalog —
Databricks' analogue of BigQuery project / Snowflake database) and
schema are optional. A missing required key — including an empty-string
credential, e.g. an unset env_var('DATABRICKS_TOKEN', '') that renders to
"" — raises IncompleteProfileError listing every missing key; an
unsupported auth_type raises UnsupportedAuthMethodError. PAT auth is
the only v0.x connection path — the OAuth-M2M fields are validated
coherently now, but the connector uses the PAT (token); the OAuth
connection is deferred. A BigQuery-only (location:) or Snowflake-only
(account:) field on a databricks target fails loud, and vice versa.
catalog / schema are validated as strict SQL identifiers (no length
bound, so short Unity Catalog catalogs like main / workspace pass);
host / http_path use a permissive non-SQL grammar (they never become
SQL — log-injection hygiene only).
A typical PAT profile reads its credentials from the environment so the
secret never lands in profiles.yml:
my_databricks_project:
target: dev
outputs:
dev:
type: databricks
host: "{{ env_var('DATABRICKS_SERVER_HOSTNAME') }}"
http_path: "{{ env_var('DATABRICKS_HTTP_PATH') }}"
token: "{{ env_var('DATABRICKS_TOKEN', '') }}"
catalog: workspace
schema: my_schema
The env-var contract mirrors the databricks-sql-connector SDK and the
gated live tests:
| Env var | Source (Databricks UI) |
|---|---|
DATABRICKS_SERVER_HOSTNAME |
SQL warehouse → Connection details → Server hostname (dbc-….cloud.databricks.com). |
DATABRICKS_HTTP_PATH |
SQL warehouse → Connection details → HTTP path (/sql/1.0/warehouses/…). |
DATABRICKS_TOKEN |
User Settings → Developer → Access tokens → Generate new token (a dapi… PAT). |
Dialect. DATABRICKS_DIALECT sets quote_char=''(backtick — Spark
SQL, unlike Snowflake/Postgres double-quote),identifier_case='lower'(Unity Catalog folds unquoted identifiers to **lower**-case — the *opposite*
of Snowflake's'upper', so the adapter and compiler fold-then-quote so a
conventionally-cased manifest identifier resolves against the real object
and a CREATEd temp table matches the name the compiler REFERENCEs),
per-component qualified-name quoting (``catalog.schema.table``,
becausequote_qualified_per_component=True— a single backtick-quoted
string spanning dots would read as ONE identifier literally namedcatalog.schema.table), and the masked sampling hash(xxhash64(to_json(struct())) & 9223372036854775807). The sign-bit
**mask** (& 9223372036854775807=Long.MAX_VALUE), notABS, is
load-bearing:xxhash64returns a *signed* 64-bit long and Spark'sABS(Long.MIN_VALUE)stays negative in non-ANSI mode, which would skew the
deterministicMOD(sample; clearing the sign bit is
always non-negative and uniform.xxhash64(not Spark's bare 32-bithash(
Connection-bound session + materialised sampling. Like Snowflake (and
unlike BigQuery, which threads a server-side session_id on every query),
the Databricks connection holds the session: _get_connection() lazily
opens one connection and every op runs on it. Deterministic sampling uses
the projection-subquery shape — the masked
(xxhash64(to_json(struct(*))) & 9223372036854775807) whole-row hash is
computed once in an inner projection alias (_sf_sample_hash) and the outer
WHERE/ORDER BY reference that alias, with SELECT * EXCEPT (_sf_sample_hash)
stripping the helper column. Like Snowflake (whose HASH(*) is rejected as a
predicate), this is required because Spark rejects struct(*) inside a Sort
node ([INVALID_USAGE_OF_STAR_OR_REGEX] Invalid usage of '*' in Sort) — #226's
live cert corrected the originally-assumed inline shape. Table sizing uses
SELECT COUNT(*) (metadata-cheap on Delta);
DESCRIBE DETAIL is not used because it carries no reliable numRows
(the figure lives in the Delta statistics map, populated only after
ANALYZE TABLE COMPUTE STATISTICS and commonly NULL/stale). As with every
adapter, a table ≥ 100M rows requires a partition_filter or sampling fails
loud (SamplingRequiresPartitionFilterError / UnknownTableSizeError).
materialise_sample runs a qualified
CREATE OR REPLACE TABLE <catalog>.<schema>._sf_sample_<run_id> AS <sample
body> colocated with the source (the table lives in the source
catalog/schema, fold-then-quoted per-component, so the prune compiler's
REFERENCE matches the adapter's CREATE); the connection is pinned so a
follow-up run_test_sql reaches it. #226's live cert found Databricks rejects
a qualified temp name ([TEMP_TABLE_CREATION_REQUIRES_SINGLE_PART_NAME]) and
a TableRef cannot express a bare single-part name, so a real table is
used — it does NOT auto-reap with the session and is explicitly
DROP TABLE IF EXISTS-ed at session cleanup (see below). The run_id reuses
the shared _compute_run_id recipe, so the table name is byte-identical to the
BigQuery / Snowflake adapters' for the same (table, n, partition_filter)
tuple. Both prune.sample_strategy values work: materialised (the
CREATE OR REPLACE TABLE — needs a writable source catalog, e.g. the
Free-Edition workspace) and oneshot (per-test hash-mod via the
vendor-neutral get_row_count seam — no CTAS, so no writable source needed).
Session cleanup is fail-soft, per-table. At __exit__ the adapter first
drops each materialised-sample table (DROP TABLE IF EXISTS <table>) on the
session connection, then closes the connection. Because the materialised
tables are real CREATE OR REPLACE TABLEs (not session-local temps), a
manual command does exist: a per-table drop failure is swallowed and emits
one operator-actionable WARNING naming the exact DROP TABLE IF EXISTS
<backtick-quoted-name> to run. A separate connection-close failure is likewise
swallowed with one WARNING quoting the raw session_id — the session itself is
reaped server-side when the SQL warehouse drops the idle connection (no
auto-expire in <N>s countdown; the reap is not locally computable). The raw
session_id appears only in these failure WARNINGs (success logs hash it).
--quiet does not suppress them.
Concurrency caveat (v0.x known limitation). The materialised-sample table
name is the shared deterministic _compute_run_id recipe ((table, n,
partition_filter) → byte-stable name, the compiled_sql audit-reproducibility
invariant). Because a Databricks materialised sample is a real
globally-visible table (not session-isolated like BigQuery's _SESSION dataset
or Snowflake's CREATE TEMPORARY TABLE), two concurrent SignalForge runs
against the same (table, n, partition_filter) on the same catalog collide on
it — one run's cleanup DROP can remove the other's sample mid-prune. The
failure is safe: the affected run_test_sql hits table-not-found and routes
to the conservative kept-without-evidence degrade (no corruption — the
deterministic SELECT yields identical rows either way; no crash). A per-session
suffix would avoid the collision but break the audit-reproducibility invariant,
so it is deliberately not applied. Operators running concurrent Databricks
prunes against the same model should serialise them or vary prune.sample_size.
Estimate. signalforge generate --estimate runs EXPLAIN COST <sql>
and parses the maximum Spark CBO Statistics(sizeInBytes=...) across plan
nodes (see § Query-bytes estimation
for the full mechanism). Unlike Snowflake's EXPLAIN USING JSON, EXPLAIN
COST returns the plan as multi-line text. When a plan node has no CBO
statistics, Spark prints spark.sql.defaultSizeInBytes (Long.MaxValue ≈
8.0 EiB); the parser detects a maximum at-or-above that sentinel and
raises the reused EstimateUnavailableError (naming `ANALYZE TABLE