Skip to content

RFC - Auto-generate Ossie files from SQLMesh models and Metrics #5997

Open

Description

@cmgoffena13

Summary

SQLMesh already has a prototype Metrics feature and rich model metadata (grain, references, columns, environments). Semantic layers provide production-ready capabilities (dimensions, measures, joins, query API, caching).

Today these are completely separate — teams maintain SQLMesh models and hand-write semantic layer schemas. This issue proposes connecting them: use SQLMesh as the source of truth and auto-generate Ossie files for semantic layers to pick up.

Why Ossie

Ossie is a strong fit for SQLMesh to align to:

  • Apache 2.0 — same license as SQLMesh; no proprietary lock-in for the core semantic layer and query API
  • Long-term viability - sponsored by 60+ organizations, such as Snowflake, Cube, etc. -- dbt-metricflow is also aligned to this standard.
  • Complementary roles — SQLMesh owns transformation, environments, and versioning; Semantic layers 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 Ossie provides

Semantic layer standard that can be ingested by technologies such as Cube. It solves the serving problem Metrics was pointing at but didn't finish.

Proposal (high level)

Instead of maintaining semantic layer YAML/JS by hand (or as a one-off file export), SQLMesh would compile Ossie schemas.

Ossie Standard SQLMesh source
Semantic Model SQLMesh Project
Datasets SQLMesh models
Fields / Dimensions Model columns / types
Primary keys grain
Relationships grain + references
Metrics METRIC(...) definitions
ai_context Model/column descriptions

Delivery could be:

  • Runtime — Semantic layer adapters load schemas from SQLMesh on demand (no generated files in repo)
  • Export (optional) — sqlmesh create_ossie_models for GitOps / air-gapped deploys
  • Optional extra: sqlmesh[ossie] 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
  • etc.

Possible v1 scope

  • Schema compiler: Context → Ossie YAML
  • sqlmesh create_ossie_models (export mode, mirrors create_external_models)
  • sqlmesh ossie validate — compile-check joins/measures against project metadata

References

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

Activity

  1. changed the title [-]RFC - Auto-generate Cube semantic layer from SQLMesh models and Metrics[/-] [+]RFC - Auto-generate Ossie files from SQLMesh models and Metrics[/+] on Oct 15, 2026
  2. cmgoffena13 commented on Nov 3, 2026

    @cmgoffena13
    CollaboratorAuthor

    Recently became aware of sqlmesh-cube a pre-release pypi package that tobiko created. Only a few updates in 2024 were made. You can install it normally, but when it installed, it actually ends up being another packaged version of sqlmesh, which is sqlmesh with these changes:

    New Files

    • sqlmesh/core/cube.py
    • sqlmesh/core/openai_client.py
    • sqlmesh/core/prompts/__init__.py
    • sqlmesh/core/prompts/cube_docs.py
    • sqlmesh/core/prompts/cube_generate.txt
    • sqlmesh/core/test/cube_test.py

    Modified Files

    • sqlmesh/cli/main.py -- adds cube_generate
    • sqlmesh/core/context.py -- ads Context.cube_generate

    Need to look into them to understand the attempted approach.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    FeatureAdds new functionalityImprovementImproves existing functionality

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions