Recommended tools to ETL data from Azure SQL Database to Azure SQL Data Warehouse? [closed]

Question

I have a requirement to build a data warehouse using Azure SQL Data Warehouse, sourced with data from a single Azure SQL Database. Looking at this article, it seems I have two options:

• SSIS
• Azure Data Factory

My Azure SQL Database is normalized, but I'd like to build my Azure SQL Data Warehouse in a star or snowflake schema format, so it's not a straight copy of tables - there definitely needs to be some transformation happening.

Given SSIS isn't supported on Azure SQL Database and would require me running a VM with SQL Server on it to keep my processes entirely in Azure, is Azure Data Factory the recommended tool to ETL data between Azure SQL Database and Azure SQL Data Warehouse? Would one choice vs. the other be more fully featured in this situation?

Thank you!

Answer 1

If you are looking to keep your processes entirely in the cloud and not spin up a VM, then Azure Data Factory is currently your best option for data movement. However, at this point in time I might recommend an ELT approach over an ETL approach. By loading your data first and then completing your transformations within the SQL Data Warehouse your loads will be quicker and you will be able to take advantage of additional compute power when transforming your data. Additionally, it sounds like you are familiar with SQL, so it may be easier for you to write your transformations inside of SQL Data Warehouse as opposed to using Data Factory' JSON.