accelerate-data/validating-semantic-model

Fabric Semantic Model Validator

Validate a TMDL semantic model for correctness, best practices, and common issues.

Step 1: Gather context (dbt-first)

1. Scan the working directory for a dbt project (dbt_project.yml). If found, read schema YAML files under models/marts/ to build a reference of expected tables, columns, types, descriptions, and relationships.
2. Locate the TMDL model: search for model.tmdl or database.tmdl. Read all TMDL files in the model folder.
3. If both dbt and TMDL exist, the dbt project is the source of truth for what the model should contain.

Step 2: Structural validation

Check for required files:

• database.tmdl exists and has compatibilityLevel
• model.tmdl exists and has culture
• relationships.tmdl exists (unless no relationships needed)
• At least one table file exists in tables/
• All tables referenced in model.tmdl have corresponding files

Check TMDL syntax:

• Proper indentation (tab-based)
• All object names properly quoted when containing special characters
• No duplicate object names within the same scope
• All column dataType values are valid (string, int64, double, dateTime, decimal, boolean)

Step 3: Relationship validation

• No orphan foreign key columns (columns named *Key or *ID without relationships)
• No circular relationship paths
• Exactly one active relationship between any two tables
• Cross-filter direction is single unless explicitly justified
• All relationship columns have matching data types
• Role-playing dimensions have inactive relationships properly configured

Step 4: Measure validation

• All measures have /// descriptions
• All measures have formatString
• Measures use fully qualified column references ('Table'[Column])
• Measures use unqualified measure references ([Measure])
• No CALCULATE with simple aggregation that could use direct SUM/COUNT
• Time intelligence measures reference a proper date table
• No measures with implicit CALCULATE context transition without awareness
• VAR/RETURN pattern used for measures with multiple expressions

Step 5: Column validation

• Key columns are marked isHidden
• Key columns have summarizeBy: none
• Non-numeric columns have summarizeBy: none
• All columns have sourceColumn (for import/DirectLake) or expression (for calculated)
• No columns with default auto-summarization on inappropriate types
• Description provided for business-facing columns

Step 6: Naming convention check

• Table names: PascalCase, singular (e.g., Customer not customers)
• Column names: PascalCase with spaces (e.g., Product Name)
• Measure names: descriptive, business-friendly
• No SQL-style naming (dim_, fact_, fk_, pk_) — use clean business names
• Display folders organize measures logically

Step 7: Performance checks

• Avoid calculated columns where measures would work
• No bi-directional cross-filtering without justification
• Large tables should not have unnecessary columns (reduce model size)
• Partitions configured appropriately

Step 8: RLS validation (if roles exist)

• All roles have modelPermission: read
• tablePermission filter expressions are valid DAX
• No overly permissive filters
• Role names are descriptive

Step 9: dbt Alignment (if dbt project found)

• Every fct_*/dim_* dbt model has a corresponding TMDL table
• No TMDL tables exist without a matching dbt model (drift detection)
• TMDL sourceColumn values match dbt column names
• TMDL column data types are compatible with dbt column types
• dbt relationships tests are reflected in relationships.tmdl
• dbt column descriptions are carried through to TMDL /// descriptions (warn if missing)
• DirectLake entityName matches the dbt model's materialized table name

Step 10: Report

Present results grouped by severity:

• ERROR: Must fix — model will not work correctly
• WARNING: Should fix — violates best practices, may cause issues
• INFO: Consider — improvement opportunity

For each finding: severity, category (Structure, Relationship, Measure, Column, Naming, Performance, Security), object affected, description, and recommended fix with specific TMDL code.

End with a summary count by severity and a pass/fail assessment.