Opinions/Thoughts: SQL Best Practices in Production .. DBT vs DLT ?

[BS_THE_ANALYST]

Hey everyone, 

I'd like to hear the experiences of the community on DLT (Lakeflow declarative pipelines) vs DBT. 

1. Why would one choose one instead of the other? 
2. How does picking one of these level up your SQL strategy?

I am somebody who's well-versed in using SQL to answer business problems (more of an analyst). I'd love to know the next steps for levelling up & using best practices.

All the best,
BS

[szymon_dybczak · Accepted solution]

Hi @BS_THE_ANALYST ,

That’s a really good question! I don’t think there’s a single definitive answer, since the choice depends on several factors. Each framework has its own advantages and disadvantages.

In my opinions strengths of dbt are:

- Wide adoption - dbt is very popular, with a large and active community.

- SQL on steriods - you can write transformations purely in SQL, but dbt also supports Jinja templating, which makes it easier to add loops, conditionals, and other logic.

- Auto-generated documentation -

- Support for multiple environments - it fits nicely into dev/test/prod setups.

- Familiarity for analysts - since it’s SQL-based, a team of BAs or analysts with strong SQL skills can be productive quickly.

- Open source -  community-driven and not tied to a single vendor.

That said, dbt has limitations. It focuses mainly on the “T” in ETL/ELT. It doesn’t handle the extraction part, and its orchestration capabilities are more limited compared to native tools.

As about strengths of Declarative Pipelines:

- Supports ingestion as well as transformation - full ETL process 

- SQL and Python support - Python provides more flexibility, automation, and complex transformations for experienced teams.

- Tight integration with Databricks Unity Catalog and the broader ecosystem - since it's a native product, it often feels more seamless and coherent.

- Efficient incremental loading - I think DLT should handle incremental loading in a better manner thanks to features like the Enzyme engine and auto-optimization

- Ifrastructure management - DLT manages the underlying compute resources and integrates with Databricks Workflows, while dbt requires external orchestration tools.

- Streaming: DLT has native support for streaming data, whereas dbt can handle streaming via the dbt-databricks package.

On the other hand, there are trade-offs. The entry barrier is higher, and for now, there’s still an element of vendor lock-in. Databricks has announced plans to donate Declarative Pipelines to open source, but feature parity isn’t there yet, and practically speaking, choosing it means committing to the Databricks ecosystem.

My personal take: If your entire platform is already built on Databricks, Declarative Pipelines are a strong choice. If you need flexibility to run your pipelines on other databases or cloud platforms, dbt might be the safer choice.

[BS_THE_ANALYST]

That's a cracking write up @szymon_dybczak. Thanks for that 🤝.

That's certainly given me some food for thought. I think the safest option here, at least for me, is digging into both of them. I feel better informed moving forward with this. 

I'd love to hear about people's experiences/thoughts on working with either DBT/DLT and how they've found it in practice? The pros and cons they've found, and most importantly, what they'd have done differently whilst adopting DBT or DLT.

All the best,
BS