Library · Data Practice OS
ADR-006: Define metrics once in a semantic layer
Decision record · Proposed · Source reviewed
Status: proposed.
Context
Text-to-SQL tools (Cortex Analyst, AI/BI Genie) are only as good as the business definitions they are grounded in. Each platform has its own native semantic objects: Snowflake semantic views and Databricks Unity Catalog metric views. Power BI has its own semantic models too. If "active licences" is defined in four places, an LLM and a dashboard will eventually disagree in front of an executive.
Decision drivers
- One definition per metric, owned by the data product team
- Native AI tools on each platform must be able to use the definitions
- Definitions stay portable if either platform changes
- Definitions are reviewed and tested like code
Options considered
| Option | For | Against |
|---|---|---|
| A. Native semantic objects per platform | Best native AI integration | Duplicate definitions if both platforms are used; lock-in |
| B. Power BI semantic models as the source | Familiar to BI teams | Unavailable to SQL, APIs and platform AI tools |
| C. dbt Semantic Layer as source, native views generated or synced | One definition in git; cross-platform; works for BI and LLMs | Sync automation needed; feature parity varies |
| D. Wait for Apache Ossie (formerly Open Semantic Interchange) | Possible future standard; now an Apache incubating project | No release yet; not ready to depend on |
Decision
Option C. Metrics and dimensions are defined in the dbt Semantic Layer (MetricFlow), alongside the gold models they describe. Where a native AI tool needs native objects, Snowflake semantic views and Databricks metric views are generated from, or tested against, the dbt definitions in CI. They are never hand-edited. Power BI models consume the metrics rather than redefining them. We track Apache Ossie (formerly Open Semantic Interchange) and will move to it if it matures.
If only one platform is adopted (ADR-001: One platform by default, two only by workload), native semantic objects may become the source, but definitions still live in git and are deployed by CI.
Consequences
- Each Data product lists its metrics, synonyms and example questions (Data product canvas). These double as the evaluation set for text-to-SQL (Evaluating AI systems).
- The platform team owns the generator and drift tests (The semantic layer).
- The dbt Core vs dbt hosted decision depends on how the Semantic Layer is served. Decide it together.
Revisit when
- Apache Ossie (formerly Open Semantic Interchange) or a similar standard reaches a release with support from both platforms
- Native semantic objects gain import from a portable format