As a newcomer to data engineering, many of the programs and how they relate to each other can be difficult to wrap my head around. Databricks and dbt are a good example of this. Beyond their similar names, neither stores data, both sit on top of other systems, and both can perform transformations. So how are they different, and how do they work together? The keys to understanding each tool’s role in the data pipeline are where it sits in the stack and what it is built to do.
Data is typically stored in cloud storage, such as Amazon S3 or Microsoft Azure, which houses the data and little else. This is where a program like Databricks comes in. It sits on top of that storage and adds a UI, organization and cataloging, workflow scheduling, and the computational power to query data and run transformations. The data itself stays in your cloud storage.
While you can technically write transformation code inside the Databricks UI, using SQL or notebooks, dbt offers a more structured way to write and manage them. dbt Core is an open-source, command-line tool that connects to a platform such as Databricks through an adapter. It compiles your project into SQL and sends it to that platform to run. In other words, dbt has no computational power of its own, and it does not extract or load data. It only handles transformation.
This raises a question for beginners like myself: if Databricks can already do transformations, why use dbt at all? The answer is that dbt adds structure and additional features to the process. It breaks long, unwieldy SQL into cleaner, reusable code. It pairs SQL with Jinja, a templating engine that brings features usually associated with Python, such as macros (reusable code snippets) and loops. Its ref() function lets dbt map the dependencies between models automatically and run them in the correct order. These additional functions help simplify coding, and let you easily run transformations across multiple files and across a large amount of data. It also supports built-in testing, convenient documentation of your pipeline, and version control. In summary, these features make transformations easier to maintain and keep track of, which can be a lifesaver for large, complex databases.
Hopefully this clarifies for fellow novices to data engineering how dbt and platforms like Databricks relate, and when it makes sense to reach for dbt.