In a recent article dedicated to introducing – a tool for creating and managing ETL processes, I promised at the end that we would try to create a pipeline in the next article. If you are not familiar with this ETL framework, I recommend going through the introductory article.

Source and Destination Databases for ETL Pipeline

In this exercise, our task will be to create an ETL process using that copies the “Address” table from one instance to another, without any transformations – simply a 1:1 copy. First, let’s take a look at where we will be doing our data ETL pump from.

Source Table and Database for ETL – Adventureworks Azure

Our source will be a publicly accessible database Adventureworks, operated by sqlcentralcom. This OLTP database is hosted on Azure and contains several tables as seen in the screenshot. For the purposes of the ETL pipeline, we will only be interested in the following:

  • Host:
  • Database: AdventureWorks
  • Schema: SalesLT
  • Table: Address
  • User: sqlfamily
  • Password: see link to sqlcentralcom.

Additional information – the classic port 1433


So, we know the source, and we have everything we need to create a connection string.

Destination Database for ETL – Data Warehouse

For the destination database, we envision a data warehouse – which, for illustrative purposes, is operated on:

  • Macbook M1 Air 16GB RAM with Windows running via Parallels
  • SQL server running via Docker on iOS
  • instance running on iOS

We manage SQL Server and on Windows using Management Studio and a browser.

  • Host: 192.168.XXX.XXX
  • Database: AW_FULL_LOAD
  • Port: 1433
  • Login: integration_account
  • Password: *****


Now we have everything we need.

Creating the ETL Pipeline in

Creating the ETL will consist of several steps. First, we need to:

  • Configure new databases in the io_config.yml file – we don’t want passwords in the configuration file, so in Mage, we will use the secrets management function and refer to them in the configuration.
  • Next, create a new pipeline and add 2 blocks to the new pipeline
    • data_loader – connects to our source database and retrieves data
    • data_exporter – connects to the destination database and uploads the data there
  • Run the entire pipeline and check it

This configuration needs to be done only once for each new data source or target.

Configuring the io_config.yml file in

We will enable the Mage web server and take a look at how the file actually looks. We can see that after installing, there are some sample settings in the file, and these settings are under the default profile.


This profile is “templates repository.” We will look for the MSSQL section, copy it to the end, and modify the parameters to establish connections to our servers. We will create a new profile for each server. You can see that I have created 2 profiles – one for Adventureworks and one for the data warehouse.


You may have noticed that we don’t have passwords in the configuration file – we never want that. In the application, we went to Secrets and created 2 new secrets. We will refer to these secrets in the configuration file.


Creating the data_loader in the pipeline

First, go to the pipelines section and create a new pipeline – standard batch type. We will name it appropriately later.


After creating the pipeline, it takes us to the environment where blocks are created – our data_loaders and data_exporters. First, let’s create a block that connects to our source data.

Click on Data loader – python – databases – MSSQL


After creating the data loader, it takes us to the pipeline definition, and we see a new block on the right. At the same time, a window for this Python block has opened. As you can see, everything is prepared, and we just need to slightly modify the code – we don’t have to write it, takes care of that for us.

The only two things we need to modify are the query and config_profile.


For config_profile, choose our profile from the configuration file: AdventureWorks, and for the query, write the query for our table

We test if the data loads – it looks okay.


Creating the data_exporter in

In the previous step, we have a block that can import data. Now we need to prepare a block that will upload this data to our destination database. This is even easier than with the data_loader. Click on Add Data Exporter and edit the pre-prepared Python script.


You can run the data_exporter and check if it completes successfully – everything is okay.


Your ETL pipeline should be configured and ready to go.

Running the Mage Pipeline and Testing the Data

It’s time for a small test. Let’s try running the Mage pipeline from the application and then check the destination in the data warehouse to see if the data is there.

You can run the pipeline, for example, by running it once. Another option is to schedule it, and in this case, the ETL will run at a predefined time. In the screenshot below, we run the pipeline, and you can see that it completed successfully. pipeline completed

In the imaginary data warehouse database, we have new data:

>> For more articles on please see category page

5/5 - (1 vote)

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *