Schema Export (SQLModel Stubs)¶

Consist can capture the observed schema of tabular artifacts and export that schema as a static SQLModel class stub you can commit and edit. The export removes the mechanical work of retyping columns while keeping semantic choices in your hands.

What You Get¶

A Python 3.11 file containing one SQLModel class stub (exported as table=True).
Columns with:
a best-effort type mapping from DuckDB logical types
nullability (Optional[...] = None only when nullable)
deterministic ordering (prefers ordinal_position)
“Hints” as comments (enums/stats) when available (on by default).
Messy column names are handled: the generated attribute name is sanitized, but the original DB column name is preserved via Field(sa_column=Column("original", ...)) when needed.
Foreign keys are preserved if they were provided in a curated SQLModel schema (rendered as Field(foreign_key="table.column")).

Typical Workflow¶

Ingest tabular data into DuckDB via log_dataframe(..., schema=MySchema) or enable lightweight file profiling with profile_file_schema=True. Consist captures the table structure automatically.
Export the captured schema as a SQLModel stub (consist.cli schema export ...) and edit it to add primary keys, relationships, and normalization decisions.
Register the curated model with a Tracker to activate a hybrid view for cross-run queries.

Abstract vs Concrete Exports¶

By default, Consist exports SQLModel classes as abstract (__abstract__ = True). This keeps the stub importable immediately, because SQLAlchemy requires a primary key to map a concrete table/view, and many analysis tables don’t have an obvious primary key.

Default (recommended): abstract export, safe to import, great for view schemas.
Concrete export (advanced): export with --concrete (or abstract=False in Python) and then add a primary key before importing, otherwise SQLAlchemy will raise an error like “could not assemble any primary key columns”.

What You Still Do Manually¶

Consist does not infer semantic intent. Treat the generated file as a starting point and edit it to add:

primary keys / foreign keys
indexes / uniqueness constraints
relationships
renames / normalization decisions

If you pass a curated SQLModel schema to log_artifact(..., schema=YourModel), Consist tags the artifact with that logical schema. If the model includes foreign_key=..., those FKs are persisted as schema metadata and will be re-emitted during export. This tagging is non-strict; validation only happens in separate ingestion or validation workflows.

Prerequisites¶

Schema export uses the schema captured from a DuckDB-ingested table. In practice:

You run with a database configured (db_path=...).
You ingest tabular data into DuckDB (hot data).
Consist profiles the ingested table and stores a deduped schema referenced by artifact.meta["schema_id"].

If you already see schema_id in an artifact’s meta, you’re ready to export.

You can also capture schemas without ingestion for CSV/Parquet artifacts by enabling lightweight file profiling at log time (profile_file_schema=True, optional file_schema_sample_rows=). This writes the same schema_id pointer into artifact.meta, allowing schema export even if the original file is later missing or moved.

For wrapped file-writing steps, enable profiling at the run level (Tracker.run(...) or ScenarioContext.run(...)) or at the individual output declaration:

from consist import ArtifactSpec

result = tracker.run(
    fn=run_step,
    inputs={"raw_firms": raw_firms_csv},
    output_paths={
        "synthetic_firms": ArtifactSpec(
            path=output_dir / "synthetic_firms.csv",
            schema=SyntheticFirms,
            profile_file_schema=True,
        )
    },
    profile_file_schema=True,
)

The run-level flag profiles tabular artifacts logged through the normal run lifecycle, including named Tracker.run(inputs={...}) or ScenarioContext.run(inputs={...}) inputs and declared output_paths. A per-output ArtifactSpec(profile_file_schema=...) overrides the run default for that output. schema=... on an ArtifactSpec is also a non-strict logical tag; it does not validate the file contents. These declarations apply when the artifact is logged. If a step returns from cache, newly added ArtifactSpec schema/profile metadata is not backfilled onto the cached artifact; use cache_mode="overwrite" or change the cache version/epoch when you need to persist new metadata for an already-cached output.

If an artifact was already logged without schema profiling, you can backfill file schema metadata later (as long as the file is still accessible):

python -m consist.cli schema capture-file --artifact-key trip_table

Use --artifact-id for UUID-based selection, and --path to override path resolution when the stored URI no longer resolves directly.

Exporting from the CLI¶

Export by artifact UUID (recommended UX for now):

python -m consist.cli schema export \
  --artifact-id 00000000-0000-0000-0000-000000000000 \
  --out your_pkg/models/persons.py

Or export by schema id (hash):

python -m consist.cli schema export \
  --schema-id <schema-hash> \
  --out your_pkg/models/persons.py

Useful flags:

--class-name Persons to override the generated class name
--table-name persons to override __tablename__
--include-system-cols to include system/ingestion columns like consist_* and _dlt_*
--no-stats-comments to omit stats/enum hint comments
--concrete to export a non-abstract mapped class (you must add a primary key)

If --out is omitted, the stub is printed to stdout (so it can be piped or redirected).

Exporting from Python¶

You can generate the code (and optionally write it) from a Tracker:

from pathlib import Path
from consist import Tracker

tracker = Tracker(run_dir=".", db_path="provenance.duckdb")

code = tracker.export_schema_sqlmodel(
    artifact_id="00000000-0000-0000-0000-000000000000",
    out_path=Path("your_pkg/models/persons.py"),
    abstract=True,
)

The method returns the generated string regardless of whether you write it.

Best‑Effort Foreign Key Enforcement¶

If you want DuckDB to attempt to enforce persisted foreign keys, run:

python -m consist.cli schema apply-fks

This step is best‑effort: if a constraint can’t be added (e.g., due to missing parent rows), Consist logs a warning and continues.

Where to Put the Generated File¶

Place it anywhere importable by your project (on PYTHONPATH), for example:

your_pkg/models/
your_pkg/schemas/

If it’s a package directory, include an __init__.py so you can import your_pkg.models.persons.

Consist does not require a special directory layout; it only needs you to import the class and register it for views.

Using the Generated Model with Views¶

Consist’s hybrid views are created from your SQLModel schema. To activate a view:

1) Import the model class. 2) Register it with the Tracker (at init time or after).

Register at Tracker initialization¶

from consist import Tracker
from your_pkg.models.persons import Persons

tracker = Tracker(run_dir="./runs", db_path="./provenance.duckdb", schemas=[Persons])
VPersons = tracker.views.Persons

Register later¶

from your_pkg.models.persons import Persons

VPersons = tracker.view(Persons)  # registers + creates/refreshes the hybrid view

Querying¶

from sqlmodel import select
import consist

VPersons = tracker.views.Persons
rows = consist.run_query(select(VPersons).limit(5), tracker=tracker)

Views automatically include Consist metadata columns (e.g. consist_run_id, consist_year, consist_scenario_id) for filtering and grouping.

Column Name Rules (Important)¶

The generated stub aggressively normalizes attribute names to reduce runtime errors:

invalid characters become _
leading digits are prefixed with _
Python keywords get a trailing _
collisions become __2, __3, ...

When the attribute name differs from the real column name, the stub uses:

mass_kg: float | None = Field(default=None, sa_column=Column("Mass(kg)", ...))

This matters for views and empty-view typing: Consist now uses the original DB column name when creating typed empty views, so schemas with renamed attributes still behave correctly.

Matching Artifacts, Tables, and `tablename`¶

For views to “pick up” your data, the model’s __tablename__ should match the concept key you are querying:

for ingested (“hot”) data, this is typically the DuckDB table name used at ingest time
for file-based (“cold”) data, this is typically the Artifact.key

If you want your curated model name to differ from the ingestion key, either:

override --table-name / table_name=... during export, or
set __tablename__ = "..." manually after editing

System and Ingestion Columns¶

By default, exported stubs omit system/ingestion columns:

consist_* (Consist provenance columns)
_dlt_* (common ingestion/system columns)

Use --include-system-cols if you need a faithful “as-ingested” representation.

Wide / Sparse Tables¶

Very wide tables can cause the stored JSON profile to truncate. Consist still persists per-field rows so schema export continues to work, and emits a warning when truncation occurs.

If your upstream workflow produces sparse wide tables, consider reshaping to a long format before ingestion or as a dedicated transformation step. Schema export is designed to keep working even when the summary JSON is truncated.