Path: models allowed to connect
Cardinality: 1:1, 1:N, N:M
Keys: business key and null handling
Time: current or historical relation
Guards: row, duplicate, fanout tests
A successful join can still be wrong
A database validates syntax and types, not the difference between a customer’s current store and historical transaction store. Bad relations often run without error and silently inflate or misattribute metrics.
Lift relationships from hidden SQL into reviewable semantic contracts.
Include business time
Organizations, tiers, categories, and regions change. Joining historical facts to current dimensions rewrites history unless effective intervals are used or the current-view choice is explicit.
Document start, end, current flag, late change, and unknown-member handling.
Model many-to-many explicitly
Products and campaigns, customers and tags, or accounts and owners are often many-to-many. Direct joins create combinations; use bridges, weights, deduplication, or pre-aggregation.
State which metrics may cross a bridge and how non-additive metrics behave.
Turn contract failure into a gate
Test key uniqueness, referential integrity, before-and-after rows, fanout, and metric reconciliation. Block release when thresholds fail.
Record owner, version, dependent metrics, and approval so a failure routes to accountable work.
Accept with query paths
Test one metric by customer, product, campaign, and historical organization. Verify the allowed join and rejection of unsafe paths.
When comparing custom SQL, catalogs, and governed semantic layers, evaluate relation expression, tests, versions, impact analysis, and exit options.
Public references
Build an AI-ready data foundation
Download BuildTable or talk with us about your data modeling scenario.
Download BuildTable