From a TMDL export to a governed metric view
A deterministic pipeline that migrates Power BI semantic models into Unity Catalog metric views, with humans reviewing the measures the rules cannot settle.
AI Data Engineer: design and implementation
The problem
Enterprises have years of business logic locked in Power BI semantic models: measures, relationships and formatting that define how the business reads data. As the lakehouse becomes the centre of the platform, that logic stays stuck in legacy BI tooling, outside Unity Catalog governance and unreachable by Genie. Moving it by hand means an engineer reading TMDL, translating every measure, reconstructing joins and validating KPIs manually: two to three days per model, with errors surfacing only at validation time. Across a fleet of models, migration is measured in months.
What I did
- Built the TMDL parsing stage that extracts tables, measures, relationships and partition sources into one intermediate model.
- Wrote the configurable DAX-to-SQL, format-string and relationship mapping rules behind translation.
- Implemented the generation stage: metric view YAML, model summaries and review bundles for flagged measures.
- Built the deployment path that registers metric views as Unity Catalog objects via the SQL Statement Execution API.
- Owned the KPI parity validation harness that compares migrated results against the Power BI baseline.
Architecture
- Parse reads the TMDL export into a single intermediate model. Everything downstream works off that model, never off the raw files, so a parsing fix propagates everywhere at once.
- Translate applies configurable mapping rules: DAX-to-SQL for measures, format strings, and relationship reconstruction. Rules are data rather than code branches, so supporting a new DAX pattern is a config change rather than a release.
- Generate produces the metric view YAML, a model summary, and a review bundle for every measure the rules could not settle. Flagged does not mean failed; it means a human looks at exactly one thing.
- Deploy registers the metric view as a Unity Catalog object through the SQL Statement Execution API, so the result is governed from the moment it exists.
- Validate runs KPI parity checks against the Power BI baseline and produces reviewer-ready quality reports. Sign-off reads a report; it does not re-derive numbers by hand.
What went wrong first
DAX is not SQL with different spelling
Iterators like AVERAGEX and time-intelligence functions carry evaluation context that a string rewrite loses. Translation has to understand the pattern, not the text, which is why the rules operate on the parsed model, and why genuinely context-dependent measures are flagged for review instead of guessed at.
Deterministic beats clever on the production path
An LLM will happily translate any DAX you hand it, and be wrong in ways that pass a casual read. The supported path stays deterministic; bounded Mosaic AI review is an optional assist on flagged edge cases. That keeps the output reproducible, and reproducibility is what makes parity validation meaningful.
Parity is a trust problem before it is a math problem
A migrated number that differs from Power BI without an explanation kills adoption, even when the new number is more correct. The quality report shows both values and the difference, so a reviewer can tell a translation bug from a deliberate semantic change.
Results
- per-model conversion
- <2 minper-model conversionversus two to three days manually
- KPI parity target
- >95%KPI parity targeton supported star-schema models
- faster migration
- 60-80%faster migrationpublished accelerator benchmark
- automated tests passing in CI
- 156+automated tests passing in CI
The migration is not the point; the governance is. Once metric views live in Unity Catalog, access control, lineage and Genie come from the platform. The semantic layer stops being something trapped in a BI tool and becomes something the lakehouse can enforce and AI can query.