I'm already familiar with DML, and can write these statements manually, why should I use dbt to do this?
select statements make transformations accessible
More people know how to write
select statements, than DML, making the transformation layer accessible to more people!
Writing good DML is hard.
If you write the DDL / DML yourself you can end up getting yourself tangled in problems like:
- What happens if the table already exists? Or this table already exists as a view, but now I want it to be a table?
- What if the schema already exists? Or, should I check if the schema already exists?
- How do I replace a model atomically (such that there's no down-time for someone querying the table)
- What if I want to parameterize my schema so I can run these transformations in a development environment?
- What order do I need to run these statements in? If I run a
cascadedoes it break other things?
Each of these problems can be solved, but they are unlikely to be the best use of your time.
dbt does more than generate SQL
You can test your models, generate documentation, create snapshots, and more!
You reduce your vendor lock in
SQL dialects tend to diverge the most in DML and DDL (rather than in
select statements) — check out the example here. By writing less SQL, it can make a migration to a new database technology easier.
If you do need to write custom DML, there are ways to do this in dbt using custom materializations.