RFC - Auto-generate Cube semantic layer from SQLMesh models and Metrics

[cmgoffena13]

Summary

SQLMesh already has a prototype Metrics feature and rich model metadata (grain, references, columns, environments). Cube provides a production-ready semantic layer (dimensions, measures, joins, query API, caching).

Today these are completely separate — teams maintain SQLMesh models and hand-write Cube schema. This issue proposes connecting them: use SQLMesh as the source of truth and auto-generate what Cube needs.

Why Cube

Cube is a strong fit as SQLMesh's serving-layer partner:

• Apache 2.0 — same license as SQLMesh; no proprietary lock-in for the core semantic layer and query API
• Aligns with OSI — is a launch partner of the Open Semantic Interchange (OSI) standard that was introduced by Snowflake. dbt-metricflow is also aligned to this standard.
• Headless by design — REST/SQL APIs, caching, and pre-aggregations; SQLMesh doesn't need to rebuild a BI/query engine
• Mature product — production-ready semantic layer vs SQLMesh Metrics, which remains a prototype
• Complementary roles — SQLMesh owns transformation, environments, and versioning; Cube owns metric serving and performance

Background

What SQLMesh already has

• Models — tables/views SQLMesh builds and versions
• Grain & references — how models join (used today by the metrics rewriter and table_diff)
• Metrics (prototype) — METRIC(...) definitions in metrics/*.sql that expand to SQL via sqlmesh rewrite
• Environments — dev/prod isolation with virtual views
• Metrics is documented as prototype-only and not integrated into plan/run. The core rewriter and ReferenceGraph exist but the feature was never fully developed.

What Cube provides

Cube is an Apache 2.0 semantic layer: cubes, dimensions, measures, joins, query API, and caching/pre-aggregations. It solves the serving problem Metrics was pointing at but didn't finish.

Proposal (high level)

Instead of maintaining Cube YAML/JS by hand (or as a one-off file export), SQLMesh would compile Cube schemas from:

Cube needs	SQLMesh source
Cubes (entities)	SQLMesh models
Dimensions	Model columns / types
Primary keys	grain
Joins	grain + references
Measures	METRIC(...) definitions

Delivery could be:

• Runtime — Cube loads schema from SQLMesh on demand (no generated files in repo)
• Export (optional) — sqlmesh create_cube_models for GitOps / air-gapped deploys
• Optional extra: sqlmesh[cube] for integration tooling (similar to sqlmesh[dbt], sqlmesh[web]).

Why this fits SQLMesh

• Completes the arc Metrics started without SQLMesh becoming a headless BI server
• Leverages grain/references — gives them a concrete consumer beyond the prototype rewriter
• Gives METRIC(...) definitions a production path — today they're prototype-only with nowhere to go; this connects them to a real query API
• Aligns with existing integration pattern — mirrors sqlmesh[dbt] and sqlmesh[dlt]

Open questions

• Opt-in model: global config flag, per-model flags, or both?
• How to handle models without grain/references (linting rule?)
• Runtime vs export-first for v1?
  • export seems easy to validate if this can work out
• Self-hosted Cube OSS vs Cube Cloud deployment story?
  • I could see Cube Cloud deployment being incentive for Cube engineers to help
• etc.

Possible v1 scope

• CubeConfig in config.yaml
• Schema compiler: Context → Cube YAML
• sqlmesh create_cube_models (export mode, mirrors create_external_models)
• sqlmesh cube validate — compile-check joins/measures against project metadata
• Docs under docs/integrations/cube.md

What's possible after integration

These build on the schema compiler — not required for v1.

Foundation

Capability	What it gives you
Metric dependency graph	Map each metric to the SQLMesh models it depends on — inferred from METRIC() expressions and model refs

Built on the graph

Capability	What it gives you
Plan metric impact	When models change in a plan, show which metrics are downstream
Metric diff	Compare those metrics dev vs prod before promoting ("revenue -5.3%")
CI / PR checks	Warn on broken joins, missing grain, or KPI shifts on affected metrics

SQLMesh provides environments and change tracking; Cube provides fast metric queries. Together: validate business impact of transformations, not just SQL correctness.

References

SQLMesh Metrics overview
SQLMesh grain/references
Cube data modeling
Example metrics: examples/sushi/metrics/metrics.sql