Power BI DirectQuery vs Import - Choosing the Right Connection Mode for Your Data

Every Power BI project starts with the same question - how should we connect to the data? It sounds basic, but this decision affects everything downstream. Report performance, data freshness, infrastructure costs, and even how your team works day-to-day all hinge on whether you choose Import or DirectQuery.

We've worked on enough Power BI projects to know that most teams either default to Import without thinking or jump to DirectQuery because "real-time" sounds impressive in a stakeholder meeting. Both approaches can go wrong if you don't understand what you're actually trading off.

Import vs DirectQuery - What's Actually Different

With Import mode, Power BI pulls a copy of your data into its own in-memory engine. You define tables and columns, hit refresh, and everything gets loaded into a compressed columnar store. Queries against your reports run against this local copy, which is why Import mode reports feel fast. The downside is your data is only as current as your last refresh.

DirectQuery takes the opposite approach. Nothing gets imported. When someone opens your report or clicks a slicer, Power BI sends a query to the source database in real time. You're always looking at current data. The downside is that every interaction depends on how fast your source database can respond.

Here's how I think about it in plain terms. Import mode is like printing a report from your database every morning and working from the printout. DirectQuery is like having a live terminal open to the database. The printout is fast to read but might be stale. The live terminal is always current but sometimes slow.

When DirectQuery Makes Sense

Not every project needs DirectQuery, but some genuinely do. Here's where we've seen it work well.

Large datasets that exceed Power BI's limits. Import mode has a 1 GB dataset size limit in the Power BI service (though Premium capacities raise this). If you're working with hundreds of millions of rows - common in manufacturing, logistics, and financial services - you may not be able to import everything. DirectQuery sidesteps this entirely because no data is stored in Power BI.

Regulatory or compliance requirements. Some Australian organisations, particularly in financial services and healthcare, have rules about where data can be copied. If your compliance team says "the data stays in our database," DirectQuery lets you build reports without moving data outside your controlled environment.

True real-time needs. If your operations team needs to see orders, stock levels, or sensor data as it happens - not as it was at 6am this morning - DirectQuery gives you that. We've built DirectQuery reports for a logistics company tracking container movements where a 15-minute delay in data was genuinely unacceptable.

Massive data that's expensive to refresh. Sometimes the issue isn't technical capability but cost. Refreshing a 50 GB Import model from Azure Synapse multiple times a day burns through compute. DirectQuery shifts that cost to per-query rather than bulk refresh, which can work out cheaper depending on usage patterns.

When Import Mode Is the Better Choice

For most reporting scenarios, Import mode wins. I'll be honest about that. It's faster, more reliable, and supports the full range of DAX and Power BI features.

Dashboards where sub-second performance matters. Import mode queries resolve in milliseconds. DirectQuery queries depend on your source database, and five-second response times are common. If you have executives who expect instant interactions with slicers and filters, Import mode is the safer bet.

Complex DAX calculations. Some DAX patterns don't translate well to SQL. Time intelligence functions, complex iterators, and certain calculated tables work beautifully against the in-memory engine but can generate horrifically inefficient SQL queries when pushed through DirectQuery. We've seen single DirectQuery queries take 90 seconds because the generated SQL was joining seven tables with nested subqueries.

Limited data source performance. If your source database is already under heavy transactional load, adding DirectQuery report traffic on top can cause problems. Every report viewer, every slicer click, every page navigation - they all hit your database. For a report used by 200 people, that's a lot of concurrent queries.

The DirectQuery Setup Process

Connecting to a data source with DirectQuery in Power BI Desktop is straightforward. When you click Get Data and select your source (SQL Server, Azure SQL, Synapse, Snowflake, and many others), you'll see a "Data Connectivity mode" option. Select DirectQuery instead of Import, and you're off.

The catch is on the publishing side. When you publish a DirectQuery report to the Power BI service, you need to handle credentials and gateways.

For cloud data sources like Azure SQL Database, Azure Synapse, Snowflake, and Amazon Redshift, no gateway is needed. You just provide credentials in the Power BI service dataset settings - navigate to the workspace, find the semantic model, and enter your data source credentials under Settings. Without these credentials, anyone trying to open the report gets an error.

For on-premises data sources (your SQL Server sitting in a data centre or under someone's desk), you need an on-premises data gateway. The gateway acts as a bridge between the Power BI service in the cloud and your local database. It's an extra piece of infrastructure to maintain, but it works reliably once configured.

One gotcha that trips people up - if you're connecting to an Azure SQL Database with a private IP address (a common setup for security-conscious organisations), you still need the on-premises gateway even though it's technically a cloud resource. The private endpoint means Power BI can't reach it directly.

Performance Realities

Microsoft recommends that DirectQuery responses come back within five seconds for a reasonable user experience. Beyond 30 seconds, the experience becomes genuinely poor. Queries that exceed four minutes time out entirely.

These aren't arbitrary numbers. We've watched users abandon reports that take more than 10 seconds to respond to a filter change. They go back to asking someone to pull data into Excel, which defeats the purpose of the whole BI investment.

Here's what affects DirectQuery performance in practice:

Query complexity. Simple visuals with a few columns and basic aggregations generate clean SQL that runs fast. Complex visuals with many measures, calculated columns, and cross-filtering generate complex SQL that runs slowly. There's a direct relationship between visual complexity and query time.

Concurrent users. Ten people using a report simultaneously means ten times the queries. If you're using row-level security (RLS), it's even worse - each user gets their own query rather than sharing cached results. We've seen source databases buckle under the load of a popular DirectQuery report.

Source database optimisation. This is the one most teams neglect. Your source database needs proper indexing, statistics, and query execution plans tuned for the queries Power BI generates. The best Power BI report design in the world can't fix a poorly optimised database.

The million-row limit. DirectQuery caps results at one million rows for cloud data sources. This catches people off guard when they build visuals with high-cardinality columns without aggregation. The fix is to either aggregate your data or apply filters to bring results below the limit. Premium capacities can adjust this limit, but the default catches most people.

Practical Recommendations

After working on dozens of Power BI and data analytics projects, here's what we typically recommend:

Start with Import unless you have a specific reason not to. The performance advantage is significant, and scheduled refreshes every 30 minutes or hour are frequent enough for most business reporting.

Use composite models when you need both. Power BI supports mixing Import and DirectQuery in the same model. Keep your large fact tables in DirectQuery and import your dimension tables. This gives you decent performance while keeping data volumes manageable.

Invest in source database optimisation if you go DirectQuery. Budget time and effort for creating proper indexes, reviewing query plans, and potentially creating optimised views for Power BI to query against. This is not optional - it's where most DirectQuery projects succeed or fail.

Test with realistic user loads. A DirectQuery report that works fine for one developer will behave very differently with 50 concurrent users. Test accordingly before rolling out to the business.

Consider the gateway carefully. If you need an on-premises gateway, plan for redundancy (a gateway cluster), monitoring, and updates. A single gateway that goes down takes all your DirectQuery reports with it.

Microsoft's full documentation on DirectQuery connectivity modes covers additional edge cases and data source-specific considerations that are worth reading before committing to an approach.

The Bottom Line

DirectQuery is a powerful option, but it's not a free lunch. You're trading import complexity for query performance complexity. For the right use cases - large datasets, compliance requirements, genuine real-time needs - it's the right choice. For everything else, Import mode remains the pragmatic default.

The most successful Power BI deployments we see at Team 400 are the ones where someone actually thought about this choice upfront rather than discovering the trade-offs six months after go-live. If you're planning a Power BI rollout and aren't sure which direction to go, that's exactly the kind of decision worth getting right early.