Applies toAzure Synapse dedicated SQL pools, Azure SQL Database used as a reporting mart, Databricks SQL (serverless/pro warehouses), Unity Catalog, Power BI
The pattern being replaced
Lake (Delta) → copy pipeline → Synapse dedicated / Azure SQL mart → Power BI
Each copy adds latency, a pipeline to maintain and a second set of permissions. If the gold tables already live in Delta, a SQL warehouse can serve them directly.
1. Decide what moves
| Keep in the SQL database | Move to Databricks SQL |
|---|---|
| OLTP-style apps writing single rows | Star-schema marts read by BI |
| Heavy use of stored procedures with cursors, temp tables, app logic | Aggregations and views over gold tables |
| Features the app depends on (e.g. row-level triggers) | Scheduled refresh workloads |
2. Convert objects
| T-SQL | Databricks SQL |
|---|---|
| Tables with distribution/indexes | Delta tables with liquid clustering (CLUSTER BY) |
| Views | Views (check T-SQL-only functions) |
IDENTITY | GENERATED ALWAYS AS IDENTITY |
TOP n | LIMIT n |
ISNULL, GETDATE(), DATEADD | coalesce, current_timestamp(), dateadd / date_add |
MERGE | MERGE INTO |
| Stored procedures | SQL scripting / SQL tasks in jobs (check current support) or Python tasks |
| Row-level security / dynamic masking | Row filters and column masks in Unity Catalog |
CREATE TABLE main.gold.fact_sales (
sale_id BIGINT GENERATED ALWAYS AS IDENTITY,
date_key INT, store_key INT, product_key INT,
qty INT, amount DECIMAL(18,2)
) CLUSTER BY (date_key, store_key);
3. Size the warehouse
Start with a serverless warehouse, small size, auto-stop at 10 minutes, scaling min 1 / max 2–4 clusters. Load test with the real report queries; scale up size for slow single queries, scale out clusters for concurrency. Check system.query.history for queued time.
4. Repoint Power BI
- Use the Azure Databricks connector with the warehouse's server hostname and HTTP path.
- Authenticate with OAuth (Entra ID) so UC permissions apply per user, or a service principal for import refresh.
- For DirectQuery, check query patterns — add aggregation tables or materialized views for the heaviest visuals.
5. Parallel run and retire
Compare report totals from both sources for a full period close. Then switch the published datasets, stop the copy pipelines, and pause/scale down or delete the dedicated pool.
Tip
Primary key and foreign key constraints (informational) on gold tables help Power BI and query optimisation — declare them.
Checklist
- Mart objects classified (move/keep)
- DDL and views converted; security as UC filters/masks
- Warehouse sized from real queries
- Power BI repointed with OAuth/SP
- Copy pipelines and pool retired