Alteryx and dbt are two data transformation programs we use most often at The Information Lab Netherlands. But which of these is the best option for your company?
First, let us begin by explaining what we mean by data transformation.
Imagine you have two tables. In the first tables you have customers names and country of origin, and in the second you have customers names and the product they bought. If your goal is to know how many products are purchased by country, then you need data transformation.
Now let's look at both options.
Alteryx
Alteryx can actually do much more than data transformation: with Alteryx , you can connect to APIs, perform complex geographic analysis and even do machine learning. Often, when a company chooses Alteryx, that choice comes from one or more of these capabilities.
Benefits of data transformation in Alteryx
Alteryx is a drag and drop tool. That means that, despite being SQL-based, users do not need to have SQL experience. It is enough to understand the underlying logic, and this lowers the barrier to entry for Alteryx users. The fact that it is so visual also makes it easier for colleagues to quickly understand what a workflow actually does, as compared to a SQL script.
Alteryx does not use Git, the well-known version control system. This is the biggest drawback of Alteryx as far as I am concerned.
Disadvantages of data transformation in Alteryx
The way you can collaborate with your colleagues in Alteryx is as follows. Someone creates an Alteryx file and saves it (either on Alteryx Server or on your own computer). Let's call this file "Workflow A." If someone else, or also the same person, wants to modify Workflow A, they have two options:
- They can create a copy of Workflow A and add modifications within that copy. This way we still have a history of files, but it can cause confusion: which version is correct? Which one is the most recent? What are the differences between the two files? These questions are often not quick to answer.
- The other option is to modify Workflow A directly within the same file. However, this can still create several risks. By saving modifications within the same file, we immediately lose history: we don't remember what the file looked like before, who modified it and what modifications were added. This means that if something goes wrong, it's not easy to go back to the previous version.
-> SUGGESTION: If you work with Alteryx, make sure to set up proper documentation within your company to keep a description and explanation of each customization.
Advantages of Alteryx:
- users do not need to know SQL
- visual
Disadvantages of Alteryx:
- no Git: harder to collaborate
- hard to go back to the previous version of a file
dbt
dbt is very different.
Data transformation in dbt: powered by Git
dbt is based on SQL. If you work with dbt, then you must be able to write SQL. Furthermore, you use dbt only for the data transformation piece. It doesn't contain as many features as Alteryx, but often companies don't need that many features either.
With dbt, collaboration is easy because it uses Git. I won't go into what Git is and how it works, but let's just say that if we go back to the example from earlier, where someone creates Table A (aka Workflow A) and someone else wants to modify it. When that second person saves a new version of Table A, Git makes sure to keep a comprehensive history of the file. This means we can always look at both the new and old versions of the file, and it's very easy to see the differences between the two files. It is also possible to add comments and explanations.
The biggest advantage of this is that if something in the code is wrong it is very easy to go back to the old version. This is useful when we have data that is in use throughout the company. We can go to the old version first and fix the problem later.
Advantage of dbt:
- history of modifications and old versions available
- collaborating and going back to previous versions is easy
Disadvantages of dbt:
- users need to know SQL
- it only does the data transformation piece
Conclusion
This equation is not as simple as "A is better than B."
Do you have a data team that is already fairly technical (or at least used to working with SQL) and only need that transformation piece? Then dbt is a fantastic tool for your organization.
Are you more interested in other functionalities such as APIs, complex geographic analysis or machine learning? Then Alteryx may be a good solution.
And what if your data team doesn't fit any of these descriptions? Then you might want to look at a third option (e.g., Coalesce, Matillion, etc.). There are many options on the market, what's important is to choose a product that fits your needs!
Need help choosing? Then contact us quietly 🙂 .