NativeQuery is a table function that helps you to express queries in the native SQL dialect of your source. It is commonly used on drivers that do not support ExecModel as it is useful if you want to utilize source's specific features such as window functions, Common Table Expressions (CTE) or the CONNECT BY option in Oracle. It is typically suggested for edge cases.
NativeQuery is compatible with sources that do support native-SQL. This function can be used after a FROM clause to execute a query using source’s-native SQL. For example:
SELECT * FROM NATIVEQUERY('SELECT * FROM UNNEST([1,2,3])')
This query will execute the inner query in source directly and return the results. This will work even in tools which are not normally compatible with QueryPassthrough=true.