I created an explore by joining few tables(TeraData). Then I created a view on this explore using derived tables with some case statements to act on this explore. When I ran the explore, Looker created a volatile table in the name of the explore and then does the case statement manipulations by referring the volatile table. My question is, the volatile table created stays in the memory of TeraData or Looker? How long does it stay in the memory?
When you create a derived table, if you don’t add persistence to it, then it will be executed at query run-time in a CTE or subquery and not stored anywhere. If you do add persistence, it will be written to a scratch schema on your Teradata connection.
That table will then exist until the persistence wears off— Depending on how you’ve configured persistence, it will either vanish, or it will regenerate itself to have the most up-to-date data.
There’s lots more here: https://docs.looker.com/data-modeling/learning-lookml/derived-tables#temporary_and_persistent_derived_tables, but tl;dr: The table will be created in the scratch schema of your Teradata database, and it will remain there until the persistence you’ve set expires.