Someone asked the other day for my opinion about the open-source dbt tool for ETL. I hadn’t heard about it. Next thing I’ve noticed was that Fabric Warehouse added support for it, so I got inspired to take a first look. Seems like an ELT-oriented tool. Good, I’m a big fan of the ELT pattern whose virtues I extolled many times here. But a Python-based tool that requires writing templates in a dev environment, such as Visual Studio Code? Yuck!
So, what’s my first impression? Same thoughts as when I see developers use another generic programming language, such as C#, for ETL. You can do it but why?
For years, I’ve been trying to get developers out of custom coding for ETL to low-code ETL specialized tools, such as SSIS and ADF.
Just because you studied Python in college, should you use Python for everything? I guess the open-source custom code gravitational pull is too strong. Or there are plenty of masochists out there. One of their case studies hailed dbt for democratizing ETL because everyone knows SQL and can contribute. That’s true but what goes around SQL is also important, as well productivity and maintainability of the overall solution.
On the Azure platform my preferred ETL architecture remains ADF and ELT with SQL in stored procedures. I don’t see any dbt advantages. Dbt might make sense to you if want to stay vendor-neutral, but I’d argue that if you follow the ELT pattern, migrating your ETL processes to another vendor would be trivial.
What am I missing?
UPDATE 10/19/2023
I’m humbled by the interest and comments this blog inspired on LinkedIn. It might well become one of the most popular posts I’ve ever written! This is what I’ve established based on the feedback from people who have used dbt:
- Although designed for ELT, it does only the “T” (transformation) part of the ELT process. You’d still need other tools, such as ADF, to extract and load the data. And so in a typical DW project, you could use ADF to extract data from the data sources, stage it, and then execute your dbt process to load the DW tables.
- It’s a template-based tool, where you use Python-like syntax to define “models”, such a model corresponding to a fact table, and the SQL statement to load it. Therefore, the SQL statement is embedded inside the template.
- It supports features, such as tests, macros, lineage, and documentation.
I wonder how many ETL projects would need these features though. If yours does, then dbt might be of interest. For me, the tool is still a hard pass that shares the same dark corner as ETL automation tools and ETL with custom code. Everybody is trying to do more with less nowadays, so maintainability and productivity are more important. If you have resources and time, my recommendation would be to invest into a home-grown SQL generator that would auto-generate the “T” part, such as the MERGE statement.
You don’t need to know that much Python. I’ve been working on a project with dbt for a couple of months now. It’s mainly SQL. You could argue it’s a bit the same as with BIML and SSIS. You can write only SQL, but once you want to automate some stuff, you start writing macro’s etc.
Thanks, Koen. How would you use it though in a common DW project given that the “EL” is missing?
You use other tools or replication features to get the data into a staging zone/layer. This can be ADF, CData, or some replication feature. You could argue that if you’re using ADF, you can just orchestrate the SQL there as well. The point of dbt is that it comes with much more features than “just executing SQL”. It comes with tests, macro’s, lineage etc.
I wonder how many of these features are really needed to justify yet another tool and template-based approach with embedded SQL.
So, I think the main frustration point with many I come across is the actual testability of an ETL solution. Software engineers are so accustomed to test-driven development patterns and integrating those tests with things such as ADO Test Plans. I believe the lineage and testing to be the main attraction.
However, as we both know, data quality subsystems are really just another subsystem in the data warehouse. Those that know the Kimball Architecture For Data Quality or other frameworks know these checks should already be built into mature solution. The DQ subsystem itself is the real world testing. Unfortunately, we also see some wheel reinventions there with open source tools like Great Expectations. We see it in the Semantic Layer with tools like Metric Flow.
That said, for folks that are just "moving data around" and maybe not part of a BI team, or not trained in star schema modeling….their world is just completely different. It appeals more to analytics engineers, I believe, as well as folks preferring open sourced solutions. Perhaps they are doing "reverse ETL" up against solutions lacking second and third level fact tables.
This all said, I don't think dbt adds much value to a well established and mature data program. Same is true for many tools having provincial scope. It's not a bad tool, though, and covers about 30 – 45 % of the data lifecycle.
What I DO LOVE about these open source tools? If someone has very little data management experience….perhaps comes from an open source, containerized app development background, you can point them right to a tool like metric flow to teach them the concepts of star schema modeling that speaks their language and background in a very good way.
It's all about how you use the tool!
Well, that was the whole point. Why would software engineers do ETL with code-oriented tools? That’s a job of BI developer using low-code tools we have been having at our disposal. Previously, it was programmers writing .NET code, now it’s Python or Python derivatives. What’s next or deja vu – software engineers writing code for reports and dashboards? Been there, done that, seen the damage…let’s use code for other purposes than BI.
I don’t disagree!