Warehouse Scaling

Hi binitafulpagare,

Microsoft Learn recommends optimizing statistics, queries, data types, and data loads, implementing a well-designed dimensional model, using workload management features (such as automatic scaling and separate compute pools), and sizing Fabric capacity based on workload demand to improve query performance, support concurrent workloads, optimize costs, and build scalable data warehouse solutions. While these articles provide official best practices, they don't include production deployment experiences. We'll keep this thread open so other community members can share their real-world lessons learned.

Performance Guidelines - Microsoft Fabric | Microsoft Learn

Workload Management - Microsoft Fabric | Microsoft Learn

Modeling Fact Tables in Warehouse - Microsoft Fabric | Microsoft Learn

Thank you

binitafulpagare 

For long-term optimization of a Data Warehouse, it's important to follow established best practices all the time. Based on production grade workload and experience, I suggest focusing on the following areas:

1. Making Queries Run Faster

• Use a simple Star Schema: Keep facts in the middle and dimensions around it. It works much better than flat or messy tables.
• Partition big tables: Group huge fact tables by date so the system does not scan everything every time.
• Don't pull extra data: Avoid SELECT *. Only pull the exact columns you need, and try to limit heavy joins on massive tables.
• Pre-aggregate when you can: Use aggregated tables or materialized views for common reports, and turn on Incremental Refresh in Power BI.

2. Keeping Costs Down

• Match capacity to actual use: Pause or scale down your Fabric capacity during nights or weekends when no one is working.
• Load only what changed: Use incremental loads instead of re-processing entire tables every day.
• Clean up old data: Move old, unused data to cheaper cold storage or delete it if it is not needed.
• Watch your metrics: Keep an eye on capacity usage to spot expensive queries before they blow up the bill.

3. Simple Data Modeling Rules

• Do transformations early: Move calculated columns to the ETL stage instead of doing them inside Power BI.
• Keep names clean: Use standard naming convention, I would go with the snake case(lower case) for all names of artifacts in warehouse, use surrogate keys and consistent field names so the model stays easy to read.

4. Managing Busy Times

• Split the work: Try not to run weighty data refreshes at the exact same time business users are viewing reports.
  
  If this helps, ✓ Mark as Kudos | Help Others

Hi binitafulpagare​,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

Hi binitafulpagare​,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

Hi binitafulpagare,

A few Fabric-specific lessons I would add from a scaling perspective:

• I would first use the Fabric Capacity Metrics app to separate a query-design problem from a capacity problem. It shows peak CU consumption, throttling and which items are driving the load. Fabric Warehouse can burst above its baseline capacity for short periods, so I would only scale the capacity after confirming sustained pressure rather than reacting to one slow query.
• For query tuning, I would use Data Warehouse Monitor and query insights to identify the longest-running and most frequently executed statements, then review join and filter columns, data types, statistics and unnecessary data scans.
• One distinction from traditional SQL warehouses is that Fabric manages distribution, micro-partitioning and file compaction automatically. User-defined partitioned tables are not currently supported, as noted in the Warehouse table limitations, so I would focus more on a clean dimensional model, efficient loads and selective queries than manual partition maintenance.

For concurrency, I would also avoid scheduling every ingestion, transformation and reporting workload into the same peak window. Fabric has autonomous workload management, but all workloads still consume the underlying capacity, so staggering non-urgent processing can make a noticeable difference.

My general order would be: measure the workload, tune the expensive queries and loads, check for sustained throttling, and only then increase the Fabric capacity.