Understanding `datagroup`, `persist_with` , and `persist_for`in LookML

In LookML, the modeling language used by Looker, managing data caching is critical for balancing query performance and data freshness. Three key concepts: datagroup, persist_with, and persist_forprovide granular control over caching within the model file. This article offers an exhaustive explanation of these concepts using an analogy, a diagram, an example, plain speech, and technical language, culminating in a comprehensive summary.

Analogy

The Notebook Concept: Imagine you’re a student with a notebook where you jot down answers to questions you’ve already solved. This saves you from recalculating or rethinking every time someone asks the same question; much like caching in Looker, where query results are stored for reuse.

Updating Rules with datagroup: Now, picture setting rules for when to update your notebook. You might decide to refresh your notes every morning (a time-based rule) or whenever a teacher gives you new information (a condition-based rule). In LookML, a datagroup is like these rules; it defines when cached data should be refreshed, either on a schedule or when specific conditions are met.

Choosing Rules with persist_with: Suppose your notebook has sections; like math, history, and science; and you apply different updating rules to each. For math, you update daily; for history, weekly. In LookML, persist_with lets you assign a specific datagroup to an explore or model, tailoring the caching policy to that component, just as you tailor rules to notebook sections.

Setting a Timer with persist_for: Alternatively, imagine writing an answer in your notebook and adding a note: “Keep this for one week, then redo it.” This is akin to persist_for in LookML, where you set a fixed time limit for how long a cached result remains valid before it’s discarded and recalculated.

Why It Works Together: Your notebook system ensures you’re efficient (reusing answers) yet current (updating when needed). Similarly, datagroup, persist_with, and persist_for work together in LookML to optimize performance while keeping data fresh, adapting to different needs across your model.

Diagram

Below is a Mermaid flowchart depicting the caching decision process in LookML, illustrating how datagroup, persist_with, and persist_for influence whether cached data is used or refreshed.

Explanation of Components:

• A (User runs query): The process begins when a user executes a query in Looker.
• B (Is there a cached result?): Looker checks if a cached result exists for this query.
• C (Is cache still valid?): If a cache exists, its validity is assessed based on the caching policy.
• D (Run query and cache result): If no cache exists or it’s invalid, Looker runs the query and caches the result.
• E (Use cached result): If the cache is valid, Looker returns it without rerunning the query.
• F (Caching Policy): This input represents the policy governing validity, splitting into two paths:
• G (persist_for): Validity depends on whether the cache’s age is less than the time set by persist_for (e.g., "24 hours").
• H (datagroup via persist_with): Validity hinges on whether the datagroup’s trigger (e.g., a SQL condition or time event) has fired since the cache was created.

Significance: This diagram highlights the decision-making process Looker uses for caching, showing how persist_for offers a simple time-based check, while datagroup (via persist_with) enables more dynamic, trigger-based control. It visually ties the concepts to their practical application in query execution.

Example

Here’s a concrete LookML example demonstrating datagroup, persist_with, and persist_for within a model file:

Scenario: You’re modeling sales data and want to manage caching for an orders explore. You’ll define a datagroup for daily refreshes and compare it with a persist_for approach.

Code:

      # Define a datagroup for daily cache refresh at midnight
    datagroup: daily_sales_refresh {
      mac_cache_age: "24 hours"
      sql_trigger: SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP) ;;
    }
    
    # Model file with explores using different caching strategies
    model: sales_model {
      # Explore using datagroup via persist_with
      explore: orders_with_datagroup {
        from: orders
        persist_with: daily_sales_refresh
        description: "Orders refreshed daily at midnight via datagroup"
      }
    
      # Explore using persist_for
      explore: orders_with_persist_for {
        from: orders
        persist_for: "24 hours"
        description: "Orders cached for 24 hours from last query"
      }
    }

Breakdown:

Datagroup Definition:

• datagroup: daily_sales_refresh: Creates a named caching policy.
• sql_trigger: SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP): This SQL query changes value at midnight daily, invalidating the cache and triggering a refresh on the next query.

Explore with persist_with:

• explore: orders_with_datagroup: Links to the orders view.
• persist_with: daily_sales_refresh: Ties this explore to the daily_sales_refresh datagroup, ensuring its cache refreshes daily at midnight.

Explore with persist_for:

• explore: orders_with_persist_for: Also links to the orders view.
• persist_for: "24 hours": Sets a 24-hour cache duration, meaning the cache is valid for 24 hours from when it was created, refreshing only when a query finds it expired.

Behavior:

• For orders_with_datagroup, the cache resets at midnight daily, regardless of when queries run.
• For orders_with_persist_for, the cache lasts 24 hours from each query’s execution, expiring individually per query.

Significance: This example shows how datagroup offers scheduled, proactive control, while persist_for provides a simpler, reactive time limit, both applied within the model file to tailor caching per explore.

Plain Speech

What’s Caching?: In Looker, when you ask a question about your data ; like “How many orders did we get this month?”; it can save the answer. Next time someone asks the same thing, Looker just pulls out the saved answer instead of figuring it out again. This saving trick is called caching, and it makes things faster.

Get Michael Stroud’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Why Refresh?: But data changes; like new orders coming in; so you don’t want to keep old answers forever. You need rules to decide when to toss the saved answer and get a new one.

persist_for - The Timer: One way is like setting a timer. You tell Looker, “Keep this answer for 24 hours, then forget it and get a fresh one.” That’s what persist_for does. It’s simple: after the time’s up, the next person asking gets a new answer.

datagroup - The Smart Rules: Another way is smarter. Imagine checking every day at midnight to see if your answers need updating, or watching for a signal—like new data arriving.

• A datagroup is like that: it sets rules for when to refresh, either at set times or when something changes.

persist_with - Picking Rules: Now, you might have different parts of your data; like orders or customers; and want different rules for each. persist_with lets you say, “For orders, use the midnight refresh rule.” It picks a datagroup for that part of your model.

How It Fits: These tools let you control caching in your Looker model file. You decide what’s fast and what’s fresh, section by section, like organizing a filing cabinet where some files expire quickly and others get a daily overhaul.

Technical Language

Overview: In LookML, caching enhances query performance by storing results, governed within the model file by datagroup, persist_with, and persist_for.

Datagroup:

Definition: A datagroup is a named entity defining a caching policy.

Parameters:

• sql_trigger: A SQL query (e.g., SELECT MAX(updated_at) FROM orders) whose result change invalidates the cache. For time-based triggers, a query like SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP) shifts daily.
• max_cache_age: An optional time limit (e.g., "24 hours") for cache validity, though typically paired with triggers.

Behavior: When the trigger fires (e.g., SQL result changes), associated caches are invalidated, prompting a refresh on the next query.

Persist_with:

Definition: A parameter in model or explore blocks linking them to a datagroup.

Syntax: persist_with: datagroup_name.

Scope: Can be set at the model level (default for all explores) or explore level (override).

Effect: Applies the datagroup’s policy to queries for that component, ensuring consistent refresh timing.

Persist_for:

Definition: A parameter specifying a fixed cache duration (e.g., “24 hours”).

Syntax: persist_for: "duration".

Scope: Applicable at model level (default) or explore level (specific override).

Behavior: Cached results are valid until the duration elapses from creation; subsequent queries refresh the cache if expired.

Execution Flow:

• Query runs → Check for cached result → Validate cache:
• If persist_for, compare cache age to duration.
• If persist_with ties to a datagroup, check if trigger has fired since caching.
• Valid cache → Return result; Invalid/No cache → Execute query, cache result.

Distinctions:

• datagroup enables dynamic, trigger-driven policies; persist_for is static, time-based.
• persist_with integrates datagroup flexibility into specific model components.

Conclusion/Summary

• datagroup: A versatile tool for defining caching policies with triggers (SQL or time-based), offering precise control over refresh timing within the model file.
• persist_with: Bridges datagroup policies to explores or models, enabling tailored caching strategies per component.
• persist_for: A straightforward time-based caching option, setting fixed expiration periods for simplicity.
• Combined Power: Together, these constructs allow LookML developers to optimize query performance and data freshness, balancing efficiency (reusing cached results) with accuracy (refreshing as needed). Whether using proactive datagroup triggers or reactive persist_for timeouts, they provide a robust framework for managing caching in Looker models.

This comprehensive exploration; from relatable analogies to technical precision; equips you with a deep understanding of how datagroup, persist_with, and persist_for function in LookML, empowering effective data modeling in Looker.