Multi-fact star schema: auto-aggregate to align grains before join

[hachej]

Problem

When joining two fact tables at different granularities (e.g. monthly revenue + daily hours), BSL requires manual pre-aggregation to avoid fan-out / double-counting. This is a common star schema pattern that tools like Power BI handle automatically through relationship definitions.

Example:

• financials table: year/month grain (revenue)
• hours table: year/month/day grain (hours worked)
• Goal: compute hourly_fee = total_revenue / total_hours per month

Currently, join_many causes fan-out (revenue multiplied by number of days), and join_one expects the right side to already be at the correct grain. The user must manually pre-aggregate:

# Manual pre-aggregation required today
monthly_hours = hours_model.group_by('year', 'month').aggregate('total_hours').to_untagged()
monthly_hours_tbl = con.create_table('monthly_hours', monthly_hours.execute())
monthly_hours_model = to_semantic_table(monthly_hours_tbl, name='monthly_hours')...

combined = financials_model.join_one(monthly_hours_model, on=...)

Proposal

BSL should support grain-aware joins that automatically pre-aggregate fact tables to align granularity before joining. Something like:

# Option A: explicit grain declaration on the join
combined = financials_model.join_one(
    hours_model,
    on=lambda f, h: (f.year == h.year) & (f.month == h.month),
    grain="month",  # or inferred from join keys
)

# Option B: grain metadata on the model itself
hours_model = (
    to_semantic_table(hours_tbl, name='hours')
    .with_grain('year', 'month', 'day')
    .with_dimensions(...)
    .with_measures(...)
)
# BSL knows hours is day-grain, financials is month-grain,
# and auto-aggregates hours to month before joining

Related

• Exploration/Discussion: has-one Relationships between Dimensions #64 — has-one dimension relationships / deferred join_one (post-aggregation join optimization — the inverse of this: join after aggregation for lookups vs aggregate before join for multi-fact)
• Databricks backend unsupported in xorq #216 — Databricks backend support (original user request that surfaced this pattern)

Context

From a user building BI-as-a-Service who previously used Power BI's star schema with multiple fact tables at different granularities. This is a common pattern in financial/operational reporting (e.g. monthly revenue + daily hours → hourly fee).

[pieter-factful]

I prefer option B:

• It's easier to read
• It enforces a user to think of the grain

Two questions though:

• What if one table uses 'period' and the other 'month'?
• Would this require specifying an calculation type for quantitative columns in the pre-aggregation? You could have sum() as default but avg (or min/max for timestamps) might be relevant.

I also considered specific dimension tables (no metrics). Guess this would be a major change and complicates creating measures over the combined tables.