I have a very heavy query I want to use the resutls of this query to create over 50 different charts and tables.
But the query is heavy and I dont want it to run eveyr time, I would like it to run once for the netire dashboard and then use its results as the basis for all the other queries / charts on the dashboard is this possible?
Thank you
Hi there,
I believe one way is to build questions starting from a common Model (the "heavy" part of the query), and then enabling Model persistence to cache the query results of the Model into a table.
Once that is set up, questions will grab the results directly from the cache without running the Model's query each time.
It works similarly to materialized views (which are another workaround you could try as well)
Thank you,
I am using Athena, I think it is not support but I am not sure based on the documentation.
I am using the Starter tier, and I only need to cash this specific query, is that possible or will now everything be cashed?
He's suggested caching the model, not all queries. Only questions based upon the model will used cached data. You just set the update frequency of the model (it works differently to question caching).
we don't support model caching in athena yet
you should do a data pipeline that runs this query in a certain cadence and persists it back to an S3 bucket, that's the only way
re: "questions will grab the results directly from the cache without running the Model's query each time" - are you sure about that? Reading the Metabase documentation, I can't find anything that suggests any local cache is being stored, so even though Metabase create an object in the underlying database schema (a materialised view/table), I think the questions will still fire SQL direct to that table every time as oppose to hold it in RAM/cache? The "cache refresh" will presumably just rebuild that materialised table on a schedule, but the query still hits that database table regardless, unless I missed it in the documentation? I don't think this is the same as question/query cache.
You're right, sorry, my answer was a bit unclear on that.
Instead of running the model's query every time you're trying to see a question based on that model, it will run a simpler query to fetch the cached results from the persisted Model's table.
This still involves querying the database, and the cache is stored in tables so this will fire SQL. In cases where the Model stores a huge set of results, for example, there might be no performance improvement at all, so it is something to keep in mind
How does the model persistence work? I created a model set it to be persisted, based on that model I created a question. I added three of these questions to a dashboard. However when I view the dashboard I see three queries going to the database every time.. and dont see anything in the persistence schema except one table called cache_info which has one record