5 ms·

As someone who works in the data-{engineering, science,etc} space, I really don’t understand the communities obsession with unwieldy tools like DBT. It’s like

As someone who works in the data-{engineering, science,etc} space, I really don’t understand the communities obsession with unwieldy tools like DBT. It’s like there’s some obsession with ignoring any kind of practice in wider software engineering, or innovation in the PL theory space, in favour of…gluing sql together using string templates? It seems like for the most part-there’s very little innovation or progress in the space. It’s just 15-variations on a theme of configure some yaml for your ninja-interpolated strings to dump ever more data into your cost-centre black hole known as a “modern data lake”. I’m sure there’s some interesting things going on in small corners, but it’s difficult to find, and if it exists, it’s being studiously ignored by mainstream tooling.

alexyz12 4y ago
What do you use instead?
- datalopers 4y ago
  regular ole views and mviews work just fine and provide the same composability.
  - Helmut10001 4y ago
    I love mviews since discovering them! So easy to split complex queries into multiple smaller ones, which are then queried sequentially through mviews.
    - epgui 4y ago
      Sounds just like dbt, but less organized and without jinja templates.
      - adammarples 4y ago
        People who don't use dbt usually have a half baked implementation they wrote themselves in python
        Helmut10001 4y ago
        I write my SQL queries in Jupyter Lab, mixed in Python code and f-strings, which is enough of a template engine to my needs atm. I can also directly visualize stuff with pandas (etc.) species: str = 'acer' structure: str = 'trees.table' query = f""" SELECT * FROM {structure} WHERE species = {species} """
  - EDEdDNEdDYFaN 4y ago
    And how do you manage the versioning and deployment of your views and mviews? What tooling do you use to refresh those materialized views on a schedule? Is it something you've written and maintain yourself? What testing do you have on them? I find that dbt answers all of these questions pretty simply in a way that avoids the need to reinvent the wheel. It's not some godlike piece of software, just a helpful framework wrapper around plain old SQL
    - FridgeSeal 4y ago
      > And how do you manage the versioning and deployment of your views and mviews? Underlying SQL lives in git, along with the code. Deployment happens via normal CICD pipelines. > What tooling do you use to refresh those materialized views on a schedule? A database that automatically rebuilds views when the underlying table changes (ClickHouse), or if I’m using postgres, a “dumb” job that simply updates the views. Periodically. > Is it something you've written and maintain yourself? What testing do you have on them? Written and maintain myself? Not really, it’s either Spark or Rust code that’s a very thin wrapper to drive the underlying SQL. Everything runs in Kubernetes, as either plain deployments (for ongoing/streaming workloads) or cronjobs for periodic workloads. SQL and wrapper code are tested using the normal in-language testing tools. > I find that dbt answers all of these questions pretty simply in a way that avoids the need to reinvent the wheel. I don’t think people not using dbt are reinventing the wheel, in my case I’m not doing anything that wouldn’t be considered exceedingly boring in architecture and usage. Avoiding weird proprietary tools also means that the technology and approach used is almost identical to what the devs use, which means more shared knowledge and better reusability.
lunatuna 4y ago
There has been a big improvement in productivity and visibility when we introduced dbt along with Snowflake. The concepts of DataOps became real. We've been able to work a lot more like software developers to our advantage. I didn't get it either. I thought it just looks like SQL with some complicated additions. Then I did some work with it and the flow got way better. We could work a lot more incrementally and as a larger team rather than as individuals tripping all over each other. Maybe there is a better way doing something else, but this clicked with the team. Not sure I get your cost centre black hole comment, but for the most part storage is so cheap that it isn't a concern. ELT has made it pretty easy for us to do less analysis and work on the data gathering side, which is low value, and do more on the modeling side which is higher value. Further, analysts can play with the raw data and find what they want and if they like it they will give it to us to model in a formal way. No need to work with an ETL team and application team to bring the data over. In ye olden times, this was overthought and created friction - "If you don't know what you're going to exactly do with the data how am I going to know what to bring in and how to land it"."But how am I to know what to do with the data if I can't query it and see what opportunities there are." On the unwieldy part, sounds like you've had a bad experience. Ours has been the opposite. Really easy to setup and use. Easy incremental step for the team to get their heads around. Integration with git and snowflake was really easy. I'm still a bit worried that something is going to get really messed up with the declarative approach that dbt has, but we've not seen any issues yet. I've been curious as well about what more can be done to make the development lifecycle better. Most of the other "big data" approaches that I've worked with seemed to make it worse not better. A lot more complexity, not thinking in sets, layers and layers of nothing I care about that can go wrong. I can't believe I'm still working with SQL.
- FridgeSeal 4y ago
  > I've been curious as well about what more can be done to make the development lifecycle better. Most of the other "big data" approaches that I've worked with seemed to make it worse not better Interesting, what other approaches and tools are you thinking of that are worse/failed/etc.
  - lunatuna 4y ago
vaughan 4y ago
People don't have enough time to step back from their tools and think about what the ideal thing would look like. I'm convinced this entire space should be visual. We always visualize data pipelines in our minds and on whiteboards...well, visually. Two-way code-diagram syncing is needed to allow best of both worlds. We should serialize to yaml from imperative code, but allow manipulating the diagram to modify the imperative code. Ideally what you want is also to track the dependencies of every atom of data in your org, and then have something cache and incrementally compute updates. The biggest thorn in this vision is SQL and the relational model, and I don't think a lot of people realize it. It favors representing data such that the query planner can optimize query execution...rather than being able to track data dependencies and visualize data flow. It wasn't designed in mind with complex ETL pipelines and many external data sources of today's world.
- FridgeSeal 4y ago
  > I'm convinced this entire space should be visual. At my last 2 jobs I spent entirely too much time debugging Matillion jobs, which are visual. I have my doubts that it’s the panacea that it appears to be. That said, you may find Enso particularly interesting: https://github.com/enso-org/enso https://github.com/enso-org/enso
  - vaughan 4y ago
    Thanks for the link. Enso looks very cool. The traceability aspect is nice. I'd never want to use a visual interface to "code" though like they show in their intro video. They have the two-way sync to code-diagram which is cool, and its to an imperative language, but it's to their own strange programming language. I just can't see myself coding Enso. If it were something familiar like Python or JS - then now we are talking!
- lukesingham 4y ago
  >I'm convinced this entire space should be visual. We always visualize data pipelines in our minds and on whiteboards...well, visually. Two-way code-diagram syncing is needed to allow best of both worlds I'm also convinced by this! Currently building something like this at https://www.ilumadata.io/ https://www.ilumadata.io/ We have a pipeline builder (not yet on our website) that lets you visually build out the data pipeline. At the same time it's creating the corresponding files for each node which you can switch to.
jdub 4y ago
The whole point of dbt is to bring software engineering principles to the handling of data for analytics. It's still SQL (as are all the modern data warehouses) because nothing else has beaten it.
- FridgeSeal 4y ago
  I’ve been using software engineering principles in my data work (and plenty of SQL) and haven’t had a need for dbt. SQL is amazing, I’m not convinced string templates and this one tool offer enough value to be worth the hype they receive.
panda888888 4y ago
You have to go back a step, to ETL vs ELT. ELT is powerful because you're doing the transformations as the final step, so you can quickly ingest the data and then transform it however you want and in different ways. While you may not love DBT, the innovative thing here is that the IT department no longer needs to do this work (this is in contrast to ETL). A data analyst or business analyst or anyone who knows SQL can now do it. So if you're a software engineer, you're probably not the target audience for DBT. I agree with your criticism that the tool itself isn't necessarily that innovative, but if you think of DBT as version control/templating for people who aren't already software engineers, it's easier to see why it's useful.
- FridgeSeal 4y ago
  > You have to go back a step, to ETL vs ELT. ELT is powerful because you're doing the transformations as the final step, so you can quickly ingest the data and then transform it however you want and in different ways No I get it, land your data, apply transforms land it, repeat. I’m already following this approach, so maybe the value prop of dbt is lost on me. My concern is that I’ve worked in places where the proverbial “reigns” of “do whatever” with the data were handed off to anyone who asked for them, and I was involved trying to fix the inevitable mess that was created. There’s zero coherency, so 8 different people create 5 different, incompatible reports for the same thing, mishandled money and date values everywhere, etc. That’s not an experience I wish to relive any time soon.
  - panda888888 4y ago
    That sounds like a data governance problem that goes above and beyond the things that DBT does. The typical way to handle this is by implementing a gold/silver/bronze architecture.
AnEro 4y ago
I'm all in on DBT for several reasons: 1. One source of transforms for static data 2. Easy to do plug-in or changes, even 'materializations', are Jinja. So I can have custom implementation for a specific issue 3. DBT to Metabase documentation, still super new but imagine other tools getting intake of metadata and metrics from dbt 4. I'm the data team, and managing a huge amount of workflows. If I just use a select and not DML style of writing the queries, it will 'orchestrate' it for me. 4.1 I can have a quick version controlled workflow for a new report/kpi/metric that we don't know if it is even important yet. 5. The speed trade-offs are made up for with the time saved from point 4 6. Embeddable in to the datamesh I dream of no one knowing I use DBT or any software at all, its all just [company branding] magic
- FridgeSeal 4y ago
  So if I’m understanding this correctly, what dbt is giving you is really just a straightforward, reliable way to get SQL executed? You can write some queries, it’ll make sure they’re run (possibly on a schedule or in a dependency flow), it has a bunch of integrations, and it gives some handy lineage metadata. Is that about it? This whole really just makes me want to write an alternative to dbt that does this stuff, but better. I’m convinced there must be better solutions to sql composability than string interpolations and too-many-yaml-files.
  - tomnipotent 4y ago
    > write an alternative to dbt that does this stuff, but better How are you going to do that when your comments betray that you barely know what dbt does in the first place? > solutions to sql composability dbt isn't a solution to SQL composability, but it offers something similar in spirit because of how its DAG-based processing works.
    - FridgeSeal 4y ago
      > How are you going to do that when your comments betray that you barely know what dbt does in the first place? God forbid I ask questions to fill in my knowledge gaps. “Barely know” is a bit strong, I understand what it is, I don’t personally see the value proposition. The question about composability is hence a proxy- if I don’t see the value prop, I need to understand what the underlying benefit is from people who do value it. To me it just looks like some shiny bells and whistles on top of gluing a bunch of sql execution together. Other products have failed at that, so it’s got to be slightly more than that. The other thing it offers is composition via string templates, and seemingly, some level of orchestration as a direct result. Sometimes you have to ask “dumb looking” questions to make sure you don’t miss things.
mythhouse 4y ago
> I really don’t understand the communities obsession with unwieldy tools like DBT. It lets me write test first sql transforms. I never thought TDD sql would be possible. My sql is so much more readable with common logic extracted into ephmeral models. I practice same method to write clear code to write sql, eg: too many mocks = refactor into separate model ( class) . I think DBT made this possible with refs that can be swapped out with mocks. This is the awesome library I am using https://github.com/EqualExperts/dbt-unit-testing https://github.com/EqualExperts/dbt-unit-testing
- akhmatova 4y ago
  My sql is so much more readable with common logic extracted into ephmeral models. Sounds great. My concern though with DBT is the cognitive overhead and just plain ... weirdness and clunkiness it seems to introduce as a cost to obtaining benefits like these. For example if we look at their own documentation: When using the view materialization, your model is rebuilt as a view on each run, via a create view as statement. https://docs.getdbt.com/docs/build/materializations https://docs.getdbt.com/docs/build/materializations Now, isn't your reaction, upon reading this, some version of: "Whaaat? Views are not materializations - by definition, they are essentially the opposite of materializations." And yet here the DBT documentation is describing the results of plain old "create view" statements as materializations. And then we're not getting into even more basic issues - like why we're supposed to believe that code like: select ... from {{ source('jaffle_shop', 'orders') }} left join {{ source('jaffle_shop', 'customers') }} using (customer_id) Is more readable or maintainable than what it compiles into: select ... from raw.jaffle_shop.orders left join raw.jaffle_shop.customers using (customer_id) I'm happy it works for you. But I'm just not seeing the magic here.
  - mb7733 4y ago
    > Now, isn't your reaction, upon reading this, some version of: "Whaaat? Views are not materializations - by definition, they are essentially the opposite of materializations I'd never heard of dbt before today but from a quick scan of that page it's clear to me what a that 'materialization' is in dbt. It's not the same as a SQL 'materialized view'; it could be a SQL view, an SQL table, a CTE, or a table with extra machinery for incremental updating. In my experience in software it's common for similar words to be used for different concepts in different contexts. I think the dbt documentation is well done so I don't see a big issue.

alexyz12 4y ago

What do you use instead?

datalopers 4y ago
regular ole views and mviews work just fine and provide the same composability.
- Helmut10001 4y ago
  I love mviews since discovering them! So easy to split complex queries into multiple smaller ones, which are then queried sequentially through mviews.
  - epgui 4y ago
    Sounds just like dbt, but less organized and without jinja templates.
    - adammarples 4y ago
      People who don't use dbt usually have a half baked implementation they wrote themselves in python
      - Helmut10001 4y ago
        I write my SQL queries in Jupyter Lab, mixed in Python code and f-strings, which is enough of a template engine to my needs atm. I can also directly visualize stuff with pandas (etc.) species: str = 'acer' structure: str = 'trees.table' query = f""" SELECT * FROM {structure} WHERE species = {species} """
- EDEdDNEdDYFaN 4y ago
  And how do you manage the versioning and deployment of your views and mviews? What tooling do you use to refresh those materialized views on a schedule? Is it something you've written and maintain yourself? What testing do you have on them? I find that dbt answers all of these questions pretty simply in a way that avoids the need to reinvent the wheel. It's not some godlike piece of software, just a helpful framework wrapper around plain old SQL
  - FridgeSeal 4y ago
    > And how do you manage the versioning and deployment of your views and mviews? Underlying SQL lives in git, along with the code. Deployment happens via normal CICD pipelines. > What tooling do you use to refresh those materialized views on a schedule? A database that automatically rebuilds views when the underlying table changes (ClickHouse), or if I’m using postgres, a “dumb” job that simply updates the views. Periodically. > Is it something you've written and maintain yourself? What testing do you have on them? Written and maintain myself? Not really, it’s either Spark or Rust code that’s a very thin wrapper to drive the underlying SQL. Everything runs in Kubernetes, as either plain deployments (for ongoing/streaming workloads) or cronjobs for periodic workloads. SQL and wrapper code are tested using the normal in-language testing tools. > I find that dbt answers all of these questions pretty simply in a way that avoids the need to reinvent the wheel. I don’t think people not using dbt are reinventing the wheel, in my case I’m not doing anything that wouldn’t be considered exceedingly boring in architecture and usage. Avoiding weird proprietary tools also means that the technology and approach used is almost identical to what the devs use, which means more shared knowledge and better reusability.

lunatuna 4y ago

There has been a big improvement in productivity and visibility when we introduced dbt along with Snowflake. The concepts of DataOps became real. We've been able to work a lot more like software developers to our advantage. I didn't get it either. I thought it just looks like SQL with some complicated additions. Then I did some work with it and the flow got way better. We could work a lot more incrementally and as a larger team rather than as individuals tripping all over each other. Maybe there is a better way doing something else, but this clicked with the team. Not sure I get your cost centre black hole comment, but for the most part storage is so cheap that it isn't a concern. ELT has made it pretty easy for us to do less analysis and work on the data gathering side, which is low value, and do more on the modeling side which is higher value. Further, analysts can play with the raw data and find what they want and if they like it they will give it to us to model in a formal way. No need to work with an ETL team and application team to bring the data over. In ye olden times, this was overthought and created friction - "If you don't know what you're going to exactly do with the data how am I going to know what to bring in and how to land it"."But how am I to know what to do with the data if I can't query it and see what opportunities there are." On the unwieldy part, sounds like you've had a bad experience. Ours has been the opposite. Really easy to setup and use. Easy incremental step for the team to get their heads around. Integration with git and snowflake was really easy. I'm still a bit worried that something is going to get really messed up with the declarative approach that dbt has, but we've not seen any issues yet. I've been curious as well about what more can be done to make the development lifecycle better. Most of the other "big data" approaches that I've worked with seemed to make it worse not better. A lot more complexity, not thinking in sets, layers and layers of nothing I care about that can go wrong. I can't believe I'm still working with SQL.

FridgeSeal 4y ago
> I've been curious as well about what more can be done to make the development lifecycle better. Most of the other "big data" approaches that I've worked with seemed to make it worse not better Interesting, what other approaches and tools are you thinking of that are worse/failed/etc.
- lunatuna 4y ago

vaughan 4y ago

People don't have enough time to step back from their tools and think about what the ideal thing would look like. I'm convinced this entire space should be visual. We always visualize data pipelines in our minds and on whiteboards...well, visually. Two-way code-diagram syncing is needed to allow best of both worlds. We should serialize to yaml from imperative code, but allow manipulating the diagram to modify the imperative code. Ideally what you want is also to track the dependencies of every atom of data in your org, and then have something cache and incrementally compute updates. The biggest thorn in this vision is SQL and the relational model, and I don't think a lot of people realize it. It favors representing data such that the query planner can optimize query execution...rather than being able to track data dependencies and visualize data flow. It wasn't designed in mind with complex ETL pipelines and many external data sources of today's world.

FridgeSeal 4y ago
> I'm convinced this entire space should be visual. At my last 2 jobs I spent entirely too much time debugging Matillion jobs, which are visual. I have my doubts that it’s the panacea that it appears to be. That said, you may find Enso particularly interesting: https://github.com/enso-org/enso https://github.com/enso-org/enso
- vaughan 4y ago
  Thanks for the link. Enso looks very cool. The traceability aspect is nice. I'd never want to use a visual interface to "code" though like they show in their intro video. They have the two-way sync to code-diagram which is cool, and its to an imperative language, but it's to their own strange programming language. I just can't see myself coding Enso. If it were something familiar like Python or JS - then now we are talking!
lukesingham 4y ago
>I'm convinced this entire space should be visual. We always visualize data pipelines in our minds and on whiteboards...well, visually. Two-way code-diagram syncing is needed to allow best of both worlds I'm also convinced by this! Currently building something like this at https://www.ilumadata.io/ https://www.ilumadata.io/ We have a pipeline builder (not yet on our website) that lets you visually build out the data pipeline. At the same time it's creating the corresponding files for each node which you can switch to.

jdub 4y ago

The whole point of dbt is to bring software engineering principles to the handling of data for analytics. It's still SQL (as are all the modern data warehouses) because nothing else has beaten it.

FridgeSeal 4y ago
I’ve been using software engineering principles in my data work (and plenty of SQL) and haven’t had a need for dbt. SQL is amazing, I’m not convinced string templates and this one tool offer enough value to be worth the hype they receive.

panda888888 4y ago

You have to go back a step, to ETL vs ELT. ELT is powerful because you're doing the transformations as the final step, so you can quickly ingest the data and then transform it however you want and in different ways. While you may not love DBT, the innovative thing here is that the IT department no longer needs to do this work (this is in contrast to ETL). A data analyst or business analyst or anyone who knows SQL can now do it. So if you're a software engineer, you're probably not the target audience for DBT. I agree with your criticism that the tool itself isn't necessarily that innovative, but if you think of DBT as version control/templating for people who aren't already software engineers, it's easier to see why it's useful.

FridgeSeal 4y ago
> You have to go back a step, to ETL vs ELT. ELT is powerful because you're doing the transformations as the final step, so you can quickly ingest the data and then transform it however you want and in different ways No I get it, land your data, apply transforms land it, repeat. I’m already following this approach, so maybe the value prop of dbt is lost on me. My concern is that I’ve worked in places where the proverbial “reigns” of “do whatever” with the data were handed off to anyone who asked for them, and I was involved trying to fix the inevitable mess that was created. There’s zero coherency, so 8 different people create 5 different, incompatible reports for the same thing, mishandled money and date values everywhere, etc. That’s not an experience I wish to relive any time soon.
- panda888888 4y ago
  That sounds like a data governance problem that goes above and beyond the things that DBT does. The typical way to handle this is by implementing a gold/silver/bronze architecture.

AnEro 4y ago

I'm all in on DBT for several reasons: 1. One source of transforms for static data 2. Easy to do plug-in or changes, even 'materializations', are Jinja. So I can have custom implementation for a specific issue 3. DBT to Metabase documentation, still super new but imagine other tools getting intake of metadata and metrics from dbt 4. I'm the data team, and managing a huge amount of workflows. If I just use a select and not DML style of writing the queries, it will 'orchestrate' it for me. 4.1 I can have a quick version controlled workflow for a new report/kpi/metric that we don't know if it is even important yet. 5. The speed trade-offs are made up for with the time saved from point 4 6. Embeddable in to the datamesh I dream of no one knowing I use DBT or any software at all, its all just [company branding] magic

FridgeSeal 4y ago
So if I’m understanding this correctly, what dbt is giving you is really just a straightforward, reliable way to get SQL executed? You can write some queries, it’ll make sure they’re run (possibly on a schedule or in a dependency flow), it has a bunch of integrations, and it gives some handy lineage metadata. Is that about it? This whole really just makes me want to write an alternative to dbt that does this stuff, but better. I’m convinced there must be better solutions to sql composability than string interpolations and too-many-yaml-files.
- tomnipotent 4y ago
  > write an alternative to dbt that does this stuff, but better How are you going to do that when your comments betray that you barely know what dbt does in the first place? > solutions to sql composability dbt isn't a solution to SQL composability, but it offers something similar in spirit because of how its DAG-based processing works.
  - FridgeSeal 4y ago
    > How are you going to do that when your comments betray that you barely know what dbt does in the first place? God forbid I ask questions to fill in my knowledge gaps. “Barely know” is a bit strong, I understand what it is, I don’t personally see the value proposition. The question about composability is hence a proxy- if I don’t see the value prop, I need to understand what the underlying benefit is from people who do value it. To me it just looks like some shiny bells and whistles on top of gluing a bunch of sql execution together. Other products have failed at that, so it’s got to be slightly more than that. The other thing it offers is composition via string templates, and seemingly, some level of orchestration as a direct result. Sometimes you have to ask “dumb looking” questions to make sure you don’t miss things.

mythhouse 4y ago

> I really don’t understand the communities obsession with unwieldy tools like DBT. It lets me write test first sql transforms. I never thought TDD sql would be possible. My sql is so much more readable with common logic extracted into ephmeral models. I practice same method to write clear code to write sql, eg: too many mocks = refactor into separate model ( class) . I think DBT made this possible with refs that can be swapped out with mocks. This is the awesome library I am using https://github.com/EqualExperts/dbt-unit-testing https://github.com/EqualExperts/dbt-unit-testing

akhmatova 4y ago
My sql is so much more readable with common logic extracted into ephmeral models. Sounds great. My concern though with DBT is the cognitive overhead and just plain ... weirdness and clunkiness it seems to introduce as a cost to obtaining benefits like these. For example if we look at their own documentation: When using the view materialization, your model is rebuilt as a view on each run, via a create view as statement. https://docs.getdbt.com/docs/build/materializations https://docs.getdbt.com/docs/build/materializations Now, isn't your reaction, upon reading this, some version of: "Whaaat? Views are not materializations - by definition, they are essentially the opposite of materializations." And yet here the DBT documentation is describing the results of plain old "create view" statements as materializations. And then we're not getting into even more basic issues - like why we're supposed to believe that code like: select ... from {{ source('jaffle_shop', 'orders') }} left join {{ source('jaffle_shop', 'customers') }} using (customer_id) Is more readable or maintainable than what it compiles into: select ... from raw.jaffle_shop.orders left join raw.jaffle_shop.customers using (customer_id) I'm happy it works for you. But I'm just not seeing the magic here.
- mb7733 4y ago
  > Now, isn't your reaction, upon reading this, some version of: "Whaaat? Views are not materializations - by definition, they are essentially the opposite of materializations I'd never heard of dbt before today but from a quick scan of that page it's clear to me what a that 'materialization' is in dbt. It's not the same as a SQL 'materialized view'; it could be a SQL view, an SQL table, a CTE, or a table with extra machinery for incremental updating. In my experience in software it's common for similar words to be used for different concepts in different contexts. I think the dbt documentation is well done so I don't see a big issue.