Looker y LookML en 2026: Guía Completa de Business Intelligence y Preguntas de Entrevista

# Ejemplo de conexión en looker.ini
[database]
host: your-project.region.bigquery.googleapis.com
port: 443
ssl: true
database: your_dataset
schema: analytics

# model: ecommerce.model.lkml
connection: "bigquery_production"

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

explore: orders {
  label: "Sales Analysis"
  description: "Explore orders with customer and product details"
  
  join: customers {
    type: left_outer
    sql_on: ${orders.customer_id} = ${customers.id} ;;
    relationship: many_to_one
  }
  
  join: products {
    type: left_outer
    sql_on: ${order_items.product_id} = ${products.id} ;;
    relationship: many_to_one
  }
}

# view: orders.view.lkml
view: orders {
  sql_table_name: `analytics.orders` ;;

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

  dimension_group: created {
    type: time
    timeframes: [raw, date, week, month, quarter, year]
    sql: ${TABLE}.created_at ;;
  }

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

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

  measure: count {
    type: count
    drill_fields: [id, created_date, status, total_amount]
  }

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

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

view: daily_sales_summary {
  derived_table: {
    sql:
      SELECT
        DATE(created_at) as sale_date,
        COUNT(DISTINCT order_id) as order_count,
        SUM(total_amount) as daily_revenue,
        COUNT(DISTINCT customer_id) as unique_customers
      FROM ${orders.SQL_TABLE_NAME}
      WHERE status = 'completed'
      GROUP BY 1
    ;;
    
    datagroup_trigger: daily_refresh
    indexes: ["sale_date"]
  }

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

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

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

view: orders_with_params {
  parameter: date_granularity {
    type: unquoted
    allowed_values: {
      label: "Day"
      value: "day"
    }
    allowed_values: {
      label: "Week"
      value: "week"
    }
    allowed_values: {
      label: "Month"
      value: "month"
    }
  }

  dimension: dynamic_date {
    type: string
    sql:
      {% if date_granularity._parameter_value == 'day' %}
        DATE(${created_raw})
      {% elsif date_granularity._parameter_value == 'week' %}
        DATE_TRUNC(${created_raw}, WEEK)
      {% else %}
        DATE_TRUNC(${created_raw}, MONTH)
      {% endif %}
    ;;
  }
}

# 1. Usar PDTs para pre-agregar datos
view: monthly_metrics {
  derived_table: {
    sql:
      SELECT
        DATE_TRUNC(created_at, MONTH) as month,
        category,
        SUM(revenue) as total_revenue
      FROM orders
      GROUP BY 1, 2
    ;;
    datagroup_trigger: monthly_refresh
    indexes: ["month", "category"]
  }
}

# 2. Implementar always_filter para limitar los scans
explore: large_transactions {
  always_filter: {
    filters: [created_date: "last 90 days"]
  }
}

# 3. Usar sql_always_where para restricciones permanentes
explore: active_customers {
  sql_always_where: ${customers.status} = 'active' ;;
}

explore: orders {
  # LEFT OUTER: mantiene todos los registros de orders
  join: customers {
    type: left_outer
    sql_on: ${orders.customer_id} = ${customers.id} ;;
    relationship: many_to_one
  }
  
  # INNER: únicamente las coincidencias
  join: payments {
    type: inner
    sql_on: ${orders.id} = ${payments.order_id} ;;
    relationship: one_to_many
  }
  
  # FULL OUTER: todos los registros de ambas tablas
  join: returns {
    type: full_outer
    sql_on: ${orders.id} = ${returns.order_id} ;;
    relationship: one_to_one
  }
  
  # CROSS: producto cartesiano (raramente utilizado)
  join: date_spine {
    type: cross
    relationship: many_to_many
  }
}

# Estructura recomendada para un proyecto empresarial
# /models
#   └── ecommerce.model.lkml
# /views
#   └── /core
#       ├── orders.view.lkml
#       ├── customers.view.lkml
#       └── products.view.lkml
#   └── /derived
#       └── daily_summary.view.lkml
# /explores
#   └── sales.explore.lkml
# /dashboards
#   └── executive_summary.dashboard.lookml

# Uso de extends para reutilización
view: base_order_metrics {
  extension: required
  
  measure: total_revenue {
    type: sum
    sql: ${amount} ;;
  }
  
  measure: order_count {
    type: count_distinct
    sql: ${id} ;;
  }
}

view: orders {
  extends: [base_order_metrics]
  sql_table_name: analytics.orders ;;
}

# Tests de validación de datos
test: orders_have_positive_amounts {
  explore_source: orders {
    column: order_count { field: orders.count }
    filters: [orders.total_amount: "<0"]
  }
  assert: order_count {
    expression: ${orders.count} = 0 ;;
  }
}

test: customer_ids_are_unique {
  explore_source: customers {
    column: duplicate_count {
      field: customers.count
    }
    column: id_count {
      field: customers.id_count
    }
  }
  assert: no_duplicates {
    expression: ${duplicate_count} = ${id_count} ;;
  }
}

# Uso de BigQuery ML directamente en LookML
view: customer_predictions {
  derived_table: {
    sql:
      SELECT
        customer_id,
        predicted_ltv,
        predicted_churn_probability
      FROM ML.PREDICT(
        MODEL `project.dataset.customer_ltv_model`,
        (SELECT * FROM ${customers.SQL_TABLE_NAME})
      )
    ;;
    datagroup_trigger: weekly_predictions
  }
}

# Conexión optimizada para BigQuery
# Looker utiliza sesiones BigQuery para reducir costos
connection: "bigquery_optimized" {
  host: "bigquery.googleapis.com"
  maximum_billing_gigabytes: 100
  query_timeout: 3600
}

view: cohort_analysis {
  derived_table: {
    sql:
      WITH first_purchase AS (
        SELECT
          customer_id,
          DATE_TRUNC(MIN(created_at), MONTH) as cohort_month
        FROM ${orders.SQL_TABLE_NAME}
        GROUP BY 1
      )
      SELECT
        fp.cohort_month,
        DATE_DIFF(
          DATE_TRUNC(o.created_at, MONTH),
          fp.cohort_month,
          MONTH
        ) as months_since_first,
        COUNT(DISTINCT o.customer_id) as customers,
        SUM(o.total_amount) as revenue
      FROM ${orders.SQL_TABLE_NAME} o
      JOIN first_purchase fp ON o.customer_id = fp.customer_id
      GROUP BY 1, 2
    ;;
    datagroup_trigger: daily_refresh
  }

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

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

  measure: customer_count {
    type: sum
    sql: ${TABLE}.customers ;;
  }

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