Joins overhaul, dbt semantic model ingestion

[ZmeiGorynych]

Join overhaul: query-time multi-hop resolution, INNER join support, per-measure CTE isolation

Motivation

The join system had fundamental limitations exposed by the ACME insurance benchmark (gpt-5.3-codex scored 1/11 correct). Baked-in multi-hop joins at ingestion created stale metadata when the graph changed. All joins were LEFT, causing fan-out on entity relationships that should be INNER (50-118x inflated sums on benchmark queries). Cross-model filtered measures sharing a single base query caused JOIN intersections that zeroed out results. This PR rewrites the join and SQL generation architecture to fix these issues.

What changed

1. Query-time multi-hop join resolution

Ingestion now emits only direct joins (one ModelJoin per FK). Multi-hop paths like orders ? customers ? regions are resolved at query time by walking each intermediate model's own joins. This eliminates redundant storage and keeps join metadata consistent when the graph changes.

Files: ingestion.py, enrichment.py (_collect_needed_paths, _resolve_joins)

2. LEFT/INNER join support

New JoinType enum (LEFT, INNER). dbt entity-based joins (both foreign?primary and peer) now use INNER, matching MetricFlow behavior. Inner joins are automatically mirrored: if A?B is INNER, B?A is created too.

Join synchronization is centralized in a new JoinSyncStorage decorator that wraps every storage backend, handling mirroring on save, orphan cleanup on delete, and one-time reconciliation of hand-edited YAML.

Files: enums.py, models.py, entities.py, converter.py, join_sync.py (new), base.py

3. Per-measure CTE isolation

Major SQL generation refactor. The generator now follows a multi-stage pattern:

• Stage 1: Base CTE (simple measures + dims) + per-measure CTEs for cross-model and cross-model-filtered measures
• Stage 2: Combined CTE (LEFT JOIN all CTEs on shared dims) ? expressions/transforms stacked on top

This fixes two classes of bugs: filtered measures needing different INNER JOINs no longer intersect in a single base query, and cross-model measures in arithmetic formulas (previously "not yet supported") now work.

CTE builders were also converted from f-string SQL to sqlglot AST building for dialect safety.

Files: generator.py (major rewrite), enrichment.py

4. Re-rooted subqueries for cross-model measures

Cross-model measure CTEs previously only joined source?target, so filters on tables reachable from the target's join graph were silently dropped. Now each cross-model measure gets a re-rooted EnrichedQuery where the target model is the FROM table, giving it access to its full join graph for correct filter application. Unreachable dimensions/filters are dropped gracefully (scalar CROSS JOIN).

Files: query_engine.py (_build_rerooted_enriched), enriched.py, generator.py

5. dbt entity system fixes

• Peer joins: Models sharing a primary entity (e.g., claim and claim_coverage both declaring claim_identifier) now get bidirectional joins, enabling paths like claim ? claim_coverage ? policy
• Foreign entity fan-out: Resolution now iterates ALL matching primary models, not just the first
• Metric filter qualification: Filters referencing dimensions on peer models are now qualified correctly (e.g., loss_payment.has_loss_payment)
• Deterministic resolution: get_primary_model and peer dimension selection use lexicographic ordering instead of depending on registration order
• Peer join dedup: Uses (model, local_expr, peer_expr) signature instead of model-name-only

Files: entities.py, filters.py, converter.py

6. LLM-friendliness improvements

• Auto-promote fields to dimensions: When an LLM puts a bare dimension name in fields, it's automatically moved to dimensions (with validation: no aggregation, resolves as dimension, not a measure)
• Source model prefix stripping: New SlayerQuery.strip_source_model_prefix() centralizes the removal of redundant prefixes like orders.revenue:sum when source_model=orders, handling all reference types (dims, time dims, fields, filters, order, main_time_dimension)
• Dimension aggregation: claim_identifier:count_distinct now works ? dimensions are looked up when no measure matches, with type-safety checks (rejects sum/avg on strings)
• Cross-model dimension aggregation: Formulas like policies.id:count_distinct work via a synthetic Measure wrapper
• Constant-SQL filter fix: Dimensions with literal SQL (e.g., sql="1") no longer produce broken premium.1 qualifications
• inspect_model show_sql: Now controls ALL SQL visibility (model SQL, filters, dim/measure SQL columns), reducing noise in LLM context

Files: query_engine.py, query.py, enrichment.py, generator.py, server.py

7. Runtime column type inference and measure profiling

• New get_column_types() probes measure types via the engine pipeline (properly joined SQL), supporting DuckDB, Postgres, MySQL, and SQLite with dialect-specific OID maps
• inspect_model now shows type (number/string/time/boolean) and value range (min..max or "all NULL") columns for measures, helping LLMs avoid errors like SUM(VARCHAR)

Files: client.py, query_engine.py, server.py

8. Time shift rework

• time_shift now shifts the time dimension column via SQL INTERVAL instead of computing shifted date range strings in Python. Eliminates _shift_date, _build_row_number_join, and the row-number vs calendar join branching
• change(x) and change_pct(x) are desugared at enrichment time into a hidden time_shift + arithmetic expression, simplifying the generator

Files: enrichment.py, generator.py, enriched.py

9. Additional bug fixes

• ORDER BY with custom field names: field_name_aliases map consulted by the ORDER BY resolver
• ORDER BY in computed/transform path: Uses _resolve_order_column() instead of manual f-string formatting
• Nested WITH clause: _build_combined() returns structured CTE tuples instead of assembled SQL
• CTE name collisions: Same measure with different aggregations (e.g., revenue:sum + revenue:avg) gets distinct CTE names
• CTE WHERE leaking: Filters narrowed to only conditions whose table aliases are available in each CTE
• Inline-SQL model joins: JOIN injection moved from string replacement to sqlglot AST level
• FK dedup: Uses (target, src_col, tgt_col) signature, preserving multiple FKs to the same table
• Self-referencing join mirroring: Skipped to prevent duplicates
• First/last isolated measures: Get their own ranked subquery within their CTE instead of referencing non-existent _last_rn columns from the base

Test coverage

~3,400 new test lines across 12 test files:

• test_sql_generator.py: ~2,000 new lines covering CTE isolation, re-rooted subqueries, cross-model dimension aggregation, ORDER BY resolution, first/last isolation, SQL validity checks (_assert_valid_sql validates every generated query with sqlglot)
• test_join_sync.py (new): 321 lines ? mirroring, save/delete sync, reconciliation
• test_models.py (new): 303 lines ? strip_source_model_prefix exhaustive coverage
• test_dbt_converter.py: 326 new lines ? peer joins, foreign entity, inner join mirroring
• test_dbt_filters.py: 100 new lines ? peer dimension qualification, deterministic selection
• test_sql_client.py (new): 86 lines ? type code mapping across drivers
• test_mcp_server.py: 154 new lines ? show_sql gating, measure types, inner join mirroring
• Integration tests: expanded with cross-model filter scenarios

Documentation

Updated across ingestion, joins, formulas, transforms, queries, terminology, and help topics to reflect direct FK joins at ingestion, query-time traversal, INTERVAL-based time shifting, and dimension aggregation.

---

This discussion was created from the release Joins overhaul, dbt semantic model ingestion.