Looker Cheat Sheet for Developers

This Looker cheat sheet is designed to give developers a quick reference guide to the key features and functionalities in Looker, focusing on LookML, SQL queries, and dashboard development. Whether you’re a beginner or an experienced Looker developer, this cheat sheet will help you quickly recall essential commands, syntax, and best practices for working in Looker.

1. LookML Basics

LookML (Looker Modeling Language) is the core language used in Looker to define data models and how data should be transformed for use in dashboards and reports.

LookML Structure:

Model: A LookML file that contains the relationships between different data tables.
View: Defines how data in a table can be queried.
Explore: Specifies the data sources (models) available to end-users for querying.
Dimension: A field that users can group by or filter on.
Measure: A calculated value, such as sum, count, or average.

Basic Syntax:

Define a Dimension:

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

Define a Measure:

measure: total_sales {
type: sum
sql: ${TABLE}.sales_amount ;;
}

Define a Join:

join: orders {
sql_on: ${customers.customer_id} = ${orders.customer_id} ;;
relationship: many_to_one
}

2. LookML Types

LookML uses different types of fields for data modeling. Some of the common field types include:

String: Text-based data.
Number: Numeric data.
Yesno: Boolean values (true/false).
Date: Date and time fields.

Example:

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

3. Measures in Looker

Measures are aggregations of data. Common types of measures include:

Count: Counts the rows in a table.
Sum: Sums a numeric column.
Average: Calculates the average of a column.

Example Measures:

measure: count_customers {
type: count
sql: ${customer_id} ;;
}

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

measure: average_order_value {
type: average
sql: ${TABLE}.order_value ;;
}

4. Filters in LookML

Filters are used to restrict data in reports and dashboards. You can define default filters in LookML to ensure consistent reporting across users.

Example Filter:

filter: signup_year {
type: string
sql: ${TABLE}.signup_year ;;
}

5. Persistent Derived Tables (PDTs)

PDTs are precomputed tables that store complex query results to improve performance. These tables can be refreshed on a schedule or when data is updated.

Basic PDT Definition:

derived_table: {
sql: SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id ;;
persist_for: “24 hours”
}

6. Exploring Data in Looker

Explores allow users to query data in a flexible, interactive environment without needing to write SQL manually.

Join Multiple Tables in an Explore:

explore: orders {
join: customers {
sql_on: ${customers.customer_id} = ${orders.customer_id} ;;
}
}

Creating a Drill-down: Drill-downs allow users to click on a dimension to view more granular data.

dimension: region {
type: string
sql: ${TABLE}.region ;;
drill_fields: [state, city]
}

7. SQL in LookML

LookML generates SQL under the hood. Knowing basic SQL is essential for Looker development.

Basic SELECT Query:

SELECT customer_id, SUM(order_value)
FROM orders
WHERE order_date >= ‘2023-01-01’
GROUP BY customer_id

Join SQL Example:

SELECT customers.name, SUM(orders.order_value)
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.name

8. Dashboard Creation in Looker

Dashboards in Looker are built by combining multiple visualizations and filters.

Basic Steps to Create a Dashboard:
1. Open an Explore and build your query.
2. Click “Save & Add to Dashboard.”
3. Choose the dashboard you want to add the visualization to or create a new one.
4. Use filters, drill-downs, and other features to enhance interactivity.
Embedding Dashboards: You can embed Looker dashboards in other applications using the Looker API or iframe embedding.

9. Best Practices for Looker Development

Organize Models and Views: Keep your models and views well-organized to improve maintainability.
Optimize SQL Queries: Always aim for efficient SQL by using indexes and avoiding unnecessary joins.
Use Derived Tables Wisely: Persistent Derived Tables can improve performance, but don’t overuse them as they can consume resources.
Limit the Number of Dimensions and Measures in Explores: Too many fields in an Explore can confuse users. Only include necessary fields.
Document Your Code: Comment on LookML code to explain complex joins, calculations, or filters.
Version Control: Use Git for LookML version control to track changes and collaborate with other developers.

10. Useful Looker Commands

Create a new Explore:

explore: orders {
join: customers {
sql_on: ${customers.customer_id} = ${orders.customer_id} ;;
}
}

Define a Calculated Field:

dimension: order_total_with_tax {
sql: ${TABLE}.order_total * (1 + ${TABLE}.tax_rate) ;;
}

Aggregate Function Examples:

Sum: SUM(order_value)
Count: COUNT(customer_id)
Average: AVG(order_value)
Min/Max: MIN(order_value), MAX(order_value)

Apply a Filter:

filter: active_customers {
type: yesno
sql: ${TABLE}.is_active = TRUE ;;
}

11. Looker Dashboard Tips

Add Filters to Dashboards: Use filters to allow users to customize the data they are viewing.
Drill-downs: Add drill-downs to dimensions for users to explore data in more depth.
Use Conditional Formatting: Apply conditional formatting to highlight critical metrics.
Set Performance Alerts: Use scheduled looks or alerts to notify users of critical changes in data.

12. Looker API Overview

The Looker API allows for programmatic interaction with Looker, such as retrieving data, managing dashboards, and embedding Looker elements in external applications.

Basic API Call to Retrieve Data:

curl -X GET “https://.api.looker.com/api/3.1/query/12345/run/json” \
-H “Authorization: token “

13. Common Looker Developer Challenges

Handling Large Datasets: Always optimize queries and use aggregation to reduce the load.
Data Security: Implement row-level security to control who sees what data.
Collaboration: Use Git for version control to work seamlessly with other developers.
Performance Issues: Regularly review and optimize LookML models and queries for performance.

14. Quick SQL Optimization Tips

Use indexes on frequently queried columns.
Avoid unnecessary joins and limit them to essential data.
Use GROUP BY sparingly, only when necessary.
Use WHERE clauses to filter data early in the query process.

15. Shortcuts and Commands

Run a Query: Ctrl + Enter (on the Explore page).
Switch to SQL View: Ctrl + Shift + S (to see the generated SQL).
Comment Code in LookML: Use # for comments.
Save & Rebuild Dashboards: Regularly save dashboards and visualizations to prevent data loss.

Conclusion

This Looker cheat sheet provides a handy reference to help you navigate the key aspects of Looker development, from writing LookML models to optimizing dashboards and working with SQL. Whether you’re working on a large-scale data model or refining a complex report, these commands, tips, and best practices will help you make the most of Looker’s capabilities.