Hi I am new to dbt and using views in general, never really found a good use case for them, but reading the docs for dbt I get it.
Having said that, how do they get called with a where clause?
I have a view set up right now that has all the data for the past year with no filter.
If I call that view on an incremental model, does the whole view run and then get filtered?
Can the is_incremental() macro be added to the view code?
Should I just make my view a partitioned table instead?
I feel like I only think in partitioned tables and can’t fully understand a good use case for a view that can handle being backfilled on a full resync. Or am I supposed to unfilter - resync - add the filter back in?
Thank you in advance!
I am unfamiliar with BQ, so we’re going to go on an adventure together!
This depends on the specific optimisations of the warehouse, but it should be able to push the filter into the view instead of processing the whole query and then throwing away the unwanted data. Understanding query optimisers is a dark art, but I found this write up really interesting: Understanding the Snowflake Query Optimizer
No, it’s only available inside of incremental models. But, as discussed above if you use the is_incremental macro inside of an incremental model to filter the results of a view, the optimiser should take that into account.
Only if performance demands it - I try to leave everything as views until that gets slow, and then strategically change some things to be tables to speed them up.
You might find it useful to think about views as a named, saved query that gets run on demand. It only exists when summoned, it’s not so much that you create it with filters and then dismiss it afterwards
Hi @joellabes
Im was looking an answer to this question:
You mentioned that depends on the DB optimizer but the filter should be applied to the view by the optimizer.
Do you have any insights about Postgres about this?
Thanks