Best practices in Snowflake for Power BI

The Snowflake Connector 2.0 (based on the ADBC driver) offers significant performance improvements over the legacy ODBC driver, including faster data retrieval and reduced metadata calls.

Ensure your Power BI Desktop is updated to the latest version (post-July 2025 GA). Verify the connection uses Implementation 2.0 in the advanced settings if not selected by default.

Mirroring Snowflake in Microsoft Fabric allows you to replicate data into OneLake in near-real-time without building ETL pipelines. This provides Direct Lake performance (faster than DirectQuery) without the latency.

Configure Mirroring in your Fabric workspace to replicate Snowflake tables. Connect Power BI to the mirrored OneLake data using Direct Lake mode for blazing-fast performance.

Power BI performs best with dimensional models like star schemas, which separate fact tables (transactional data) and dimension tables (descriptive data). This reduces query complexity and improves performance.

Store large, frequently updated transactional data in a central fact table. Keep smaller, less volatile reference data (categories, dates, customer details) in dimension tables. Ensure relationships are well-defined in Snowflake even though Power BI handles joins in its model.

Clustering organizes data in Snowflake's micro-partitions based on specific columns, reducing the amount of data scanned during Power BI queries (partition pruning).

Identify columns commonly used in Power BI filters or joins (e.g., date, customer_id, product_id). Set a clustering key on these columns with ALTER TABLE … CLUSTER BY (column1, column2).

Pre-aggregating data reduces compute load on Snowflake during report rendering. Dynamic Tables simplify the data engineering pipeline for continuous transformations.

Use Materialized Views for simple aggregations (e.g., SUM(sales)) that need instant consistency. Use Dynamic Tables for complex multi-table joins and transformations that can tolerate slight staleness (defined by target lag).

Query tagging lets you accurately attribute Snowflake costs to specific Power BI reports or workspaces.

Use the Query Tag feature in the Snowflake connector (supported in V2) to pass context (e.g., Report Name, Workspace ID) with every query.

Power BI supports Import, DirectQuery, and Composite modes — each with different storage implications in Snowflake.

Use Import mode for best performance and pre-aggregate in Snowflake to reduce dataset size. Use DirectQuery for real-time requirements and optimize with clustering and materialized views. Use Composite to keep large fact tables in DirectQuery and dimensions in Import.

Power BI's incremental refresh feature works best when Snowflake tables are structured to support efficient updates.

Add a last_updated timestamp column to track changes and ensure it is part of the clustering key to speed up the range queries generated by Power BI.