Power BI with Snowflake: DirectQuery vs Import Mode and When to Use Each

Last updated: September 2025

Import Mode: How It Works

Import mode pulls data from Snowflake into Power BI's in-memory VertiPaq engine during a scheduled refresh. Once imported, all interactions (filtering, slicing, drilling) happen locally in memory. Snowflake isn't involved at all until the next refresh.

The upside: dashboards are fast. Visuals render in milliseconds because the data lives in Power BI's compressed columnar store. Complex DAX calculations (time intelligence, running totals, percentile calculations) execute instantly because they operate on local data.

The downside: data staleness. If your refresh runs at 6am, users see 6am data all day. You can configure refresh schedules up to 8 times per day (or 48 with a Premium capacity), but that's still not real-time. There's also a 1GB dataset size limit on shared capacity and 10GB on Premium per dataset. For a 500M-row fact table, that's usually not enough.

DirectQuery Mode: How It Works

DirectQuery doesn't import data. Every interaction in the dashboard sends a SQL query to Snowflake in real time. Click a filter? Snowflake runs a query. Drill into a chart? Another query. Change a slicer? Every visual on the page re-queries Snowflake.

The upside: data is always fresh. Users see the latest data every time they interact with the dashboard. There's no dataset size limit because the data stays in Snowflake. You can query tables with billions of rows without importing anything.

The downside: performance depends entirely on how fast Snowflake can execute those queries. And here's the part that surprises people: Power BI sends individual queries per visual. A dashboard page with 12 visuals, 3 slicers, and 2 cards fires at least 17 separate Snowflake queries every time the page loads. Change one slicer, and most of those queries fire again. At $3/credit and 1 credit/hour for an X-Small warehouse, those queries add up fast if the warehouse is running all day.

When to Use Import

• Dataset is under 10 million rows (comfortably fits in Power BI's memory)
• Dashboard is used infrequently (a few times per day, not continuously)
• You need complex DAX calculations (time intelligence, statistical functions, custom measures that don't translate well to SQL)
• Users expect sub-second visual rendering
• Data freshness of 1-3 hours is acceptable

When to Use DirectQuery

• Dataset is larger than 10 million rows or growing rapidly
• Real-time or near-real-time data freshness is required
• Many concurrent users need different data slices (regional managers, client-specific views)
• You're implementing row-level security that needs to be enforced in Snowflake
• Dataset exceeds Power BI's import size limits

The Hybrid Approach: Best of Both

Most production deployments we've built use a composite model. Here's the pattern: import your dimension tables (products, customers, dates, regions - typically small, slowly-changing tables that compress well) and use DirectQuery for your fact tables (transactions, events, logs - large, frequently-updated tables).

This gives you the best of both modes. Slicer interactions against dimension tables are instant (in-memory). Fact table aggregations query Snowflake in real time for fresh data. The dimension tables act as filters that get pushed down to the Snowflake query, so the fact table queries are scoped to just the relevant partition. In Power BI Desktop, you set this up by choosing "Dual" storage mode for dimensions and "DirectQuery" for facts when configuring table storage.

Setting Up the Snowflake Connection

Power BI connects to Snowflake via the Snowflake ODBC driver. Download the latest driver from Snowflake's website (not the generic ODBC driver - it needs to be the Snowflake-specific one). In Power BI Desktop, go to Get Data → Snowflake → enter your account identifier and warehouse name.

For the Power BI Service (published reports), you've got two authentication options:

• SSO with Azure AD: If your Snowflake account supports Azure AD federation, users authenticate with their Microsoft credentials. Snowflake sees the individual user identity, which matters for row-level security enforcement on the Snowflake side.
• Service account credentials: A shared Snowflake username/password stored in the Power BI gateway. Simpler to set up, but Snowflake sees all queries as coming from one user. RLS must be implemented in Power BI, not Snowflake.

Warehouse Sizing for Power BI

Create a dedicated Snowflake warehouse for Power BI. Don't share it with ETL workloads - a heavy ETL job will starve your dashboard queries. Start with X-Small and auto-suspend at 60 seconds:

CREATE WAREHOUSE powerbi_wh
  WITH WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 2
  SCALING_POLICY = 'ECONOMY';

Why X-Small? Power BI's DirectQuery queries are typically simple: SELECT col1, SUM(col2) FROM table WHERE date_col BETWEEN x AND y GROUP BY col1. These queries don't need a lot of compute. An X-Small warehouse (1 credit/hour) handles most dashboard workloads fine. If you've got 50+ concurrent users hitting the dashboard simultaneously, set MAX_CLUSTER_COUNT = 3 or 4 and let multi-cluster warehouses handle the concurrency.

Row-Level Security: Snowflake-Side vs Power BI-Side

You've got two options for RLS, and the right choice depends on your authentication setup:

• Snowflake-side RLS (with SSO): If users authenticate via Azure AD SSO, Snowflake knows who they are. Create a secure view that filters data based on CURRENT_USER() and a mapping table. Users can only see data they're authorized for, regardless of what Power BI tries to query. This is the most secure approach.
• Power BI-side RLS (with service account): If all queries go through a single service account, Snowflake can't distinguish between users. Define RLS roles in Power BI that filter data using DAX. The service account fetches all data, and Power BI applies the filter before rendering. Less secure (the service account has access to everything), but simpler to implement.

Performance Tips

• Reduce visual count per page. Every visual fires at least one Snowflake query. A page with 20 visuals fires 20+ queries on every interaction. Keep it under 8-10 visuals per page. Move supplementary visuals to drill-through pages that only load when clicked.
• Avoid bidirectional cross-filtering. Bidirectional relationships in the data model cause Power BI to send more complex queries to Snowflake, often resulting in multiple subqueries. Use single-direction relationships where possible.
• Use aggregation tables. If your fact table has 500M rows, create a pre-aggregated summary table in Snowflake with daily/weekly granularity. In Power BI, set up an aggregation that serves the summary table for high-level visuals and falls back to the detail table only when users drill in. This can reduce query execution time by 10-50x for top-level dashboard views.
• Avoid calculated columns on DirectQuery tables. Power BI calculated columns are computed during import. On DirectQuery tables, they're translated to SQL and evaluated on every query. If you need computed values, create them as columns in a Snowflake view instead.
• Use Performance Analyzer. Power BI Desktop has a built-in Performance Analyzer (View → Performance Analyzer) that shows the exact SQL query sent to Snowflake for each visual and how long it took. Run this before publishing to catch slow visuals.

The Multi-Query-Per-Visual Gotcha

This is the #1 surprise for teams new to Power BI + Snowflake. When a user opens a dashboard page with 12 visuals, Power BI sends 12+ independent SQL queries to Snowflake. It doesn't batch them or send one big query - each visual gets its own query. If one visual has a complex filter, it sends an even more complex SQL statement.

The cost implications: if you've got 50 users each opening this dashboard 10 times a day, that's 50 * 10 * 12 = 6,000 Snowflake queries per day from one dashboard. At an X-Small warehouse, the compute cost isn't huge. But Snowflake's result cache helps - if two users run the same query (same filters, same data), the second query is served from cache at zero cost. Design your dashboards with shared default views to maximize cache hit rates.

Conclusion

The TL;DR is: start with a hybrid composite model (import dimensions, DirectQuery facts), use a dedicated X-Small Snowflake warehouse with 60-second auto-suspend, keep visuals under 10 per page, and build aggregation tables for high-traffic dashboards. The combo of Power BI's in-memory engine for dimensions and Snowflake's compute for facts gives you fast dashboards with fresh data - without the cost of importing a billion-row fact table into Power BI's memory eight times a day.