Quite simply the objective as follows: Move data from Azure SQL Database to Azure SQL DW via Azure Data Factory v2 (ADF).
Before I begin, what exactly is Azure Data Factory? At an extremely high level it is a managed cloud service that is built for complex hybrid extract-transform-load (ETL), extract-load-transform (ELT), and data integration projects. (Currently version 2). I am going to use this tool to load data into an Azure SQL Data Warehouse. How does Data Factory do this? A process called Copy Activity. Copy Activity goes through the following stages to copy data from a source to a sink.
Read more about it here: https://docs.microsoft.com/en-us/azure/data-factory/copy-activity-overview
Enough of the Theory – Let’s Get Practical
This process assumes 3 things. You have an Azure SQL Database (source), an online Azure SQL DW (target) and a storage account. Under common tasks within the data warehouse you will see the Load Data option.
As it states below – ADF is a “Cloud-based data integration service that orchestrates and automates the movement and transformation of data. Use ADF to load data from various data sources and monitor your ingest, transform, and publish steps through a rich visual interface”
Soon as you click ADF – Azure Data Factory you will be asked to create one.
Once created naturally we need the load data option.
Now it is a case of working through the wizard, more specifically the below steps 1-6.
- Properties – where you set the schedule.
2. source – Have a browse through, many connectors are available.
Here I select SQL Database hence need the tables.
3. Destination – The Azure SQL DW and mapping where required.
4. Further settings around performance – using polybase.
5. A nice summary.
6. Deploy – watch those pipelines get created.
Once completed you will have an Azure SQL DW with data that has come from a SQL Database.