Matillion: the definitive guide

Countless organizations have been collecting data for a long time and they have been trying to leverage data-based insights for even longer. Every business has a set of unique data sources which capture information on various objects like users, accounts, etc. As the complexity of the data ecosystem has increased, so has the number of disparate data sources within any given company. With the emergence of the cloud data warehouse, companies are focused on consolidating their data into a single location to eliminate the data silos that exist between different sources. Conventionally, this process has been known as data integration. The purpose of this guide will be to go over everything about Matillion, which is a tool for data integration.

Understanding Data Integration & Orchestration

In its simplest form, data integration is the process of consolidating data from various data sources into a single unified view. Without data integration, you are collecting data but not acting on it. Data integration enables your business to derive insights on the information collected. The purpose of data integration is to democratize data and remove data silos. The easiest way to understand data integration is to break it down into three pillars.

• Data Acquisition: The place where data is initially collected (ex: Salesforce, Marketo, Amplitude, Netsuite, etc).
• Data Ingestion: The process of moving data from various sources or operational systems to a new location like a data warehouse (ex: Snowflake, Redshift, BigQuery, Synapse, etc.).
• Data Transformation/Preparation: The point at which raw data is transformed and prepped for analysis so that analysts can leverage BI tools like Tableau, PowerBI, Looker, etc. to create detailed reports and dashboards.

Ultimately a modern data stack should be an efficient end-to-end flow from data acquisition and data ingestion to data transformation). Data orchestration sits on top of data integration as another step in the process with a focus placed on managing data and automating the process as it relates to the infrastructure, data pipelines, and workflows required to move data.

Understanding ETL & ELT

Conventionally, ELT (extract, transform, and load) has been the most effective way to handle data integration. This process dates all the way back to the early 1970s and has been the standard for a long time. However, the mass adoption of data warehouses changes this paradigm because businesses realize that they can take advantage of ELT (extract, load, transform) to leverage the power of the cloud to transform their data. Data modeling and transformation tools like dbt have made this easier than ever before.

The main difference between ETL and ELT really lies in the transformation layer. With ETL you transform data before ingesting it and with ELT you transform data after it has been ingested. ETL tends to be a longer process since the data is transformed before ingestion, whereas ELT loads data in a much shorter amount of time. ETL solutions often require a ton of building and maintenance and use custom scripting languages to enable transformations. Even worse, a large portion of ETL solutions are on-premise. On the other hand, ELT solutions use SQL to tackle transformations and are almost always strictly cloud-based and fully automated.

What Is Matillion?

Matillion claims to be an all-encompassing SaaS solution and cloud data integration platform that handles the entire data integration process, all the way through from acquisition and ingestion to transformation. It offers a low-code approach that utilizes the native compute power of popular cloud data warehouse systems. That is to say that Matillion leverages the power of your cloud environment to transform your data. Matillion has two flagship products, Matillion ETL and Matillion Data Loader. Matillion Data loader is strictly used for moving data. On the other hand, Matillion ETL is Matillion's flagship product. It is a more robust ETL solution. Both offer a detailed level of configuration for advanced users.

Matillion Series E Funding

As of September 15, 2021, Matillion announced $150 million in Series E funding. This was led by General Atlantic and several other companies including, Battery Ventures, Sapphire Ventures, Scale Venture Partners, and Lightspeed Venture Partners. To date at the writing of this article, Matillion has raised a total of $310 million bringing its valuation to $1.5 billion.

Matillion ETL Key Features:

• Matillion ETL currently has just over 100 pre-built connectors for various data sources and currently supports Amazon Redshift, Snowflake, Azure Synapse, Google Bigquery, and Delta Lake as destinations.
• In addition to pre-built connectors, you also have the ability to create your own custom connectors to any REST API source system.
• Since everything is low code, you can leverage Matillion’s graphical UI to build orchestration jobs to build sophisticated ETL pipelines.
• You can also build transformation jobs within Matillion by selecting from more than 30 components to create large-volume, complex transformation workflows
• Every job in Matillion can be scheduled to run at a specific time or a regular interval depending on your needs. You can also create generic jobs which can be reused across different projects.
• Matillion also gives you the ability to stage data in your own cloud environment.
• Once your jobs are orchestrated and scheduled in Matillion everything is fully automated. Matillion’s API triggers jobs and its sophisticated flow logic handles the data.
• You can even use custom scripting leveraging languages like Bash, SQL, or Python for specific transformation requirements.
• Since Matillion is hosted in your cloud environment, you can send alerts and notifications directly to Slack or email.

What Is Matillion Data Loader?

Matillion Data Loader is a free solution that Matillion offers to help extract data from your source systems and load it into your target destination (i.e. your data warehouse). Since Matillion Data Loader is free, it comes with fewer features. For instance, you can’t perform any transformations. Currently, Matillion Data Loader only supports around 35 connectors and does not appear to have support for Delta Lake or Azure Synapse as a destination. However, it does support Snowflake, Amazon Redshift, and Google BigQuery. Matillion Data Loader provides a simple wizard to build data pipelines for extracting and loading your data. Matillion Data Loader doesn’t offer any extra developer tools or any automation capabilities. It’s really just a point-to-point tool for moving data.

The Problems With Matillion

Matillion has positioned itself as a SaaS solution, but in reality, Matillion is an iPaaS (integration platform as a service) solution. It supplies you with a platform to enable data integration, but it is up to you to handle all of the nitty-gritty details and set everything up. Matillion’s UI is very intuitive and user-friendly and it can be relatively simple to create various data pipelines and orchestrate ELT jobs. Although Matillion is an ELT platform, it should really be thought of more as a data orchestration platform. That is to say that Matillion enables you to coordinate the execution and monitoring of your data pipelines and workflows.

The problem with this method is that it does not scale well. Depending on your data ecosystem, setting up these data pipelines quickly creates complex workflows. Even worse, you have to maintain and address errors when they inevitably occur. ETL and ELT solutions are meant to free up the time of engineers, but if your engineers are maintaining the pipelines without writing code, you have to wonder how much value you are deriving from the solution.

Additionally, Matillion largely has enterprise customers. This means it does not place any emphasis on SMB/mid-market companies. To be specific, Matillion does not provide a ton of support for smaller businesses.

Matillion Alternatives: Why Fivetran + dbt Is a Better Solution

Whereas Matillion is an iPaaS solution that supplies you with a data integration platform to build ELT pipelines and orchestrate them, Fivetran is a fully managed SaaS platform that manages all of that for you. Fivetran currently supports over 150 different connections for various data sources and around ten different destinations like Snowflake, Azure Synapse, Google BigQuery, Amazon Redshift, Databricks, etc.

Unlike Matillion, Fivetran does not have a graphical UI that forces you to build and connect your various data pipelines and map out all of your workflows. Fivetran simply connects to your data source and you tell it where to load your data. You don’t have to worry about the entire data orchestration aspect or any of the common factors that break data pipelines like random errors, schema changes, execution order, changes in data models, etc.

Similar to Matillion, Fivetran is an ELT solution, but it extracts data from more sources and loads data to more destinations. However, aside from the fact that Fivetran handles all of the data orchestration for you, it is important to note that Fivetran does not currently have any transformation abilities. To be specific, Fivetran only handles the “E” (extract) and “L” (load) aspects of ELT. It doesn’t offer built-in transformations like Matillion.

Thanks to dbt, this is not a problem. If you are not familiar with dbt, it is a transformation tool that leverages SQL. It is extremely efficient at transforming data that is already loaded into your warehouse for analytics purposes. Strictly speaking, dbt gives you the ability to create data models that can be reused. Better yet, if your data models are dependent upon one another, one change in one data model will update another. If you are not using dbt today, then you will end up building it internally down the line. Pairing Fivetran and dbt together creates a flexible solution that is more efficient than Matillion.

What Comes After ETL/ELT?

Once the data is fully loaded into your warehouse and transformed, the typical process is to let your data analysts begin building reports and dashboards to address the questions coming from your key stakeholders. Often this is powered through a BI tool like Tableau, PowerBI, Thoughtspot, Looker etc.

There’s still a major problem though. All of this data that you spent so much time and effort trying to consolidate and transform in your warehouse is now siloed because now the only people who have access to that information are your analysts. A data silo is just a collection of information that is not accessible by the other parts of your organization. If your data only lives in a dashboard, it’s only useful for high-level business decisions and not actually actionable by your other team members.

Dashboards are practical for identifying trends and showing a zoomed-out view of your data, but they are not useful for associating and merging data collected by other sources to an individual user. In reality, your Sales and Marketing teams are often asking questions like “Who is the most active user in an account?” or “Which contacts have downloaded X?” “Which customers are in an active deal or POC?” or “What is the annual recurring revenue of company ABC?”, all with the intent of improving personalization.

Answering these questions often involves going to your data teams and requesting the information. In most cases, your data team already has a list of backlogged items they need to address, so they don’t have time to build an entirely new data pipeline to push this information out of the warehouse and into the tool they requested. By the time this data is made available in a CSV, it is unusable because the customer or prospect has already moved on to another point in their journey.

Why You Need Reverse ETL

“Reverse ETL is the process of copying data from the data warehouse to operational systems of record, including but not limited to SaaS tools used for growth, marketing, sales, and support.” The data warehouse should be your standard of truth. However, the information it houses should not be inaccessible to your other teams. It should be democratized and made available to everyone within your organization so they can leverage it day in and day out to make decisions that can positively affect your bottom line.

Reverse ETL tools like Hightouch solve this problem by taking the transformed data out of your warehouse and syncing it back into the native tools of your business teams (i.e. Salesforce, Hubspot, Marketo, Braze, Amplitude, Asana, Google Ads, etc.). Better yet, Hightouch leverages all of the data models built in your warehouse (ex: PQL, MQL, SQL, lifetime value, propensity scores, customer health scores, churn rate, overall product usage, etc.) and pushes this information to the destination of your choosing. You can even send information directly to Slack to notify your sales, marketing, product, or customer support teams to take action in real-time based on the criteria defined in your data models.

Reverse ETL is the final piece of a modern data stack. When it is added as another layer on top of data integration it makes for some really interesting discoveries because it allows you to iterate and experiment at a speed that is simply not possible otherwise. Likewise, when the transformed data is taken out of the warehouse and pushed back into the operational systems of your business users it creates a single source of truth across the entire organization because now the information that was in your warehouse is in the hands of everyone.

Why Can’t I Use Matillion for Reverse ETL?

ELT solutions like Fivetran and Matillion read from the source and write to the warehouse. Reverse ETL solutions like Hightouch read from the warehouse and write to the source. The process between the two is completely different. Matillion actually reads and writes in both directions for some connectors, but this only provides some underbaked capabilities because Matillion specializes in ELT, not reverse ETL.

Summary

All in all, Matillion is a tool for data integration that places a heavy amount of work on your team. Matillion is not the industry standard data integration tool. A modern ELT product like Fivetran and a dedicated transformation tool like dbt provide the best basis for creating a modern data stack. If you are using ETL/ELT, Reverse ETL is the missing piece in your architecture to activate your data.

This alternative ETL approach provides the best way to create actionable insights. At the end of the day, cloud data warehouses help power business intelligence and analytics, but they do little to leverage and democratize that data for day-to-day operations to improve the overall customer experience. This is the exact reason Reverse ETL is so valuable.