Is a Custom SQL Query considered as non-folded?

[Anonymous]

Hi,
I am playing with Incremental refreshes on a "Ticket Table",  Unfortunately, the field that I am using to flag new/changed data is a "date closed" field, which is nullable.

What I have done in this instance is create a custom SQL Query, which I am using in the datasource.  The query is similar to this:

SELECT *,ISNULL(TicketCloseDate,GETDATE()) AS IncrementalDateFeed
FROM MyTickets

Apart for the RangeStart and RangeEnd filtering, there are no more transformations, but, as expected, this is showing that this query could not be determined as foldable.

While it works, I have not done enough testing to determine whether it is actually gaining me any performance with my refreshes.

Is this a sound approach?

Or, would I be better advised to create and call views on the source database?

Thanks

[AntrikshSharma · Accepted solution]

@Anonymous If you use SQL code for importing data then Query Folding breaks, however what you can do is first create a connection to the database in PQ and then add a new step that will use the function Value.NativeQuery

let
    Source = Sql.Databases ( "autumn\sql2019" ),
    ContosoRetailDW = Source{[ Name = "ContosoRetailDW" ]}[Data],
    //Query is now folded                     
    EnableFolding = 
        Value.NativeQuery (
            ContosoRetailDW,
            "SELECT * FROM DimProduct",
            null,
            [ EnableFolding = true ]
        ),
    FilterRows = 
        Table.SelectRows ( 
            EnableFolding, 
            each [ProductKey] <> 3
        )
in
    FilterRows

But I would suggest you create a View so that you can add an additional layer between tables and PQ, makes it easy to govern and DBA would know if a View depends on a table and will modify accordingly.

[ImkeF]

Hi @Anonymous ,
I'd recommend to use Profiler to check what is actually going on, as the folding indicators in PQ itself are not reliable.
Usually, once you've used custom code in your initial SQL call, this command itself will fold, but nothing afterwards. So your incremental refresh parameters wouldn't. So using a view looks like the way to go here.

[Anonymous]

Fair point.  I did think that was going to be the answer.  I think I was looking for the easy answer out.

By the way, when you say "profiler" are you suggesting the profiler in SSMS (or Redgate)?