Materialize Views Without Caching Underlying Tables
I'd like to add a product request. Periscope is very close to being a data warehouse solution. Analysts can design views just as they'd design tables and those views can then be used by analysts and other users as vetted, curated tables, perhaps with business logic embedded. However, without materialized views, performance becomes prohibitively slow very quickly. Unfortunately, even with the caching option, views can only be materialized if the underlying data is cached. Since one point of creating warehouse tables is to reduce the size of underlying tables, it's not cost-efficient to cache the underlying tables. In our case, our underlying data is 10-20x larger than the views we're creating.
We won't be purchasing the cache option because our underlying data is too large and getting larger. Please consider adding the ability to cache only the materialized view. This seems technically feasible - just schedule the view query and cache the result.
Thanks for your consideration.
Hi Dave W - thanks for reaching out and sharing your use case in such detail! I'd love to share your use case with our product team and definitely understand the appeal to being able to cache a view without pulling in the full underlying data.
Reading through - I can suggest a workaround that uses the current cache setup. We have the ability to cache views on your database. In other words, you can create a view on your database that only has the rows you want to use in your materialized view (for instance, a view that has data for only the last 6 months). By caching the database view you are (1) using your cache resources in the most cost-effective manner and (2) still have the ability to create materialized views with that subset of data.
Let me know what you think of that above approach. For further questions, please feel free to reach out to our support team directly through Live Chat (found at the bottom corner of your screen in-app). We have worked with several customers to propose in-depth solutions to meet their unique warehousing and caching needs, so we'd love to discuss your particular case at greater length in conjunction with your Customer Success Manager.
Looking forward to hearing from you!Reply