Naming and aliasing conventions
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.
A LookML model represents a business user's view of the data
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:
- Your business users get more value because they readily understand view and field names.
- You spend less time supporting users with misunderstandings caused by cryptic names.
- Your LookML model is more resilient over time, able to accommodate the evolving needs of business users and the underlying data.
The sections below spell out guidelines for naming conventions, enabling you to get the most power out of your LookML model.
Aliasable names: dimensions, measures, views, base views and models
The names of the following elements are exposed to Looker users, so you'll want to put forethought into the names you choose.
- Dimension and measure fields - Query builders and data consumers alike see these names every day on dashboards, reports, tables, embedded visualizations, and so on. Ideally, business users can look at a field name and understand exactly what its data represents. The mapping of dimension and measure names to their respective columns in the underlying column names occurs in View files.
- View names - View names, which typically correspond to an underlying table or a derived table, are displayed in Looker as a prefix to dimension and measure names. View names are defined in the View file.
- Base views - A Base View is the starting point for data exploration in Looker (Base Views show up under the Explore menu), so use a name that evokes the subject area related to the exploration. For example, naming a base view "Sales" might resonate with e-commerce business users more than the name of the underlying view (such as "order_items"). Base view names are defined in the model file.
- Models - The model name shows up the Explore menu (for Lookers with multiple models). The model name is defined by the name of the .model.lookml file.
a-z and _
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 _.
View Name = file name, one view per file
Recommended conventions for naming views and view files are below.
- Every view file contains exactly one view declaration, containing all associated dimensions and measures for the view.
- The view filename matches the name of the view declared in the file.
An example of a well-formed view file is below.
Dimension and measure names are for business users
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.
Declare dimensions for all columns, using ${TABLE} once for each column
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:
- For every column in a view's underlying table, declare a dimension. Use $ {TABLE}.<column name> to reference the underlying column. This makes every underlying column available to the LookML model as ${<view name>.<dimension name>}. Within the view that declares the dimension, you don't need to scope the view name, so ${<dimension name>} alone (without the <view name>) also works.
- Refer to each column exactly once using the table substitution operator. All other references use the field substitution operator.
- If there's no need for business users to see or use a dimension directly, declare it hidden: true. This prevents Looker from displaying the dimension to users when building queries. However, the dimension is still accessible by name anywhere in the model.
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.
Naming base views and limiting fields (from:, label: and fields:)
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.
Aliasing view names from the base view
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).
Substituting an underlying table without changing the model
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.
Conclusion
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.
