..., where n is the total number of columns that form the primary key, and which may be omitted if it is 1, and key_name is any descriptive name.

Note: This applies to "regular" views that describe an underlying table, i.e. through a sql_table_name, derived_table, or implicit table name. In some cases, you may have a "field-only view", which would be joined using `join: {sql: ;;}`, for which this rule would not apply.

Rationale: With this naming convention, anyone creating a join can conclusively find the correct logic without having to investigate:

Additionally, anyone reading the join could conclusively verify it.

Notes

• The number can (and must) be 0 in the rare case that you are working with a table with no primary key.
• The name should be descriptive outside of the context of the view. So, prefer pk1_account_id over pk1_id. This way, views that logically share one or more primary keys can be recognized.
• For keys with more than one column, all the numbers should be equal to the total number of columns. So, pk3_a, pk3_b, and pk3_c, NOT pk1_a, pk1_b, and pk1_c.
• For keys with more than one column, this does mean you would need to define it in two ways. Once this way, and once as a single concatenated dimension with primary_key: yes (if necessary) so Looker can use it for generating symmetric aggregates. This is a small price to pay for the many instances of time saved looking up primary key information when writing/reading explores.
• An earlier version of these guidelines specified a naming convention starting with {n}pk. This has been changed to reflect the fact that many databases do not allow column names to start with a number.

... and the table definition should be defined before any other dimensions.

Rationale: By placing primary keys immediately below a derived table definition, users viewing the view file can more easily verify that the Primary Key Dimensions match the derived table's actual primary keys.

Rationale: The audience for PK information is developers. They use it to inform how to join and consume tables, and they can do so equally well with a hidden field.

Note: If a column that is a primary key should be user facing, you may want to expose it via a separate dimension.

...unless the view declares that it has no primary key via a pk0_ named dimension.

In the case of a simple, 1-column primary key, this simply means the Primary Key Dimension and the primary_key:yes dimension are the same dimension

However, for composite primary keys, you should ensure that the primary_key:yes dimension uses all of the Primary Key Dimensions in its sql:

...named like pkd_{key_name}

Rationale: By naming it similarly to Primary Keys, it will be suggested to anyone joining on a view as a possible join key. At the same time, the use of a `d` instead of a number allows it to be differentiated from a primary key

Note: If your database supports multiple projections of data, you may want to come up with a more sophisticated naming scheme instead.

...named like pkc_{key_name}

Rationale: By naming it similarly to Primary Keys, it will be suggested to anyone joining on a view as a possible join key. At the same time, the use of a `c` instead of a number allows it to be differentiated from a primary key

Note: The name for this concept varies greatly from database to database. "pkc" was chosen for cluster, in favor of "pks" which could be read as a "surrogate key".

Note: If you have compound cluster/sort keys, it would be a good idea to include the rank of each column in the dimension name. Unlike the PK naming convention, each column should get a different numner, e.g. pkc1_site, pkc2_date, pkc3_country.

... unless this is (A) a field-only view, or (B) a tightly coupled view (provide a base explore too!)

Rationale: Inter-view dependencies interfere with the re-use of the view

view: users {...} view: orders { ... measure: orders_per_user { sql: ${count} / NULLIF(${users.count},0) } } explore: orders {} # Errors :( view: users {...} view: orders {...} view: users_orders { # No need for a sql_table_name or derived_table measure: orders_per_user { sql: ${orders.count} / NULLIF(${users.count},0) } } explore: orders {} #Doesn't break like before! explore: users { join: orders {...} join: users_orders { sql: ;; # Use `sql` instead of `sql_on` and put some whitespace in it relationship: one_to_one view_label: "Orders" #For cleaner explore UI } }

Rationale: Field-level view labels can't be overridden by a join. Prefer view-level view labels.

"Plain" count fields can/should specify a filter of "primary key dimensions (or any non-nullable field) are not null"

Rationale: By default Looker will implement any non-filtered & non-distinct count field as a `COUNT(*)`. Filtering such "plain" counts on PK IS NOT NULL ensures correct counts in all of the following uses of the field: Counting just that table, counting that table when joined on as a one-to-one-or-zero table, counting that table with symmetric aggregates when joined on as a many_to_one table, and counting that table in explores with join paths. measure: count { type: count filters:{ field: pk1_user_id value: "NOT NULL" } }

Note: The LookML filter syntax for "is not null" varies depending on the type of the field. For strings, use -NULL. For numbers, use NOT NULL.

Rationale: User experience. Just do it.

Note: Think about the audience when you write a description. Information for end users can go in the description, whereas information for other developers can go in a LookML comment

Rationale: The ID field will make most sense for end users when they are exposed under the view for which they are a primary key.

Rationale: sql_trigger_value is an older construct and can have subtle timing issues when multiple tables interact.

Rationale: Primary keys define a contract that allows anyone reviewing the logic of a query to evaluate it modularly. Without them, any attempt to troubleshoot a query requires an arbitrarily deep investigation of related subqueries. With them, each query and subquery can be evaluated independently against their declared PK and the declared PK's of the participating tables.

...where {n} is the total number of columns in the primary key

Note: If you are using _in_query to generate a dynamic derived table where a column may or may not be present in the derived table's primary key, you should always select a static number of PK columns and conditionally NULL out their values, rather than selecting a dynamic number of columns.

Notes

• In most cases, unless you intend to use a window function to create sequence numbers within a partition, the primary key should simply be ALL of the grouped by fields.
• If you want to join a derived table on only a subset of its columns, what you probably want is to groups by ONLY those columns and then aggregate the rest (see example below)

Although the above two "questionable" examples satisfy rules T4 & T6, you will likely find that it is not natural to join them on their entire primary key when you try to use them elsewhere.

Note: This rule requires some reasoning to evaluate, but with proper PK information, can be done within the context of the current query. For example, the 1-to-1 full outer join example coalesces the PK's from the two sides of the join to come up with the effective primary key of the join.

... T5/T6 did not use ALL available columns. The window function should have a PARTITION BY of the preceding PK columns (at least one required)

Note: Actually, any window function that produces unique values for each row is acceptable, but this is almost always ROW_NUMBER(). Make sure not to use a RANK-like window function instead of ROW_NUMBER. The former yields duplicate ranks when two rows match. (For example, "Monday, Monday, Tuesday" would have ranks of 1,1,3)

I only mention the above example because I've actually seen it used. The primary key for this is simply site,date,source. The ROW_NUMBER added as an afterthough is not a primary key. (In this ruleset, it is disallowed by rules T5/T6 which require one or more columns to precede the window function, if any)

Note: This does not strictly need to immediately follow the columns required by rules T5 - T7. After, such columns, the PK may continue with 0, 1, or more aggregates, but this is rare, and in most cases such aggregates would not need to be in a primary key.

Rationale: Often, short subqueries are desired for existence or scalar logic that are (a) not reused outside of the current query, and (b) simple enough to read and understand that they can be analyzed in the context of the containing query with little added complexity

Rationale: Since this style of query/subquery simply passes on the PK attributes of the referenced table/query, it's reasonable to save time writing this query and for readers of your query to go find that PK information in that single other table/query.

Rationale: Rather than trusting/hoping the data will be what you want, we can ensure it and prevent bad data from propagating upstream

Note: In a dialect where dividing by zero throws an error, this is a good approach. If done in a PDT, the building of the new PDT will fail.

...unless a limit is also used (which should itself be unusual)

Rationale: We often order data when querying directly, but make sure to remove this ORDER BY clause when saving the query as a derived table, as the sort order does not ultimately affect the table, but does cause the query to use more resources.

Rationale: Sorting is much more resource intensive than a simple MIN/MAX (or FIRST/LAST if your dialect supports it).

Note: If you want to select the min/max based on a different column, like date, see this guide

To clarify, liquid/dynamic SQL in non-persisted, aka ephemeral, derived tables is fine.

Rationale: Triggered persistent tables usually don't have the necessary explore context to correctly evaluate the dynamic logic. And even persists_for derived tables do not work as expected with dynamic SQL (confirmed as of v5.14)

Note: One possible exception may be {{_model._name}}, but still be careful.

Rationale: More often than not, developers use UNION instead of UNION ALL unintentionally, with terrible performance implications. Therefore, if you actually need a plain UNION, make that explicit in an accompanying comment.

Note: Sometimes you may want to make joins dynamic using _in_query. This requires you to reference fields to join on IF the view is _in_query but NOT use the substitution operator since that forces the view to be in the query.

This is relevant for any 1:1, 1:m, m:1, 1:0or1 joins. In otherwords, any non-many-to-many join (which you should only be using with great caution)

Note: In the EAV example, even though the first part of the primary key, the distribution key, and the sort/cluster key all result in `entity_id`, writing them out multiple times with the different key dimensions communicates what they do to anyone reading the join, and should have negligible performance impact.

For small tables, this won't matter much, but for big tables it will make a big difference, so it's good to be in the habit.

explore: events { always_filter: { filters:{ field:codim_activity.__date value:"28 days,null"} } }

Note: This is all that's needed if the filtered table is the base of an explore with only left or inner joins. Otherwise, you can push the filter down into the sql_table_name (or derived table) using {% condition view.always_filtered_field %} table.column {% endcondition %}

Rationale: In addition to performance problems associated with the multiplicative "fanout", the spurious relationships can cause bad results, and break user trust.

Note: The above strategy relies on enforcement of rule F3. See this article for more details.

Note: Specifying join logic using sql_on is usually preferred to using foreign_key, as sql_on is more general-purpose.

If you do use foreign_key to specify your join logic, Looker will implicitly join this declared foreign key against the primary key of the joined table. As such, the resulting join should be a many_to_one join (or rarely one_to_one if you use the primary key of the base view as the foreign key in the join)

Among other reasons to favor brevity, long explore names/labels will be truncated in Looker's Explore picker UI. Help maintain a good experience for end users by keeping Explore names below 30 characters.

Note: In LAMS, you can customize the max length for this rule by setting a maxLength option associated with this rule in your manifest file.

Each LookML block within a file should add one level of indentation for any inner line breaks, except for the line containin the block's closing brace, which should be at the prior indentation.