Data Load issues in direct query mode

1) Reduce the number of round-trips Power BI generates (Power BI-side)

DirectQuery performance is often less about “one query being slow” and more about “too many queries”.

Do these first:

• Turn on Query reduction and use “Apply” button on slicers
  File > Options and settings > Options > Query reduction
  This prevents Power BI from firing queries on every single click/typing step.

• Limit “Show items with no data”, avoid “Top N” that forces extra scans.

• Prefer measures over calculated columns, and avoid patterns that force expensive grouping at high grain.

2) Use Aggregations / Composite Model 

• Create an aggregated fact table (e.g., daily/weekly, by product/store/channel) and Import it

• Keep the today data table as DirectQuery and create your measure considering this

• Configure Aggregations so most visuals hit Import, not Redshift

This reduces Redshift query load by an order of magnitude in many cases.

Hey Lancy_vini , 

When you use DirectQuery against a very large Amazon Redshift table (2 billion rows × 250 columns), Power BI sends a SQL query to Redshift every time a user interacts with a slicer, filter, or visual. Each click can trigger multiple queries. Because of this, Redshift ends up handling many concurrent long-running queries, which can lead to locks and overall performance degradation. This is expected behavior in DirectQuery mode, especially with very large fact tables.

The first and most important optimization is to reduce the amount of data Power BI needs to query. Instead of connecting directly to the 2B-row raw table, create aggregated tables or materialized views in Redshift that pre-filter or pre-summarize the data based on reporting needs. Power BI works much better when querying summarized datasets rather than raw transactional-level data. You can also create reporting-specific views that only expose the required columns instead of all 250 columns.

From the Power BI side, you should optimize the model. Remove unnecessary columns, avoid complex DAX measures that generate heavy SQL, and minimize the number of visuals on each page because each visual generates its own query. Also disable “Auto date/time,” avoid bi-directional relationships unless absolutely necessary, and turn off unnecessary interactions between visuals. These steps reduce the number of backend queries triggered.

Another strong recommendation is to use a Composite Model instead of pure DirectQuery. You can import aggregated or historical data into Power BI and keep only the most recent or critical portion in DirectQuery. Even if the database refreshes every few hours, you can schedule full dataset refreshes in Import mode after each load. A full refresh every few hours is often still more efficient than constant DirectQuery hits on a 2B-row table.

Since you mentioned there is no incremental column, consider adding one at the database level. Even a load timestamp column added during ETL would allow incremental refresh in Power BI, which is far more efficient and avoids querying the full dataset repeatedly.

Finally, from the Redshift side, ensure proper distribution keys, sort keys, workload management (WLM) configuration, and consider concurrency scaling or RA3 nodes if not already used. Many locking issues in Redshift under BI workloads are actually workload configuration problems rather than Power BI problems.

In summary, DirectQuery on a 2-billion-row wide table will always be heavy. The right approach is to push aggregation to Redshift, reduce columns, optimize the Power BI model, consider composite or import mode with scheduled refresh, and if possible introduce an incremental column to enable incremental refresh.

If this explanation helped, please mark it as the solution so others can find it easily.

If it helped, a quick Kudos is always appreciated it highlights useful answers for the community.

Thanks for being part of the discussion!