DP-600: how to master DirectQuery in Power BI/Fabric

I will teach the skill of configuring and optimizing DirectQuery in Power BI/Fabric — an ability that appears in the Implement and manage semantic models and Prepare the data areas. DirectQuery is crucial for the exam and in practice when you need real-time reports or have volumes of data that you should not import.

What you need to know

DirectQuery is a connectivity mode where the semantic model (dataset) does not import data into the local engine; instead, queries are translated and sent to the data source on each interaction. This enables reports with lower latency in data freshness, but imposes constraints: performance depends on the source, transformations are limited and some DAX/storage functions are unavailable or behave differently.

Simple example: a Power BI report connected to a sales table in a Synapse SQL Server via DirectQuery. When the user applies a date filter, Power BI converts that selection into a SQL query against the table in Synapse and retrieves only the required records.

How it works

Key steps and components to understand:

1. Connection and mode: when creating a connection you choose Import, DirectQuery or Composite. DirectQuery keeps the data at the source.
2. Query folding: in Power Query, operations that can be translated to the source are sent as parts of the query. In DirectQuery, maximizing query folding is critical for performance.
3. Cache and lifetime: Power BI/Fabric can use result caching (query cache), but the model is driven by calls to the source. Configuring cache lifetimes helps balance freshness and load.
4. Transformation limitations: very complex transformations may not be supported; whenever possible, push logic to the source (views/stored procedures).
5. Security: row-level security (RLS) in DirectQuery can be applied in the model, but also consider security at the data source.

In practice

Practical step-by-step example (scenario: Power BI Desktop connected to Azure SQL Database in DirectQuery).

1. In Power BI Desktop, Get data → Azure → Azure SQL Database.
2. Enter server and database; choose DirectQuery instead of Import.
3. In Power Query Editor, avoid steps that prevent query folding (e.g., operations that use custom functions).
4. Create views in the database to consolidate complex logic; connect Power BI to those views.
5. Publish to Power BI Service / Fabric. Configure gateway connections if the source is on-premises.
6. In the service, validate the Performance Analyzer to see the generated queries and measure latency.
7. Enable the query cache and configure the cache lifetime if needed (in the Fabric/Power BI admin settings) to reduce repeated calls.

Concrete optimization tips:

• Design indexes at the source to support the most common queries (filters and joins).
• Use aggregations in the semantic model to quickly answer summary queries, while details remain in DirectQuery (Composite model).
• Avoid DAX measures that force full scans; prefer indexed columns and simple aggregation measures at the source.
• Monitor queries with the Performance Analyzer and with database monitoring tools to identify bottlenecks.

Common mistakes

1) Ignoring the impact of query folding: applying steps in Power Query that break folding and force local operations or heavy queries at the source. This degrades performance.

2) Using DirectQuery without considering indexes and models at the source: without proper indexes, each interaction translates into an expensive scanning operation.

3) Expecting full Import mode functionality: certain DAX functions or Vertipaq optimizations are not available; do not mentally transform a DirectQuery into an imported dataset.

How to practice

Practice with real scenarios in controlled environments: set up an Azure SQL Database with test data and create DirectQuery models, then use the Performance Analyzer and the SQL monitoring views to measure impact. To prepare for the DP-600 exam, use the OFFICIAL and free Microsoft Practice Assessment to gauge practical knowledge — the Practice Assessment and the Microsoft study guide are free and recommended as a complement. Also consult the official documentation on DirectQuery, Composite models and query folding on Microsoft Learn.

In summary

• DirectQuery enables reports with real-time data by keeping data at the source instead of importing it.
• Query folding and source optimization are essential for good performance.
• Use Composite models and aggregations to balance latency and analytical capability.
• Practice with monitoring tools and the Microsoft official Practice Assessment to validate skills.