Power BI model authoring standards

These standards bind every Power BI semantic model shipped from a Causeway workspace. They are not recommendations. Exceptions require a documented waiver in the PR description.

1. Sources

Connector: native Databricks Power Query connector. No Databricks.Query custom SQL.
Driver: ADBC (Implementation="2.0"). ODBC is permitted for legacy reports only.
Warehouse: Serverless SQL warehouse sized per warehouse reference. Never a team-specific warehouse.
Auth: service principal with M2M OAuth for service-side refresh. PATs banned.

Danger

Databricks.Query is the first thing seasoned SQL developers reach for and the first thing to fail in DirectQuery. Custom SQL belongs in a Databricks view or dbt model that the connector reads like any other table. Enforce at review; Databricks.Query in a new model is a reject.

2. Model shape

Star schema. Facts in the middle, dimensions radiating out.
One relationship per fact-to-dim edge. No bridge tables unless genuinely many-to-many.
Wide dimensions, narrow facts. Every describable attribute lives on the dim. Facts hold keys, measures, and a date.
Conformed dimensions. dim_date, dim_customer, dim_product are shared across facts. One dim_date per model, always.
Surrogate keys, integer-typed. VertiPaq compresses integers aggressively; joins cost less.
No calculated columns on facts. Push derived columns upstream to dbt or Databricks views.

If your Databricks gold layer is a proper star, model authoring becomes almost mechanical. If it is not, fix the marts before writing DAX.

3. Storage mode

Follow the decision tree from storage modes. Defaults:

Artifact	Mode
Small static tables	Import
Dimensions	Import (default) or Dual (mixed-mode models)
Facts in a model under capacity limits	Import
Facts too large to import	DirectQuery with dims in Dual
Facts with the Fabric stack	Direct Lake

Any deviation from these defaults requires justification in the PR.

4. Naming

Object	Pattern	Example
Fact table	`fct_<grain>`	`fct_orders`
Dimension	`dim_<entity>`	`dim_customers`
Aggregate	`agg_<metric>`	`agg_revenue_daily`
Bridge	`bridge_<a>_<b>`	`bridge_customer_segment`
Column	snake_case	`customer_id`, `placed_at`
Measure	Title Case with spaces	`Total Revenue`, `Revenue YoY`
Hidden helper measure	`_<Name>`	`_Prior Period Revenue`
Calculation group	Title Case	`Time Intelligence`
Role	`<role-name>`	`analyst`, `steward`

Measures are user-facing and named in Title Case. Columns are storage-facing and named in snake_case. Do not mix.

5. Measures

Authoring rules (see DAX reference for details):

Measures, not calculated columns. Exceptions require a waiver.
Variables for anything reused. Multiple calls to the same expression is a review reject.
CALCULATE + filter, not SUMX(FILTER(...)).
DIVIDE(), not /.
Single-direction relationships. Bidirectional only via CROSSFILTER() inside a specific measure.
Every measure has a description on Certified models.
Every measure has an explicit FormatString. Do not rely on the default.

measure 'Revenue YoY' =
    VAR Current = [Total Revenue]
    VAR Prior   = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('dim_date'[date]))
    RETURN
        DIVIDE(Current - Prior, Prior)
    description: "Year-over-year change in Total Revenue. Blank when prior period is zero."
    formatString: "0.00%;-0.00%;0%"
    displayFolder: "Revenue"

6. Semantic model topology

One shared semantic model per business domain. Domains: sales, finance, product, ops, etc.
Published to a gold-tier workspace. workspace-<domain>-gold.
Certified endorsement on shared models. Promoted at least, Certified for anything consumed across teams.
Thin reports connect via live connection. No report-local semantic models for Certified content.
Ownership reviewed quarterly.

See semantic models concept for the pattern.

7. Metric views

Grain-level business logic lives in Databricks metric views.
Report-local DAX measures wrap them for time-intelligence, slicing behavior, formatting.
Do not duplicate a metric definition in DAX that already exists as a Databricks metric view. If you find yourself writing the same aggregation, promote it to a metric view.

8. Refresh

Import models: incremental refresh policy on every fact table that grows over time.
Source queries parameterize RangeStart / RangeEnd. Verify Databricks sees a scoped WHERE in Query History.
Refresh triggered by orchestrator via Enhanced Refresh API. Do not rely on the UI scheduler for production.
commitMode: "transactional" unless you have a specific reason for partial-batch.

See Enhanced Refresh.

9. Source control

PBIP format, not .pbix. .pbix in .gitignore.
TMDL for the .SemanticModel folder.
Per-developer workspaces (workspace-dev-<user>) for testing.
Trunk-based development: short-lived feature branches, merged daily.
BPA gate on PRs: no new BPA violations merge.

See PBIP + Git guide.

10. Deploy

fabric-cicd deploys every semantic model. Fabric Deployment Pipelines permitted for small teams; not the default.
Three environments: dev, staging, prod. XMLA read-write on all three.
Service principal auth. OIDC federation from CI.
BPA validation + DAX query tests before deploy.
Post-deploy refresh smoke test on every environment.

See CI/CD guide.

11. Automatic aggregations

Enabled on any DirectQuery or Direct Lake model with more than ~100M fact rows.
Disabled on models with calculated columns on facts, bidirectional relationships, or non-star topology. Fix the model first.

12. Row-level security

Defined in TMDL, not in the service.
Tested in dev against known users before staging deploy.
Role filters expressed via DAX on dimension tables, not on facts (facts are scanned; filtering the dim prunes the fact scan).

role 'Regional Analyst'
    tableFilter
        'dim_customers' =
            'dim_customers'[region] = USERPRINCIPALNAME()

13. Documentation

Every shared model has a one-paragraph description, listing upstream sources, grain, intended consumers.
Every measure has a description including what it computes and edge cases (nulls, blanks, currency assumptions).
Every breaking change to a Certified model is documented in a release note before deploy.

Note

"Documented" means in the TMDL file, not in a Notion page. TMDL descriptions surface in Power BI's measure picker, in dbt exposures lineage, and in fabric-cicd validation. Notion pages drift.

14. Testing

BPA passes on every PR.
DAX query tests for measure correctness run on every deploy.
Refresh smoke test on every environment after deploy.
Visual regression (optional but recommended for shared models) via Playwright screenshot diffs.

15. Review checklist

PRs touching a semantic model must satisfy:

[ ] PBIP + TMDL format; no .pbix committed.
[ ] Connector and auth conform to section 1.
[ ] Star schema, no calculated columns on facts, conformed dim_date.
[ ] Storage mode choice documented.
[ ] Measures follow section 5 rules.
[ ] New measures have description and FormatString.
[ ] BPA passes.
[ ] DAX query tests updated for any measure change.
[ ] Incremental refresh policy in place on growing fact tables.
[ ] fabric-cicd validation passes.