The native query is an excellent solution in big projects/companies where you can not easily create another view or table in the databricks catalog (especially when you have read-only access :-) ).
Great video! I had this intuition, and for me the main reason I didn't like to put a query in there is that it will always be more hidden and easier to forget there might be a data transformation in there. Anyway for me the big question is: why don't they just add to the connector the capacity to connect to an SQL query in databricks? It is easier to have access to create a query than a view. The connector to SAP BW for example connects to BW queries. Why can't we have the equivalent in Databricks? Is there any problematic downside I'm not seeing?
Two questions as I watch this, 1. Even with an assumption that Query folding is not possible, is that big of a deal if you expect the users to put in the filter conditions/grouping/aggregations in the Query itself? 2. For Power BI reports with low latency requirements, (.i.e. users cant wait for longer to get the results after a click) isn't doing calcs in Power BI a better option?
One related question... (maybe it is a stretch :) )... We are having the medallion architecture, what is the best option of authoring tabular models to be deployed as Power BI datasets? I want to use tabular editor, but not connecting to Databricks views in gold layer use the Simba ODBC. There is a way to gather metadata in Power BI Desktop, and paste it into Tabular editor. But it is cumbersome development expericence. One option is to use Power BI Desktop to author the model, deploy it, and through Tabular Editor get the xmla. Then you can add that to repo etc. But still.. adding tables you need that cumbersome workaround producing table metadata in Power BI Desktop. Do you have any suggestions? Or is it up to Tabular editor to add this functionality?
I went into this video with my mind already made up - Native queries are bad. Nice to have my mind changed. Not sure I'll use the functionality, but at least I know that it exists and it's not useless.
The thing with query folding is that if it's broken, the predicates aren't passed through to Databricks, so it doesn't know what to optimise. The fact that this folds queries by default at least gives the spark engine a chance