From DBT vs. Dataform: Picking the Right Data Transformation Tool

Why compare both tools?

DBT and Dataform’s frequent comparison comes from their shared mission: simplifying data pipelines on databases like BigQuery. 

Similarities:

• SQL Transformations as files: Both frameworks wrap your core transformation in the correct SQL for seamless creation of the specified database objects (table, views, etc.).
• Reusable Code: Both promote DRY (“Don’t Repeat Yourself”) principles through templating and scripting.
• Dependency Management: Built-in reference functions handle dependencies and determine query execution order.
• Partial Rebuilds: Select pipeline parts for rebuilding based on specific conditions.
• Data Testing: Automate data validation with integrated testing tools.

What is the difference between the tools?

Both DBT and Dataform simplify data pipelines, but their underlying approaches differ, impacting your development experience. Let’s take a look at the key differences to guide your tool selection:

Innovation:

• DBT: Open-source contributions contribute to core features, while an internal team focuses on cohesion and cloud offering. This fosters agility and diverse solutions, but long-term vision might be decentralised.
• Dataform: Google engineers control its evolution, ensuring tight Cloud integration but potentially slower feature adoption due to centralised decision-making.

Free Tier:

• DBT (core): Offers core functionality without a user interface or pipeline scheduler. Ideal for cost-conscious teams comfortable building custom workflows.
• Dataform: Provides a full web interface within Google Cloud Console, perfect for quick starts within that ecosystem but less flexible for external tools.

Integration:

• DBT: Benefits from a large open-source community, offering numerous integrations with diverse databases and tools, catering to various tech stacks.
• Dataform: Integrates seamlessly with Google Cloud services, providing a smooth experience within that environment but limited options outside.

Functionality:

• DBT: Excels in configuration management, allowing you to specify configurations to sets of transformations in a single place.
• Dataform: Enables the bulk creation of data transformations in JavaScript, offering one-file convenience and faster development for projects needing similar transformations on a larger set of database objects.

When to pick what tool?

Choosing between DBT and Dataform can feel like picking your favourite snack but with longer-lasting consequences. Both excel at transforming data but cater to different needs. This quick quiz can help you narrow down your options:(For each statement if the left part is more applicable choose a low number, otherwise choose a high number. Your result will be the sum of your chosen answers. )

Backed by Google Cloud – Backed by open-source contributors

1-2-3-4-5

No external parties can get access – Self-maintenance or external access is sufficient

1-2-3-4-5

Excellent integration with Google Cloud tools – Great integration with a variety of tools

1-2-3-4-5

Specialised tool for BigQuery – Similar tool for a variety of databases

1-2-3-4-5

Running only SQL – Running both SQL and Spark jobs

1-2-3-4-5

Results:

• Less than 15: You will favour Dataform’s secure and tight Google Cloud integration and BigQuery focus.
• More than 15: You’ll benefit from DBT’s open-source flexibility and diverse tool integrations.