accelerate-data/designing-semantic-model

Fabric Semantic Model Design

Guide the design and generation of Microsoft Fabric semantic models using TMDL (Tabular Model Definition Language).

Source Detection (dbt-first)

Always start by scanning the working directory for a dbt project. Look for:

1. dbt_project.yml — confirms a dbt project exists
2. models/ directory — contains model SQL files
3. models/**/schema.yml or models/**/_schema.yml — column definitions, descriptions, tests
4. target/manifest.json — compiled metadata (if available)

If a dbt project is found, use it as the primary source. Fall back to the powerbi-remote MCP or user-provided schema only if no dbt project exists.

Standard dbt project layout

dbt_project.yml
models/
├── staging/          # stg_* models — SKIP these
│   ├── _sources.yml
│   └── stg_*.sql
├── intermediate/     # int_* models — SKIP these
│   └── int_*.sql
└── marts/            # fct_* and dim_* models — USE these
    ├── _schema.yml   # Column descriptions, tests, relationships
    ├── fct_*.sql
    └── dim_*.sql

What to include vs skip

• Include: fct_*, dim_*, mart_* — final consumption-layer models
• Skip: stg_*, int_*, base_* — transformation-layer models not meant for reporting
• Date dimension: Look for dim_date, dim_calendar, or similar — this becomes the Calendar table

TMDL Fundamentals

TMDL is a text-based, human-readable format for defining tabular semantic models. It uses indentation-based syntax (similar to YAML) and maps 1:1 to the Tabular Object Model (TOM).

Folder Structure

Every TMDL model follows this layout:

model-name/
├── database.tmdl          # Database name and compatibility level
├── model.tmdl             # Model config, culture, ref ordering
├── relationships.tmdl     # All inter-table relationships
├── expressions.tmdl       # Shared M expressions / parameters
├── tables/
│   ├── Sales.tmdl         # One file per table (columns, measures, partitions)
│   ├── Product.tmdl
│   └── Calendar.tmdl
├── roles/
│   └── RegionalManager.tmdl  # One file per RLS role
└── perspectives/
    └── ExecutiveSummary.tmdl  # One file per perspective

Key Syntax Rules

1. Indentation: Single tab per level. Three levels max: object → properties → expressions
2. Object declaration: objectType 'Object Name' (quote names with spaces/special chars using single quotes)
3. Properties: propertyName: value (colon delimiter)
4. Expressions/defaults: = expression (equals delimiter)
5. Descriptions: /// triple-slash above the object declaration
6. Boolean shortcut: Just the property name implies true (e.g., isHidden means isHidden: true)
7. Multi-line expressions: Indented one level deeper than parent properties
8. Refs in model.tmdl: ref table TableName defines collection ordering

Data Types

Valid column data types: string, int64, double, dateTime, decimal, boolean, binary

Star Schema Design Principles

1. Fact tables contain numeric measures and foreign keys. Name them as business processes: Sales, Inventory, Orders
2. Dimension tables contain descriptive attributes. Name them as business entities: Product, Customer, Date
3. One fact, many dimensions — every fact table relates to dimensions via single-column foreign keys
4. Conformed dimensions — shared dimensions (Date, Customer) should be identical across fact tables
5. Date table required — every model needs a dedicated date/calendar dimension marked as a date table
6. Avoid snowflaking — flatten dimension hierarchies into single tables unless there's a strong reason

DirectLake Configuration

For Fabric Lakehouse/Warehouse sources, use DirectLake mode:

table Sales

	partition 'Sales-Partition' = entity
		mode: directLake
		source
			entityName: sales
			schemaName: dbo
			expressionSource: DatabaseQuery

The entityName maps to the dbt model's materialized table name in the lakehouse (typically the model name, e.g., fct_sales). The expressionSource references a named expression in expressions.tmdl that defines the SQL endpoint connection.

Naming Conventions

| Element | Convention | Example | |---------|-----------|---------| | Tables | PascalCase, singular | Product, SalesOrder | | Columns | PascalCase with spaces | Product Name, Order Date | | Measures | Business-friendly, descriptive | Total Revenue, Sales Amount YTD | | Key columns | End with Key or ID, hidden | Product Key (isHidden) | | Display folders | Logical groupings | Revenue, Margins, Time Intelligence | | Roles | Descriptive business names | Regional Sales Manager |

dbt uses snake_case. Always transform to the TMDL convention above. The sourceColumn property keeps the original dbt/database column name; the TMDL column name is the user-facing display name.

Scaffolding Workflow

Step 1: Read the dbt project

1. Read dbt_project.yml to get the project name (used as the semantic model name)
2. Find all schema YAML files under models/marts/ (or equivalent mart layer)
3. For each fct_* and dim_* model, extract: model name, description, columns with names/types/descriptions, tests (unique, not_null, relationships)
4. If target/manifest.json exists, use it for compiled column types and dependency graph

Step 2: Map dbt to TMDL

• Each mart-layer dbt model → one TMDL table file
• dbt model description → TMDL table /// description
• dbt column description → TMDL column /// description
• dbt relationships tests → TMDL relationships (the model with the test is the many-side)
• dbt unique + not_null tests → confirms a column is a valid key (isKey)
• dbt column names (snake_case) → TMDL display names (PascalCase With Spaces), with sourceColumn keeping the original
• Table name prefixes (fct_, dim_) → stripped for clean business names (fct_sales → Sales)

See references/dbt-to-tmdl.md for the complete mapping reference.

Step 3: Generate TMDL files

Create the standard folder layout. Generate database.tmdl, model.tmdl, table files with DirectLake partitions pointing to the dbt model names as entityName, and relationships.tmdl from dbt relationship tests.

Step 4: Generate starter measures

Add basic measures to fact tables: row count, SUM for numeric amount/quantity columns. If a date dimension exists, suggest time intelligence measures.

Step 5: Summarize

Report: number of tables (fact vs dimension), relationships, measures, file paths created, and recommended next steps.

Additional Resources

Read these reference files for detailed guidance:

• references/tmdl-syntax.md — Complete TMDL syntax reference with examples for every object type
• references/star-schema-patterns.md — Common star schema patterns for different industries
• references/dbt-to-tmdl.md — Mapping guide from dbt models/YAML to TMDL