LookML Audit Checklist: the 25 Points to Check

A LookML audit reviews five layers of a Looker project: Explore structure, join and aggregation correctness, persistence and caching, code quality (naming, duplication, dead fields) and performance as users experience it. The 25 points below are the ones Neuravoid checks over five days on a production project, in this order, with what each reveals and how to fix it.

Why audit a project that "works"

A LookML project degrades quietly. Each new request adds a field, a join, a derived table. Several years later the Explores have four hundred fields, three measures are called "Revenue" with different formulas, and the executive dashboard takes forty seconds to open. Nobody made a mistake; the model just grew without a gardener.

The audit serves three purposes: restoring trust in the numbers, cutting response times and the BigQuery bill, and making the model ready for conversational agents, which inherit every one of its flaws.

A. Explore structure (points 1 to 5)

1. Number and purpose of Explores. One Explore per business question, not one per table. A project with forty Explores of which thirty are never used (check System Activity, History Explore) carries maintenance cost with no value. Target: fewer than fifteen active Explores, each with a description.

2. Kitchen-sink Explores. An all_data Explore joining twenty views produces slow queries and wrong results as soon as two joins are many-to-many. Split by domain.

3. Exposed fields. The Explore-level fields: parameter and hidden: yes on technical fields cut noise for users and for agents. A four-hundred-field Explore with sixty useful ones is a governance problem, not a training problem.

4. Default filters. Explores on large partitioned tables need an always_filter or conditionally_filter on the partition date. Without one, an innocent exploration scans the whole table.

explore: orders {
  conditionally_filter: {
    filters: [orders.created_date: "last 90 days"]
    unless: [orders.order_id]
  }
}

5. Labels and descriptions. Every Explore, view and important measure carries a business label and a description. This is also the first prerequisite for Conversational Analytics.

B. Joins and aggregations (points 6 to 10)

6. Primary keys declared. Every joined view must declare primary_key: yes on a genuinely unique dimension. Without it Looker cannot enable symmetric aggregates, and sums inflate across one-to-many joins.

7. Correct relationships. relationship: many_to_one, one_to_many, one_to_one must reflect the data, not the intention. A join declared many_to_one where the right-hand table has duplicates produces silent fan-out. Verify with COUNT(*) against COUNT(DISTINCT key).

8. Symmetric aggregates. When a sum measure sits on the "one" side of a one-to-many join, Looker generates SQL with hashing functions to avoid double counting, provided the primary key is declared. Check the generated SQL for SUM(DISTINCT ...) and the key. If the auditor finds sql_distinct_key sprinkled at random, someone has been patching by hand.

9. Join type. type: left_outer by default, inner only when the semantics demand it. An inner join added "to make it faster" drops rows from totals.

10. Count measures. type: count counts rows of the Explore's base view, not of the view where the measure is written, a classic source of confusion. Prefer explicit count_distinct on the business key.

C. Persistence and caching (points 11 to 15)

11. Datagroups defined. Every model needs at least one datagroup with a sql_trigger that genuinely reflects new data arriving, plus a max_cache_age.

datagroup: daily_load {
  sql_trigger: SELECT MAX(loaded_at) FROM `dwh.meta.load_log` ;;
  max_cache_age: "24 hours"
}

12. persist_with at model and Explore level. An Explore without a datagroup inherits a default one-hour cache, often too short for daily data, which multiplies BigQuery queries.

13. Persistent derived tables (PDTs). For each PDT: is it triggered by a datagroup (datagroup_trigger) rather than persist_for? Does it use partition_keys and cluster_keys on BigQuery? How long does the rebuild take (Admin, PDTs)? A PDT rebuilt hourly that scans three years of data costs more than the problem it solves.

14. Incremental PDTs. On large fact tables, increment_key and increment_offset avoid full rebuilds. Rarely used, often the fastest win on the bill.

15. PDT chains. A PDT that depends on a PDT that depends on a PDT creates rebuild cascades. Beyond two levels, the logic belongs in dbt, not Looker.

D. Code quality (points 16 to 20)

16. Duplicated measures. Search for measures whose sql is identical or nearly identical across views. Three definitions of "net revenue" with diverging formulas is the most common and most expensive finding. One measure, in the fact view, reused everywhere.

17. Naming. One convention: snake_case, consistent prefixes for dimension groups, _amount, _count, _rate suffixes on measures. label handles display, never the field name.

18. Unused fields. The System Activity Field Usage Explore lists fields never queried in the last 90 days. Removing half of them is not unusual. Every dead field is a chance for an analyst or an agent to pick the wrong one.

19. Refinements and extends. extends stacked three deep and refinements (view: +orders) scattered across files make the model unreadable. A field should be understandable by opening one file.

20. Continuous validation. Do the LookML Validator and Content Validator pass clean? Is the project on Git with mandatory pull requests? Is there an automated test (Spectacles, or a script on the run_query API) that runs the critical Explores on every PR?

E. Security and performance (points 21 to 25)

21. Access grants. Sensitive fields (salaries, margins, personal data) are protected with access_grant and required_access_grants backed by a user attribute, not merely hidden with hidden: yes.

access_grant: can_view_margin {
  user_attribute: department
  allowed_values: ["finance", "leadership"]
}

22. Row-level security. access_filter on multi-entity Explores, with a user attribute fed by SSO, tested with one account per profile. A hard-coded sql_always_where is not a substitute for per-user filtering.

23. Slow queries. The History Explore sorted by runtime shows the ten slowest queries of the last 30 days. For each: read the generated SQL, check partitions touched in BigQuery, decide between PDT, aggregate table or a dbt model.

24. Executive dashboards. A dashboard with thirty tiles fires thirty queries. Target: under twelve tiles, filters that hit the partition column, and aggregate_table (aggregate awareness) for the most-viewed rollups.

25. BigQuery cost attributable to Looker. Cross the Looker history with BigQuery INFORMATION_SCHEMA.JOBS, filtered on the Looker service account. The twenty most expensive queries of the month show where to act first. Without this number no audit can prove its return.

What the audit delivers

The useful output is not 25 ticked boxes but a hierarchy: what produces wrong numbers (fix this week), what costs money or time (plan this month), what hinders maintenance (fold into ongoing work). On a mid-sized project, the first category usually fits in a development branch delivered with the report.

What we see in production: in a large industrial group running Looker on BigQuery for several years, the three clearest gains came from points 6 to 8 (keys and symmetric aggregates, which had inflated some totals), point 13 (partitioned PDTs) and point 16 (a single definition of revenue). None of the three required a new tool.

FAQ

How long does a LookML audit take?

Five working days for a mid-sized project (ten to thirty Explores, a few hundred fields), covering code review, System Activity analysis, BigQuery cost checks and a prioritised report with the critical fixes on a branch.

Do you need admin access to audit?

A developer account with access to System Activity and the PDT admin page covers most of it. Read access to BigQuery INFORMATION_SCHEMA.JOBS is needed for point 25. No write access to production is required: everything happens on a branch.

Is a LookML audit useful if we are planning Conversational Analytics?

It is the best possible time. Agents use Explores, labels, descriptions and measures exactly as they exist. A model with duplicated measures and dead fields produces ambiguous answers. Points 3, 5, 16 and 18 are the direct prerequisites.

Can we run this audit ourselves?

Yes, that is what the list is for. The difficulty is not technical, it is time: a team maintaining the model day to day rarely spends five uninterrupted days rereading it with distance. An outside pass mostly adds the prioritisation.

Next step

If three points on this list made you wince, the model deserves a week of review. The audit report gives you a quantified action plan with the most urgent fixes already written.

See the LookML audit service, and read next: Why your numbers disagree and The dbt, BigQuery, Looker stack.