3 ms·

Whether you're trying to do this for a data warehousing use case, or in an environment where you are creating production database queries will change the approa

Whether you're trying to do this for a data warehousing use case, or in an environment where you are creating production database queries will change the approach you need to take here. We have been working on SQLX for the data warehousing use case, allowing you to embed JavaScript into your SQL queries and making things like code re-use much easier while fitting in with your existing SQL dialect, might be of interest - https://docs.dataform.co/guides/sqlx https://docs.dataform.co/guides/sqlx. Some of the concepts in there are specific to our framework, some are not. For data warehousing, create more views! They are IMO the right level of abstraction for encapsulating common business logic, data definitions, joins etc, making composing downstream queries much easier. Managing lots of views like this requires some investment in a data modelling tooling tool however (Dataform, DBT etc). For generating queries used against production databases for building user applications, none of this applies.

chrisjc 6y ago
> For data warehousing, create more views! Agreed, but... the problem with views are they aren't parameterizable. In effect they are static templates. Fortunately, modern data warehouses often provide user defined table functions that accomplish pretty much the same thing, but allow you to "create input parameters to your view". Dataform looks interesting (how have I not heard of this before) but I wonder if they support UDTFs?
- nightski 6y ago
  Sure they are, you just slap a where statement on your query when you query a view. Or am I missing something?
  - chrisjc 6y ago
    Sure, that's sufficient most of the time. However, there times when your view might span multiple tables and you want to ensure that full table scans aren't triggered before your view predicate is acted upon. This may also come down to how sophisticated your DBs sql optimizer is.

chrisjc 6y ago

> For data warehousing, create more views! Agreed, but... the problem with views are they aren't parameterizable. In effect they are static templates. Fortunately, modern data warehouses often provide user defined table functions that accomplish pretty much the same thing, but allow you to "create input parameters to your view". Dataform looks interesting (how have I not heard of this before) but I wonder if they support UDTFs?

nightski 6y ago
Sure they are, you just slap a where statement on your query when you query a view. Or am I missing something?
- chrisjc 6y ago
  Sure, that's sufficient most of the time. However, there times when your view might span multiple tables and you want to ensure that full table scans aren't triggered before your view predicate is acted upon. This may also come down to how sophisticated your DBs sql optimizer is.