Applies toPower BI (Desktop, Service, Fabric) with the Azure Databricks connector, Databricks SQL warehouses, Unity Catalog, on-premises/VNet data gateways
Storage modes
| Mode | Freshness | Speed | Databricks load |
|---|---|---|---|
| Import | As of last refresh | Fast (in-memory) | Load during refresh only |
| DirectQuery | Live | Depends on warehouse and model | Every visual runs a query |
| Composite (aggregations in import, detail in DirectQuery) | Mixed | Fast for summaries | Detail queries only |
| Direct Lake (Fabric, on OneLake Delta) | Near live | Fast | Reads Delta files from OneLake, not via SQL warehouse |
Choosing
| Situation | Mode |
|---|---|
| Small–medium data, daily/hourly freshness fine | Import (with incremental refresh) |
| Large data, users need recent data, focused queries | DirectQuery to a serverless SQL warehouse |
| Large data with common summaries | Composite with aggregation tables |
| Data mirrored or shortcut into OneLake | Consider Direct Lake |
Security and identity
| Need | Option |
|---|---|
| Per-user permissions (row filters, masks) | SSO with Entra ID so Unity Catalog sees the real user (DirectQuery) |
| Shared scheduled refresh | Service principal / OAuth credentials, not personal tokens |
| Private networking | Private Link to the workspace with a VNet data gateway or on-premises gateway |
Personal access tokens in dataset credentials break when people leave (see the token Sev1 posts).
Warehouse sizing for BI
- Serverless SQL warehouse for fast scale-out at peaks.
- Separate warehouses for heavy BI and ad-hoc analysts if they interfere.
- Watch queue time and scaling in warehouse monitoring (see the SQL warehouse optimisation post).
Design gold tables for BI
- Star schemas: facts and dimensions, integer/surrogate keys.
- Pre-aggregated tables or materialized views for common visuals.
- Liquid clustering on common filter columns (date, region).
- Avoid many-to-many relationships and very wide tables in DirectQuery.
Tip
Use Power BI Performance Analyzer and the Databricks query history side by side. You'll see exactly which visual generated which SQL and how long each took.
Checklist
- Storage mode chosen per dataset
- SSO or service principal authentication
- Private connectivity via gateway
- Warehouses sized and separated where needed
- Gold tables modelled for BI
- Performance measured end to end