Forum Discussion

Suzanne_Shuford's avatar
Suzanne_Shuford
Frequent Visitor
6 months ago
Solved

Data Flow Gen2 ODBC connection to AWS for Incremental Refresh

 Hi, I am in higer Ed and we are not currently on Fabric License.  I have a query against an AWS Athena table using  an AWS ODBC Connection. The table is upward of 17 million rows and approximately...
data engineering
lakehouse
  • Vinodh247's avatar
    Vinodh247
    6 months ago

    your incremental refresh is not working because Dataflow Gen2 incremental refresh requires query folding at the source, and ODBC to AWS Athena does not properly support folding in Fabric.

     

    When you use Odbc.Query() with a manually constructed SQL string, Fabric treats it as a native query. Native queries break folding. Even if Power Query preview looks fine, the Fabric service cannot push partition filters back during refresh. So every refresh scans everything again. That is why your 2nd run is slower. It is reading data again, not refreshing partitions.

     

    Key issues in your setup:

    1. Odbc.Query() = no folding recognition by Fabric incremental engine.

    2. “Does not require folding” on reference query = incremental refresh will not partition.

    3. Manual SQL string with RangeStart/RangeEnd = Fabric cannot inject partition filters dynamically.

    4. Athena ODBC driver does not expose metadata in a way Fabric can fold date filters automatically.

    Incremental refresh in Dataflow Gen2 works only when:

    • The source supports folding.

    • The date filter step using RangeStart and Range End is directly applied to a foldable source step.

    • You do NOT use a hard-coded SQL statement.

    With Athena via ODBC, this rarely works.

     

    As a fix, I would suggest move ingestion using:

    • AWS glue job -> parquet partitioned by date

    • or replicate using ADF/fabric pipelines

    • Then use fabric Lakehouse incr refresh on Parquet/Delta (fully supported).

    Right now you are trying to force incremental refresh on a connector that does not properly support folding. That is the root problem.

     

Vinodh247's avatar
Vinodh247
Icon for Super User rankSuper User
6 months ago

your incremental refresh is not working because Dataflow Gen2 incremental refresh requires query folding at the source, and ODBC to AWS Athena does not properly support folding in Fabric.

 

When you use Odbc.Query() with a manually constructed SQL string, Fabric treats it as a native query. Native queries break folding. Even if Power Query preview looks fine, the Fabric service cannot push partition filters back during refresh. So every refresh scans everything again. That is why your 2nd run is slower. It is reading data again, not refreshing partitions.

 

Key issues in your setup:

  1. Odbc.Query() = no folding recognition by Fabric incremental engine.

  2. “Does not require folding” on reference query = incremental refresh will not partition.

  3. Manual SQL string with RangeStart/RangeEnd = Fabric cannot inject partition filters dynamically.

  4. Athena ODBC driver does not expose metadata in a way Fabric can fold date filters automatically.

Incremental refresh in Dataflow Gen2 works only when:

  • The source supports folding.

  • The date filter step using RangeStart and Range End is directly applied to a foldable source step.

  • You do NOT use a hard-coded SQL statement.

With Athena via ODBC, this rarely works.

 

As a fix, I would suggest move ingestion using:

  • AWS glue job -> parquet partitioned by date

  • or replicate using ADF/fabric pipelines

  • Then use fabric Lakehouse incr refresh on Parquet/Delta (fully supported).

Right now you are trying to force incremental refresh on a connector that does not properly support folding. That is the root problem.