RangeStart and RangeEnd in Incremental Refresh

[nottyheadedboss]

My client has a 135 GB data set in a delta table on Azure DataBricks that has been setup for the purpose of a PoC which needs to be displayed on to a Power BI a report.

This is the data count i have for the PoC I am working on

month_num: - row count

202307: - 956,709,036

202308:- 934.470,054

202309: - 937,174,626

202310: - 978,703,128

202311: - 2,320,280,532

I have a set up an Incremental Refresh for a period of 5 months and daily refresh for 30 days.

I am setting the Power Query using the month_num by converting the datatime to Integer via a Function. However, when the Periods are set specifically as above, why does my RangeStart start from "0001,01,01,00,00,00" and RangeEnd end with "9999,12,31,23,59,59".

RangeStart = 

#datetime(0001,01,01,00,00,00) meta [IsParameterQuery=true, List={}, Type="DateTime", IsParameterQueryRequired=true, Description="autogenerated_for_incremental_refresh"]

RangeEnd = 

#datetime(9999,12,31,23,59,59) meta [IsParameterQuery=true, List={}, Type="DateTime", IsParameterQueryRequired=true, Description="autogenerated_for_incremental_refresh"]

PowerQuery aimed at Query Folding for DataBricks

let

    Startdate=DateKey(RangeStart),

    EndDate=DateKey(RangeEnd),

    Query = "select * from hive_metastore.default.po_bi_poc where month_num_part >= " & Text.From(Startdate) & " and month_num_part <= " &Text.From(EndDate),

    Source = Databricks.Query("adb-**********.azuredatabricks.net", "/sql/1.0/warehouses/**********", [Catalog = null, Database = null, EnableAutomaticProxyDiscovery = "enabled"]),

    Result = Source(Query),

    RowDataLimiter = if DataSize <> 0 then Table.FirstN(Result, DataSize) else Result

in

    RowDataLimiter

1. Also why does the Query Execute for the Complete Data to analyze potential issues?
2. 
3.  
4. Is it possible to restrict this. as mentioned above I have 135GB of data. will this be executed against all of that when I am restricting the data to 10 using the DataSize parameter.
5. Is it possible to set the DataSize parameter against the Query such a LIMIT DataSize?

     Thanks

[AnkitKukreja]

Hi @nottyheadedboss 

To work with incremental refresh the requirement is to work with date/time columns else to convert it to integer we need specific steps to be followed. Please have a look at the following document for further guidance and there would be previously solved posts on your requirements as well. Try to search for them as well.

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure#convert-dateti...