Skip to content

Cross-fact alignment (D-CONFORM)

Where: a Relationship's custom_extensions. Payload key: cardinality, enum "many_to_one" | "many_to_many". Default (absent): "many_to_one" — the ordinary foreign-key edge the join walk follows. Impact if ignored: silent — list it in requires.

Two fact tables often share dimensions — a date, a platform — without either being the many side of the other: a login row does not belong to a payment row. Strict OSI can relate them only through a shared dimension table, so a cross-fact metric like payers per active player by date has no date to group by (unconformed_dimension). many_to_many declares the pairing directly: each from_columns / to_columns pair is the same dimension spelled on each side. Grain: what one row means explains why facts are aligned rather than joined row to row.

relationships:
  - name: login_payment_conformed
    from: netbar_login
    to: netbar_payment
    from_columns: [stat_date, platform_id]
    to_columns:   [pay_date,  platform_id]
    custom_extensions:
      - vendor_name: DATUS
        data: '{"v": "1.8", "requires": ["cardinality"], "cardinality": "many_to_many"}'

How a conformed edge is queried

A conformed edge is never joined row to row — that would fan every SUM / COUNT out. Each fact aggregates alone, projects its own spelling of the paired column under the query's output name, and the branches merge on that name exactly as S-FAN-3 merges any two branches:

{"metrics": ["pay_rate"], "group_by": ["netbar_login.stat_date"]}

WITH m0 AS (SELECT netbar_payment.pay_date AS stat_date, COUNT(DISTINCT netbar_payment.player_id) AS payers
            FROM main.netbar_payment GROUP BY netbar_payment.pay_date),
     m1 AS (SELECT netbar_login.stat_date AS stat_date, COUNT(DISTINCT netbar_login.player_id) AS users
            FROM main.netbar_login GROUP BY netbar_login.stat_date)
SELECT COALESCE(m0.stat_date, m1.stat_date) AS stat_date, CAST(m0.payers AS DOUBLE) / m1.users AS pay_rate
FROM m0 FULL OUTER JOIN m1 ON m0.stat_date = m1.stat_date OR m0.stat_date IS NULL AND m1.stat_date IS NULL

What the pairing makes possible

  • Either spelling works in group_by, where_sql and time_range.dimension, for metrics on either fact — netbar_payment.pay_date groups a login-only metric too; the output column keeps the spelling the query used.
  • metric_time resolves per branch when the facts' primary time dimensions are paired: each branch filters and groups its own date.
  • A grain rolls up per branch (stat_date:month): each fact truncates its own column and the merge key is the truncated value. Facts stored at different grains may be paired; the coarser side's D-GRAIN floor applies.
  • Transitive: payment ↔ login and login ↔ register pair payment with register too — three facts, one date dimension.
  • Only the paired columns are shared. Grouping a cross-fact metric by an unpaired column is unconformed_dimension; filtering on one is no_join_path, with a hint that the filter cannot be applied to one side alone (make the filtered member a D-DERIVE filter metric instead). A conformed edge cannot be named in a relationship path (invalid_relationship_path) — there is no row on the other side to reach.
  • Listed once. list_dimensions for a metric reports a conformed dimension as one row, under the spelling on the metric's own dataset (any spelling is still a legal group_by). When the paired columns are the metric's time axis, that row is metric_time.
  • S-FAN-6 still holds: a count inside a ratio reads NULL for a date that has rows on one side only; only a bare count metric fills 0.
  • A pair may be stored differently on each side — a native DATE on one fact, a D-FORMAT %Y%m%d string on the other. A pairing says the columns are one dimension, not one encoding, so every bound the planner moves across is folded into the target column's encoding: the single time range, each tagged range's CASE gate, and the scan predicate those gates OR together. The one thing that cannot cross is a where_sql comparison — D-FORMAT refuses an encoded column there (the literal would not be in its encoding), and naming the pair's native side is refused the same way rather than silently rewritten (unsupported_filter, naming both spellings). Use time_range with that dimension instead.
  • A window metric may be grouped by either spelling, and a pair of dates is one time axis. A window over one fact asked for under the other fact's name is that fact's own series, ordered by its own column, under the name the query used. A window whose own members span two paired facts — the period-over-period of revenue - refunds — orders the merged rows by the name the query used while each branch reads its own date; it needs no metric-level time_dimension, and a time range on either spelling (or metric_time) lands on both facts. Facts whose dates are not paired have no shared axis (no_primary_time_dimension, pointing at the pairing).

Validation

Declared once, checked once: paired columns must agree on dimension.is_time and, when both declare a datatype, on the type; a dataset contributes at most one column to a conformed class; join_type is invalid on a many_to_many edge (invalid_datus_extension). Model validation still warns relationship_target_not_unique on the edge — correct under strict OSI, and the one signal a consumer that ignores the key gets.

Why ignoring the key is silent

Why silent: an engine that drops the key sees an ordinary many→one edge and joins the facts row to row. A COUNT DISTINCT compose then returns plausible numbers (every login without a same-day payment falls into the NULL bucket); a SUM across the join is refused as fan_out_risk only when the dimension sits on the other fact, and double-counts otherwise. Listing cardinality in requires makes a datus-mode engine without it fail at the version gate instead. Basic mode is that degradation by design.