9 ms·

The new local data stack: Integrating Dagster, dbt and DuckDB

144 points by geoHeil 3y ago

Eumenes 3y ago
Looks cool but I don't like to push to paid managed services like dbt. Rather use something like Airflow but its not as easy to use for most teams.
- Jgrubb 3y ago
  As the other poster mentioned, DBT is an open core product and I'm not sure how you'd use the cloud service on a local DB install anyway. Airflow and DBT don't really do the same things anyway.
- contravariant 3y ago
  I'm not sure those two solve the same problem. Regardless airflow is an amazingly useful tool.
- Hasnep 3y ago
  dbt-core is open source and can be used locally.
- bart_spoon 3y ago
  DBT is open source and can be used entirely for free. They sell a hosted version. Also DBT and Airflow are different tools, aimed at solving completely different problems. The Airflow competitor in this article is Dagster, which is also open source.
skypanther 3y ago
I only skimmed the article due to its length. But I didn't see anything like a comparison to other toolchains. Like, how is this better/different/worse than Airflow + dbt + Snowflake?
- RockyMcNuts 3y ago
  Snowflake isn't local, you have to pay for cloud ... Airflow is Airflow, complexity, steep learning curve, there is a whole industry trying to be as/more powerful than Airflow with less complexity and cleaner integration with modern things you might want to do like k8s, although of course Airflow is still super popular and powerful. the Dagster folks have some comparisons, of course there are popular modern alternatives other than Dagster https://dagster.io/blog/dagster-airflow https://dagster.io/blog/dagster-airflow https://dagster.io/vs/dagster-vs-airflow https://dagster.io/vs/dagster-vs-airflow
- remram 3y ago
  It does feel like it was lengthened with GPT. There's so many essay-style "now we propose to show that...", it's uncanny. > we argue that we can rethink the current state of data transformation pipelines (intro) > In the blog we will cover: > We show a way how you can combine the best of both worlds > We will tackle its impact and explain more in the following development section. edit: yeah it's ChatGPT: > Parts of this text were adeptly generated by ChatGPT but enhanced by real humans. I guess it's the future, turn a tweet into a 5-page essay with your AI, so your readers can summarize it back to tweet-length with their AI.
  - geoHeil 3y ago
    Hi, Georg - one of the authors is here. Indeed, we used LLMs/GPT4 for proof reading and enhancing the English language (we are not native speakers). We were thinking about breaking up the content - but decided that one long post is a better fit. > > We show a way how you can combine the best of both worlds In fact, that sentence I wrote by hand : )
- bart_spoon 3y ago
  Far more helpful to me than these essays on the benefits of a particular paradigm is a simple, minimal example in a repo that I can dig into and explore.
- adammarples 3y ago
  It just seems like one choice, among many. The dagster-dbt library in particular seems like a slightly pointless wrapper around the dbt cli and the json artifacts it creates. I've been using airflow and astronomer-cosmos which is not perfect but I didn't enjoy using dagster last time I tried.
jakozaur 3y ago
It's a cool idea, but it looks incomplete for the production use case. 1. Usually, you want to run some warehouse all the time. Bring their data through ETL, run transformation, and report. This goes against the local environment. Ideally, I would love a cloud warehouse, which each engineer could easily fork to their laptop. 2. Almost all companies already have some data setup. The migration path is very unclear. Most likely, this is a secondary system for the majority of companies. Ideally, I would love to describe how I can use it along big platforms (e.g. BigQuery or Snowflake).
- dappermanneke 3y ago
  what stops you from running this on a container and dumping the results with a script to be queried by a reporting solution into bigquery or snowflake?
  - jakozaur 3y ago
    I already see companies with BigQuery setuped. I would like to quickly grab some data, queries and dashboards and run them in my local warehouses.
    - geoHeil 3y ago
      If you use bigquery in big lake mode - i.e. with parquet or iceberg or delta files on GCS (or other object store) you can easily pull in the data into duckdb as well
  - geoHeil 3y ago
    Hi, Georg - one of the authors is here. In fact, this is exactly what we argue: Keep data the consumer experience high a Paas platform (Fabric, BigLake, Databricks, SF, ...) can make a lot of sense, whereas for the best data development/creator experience a high-code solution based on solid software engineering best practices should be the preferred solution - at least in my/our(authors) opinion.
- rodrigodlu 3y ago
  I could add more but on a newish local stack I was trying: I spent a good amount of hours on duckdb this week to process personal data from data dumps (social networks, etc) and now I'm back to the combo of postgresql in containers + sqlite. After the initial imports and some massaging with queries that felt awesome, I found hard to step-up the game building the relationships I wanted. The last stroke before switching was the lack of managing foreign keys without recreating entire tables again. I can go over other examples. It can be done, but it just takes you out of the flow when you're analyzing the data + cleaning it, specially because I know that I can do with psql and sqlite in a blink of an eye. Since many etl tools don't care about the target database being these old and trusty fellas, I felt losing a lot of time just to get rid of a postgres install that is right now consuming only 200 mb of ram on a docker/podman container. Or working around some sqlite ingestion issues with simple notebooks + pandas/polars/etc. in my pov it seems a shaky ground for an entire new stack I appreciate duckdb taking me out of the comfort zone tho.
OJFord 3y ago
Tools like Dagster, dbt, and AWS Glue always strike me as built for BI/in-house analytics over user & app-generated data. What do people use for ingesting data from end-users, their transactional CRUD to it too, and then OLAP queries triggered (not written) by and for those same end users?
- lysecret 3y ago
  Bunch of python code.
- benjaminwootton 3y ago
  There are a bunch of collector tools for capturing application side events and sending them into your server. The common stack is then to ingest them from Kafka into an OLAP datastore like ClickHouse or Pinot. Then I think you are referring to what is sometimes called “user facing analytics” against that datastore. We like to use a tool called Cube.dev as middleware between the app and OLAP database which gives APIs and a semantic layer.
  - OJFord 3y ago
    'user-facing analytics' and (via Cube.dev) 'embedded analytics' are helpful terms, thank you!
  - totalhack 3y ago
    +1 for using a semantic layer. I built (and open-sourced) one for use in my own business but Cube looks well done. https://github.com/totalhack/zillion https://github.com/totalhack/zillion
  - geoHeil 3y ago
    Not to forget https://www.starrocks.io/ https://www.starrocks.io/ or https://risingwave.com/ https://risingwave.com/ - see also a recent blog post of a friend: https://juhache.substack.com/p/moving-from-bi-to-data-apps-part?r=l9wvi https://juhache.substack.com/p/moving-from-bi-to-data-apps-p...
- gms 3y ago
jasmcole 3y ago
If you're interested in interrogating dbt models using DuckDB, you may be interested in some new dbt features we've recently released at https://count.co https://count.co
- dotandgtfo 3y ago
  Your link probably doesn't lead where you want it to btw.
  - jasmcole 3y ago
    Thanks for the heads up - a page more focussed on dbt specifically is here: https://count.co/product/data-modeling/ https://count.co/product/data-modeling/ and associated blog post: https://count.co/blog/how-we-made-data-modeling-multiplayer/ https://count.co/blog/how-we-made-data-modeling-multiplayer/
    - dotandgtfo 3y ago
      Thanks! I was logged into count so I just got sent straight to my homepage as well. Seems interesting
mgaunard 3y ago
I use DuckDB, it's a great piece of tech. Only way I could figure out how to use it though was to read the source (found a few interesting bugs while doing so as well)
- hackyhacky 3y ago
  Which bug?
- wenc 3y ago
  No code is fully free of bugs. Did you submit an issue or PR?
  - mgaunard 3y ago
    I didn't do all of them because I found them on the job. Submitted one bug report for the thing I didn't know how to fix.
kalendos 3y ago
I've been working with this stack (building Open Data Portals¹) for a few months and am super happy with how well everything plays together. ¹ https://github.com/davidgasquez/gitcoin-grants-data-portal https://github.com/davidgasquez/gitcoin-grants-data-portal
- geekodour 3y ago
  Hi, i am working on something similar and was looking for ways how i can host my open data. the approach seems interesting, can i reach out to you to discuss more on this somewhere?
  - kalendos 3y ago
    Sure! You can find my contact details on GitHub: https://github.com/davidgasquez https://github.com/davidgasquez.
datadrivenangel 3y ago
I've helped with multiple data engineering teams that suffer tremendously due to a lack of local setup, so DuckDB and the right choice of tools looks immensely promising. Also for 99% of companies and data workloads the optimal choice should usually be a janky bash script, so this provides meaningful competition for annoyingly medium data.
- riku_iki 3y ago
  > I've helped with multiple data engineering teams that suffer tremendously due to a lack of local setup why remote setup is not enough for those teams?..
  - datadrivenangel 3y ago
    remote setup was enough, but our dev environments were never quite 100% the same as prod because copying all the data would have been too expensive, and the remote environments were always just a little higher latency for things than the local computers... Just a little bit worse in ways that annoyed people.
hbarka 3y ago
On Dagster’s website there are comparisons to dbt. Is it complementary (as positioned in this stack) or competitive? Dagster versus dbt https://dagster.io/vs https://dagster.io/vs Migrating off dbt cloud https://dagster.io/blog/migrate-off-dbt-cloud https://dagster.io/blog/migrate-off-dbt-cloud
- jnac 3y ago
  Dagster (orchestration tool) is complementary to dbt core (open source CLI tool for defining data transformation jobs) but competitive to dbt cloud (SaaS, orchestrates dbt jobs).
- mritchie712 3y ago
  you can run dbt core via a dagster job (so it'd replace dbt cloud)
geoHeil 3y ago
We (Aleksandar and Georg) want to share our new blog post on: "Dagster, dbt, DuckDB as new local MDS" here. It re-introduces the local environment to enhance the developer productivity for data pipelines by bringing back software engineering best practices. We suggest that PaaS platforms should become an implementation detail and refine the new local stack with great a data consumer experience by combining the best of both worlds. Please take a read here: https://georgheiler.com/2023/12/11/dagster-dbt-duckdb-as-new-local-mds/ https://georgheiler.com/2023/12/11/dagster-dbt-duckdb-as-new... We (Georg Heiler and Aleksandar Milicevic) are keen to discuss the proposed new stack with you. Do not hesitate to reach out.
- deleted 3y ago
  [deleted]
- Jgrubb 3y ago
  Hey Georg, thanks for posting. I've been working on building a thing with almost the same stack lately and the Dagster integration is my next large-ish step. In the last couple weeks I've kicked around Duck and Clickhouse for the backend. It's been a lot of fun. Ultimately I'd like something that can be run locally or easily installed and run on a PaaS. The only trick with local is that data sets of any appreciable size take ages to pull down, at least here in the US with our terrible internet (in the average Italian mountain village this would probably work great).
  - geoHeil 3y ago
    Well local can mean your laptop. But could mean your local server. However, it can also mean a VM on your cloud provider of choice colocated to the object store. In such a case the network transfer (for many cases) can be almost irrelevant - at least for up to medium-ish sized datasets.
    - Jgrubb 3y ago
      Funny thing, I'm trying to get going with Dagster today like I said, and figuring out how to debug is a bit .. annoying. The most helpful post I've come across so far just happens to be https://georgheiler.com/2022/02/02/interactive-dagster-debugging/ https://georgheiler.com/2022/02/02/interactive-dagster-debug... So thanks!

144 points by geoHeil 3y ago

Looks cool but I don't like to push to paid managed services like dbt. Rather use something like Airflow but its not as easy to use for most teams.

Jgrubb 3y ago
As the other poster mentioned, DBT is an open core product and I'm not sure how you'd use the cloud service on a local DB install anyway. Airflow and DBT don't really do the same things anyway.
contravariant 3y ago
I'm not sure those two solve the same problem. Regardless airflow is an amazingly useful tool.
Hasnep 3y ago
dbt-core is open source and can be used locally.
bart_spoon 3y ago
DBT is open source and can be used entirely for free. They sell a hosted version. Also DBT and Airflow are different tools, aimed at solving completely different problems. The Airflow competitor in this article is Dagster, which is also open source.

skypanther 3y ago

I only skimmed the article due to its length. But I didn't see anything like a comparison to other toolchains. Like, how is this better/different/worse than Airflow + dbt + Snowflake?

RockyMcNuts 3y ago
Snowflake isn't local, you have to pay for cloud ... Airflow is Airflow, complexity, steep learning curve, there is a whole industry trying to be as/more powerful than Airflow with less complexity and cleaner integration with modern things you might want to do like k8s, although of course Airflow is still super popular and powerful. the Dagster folks have some comparisons, of course there are popular modern alternatives other than Dagster https://dagster.io/blog/dagster-airflow https://dagster.io/blog/dagster-airflow https://dagster.io/vs/dagster-vs-airflow https://dagster.io/vs/dagster-vs-airflow
remram 3y ago
It does feel like it was lengthened with GPT. There's so many essay-style "now we propose to show that...", it's uncanny. > we argue that we can rethink the current state of data transformation pipelines (intro) > In the blog we will cover: > We show a way how you can combine the best of both worlds > We will tackle its impact and explain more in the following development section. edit: yeah it's ChatGPT: > Parts of this text were adeptly generated by ChatGPT but enhanced by real humans. I guess it's the future, turn a tweet into a 5-page essay with your AI, so your readers can summarize it back to tweet-length with their AI.
- geoHeil 3y ago
  Hi, Georg - one of the authors is here. Indeed, we used LLMs/GPT4 for proof reading and enhancing the English language (we are not native speakers). We were thinking about breaking up the content - but decided that one long post is a better fit. > > We show a way how you can combine the best of both worlds In fact, that sentence I wrote by hand : )
bart_spoon 3y ago
Far more helpful to me than these essays on the benefits of a particular paradigm is a simple, minimal example in a repo that I can dig into and explore.
adammarples 3y ago
It just seems like one choice, among many. The dagster-dbt library in particular seems like a slightly pointless wrapper around the dbt cli and the json artifacts it creates. I've been using airflow and astronomer-cosmos which is not perfect but I didn't enjoy using dagster last time I tried.

jakozaur 3y ago

It's a cool idea, but it looks incomplete for the production use case. 1. Usually, you want to run some warehouse all the time. Bring their data through ETL, run transformation, and report. This goes against the local environment. Ideally, I would love a cloud warehouse, which each engineer could easily fork to their laptop. 2. Almost all companies already have some data setup. The migration path is very unclear. Most likely, this is a secondary system for the majority of companies. Ideally, I would love to describe how I can use it along big platforms (e.g. BigQuery or Snowflake).

dappermanneke 3y ago
what stops you from running this on a container and dumping the results with a script to be queried by a reporting solution into bigquery or snowflake?
- jakozaur 3y ago
  I already see companies with BigQuery setuped. I would like to quickly grab some data, queries and dashboards and run them in my local warehouses.
  - geoHeil 3y ago
    If you use bigquery in big lake mode - i.e. with parquet or iceberg or delta files on GCS (or other object store) you can easily pull in the data into duckdb as well
- geoHeil 3y ago
  Hi, Georg - one of the authors is here. In fact, this is exactly what we argue: Keep data the consumer experience high a Paas platform (Fabric, BigLake, Databricks, SF, ...) can make a lot of sense, whereas for the best data development/creator experience a high-code solution based on solid software engineering best practices should be the preferred solution - at least in my/our(authors) opinion.
rodrigodlu 3y ago
I could add more but on a newish local stack I was trying: I spent a good amount of hours on duckdb this week to process personal data from data dumps (social networks, etc) and now I'm back to the combo of postgresql in containers + sqlite. After the initial imports and some massaging with queries that felt awesome, I found hard to step-up the game building the relationships I wanted. The last stroke before switching was the lack of managing foreign keys without recreating entire tables again. I can go over other examples. It can be done, but it just takes you out of the flow when you're analyzing the data + cleaning it, specially because I know that I can do with psql and sqlite in a blink of an eye. Since many etl tools don't care about the target database being these old and trusty fellas, I felt losing a lot of time just to get rid of a postgres install that is right now consuming only 200 mb of ram on a docker/podman container. Or working around some sqlite ingestion issues with simple notebooks + pandas/polars/etc. in my pov it seems a shaky ground for an entire new stack I appreciate duckdb taking me out of the comfort zone tho.

OJFord 3y ago

Tools like Dagster, dbt, and AWS Glue always strike me as built for BI/in-house analytics over user & app-generated data. What do people use for ingesting data from end-users, their transactional CRUD to it too, and then OLAP queries triggered (not written) by and for those same end users?

lysecret 3y ago
Bunch of python code.
benjaminwootton 3y ago
There are a bunch of collector tools for capturing application side events and sending them into your server. The common stack is then to ingest them from Kafka into an OLAP datastore like ClickHouse or Pinot. Then I think you are referring to what is sometimes called “user facing analytics” against that datastore. We like to use a tool called Cube.dev as middleware between the app and OLAP database which gives APIs and a semantic layer.
- OJFord 3y ago
  'user-facing analytics' and (via Cube.dev) 'embedded analytics' are helpful terms, thank you!
- totalhack 3y ago
  +1 for using a semantic layer. I built (and open-sourced) one for use in my own business but Cube looks well done. https://github.com/totalhack/zillion https://github.com/totalhack/zillion
- geoHeil 3y ago
  Not to forget https://www.starrocks.io/ https://www.starrocks.io/ or https://risingwave.com/ https://risingwave.com/ - see also a recent blog post of a friend: https://juhache.substack.com/p/moving-from-bi-to-data-apps-part?r=l9wvi https://juhache.substack.com/p/moving-from-bi-to-data-apps-p...
gms 3y ago

jasmcole 3y ago

If you're interested in interrogating dbt models using DuckDB, you may be interested in some new dbt features we've recently released at https://count.co https://count.co

dotandgtfo 3y ago
Your link probably doesn't lead where you want it to btw.
- jasmcole 3y ago
  Thanks for the heads up - a page more focussed on dbt specifically is here: https://count.co/product/data-modeling/ https://count.co/product/data-modeling/ and associated blog post: https://count.co/blog/how-we-made-data-modeling-multiplayer/ https://count.co/blog/how-we-made-data-modeling-multiplayer/
  - dotandgtfo 3y ago
    Thanks! I was logged into count so I just got sent straight to my homepage as well. Seems interesting

mgaunard 3y ago

I use DuckDB, it's a great piece of tech. Only way I could figure out how to use it though was to read the source (found a few interesting bugs while doing so as well)

hackyhacky 3y ago
Which bug?
wenc 3y ago
No code is fully free of bugs. Did you submit an issue or PR?
- mgaunard 3y ago
  I didn't do all of them because I found them on the job. Submitted one bug report for the thing I didn't know how to fix.

kalendos 3y ago

I've been working with this stack (building Open Data Portals¹) for a few months and am super happy with how well everything plays together. ¹ https://github.com/davidgasquez/gitcoin-grants-data-portal https://github.com/davidgasquez/gitcoin-grants-data-portal

geekodour 3y ago
Hi, i am working on something similar and was looking for ways how i can host my open data. the approach seems interesting, can i reach out to you to discuss more on this somewhere?
- kalendos 3y ago
  Sure! You can find my contact details on GitHub: https://github.com/davidgasquez https://github.com/davidgasquez.

datadrivenangel 3y ago

I've helped with multiple data engineering teams that suffer tremendously due to a lack of local setup, so DuckDB and the right choice of tools looks immensely promising. Also for 99% of companies and data workloads the optimal choice should usually be a janky bash script, so this provides meaningful competition for annoyingly medium data.

riku_iki 3y ago
> I've helped with multiple data engineering teams that suffer tremendously due to a lack of local setup why remote setup is not enough for those teams?..
- datadrivenangel 3y ago
  remote setup was enough, but our dev environments were never quite 100% the same as prod because copying all the data would have been too expensive, and the remote environments were always just a little higher latency for things than the local computers... Just a little bit worse in ways that annoyed people.

hbarka 3y ago

On Dagster’s website there are comparisons to dbt. Is it complementary (as positioned in this stack) or competitive? Dagster versus dbt https://dagster.io/vs https://dagster.io/vs Migrating off dbt cloud https://dagster.io/blog/migrate-off-dbt-cloud https://dagster.io/blog/migrate-off-dbt-cloud

jnac 3y ago
Dagster (orchestration tool) is complementary to dbt core (open source CLI tool for defining data transformation jobs) but competitive to dbt cloud (SaaS, orchestrates dbt jobs).
mritchie712 3y ago
you can run dbt core via a dagster job (so it'd replace dbt cloud)

geoHeil 3y ago

We (Aleksandar and Georg) want to share our new blog post on: "Dagster, dbt, DuckDB as new local MDS" here. It re-introduces the local environment to enhance the developer productivity for data pipelines by bringing back software engineering best practices. We suggest that PaaS platforms should become an implementation detail and refine the new local stack with great a data consumer experience by combining the best of both worlds. Please take a read here: https://georgheiler.com/2023/12/11/dagster-dbt-duckdb-as-new-local-mds/ https://georgheiler.com/2023/12/11/dagster-dbt-duckdb-as-new... We (Georg Heiler and Aleksandar Milicevic) are keen to discuss the proposed new stack with you. Do not hesitate to reach out.

deleted 3y ago
[deleted]
Jgrubb 3y ago
Hey Georg, thanks for posting. I've been working on building a thing with almost the same stack lately and the Dagster integration is my next large-ish step. In the last couple weeks I've kicked around Duck and Clickhouse for the backend. It's been a lot of fun. Ultimately I'd like something that can be run locally or easily installed and run on a PaaS. The only trick with local is that data sets of any appreciable size take ages to pull down, at least here in the US with our terrible internet (in the average Italian mountain village this would probably work great).
- geoHeil 3y ago
  Well local can mean your laptop. But could mean your local server. However, it can also mean a VM on your cloud provider of choice colocated to the object store. In such a case the network transfer (for many cases) can be almost irrelevant - at least for up to medium-ish sized datasets.
  - Jgrubb 3y ago
    Funny thing, I'm trying to get going with Dagster today like I said, and figuring out how to debug is a bit .. annoying. The most helpful post I've come across so far just happens to be https://georgheiler.com/2022/02/02/interactive-dagster-debugging/ https://georgheiler.com/2022/02/02/interactive-dagster-debug... So thanks!