Best way to connect two SQL Data Data Warehouses

Question

I'm dealing with a problem where we have 2 different organizations, each with their own data warehouse. Both data warehouses will be migrated from on-prem environments to Azure and use SQL Data Warehouse. Currently, they are just sharing data via SFTP but when migrating to the Cloud they want to know what would be the best option to replace that dated integration.

Are there any Azure Products that would help integrate the two? Is there an ODBC equivalent in Azure?

Answer 1

@Mike Dean currently there is no direct connection like ODBC available, but there are other options.

Azure Data Factory facilitates data movement and orchestration, and is like the cloud version of SQL Server Integration Services (SSIS). It has built-in connectors for Azure SQL Data Warehouse / Azure Synapse, and scheduling abilities. Typically, Data Factory is the one to initiate pulling the data. Here is a How-to involving loading into Synapse/ Data Warehouse.

A more passive option would be to use External Tables and External Data Sources. The External Data Sources can target a location in your Azure Storage Account (typically blob) where data resides (often partitioned). If both of your Warehouses have identically configures External Data Source, and External Table, then they would both be operating on the same data.

These are the top two recommendations I have for you. Other approaches are possible. Please let me know if this helps, or if you have further queries.