Make every business metric a governed executable contract

A semantic layer defines reusable business measures, dimensions, entities, relationships, time behavior, and access rules above physical tables. Its job is not merely to rename columns: it lets dashboards, notebooks, applications, and agents ask the same business question and receive the same correctly scoped result. A metric contract includes meaning, formula, grain, eligible population, exclusions, time basis, dimensions, unit, currency, ownership, freshness, quality, access, and version.

Begin with grain. State what one row or semantic entity instance represents before defining measures or joins. Facts record events, transactions, snapshots, or accumulating processes; dimensions describe business entities and classification. A measure such as revenue is only meaningful relative to its fact grain, line-item behavior, refunds, taxes, currency, and recognition time. Joining a one-to-many table before aggregation can multiply facts and silently inflate results.

Classify measures by additivity. Fully additive measures can sum across supported dimensions; semi-additive balances may sum across accounts but not time; ratios and distinct counts are non-additive and must be recomputed from appropriate components. Store numerator and denominator where possible rather than averaging percentages. Define null, zero, negative, late, duplicate, cancelled, test, internal, and unknown-member semantics explicitly.

Model entities and relationships with declared keys, cardinality, join direction, and validity. Enforce or test key uniqueness and referential integrity; do not describe a many-to-many relationship as many-to-one because current sample data happens to be unique. Use bridge tables or scoped relationship models for legitimate many-to-many analysis. Prevent ambiguous paths and fanout, and test every supported metric-by-dimension combination—not only isolated model SQL.

Time requires a contract. Distinguish event, booking, settlement, ingestion, processing, and snapshot time; choose the business time for each metric and retain operational times for freshness. Declare timezone, calendar, fiscal periods, week start, daylight-saving behavior, incomplete periods, and point-in-time joins. Slowly changing dimensions need explicit historical semantics: current-state reporting and “as known at the event time” answer different questions.

Dimensions need governance as much as metrics. Define allowed values, hierarchy, sort, labels, unknown and not-applicable members, privacy classification, and source authority. Prevent users from grouping by a high-cardinality identifier that exposes sensitive data or creates unusable queries. Conformed dimensions allow consistent customer, product, region, and calendar slices across facts; inconsistent keys or meanings create dashboards that agree only accidentally.

Metric ownership includes change control. Give every metric a business owner and technical steward, definition, examples, lineage, tests, consumers, certification state, and deprecation path. Version breaking changes to formula, grain, population, time, or dimensions; do not silently rewrite history. Provide comparison queries, impact analysis, migration windows, and visible annotations so old and new results are not mistaken for one continuous definition.

Security belongs in the semantic and data layers, not only in dashboards. Enforce tenant, row, column, object, and metric access at a trusted query boundary, propagate caller identity safely, partition caches, and reauthorize exports and subscriptions. Derived metrics can disclose sensitive inputs, while small groups and differences can enable inference. Avoid exposing raw SQL or hidden columns through drill paths, generated queries, error messages, or metadata APIs.

Performance must preserve meaning. Pre-aggregations, materializations, aggregate awareness, and caches are valid only when their grain, filters, time zone, currency, security scope, semantic version, and freshness match the requested metric. An approximate distinct count must be labeled and bounded; a stale aggregate must not masquerade as live detail. Test rollup equivalence and invalidation under late-arriving corrections and policy changes.

Operate the layer as software and a product. Validate schemas, compile queries, test keys and accepted values, verify metric invariants, compare to trusted fixtures, run fanout and cross-model tests, lint naming and metadata, review changes, and canary important definitions. Observe query success, latency, cost, cache and aggregate use, freshness, quality incidents, definition divergence, uncertified duplicate metrics, broken consumers, and adoption by intended decision workflows. The goal is consistent trustworthy decisions, not simply a central catalog with many definitions.

Grain is the first decision and the one that most often goes wrong. Every fact table has exactly one grain — one row per order line, per shipment, per daily snapshot — and mixing grains in one table produces totals that are correct for some questions and silently double-counted for others. State the grain in a sentence before designing the table, and test it by confirming that the declared key is genuinely unique, because a grain that is documented but not enforced will drift as soon as a source changes.

Fan-out is the specific failure that follows from joining facts at different grains. Joining an order-header fact to an order-line fact multiplies the header's value across every line, so a shipping cost of 10 recorded once on a three-line order becomes 30 in any measure that sums it after the join. The symptom is a total that is a multiple of the truth and that varies with an apparently unrelated dimension, which is why it is usually found by an accountant rather than by a test. Keep facts at different grains in separate tables joined through conformed dimensions rather than to each other, and add a test that compares a header-level measure computed before and after the join.

Conformed dimensions are what let two facts be compared, and the work is agreeing the definition rather than building the table. If sales and support each maintain their own customer dimension with different keys and different notions of when a customer becomes active, no query can combine them honestly however the joins are written. Agree the key, the hierarchy, and the change behaviour once, and treat a request for a second version of a conformed dimension as a definitional disagreement to be resolved rather than a modelling task to be delivered.