Schemas

Schema metadata defines column names, types, nullability, descriptions, and column audits. It can live inline in one MODEL() header or in a reusable SCHEMA() declaration.

Inline columns

Use inline columns for metadata owned by one model:

MODEL (
  materialized view,
  description "Cleaned order records",
  columns (
    order_id (type INTEGER, nullable false, audits [not_null, unique]),
    customer_id (type INTEGER, nullable false, audits [not_null]),
    status (
      type VARCHAR,
      audits [accepted_values (values ["placed", "completed", "cancelled"])],
    ),
  ),
);

See Audits for built-in audits, custom audits, arguments, severity, and incremental run scope.

Reusable schemas

When multiple models implement the same relation shape, declare it once under schemas/. SQLBuild discovers schema files recursively and makes public names available throughout the project.

-- schemas/orders/order.sql
SCHEMA (
  name order,
  description "Canonical staged order shape",
  columns (
    order_id (type INTEGER, nullable false, audits [not_null]),
    customer_id (type INTEGER, nullable false, audits [not_null]),
    status (type VARCHAR),
  ),
);

Bind a model with model_schema:

MODEL (
  materialized view,
  schema staging,
  model_schema order,
  contract enforced,
);

schema staging selects the warehouse destination schema. model_schema order selects reusable column metadata.

The reusable description becomes the model description when the model does not declare one. A model-owned description takes precedence.

Model-local columns

A bound model may add output columns that are not part of the reusable shape:

MODEL (
  model_schema order,
  columns (
    ingestion_batch_id (type VARCHAR, nullable false),
  ),
  contract enforced,
);

Resolved schema columns retain their order and new model-local columns follow them. Use a named child schema when an extension is reusable; use inline columns for an extension owned by one model.

Model-specific column audits

Audits in a reusable schema apply to every bound model. A model can add stricter audits to an inherited column by naming that column and declaring only audits:

MODEL (
  model_schema order,
  columns (
    order_id (audits [unique]),
  ),
);

The effective order_id keeps the reusable type, nullability, description, and not_null audit, then adds unique. A model cannot remove reusable audits or override inherited metadata. An inherited-column entry containing type, nullable, or description fails compilation. Identical audit instances are deduplicated.

Inheritance

A reusable schema may extend one parent with additional columns:

SCHEMA (
  name sourced_order,
  extends order,
  columns (
    source (type VARCHAR, nullable false),
  ),
);

Inheritance may be transitive. Parent columns resolve before child columns. An inherited column cannot be redeclared or overridden in a child schema. SQLBuild rejects unknown parents, cycles, case-insensitive duplicates, and multiple parents.

Physical SQL output order is not currently enforced. Static contract analysis matches names and checks declared types and proven non-nullability. Runtime exact-contract validation matches names and types but does not validate nullability.

Contracts and planning

contract enforced treats the complete named-plus-local declaration as the exact output shape. An unspecified model follows the project’s column_contract_mode; contract none explicitly keeps the resolved columns as metadata, audit attachment, and type-enforcement inputs without activating shape validation. See Contracts.

For models bound to a reusable schema, effective column names, types, nullability, and enum members participate in model version identity. Changing those fields on a parent therefore affects models bound through descendants. Descriptions do not change model identity. Audits have their own audit-gate identities, so audit changes invalidate reusable audit results without changing the model version itself.

Limitations

Reusable schemas intentionally support a narrow ownership model:

• One optional parent, with transitive inheritance.
• Additive child and model-local output columns.
• Audit-only model augmentation of inherited columns.
• No general column overrides, multiple inheritance, composition, mixins, parameters, or generated projections.
• No physical output ordinal enforcement.