What is LookML?
LookML (Look Markup Language) is a data modeling language created by Looker that allows you to describe your data's dimensions, aggregates, calculations, and data relationships in a git-versioned, declarative language. It serves as an abstraction layer between your raw data and your business users, enabling them to explore data through a business-oriented lens.
Key Concepts in LookML
1. Views
Views typically correspond to database tables or derived tables. They define:
- Dimensions (fields you can group by)
- Measures (aggregate calculations)
- Parameters
- Filters
2. Explores
Explores define how different views can be joined together and what fields are available for querying.
3. Models
Models are collections of explores that define which explores should be made available to users.
Basic LookML Components
1. Dimensions
Dimensions are attributes that can be used to group or filter data.
dimension: user_id {
type: number
primary_key: yes
sql: ${TABLE}.user_id ;;
}
dimension: email {
type: string
sql: ${TABLE}.email ;;
}
dimension: signup_date {
type: date
sql: ${TABLE}.created_at ;;
}
2. Dimension Groups
Dimension groups create multiple time-based dimensions from a single timestamp.
dimension_group: created {
type: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.created_at ;;
}
3. Measures
Measures are aggregate calculations across multiple rows.
measure: total_revenue {
type: sum
sql: ${revenue_amount} ;;
value_format_name: usd
}
measure: average_order_value {
type: average
sql: ${order_amount} ;;
value_format_name: usd_0
}
measure: user_count {
type: count_distinct
sql: ${user_id} ;;
}
Practical Example: E-commerce Data Model
Let's create a simple e-commerce data model with orders and users.
1. Users View
view: users {
sql_table_name: public.users ;;
dimension: user_id {
primary_key: yes
type: number
sql: ${TABLE}.id ;;
}
dimension: email {
type: string
sql: ${TABLE}.email ;;
}
dimension: first_name {
type: string
sql: ${TABLE}.first_name ;;
}
dimension: last_name {
type: string
sql: ${TABLE}.last_name ;;
}
dimension: full_name {
type: string
sql: concat(${first_name}, ' ', ${last_name}) ;;
}
dimension_group: created {
type: time
timeframes: [date, week, month, year]
sql: ${TABLE}.created_at ;;
}
measure: count {
type: count
drill_fields: [user_id, full_name, email]
}
}
2. Orders View
view: orders {
sql_table_name: public.orders ;;
dimension: order_id {
primary_key: yes
type: number
sql: ${TABLE}.id ;;
}
dimension: user_id {
type: number
sql: ${TABLE}.user_id ;;
}
dimension_group: ordered {
type: time
timeframes: [date, week, month, year]
sql: ${TABLE}.created_at ;;
}
dimension: status {
type: string
sql: ${TABLE}.status ;;
}
dimension: is_completed {
type: yesno
sql: ${status} = 'completed' ;;
}
dimension: total_amount {
type: number
sql: ${TABLE}.total_amount ;;
value_format_name: usd
}
measure: count {
type: count
drill_fields: [order_id, user_id, ordered_date, total_amount]
}
measure: total_revenue {
type: sum
sql: ${total_amount} ;;
value_format_name: usd
filters: {
field: is_completed
value: "yes"
}
}
measure: average_order_value {
type: average
sql: ${total_amount} ;;
value_format_name: usd
filters: {
field: is_completed
value: "yes"
}
}
}
3. Explore Definition
explore: orders {
join: users {
type: left_outer
sql_on: ${orders.user_id} = ${users.user_id} ;;
relationship: many_to_one
}
}
4. Model Definition
connection: "my_database"
include: "*.view.lkml" # include all views
include: "*.dashboard.lookml" # include all dashboards
model: ecommerce {
label: "E-commerce Analytics"
persist_for: "24 hours"
explore: orders {
label: "Orders Analysis"
description: "Analyze order data with user information"
}
}
Advanced Features
1. Derived Tables
Create tables on the fly using SQL:
view: user_order_stats {
derived_table: {
sql: SELECT
user_id,
COUNT(*) as order_count,
SUM(total_amount) as lifetime_revenue
FROM orders
GROUP BY user_id ;;
}
dimension: user_id {
primary_key: yes
type: number
}
dimension: order_count {
type: number
}
dimension: lifetime_revenue {
type: number
value_format_name: usd
}
}
2. Liquid Variables
Use Liquid templating for dynamic SQL:
dimension: high_value_customer {
type: yesno
sql: ${lifetime_revenue} > {% parameter revenue_threshold %} ;;
}
parameter: revenue_threshold {
type: number
default_value: "1000"
}
3. Custom Measures
Create complex calculations:
measure: repeat_purchase_rate {
type: number
sql: ${repeat_orders} / NULLIF(${total_orders}, 0) ;;
value_format_name: percent_2
}
Best Practices
-
Naming Conventions
- Use clear, descriptive names
- Follow a consistent pattern
- Include units in measure names when applicable
-
Documentation
dimension: customer_segment { type: string description: "Customer segment based on lifetime value" sql: ${TABLE}.segment ;; } -
Organization
- Group related views in folders
- Use consistent file naming
- Organize dimensions and measures logically
-
Performance
- Use persistent derived tables for complex calculations
- Implement appropriate indexes
- Use datagroups for caching
Common Use Cases
-
Sales Analytics
measure: conversion_rate { type: number sql: ${completed_orders} / NULLIF(${total_visits}, 0) ;; value_format_name: percent_2 } -
Customer Analysis
dimension: customer_lifetime_tier { type: tier tiers: [0, 100, 500, 1000, 5000] style: integer sql: ${lifetime_revenue} ;; } -
Inventory Management
dimension: stock_status { type: string sql: CASE WHEN ${inventory_count} = 0 THEN 'Out of Stock' WHEN ${inventory_count} < ${reorder_point} THEN 'Low Stock' ELSE 'In Stock' END ;; }
Conclusion
LookML provides a powerful way to model your data and make it accessible to business users. By abstracting complex SQL into reusable, version-controlled definitions, it enables self-service analytics while maintaining data governance and consistency.
The key to successful LookML implementation is understanding your business requirements, planning your data model carefully, and following best practices for organization and performance.