---
name: lookml-best-practices
description: Use when writing, reviewing, or modifying LookML code in Looker projects. Enforces production-quality patterns for project structure, refinements, derived tables, descriptions, and naming conventions.
license: MIT
metadata:
    author: labs4change
    version: "1.0.0"
    homepage: https://labs4change.com
    source: https://github.com/labs4change/lookml-best-practices
---

# LookML Best Practices

## Overview

Enforces production-quality LookML patterns when writing or reviewing Looker code. Based on experience training 16,000+ analytics engineers and auditing hundreds of Looker instances.

## When to Use

- Writing new LookML views, explores, or models
- Reviewing existing LookML for quality
- Restructuring a Looker project
- Adding business logic to auto-generated views
- Creating derived tables (NDTs, PDTs)
- Setting up caching with datagroups

## Core Rules

### 1. Project Structure

Always organize with clear separation of concerns:

```
project/
├── views/
│   ├── _autogen/              # Auto-generated views — NEVER modify
│   ├── _refined/              # Refinements with business logic
│   └── derived_tables/        # NDTs, SQL DTs, PDTs
├── explores/                  # Explore definitions (separate from views)
├── models/
├── tests/                     # Data tests
└── dashboards/                # LookML dashboards (optional)
```

- One view per file. File name must match view name.
- Auto-generated views in `_autogen/` are read-only.
- All business logic lives in refinement files in `_refined/`.
- Explores live in their own folder, not inside view files.

### 2. Auto-Generated Views

Only use `${TABLE}.column_name` syntax. No labels, descriptions, measures, or business logic:

```lookml
view: orders {
  sql_table_name: `project.dataset.orders` ;;

  dimension: order_id {
    type: number
    sql: ${TABLE}.order_id ;;
  }
}
```

### 3. Refinements

Use the `+` prefix to add business logic without touching originals:

```lookml
include: "/views/_autogen/orders.view.lkml"

view: +orders {
  dimension: order_id {
    primary_key: yes
    description: "Unique identifier for each order."
  }

  measure: total_revenue {
    type: sum
    sql: ${amount} ;;
    description: "Sum of all order amounts in USD."
    value_format_name: usd
  }
}
```

- Always use refinements (not extends) for business logic on auto-generated views.
- Use extends only for reusing logic across genuinely different views.

### 4. Substitution Operator

| Context | Syntax | Example |
|---|---|---|
| Database column (autogen only) | `${TABLE}.column` | `sql: ${TABLE}.order_id ;;` |
| Same-view field reference | `${field_name}` | `sql: ${amount} * ${quantity} ;;` |
| Cross-view field reference | `${view.field}` | `sql_on: ${orders.user_id} = ${users.id} ;;` |
| Table reference in SQL DTs | `${view.SQL_TABLE_NAME}` | `FROM ${orders.SQL_TABLE_NAME}` |

NEVER use `${TABLE}.column` outside auto-generated views. ALWAYS use `${field_name}` within the same view.

### 5. Descriptions

Every field, view, and explore must have a description answering:
1. **What is this?** — Business meaning
2. **What are the possible values?** — Enums, nulls, edge cases
3. **How should I use this?** — Guidance for the user

```lookml
dimension: is_first_purchase {
  type: yesno
  description: "Yes if this is the customer's earliest order by created date.
    Useful for new customer acquisition analysis.
    Returns No for customers whose only order was cancelled."
}
```

Descriptions also feed Looker's Gemini AI for natural language querying.

### 6. Derived Tables

- Prefer Native Derived Tables (NDTs) over SQL-based for maintainability.
- Always define a primary key in derived table views.
- If source explore has `always_filter`, explicitly set or clear it.
- Use `bind_filters` to pass user input into NDT WHERE clauses.
- Use `datagroup_trigger` (not `persist_for`) for PDTs.
- For complex transformations — build in dbt instead.

```lookml
view: customer_summary {
  derived_table: {
    explore_source: orders {
      column: user_id { field: orders.user_id }
      column: lifetime_revenue { field: orders.total_revenue }
      # Explicitly clear inherited filter
      filters: [orders.created_date: ""]
    }
    datagroup_trigger: daily_etl_datagroup
  }

  dimension: user_id {
    primary_key: yes
    type: number
  }
}
```

### 7. Naming Conventions

| Object | Convention | Example |
|---|---|---|
| View files | snake_case, match view name | `orders.view.lkml` |
| Refined view files | append `_r` | `orders_r.view.lkml` |
| Dimensions | snake_case, business-friendly | `customer_name` |
| Measures | verb + noun | `total_revenue`, `average_order_value` |
| Yesno dimensions | prefix `is_` or `has_` | `is_first_purchase` |
| Dimension groups | no "date" or "time" suffix | `created`, `shipped` |
| Primary keys | entity + `_id` | `order_id`, `user_id` |
| Constants | UPPER_SNAKE_CASE | `DATASET_NAME` |

### 8. Primary Keys & Joins

- Every view must have a primary key. No exceptions.
- Always specify `relationship` on every join.
- Hide composite/technical primary keys.

```lookml
explore: orders {
  join: users {
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.user_id} = ${users.id} ;;
  }
}
```

### 9. Caching

```lookml
datagroup: daily_etl {
  max_cache_age: "24 hours"
  sql_trigger: SELECT MAX(updated_at) FROM orders ;;
}

explore: orders {
  persist_with: daily_etl
}
```

Always use datagroups over `persist_for`.

### 10. Data Tests

```lookml
test: pk_not_null {
  explore_source: orders {
    column: order_id { field: orders.order_id }
  }
  assert: {
    expression: ${order_id} != null ;;
  }
}
```

Run tests before every deploy to production.

## Quick Checklist (When Generating LookML)

1. Ask which folder the file belongs in (_autogen, _refined, derived_tables, explores)
2. Use refinement syntax (`view: +name`) when modifying existing views
3. Add `primary_key: yes` to every view
4. Add `description` to every field, view, and explore
5. Use `${field_name}` not `${TABLE}.column` (unless autogen view)
6. Specify `relationship` on every join
7. Use `datagroup_trigger` for PDTs, never `persist_for`
8. Name dimension groups without "date" or "time" suffixes
9. Prefix yesno fields with `is_` or `has_`
10. Add `drill_fields` to measures