Super long refresh times despite incremental refresh

Anonymous  

Hi sorry for the late reply, When I check in our SnowFlake Query history, it does look like it's only sending queries for the intervals set up in the incremental settings. But there are some extra queries like the following that appear almost more often than the real query

SHOW PRIMARY KEYS IN TABLE "DB"."Schema"."Table"

SHOW COLUMNS IN TABLE "DB"."Schema"."Table"

Here's an anonymised version of the query that messes up the load time: (then as previously mentioned there's a small number of simple calcualted columns as well)

let
    Source =
        Snowflake.Databases(
            "your_account.region.azure.snowflakecomputing.com",
            "DATA_SOURCE",
            [Implementation = "2.0"]
        ),

    TargetDatabase =
        Source{[Name = "TARGET_DB", Kind = "Database"]}[Data],

    TargetSchema =
        TargetDatabase{[Name = "TARGET_SCHEMA", Kind = "Schema"]}[Data],

    FactTable =
        TargetSchema{[Name = "FACT_TABLE", Kind = "Table"]}[Data],

    #"Filtered on Date Range" =
        Table.SelectRows(
            FactTable,
            each [TimePeriod] >= RangeStart
                and [TimePeriod] < RangeEnd
        ),

    #"Selected Columns" =
        Table.SelectColumns(
            #"Filtered on Date Range",
            {
                "EntityID",
                "Date",
                "Hour",
                "TimePeriod",
                "DeviceID",
                "Measure1",
                "Measure2",
                "Measure3",
                "Measure4"
            }
        ),

    #"Grouped Rows" =
        Table.Group(
            #"Selected Columns",
            {"EntityID", "Date", "Hour", "DeviceID"},
            {
                {"Measure1_Per_Hour", each List.Sum([Measure1]), type nullable number},
                {"Measure2_Per_Hour", each List.Sum([Measure2]), type nullable number},
                {"Measure3_Per_Hour", each List.Sum([Measure3]), type nullable number},
                {"Measure4_Per_Hour", each List.Sum([Measure4]), type nullable number}
            }
        )
in
    #"Grouped Rows"

Hi F003Yum ,

Thank you for the detailed update, it really helps clarify the issue. From what you’ve described, it seems the problem is likely related to query folding at the group by step, rather than capacity or composite model issues. Incremental refresh depends on Power BI pushing date filters to the source, but certain group by patterns or changes in column order or data types can prevent this. When folding breaks, Power BI pulls in more data and aggregates locally, which explains the failures and inconsistent behavior you’re experiencing, and why one grouped version works while another does not. Using the same fact table multiple times can also increase the load, as each reference might be evaluated separately. The main thing to check is whether folding is maintained in the failing query. If not, performing hourly aggregation closer to the source usually makes incremental refresh more reliable in these cases.