home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
LookML Parameter Reference by Function

Overview

LookML is the language Looker uses to describe dimensions, aggregates, calculations, and data relationships in a SQL database, and to construct SQL queries against that database. For recommended steps to learning LookML, see the Steps to Learning LookML documentation page.

You can use this reference page to identify how LookML parameters can help you achieve the data modeling effects you want. This reference may also be useful if you are looking at an existing model and want to know what each parameter is doing. You may also want to visit the individual reference pages for the relevant LookML structures: model, view, Explore, join, manifest, or fields.

For an introduction to these LookML structures and how they relate to each other, see the LookML Terms and Concepts documentation page. You can find links to additional resources at the bottom of this page to help you identify, understand, and implement LookML elements.

How to Read This Reference

On this page, parameters are organized by the different functions they can accomplish and the different effects you can achieve with them.

The table entry in each section lists:

Parameter Name Level Description
Name and link to the parameter’s main documentation page Level at which the parameter applies, with a link to a list of all other parameters available at that level: model, view, Explore, join, manifest, or fields. Some parameters can be used at multiple levels in LookML. Brief description of the parameter’s function and whether it can have subparameters

Some field-level parameters can be used with multiple field types, so on this page we mark each field-level parameter with icons indicating the field types it can be used with. You can hover over an icon to see its name, or click it to see a reference page for that field type.

D = Dimension    DG = Dimension Group    F = Filter    M = Measure   P = Parameter  


Structural Parameters

These LookML parameters affect the structure of a project or its underlying files.

Major Structural Parameters in a Model or an Explore File

As shown on the LookML Terms and Concepts documentation page, a project contains one or more model files, which use parameters to define a model and its Explores and joins.

This section describes the major structural parameters that you typically put in a model file. These act as the framework for all other parameters. An explore parameter is usually defined at the top level of a model file, but when you are using a native derived table it may be defined in an Explore file.

Parameter Name Level Description
explore Model Exposes a view in the Explore menu. For more information about Explores and their parameters, see the Explore Parameters reference page. Affects the Explore name and menu. This parameter has many subparameters listed on the Explore Parameters reference page and elsewhere on this page.
fields Explore Limits the fields available in an Explore from its base view and through the Explore’s joins. Affects the fields available in the field picker.
include Model Adds files to a model. This parameter can also be used in view files for native derived tables.
join Join Joins an additional view to an Explore. See also the Joining Views section below.
test Model ADDED6.20 Creates a data test to verify your model’s logic. The project settings include an option to require data tests. When this is enabled for a project, developers on the project must run data tests before deploying their changes to production. This parameter has explore_source and assert subparameters.

Major Structural Parameters in a View File

As shown on the LookML Terms and Concepts documentation page, a project contains one or more view files, which use parameters to define that view, its fields (including dimensions and measures), and its sets of fields.

This section describes the major structural parameters that you typically put in a view file.

Parameter Name Level Description
dimension View (but listed on field reference page) Creates a dimension field. Affects the default behavior of the field picker.
dimension_group View (but listed on field reference page) Creates several time-based dimensions at the same time. Affects the default behavior of the field picker.
fields Join Determines which fields from a join are brought into an Explore
measure View (but listed on field reference page) Creates a measure field. Affects the default behavior of the field picker.
view Model (but used in view file) Creates a view. Affects the default behavior of the field picker. This parameter has many subparameters listed on the View Parameters reference page and elsewhere on the current page.

Helper Structural Parameters

These parameters help you define the structural behavior you want, such as letting you specify sets of fields, include certain files (and their contents), reuse definitions, and so on.

Parameter Name Level Description
+ (refinements) Explore and View Use LookML refinements to build on the LookML of views and Explores in your projects.
constant Manifest ADDED6.12 Defines a LookML constant that can be used throughout your project. This parameter has value and export subparameters.
extends Explore and View Reuses the definition of another LookML object, adding and overriding subparameters as desired. This parameter can also be used with LookML dashboards.
extension Explore and View Specifies that an Explore, a view, or a dashboard cannot be used directly. Instead the definition is intended as a template for other objects, which use extends based on this object. This parameter can also be used with dashboards.
include Model Adds files to a model. This parameter can also be used in view files for native derived tables.
intervals Fields: DG ADDED6.0 Define the set of duration dimensions you want in a dimension_group, for dimension groups configured as type: duration. Affects the default behavior of the field picker.
local_dependency Manifest Specifies one or more projects that contain files that you want to include
parameter View (but listed on field reference page) Creates a filter-only field users can use to provide input to a Liquid {% parameter %} tag. Affects the default behavior of the field picker.
project_name Manifest Specifies the name of the current project
remote_dependency Manifest ADDED5.18 Specifies one or more remote repositories that contain files that you want to include
set View Defines a set of dimensions and measures to be used in other parameters. This parameter can be used to limit the fields available to a join and therefore to the field picker. This parameter can also be used to define which fields should appear when a user drills into the data. This parameter has a fields subparameter.
timeframes Fields: DG Defines the set of timeframe dimensions you want in a dimension_group, for dimension groups configured as type: time. Affects the default behavior of the field picker.

Extension Framework Applications

Looker developers can add an application to Looker’s extension framework using the application parameter in a project’s manifest file. Once an extension is added to a project, Looker users with appropriate permissions can see the extension listed in the Looker Browse menu.

Parameter Name Level Description
application Manifest ADDED7.12 Adds an application to the project using Looker’s extension framework. This parameter has subparameters described on the application documentation page.

Explore Name and Menu

The name of an Explore and its appearance on the Explore menu can help your users choose the right Explore for their needs. These LookML parameters affect the default behavior for an Explore’s name and appearance on the Explore menu.

For further discussion, see the Explore Name and Menu section of the Changing the Explore Menu and Field Picker documentation page.

Default Behavior

The default behavior for an Explore’s name and its appearance on the Explore menu is specified by the parameters listed below.

Parameter Name Level Description
explore Model Exposes a view in the Explore menu. For more information about Explores and their parameters, see the Explore Parameters reference page. Affects the Explore name and menu.

Modifying the Explore Name and Menu

The Explore name and how it appears on the Explore menu can be modified by the parameters in the table below.

Parameter Name Level Description
description Explore Adds a description for an Explore that appears to users on the Explore page and in the Explore menu
group_label Explore Creates a label to use as a heading in the Explore menu
hidden Explore Hides an Explore from the Explore menu
label Explore Changes the way an Explore appears in the Explore menu
label Model Changes the way a model appears in the Explore menu

Field Picker

The organization and display names of the views and fields in the field picker can help users understand visualizations and find the fields they need in Explores. These LookML parameters affect the default behavior for the field picker’s contents, appearance, and organization.

For further discussion, see the Field Picker section of the Changing the Explore Menu and Field Picker documentation page.

Default Behavior

The default field picker appearance and behavior is specified by the parameters listed below. For a full explanation of this default behavior, see the Field Picker Display section of the Changing the Explore Menu and Field Picker documentation page.

Parameter Name Level Description
dimension View (but listed on field reference page) Creates a dimension field
dimension_group View (but listed on field reference page) Creates several time-based dimensions at the same time
filter View (but listed on field reference page) Creates a filter-only field for use in a templated filter or conditional join
measure View (but listed on field reference page) Creates a measure field
parameter View (but listed on field reference page) Creates a filter-only field users can use to provide input to a Liquid {% parameter %} tag
timeframes Fields: DG Defines the set of timeframe dimensions you want in a dimension_group, for dimension groups configured as type: time
intervals Fields: DG ADDED6.0 Defines the set of duration dimensions you want in a dimension_group, for dimension groups configured as type: duration
view Model (but listed on view reference page) Creates a view

Modifying the View Names in the Field Picker

A view’s display name can help your users understand and find the fields they need in Explores. If a visualization shows the view name, then modifying the way the name appears can help users understand the visualization. This section describes the LookML parameters that affect the view names in the field picker. For a full explanation of this topic, see the Field Picker section of the Changing the Explore Menu and Field Picker documentation page.

Parameter Name Level Description
label View Specifies how the view name will appear in the field picker
view_label Explore Specifies how a group of fields from the Explore’s base view will be labeled in the field picker
view_label Join Changes the way the join’s view name appears in the field picker

Modifying the Field Listings in the Field Picker

Modifying the way that fields are listed can help your users understand visualizations and find the fields they need in Explores. The way fields are listed in the field picker can be modified by the parameters in the table below. For a full explanation of this topic, see the Field Picker section of the Changing the Explore Menu and Field Picker documentation page.

Parameter Name Level Description
description Fields: D DG M F P Adds a description to the field users can see on hovering
fields Explore Limits the fields available in an Explore from its base view and through the Explore’s joins. Affects the fields available in the field picker.
fields Join Determines which fields from a join are brought into an Explore
hidden Fields: D DG M F P Hides a field from the Explore UI
label Fields: D DG M F P Changes the way a field name appears in the field picker
label_from_parameter Fields: D M Changes the way a field name appears in a visualization based on the input to a parameter

Modifying the Organization of Fields in the Field Picker

The organization of fields can help your users find the fields they need in Explores. The organization of fields in the field picker can be modified by the parameters in the table below. For a full explanation of this topic, see the Field Picker section of the Changing the Explore Menu and Field Picker documentation page.

Parameter Name Level Description
group_label Fields: D DG M F Group fields together within a view in the field picker
view_label Fields: D DG M F P Changes the fields that appear within a view in the field picker

Localization

If you are localizing your model, the location settings parameters let you customize the display of the labels and descriptions in the Looker UI based on the user’s locale setting. See the Model Localization documentation page for information on setting up your model for localization.

Parameter Name Level Description
localization_settings Manifest ADDED6.2 Specifies the localization information for your model. This parameter has default_locale and localization_level subparameters.
localization_level Manifest ADDED6.2 Specifies whether strings with no translation are allowed in your model
default_locale Manifest ADDED6.2 Specifies the locale that will be used as your model’s default for translating strings

Data Values and Data Display

Modifying the values and presentation of the data in the Data section of an Explore can help your users understand their results. You may want to modify or restrict the types of data for your dimensions or measures, change the format of the data your users see, fill in missing values, or change other visualization options.

Users can also define their own table calculations that show in the Data section. However, when possible, you should define dimensions and measures in LookML so that the correct calculation is made once and then used consistently in various queries.

Data Values for Multiple Field Types

These parameters let you change data values for multiple field types.

Parameter Name Level Description
case Fields: D M Creates a discrete set of values a dimension can have determined by SQL conditions. This parameter has when and else subparameters.
sql Fields: D DG M F Determines how a field will be calculated
type (for dimension, filter, or parameter) Fields: D F P Specifies the type of dimension, filter, or parameter
type (for dimension group) Fields: DG Specifies the type of dimension group
type (for measure) Fields: M Specifies the type of measure

Data Values for Specific Dimension Types

You can modify the data values for some types of dimensions using type-specific parameters.

Dimensions of type: date or type: datetime

Parameter Name Level Description
convert_tz Fields: D DG F M P Disables automatic time zone conversion for the field
datatype Fields: D DG F M Specifies the type of time data you are providing to the field
fiscal_month_offset Model Specifies the month your fiscal year begins (if it differs from calendar year)
week_start_day Model Specifies the day of week that week-related dimensions should start on.

Dimensions of type: distance

Parameter Name Level Description
end_location_field Fields: D Defines the field that contains the end location for a field of type: distance. This parameter is also considered a structural parameter.
start_location_field Fields: D Defines the field that contains the start location for a field of type: distance
units Fields: D Specifies the unit to use for fields of type: distance

Dimensions of type: location

Parameter Name Level Description
sql_latitude Fields: D Defines the latitude of a dimension of type: location
sql_longitude Fields: D Defines the longitude of a dimension of type: location

Dimensions of type: string

Parameter Name Level Description
string_datatype Fields: D Specifies the unicode datatype for a dimension of type: string for SQL Server or MySQL

Dimensions of type: tier

Parameter Name Level Description
tiers Fields: D Defines the tiers for a dimension of type: tier

Dimension Groups of type: duration or Dimensions of type: duration_x

Parameter Name Level Description
sql_start Fields: D DG ADDED6.0 Defines the start time of a duration for a dimension group of type: duration or a dimension of type: duration_x
sql_end Fields: D DG ADDED6.0 Defines the end time of a duration for a dimension group of type: duration or a dimension of type: duration_x

Data Values for Measures

Most of these parameters are limited to specific measure types, as specified in the table. To create a count filtered by a dimensional value, see Filtering Counts by a Dimension. To create a measure that calculates the percentage between two fields, see Percentages.

Parameter Name Level Description
approximate Fields: M Performs an approximate count for measures of type: count_distinct, for Redshift or BigQuery
approximate_threshold Fields: M Sets the count at which BigQuery switches from an exact count distinct to an approximate count distinct.
direction Fields: M Determines the direction that measures of type: percent_of_total or of type: running_total are calculated when pivots are used.
list_field Fields: M Declares the dimension from which a measure of type: list will be calculated. This parameter is also considered a structural parameter.
percentile Fields: M Specifies the fractional value (the Nth percentile) for a measure of type: percentile or type: percentile_distinct
primary_key Fields: D Declares a dimension as the primary key of a view
sql_distinct_key Fields: M Defines the unique entities over which a distinct measure, such as type: sum_distinct or type: average_distinct, will be calculated
symmetric_aggregates Explore Specifies whether symmetric aggregates are enabled for an Explore. This parameter is also listed for parameters affecting how a join behaves. See also the Joining Views section below.

Data Formats

You can make data values more easily readable for your users by formatting the data using the parameters in the following table.

Parameter Name Level Description
html Fields: D DG M Modifies the HTML output of a field using Liquid templating
named_value_format Model Creates a custom value format to be used with value_format_name. This parameter has value_format and strict_value_format subparameters.
style Fields: D Changes the way that tiers appear in the Looker UI for a dimension of type: tier
value_format Fields: D M Formats the output of a field using Excel-style options
value_format_name Fields: D M Formats the output of a field using a built-in or custom format

Data Order and Filling

For most fields, the sort order is straightforward — just an alphanumeric sort of the values. In some cases, you may want the results of a New LookML case or Old LookML sql_case statement to sort in a particular order. Or you may want the values to sort by another field’s value. You can also use the allow_fill parameter to enable or prevent users from asking Looker to fill in missing dates and values for a field.

Parameter Name Level Description
allow_fill Fields: D DG Determines if dimension filling is allowed for a dimension
alpha_sort Fields: D Makes a case parameter sort its conditions alphabetically
order_by_field Fields: D DG

Sorts a field by the values of another field

ADDED6.2 Supported for dimension groups

Visualizing Location Data

Looker provides a variety of maps you can make available to visualize locational data values in the Visualization section. You can also create and use custom maps for the visualizations.

Parameter Name Level Description
map_layer Model Creates custom maps to be used with map_layer_name. This parameter has many subparameters listed on the map_layer page.
map_layer_name Fields: D Specifies a mapping from a data value to a geographic region that you’ve defined on a built-in or custom map.

Clickable Actions for Data Values

Drilling

In Looker, every query result can be the starting point for another query. Users can click on a data value to drill into the data. You can also use LookML to specify which fields are displayed when the user drills into the data. Dimensions and measures have different default behavior for drilling.

For advanced options for building out a custom drill path, see the More Powerful Data Drilling article in the Help Center.

Parameter Name Level Description
drill_fields Fields: D DG M Declares the list of fields that will be displayed when the measure or dimension is drilled into
set View Defines a set of dimensions and measures to be used in other parameters. This parameter be used to limit the fields available to a join and therefore to the field picker. This parameter can also can be used to define which fields should appear when a user drills into the data. See also Using Sets for Drill-Down Details on the Additional LookML Basics documentation page.

Data Actions

Sometimes your users will want to be able to trigger other events after viewing the data. If you are using the Looker Action Hub, you can specify that fields with certain tags can use specific integrated services. You can also use data actions to specify what options are available to the users for a field.

Parameter Name Level Description
action Fields: D M Creates a data action on a field that lets users perform tasks in other tools, directly from Looker. This parameter has many subparameters listed on the action page.
tags Fields: D DG M F P Adds text that can be passed to other applications to provide data about a field

Linking

You can let your users click on a data value to navigate to a related URL. With these parameters, you can specify which fields are displayed when the user drills into the data, as well as the text to display, the destination URL, and a favicon for the destination website.

Parameter Name Level Description
link Fields: D M Creates links to other Looker and external content. This parameter has label, url, and icon_url subparameters. For advanced options using link, see also the More Powerful Data Drilling article in the Help Center.

Filtering

Users can apply filters to their queries in Explores, Looks, and dashboards.

You can help curate the user’s experience by specifying filter behavior directly in your LookML, like adding helpful filters or ensuring that they don’t accidentally create a query that puts too much demand on your database resources. You can specify filter behaviors like case-sensitivity, default values, and the filter suggestions that Looker can generate while the user is typing.

For more general discussion of filtering in LookML, see Filtering Result Sets on the Additional LookML Basics documentation page. For advanced options using templated filters, see the Templated Filters and Liquid Parameters and Advanced Templated Filters articles in the Help Center.

You can find a full list of filter expressions and syntax on the Looker Filter Expressions documentation page.

Requiring Filters with Fixed Values

These parameters let you specify filters that will always be applied.

See also Filtering Counts by a Dimension on the Additional LookML Basics documentation page.

Parameter Name Level Description
access_filter Explore Adds user-specific filters to an Explore. This parameter has the subparameters field and user_attribute.
sql_always_having Explore Inserts conditions into the query’s HAVING clause that a user cannot change or remove for this Explore
sql_always_where Explore Inserts conditions into the query’s WHERE clause that a user cannot change or remove for this Explore
sql_where Join If this join is included in the query, inserts conditions into the query’s WHERE clause that a user cannot change or remove for this Explore. For BigQuery only. This parameter could also be considered a filter parameter.

Requiring Filters with Changeable Values

These parameters let you specify filters that must be used, but permit the user to change the filters’ values.

Parameter Name Level Description
always_filter Explore Adds filters a user can change, but not remove, to an Explore. This parameter has a filters subparameter.
filter Fields: F Creates a filter-only field for use in a templated filter
filters Fields: M Restricts a measure’s calculation based on dimension limitations

Preventing Filters

These parameters let you prevent a user from using a field as a filter, either in all circumstances or just when drilling.

Parameter Name Level Description
can_filter Fields: D DG M Determines if a dimension or measure can be used as a filter
skip_drill_filter Fields: D Stops a dimension from being added to the filters when a measure is drilled into

Conditional Filters

In some cases, you may want to specify that the user must use one of several filters to narrow their query. For example, the user must limit the query by date or by region.

In addition to the parameter below, see also Filtering Result Sets on the Additional LookML Basics documentation page.

Parameter Name Level Description
conditionally_filter Explore Adds filters to an Explore if a user does not add their own filter from a specific list. This parameter has filters and unless subparameters.

Filter Value Behavior

To make filtering easier for your users, you can provide a default value for the filter or treat the filter values as case-insensitive.

Parameter Name Level Description
case_sensitive Explore Specifies whether filters are case-sensitive for an Explore
case_sensitive Fields: D F Specifies whether filters are case-sensitive for a dimension
case_sensitive Model Specifies whether filters are case-sensitive for a model
default_value Fields: F P Specifies a default value for filter fields

Filter Suggestions

Filter suggestions are a great way to help your users filter data successfully. In some cases, it may be useful to disable suggestions or to change the suggestion behavior.

These LookML parameters affect the default behavior for filter suggestions.

Default Behavior

By default, Looker’s suggestions for a given filter field are based on all of the unique values in that field’s data.

Enabling or Disabling Suggestions

If you think that a field has a very large number of unique values, it might make sense to disable filter suggestions for that field. That prevents the user from having to wade through too many suggestions and the database from having to provide those suggestions. You can enable or disable filter suggestions at several levels.

Parameter Name Level Description
suggestable Fields: D DG M F P Enables or disables suggestions for a field
suggestions View Enables or disables suggestions for all dimensions on this view

Suggestion Values

By default, Looker generates filter suggestions based on a field’s unique values. In some cases, the suggestions might be more useful if you specified the values Looker should suggest, like the most likely values. Additionally, if you are limiting access to some values in the data, then you can choose to apply or not apply those limits to the suggestions.

Parameter Name Level Description
allowed_value Fields: P Specifies the choices for a parameter. This parameter has label and value subparameters.
bypass_suggest_restrictions Fields: D DG F P Shows suggestions to users when sql_always_where is in use, but doesn’t apply those limits to the suggestions
full_suggestions Fields: D DG F P Shows suggestions to users when sql_always_where is in use, and does apply those limits to the suggestions
suggest_dimension Fields: D DG M F P Bases the suggestions for a field on the values of a different dimension
suggest_explore Fields: D DG M F P Bases the suggestions for a field on the values of a different Explore
suggestions Fields: D F P Declares a list of values that will be used for a field’s suggestions

Caching Suggestions

By default, Looker generates filter suggestions based on a field’s unique values. Those values are cached to help performance, but you can change the length of time that the cached values are used. If the data is fairly stable, consider using a longer time to improve the performance for getting those suggestion values.

Parameter Name Level Description
suggest_persist_for Fields: D F P Changes the cache settings for Looker filter suggestions

Joining Views

As discussed in Working with Joins in LookML, joins enable the exploration of data from more than one view at the same time. You can join together different views to let users see how parts of your data relate to each other.

Joins are defined in the model file to establish the relationship between an Explore and a view. Joins connect one or more views in a single Explore, either directly or through another joined view.

These LookML parameters let you create joins and specify how they work.

What to Join

There are a variety of parameters that specify what views to join, in general and specific situations. In addition, you can specify which fields will be brought into the join.

Parameter Name Level Description
always_join Explore Specifies which joins must always be applied to an Explore
fields Join Determines which fields from a join are brought into an Explore
from Join Specifies the view on which a join will be based
include Model Adds files to a model. Only views in files available in the model can be used for joins
join Explore Joins an additional view to an Explore. For more information about joins and their parameters, see the Join Parameters reference page. This parameter has many subparameters listed elsewhere on the current page.
required_joins Join Specifies which joins should be applied to an Explore when fields from a certain join are chosen
sql_table_name Join Specifies the database table on which a join will be based

How to Join

You can specify how the joins between views should work and what the join condition will be. You should also specify a primary key so that Looker can use symmetric aggregates to provide correct results for aggregate functions.

For further discussion of symmetric aggregates, see also the A Simple Explanation of Symmetric Aggregates, or “Why On Earth Does My SQL Look Like That?” article in the Help Center and the Aggregate Functions Gone Bad and the Joins Who Made Them that Way post on the Looker blog.

Parameter Name Level Description
foreign_key Join Specifies a relationship between an Explore and a join using the joined view’s primary key
outer_only Join Specifies whether all queries must use an outer join
primary_key Fields: D Declares a dimension as the primary key of a view
relationship Join Declares a join as having a one-to-one, many-to-one, one-to-many, or many-to-many relationship
sql_on Join Specifies a relationship between an Explore and a join by writing a SQL ON clause
symmetric_aggregates Explore Specifies whether symmetric aggregates are enabled for an Explore. This parameter is also listed with parameters affecting measure value.
type Join Declares a join as being a left, a full, an inner, or a cross type

Caching

Looker reduces the load on your database and improves performance by using cached results of prior queries when available and permitted by your caching policy. In addition, you can create complex queries as persistent derived tables (PDTs), which store their results to simplify later queries. When planning your caching, you should consider both performance and how “fresh” the data should be for your users.

Caching Queries

You can use datagroups to integrate Looker more closely with the ETL (extract, transform, load) phase of your data pipeline. For example, if you batch-load data through a nightly ETL job, then you can have Looker notice that the ETL has finished and clear any related cached queries.

For further discussion, see the Caching Queries and Rebuilding PDTs with Datagroups documentation page.

Parameter Name Level Description
datagroup Model Creates a datagroup caching policy for the model. This parameter has max_cache_age, sql_trigger, label, and description subparameters.
persist_for Explore Changes the cache settings for an Explore. Consider using the greater functionality of a datagroup parameter instead.
persist_for Model Changes the cache settings for a model. Consider using the greater functionality of a datagroup parameter instead.
persist_with Explore Specifies the datagroup to use for the Explore’s caching policy
persist_with Model Specifies the datagroup to use for the model’s caching policy

Caching Filter Suggestions

By default, Looker generates filter suggestions based on a field’s unique values. Those values are cached to help performance, but you can change the length of time that the cached values are used. If the data is fairly stable, consider using a longer cache time to improve the performance for getting those suggestion values.

Parameter Name Level Description
suggest_persist_for Fields: D F P Changes the cache settings for Looker filter suggestions

Caching for Persistent Derived Tables

See Regeneration and Caching Parameters for Persistent Derived Tables below for parameters that affect caching for persistent derived tables.

Derived Tables

As discussed in the Using Derived Tables tutorial, derived tables are important tools in Looker. They enable you to create new tables that don’t already exist in your database, expand the sophistication of your analyses, and enhance query performance.

For additional discussion, see Derived Tables and Facts Tables and Persistent Derived Tables on the LookML Terms and Concepts documentation page.

Structural Parameters for All Derived Tables

Derived tables can be defined using SQL or LookML. All derived tables start with this parameter:

Parameter Name Level Description
derived_table View Bases a view on a derived table. This parameter has many subparameters listed elsewhere on the current page and shown in the example usage table on the View Parameters documentation page.

Structural Parameters for Native Derived Tables

Native derived tables (NDTs) are much easier to read, understand, and reason about as you model your data.

All of the parameters in the table below are used to define the source data for an NDT. They are described in further detail and shown in example usage on the explore_source parameter’s documentation page.

Parameter Name Level Description
bind_all_filters View ADDED6.20 Use under explore_source to pass all filters from the Explore query into the NDT subquery. See the explore_source documentation page for an example.
NOTE: The explore_source parameter can have the bind_all_filters subparameter or the bind_filters subparameter, but not both.
bind_filters View Use under explore_source to pass a filter from the Explore query into the NDT subquery. To set this up, use the from_field subparameter to specify a field defined in the NDT view or accessible in the Explore to which the NDT is joined. At runtime, any filters on the from_field in the Explore will be passed into the to_field in the NDT subquery. See the explore_source documentation page for an example.
NOTE: The explore_source parameter can have the bind_all_filters subparameter or the bind_filters subparameter, but not both.
column View Use under explore_source to specify a column to include in the table. This parameter has a field subparameter.
derived_column View Specifies a column in the explore_source with an expression in the namespace of the inner columns. Aggregate SQL expressions will not work here, since there is no SQL grouping at this step. This is especially useful for defining window functions when applicable. This parameter has a sql subparameter.
explore_source View Generates SQL for a derived table based on its associated Explore. explore_source has various subparameters described in the other rows of this table and on its main documentation page.
expression_custom_filter View Specifies a custom filter expression on an explore_source query. Optional.
filters View Specifies a custom filter expression on an explore_source query. Optional.
include Model Includes the file(s) that contain the fields to be referenced in the table, as explained on the explore_source documentation page. See the include parameter’s main documentation page for other uses, such as adding files to models and views, and see Joining Views above for using include to create joins.
limit View Specifies the row limit of the query. Optional.
sorts View Optional. Specifies a sort for this explore_source. Enclose in square brackets, include the field name to sort, followed by :, and then indicate whether the field should be sorted in ascending or descending order using the keyword asc or desc.
timezone View Sets the time zone for the explore_source query. For ephemeral (non-persistent) derived tables, set this parameter to query_timezone to automatically use the time zone of the currently running query. If a time zone is not specified, by default the explore_source query will perform no time zone conversion, and will operate in the database time zone.

Structural Parameters for SQL Derived Tables

For SQL derived tables, use the parameter listed below.

Parameter Name Level Description
sql (for derived_table) View Declares the SQL query for a derived table

Structural Parameters for Custom Data Definition

For database dialects that require a custom Data Definition Language (DDL), use the parameters listed below. For example, if your database dialect doesn’t support CREATE TABLE as SELECT issued as a single SQL statement, use the create_process parameter to build a PDT in multiple steps:

Parameter Name Level Description
create_process View Specifies a series of SQL statements that will be executed one at a time, in the order listed. Each individual SQL statement is specified using the sql_step subparameter.
sql_create View Defines a SQL CREATE statement to create a PDT on a database dialect that requires custom DDL commands

Regeneration and Caching Parameters for Persistent Derived Tables

As discussed in the Caching section above, you can use datagroups to manage your caching and balance efficiency with data freshness.

You can use datagroups to regenerate persistent derived tables (PDTs) when fresh data becomes available. Alternatively, you can specify that PDTs should be regenerated infrequently, even though the underlying tables update frequently, so queries against the PDTs can be cached longer.

For further discussion, see Adding Persistence on the Using Derived Tables documentation page and the Caching Queries and Rebuilding PDTs with Datagroups documentation page.

Parameter Name Level Description
datagroup_trigger View Specifies the datagroup to use for the PDT rebuilding policy
persist_for (for derived_table) View Sets the maximum age of a PDT before it is regenerated. Consider using the more powerful datagroup_trigger parameter.
sql_trigger_value View Specifies the condition that causes a PDT to be regenerated. Consider using the more powerful datagroup_trigger parameter.

Query Efficiency Parameters for Derived Tables

Depending on your database dialect, there are some parameters you can use to improve the efficiency of your derived table.

Parameter Name Level Description
cluster_keys View ADDED6.0 Specifies that a persistent derived table be clustered by one or more fields in BigQuery
ADDED7.6 Support added for cluster_keys on Snowflake
distribution View Sets the distribution key of a PDT that is built in Redshift or Aster
distribution_style View Sets the distribution style of a PDT that is built in Redshift
indexes View Sets the indexes of a PDT built in a traditional database (e.g., MySQL, Postgres) or an interleaved sort key in Redshift
partition_keys View Specifies that a PDT be partitioned by one or more fields in Presto, or by a single date/time field in BigQuery
sortkeys View Sets the sort keys of a PDT that is built in Redshift

Aggregate Tables

As discussed on the Aggregate Awareness documentation page, aggregate tables can help you optimize queries on your Explores. Aggregate tables are persisted on your database, similar to persistent derived tables (PDTs).

Aggregate tables are defined under an Explore.

Parameter Name Level Description
aggregate_table Explore ADDED7.8 Creates an aggregate table for an Explore. This parameter has subparameters described on the aggregate_table documentation page.

Additional Query Behavior Parameters

There are various parameters that affect what and how you query, some of which have been described in other sections of this page according to what they do. This section describes the remaining LookML parameters that let you establish query behavior.

What to Query

These parameters define specific information about the connections, files, and fields that will be used for your queries.

Parameter Name Level Description
access_grant Model ADDED6.0 Creates an access grant that limits access to LookML structures to only those users with approved user attribute values. This parameter has user_attribute and allowed_values subparameters.
connection Model Changes the database connection for a model
fanout_on Fields: D DG M Enables access to Google BigQuery repeated fields
from Explore Specifies the view on which an Explore will be based and references the fields of that view by the Explore’s name
required_access_grants Explore ADDED6.0 Limits access to an Explore to only those users whose user attribute values match the access grants
required_access_grants Join ADDED6.0 Limits access to a join to only those users whose user attribute values match the access grants
required_access_grants View ADDED6.0 Limits access to a view to only those users whose user attribute values match the access grants
required_access_grants Fields: D DG M F P ADDED6.0 Limits access to a field to only those users whose user attribute values match the access grants
required_fields Fields: D M Requires that additional fields be added to a query when a field is chosen
sql_table_name Explore Specifies the database table on which an Explore will be based
sql_table_name View Changes the SQL table on which a view is based
view_name Explore Specifies the view on which an Explore is based, and references that view’s fields by the view’s name

How to Query

These parameters affect how Looker constructs or handles your queries.

Several of these parameters are involved in making sure that symmetric aggregates will work. For further discussion of symmetric aggregates, see also the A Simple Explanation of Symmetric Aggregates, or “Why On Earth Does My SQL Look Like That?” article in the Help Center.

Parameter Name Level Description
alias Fields: D DG M F P Allows saved URLs with old field names to remain functional after renaming a field
cancel_grouping_fields Explore Cancels the GROUP BY clause when certain fields are chosen in an Explore
primary_key Fields: D Declares a dimension as the primary key of a view
sql_where Join If this join is included in the query, it inserts conditions into the query’s WHERE clause that a user cannot change or remove for this Explore. For BigQuery only. This parameter could also be considered a filter parameter.
symmetric_aggregates Explore Specifies whether symmetric aggregates are enabled for an Explore. This parameter is also listed for parameters affecting measure values.

Parameters to Avoid

You may see the following parameters in your model, so we have included a list here of what they do. However, if you are adding new modeling, please avoid these parameters.

Parameter Name Level Description
access_filter_fields Explore REMOVED6.0 Replaced by access_filter
decimals Fields: D M REMOVED5.4 Replaced by value_format
distkey View REMOVED3.26 Replaced by distribution
format Fields: D M REMOVED3.16 Replaced by value_format
scoping Model REMOVED3.52 No longer required
sql Join AVOID3.10 Replaced by a combination of sql_on, foreign_key, type, and/or sql_table_name, as described on the sql (for Joins) documentation page
sql_foreign_key Join AVOID3.16 Replaced by foreign_key
template Model REMOVED3.30 No longer required
view_label View AVOID4.4 Replaced by label

Other Tutorials and Resources

These sections offer links to additional resources that may deepen your understanding of LookML and support you in other developer tasks in Looker.

Understanding the Development Process

The Data Modeling section of Looker’s documentation menu is designed to get you started in the Develop section of Looker.

Specific pages that may be helpful to look at:

Understanding LookML

The Steps to Learning LookML section of Looker’s documentation menu is designed to introduce you to basic LookML concepts and suggest a learning path toward proficiency in LookML.

Specific pages that may be helpful to look at:

Creating LookML Dashboards

Any dashboard files in a project contain dashboard-specific LookML parameters. The current page includes only data modeling parameters, not dashboard parameters, but the Creating and Managing LookML Dashboards section of Looker’s documentation menu is designed to introduce you to working with LookML dashboards.

Specific pages that may be helpful to look at:

Embedding, API, and Admin Options

Embedding

The following pages may be helpful for learning more about embedding Looks, Explores, and dashboards:

API

The Looker API section of Looker’s documentation menu is designed to get you started with Looker’s secure, “RESTful” application programming interface (API).

Admin Options

The Getting Started with Admin Options section of Looker’s documentation menu is designed to get you started in the Admin section of Looker.

Tutorials on Learn

These tutorials require access to Learn:

Name Game: A friendly and interesting introduction to LookML basics, showing how to build some basic structures and explore data about the first names given to children in the United States from 1910 to 2013.

e-commerce: A detailed LookML tutorial, based on a sample e-commerce clothing store, including “Try it Yourself” exercises and answers.

Advanced LookML: A tutorial on some of the more advanced features of LookML, using the same e-commerce dataset as the previous tutorial.

Top