TL;DR: Should I just create views on top of incremental tables? My gut says probably.
We have a number of incremental tables that we refer to as ‘daily timeseries’ tables. They reflect the state of a record at the end of each day.
dbt is great for these materialising these tables, except in a few use-cases where we need to use window functions that evaluate the whole table. Because we only load yesterday and today’s data in each incremental load, the window functions would only evaluate the values for those two days instead of the whole table.
We have started building views on top of the incremental models to calculate the window functions, which is working okay, but I wanted to see if there was a preferable way of dealing with this that others have found? I don’t really have an issue with the view method, except that it appears to make some queries against the final output a bit slower, even when the window function isn’t being used. (For the most part, we get around this issue by dynamically changing which model is queried in Looker using the
in_query liquid variable in LookML.)
Has anyone else found other ways of dealing with this? Thanks!