Data Flow Gen2 ODBC connection to AWS for Incremental Refresh

Hi Suzanne,

What you’re seeing is usually expected with ODBC + “hand-built SQL” in Dataflow Gen2: the incremental refresh feature only behaves incrementally when the RangeStart/RangeEnd filter step can be query-folded back to the source. When we inject RangeStart/RangeEnd into a SQL string (or use Odbc.Query directly), the engine often can’t detect/validate folding, so it ends up re-reading more data than you expect.

Here are two practical paths:

Option A (try to make it fold):

1. Keep RangeStart / RangeEnd as Date/DateTime parameters (don’t convert them to text for the main filter logic).
2. Use a normal Power Query filter step on your date column first (e.g., Table.SelectRows with >= RangeStart and < RangeEnd) and only then rely on folding.
3. If you must use a native query, wrap it with Value.NativeQuery and pass the folding option (where supported) instead of Odbc.Query. This is the pattern Microsoft documents for enabling folding with native queries:
   • Value.NativeQuery(Source, SqlText, null, [EnableFolding=true])

If Athena’s ODBC driver still doesn’t fold reliably, incremental refresh will not be effective (and refresh times can even grow on subsequent runs).

Option B (recommended for Athena/ODBC): do incremental outside Dataflow IR If folding can’t be guaranteed through the Athena ODBC driver, the reliable approach is:

• Land data into OneLake/Lakehouse via a pipeline/Copy activity (or another ingestion method that can push predicate to Athena),
• Use a watermark (max accounting_date / last_updated) and load only new/changed rows,
• Then MERGE into a Lakehouse Delta table. This avoids depending on ODBC folding and gives you deterministic incremental behavior.

Also note: Dataflow Gen2 incremental refresh updates destination buckets using replace, not append, so make sure your bucket size is appropriate (e.g., daily/monthly) and that your “change detection” column is a true last-updated timestamp if you want updates within the same date range handled correctly.

Docs reference for Dataflow Gen2 incremental refresh behavior (bucket replace): https://learn.microsoft.com/fabric/data-factory/dataflow-gen2-incremental-refresh

Docs reference for enabling folding on native queries (Value.NativeQuery + EnableFolding): https://learn.microsoft.com/power-query/native-query-folding

Hope this helps — if you can confirm whether “View Native Query” is enabled/disabled on the filter step (or whether Athena ODBC supports folding for your query), we’ll know immediately whether Option A is feasible or if Option B is the best route.