Jul 17, 2014
Data analysts often face problems with naming conventions, because their vocabulary spans both the business space ("how do we talk about our business?") and the data model space ("how do our systems represent data relating to our business?"). Add to this situation legacy naming conventions entrenched in systems that describe the world of the past, and you've got a real namespace challenge.
If you write queries without a modeling tool like Looker, every SQL query you write carries an extra burden of mapping column and table names into something sensible for business users. On a good day, mapping names is pesky. On bad days, it's error-prone and inconsistent. Of course, you could decide to not bother mapping names. But this shifts the cognitive burden to your data consumers, who are left wondering things like, "Is a 'user' a customer, or a partner, or both? And what does sord_ret_valid mean again?" With Looker, the LookML modeling layer allows you to map names and terms just once, in just one place, freeing up everyone to focus on the significance of the data, not the cryptic names.
LookML lets you abstract unhelpful details about the underlying data structure, surfacing only useful, well-named elements for business users. LookML allows data modelers to define names at every level of the model. Because LookML code encapsulates the details of your data model, including name aliasing, you can define the namespace mapping once, and Looker maps names consistently every time.
Good naming conventions multiply the value of your data model:
The sections below spell out guidelines for naming conventions, enabling you to get the most power out of your LookML model.
The names of the following elements are exposed to Looker users, so you'll want to put forethought into the names you choose.
Use letters at the start of your name to avoid errors. Use only letters 'a' through 'z' (lower case only) and '_' when defining names. LookML is case sensitive. Spaces are not allowed. Using upper-case letters or other special characters might not cause errors immediately, but it can cause confusion that leads to errors later. Stick with a-z and _.
Recommended conventions for naming views and view files are below.
An example of a well-formed view file is below.
Dimensions and measures are the fundamental building blocks of Looker queries. They need to be named intuitively for business users, which empowers business users to create intelligent queries and share the results with other business users (who also need to understand the field names).
In LookML, a dimension's sql parameter specifies a SQL expression to access the underlying column(s). The code below gives examples of dimensions named differently than the underlying columns, building on the order_items view definition above.
For tips on naming conventions so that your dimensions and measures remain consistent and readily-understandable, see Talal Assir's excellent post on Best Practices in Naming Entities and Fields.
For new LookML users it's not obvious when to use ${TABLE} (the table substitution operator) versus ${<fieldname>}. (the field substitution operator) within a field's sql: block. In short, use $ {TABLE}.<column name> only once per column in order to surface the underlying value as a dimension, and use ${<fieldname>} everywhere else.
The recommended conventions clarify:
Building on the e-commerce example above, suppose that users have no need for the column returned_at, but do need a value days_since_return.
The model file is where base views are declared. Typically a base view is named after the underlying view (the starting point for a query), but you can give it any name you want with the from: parameter.
You can also limit the fields that display in the query builder with the fields: parameter, which can reduce visual clutter and improve clarity for business users. The field list can be specified directly in the base_view declaration, or it can reference a set defined elsewhere.
The following example demonstrates renaming a base view and limiting the field names.
You can also use the label: parameter (instead of the base_view: identifier) to change the string that Looker displays for this base view in the Explore menu.
If you reference a single view from multiple model files, it's possible that the different users of each model might think of the view in different terms. LookML allows you to alias views in the model file as part of a base view definition using the from: parameter.
Building on our e-commerce use case, consider a separate model for users working in the warehouse. These users might think in terms of "SKUs" rather than "products". The following code re-uses the existing products view, but aliases it as skus
.
Some caution is necessary if you alias view names within the base view. You can only alias views that are not dependent on each other. If an aliased view (including the base view) depends on another view that you have also aliased, Looker will generate an error.
The following code generates an error.
The problem here is that the users view is joined and aliased as customers. However, orders is also joined, and it expects to be joined with the users view (which is now aliased as a different name).
When developing a model, it can be useful to reference different underlying test tables. For example, you might choose to work with a smaller dataset during development to validate your model, or you might have to work with test data before real data (and a final table name) is available.
LookML abstracts underlying table names, so you can develop models independently of underlying structure names. When a table name changes, you change the name-mapping in exactly one place in the view file, leaving the rest of the model intact. The name of the view does not change.
The following example demonstrates using a test table during development.
Looker allows you to represent your organization's data in just the right terms that make it easy to consume. With the LookML techniques outlined above, you can put the burden of naming data behind you, and look ahead to the bigger picture of empowering business users to derive their own insights.