Data providers typically deliver data in formats such as parquet, JSON, or CSV. In traditional data warehouse scenarios, these files often land in a persistent staging area. Many organize these stag...
He use OpenRowset in all our projects.
Allow us
- Implement data Virtualization, and avoid Staging tables
- Grant access to data that is in the LZ but not in the User SQL tables with Views_LZ
Important to have
- The ALDS / OneLake must be under firewall ==> Private endpoint compatible (**)
- Use SAS or Acces Keys . . .., But compatible with AAD (User MI or System MI) (**)
- Most of the Cases we use Authentication SQL <==> Storage: Delegation / Impersonate
- In some cases for audit purpolses we need use Pass-Through Authentication, thus the AAD SQL user will appear on ADLS logs
- The T-SQL Openrowset must be able to compile on Azure Data studio & Azure DevOps, currently is not able to do it and block all CI/CD progress (**)
- Format files (FMT) are really obsolete, need maintenance and are not part of Database Project / GIT, need to find a 2024 replacement (**)
- We hope that Select disctinct (col_1) [for Dim Tables] On parquet will be faster than on CSV, but sometimes is not (**)
- This Synapse "sugar" is very appreciatee: '*/*/*/myfile*.parquet'
- Currently select from openrowset INTO #Temptable is not supported (**)
- All the others: AGG pushdown, Metadata, Vertipaq / V-Order, PurView , , . . . future??
(**) Hugo Queiroz knows about these issues and some of them are ACK by Azure support
