Skip to content

Data Modeling (Entities & Facts)

Provisa gives you two declarative primitives — entity and fact — that cover the building blocks every star schema and Data Vault is assembled from. Declare the spec; Provisa lowers it to exactly the materialized-view, bitemporal, and relationship definitions you would otherwise hand-write (REQ-1164). [tool-verified: modeling.py module docstring lines 11-28]

What entities and facts are

An entity is a keyed, deduplicated, optionally-historized projection of a source relation. You name it, point it at a source, declare the entity key and the attributes you want to carry, and choose a history mode. Provisa writes the view SQL and registers the MV. When history is enabled, the MV is bitemporal. [tool-verified: Entity dataclass, modeling.py lines 53-69; entity_registration function, modeling.py lines 105-120]

A fact is a join to entity keys, reduced to a declared grain, with aggregated measures. Provisa writes an aggregate-MV query (GROUP BY grain + FK columns) and registers a relationship for each declared dimension link. A fact with no measures is a pure key-set — the Data Vault link pattern. [tool-verified: Fact dataclass, modeling.py lines 91-102; fact_registration function, modeling.py lines 123-141; comment at line 130 "a measureless fact is a pure key-set (DV link)"]

Both constructs are IR. The generated definitions retarget across engines — materialized in Oracle, Databricks, or left virtual over an MPP engine — without remodeling. [tool-verified: modeling.py docstring lines 25-28]

History modes

Three modes are available on an entity [tool-verified: _HISTORY constant at modeling.py line 38, _HISTORY_MODE dict at modeling.py line 40]:

Mode Meaning Bitemporal mode
none Current-only. No history. —
scd2 Track every change. Append only changed rows (delta) keyed on the entity key. delta
snapshot Track every refresh. Append the full result set each refresh, stamped with system time. snapshot

scd2 needs an entity key to compute the delta. snapshot works on any engine but storage grows by a full copy per refresh. Pick scd2 for large, slowly-changing sources; pick snapshot when you need full history and the source can't supply a key.

Facts have no history mode — temporal coverage comes from the underlying entity history.

Measures and aggregations

Measures are declared as column:agg pairs. Supported aggregations [tool-verified: _AGGS at modeling.py line 41]:

sum  avg  min  max  count

The default aggregation is sum [tool-verified: Measure.agg default at modeling.py line 75].

Worked example: Customer entity + Sales fact

The source tables

  • raw.customers — id, name, region, tier
  • raw.orders — order_id, customer_id, amount, quantity

Register the Customer entity

  1. Open Tables and click + Model.
  2. Choose Entity (dimension).
  3. Fill the form:
  4. Name: Customer
  5. Source relation: raw.customers
  6. Domain: (your domain)
  7. Entity key: id
  8. Attributes: name, region, tier
  9. History: SCD2 (track changes — delta bitemporal)
  10. Click Create.
mutation {
  registerEntity(input: {
    name: "Customer"
    source: "raw.customers"
    domainId: "sales"
    key: ["id"]
    attributes: ["name", "region", "tier"]
    history: "scd2"
  }) {
    success
    message
  }
}

Provisa generates and registers this bitemporal MV [tool-verified: entity_registration in modeling.py lines 105-120]:

SELECT "id", "name", "region", "tier" FROM "raw"."customers"
-- registered as a bitemporal delta MV, entity key: ["id"]

Register the Sales fact

  1. Click + Model again.
  2. Choose Fact.
  3. Fill the form:
  4. Name: Sales
  5. Source relation: raw.orders
  6. Domain: (your domain)
  7. Grain: order_id
  8. Measures: amount:sum, quantity:sum
  9. Dimensions: Customer:customer_id
  10. Click Create.
mutation {
  registerFact(input: {
    name: "Sales"
    source: "raw.orders"
    domainId: "sales"
    grain: ["order_id"]
    measures: [
      { column: "amount", agg: "sum" }
      { column: "quantity", agg: "sum" }
    ]
    dimensions: [
      { entity: "Customer", via: "customer_id" }
    ]
  }) {
    success
    message
  }
}

Provisa generates and registers [tool-verified: fact_registration in modeling.py lines 123-141]:

SELECT "order_id", "customer_id",
       SUM("amount") AS "amount",
       SUM("quantity") AS "quantity"
FROM   "raw"."orders"
GROUP BY "order_id", "customer_id"

Plus one registered relationship: Sales.customer_id → Customer (cardinality: many-to-one). [tool-verified: fact_table_input in modeling_register.py lines 89-98, cardinality at line 95]

The Model form (admin UI)

The + Model button appears on the Tables page (tooltip: "Model an entity or fact (star schema / Data Vault)"). [tool-verified: tablesPage.json line 13; TablesPage.tsx lines 441-450]

A segmented control at the top of the modal switches between Entity (dimension) and Fact. [tool-verified: ModelingForm.tsx lines 102-110]

Entity fields

[tool-verified: ModelingForm.tsx lines 141-171; modelingForm.json]

Field Required Notes
Name yes The MV name in the catalog
Source relation yes Dotted relation, e.g. raw.customers
Domain yes Domain the MV belongs to
Entity key yes Comma-separated key column(s), e.g. id
Attributes no Comma-separated attribute columns, e.g. name, region, tier
History no none / scd2 / snapshot; default is none

Fact fields

[tool-verified: ModelingForm.tsx lines 172-196; modelingForm.json]

Field Required Notes
Name yes The MV name in the catalog
Source relation yes Dotted relation, e.g. raw.orders
Domain yes Domain the MV belongs to
Grain yes Comma-separated grain column(s), e.g. order_id
Measures no col:agg pairs, comma-separated, e.g. amount:sum, quantity:sum
Dimensions no Entity:fk_column pairs, comma-separated, e.g. Customer:customer_id

When agg is omitted in a measure (amount instead of amount:sum), it defaults to sum. [tool-verified: ModelingForm.tsx line 73 agg: agg || "sum"]

The GraphQL API

Both mutations live in the admin schema. [tool-verified: schema_mutation.py lines 449-472]

registerEntity

mutation RegisterEntity($input: EntityInput!) {
  registerEntity(input: $input) {
    success
    message
  }
}

EntityInput fields [tool-verified: types.py lines 449-456]:

Field Type Default Description
name String — Catalog name for the entity MV
source String — Source relation (schema.table or quoted)
domainId String — Domain id
key [String] — Entity key column(s)
attributes [String] [] Attribute columns to project
history String "none" "none" | "scd2" | "snapshot"
visibleTo [String] ["public"] Role visibility list

registerFact

mutation RegisterFact($input: FactInput!) {
  registerFact(input: $input) {
    success
    message
  }
}

FactInput fields [tool-verified: types.py lines 472-479]:

Field Type Default Description
name String — Catalog name for the fact MV
source String — Source relation
domainId String — Domain id
grain [String] — Grain column(s) for the GROUP BY
measures [MeasureInput] [] { column, agg } pairs
dimensions [DimRefInput] [] { entity, via } pairs
visibleTo [String] ["public"] Role visibility list

MeasureInput: { column: String, agg: String } — agg defaults to "sum". [tool-verified: types.py lines 460-462]

DimRefInput: { entity: String, via: String } — entity is the referenced entity name; via is the FK column on the fact source. [tool-verified: types.py lines 465-468]

On success, registerFact returns a message of the form: Fact 'Sales' registered with 1 dimension link(s). [tool-verified: schema_mutation.py line 471]

Kimball star schema and Data Vault

Neither pattern requires separate tooling. The same two primitives compose into both.

Kimball star schema

This walkthrough builds a three-dimension star. Two source tables are new:

  • raw.products — product_id, name, category, list_price [inferred: introduced for this example]
  • raw.date_spine — date_key, year, quarter, month [inferred: introduced for this example]

raw.orders also gains product_id and order_date columns here. [inferred]

Choosing SCD type

History mode is the only dial between SCD Type 1 and Type 2:

SCD type History mode Effect
Type 1 (current only) none MV rebuilt on refresh; no row history
Type 2 (versioned) scd2 Bitemporal delta MV; each change appends a new row keyed on the entity key

[tool-verified: _HISTORY_MODE at modeling.py line 40; entity_registration history branch at lines 115-119]

Use scd2 when downstream queries need to join a dimension as it existed at transaction time — a customer's tier at the moment of purchase, not their current tier. Use none for stable lookups. A date spine never changes. A product catalog where you only need the current price can rebuild on every refresh.

Grain decision

The grain is the lowest level of detail the fact answers. order_id gives one row per order, preserving the ability to count distinct orders and join to any dimension at order granularity. A coarser grain — say ["customer_id", "order_date"] — pre-aggregates across orders and discards that detail permanently. Declare the narrowest grain the business needs; coarser rollups are cheap to derive afterward.

Register the dimensions

Customer (SCD Type 2 — tier changes must be preserved):

mutation {
  registerEntity(input: {
    name: "Customer"
    source: "raw.customers"
    domainId: "sales"
    key: ["id"]
    attributes: ["name", "region", "tier"]
    history: "scd2"
  }) { success message }
}

Generates a bitemporal delta MV keyed on id [tool-verified: entity_registration modeling.py lines 105-120]:

SELECT "id", "name", "region", "tier" FROM "raw"."customers"
-- bitemporal delta MV, entity key: ["id"]

Product (SCD Type 1 — current catalog, no version history needed):

mutation {
  registerEntity(input: {
    name: "Product"
    source: "raw.products"
    domainId: "sales"
    key: ["product_id"]
    attributes: ["name", "category", "list_price"]
    history: "none"
  }) { success message }
}

Generates an ordinary MV rebuilt on refresh [tool-verified: entity_registration modeling.py lines 105-114; mv_bitemporal_mode is only added when history != "none", line 115]:

SELECT "product_id", "name", "category", "list_price" FROM "raw"."products"

DateDim (no history — a date is immutable):

mutation {
  registerEntity(input: {
    name: "DateDim"
    source: "raw.date_spine"
    domainId: "sales"
    key: ["date_key"]
    attributes: ["year", "quarter", "month"]
    history: "none"
  }) { success message }
}

Generates:

SELECT "date_key", "year", "quarter", "month" FROM "raw"."date_spine"

Register the Sales fact across three dimensions

Grain: order_id. Three dimension references — one FK column each. Both measures are additive sums.

mutation {
  registerFact(input: {
    name: "Sales"
    source: "raw.orders"
    domainId: "sales"
    grain: ["order_id"]
    measures: [
      { column: "amount",   agg: "sum" }
      { column: "quantity", agg: "sum" }
    ]
    dimensions: [
      { entity: "Customer", via: "customer_id" }
      { entity: "Product",  via: "product_id"  }
      { entity: "DateDim",  via: "order_date"  }
    ]
  }) { success message }
}

Provisa computes group_cols = dedup([grain] + [dim FKs]) = ["order_id", "customer_id", "product_id", "order_date"] and generates [tool-verified: fact_registration modeling.py lines 125-131]:

SELECT "order_id", "customer_id", "product_id", "order_date",
       SUM("amount")   AS "amount",
       SUM("quantity") AS "quantity"
FROM   "raw"."orders"
GROUP BY "order_id", "customer_id", "product_id", "order_date"

Three relationships are registered automatically [tool-verified: modeling_register.py lines 89-98, cardinality "many_to_one" at line 95]:

Relationship Cardinality
Sales.customer_id → Customer many-to-one
Sales.product_id → Product many-to-one
Sales.order_date → DateDim many-to-one

Conformed dimensions

A conformed dimension is registered once and referenced by name from any number of facts. Suppose raw.returns holds return_id, customer_id, product_id, and amount. The Returns fact reuses Customer and Product without re-registering them:

mutation {
  registerFact(input: {
    name: "Returns"
    source: "raw.returns"
    domainId: "sales"
    grain: ["return_id"]
    measures: [{ column: "amount", agg: "sum" }]
    dimensions: [
      { entity: "Customer", via: "customer_id" }
      { entity: "Product",  via: "product_id"  }
    ]
  }) { success message }
}

Both Sales and Returns point to the same Customer and Product entities. Provisa's join paths enforce that queries through either fact traverse the same dimension definition [tool-verified: fact_registration uses entity name as target_table at modeling.py lines 138-140; fact_table_input wires target_table_id from that name at modeling_register.py lines 91-93].

Data Vault

The same primitives map directly onto Data Vault vocabulary:

DV artifact Primitive History
Hub entity none — entity keys only
Satellite entity scd2 or snapshot — attribute history beside the hub key
Link fact with no measures —
Bridge / aggregate link fact with measures —

The example builds a minimal vault over raw.customers and raw.orders.

Hubs

A hub holds the entity key and nothing else. attributes: [] with history: "none" produces a deduplicated current key set; attribute history lives entirely in the satellite.

mutation {
  registerEntity(input: {
    name: "CustomerHub"
    source: "raw.customers"
    domainId: "vault"
    key: ["id"]
    attributes: []
    history: "none"
  }) { success message }
}

Generates [tool-verified: entity_registration modeling.py lines 107-108; cols = dedup([*key, *attributes]) = ["id"] when attributes=[]]:

SELECT "id" FROM "raw"."customers"

mutation {
  registerEntity(input: {
    name: "OrderHub"
    source: "raw.orders"
    domainId: "vault"
    key: ["order_id"]
    attributes: []
    history: "none"
  }) { success message }
}

Generates:

SELECT "order_id" FROM "raw"."orders"

Satellite

The satellite sits beside the hub key and carries full attribute history. Use scd2 to append only changed rows; use snapshot to stamp every full refresh.

mutation {
  registerEntity(input: {
    name: "CustomerSat"
    source: "raw.customers"
    domainId: "vault"
    key: ["id"]
    attributes: ["name", "region", "tier"]
    history: "scd2"
  }) { success message }
}

Generates [tool-verified: entity_registration modeling.py lines 115-119; _HISTORY_MODE["scd2"] = "delta" at modeling.py line 40]:

SELECT "id", "name", "region", "tier" FROM "raw"."customers"
-- bitemporal delta MV, entity key: ["id"]

CustomerSat and CustomerHub both key on id. The hub is the stable join target; the satellite provides point-in-time attribute access through the bitemporal layer.

A link records which hub keys co-occurred — keys only, no measures. Provisa omits the GROUP BY when measures is empty [tool-verified: modeling.py lines 130-131: if f.measures: view_sql += " GROUP BY ..."].

mutation {
  registerFact(input: {
    name: "OrderCustomerLink"
    source: "raw.orders"
    domainId: "vault"
    grain: ["order_id"]
    measures: []
    dimensions: [
      { entity: "CustomerHub", via: "customer_id" }
      { entity: "OrderHub",    via: "order_id"    }
    ]
  }) { success message }
}

group_cols = dedup(["order_id"] + ["customer_id", "order_id"]) = ["order_id", "customer_id"]. No measures, so no GROUP BY. Generates [tool-verified: fact_registration modeling.py lines 125-131]:

SELECT "order_id", "customer_id" FROM "raw"."orders"

Two relationships registered: OrderCustomerLink.customer_id → CustomerHub and OrderCustomerLink.order_id → OrderHub, both many-to-one [tool-verified: modeling_register.py lines 89-98].

Add measures to the link and Provisa emits the GROUP BY, producing a pre-aggregated bridge. At order_id grain with one customer per order, the result is one aggregated row per order:

mutation {
  registerFact(input: {
    name: "OrderSummary"
    source: "raw.orders"
    domainId: "vault"
    grain: ["order_id"]
    measures: [{ column: "amount", agg: "sum" }]
    dimensions: [
      { entity: "CustomerHub", via: "customer_id" }
      { entity: "OrderHub",    via: "order_id"    }
    ]
  }) { success message }
}

group_cols = dedup(["order_id"] + ["customer_id", "order_id"]) = ["order_id", "customer_id"] (the duplicate order_id from the dimension list is dropped by _dedup). Generates [tool-verified: fact_registration modeling.py lines 125-131]:

SELECT "order_id", "customer_id", SUM("amount") AS "amount"
FROM   "raw"."orders"
GROUP BY "order_id", "customer_id"

The model does not decide the methodology. Grain, conformance, SCD choice, and the hub/satellite split remain the modeler's decisions. Provisa executes them. [tool-verified: modeling.py docstring lines 25-26]

Metrics (REQ-1317, REQ-1318, REQ-1320)

A metric is a named, governed aggregate definition with no grain of its own. The grain — the dimensions the aggregate is broken out by — is bound at query time by the caller, not at definition time. That is what distinguishes a metric from a view: a view locks the grain at creation; a metric stays open until queried. [tool-verified: Metric class comment, provisa/core/models.py lines 452–455: "A named, governed aggregate definition with no grain of its own... grain is bound at query time by the requested dimension set"]

The Metric object

[tool-verified: Metric class, provisa/core/models.py lines 451–476]

Field Required Notes
name yes snake_case, e.g. net_revenue. Validated: [a-z][a-z0-9_]*
expression yes Aggregate ANSI-SQL; must include at least one aggregate function
datatype no Result type hint, e.g. number, integer
description no Human-readable business definition
ai_context no Text for AI consumers — projects to MCP tools, pg_description, GraphQL docs, and Ossie export
visible_to no Role list; defaults to ["*"] (all roles)
from_fact — Set automatically when the metric was generated from a fact measure

Column references inside the expression must be table-qualified (orders.amount, not amount). An unqualified column is a hard error at expansion time, not a warning. [tool-verified: _expression_tables, provisa/compiler/metric_expand.py lines 83–96]

The metric repository validates the expression on every write. An expression that does not parse or contains no aggregate function is rejected; it is never stored. [tool-verified: validate_expression, provisa/core/repositories/metric.py lines 34–43]

Example config entry:

metrics:
  - name: net_revenue
    expression: "SUM(orders.amount) - SUM(orders.refunds)"
    datatype: number
    description: "Order revenue after refunds"
    ai_context: "Net revenue: total order amounts minus approved refunds. Use for P&L."

Querying a metric

The compiler reserves the metrics schema. [tool-verified: METRICS_SCHEMA = "metrics", provisa/compiler/metric_expand.py line 43] Every metric is addressable as a virtual relation inside that schema. Query it like a table — the columns you select become the dimension set and the GROUP BY:

-- Scalar total (no dimension)
SELECT value FROM metrics.net_revenue;

-- Broken out by region and month
SELECT region, month, value FROM metrics.net_revenue GROUP BY region, month;

The compiler rewrites this into a real grouped aggregate over the underlying semantic tables before governance runs, so RLS and masking apply to the actual columns. [tool-verified: expand_metric_query docstring, provisa/compiler/metric_expand.py lines 263–276: "BEFORE governance, so RLS/masking apply to the real columns (REQ-1317)"]

SELECT * against a metric relation is rejected — name the dimension columns and value explicitly. [tool-verified: expand_metric_query, provisa/compiler/metric_expand.py lines 302–306]

When a metric's expression spans multiple tables, the compiler joins them through registered relationships. A dimension that is a column of a directly-referenced table resolves to that table. A dimension one relationship hop away is joined in automatically. Two hops or an ambiguous dimension is a hard error naming the offender. [tool-verified: _JoinPlan.resolve_dimension, provisa/compiler/metric_expand.py lines 190–228]

Metrics from fact specs (REQ-1320)

When you register a fact, each declared measure auto-registers a corresponding Metric object. The metric's from_fact field records the source fact table name, and the valid grouping dimensions are the entity attributes reachable over the fact's FK relationships. [tool-verified: Metric.from_fact comment, provisa/core/models.py line 466–467: "set when this metric was auto-registered from a fact spec's measure"; from_fact stored in provisa/core/repositories/metric.py line 57]

Auto-registered metrics appear on the Metrics page with a fact badge. You can edit them like any other metric. [tool-verified: MetricsPage.tsx lines 405–408: {m.fromFact && <Badge ... data-testid={metrics-from-fact-${m.name}}>...</Badge>}]

Metric-composed views (view_metrics, REQ-1318)

A view_metrics view closes a metric's grain at definition time. Declare the metric names, dimension columns, and optional filters; the compiler generates the SELECT.

[tool-verified: ViewMetricsSpec, provisa/core/models.py lines 479–492]

tables:
  - source_id: pg1
    domain_id: sales
    schema: public
    table: monthly_revenue
    view_metrics:
      metrics: [net_revenue]
      dimensions: [region, month]
      filters: ["orders.status = 'completed'"]

The compiler generates (for this example):

SELECT orders.region AS region, orders.month AS month,
       SUM(orders.amount) - SUM(orders.refunds) AS net_revenue
FROM orders
WHERE orders.status = 'completed'
GROUP BY orders.region, orders.month

view_metrics and view_sql are mutually exclusive on the same table. [tool-verified: Table model validator, provisa/core/models.py lines 614–617: if self.view_sql is not None and self.view_metrics is not None: raise ValueError(...)]

Auto-regeneration on metric change. When a metric's expression is updated, every view_metrics view that references it recompiles and the new SQL is persisted immediately. The view cannot drift from the metric definition by construction. [tool-verified: regenerate_metric_views, provisa/api/admin/_metric_views.py lines 79–117: "each dependent view_metrics spec recompiles against the UPDATED metric set and the fresh SQL is persisted"]

Inline metric() calls in free-hand view SQL. Hand-written view_sql can also reference metrics via metric('name'). The compiler replaces each call with the metric's expression and records a lineage edge. This gives free-hand views the same recompile-on-change property when they reference a metric rather than copy its formula. [tool-verified: expand_metric_calls_in_sql, provisa/compiler/metric_expand.py lines 393–429]

Note: config-path views using inline metric() calls regenerate on config reload, not on metric upsert. [tool-verified: regenerate_metric_views docstring, _metric_views.py lines 84–86: "Free-hand view_sql born from inline metric() calls carries no stored provenance, so it is not regenerated here (config-path views regenerate on config reload)"]

The Metrics admin page (REQ-1323, REQ-1324)

Open the Metrics nav item to manage governed metrics. Click a row to expand a read-only detail panel; click Edit inside it to switch to inline editing (no modal). New Metric opens an inline creation card above the table. The delete confirmation is the only modal on the page. [tool-verified: MetricsPage.tsx lines 214–216 comment: "REQ-1317: registered-metrics management page (list / create / edit / delete). REQ-1323: detail-then-edit"]

The create/edit form offers a three-picker builder for fact-sourced metrics: choose the source fact table (filtered to modelingRole=fact), a measure column, and an aggregate function (SUM, AVG, COUNT, MIN, MAX). The datatype is derived automatically: COUNT → bigint, AVG → numeric, SUM/MIN/MAX → the measure column's type. The expression textarea remains the escape hatch for arbitrary expressions. [tool-verified: deriveDatatype function, MetricsPage.tsx lines 66–70; applyBuilder, MetricsPage.tsx lines 273–285]

The IR payoff

Every registration call goes through the same path as a hand-written MV. The entity/fact spec is an intermediate representation — not a template, not a macro. The warehouse it targets is a property of the deployment, not of the model. Change the target engine and the same entity / fact declarations materialize there, because the generated SQL and bitemporal modes are engine-neutral by construction. [tool-verified: modeling.py docstring lines 25-28; modeling_register.py lines 56-66, 80-88]