Forum Discussion
Incremental Refresh - DateTime Partition Column Works in DEV but Initial Refresh Times Out in PROD
Hi Team,
I am investigating an Incremental Refresh issue with our Missed Utterances report and would appreciate your guidance.
Current Configuration
Partition column: DATE_TIME (DateTime with unique timestamp values)
Detect Data Changes column: UPDATE_DATETIME
Archive: 3 Years
Incremental Refresh Window: Last 7 Days
Power BI Premium Capacity
Import Mode
Unlike our other reports, which use a Date column (CALL_DATE) as the partition column, this report uses a DateTime column (DATE_TIME) that contains unique timestamps (for example, 2026-06-24 10:27:54).
Investigation Performed
I recreated the same configuration in our DEV environment.
Applied Incremental Refresh (3 Years archive + 7 Days refresh).
Used DATE_TIME for the partition column.
Used UPDATE_DATETIME for Detect Data Changes.
Verified Query Folding.
Verified the Native Query generated by Power BI.
Verified that RangeStart and RangeEnd filters were correctly pushed to SQL Server.
Verified that Detect Data Changes generated MAX(UPDATE_DATETIME) queries.
Successfully completed the first Incremental Refresh.
Verified that historical and incremental partitions were created successfully.
Multiple subsequent refreshes also completed successfully.
From the SQL queries captured during refresh, Power BI appears to be following the expected Incremental Refresh behavior by creating partitions and issuing Detect Data Changes queries.
Issue in PROD
The exact same report and configuration behaves differently in PROD.
A Power BI Desktop full refresh completes successfully in approximately 20 minutes.
However, the first Incremental Refresh in Power BI Service runs for more than 4–5 hours and eventually fails.
The refresh history shows:
Data source error
Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Power BI retries the refresh multiple times before ultimately failing.
We also receive the following processing message:
Processing error: An internal service error occurred, causing the cancellation of previous refresh attempts. New attempts will now be initiated.
My Question
Since this report uses a DateTime column with unique timestamps as the Incremental Refresh partition column (instead of a Date column like our other reports):
Is using a DateTime column containing unique timestamps fully supported and recommended for Incremental Refresh?
Can this type of partition column cause longer partition creation times or SQL timeout issues compared to using a Date-only column?
Are there any Microsoft recommendations or best practices for reports that use a DateTime partition column with unique timestamps?
Based on the above behavior (working in DEV but timing out only in PROD), do you believe the issue is more likely related to the SQL Server environment rather than the Incremental Refresh configuration?
Any guidance or official Microsoft documentation would be greatly appreciated.
Thank you.
We have seen similar behavior with Power BI incremental refresh where Desktop succeeds but the first Service refresh fails or runs for hours.
A few key points I would check:
A DateTime partition column is supported
Unique timestamp values should not be an issue by themselves. Power BI should still create partitions based on the incremental refresh policy, not one partition per timestamp.The issue is likely execution/performance, not the DateTime column itself
Since Desktop refreshes in ~20 minutes but the Service runs for 4–5 hours and times out, I would look at Service-side execution differences: gateway latency, Premium capacity pressure, SQL timeout limits, and the actual SQL plans being used during refresh.Confirm query folding all the way back to SQL
The partition filter should fold into a clean SQL predicate similar to:WHERE DATE_TIME >= @RangeStart
AND DATE_TIME < @RangeEndAny casts, transformations, timezone handling, or non-foldable steps before the filter can make Service refresh much more expensive.
Test without Detect Data Changes
Temporarily disable Detect Data Changes and rerun the initial refresh. If it succeeds, the bottleneck may be the additional MAX(UPDATE_DATETIME) checks. If it still fails, focus on the partition query, gateway, SQL performance, or capacity.Review indexing
I would confirm SQL has an index supporting DATE_TIME, and review whether UPDATE_DATETIME also needs to be indexed or included based on the actual generated queries.My read is that the DateTime partition column is probably not the root cause. I would capture the SQL generated during the Service refresh, compare it against Desktop, and review the execution plans for the slowest partition queries.
4 Replies
We have seen similar behavior with Power BI incremental refresh where Desktop succeeds but the first Service refresh fails or runs for hours.
A few key points I would check:
A DateTime partition column is supported
Unique timestamp values should not be an issue by themselves. Power BI should still create partitions based on the incremental refresh policy, not one partition per timestamp.The issue is likely execution/performance, not the DateTime column itself
Since Desktop refreshes in ~20 minutes but the Service runs for 4–5 hours and times out, I would look at Service-side execution differences: gateway latency, Premium capacity pressure, SQL timeout limits, and the actual SQL plans being used during refresh.Confirm query folding all the way back to SQL
The partition filter should fold into a clean SQL predicate similar to:WHERE DATE_TIME >= @RangeStart
AND DATE_TIME < @RangeEndAny casts, transformations, timezone handling, or non-foldable steps before the filter can make Service refresh much more expensive.
Test without Detect Data Changes
Temporarily disable Detect Data Changes and rerun the initial refresh. If it succeeds, the bottleneck may be the additional MAX(UPDATE_DATETIME) checks. If it still fails, focus on the partition query, gateway, SQL performance, or capacity.Review indexing
I would confirm SQL has an index supporting DATE_TIME, and review whether UPDATE_DATETIME also needs to be indexed or included based on the actual generated queries.My read is that the DateTime partition column is probably not the root cause. I would capture the SQL generated during the Service refresh, compare it against Desktop, and review the execution plans for the slowest partition queries.
Hi manoj_0911
- DateTime partition columns are supported and using highly granular unique timestamps can create many small partitions and increase SQL workload compared to a Date only column.
- Microsoft generally recommends Date columns for partitioning because they produce fewer, larger partitions and reduced timeout risk.
- If you must use DateTime, truncate to day-level like CAST(DateTime AS Date) for partitioning while still using UPDATE_DATETIME for change detection.
- The fact that DEV works but PROD times out points more to SQL Server environment or load differences (capacity, indexes, query timeout settings) than to Incremental Refresh configuration itself.
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-xmla
Hi manoj_0911,
Thank you for reaching out to Microsoft Fabric Community.
Thank you babybots and krishnakanth240 for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
Hi @manoj_0911,
We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
