Cannot persist Native Derived Table for view "xxxx" with dynamic filters

I really need dynamic filter, and also need persist it.

any suggestion?

We generally don’t recommend that you persist derived tables of any kind— But I think that if it’s a non-native derived table, it won’t throw an error like that.

Be advised that it can lead to excessive bloat of the scratch schema, as each dynamic filter option will create a new PDT table, rather than overwriting the previous one, and you’ll wind up with as many tables as you’ve got filter options.

Perhaps if you say more about your use case/why you need this, someone can provide an alternate way!

1 Like

I faced similar issue.

I have created dashboard which is embedded on clients portal and he wants to see it updated whenever new data is added into database. His concern was he wants to do it manually from his end and I thought off for the option " Clear Cache & Refresh", but that option is not available for embedded dashboards.
So I tried implementing Datagroup, setting sql_trigger to ‘EVERY HOUR’. However it didn’t work out for me, as my looks were created using derived tables containing templated filter within it and Looker doesn’t allow derived table containing Templated filter or Liquid Parameter to be persistent because the dynamic filters will cause the table to rebuild every time the filter changes, and can cause a huge amount of strain on your Database.

The next option I tried was using sql_trigger_value in derived table. It worked when I removed templated filter from my query but not after using it. Even tried out using persist_for but it says no built in Admin --> PDT.

So, we can either have a PDT that runs every hour and pick up new data or we can have a derived table that has dynamic filters.

In the end, I believe the only option we have as the two things won’t work together, is then to create this Derived table in your DB with your ETL.