Item: Matillion
Rating: 10
Author: Verified User

Use Cases and Deployment Scope

Matillion is the primary ELT software used by our Data Engineering team to provide ELT orchestration for both our own and clients' AWS data platforms which are primarily built around Amazon Redshift.

Matillion allows us to rapidly develop and deploy ELT processes across our environments. With its native AWS support, it integrates seamlessly with the rest of our platform, enabling easy deployment, support, and monitoring of our Redshift workloads.

Pros and Cons

Very intuitive and easy to learn
Native integration with AWS services
Aggressive roadmap with continual enhancement
Competitive pricing
Push-down SQL - all transformations are native Redshift SQL, meaning fairly easy decoupling

More granularity for security permissions
Better orchestration of job scheduling/execution (e.g. setting maximum execution time, managing queued jobs)

Return on Investment

Low cost of Matillion has allowed us to power big workloads for a comparatively small outlay.
Fast speed of development has allowed quick delivery.

Usability

Extremely easy to use, with an intuitive interface and direct correlation between transformation types and the underlying Redshift commands. UI comes with in-place help documentation linking to both Matillion's own knowledge base and AWS Redshift documentation making it very easy to diagnose issues and understand component use cases.
In-place data sampling and counting, as well as SQL view, makes it simple to see what your transformations are doing without having to run the whole job end-to-end.

Built-in notation allows developers to comment as they go, providing additional context. It has a very simple drag-and-drop UI.

Time to Value

We ran a brief proof of concept which was supported by Matillion themselves. We were able to get up and running within an hour, and due to the direct correlation between transformation tasks and Redshift commands, we were able to understand the components available and map out our workloads quickly.
We ran our PoC for a period of around a week and were able to begin developing real solutions almost immediately upon purchase of the license.
Being able to sample data and see data lineage as we build out jobs makes it incredibly simple to test as you go.

Scalability

Because Matillion operates in a push-down manner, most of the workload is actually carried out on the data warehouse cluster. Matillion acts as the orchestration for these transformations but is relatively untouched by even the biggest, most complicated transformations.

Matillion scaling is vertical (scale up) rather than horizontal (scale out). The scalability of concurrent users is tied to the license model and only offers two concurrent users at its base level. This is fine for small teams but may prove a challenge for larger organisations. However, even the larger license options are fairly budget-friendly.

Matillion also supports an active-active high availability configuration, allowing clustering of additional instances to provide failover in the event of an issue.

Alternatives Considered

Pentaho and Talend Data Integration

Being a fairly new player in the market, Matillion is coming up against some well-established names such as Pentaho and Talend. The primary benefit for our use case was the fact that Matillion is built specifically for Amazon Redshift. While Pentaho and Talend are more mature products with some advantages (e.g. Talend's suite of data quality management tools, or Pentaho's customisation), neither are Redshift-specific products. While they support Redshift and AWS generally, they also have to cater for similar requirements across other database and platforms, which means they are not always doing things in the most optimal way for Redshift workloads or may take additional configuration.

Matillion's native AWS integration and direct mapping of features to Redshift functionality mean it squeezes every drop of performance possible out of Redshift and operates in the most optimal manner.

Think of it like performing surgery. While you could use a Swiss army knife because it supports that among other things, you might prefer to use a scalpel since it has been specifically designed to provide precision and performance for that specific job.

Other Software Used

Amazon Redshift, AWS Lambda, Amazon DynamoDB, Amazon Elastic Compute Cloud (EC2), Amazon S3 (Simple Storage Service), SQL Server Integration Services, Microsoft SQL Server

Likelihood to Recommend

When working with Amazon Redshift, Matillion is the best ELT software I've seen. If you have Redshift as part of your project, you should absolutely consider it.
If you're planning on an ELT approach and loading the data to your warehouse before transformation (as opposed to ETL, transforming the data outside of your warehouse), then Matillion's push-down SQL is ideal. It will use the power of your data warehouse to transform data via native SQL operations rather than expensive in-memory operations on its own server.

If you're not working with Redshift (or Snowflake, Azure PDW, or Google Big Query, which are also supported) then Matillion isn't for your use case as it is 100% designed around cloud-native MPP data warehouses.

Users and Roles

9 - In our organisation, the primary users of Matillion are our Data Engineering team. They are responsible for building our data pipelines and processing client data for our data warehouse.
In addition to our Data Engineers, our Cloud Services and Solution Architecture teams use Matillion to support with stack operation and proof of concept developments retrospectively.

Straightforward, native ELT for cloud-based Data Warehouse solutions

Overall Satisfaction with Matillion