Making the Work Cheaper

Every Fabric workload draws from the same capacity, but each engine has its own tuning playbook. Apply the fix where the CUs were consumed, not where the symptom appeared.

There are only two ways to reduce what a workload consumes:

Daily CU-seconds = CU-seconds per operation × operations per day

You can make each operation cheaper, or run fewer of them. Teams often focus on the first and ignore the second, which is usually the larger and easier saving.

Start with frequency

Return to the baseline from lesson two, where you wrote down how often each top consumer’s data actually needs to change. Compare that with how often it runs.

An illustrative example: a semantic model refresh consumes 40,000 CU-seconds and runs hourly, so it consumes 960,000 CU-seconds a day. Its source system loads once a night. Moving it to a daily refresh after the load saves 920,000 CU-seconds a day, about 17% of an F64’s daily budget, without touching a line of code.

Frequency reviews are uncomfortable because someone once asked for hourly data. Ask what decision the data supports and how quickly that decision is made. Freshness nobody acts on is capacity spent for nothing.

Note that moving work to a different time of day is not the same as removing it. Background work is already smoothed over 24 hours, so rescheduling 24 hourly runs to a quieter period does not reduce their total consumption. Reschedule when it reduces contention with a critical user window or protects a source system. Remove runs when the data does not need them.

Warehouse and SQL analytics endpoint

Start with Query Insights, which keeps history for completed queries and highlights long-running, frequent, and CPU-intensive requests.

SELECT TOP 50
    query_hash,
    COUNT(*) AS executions,
    SUM(allocated_cpu_time_ms) AS total_cpu_ms,
    MAX(label) AS sample_label
FROM queryinsights.exec_requests_history
GROUP BY query_hash
ORDER BY total_cpu_ms DESC;

Grouping by query_hash shows the total cost of a query pattern, which often matters more than the single most expensive run. Then look for the usual causes:

• SELECT * on wide tables, and missing filters that force large scans
• Joins on mismatched data types that require conversion
• Stale statistics after large loads
• Streams of tiny inserts or updates instead of batches
• Small-file accumulation in Lakehouse tables that table maintenance would fix

Ignore the first run when you compare. Cold cache and statistics creation make it slower. Compare repeated runs under similar conditions.

Spark and notebooks

Spark cost comes from the relationship between data layout, code, executor use, and session lifetime. Use the Spark monitoring views to find skew, large shuffles, spills, and idle executors.

• Filter and select columns early instead of carrying unused data through every step.
• Avoid repeated actions over the same uncached data.
• Compact small Delta files after material changes.
• Size the pool and executors to the job rather than assuming more parallelism is cheaper.
• Stop sessions when the work is done. Idle interactive sessions keep consuming capacity until they time out, so review the session timeout for development workspaces.

If Spark demand is bursty and competes with reporting, consider on-demand billing for Spark, covered in lesson six. Measure a Spark baseline first so you can compare the two cost models honestly.

Pipelines and Dataflow Gen2

A pipeline often triggers work that is billed to another engine: a notebook, a stored procedure, a copy, or a semantic model refresh. Follow the activity to the item that consumed the CUs.

• Load incrementally instead of copying full history on every run.
• Keep query folding intact in Dataflow Gen2, so filters and joins run at the source rather than in the dataflow.
• Remove activities that copy data into an intermediate location nobody reads.
• Check retry settings. A failing activity with generous retries can multiply cost silently.

Semantic models and reports

Power BI consumes capacity in two ways: background refreshes and interactive report queries.

For refresh:

• Use incremental refresh for large fact tables.
• Remove unused columns, and reduce high-cardinality columns where the business allows.
• Keep refresh frequency aligned with the source.
• Avoid scheduling many subscriptions or paginated reports at the same minute.

For report queries:

• Use Performance Analyzer in Power BI Desktop to find the expensive visuals, then fix the DAX behind them.
• Keep a clean star schema with one-to-many relationships.
• Reduce the number of visuals per page, and set restrictive default filters.
• Test row-level security, because complex security filters multiply query cost.

Direct Lake models avoid import refresh, but they still consume capacity for queries. If a Direct Lake model reads through a SQL analytics endpoint, check whether queries fall back to DirectQuery, which is usually slower and more expensive.

Real-Time Intelligence

Continuous ingestion and frequent queries are part of the design, so the questions change.

• Limit query time ranges and project only the columns you need.
• Use materialized views for repeated aggregations when their maintenance cost is justified.
• Align caching and retention policies with how far back people actually query.
• Batch ingestion where the latency requirement allows.

A real-time workload that becomes cheaper but misses its freshness target is not optimized. Measure latency alongside consumption.

Data agents and Copilot

AI workloads consume capacity in two places: the AI processing itself, and the queries the agent generates against warehouses, semantic models, or KQL databases. Focused schemas, concise instructions, efficient measures, and sensible answer lengths reduce both. Lesson five covers how to estimate this demand.

Prove the saving

A change is not an optimization until the evidence says so. For the same business operation, compare before and after over a representative period:

Measure	Before	After
CU-seconds per operation
Operations per day
Daily CU-seconds
Duration
Rejected and failed count
User-facing latency or freshness

Check that the cost did not simply move. A query that became cheaper because a new nightly job precomputes its result has shifted cost into background work. That can still be a good trade, but count both sides.

Exercise: one fix per top consumer

For each of your top five items from the baseline, decide which lever applies and estimate the saving.

Item	Lever: frequency, per-operation, or neither	Planned change	Estimated daily CU-seconds saved	Owner

Make the one change with the largest estimated saving and the lowest risk first. Measure it for a week before moving on. Changing five things at once leaves you unable to tell which one worked.

Carry this forward

Once the work is efficient, what remains is legitimate demand. The next lesson sizes a capacity around that demand, including new workloads that have not been built yet.

Microsoft references

• Query Insights in Fabric Data Warehouse
• Monitor Apache Spark applications
• Incremental refresh in Power BI
• Optimization guide for Power BI