Guide

Standards that keep the data platform consistent

Naming rules, keys, history and ownership. How to write standards for your data platform, and why they should be enforced automatically.

By Steffen Sande10 min read

The web shop has three teams building tables. One of them calls the customer key customer_id, another customer_no, and the third just id. One table marks active customers with active, another with is_active_flag. The order date is called date in one table and order_ts in another, and only one of them says whether the time is local time or UTC. Each choice makes sense on its own. Together they mean an analyst has to open every table to find out what the columns mean, and that two reports can join customer and order in different ways.

A standard is a list of decisions made once, so nobody has to make them again for every table. This article covers the decisions that pay off the most: names, keys, system columns, history and ownership. It ends with how the standard actually gets followed.

Table names

Start with the layer the table belongs to. The web shop’s data platform has three layers: one with raw data as it arrived from the source, one where the data is cleaned and reconciled, and one with tables built for specific purposes. Why the shared model belongs in the middle layer, the silver layer, is covered in the article on the shared data model. Here it is enough that the layer shows in the name, either as a prefix or as a separate schema.

For the web shop it might look like this:

Layer Pattern Example
Bronze brz_<source>__<object> brz_webshop__order
Silver slv_<object> slv_customer, slv_order, slv_product
Gold dim_<object>, fact_<process> dim_customer, fact_order_line

brz_ and slv_ are one of several possible patterns. What matters most is picking one and using it everywhere.

In the bronze layer the source system is part of the name, because the same object can come from several sources. dbt Labs uses the same pattern in its style guide for dbt projects: stg_[source]__[entity], with a double underscore between source and object so the two parts are easy to tell apart when a source name has several words. In the silver layer the source is gone from the name, because the customer from the web shop and the customer from customer service have become one customer there. In the gold layer the prefix says whether the table is a dimension or a fact table, as described in dimensional modeling in practice.

If you use Databricks, the layer can also live in the catalog or schema instead of the table name, for example silver.customer. Databricks notes that catalogs often map to an environment, a team or a business unit, and Unity Catalog stores all object names in lowercase. Pick one approach and use it everywhere. Two systems for the same thing are worse than either of them.

Also decide whether table names are singular or plural. The dbt guide recommends plural, customers and orders, because SQL then reads more like ordinary prose. These articles use singular, customer and order. Both work. What doesn’t work is having customer next to orders.

Column names

For columns, the most important rules are easy to write down:

  • Lowercase and underscores. order_date, not OrderDate. The dbt guide says the same: schemas, tables and columns in snake_case.
  • No abbreviations. customer, not cust, and not customer_nr in one table and customer_number in another. The dbt guide says plainly that readability beats brevity.
  • The business’s name, not the source’s. If the source system calls the customer user but the business says customer, the column is called customer_id in the silver layer. The term behind the name should be defined in one place, as described in from business term to column.
  • The same name everywhere. A key to the customer is called customer_id in every table that has it, not customer_id in the order table and id in the customer table. The dbt guide uses exactly this as its example.

The last rule is what makes it possible to join tables without looking anything up. Kimball Group says dimensions are conformed when their attributes have the same column names and the same contents. That is what lets sales and returns use the same customer dimension.

The second half of a column name says what kind of value the column holds. The ISO/IEC 11179 standard builds a data element from an object and a property, for example customer and municipality, and has a separate part on naming, part 5. It describes a representation term: the part of the name that says what type of value the element holds, such as amount, code, date, identifier, indicator or name. In practice this becomes fixed suffixes and prefixes:

Type of value Rule Example
Natural key <object>_id customer_id, product_id
Surrogate key <object>_key customer_key
True/false is_ or has_ prefix is_active, has_consent
Date _date suffix order_date
Timestamp _timestamp suffix, always in UTC paid_timestamp
Amount _amount suffix, currency in the description order_amount

The dbt guide has similar rules: is_ and has_ in front of booleans, <event>_at for timestamps in UTC, <event>_date for dates, and a suffix that flags a timestamp that is not in UTC. Whichever endings you choose, the point is the same. With a rule like this, anyone reading paid_timestamp knows the value is a timestamp in UTC without opening the documentation.

Keys, system columns and history

Every table should have a primary key, and the standard should say which kind. In the silver layer you can keep the natural key from the source, customer_id, because it lets you trace a row back. In the gold layer’s dimensions you also need a surrogate key. Kimball Group writes that the primary key of a dimension cannot be the natural key from the source system when changes are tracked over time, because there will then be several rows for the same natural key. The surrogate key is a number the data platform creates itself. Why and how is covered in dimensional modeling in practice. What the standard has to settle is the name and the type, so that customer_key is an integer in every table.

System columns are the columns the platform adds itself, regardless of the content. The standard should say which ones are required and what they are called. For the web shop that might be:

  • loaded_timestamp: when the row was loaded into the platform.
  • source_system: which system the row came from, in the silver layer where several sources are merged.

History needs more system columns. When a dimension keeps old versions of a row, known as SCD type 2, it gets a new row every time a tracked value changes. Kimball Group says such a dimension needs at least three extra columns: when the row started to apply, when it stopped applying, and a flag for which row is current. Give them the same names in every table, for example valid_from, valid_to and is_current. Then an analyst who has learned to fetch the current customer can fetch the current product the same way.

The standard should also say which type of history is the default. A simple choice is that the silver layer holds only the current value, and that history is built in the gold layer’s dimensions when someone needs it. What matters is that the choice is written down, so it isn’t made again for every table.

Ownership and required documentation

Two things should be required for every table: an owner and a description.

The owner is the one who answers when someone wonders what the table contains, and the one who decides what happens when it needs to change. Databricks recommends always assigning ownership of production catalogs and schemas to groups, not individuals. The same advice applies to tables: the owner should be a team, so the table isn’t left without an owner when someone changes jobs.

The description says what one row is, where the data comes from, and what the table does not contain. “Customer table” is not enough. “One row per customer, combined from the web shop and customer service. Does not include customers who have deleted their account” can be read by someone who has never seen the table before. Databricks writes that comments on tables and columns make data easier to discover and govern. The standard should require a description on the table and on every column, except the system columns, which are described once in the standard.

What a table that follows the standard looks like

If you describe your tables as data contracts, the standard can be read straight from the contract. Here is the silver table for customers in the Open Data Contract Standard, with a layer prefix, an owning team, descriptions and a primary key:

apiVersion: v3.2.0
kind: DataContract
id: slv_customer
version: 1.0.0
status: active
team:
  name: Customer data
schema:
  - name: slv_customer
    description: One row per customer, combined from the web shop and customer service.
    properties:
      - name: customer_id
        logicalType: string
        primaryKey: true
        description: The customer number from the web shop.
      - name: is_active
        logicalType: boolean
        description: True if the customer is active. Calculated in the nightly load.

Everything a standard says about this table can be checked here: the name has the right prefix, the key is called customer_id, the boolean starts with is_, and both the table and the columns have a description and an owning team.

Why the standard should be enforced automatically

Imagine the standard is a document. It gets written, shared and read by the people who were there when it was made. Then three things happen.

Deviations creep in. A new team member hasn’t read the document and creates customer_no. Another team is short on time and skips the description. None of the deviations is big, but after a year there are as many tables breaking the standard as following it.

Review comments get forgotten. When the standard is only enforced in review, it depends on the reviewer remembering every rule and bothering to write the same comment for the tenth time. The comment “should be called customer_id” gets noted as a follow-up and never done, or it doesn’t get written because the rest of the change mattered more.

The same term gets several names. When names aren’t checked, the customer ends up with three names in three tables, and nobody knows whether customer_id, customer_no and user_id are the same thing. What disappears is exactly what Kimball calls conformed dimensions: the same thing having the same name and the same contents everywhere.

The fix is to turn the rules into checks that run every time a table is created or changed, instead of comments that depend on the reader’s memory. Prefixes, suffixes, a required owner and a required description are easy to check by machine. Review can then focus on what takes judgment: whether the table has the right grain, and whether the definition is the one the business actually uses.

Where to start

You don’t need a twenty-page standard. Write down the decisions in this article for the web shop’s three tables, slv_customer, slv_order and slv_product: a prefix per layer, key names, suffixes for dates, timestamps and booleans, required system columns, and that every table must have an owner and a description. Then turn the rules into checks before you ask anyone to follow them. A standard enforced automatically from the first table is easier to keep than one introduced after the fact.

Sources