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.

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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 Like
      • Dave W
      • Dave_W
      • 8 mths ago
      • Reported - view

      Neha Kumar Thanks for your response, Neha! I'm not sure I follow the workaround. My understanding was that the underlying table must be cached. If I create a view with the rows I want in Periscope, in order to materialize that view, don't I need to cache the entire underlying table?

      Reply Like
      • Neha Kumar
      • Solutions Engineer
      • Neha_Kumar
      • 8 mths ago
      • Reported - view

      Dave W The workaround uses a view created on your database. For clarity I'll define the two views involved, as I understand that they definitely sound quite similar:

      Database View: You create this view directly on your database (outside of the Periscope UI). Periscope treats a database view like it's a normal table

      Periscope View: This is what the Periscope UI offers (details on Periscope views here). To materialize a Periscope view, the underlying data sources used in the view must be cached

      Here, we suggest creating a Database view. You can cache the database view as if it is a normal table on your database via the Cache console in Periscope. Then, once you have cached your database view, we can create a materialized view in the Periscope View UI to do any additional calculations / processing. 

      The overall workflow is Original Table -> Database View -> Cache the Database view in Periscope -> Create Periscope View on the cached database view

      If you have any further questions, please feel free to reach out out our team over chat in-app in Periscope! We'd love to discuss your exact case in further detail with you

      Reply Like
Like Follow
  • 8 mths agoLast active
  • 3Replies
  • 243Views
  • 2 Following