4 ms·

I highly recommend dbt (data build tool) for a modular approach to sql; postgres, snowflake, bigquery or redshift. https://github.com/fishtown-analytics/dbt ht

I highly recommend dbt (data build tool) for a modular approach to sql; postgres, snowflake, bigquery or redshift. https://github.com/fishtown-analytics/dbt https://github.com/fishtown-analytics/dbt

veritas3241 6y ago
Seconded! And you can build your own adapters for other databases as well.
aylr 6y ago
Thirded! It is hands down the best data tool I've used in years. It's a total delight and composes SQL DAGs out of your SQL files using tiny bits of jinja templating. If you know SQL you can learn it in an hour.
throwaway_pdp09 6y ago
I'm afraid I don't understand it. I assume a data warehouse is in a relational DB anyway, so from https://docs.getdbt.com/docs/introduction/ https://docs.getdbt.com/docs/introduction/ "dbt (data build tool) enables analytics engineers to transform data in their warehouses by simply writing select statements. dbt handles turning these select statements into tables and views" So you are taking some runnable SQL and... running it? Why does SQL need to be turned into tables an views anyway? What problem is this solving?
- chrisjc 6y ago
  > Why does SQL need to be turned into tables an views anyway? What problem is this solving? So that data is aggregated, summarized, cleansed, transformed, join, normalized, whatever before you query it. It's much cheaper, faster, etc that doing all of that ahead of time instead of query time. > I assume a data warehouse is in a relational DB anyway Not sure what you mean that a data warehouse is in a relations DB? Data in data warehouses can be set up in a relational way, but it certainly doesn't have to be. Perhaps you need to get a better understanding of olap vs oltp? https://stackoverflow.com/questions/21900185/what-are-oltp-and-olap-what-is-the-difference-between-them https://stackoverflow.com/questions/21900185/what-are-oltp-a...
  - throwaway_pdp09 6y ago
    > Not sure what you mean that a data warehouse is in a relations DB? That you're using an RDBMS of some sort. It understands SQL and the data is in the conventional tabular form. I understand olap vs oltp. My question was whether eg. snowflake was an RDBMS (I did check wikipedia and the snowflake website but neither said). If it wasn't then I could see the benefit of this tool). > So that data is aggregated, summarized... That's what SQL is good for, why (if we're already in an RDBMS) do I need another tool on top to do this? > It's much aggregated, summarized, cleansed, transformed, join, normalized ... that doing all of that ahead of time instead of query time. Of course, that's what a DW does as it pulls in new data. But that is what SQL also does rather well, so what does this tool bring to the party?
    - chrisjc 6y ago
      DWs like Snowflake certainly share some features you'll find in traditional RDBMSs, but I'm not sure I would classify it as one. https://www.quora.com/Is-Snowflake-a-RDBMS https://www.quora.com/Is-Snowflake-a-RDBMS > That's what SQL is good for, why (if we're already in an RDBMS) do I need another tool on top to do this? Writing, versioning, testing, deploying, monitoring all that complicated SQL is very challenging. This is what DBT is good for. DBT doesn't replace SQL, it simply augments it by allowing you templatize, modularize, figure out all the inter-dependencies, etc... > Of course, that's what a DW does as it pulls in new data. But that is what SQL also does rather well, so what does this tool bring to the party? Sounds like your talking more about ETL, but from what I understand DBT is really useful for what happens after ingestion. Transforming, refining, aggregating, etc from one internal DW source to another. This is where you would want to use SQL, but again, DBT doesn't replace SQL, it augments it. ---- BTW, I've never used DBT and have only really looked into it in the last 24hrs since we're really struggling with the pain of managing all of our ETL (table->table) SQL. I really see where it could make our lives a lot simpler, but it doesn't seem to provide the functionality that we need for our workloads. We're using Snowflake change streams to build refined, summary and aggregation tables off an ingestion table (Kafka Connect to SF). We use change streams on that ingestion table to track new data that we can refined, summarized and aggregated into final tables in a near real-time fashion that's used for analytics and reporting. Change streams (and tasks and procedures) is not something that DBT seems to be ready for, but it's probably understandable since they're trying to provide functionality to more DWs than just SF.

I highly recommend dbt (data build tool) for a modular approach to sql; postgres, snowflake, bigquery or redshift. https://github.com/fishtown-analytics/dbt https://github.com/fishtown-analytics/dbt

veritas3241 6y ago

Seconded! And you can build your own adapters for other databases as well.

aylr 6y ago

Thirded! It is hands down the best data tool I've used in years. It's a total delight and composes SQL DAGs out of your SQL files using tiny bits of jinja templating. If you know SQL you can learn it in an hour.

throwaway_pdp09 6y ago

I'm afraid I don't understand it. I assume a data warehouse is in a relational DB anyway, so from https://docs.getdbt.com/docs/introduction/ https://docs.getdbt.com/docs/introduction/ "dbt (data build tool) enables analytics engineers to transform data in their warehouses by simply writing select statements. dbt handles turning these select statements into tables and views" So you are taking some runnable SQL and... running it? Why does SQL need to be turned into tables an views anyway? What problem is this solving?

chrisjc 6y ago
> Why does SQL need to be turned into tables an views anyway? What problem is this solving? So that data is aggregated, summarized, cleansed, transformed, join, normalized, whatever before you query it. It's much cheaper, faster, etc that doing all of that ahead of time instead of query time. > I assume a data warehouse is in a relational DB anyway Not sure what you mean that a data warehouse is in a relations DB? Data in data warehouses can be set up in a relational way, but it certainly doesn't have to be. Perhaps you need to get a better understanding of olap vs oltp? https://stackoverflow.com/questions/21900185/what-are-oltp-and-olap-what-is-the-difference-between-them https://stackoverflow.com/questions/21900185/what-are-oltp-a...
- throwaway_pdp09 6y ago
  > Not sure what you mean that a data warehouse is in a relations DB? That you're using an RDBMS of some sort. It understands SQL and the data is in the conventional tabular form. I understand olap vs oltp. My question was whether eg. snowflake was an RDBMS (I did check wikipedia and the snowflake website but neither said). If it wasn't then I could see the benefit of this tool). > So that data is aggregated, summarized... That's what SQL is good for, why (if we're already in an RDBMS) do I need another tool on top to do this? > It's much aggregated, summarized, cleansed, transformed, join, normalized ... that doing all of that ahead of time instead of query time. Of course, that's what a DW does as it pulls in new data. But that is what SQL also does rather well, so what does this tool bring to the party?
  - chrisjc 6y ago
    DWs like Snowflake certainly share some features you'll find in traditional RDBMSs, but I'm not sure I would classify it as one. https://www.quora.com/Is-Snowflake-a-RDBMS https://www.quora.com/Is-Snowflake-a-RDBMS > That's what SQL is good for, why (if we're already in an RDBMS) do I need another tool on top to do this? Writing, versioning, testing, deploying, monitoring all that complicated SQL is very challenging. This is what DBT is good for. DBT doesn't replace SQL, it simply augments it by allowing you templatize, modularize, figure out all the inter-dependencies, etc... > Of course, that's what a DW does as it pulls in new data. But that is what SQL also does rather well, so what does this tool bring to the party? Sounds like your talking more about ETL, but from what I understand DBT is really useful for what happens after ingestion. Transforming, refining, aggregating, etc from one internal DW source to another. This is where you would want to use SQL, but again, DBT doesn't replace SQL, it augments it. ---- BTW, I've never used DBT and have only really looked into it in the last 24hrs since we're really struggling with the pain of managing all of our ETL (table->table) SQL. I really see where it could make our lives a lot simpler, but it doesn't seem to provide the functionality that we need for our workloads. We're using Snowflake change streams to build refined, summary and aggregation tables off an ingestion table (Kafka Connect to SF). We use change streams on that ingestion table to track new data that we can refined, summarized and aggregated into final tables in a near real-time fashion that's used for analytics and reporting. Change streams (and tasks and procedures) is not something that DBT seems to be ready for, but it's probably understandable since they're trying to provide functionality to more DWs than just SF.