---
name: lookml-content-authoring
description: Activates when creating or modifying LookML views, explores, and dashboards directly on the local filesystem
---

# LookML Content Authoring Skill for Claude Code

## On Activation

Before proceeding, append a one-line entry to `.wire/execution_log.md`:

```
| YYYY-MM-DD HH:MM | skill | lookml-authoring | activated | LookML view, explore, or model work triggered this skill |
```

If `.wire/execution_log.md` does not exist, create it with the standard header first (see `specs/utils/execution_log.md`). If no `.wire/` directory exists in the current repo, skip this step.



## Overview

This skill enables Claude Code to create and modify LookML content (views, explores, models) directly in the local filesystem. Claude Code works with LookML projects stored in git repositories, typically in a `/looker` directory structure. The user provides specifications, schema information, and requirements; Claude Code generates properly formatted, validated LookML files.

## Architecture

Claude Code operates entirely on the local filesystem:

| Operation | Approach |
|-----------|----------|
| Read existing LookML files | `cat`, `view` filesystem commands |
| Write new LookML files | `write`, `create` filesystem commands |
| Modify existing files | `edit`, `str_replace` commands |
| Understand schema | User-provided spec files, YAML configs, or documentation |
| Validate syntax | LookML linting rules applied during generation |
| Version control | Standard git operations |

**Note:** Claude Code does NOT have access to:
- Looker API or MCP server
- Live database connections
- Query execution or testing
- Real-time schema discovery

All schema information must be provided by the user via spec files, documentation, or direct instruction.

## RA Layered LookML Architecture

Rittman Analytics' Looker projects use a **layered architecture** built on LookML [refinements](https://cloud.google.com/looker/docs/lookml-refinements) to separate concerns and keep the semantic layer maintainable. Each layer has a specific role in transforming the analytics warehouse into business-ready analytical objects.

```yaml
analytics_warehouse
│
├── manifest.lkml                                       # Project manifest
└── analytics_warehouse
    │
    └── lookml
        ├── aggregate/
        │   └── agg_[entity_group].layer.lkml           # Refinements + new aggregate measures
        │
        ├── base/
        │   ├── _aggregate.layer.lkml                   # Generated measures from warehouse
        │   └── _base.layer.lkml                        # Generated dimensions from warehouse
        │
        ├── int/
        │   ├── int_explore_[entity_group_a].layer.lkml # Explores
        │   └── int_explore_[entity_group_b].layer.lkml
        │
        ├── model/
        │   └── analytics_warehouse.model.lkml          # Project model + connection
        │
        └── staging/
            └── stg_[entity_group].layer.lkml           # Refinements on dimensions
```

### Layer roles

**1. Base layer (`base/`) — generated LookML mapping directly to warehouse tables**
- `_base.layer.lkml`: dimensions and basic field definitions, generated from warehouse schema
- `_aggregate.layer.lkml`: measures generated from warehouse tables
- Field names and types match the warehouse exactly
- **No** business logic that doesn't exist in the warehouse
- **No** user-facing labelling

**2. Staging layer (`staging/`) — dimension refinements**
- One file per entity group: `stg_<entity_group>.layer.lkml`
- Uses LookML refinements (`+` prefix) to override or enhance base dimensions
- Adds user-friendly `label:` and `group_label:`
- Organises field-picker ordering using space characters (e.g. `label: "    my field label"`)
- Hides dimensions that aren't user-facing (typically `_pk`, `_fk`)
- Adds descriptions to newly derived dimensions
- Defines parameters, toggles, filtered dimensions
- **No** calculated or aggregate measures — those live in the aggregate layer

**3. Aggregate layer (`aggregate/`) — measure refinements + new aggregate measures**
- One file per entity group: `agg_<entity_group>.layer.lkml`
- Uses LookML refinements (`+` prefix) to override or enhance base aggregate measures
- Creates new aggregate measures (avg, median, ratios, year-over-year, etc.)
- Measures that exist **only** in the semantic layer (not in the warehouse) use the `_calculated` suffix
- All business KPI measures live here — never in the warehouse or base layer
- Apply `value_format_name` for consistent formatting
- Apply `sql_distinct_key` for accurate count distinct operations
- Include `drill_fields` for common exploration paths

**4. Integration layer (`int/`) — Explores and joins**
- One file per entity group: `int_explore_<entity_group>.layer.lkml`
- Uses `include:` to pull in base, staging, and aggregate refinement layers (in that order)
- View names in Explores reference base view names (e.g. `view: wh_core__user_dim`)
- Defines relationships, default fields, access filters
- Use [Aggregate Awareness](https://cloud.google.com/looker/docs/aggregate_awareness) where it improves performance
- Joins should be many-to-one for performance, using `_fk` → `_pk` relationships from the warehouse
- **Avoid** direct SQL or business logic in this layer (e.g. no joins between columns not already modelled as `_fk` → `_pk` in the warehouse)
- All Explores must have `label:` and `description:`

**5. Model layer (`model/`) — project connection**
- Single `analytics_warehouse.model.lkml`
- `include:` all `int_explore_*.layer.lkml` files
- One `connection:` defined
- `case_sensitive: no`

### File-naming conventions

| Folder | Suffix | Example |
|---|---|---|
| `base/` | `.layer.lkml` | `_base.layer.lkml`, `_aggregate.layer.lkml` |
| `staging/` | `.layer.lkml` | `stg_core.layer.lkml`, `stg_finance.layer.lkml` |
| `aggregate/` | `.layer.lkml` | `agg_core.layer.lkml`, `agg_finance.layer.lkml` |
| `int/` | `.layer.lkml` | `int_explore_core.layer.lkml` |
| `model/` | `.model.lkml` | `analytics_warehouse.model.lkml` |

The `.layer.lkml` suffix is a convention adopted across the Looker community for refinement files.

### Refinements — the core pattern

**Always use refinements (`+` prefix)** to modify base views in the staging and aggregate layers. A view should be refined **once** in the staging layer (for dimensions) and **once** in the aggregate layer (for measures) — never more. Multiple refinements of the same view in the same layer create version drift and data-quality risk.

```lkml
# In base/_base.layer.lkml — generated
view: wh_core__user_dim {
  sql_table_name: analytics.wh_core__user_dim ;;
  dimension: user_pk { ... }
  dimension: user_name { ... }
}

# In staging/stg_core.layer.lkml — refinement
view: +wh_core__user_dim {
  dimension: user_pk { hidden: yes }
  dimension: user_name {
    label: "User name"
    group_label: "User attributes"
    description: "Full user name from the back-office source."
  }
}

# In aggregate/agg_core.layer.lkml — refinement
view: +wh_core__user_dim {
  measure: user_count_calculated {
    type: count_distinct
    sql: ${user_pk} ;;
    description: "Distinct count of users."
  }
}
```

Note the `view: +wh_core__user_dim` syntax — the `+` indicates a refinement, and the view name matches the warehouse table name exactly.

### `include:` order matters

In an integration-layer `.layer.lkml` file, list `include:` directives in this order — base first, then refinements:

```lkml
include: "/analytics_warehouse/lookml/base/_base.layer.lkml"
include: "/analytics_warehouse/lookml/base/_aggregate.layer.lkml"
include: "/analytics_warehouse/lookml/staging/*"
include: "/analytics_warehouse/lookml/aggregate/*"

explore: user_explore {
  view_name: wh_core__user_dim
  label: "User analytics"
  description: "Explores user attributes and aggregates."
  # joins...
}
```

The base layer must be loaded **before** the refinement layers, otherwise the `+` views have nothing to refine.

### Quick reference — where things live

| Need to ... | Layer | File |
|---|---|---|
| Add a user-friendly label to an existing dimension | staging | `stg_<group>.layer.lkml` |
| Hide a primary or foreign key from the field picker | staging | `stg_<group>.layer.lkml` |
| Create a new aggregate measure (`count_distinct`, ratio, YoY) | aggregate | `agg_<group>.layer.lkml` |
| Add `value_format_name` to a base measure | aggregate | `agg_<group>.layer.lkml` |
| Define an Explore with joins | int | `int_explore_<group>.layer.lkml` |
| Add or change a database connection | model | `analytics_warehouse.model.lkml` |
| Modify base/auto-generated LookML | **don't** — regenerate from the warehouse | |

---

## Legacy Project Structure (non-layered)

Some older LookML projects use a flatter structure without the layered refinement pattern. For greenfield work, always prefer the layered architecture above. The structure below is documented only as a reference for working with legacy projects:

```
/looker/
├── manifest.lkml                    # Project manifest
├── models/
│   ├── analytics.model.lkml         # Model definitions
│   └── marketing.model.lkml
├── views/
│   ├── core/                        # Core/shared views
│   │   ├── dim_customer.view.lkml
│   │   └── dim_product.view.lkml
│   ├── staging/                     # Staging layer views
│   │   └── stg_orders.view.lkml
│   └── marts/                       # Business logic views
│       └── fct_orders.view.lkml
├── explores/                        # Explore definitions (optional)
│   └── orders.explore.lkml
├── dashboards/                      # LookML dashboards
│   └── executive_summary.dashboard.lkml
└── docs/                            # Documentation and specs
    ├── schema.yml                   # Schema definitions
    └── field_specs.md               # Field specifications
```

## When to Use This Skill

Activate this skill when the user requests:

- Creating new LookML views from schema specifications
- Modifying existing views or explores
- Adding dimensions, measures, or joins
- Refactoring LookML for better organization
- Converting dbt schema YAML to LookML views
- Building explores with proper join relationships
- Creating LookML dashboards
- Any task involving LookML file creation or modification

## Critical Rules

### 1. Always Reference Real Data Sources

Every view must connect to a real table or derived query. Never use placeholder or mock data.

❌ **FORBIDDEN PATTERN:**
```lkml
view: employee_pto {
  derived_table: {
    sql: 
      SELECT 'Alice' as name, 5 as days
      UNION ALL
      SELECT 'Bob' as name, 3 as days
    ;;
  }
}
```

✅ **REQUIRED PATTERN:**
```lkml
view: employee_pto {
  sql_table_name: `project_id.dataset.employee_pto` ;;
}
```

### 2. Match Exact Column Names

Column names in LookML must exactly match the source table columns (case-sensitive for most databases). Always verify column names from the provided schema.

### 3. Follow Project Conventions

Before creating new files, examine existing LookML in the project to understand:
- Whether the project uses the RA layered architecture (`base/`, `staging/`, `aggregate/`, `int/`, `model/` folders with `.layer.lkml` and `.explore.lkml` files) or the legacy flat structure
- Naming conventions (snake_case; warehouse view names like `wh_<group>__<entity>_dim`)
- Layer placement — never put aggregate measures in the staging layer or business labels in the base layer
- Refinement usage — `view: +<view_name>` for refinements, `view: <view_name>` only in base layer
- Label, group_label, and field-picker ordering conventions (space-padded labels)
- Value format conventions
- `_calculated` suffix on measures that exist only in the semantic layer

If the project uses the layered architecture (see "RA Layered LookML Architecture" above), place new content in the correct layer. If you are about to add a measure to a staging-layer file, stop — measures belong in the aggregate layer.

### 4. Validate LookML Syntax

Ensure all generated LookML:
- Has balanced braces `{}`
- Uses correct parameter names
- Includes required fields (e.g., `type` for dimensions)
- Has proper semicolons `;;` after SQL blocks

### 5. Document Your Work

Add comments explaining:
- Complex SQL logic
- Business context for calculated fields
- Source of truth for data
- Any assumptions made

## Input Sources

Claude Code relies on user-provided information for schema details:

### 1. Schema Specification Files

YAML or JSON files describing tables and columns:

```yaml
# schema.yml
tables:
  - name: employee_pto
    database: ra-development
    schema: analytics_seed
    columns:
      - name: First_name
        type: STRING
        description: Employee's first name
      - name: Last_name
        type: STRING
        description: Employee's last name
      - name: email
        type: STRING
        description: Employee email address
      - name: Start_date
        type: DATE
        description: PTO start date
      - name: End_date
        type: DATE
        description: PTO end date
      - name: Days
        type: FLOAT64
        description: Number of PTO days
      - name: Type
        type: STRING
        description: Type of PTO (vacation, sick, etc.)
```

### 2. dbt Schema Files

Convert dbt `schema.yml` to LookML:

```yaml
# dbt schema.yml
models:
  - name: stg_orders
    description: Staged orders data
    columns:
      - name: order_id
        description: Primary key
        tests:
          - unique
          - not_null
      - name: customer_id
        description: Foreign key to customers
      - name: order_date
        description: Date order was placed
      - name: total_amount
        description: Order total in USD
```

### 3. Direct User Instructions

User provides table details in natural language or structured format within the conversation.

### 4. Existing LookML Files

Examine existing views to understand patterns and relationships.

## Development Workflow

### Phase 1: Understand the Task

For every LookML request, extract:

1. **Business goal**: What metric or analysis is needed?
2. **Data source details**: Database, schema, table name, column specifications
3. **Target artifacts**: View? Explore? Model updates?
4. **Relationships**: How does this connect to other views?
5. **Project location**: Path to LookML files (typically `/looker/`)

### Phase 2: Examine Existing Project

```bash
# List project structure
find /looker -name "*.lkml" | head -20

# Read the model file to understand existing setup
cat /looker/models/analytics.model.lkml

# Study existing view patterns
head -100 /looker/views/core/dim_customer.view.lkml
```

Key things to identify:
- Connection name used in models
- Include patterns (`include: "/views/**/*.view"`)
- Existing explores and their joins
- Naming conventions and style

### Phase 3: Parse Schema Information

Extract from user-provided specs:

```python
# From schema.yml or user instructions, identify:
table_name = "employee_pto"
full_table_path = "`ra-development.analytics_seed.employee_pto`"
columns = [
    {"name": "First_name", "type": "STRING"},
    {"name": "Last_name", "type": "STRING"},
    {"name": "email", "type": "STRING"},
    {"name": "Start_date", "type": "DATE"},
    {"name": "End_date", "type": "DATE"},
    {"name": "Days", "type": "FLOAT64"},
    {"name": "Type", "type": "STRING"},
]
```

### Phase 4: Design the LookML

#### Choose Source Pattern

**Use `sql_table_name`** for direct table access:

```lkml
view: employee_pto {
  sql_table_name: `ra-development.analytics_seed.employee_pto` ;;
}
```

**Use `derived_table`** for transformations:

```lkml
view: employee_pto_summary {
  derived_table: {
    sql:
      SELECT
        email,
        SUM(Days) AS total_days
      FROM `ra-development.analytics_seed.employee_pto`
      GROUP BY 1
    ;;
  }
}
```

#### Map Data Types to LookML Types

| Source Type | LookML Type | Notes |
|-------------|-------------|-------|
| STRING, VARCHAR | `type: string` | |
| INT64, INTEGER | `type: number` | |
| FLOAT64, NUMERIC | `type: number` | Add `value_format` |
| DATE | `type: time` with `datatype: date` | Use `dimension_group` |
| TIMESTAMP, DATETIME | `type: time` with `datatype: timestamp` | Use `dimension_group` |
| BOOLEAN | `type: yesno` | |
| ARRAY | `type: string` | Use `ARRAY_TO_STRING()` |
| STRUCT | Access with dot notation | `${TABLE}.struct.field` |

### Phase 5: Create the View File

Write complete, properly formatted LookML:

```lkml
view: employee_pto {
  sql_table_name: `ra-development.analytics_seed.employee_pto` ;;

  # =============================================================================
  # PRIMARY KEY
  # =============================================================================

  dimension: pto_id {
    primary_key: yes
    type: string
    sql: CONCAT(${TABLE}.email, '-', CAST(${TABLE}.Start_date AS STRING)) ;;
    hidden: yes
    description: "Composite key: email + start date"
  }

  # =============================================================================
  # DIMENSIONS - STRING
  # =============================================================================

  dimension: first_name {
    type: string
    label: "First Name"
    sql: ${TABLE}.First_name ;;
    group_label: "Employee Details"
  }

  dimension: last_name {
    type: string
    label: "Last Name"
    sql: ${TABLE}.Last_name ;;
    group_label: "Employee Details"
  }

  dimension: employee_name {
    type: string
    label: "Employee Name"
    sql: CONCAT(${TABLE}.First_name, ' ', ${TABLE}.Last_name) ;;
    group_label: "Employee Details"
  }

  dimension: email {
    type: string
    sql: ${TABLE}.email ;;
    group_label: "Employee Details"
  }

  dimension: pto_type {
    type: string
    label: "PTO Type"
    sql: ${TABLE}.Type ;;
    description: "Category of time off: vacation, sick, personal, etc."
  }

  # =============================================================================
  # DIMENSIONS - DATE/TIME
  # =============================================================================

  dimension_group: pto_start {
    type: time
    label: "PTO Start"
    timeframes: [raw, date, week, month, quarter, year]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.Start_date ;;
  }

  dimension_group: pto_end {
    type: time
    label: "PTO End"
    timeframes: [raw, date, week, month, quarter, year]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.End_date ;;
  }

  # =============================================================================
  # DIMENSIONS - NUMERIC
  # =============================================================================

  dimension: pto_days {
    type: number
    label: "PTO Days"
    sql: ${TABLE}.Days ;;
    value_format_name: decimal_1
    description: "Number of days for this PTO request"
  }

  # =============================================================================
  # DIMENSIONS - DERIVED/CALCULATED
  # =============================================================================

  dimension: is_extended_leave {
    type: yesno
    label: "Extended Leave (5+ Days)"
    sql: ${pto_days} >= 5 ;;
    description: "Flag for PTO requests of 5 or more days"
  }

  dimension: pto_days_tier {
    type: tier
    label: "PTO Days Tier"
    tiers: [1, 3, 5, 10]
    style: integer
    sql: ${pto_days} ;;
  }

  # =============================================================================
  # MEASURES
  # =============================================================================

  measure: count {
    type: count
    label: "PTO Request Count"
    drill_fields: [detail*]
  }

  measure: total_pto_days {
    type: sum
    label: "Total PTO Days"
    sql: ${pto_days} ;;
    value_format_name: decimal_1
  }

  measure: average_pto_days {
    type: average
    label: "Average PTO Days"
    sql: ${pto_days} ;;
    value_format_name: decimal_2
  }

  measure: employee_count {
    type: count_distinct
    label: "Employee Count"
    sql: ${email} ;;
    description: "Distinct count of employees with PTO"
  }

  # =============================================================================
  # DRILL SETS
  # =============================================================================

  set: detail {
    fields: [
      employee_name,
      email,
      pto_start_date,
      pto_end_date,
      pto_days,
      pto_type
    ]
  }
}
```

### Phase 6: Update Model File

Add the view to an explore in the model:

```lkml
# In models/analytics.model.lkml

connection: "ra_dw_prod"

include: "/views/**/*.view.lkml"

# Add new explore
explore: employee_pto {
  label: "Employee PTO"
  group_label: "HR Analytics"
  description: "Employee paid time off tracking and analysis"
  
  # Join to employee dimension if available
  join: employees_dim {
    type: left_outer
    relationship: many_to_one
    sql_on: ${employee_pto.email} = ${employees_dim.email} ;;
  }
}
```

### Phase 7: Validate and Document

#### Syntax Validation Checklist

Before finalizing any LookML file, verify:

- [ ] All braces `{}` are balanced
- [ ] All SQL blocks end with `;;`
- [ ] All dimensions have `type:` specified
- [ ] All `sql:` references use `${TABLE}.column` or `${view.field}` syntax
- [ ] Primary keys are defined where appropriate
- [ ] Labels are business-friendly
- [ ] No trailing commas in lists
- [ ] Proper indentation (2 spaces standard)

#### Common Syntax Errors to Avoid

```lkml
# ❌ WRONG: Missing type
dimension: name {
  sql: ${TABLE}.name ;;
}

# ✅ CORRECT
dimension: name {
  type: string
  sql: ${TABLE}.name ;;
}

# ❌ WRONG: Missing semicolons after SQL
dimension: name {
  type: string
  sql: ${TABLE}.name
}

# ✅ CORRECT
dimension: name {
  type: string
  sql: ${TABLE}.name ;;
}

# ❌ WRONG: Unbalanced braces
view: test {
  dimension: id {
    type: number
    sql: ${TABLE}.id ;;
}

# ✅ CORRECT
view: test {
  dimension: id {
    type: number
    sql: ${TABLE}.id ;;
  }
}
```

### Phase 8: Provide Handover Summary

After creating LookML files, provide a summary:

```markdown
## LookML Changes Summary

### Files Created/Modified

1. **Created**: `/looker/views/hr/employee_pto.view.lkml`
   - Source table: `ra-development.analytics_seed.employee_pto`
   - Dimensions: 8 (including composite primary key)
   - Measures: 4
   - Drill set defined for detail exploration

2. **Modified**: `/looker/models/analytics.model.lkml`
   - Added `employee_pto` explore
   - Configured join to `employees_dim` view

### Next Steps for User

1. **Review the generated LookML** for accuracy against your schema
2. **Commit changes to git**:
   ```bash
   git add looker/
   git commit -m "feat: Add employee PTO view and explore"
   git push
   ```
3. **Sync in Looker IDE** - Pull changes and validate
4. **Run LookML Validator** - Check for any errors
5. **Test queries** - Run sample queries in the explore to verify data
```

## Common Patterns

### Pattern 1: Dimension Table (Slowly Changing)

```lkml
view: dim_customer {
  sql_table_name: `project.dataset.dim_customer` ;;

  dimension: customer_id {
    primary_key: yes
    type: number
    sql: ${TABLE}.customer_id ;;
    hidden: yes
  }

  dimension: customer_name {
    type: string
    sql: ${TABLE}.customer_name ;;
  }

  dimension: email {
    type: string
    sql: ${TABLE}.email ;;
  }

  dimension: customer_segment {
    type: string
    sql: ${TABLE}.segment ;;
  }

  dimension: is_active {
    type: yesno
    sql: ${TABLE}.is_active ;;
  }

  dimension_group: created {
    type: time
    timeframes: [date, month, year]
    datatype: date
    sql: ${TABLE}.created_date ;;
  }

  measure: count {
    type: count
  }

  measure: active_customer_count {
    type: count
    filters: [is_active: "yes"]
  }
}
```

### Pattern 2: Fact Table (Transactional)

```lkml
view: fct_orders {
  sql_table_name: `project.dataset.fct_orders` ;;

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

  dimension: customer_id {
    type: number
    sql: ${TABLE}.customer_id ;;
    hidden: yes
  }

  dimension: product_id {
    type: number
    sql: ${TABLE}.product_id ;;
    hidden: yes
  }

  dimension_group: order {
    type: time
    timeframes: [raw, time, date, week, month, quarter, year]
    datatype: timestamp
    sql: ${TABLE}.order_timestamp ;;
  }

  dimension: order_amount {
    type: number
    sql: ${TABLE}.order_amount ;;
    value_format_name: usd
    hidden: yes
  }

  dimension: quantity {
    type: number
    sql: ${TABLE}.quantity ;;
    hidden: yes
  }

  # Measures
  measure: count {
    type: count
    drill_fields: [order_id, order_date, order_amount]
  }

  measure: total_revenue {
    type: sum
    sql: ${order_amount} ;;
    value_format_name: usd
  }

  measure: average_order_value {
    type: average
    sql: ${order_amount} ;;
    value_format_name: usd
  }

  measure: total_quantity {
    type: sum
    sql: ${quantity} ;;
  }

  measure: order_count {
    type: count_distinct
    sql: ${order_id} ;;
  }
}
```

### Pattern 3: Aggregated Derived Table (PDT)

```lkml
view: customer_order_summary {
  derived_table: {
    sql:
      SELECT
        customer_id,
        COUNT(DISTINCT order_id) AS lifetime_orders,
        SUM(order_amount) AS lifetime_value,
        MIN(order_timestamp) AS first_order_date,
        MAX(order_timestamp) AS last_order_date,
        DATE_DIFF(CURRENT_DATE(), DATE(MAX(order_timestamp)), DAY) AS days_since_last_order
      FROM `project.dataset.fct_orders`
      GROUP BY 1
    ;;

    # PDT configuration
    datagroup_trigger: daily_refresh
    indexes: ["customer_id"]
  }

  dimension: customer_id {
    primary_key: yes
    type: number
    sql: ${TABLE}.customer_id ;;
    hidden: yes
  }

  dimension: lifetime_orders {
    type: number
    sql: ${TABLE}.lifetime_orders ;;
  }

  dimension: lifetime_value {
    type: number
    sql: ${TABLE}.lifetime_value ;;
    value_format_name: usd
  }

  dimension: lifetime_value_tier {
    type: tier
    tiers: [0, 100, 500, 1000, 5000]
    style: integer
    sql: ${lifetime_value} ;;
  }

  dimension_group: first_order {
    type: time
    timeframes: [date, month, year]
    datatype: timestamp
    sql: ${TABLE}.first_order_date ;;
  }

  dimension_group: last_order {
    type: time
    timeframes: [date, month, year]
    datatype: timestamp
    sql: ${TABLE}.last_order_date ;;
  }

  dimension: days_since_last_order {
    type: number
    sql: ${TABLE}.days_since_last_order ;;
  }

  dimension: is_repeat_customer {
    type: yesno
    sql: ${lifetime_orders} > 1 ;;
  }

  measure: average_lifetime_value {
    type: average
    sql: ${lifetime_value} ;;
    value_format_name: usd
  }

  measure: average_lifetime_orders {
    type: average
    sql: ${lifetime_orders} ;;
    value_format_name: decimal_1
  }
}
```

### Pattern 4: Explore with Multiple Joins

```lkml
explore: orders {
  label: "Orders Analysis"
  description: "Analyze orders with customer, product, and geographic context"

  # Base view
  from: fct_orders

  # Customer dimension
  join: dim_customer {
    type: left_outer
    relationship: many_to_one
    sql_on: ${fct_orders.customer_id} = ${dim_customer.customer_id} ;;
  }

  # Product dimension
  join: dim_product {
    type: left_outer
    relationship: many_to_one
    sql_on: ${fct_orders.product_id} = ${dim_product.product_id} ;;
  }

  # Customer lifetime metrics
  join: customer_order_summary {
    type: left_outer
    relationship: one_to_one
    sql_on: ${fct_orders.customer_id} = ${customer_order_summary.customer_id} ;;
  }

  # Always filter to completed orders (optional)
  always_filter: {
    filters: [fct_orders.order_status: "completed"]
  }
}
```

### Pattern 5: Native Derived Table with Parameters

```lkml
view: dynamic_date_comparison {
  derived_table: {
    explore_source: orders {
      column: order_date { field: fct_orders.order_date }
      column: total_revenue { field: fct_orders.total_revenue }
      column: order_count { field: fct_orders.order_count }
      
      bind_filters: {
        from_field: dynamic_date_comparison.date_filter
        to_field: fct_orders.order_date
      }
    }
  }

  filter: date_filter {
    type: date
  }

  dimension: order_date {
    type: date
    sql: ${TABLE}.order_date ;;
  }

  measure: total_revenue {
    type: sum
    sql: ${TABLE}.total_revenue ;;
    value_format_name: usd
  }

  measure: order_count {
    type: sum
    sql: ${TABLE}.order_count ;;
  }
}
```

## BigQuery-Specific Patterns

### Handling Nested and Repeated Fields

```lkml
view: events {
  sql_table_name: `project.dataset.events` ;;

  # Unnest repeated field
  dimension: event_param_key {
    type: string
    sql: ep.key ;;
  }

  dimension: event_param_value {
    type: string
    sql: ep.value.string_value ;;
  }
}

# In the explore, use UNNEST
explore: events {
  join: event_params {
    type: left_outer
    relationship: one_to_many
    sql: LEFT JOIN UNNEST(${events.event_params}) AS ep ;;
  }
}
```

### Partitioned Table Optimization

```lkml
view: partitioned_events {
  sql_table_name: `project.dataset.events` ;;

  # Always include partition filter for performance
  dimension_group: event {
    type: time
    timeframes: [raw, date, week, month]
    datatype: timestamp
    sql: ${TABLE}._PARTITIONTIME ;;
  }
}

explore: partitioned_events {
  # Require partition filter
  always_filter: {
    filters: [partitioned_events.event_date: "last 30 days"]
  }
}
```

### Working with JSON Fields

```lkml
dimension: metadata_source {
  type: string
  sql: JSON_EXTRACT_SCALAR(${TABLE}.metadata, '$.source') ;;
}

dimension: metadata_version {
  type: number
  sql: CAST(JSON_EXTRACT_SCALAR(${TABLE}.metadata, '$.version') AS INT64) ;;
}
```

## LookML Dashboard Template

```lkml
- dashboard: executive_summary
  title: "Executive Summary"
  layout: newspaper
  preferred_viewer: dashboards-next
  description: "Key business metrics overview"

  filters:
    - name: date_range
      title: "Date Range"
      type: date_filter
      default_value: "last 30 days"
      allow_multiple_values: false

  elements:
    - title: "Total Revenue"
      name: total_revenue_tile
      model: analytics
      explore: orders
      type: single_value
      fields: [fct_orders.total_revenue]
      listen:
        date_range: fct_orders.order_date
      row: 0
      col: 0
      width: 6
      height: 4

    - title: "Revenue Over Time"
      name: revenue_trend
      model: analytics
      explore: orders
      type: looker_line
      fields: [fct_orders.order_date, fct_orders.total_revenue]
      sorts: [fct_orders.order_date]
      listen:
        date_range: fct_orders.order_date
      row: 4
      col: 0
      width: 12
      height: 8

    - title: "Revenue by Segment"
      name: revenue_by_segment
      model: analytics
      explore: orders
      type: looker_pie
      fields: [dim_customer.customer_segment, fct_orders.total_revenue]
      sorts: [fct_orders.total_revenue desc]
      listen:
        date_range: fct_orders.order_date
      row: 4
      col: 12
      width: 12
      height: 8
```

## Quality Checklist

Before finalizing any LookML work, verify:

### Syntax
- [ ] All braces `{}` are balanced
- [ ] All SQL blocks end with `;;`
- [ ] Proper indentation (2 spaces)
- [ ] No trailing commas

### Dimensions
- [ ] Every dimension has `type:` specified
- [ ] Primary keys defined with `primary_key: yes`
- [ ] Foreign keys marked `hidden: yes`
- [ ] Labels are business-friendly
- [ ] Group labels organize related fields

### Measures
- [ ] Appropriate measure types (sum, count, average, etc.)
- [ ] Value formats applied (usd, decimal_2, percent_1)
- [ ] Drill fields defined for exploration

### Dates
- [ ] Using `dimension_group` with appropriate timeframes
- [ ] Correct `datatype:` (date vs timestamp)
- [ ] `convert_tz: no` for date-only fields

### Documentation
- [ ] View has description
- [ ] Complex fields have descriptions
- [ ] Comments explain business logic

### Relationships
- [ ] Joins have explicit `relationship:` defined
- [ ] Join types are appropriate (left_outer, inner)
- [ ] SQL ON conditions reference correct fields

## Phase 9: Validation - MANDATORY

**This phase is REQUIRED before marking any LookML work as complete.**

### 9.1 Validate Table and Column References

After generating LookML, you MUST cross-reference all SQL table and column names against the source schema file (e.g., `target_warehouse_ddl.sql`, `schema.yml`, or other provided schema documentation).

#### Validation Process

```bash
# 1. Extract all sql_table_name references from generated LookML
grep -h "sql_table_name:" /looker/views/**/*.view.lkml

# 2. Extract all ${TABLE}.column references
grep -oE '\$\{TABLE\}\.[a-zA-Z_][a-zA-Z0-9_]*' /looker/views/new_view.view.lkml | sort -u

# 3. Compare against the DDL or schema file
cat target_warehouse_ddl.sql
```

#### Checklist for Each View

For every view created or modified, verify:

- [ ] **Table name exists**: The `sql_table_name` or `FROM` clause references a table that exists in the DDL/schema
- [ ] **Database/schema path is correct**: Full path like `project.dataset.table` matches the actual structure
- [ ] **Every column exists**: Each `${TABLE}.column_name` reference matches an actual column in the source table
- [ ] **Column names are case-accurate**: BigQuery is case-sensitive for column names - verify exact casing
- [ ] **Data types are compatible**: Column types in DDL match the LookML dimension types used

#### Example Validation

Given this DDL:
```sql
-- target_warehouse_ddl.sql
CREATE TABLE `ra-development.analytics_seed.employee_pto` (
  First_name STRING,
  Last_name STRING,
  email STRING,
  Start_date DATE,
  End_date DATE,
  Days FLOAT64,
  Type STRING
);
```

Validate the LookML references:

| LookML Reference | DDL Column | Status |
|-----------------|------------|--------|
| `${TABLE}.First_name` | `First_name STRING` | ✅ Match |
| `${TABLE}.Last_name` | `Last_name STRING` | ✅ Match |
| `${TABLE}.email` | `email STRING` | ✅ Match |
| `${TABLE}.Start_date` | `Start_date DATE` | ✅ Match |
| `${TABLE}.first_name` | - | ❌ Case mismatch! Should be `First_name` |
| `${TABLE}.pto_type` | - | ❌ Column doesn't exist! Should be `Type` |

#### Fixing Mismatches

If validation reveals mismatches:

1. **Update the LookML** to use exact column names from the DDL
2. **Do not assume** column names - always verify against source
3. **Document any ambiguity** if DDL is unclear or incomplete

### 9.2 Validate preferred_slug Parameters

If any view, explore, or dashboard uses `preferred_slug`, it MUST comply with these rules:

#### preferred_slug Syntax Rules

| Rule | Requirement |
|------|-------------|
| **Maximum length** | 255 characters |
| **Allowed characters** | Letters (A-Z, a-z), numbers (0-9), dashes (`-`), underscores (`_`) |
| **NOT allowed** | Spaces, special characters, unicode, dots, slashes |

#### Valid Examples

```lkml
# ✅ VALID preferred_slug values
explore: orders {
  preferred_slug: "orders-analysis"
}

view: customer_metrics {
  preferred_slug: "customer_metrics_v2"
}

dashboard: executive_summary {
  preferred_slug: "exec-summary-2024"
}

explore: revenue {
  preferred_slug: "revenue_by_region_q4_2024"
}
```

#### Invalid Examples

```lkml
# ❌ INVALID - contains spaces
preferred_slug: "orders analysis"

# ❌ INVALID - contains dots
preferred_slug: "orders.analysis"

# ❌ INVALID - contains special characters
preferred_slug: "orders@analysis!"

# ❌ INVALID - exceeds 255 characters
preferred_slug: "this_is_a_very_long_slug_that_goes_on_and_on_and_exceeds_the_maximum_allowed_length_of_two_hundred_and_fifty_five_characters_which_is_the_limit_set_by_looker_for_preferred_slug_parameters_so_this_will_fail_validation_when_you_try_to_deploy_it"
```

#### Validation Regex

Use this pattern to validate preferred_slug values:
```regex
^[A-Za-z0-9_-]{1,255}$
```

#### Automated Check

```bash
# Extract all preferred_slug values and validate
grep -h "preferred_slug:" /looker/**/*.lkml | while read line; do
  slug=$(echo "$line" | sed 's/.*preferred_slug: *"\([^"]*\)".*/\1/')
  
  # Check length
  if [ ${#slug} -gt 255 ]; then
    echo "ERROR: preferred_slug exceeds 255 chars: $slug"
  fi
  
  # Check characters
  if ! echo "$slug" | grep -qE '^[A-Za-z0-9_-]+$'; then
    echo "ERROR: preferred_slug contains invalid characters: $slug"
  fi
done
```

### 9.3 Final Validation Summary

Before completing any LookML task, provide a validation summary:

```markdown
## Validation Summary

### Table/Column Reference Check
- **Schema source**: `target_warehouse_ddl.sql`
- **Tables validated**: 3
- **Columns validated**: 24
- **Status**: ✅ All references valid

| View | Table | Columns Checked | Status |
|------|-------|-----------------|--------|
| employee_pto | `ra-development.analytics_seed.employee_pto` | 7 | ✅ Valid |
| employee_summary | derived_table | 4 | ✅ Valid |

### preferred_slug Validation
- **Slugs found**: 2
- **Status**: ✅ All valid

| Location | Slug | Length | Characters | Status |
|----------|------|--------|------------|--------|
| explore: employee_pto | `employee-pto-analysis` | 21 | ✅ | ✅ Valid |
| dashboard: hr_overview | `hr-overview-2024` | 16 | ✅ | ✅ Valid |

### Issues Found
- None

### Recommendations
- All LookML is ready for commit and Looker validation
```

---

## Troubleshooting Guide

### Common Issues

| Error Pattern | Likely Cause | Solution |
|--------------|--------------|----------|
| "Unknown field" | Column name mismatch | Verify exact column name from schema |
| "Circular reference" | Field references itself | Check dimension SQL references |
| "Missing }" | Unbalanced braces | Count and match all `{` and `}` |
| "Invalid SQL" | Missing `;;` | Add `;;` after SQL blocks |
| "Duplicate field" | Same name in view | Rename or remove duplicate |

### Validation Commands

After creating files, user should run in Looker IDE:
1. **Content Validator**: Checks LookML syntax
2. **Explore queries**: Test with sample queries
3. **SQL Runner**: Verify generated SQL

## Summary

This skill enables Claude Code to:

1. **Parse schema specifications** from YAML, JSON, DDL files, or natural language
2. **Generate valid LookML** following best practices and project conventions
3. **Create complete views** with dimensions, measures, and drill fields
4. **Build explores** with proper joins and relationships
5. **Maintain consistency** with existing project patterns
6. **Validate all references** against source DDL/schema files before completion
7. **Ensure preferred_slug compliance** with Looker's syntax requirements
8. **Provide clear handover** with documentation, validation summary, and next steps

### Critical Workflow Reminder

**NEVER mark LookML work as complete without:**

1. ✅ Cross-checking ALL `sql_table_name` references against the DDL/schema
2. ✅ Verifying EVERY `${TABLE}.column` reference exists with correct casing
3. ✅ Validating any `preferred_slug` values meet syntax rules (alphanumeric, dashes, underscores only; max 255 chars)
4. ✅ Providing a validation summary table in the handover

Always examine existing project files first, follow established conventions, validate against source schema, and provide comprehensive summaries of changes made.