Power BI and Azure SQL Database with DirectQuery - When Live Data Is Worth the Trade-offs

There is a question that comes up on nearly every Power BI project we run, and people usually get to it about two weeks in, once the honeymoon is over and the report is slow or the data is stale. The question is: should this report import a copy of the data, or should it query the database live? For a lot of businesses sitting on an Azure SQL Database, that second option is DirectQuery, and getting the choice right is one of those decisions that quietly determines whether your reporting is fast and stale or fresh and fragile.

Microsoft's documentation on this is Azure SQL Database with DirectQuery, and it walks you through the mechanics of connecting. What it does not really do, because documentation rarely does, is tell you when you actually want this and when you will regret it. That is the part I want to cover, because we have made this call dozens of times and the pattern of where it works and where it hurts is pretty clear by now.

Import versus DirectQuery, in plain terms

Power BI has two fundamental ways to work with your data. Import mode copies the data into Power BI's own in-memory engine. Your report runs against that copy. It is fast, because the data is sitting in a highly optimised store right next to the report, and it is the default for good reason. The catch is that the copy is a snapshot. It is only as fresh as the last refresh, and refreshes happen on a schedule, so between refreshes your data is by definition out of date.

DirectQuery does not copy anything. Every time someone opens the report or clicks a filter, Power BI writes a SQL query and sends it to your Azure SQL Database in real time. The database does the work and sends back the result. The data is always live, always current, because you are looking straight at the source. The catch is that every interaction is now a round trip to your database, and the speed of your report is entirely at the mercy of how fast that database answers.

That is the whole trade in one sentence. Import gives you speed and costs you freshness. DirectQuery gives you freshness and costs you speed. Everything else is detail.

When DirectQuery is the right call

There are a few situations where I reach for DirectQuery without much hesitation.

The first is when the data genuinely has to be live. Not "the business said they want real-time" when they check the report twice a day, but genuinely operational reporting where a number that is four hours stale is a wrong number. A dispatch board, a live operations dashboard, a fraud or monitoring view. If the whole point of the report is to reflect what is happening right now, importing a snapshot defeats the purpose and DirectQuery is the honest answer.

The second is when the data is simply too big to import comfortably. Power BI's import engine is impressive, but if you are sitting on hundreds of millions of rows or you have compliance reasons the data cannot leave the database, DirectQuery lets you report on it without hauling a copy into Power BI. The database stays the source of truth and Power BI is just a window onto it.

The third is a governance and single-source-of-truth argument. When the data lives in one governed Azure SQL Database and you want every report to reflect that one place with no copies drifting around, DirectQuery keeps you honest. There is no second copy to fall out of sync, because there is no copy at all.

Where it bites you, and it will

Now the warnings, because DirectQuery has a failure mode and it catches people out constantly.

The big one is performance. Every slicer, every filter, every cross-highlight in a DirectQuery report fires SQL at your database and the user waits for the answer. If your Azure SQL Database is undersized, or your tables are not indexed for the queries Power BI generates, or your data model has complex relationships and heavy measures, the report will feel sluggish and every interaction will have that little lag that makes people hate a dashboard. I have seen DirectQuery reports where clicking a filter took eight seconds, and no amount of pretty design survives that. The database becomes the bottleneck, and if it is a database that is also serving your production application, your reporting load can slow down the app itself.

The second issue is that DirectQuery limits what you can do. Not every Power BI feature, not every DAX function, not every bit of transformation works in DirectQuery mode, because it all has to translate down to SQL the database can run. You lose some of the flexibility import mode gives you. For simple, well-shaped data this rarely bites. For gnarly transformations it can be a real constraint, and you find out about it halfway through building something.

The third, and the one that surprises people, is cost. Because every interaction hits Azure SQL Database, a heavily-used DirectQuery report drives real query load, and on some Azure SQL pricing tiers that translates into a bigger, more expensive database to keep the reports responsive. Import mode does its heavy lifting once at refresh time. DirectQuery does it on every click, all day, for every user. That is a running cost, not a one-off, and it needs to be in the business case.

How we actually decide

Our default is import. It is faster, cheaper to run, more flexible, and for the overwhelming majority of business reporting, data that refreshes every hour or every few hours is perfectly fine. Most people who insist on real-time do not actually need it once you ask them how often they look and what decision changes based on the last hour of data. Start from import and make DirectQuery earn its place.

We move to DirectQuery when there is a real reason: the data genuinely must be live, it is too large to import, or there is a hard governance requirement that it stays put. And when we do, we treat the database side as a first-class part of the job. That means making sure Azure SQL Database is sized properly, the tables are indexed for the query patterns Power BI will generate, and the data model in Power BI is kept lean so it is not firing monstrous queries on every click. DirectQuery done carelessly is a slow, expensive report. Done with attention to the database underneath, it is genuinely good.

There is also a middle path worth knowing about: composite models, where some tables are imported and some are DirectQuery in the same report. You import the small, slow-changing dimension tables and use DirectQuery only for the big, live fact table. That gets you fresh data where it matters and speed everywhere else. It is more work to set up and reason about, but for the right problem it is the best of both. We reach for it when a straight either-or choice does not fit.

Getting this decision right is a real part of what our Power BI consulting work involves, because it is the kind of choice that is cheap to get right at the start and painful to unwind later once reports and habits are built on it. It also usually opens up the deeper question of whether the data platform underneath is set up well, which is where Microsoft Fabric and a properly designed Azure data and AI foundation come in. A good reporting layer sits on a good data layer, and DirectQuery just makes the quality of that layer very visible very quickly.

If your Power BI reports are either frustratingly stale or frustratingly slow, the import-versus-DirectQuery choice is often the thing sitting underneath it, and it is fixable. Get in touch and we will work out which mode each of your reports actually wants to be in.