/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

view: employee_pto {
  derived_table: {
    sql: 
      SELECT 'Alice' as name, 5 as days
      UNION ALL
      SELECT 'Bob' as name, 3 as days
    ;;
  }
}

view: employee_pto {
  sql_table_name: `project_id.dataset.employee_pto` ;;
}

# 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.)

# 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

# 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

# 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"},
]

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

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

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
    ]
  }
}

# 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} ;;
  }
}

# ❌ 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 ;;
  }
}

## 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

## 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"]
  }
}

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} ;;
  }
}

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
  }
}

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"]
  }
}

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 ;;
  }
}

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 ;;
  }
}

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"]
  }
}

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) ;;
}

- 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

# 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

-- 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
);

# ✅ 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 - 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"

^[A-Za-z0-9_-]{1,255}$

# 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

## 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

mcp__looker-mcp__dev_mode(devMode: true)

mcp__looker-mcp__create_project_file(
  project_id: "analytics",
  file_path: "views/my_view.view.lkml",  # .lkml NOT .lookml!
  file_content: "<file contents>"
)

mcp__looker-mcp__update_project_file(
  project_id: "analytics",
  file_path: "views/my_view.view.lkml",
  file_content: "<updated contents>"
)

mcp__looker-mcp__query(
  model: "model_name",
  explore: "explore_name",
  fields: ["view.dimension", "view.measure"],
  limit: 1
)

mcp__looker-mcp__query(
  model: "model_name",
  explore: "explore_name",
  fields: [...element fields...],
  filters: {...element filters...},
  limit: 10
)

mcp__looker-mcp__pulse(action: "check_dashboard_errors")

/deploy-lookml /lookml/dashboards/financial.dashboard.lookml analytics

/deploy-lookml --all --spec /docs/design/dashboard-specification.md analytics

explore: monthly_spending {
  from: agg_monthly_spending  # View name
  label: "Monthly Spending"
}