Hi @amy
thank you for sharing this. We are actually implementing something very similar to the lambda-view that you described, but only bespoke for a few core models.
I am recently thinking about how to build a generalized approach to enable lambda-view on any given model.
Thank you @claire to share the lambda-view in dbt-lab-experiment.
Our use cases are very similar to yours, but with some additional challenges.
One challenges for us is that some our model queries are super complex, so we can not just define a bunch of views and chain them together, the query would be over-complexity. That’s why we need the lambda-view of some models to be a temp table to break down the query complexity.
The other challenge is how to filter down the near-current-time source input to as minimal as possible. For example, we have location-time-series measurements. We only fetch a few locations in each lambda-view call. many our model logic is too complex to pass the filter predicate down through view. So we need to define a cte-macro or a stored_procedure for that.
I am doing some experiments with customized materialization that could enable a lambda view for any given incremental model, The lambda-view would be created as a macro or store_procedure, which when called, will return a cte-sub-query or a temporary table.
BTW, we use BigQuery. hope to chat more.