Time spines¶

Time-series metrics across modules live at different grains. Validators are reported daily. Revenue is computed on a rolling 52-week window keyed on Monday-start weeks. Bridges have weekly netflows. Lending publishes weekly volumes. Gnosis App's web analytics are daily.

The time spines are the join axis that lets the planner compose these. Three reference dimensions:

Each is a tiny SELECT DISTINCT projection of the daily spine — keeps them deterministic and storage-cheap.

Why three spines, not one?¶

Could we just use the daily spine for everything? Yes for queries that aggregate daily → weekly. No for queries that report at native week grain — those would need synthetic daily expansion, which is wasteful and ambiguous (does a week's "value" repeat 7 times daily, or appear once on Monday?). Three spines keep grain semantics honest.

Project-wide week-anchor convention¶

All weekly marts use Monday as the week anchor (toStartOfWeek(date, 1) in ClickHouse, equivalent to toMonday(date)). This was retroactively enforced when a single outlier (the original fct_execution_lending_weekly) was found using Sunday-default mode and shifted in a one-line fix.

Two reasons the convention matters:

1. Same 7-day window, same anchor. A week starting Sunday is Sun May 3 → Sat May 9; a week starting Monday is Mon May 4 → Sun May 10. These are different 7-day groupings. Joining a Sunday-mode week = 2026-05-03 to a Monday-mode week = 2026-05-04 is incorrect even after shifting by one day.
2. Spine alignment. dim_time_spine_weekly.week is generated by toMonday(day). Sector marts that don't agree with this anchor will not join through the time-spine bridges.

Authoring checklist when adding a new weekly mart:

• Use toStartOfWeek(date, 1) or toMonday(date) — never bare toStartOfWeek(date).
• Make sure the WHERE clause's incomplete-week filter uses the same function: WHERE date < toMonday(today()).

The CI / registry validation does not yet auto-detect anchor drift — adding this check is on the open-improvements list.

Cross-grain composition: how the upcast works¶

A common shape: weekly metric × daily metric on a shared week dimension. Example: revenue weekly active users × Gnosis App on-chain daily activity events.

Without the planner's upcast support (cerebro-mcp PR 5), this would fail with "Dimension week is not supported for metric X." With it, the planner detects that the daily metric's root has a time column at day grain and a time-spine model exists at week grain, and synthesises:

toMonday(b1_root.date) AS week

…directly in the daily-side branch CTE.

The full pattern emitted by query_metrics:

WITH
  branch_1 AS (
    SELECT
      toMonday(b1_root.date) AS week,            -- daily → weekly upcast
      sum(coalesce(volume_usd, 0)) AS cow_volume_usd
    FROM dbt.fct_execution_cow_daily AS b1_root
    GROUP BY toMonday(b1_root.date)
  ),
  branch_2 AS (
    SELECT
      week,                                       -- native weekly
      sum(deposits_volume_weekly) AS lending_deposits_volume_weekly
    FROM dbt.fct_execution_lending_weekly AS b2_root
    GROUP BY week
  ),
  branch_3 AS (
    SELECT
      week,
      sum(users_cnt) AS revenue_active_users_weekly
    FROM dbt.api_revenue_active_users_totals_weekly AS b3_root
    GROUP BY week
  ),
  keys AS (
    SELECT week FROM branch_1
    UNION DISTINCT SELECT week FROM branch_2
    UNION DISTINCT SELECT week FROM branch_3
  )
SELECT
  keys.week,
  branch_1.cow_volume_usd,
  branch_2.lending_deposits_volume_weekly,
  branch_3.revenue_active_users_weekly
FROM keys
LEFT JOIN branch_1 ON keys.week = branch_1.week
LEFT JOIN branch_2 ON keys.week = branch_2.week
LEFT JOIN branch_3 ON keys.week = branch_3.week

The MCP planner picks this shape automatically when:

• All metrics share an allowed_dimension of week.
• The native grain of each metric is day, week, or month.
• An upcast template exists for each (day → week: toMonday(date); day → month: toStartOfMonth(date); week → month: toStartOfMonth(week)).

Downcast is intentionally unsupported — you can't synthesise daily values from weekly aggregates.

Bridges to time spines¶

Every weekly-grain analyst-facing metric should have a relationship declared to dim_time_spine_weekly in semantic/relationships/time_spines.yml. Pattern:

- name: <module>_weekly_to_time_spine
  left_model: dim_time_spine_weekly
  right_model: <the_weekly_mart>
  left_keys: [week]
  right_keys: [week]
  cardinality: one_to_one
  join_semantics: left
  via_entity: week
  preferred_bridge: true
  safe_for_dimension_enrichment: true
  aggregate_then_join_only: false
  allow_any_join: false
  quality_tier: approved

The preferred_bridge: true flag matters — find_safest_path weights edges by cost, and bridges flagged this way drop to cost 0.5 instead of the default 1.0. This makes time-spine paths win over alternative routes when the planner has a choice.

Current spine bridges¶

Auto-listed in the semantic graph. At time of writing:

• Daily spine: dim_time_spine_daily ↔ api_consensus_validators_active_daily (and api_execution_transactions_* via execution_transactions.yml).
• Weekly spine: revenue (cohorts + per-user, ×4 marts), bridges netflow, cow daily (upcast), lending weekly.
• Monthly spine: revenue (cohorts + per-user, ×4 marts).

Every new weekly / monthly mart should add itself here.

Limitations¶

• No automatic week-anchor enforcement. A new Sunday-mode mart silently breaks cross-sector composition. The convention is documented (above) and the planner-side anchor in _TIME_UPCAST_TEMPLATES is hardcoded to Monday, but nothing prevents an author from writing toStartOfWeek(date) without , 1.
• No hour / minute spines. The grain vocabulary is day / week / month. Hour-grain on-chain data (rare; mostly p2p) doesn't have a spine yet.
• No quarterly spine. Currently only the quarterly_data module has quarterly data, and it's reported via the ESG module's own conventions rather than a shared spine.

These are all addressable when there's a concrete need — none are blocking.