--- title: "Matillion ETL" canonical: "https://docs.vaultspeed.com/space/VPD/3012952079/Matillion%20ETL" format: markdown --- > Macro (toc) ## General information Matillion ETL is a cloud-based ETL tool created to work in sync with cloud databases like Snowflake, DataBricks, and Synapse. It is purpose-built for cloud data integration and transformation. You can find more information on this tool on their website: [http://www.matillion.com](http://www.matillion.com) VaultSpeed will generate all the code necessary to automatically create jobs in Matillion ETL to load your data to your data vault on Snowflake, DataBricks, or Synapse. By default, VaultSpeed creates a VaultSpeed folder with subfolders for each object to load. These subfolders will contain at least two jobs, one orchestration job, and one or more transformation jobs. ![image](media://07dc825f-a4e4-435f-913d-6bc8c9ab98bf) ![image](media://b00e24a7-aeb6-418f-ac84-099427eb3e27) ![image](media://bd75dd8b-c3af-49cd-82d2-88a12eec6357) Additionally, VaultSpeed will generate workflows for Airflow to run all these jobs. > 📝 **Supported Software Packages** > 📝 > 📝 Matillion currently supports two types of software packages; one that customers can host on a machine (VM) and the other being the recently introduced Data Productivity Cloud (DPC). > 📝 **VaultSpeed Support Limitation** > 📝 > 📝 At VaultSpeed we provide assistance to customers using Matillions customer hosted VM solution and do not offer support for the new Data Productivity Cloud offering. The DPC is a Software, as a Service (SaaS) product that we have not yet integrated with. ## VaultSpeed implementation ### Technical setup #### Matillion In Matillion, create a Project and a Project Group to deploy the jobs. #### VaultSpeed agent First, make sure your agent’s version is at least 4.2.5 or higher. You can check this in the client.properties file of your agent. At the top, there is a line with the version number (e.g.: VaultSpeed version: X.x.x) Inside your VaultSpeed agent, you need to define a connection that can handle both Snowflake, DataBricks, or Synapse and Matillion. To do so, add a new connection inside of the “connections.properties” file for your agent, with the following properties: | | | | --- | --- | | URL | a JDBC connection string to your snowflake/databricks/synapse environment | | deploy_url | URL of your Matillion ETL instance | | deploy_user | Login user for Matillion ETL | | deploy_password | Password for Matillion ETL | | deploy_project | Your Matillion ETL project name | | deploy_group | Your Matillion ETL group name | Example (for snowflake): ``` matillion.url="jdbc:snowflake://.snowflakecomputing.com?user=xxx&password=xxx&warehouse=vaultspeed&db=datavault_matillion" matillion.deploy_url=http://localhost:3020 matillion.deploy_user=vaultspeed matillion.deploy_password= matillion.deploy_group=vaultspeed matillion.deploy_project=matillion_demo ``` With this connection set up in your agent, you need to define a database link of the type ‘Snowflake’ with your in VaultSpeed. ![image](media://b8ff24d8-d59e-4f63-9e09-cc170251f39d)   The same applies for DataBricks or Synapse, just use the correct JDBC connection string, and don’t forget to select the correct database type in your VaultSpeed DB links screen. #### Airflow In Airflow (once you have installed the VaultSpeed plugin for Airflow), you need to define two connections. One to your Matillion instance and one to your Snowflake/DataBricks/Synapse database. More information on Airflow and generating flows can be found here: [https://vaultspeed.atlassian.net/wiki/spaces/VPD/pages/3012624432](https://vaultspeed.atlassian.net/wiki/spaces/VPD/pages/3012624432) ### Generation of the jobs Once the setup is done, you can generate all the code using VaultSpeed. In the DDL/ETL generation screen of VaultSpeed, you can choose the ETL Generation Type = Matillion Snowflake / Matillion DataBricks / Matillion Synapse. This will generate all DDL specific for the correct database and the ETL code for Matillion ETL jobs. ![image](media://ad5ad998-9fa9-4d5d-8b0f-79d29ef17dca) ### Generating workflows to run the jobs with Airflow You can generate workflows to run your Matillion ETL jobs in the Flow Management Control screen. Ensure you fill in a Data Vault Connection Name (=Snowflake/DataBricks/Synapse connection) and ETL Connection Name (= Matillion connection) for your flow. Airflow will reference these names from the connections you defined inside of Airflow earlier. Make sure these names match. ### Deployment to target database and Matillion Once you have created all the DDL and ETL for your target database and Matillion and the execution flows, you can deploy them easily using the automatic deployment screen from within VaultSpeed if you have configured the correct connections in your VaultSpeed Agent. ![image](media://d9d7758d-58d1-4b82-b6c7-d8ff08eff71c) #### VaultSpeed Automatic Deployment Once your agent has this correct setup, you can use the Automatic Deployment screen within VaultSpeed to deploy the code. Click on the line with your generated DDL, ETL, or COMP code and click the Deploy button. In the Deploy menu, choose the Database Link option and use the database link for your connection to Matillion that you defined. ![image](media://564acca9-8510-430b-8e30-09dc9f29e4df) Now your agent will deploy all DDL code to your target database environment using the JDBC connection and deploy all ETL jobs to your Matillion ETL instance. You can see the jobs appear there while they are being deployed. ![image](media://b161ab10-2e00-4006-9279-8dcb44dd9d86) ### Running the jobs Once the DDL is on the database, the jobs are deployed to Matillion, and the FMC code is ready on Airflow, you can run the code. You can run the flows inside of Airflow just as usual. Airflow will now use the Matillion Rest API to call the correct Orchestration jobs. ![image](media://eb8416ea-5e88-424e-8da6-78d7cbf4a994) Inside Matillion, you can also monitor the running or finished jobs from the Tasks Menu. ![image](media://c8fc6e03-c38f-4ee7-add4-1b25478baafc)