Transformation tooling: SQL Mesh vs dbt core

Introduction

A brief history of Transformations

Not too long ago, building a new dataset in database required a special set of skills depending on the flavor of the underlying technology. For example, if you specialized in a “traditional” database technologies (e.g. SQL Server, MySQL), chances are that the world of Spark would be hard to navigate.

Modern data platforms changed that by building meaningful abstraction layers on top of the processing layer. Python libraries (e.g. PySpark) were built on top of open source analytics engines (e.g. Apache Spark).

As data warehousing was enabled at scale, transformations became critical to provide “mart” layers for downstream consumption. Since this was a problem affecting bigger technology companies (e.g. Netflix, Airbnb), they built in-house tooling for data transformation challenges. While the data community tried several approaches to manage transformations at scale, it increasingly became clear that one technology stood out amongst the rest: dbt.

dbt (data build tool) brought best practices from engineering into the domain of analytics professionals. It democratized how data warehouse got built while also ensuring users did version control, testing, documentation. Its core offering in particular made it easy for anyone to use on top of existing warehouses and data lakes. The company (dbt labs) focused on improving the value of its product by enhancing the features provided by dbt cloud, its commercial offering.

Introducing SQLMesh

dbt’s paradigm to how data should be transformed spurred innovation in the community. One such example (and at the time of writing what we believe to be the only other viable open source transformation tool in the market) is SQLMesh. Folks at Tobiko Data learnt from dbt’s paradigm, adopted some best practices from larger technology companies, and went on to build a DataOps framework that scales well to large-scale deployments of the open source product.

This article provides an introduction to dbt core and SQLMesh, highlighting similarities and differences between both. We end the article with our opinion on when one may be better to use over another.

TL;DR

Based on maturity curve at the time of writing (Apr ‘24), dbt core is your go-to tool when wanting to get started quickly. For relatively smaller deployments, this would sufficiently address your use cases. If you’d like to be enterprise ready and DevOps friendly from the get-go, using SQLMesh is worth considering.

A Brief Intro to both tools

dbt core

dbt core is an open source tool that helps data professionals clean and organize raw data into a usable format for analysis.

Here are some key features of dbt Core:

• Modular and Centralized Analytics Code: dbt Core allows you to organize your data transformation logic into reusable components, making it easier to maintain and collaborate on data projects.
• Version Control: dbt Core integrates with version control systems, enabling you to track changes to your data transformations over time.
• Testing and Documentation: dbt Core allows you to write tests to ensure the accuracy of your data transformations and document your code for better understanding.
• Safe Deployment: dbt Core provides a safe way to deploy your data transformations to production environments.

Are you experienced with DBT but new to SQLMesh? If so, read Part 1 and Part 2 for a more in-depth coverage of “concept port-over”

SQLMesh

SQLMesh builds on the transformation fundamentals that dbt established with its core offering. It is an open-source DataOps framework designed to improve efficiency of data pipelines built with SQL or Python. It accomplishes this through several key features:

• Semantic Understanding: SQLMesh can analyze and understand the SQL code you write, allowing it to identify potential issues during the writing process itself, rather than waiting for errors to occur later.
• Virtual Data Environments: Instead of creating physical copies for development and testing purposes, SQLMesh utilizes virtual data environments. These are essentially representations of your data constructed using views, enabling faster deployments and safer rollbacks.
• Collaboration Features: SQLMesh incorporates functionalities to ease collaboration within data teams. It offers an open-source CI/CD bot and utilizes automated data contracts to improve understanding of changes made by teammates and their potential downstream impacts.

The Similarities

If you are seeing these tools for the first time, they’ll appear identical.

• Language: Both come with SQL has the primary modelling language.

  • Model configuration is handled outside of SQL code; SQL controls model logic only.
  • Note: the way that Python is run, however, is different. You’ll see this in the differences section below.

• Persistence strategies: Both support a variety of materialization strategies. The approach to achieving the same detail of incremental strategy in SQLMesh, may require more Jinja code within dbt.

• Data Model Testing: Both support data quality tests (dbt tests = audits in SQLMesh).

• Both support multi-repo setup. This allows you to have different projects for different teams, which sets up better foundations for data governance at scale.

• Resources: Both allow for seeds (reading csv files as datasets) and snapshots (a way to build type 2 scds)

• Hooks and Jinja macros are supported by both, allowing any customization in DDL statements required as needed in your data warehouse. This makes both tools extensible for your data engineering team.

The Differences

• GUI access is paid for dbt (its cloud product) beyond 1 user, while SQLMesh provides self-hosting

  • This means that SQLMesh makes column level lineage easily accessible. For dbt users, it requires upgrade to the paid plan, or addition of a metadata platform such as OpenMetaData or Datahub.

• Documentation for SQLMesh is through the GUI, while its in YAML for dbt

  • Model definition and configuration are separate in dbt, but are enforced together in sqlmesh (keeping code native to the same file)

• Unit test writing is a separate concept in SQLMesh, combined in dbt

  • unit testing is important at bigger stages of maturity

• Planning practice is largely up to the analyst in the case of dbt, SQLMesh provides this ability through semantic understanding and state awareness

  • Difference between dbt core which isolates each environment as separate targets vs
  • SQLMesh which tracks changes to the code locally (aka virtual data env) and runs the model based off production in temporary storage (aka physical data env). All of this is handled in the background with a clever use of views. For a conceptual understanding, see the image below:
    
    Credits: Tobiko Data's blog article on virtual data environments
  • The semantic understanding of how code has changed, and how it may impact downstream pipelines is achieved through the use of ASTs (Abstract Syntax Trees) which go way beyond regular code diff to parse whether a the underlying statement’s definition has changed. As a developer, all you need to do is plan before deployment to see how your changes will impact production.

• Refresh approach:

  • dbt’s approach is full-refresh first, which works well for smaller datasets.
  • SQLMesh focuses on an incremental-first approach, biased towards mindful backfills.

• dbt core has a wide variety of packages to help you leverage other peoples’ work.

  • This is especially helpful if you need to build some standard models, such as event models within Snowplow. You can find these in dbt hub.
  • You’d need to use Python macros since SQLMesh doesn’t come with a package manager.

• SQLMesh also introduces its own take of Python macros (over and above Jinja macros) providing more flexibility in expressing logic. In our experience, this makes the code more readable, and more predictable.

• Deploying dbt core in production requires you to build your own CI/CD process with guides on best practices such as Slim CI, while SQLMesh provides a CI/CD bot to help automate deployment.

• Orchestator support: While none of the tools come with native orchestration capabilities, SQLMesh allows you to declare cron parameters in your model configuration.

  • With their integrations on Airflow, this makes DAG orchestration simpler.
  • If you need an orchestrator quickly, a quick way to do this with dbt is to upgrade to their paid cloud offering.

Conclusion

Online guides, package hubs, and a paid offering with dbt will help you get you started really quickly. As your deployment grows, however, you are likely to start seeing challenges that are too complicated to resolve with dbt’s native capabilities (and will therefore be solved with compute and/or other investments).

SQLMesh is opinionated on how you should transform, and its paradigm can take some getting used to. We felt the developer experience to be more congruent to time-tested DevOps best practices adopted by engineering teams. The “under-the-hood” semantic understanding of your content could be the future of data warehouse transformations.

Note: This is a rapidly developing space, and things may have changed if you’re looking at this content far beyond Apr ‘24.

Still not sure about which tool might be good for your data warehouse? Book some time with us; Let’s talk about it.

Image credits: Tobiko Data and DBT Labs