semql-validate-db

Pre-deploy drift checker for semql catalogs. Runs cheap probe queries against a live database and surfaces the class of bugs the compiler can't see — missing tables, dropped columns, broken join predicates, base-predicate drift.

semql is intentionally pure (PHILOSOPHY: "the compiler has no I/O"). That keeps the compiler simple, but it also means a catalog can pass every compile-time check and still blow up at query time because upstream renamed a column. semql-validate-db is the out-of-band gate that catches it.

Use this for ongoing drift detection on a catalog you already authored. For greenfield scaffolding from a database's existing schema, see semql-introspect — it generates Cube stubs from the information schema, which is the opposite direction: introspect goes DB → catalog, validate-db goes catalog → DB.

Install

pip install semql-validate-db

The package is driver-agnostic. Bring your own DB-API 2.0 connection:

pip install psycopg              # Postgres
pip install clickhouse-connect   # ClickHouse
pip install duckdb               # DuckDB

Quick start

import duckdb
from semql import Dialect, Catalog, Cube, Dimension, Measure, TimeDimension
from semql_validate_db import validate_against_db

orders = Cube(
    name="orders",
    dialect=Dialect.DUCKDB,
    table="orders",
    alias="o",
    measures=[Measure(name="revenue", sql="{o}.amount", agg="sum")],
    dimensions=[Dimension(name="region", sql="{o}.region", type="string")],
    time_dimensions=[TimeDimension(name="created_at", sql="{o}.created_at")],
)
catalog = Catalog([orders])

conn = duckdb.connect(":memory:")
conn.execute(
    "CREATE TABLE orders (amount DOUBLE, region TEXT, created_at TIMESTAMP)"
)

errors = validate_against_db(catalog, connection=conn)
for e in errors:
    print(f"{e.code}: {e.cube}.{e.field or ''} — {e.message}")

A clean run returns an empty list. Drift (a missing column, a renamed table) yields one DbValidationError per finding so a single run gives the full picture instead of bailing on the first failure.

DbValidationError.detail retains raw driver text for trusted internal troubleshooting and can contain SQL or database details. Do not include it in ordinary CLI output or send it to external consumers.

What it catches

• missing_table — cube.table doesn't exist or the connection's role can't see it.
• missing_column — a measure / dimension / time-dimension SQL fragment references a column that no longer exists.
• base_predicate_invalid — cube.base_predicate doesn't execute.
• join_predicate_invalid — a Join.on predicate references columns that aren't there, or compares incompatible types.

(A required_filters entry that names no real dimension is now rejected at catalog construction — it can't reach this pre-deploy stage, so there's no DB-level check for it.)

What it doesn't catch

• Semantic drift (a column exists but means something different now). Schema is necessary, not sufficient.
• Cross-table referential integrity. The probes are LIMIT 0; they parse, they don't sample.
• Dialect-specific feature drift (a function got dialect-renamed). Use the compiler's snapshot tests for that.

Why LIMIT 0?

Every probe runs SELECT … LIMIT 0. The query planner type-checks identifiers and predicates but does no row work, so the cost is microseconds per probe — fine for a per-cube fan-out in CI. The trade-off is that purely runtime drift (e.g. an enum value that got dropped from a check constraint) won't surface here.

CLI

The package is library-first; a CLI lives in callers' deploy scripts where the connection / DSN / role are already known.

Status

Phase A: probe-by-fragment shape. Drift findings are accurate; performance is "fine for CI, not for runtime gates."