Data Warehouse - Naming Conventions

I was reading a lot of news in the last few days about Elon Musk and Grimes revealing their baby name X Æ A-12 ... And it got me thinking about the importance of the proper naming. Especially in your data repositories. So I got my trusty flashlight and went deep into the archives to find the SageData Data Warehouse entity naming conventions that I had put together about a million years ago based on the work of Ralph Kimball.

So, without much ado, I present to you our... (drum roll)...

Table and Entity Naming Convention

SageData uses the following naming convention within the DWH production schema (dwh):

• Use singular forms rather than plural (“user” not “users”).

• Keep the entity names relatively short. Abbreviate any words longer than 9 characters ("conn" not "connection") but minimum 3 letters.

• Prepend entity name with the entity category definition:

  • dim_ for dimensions

  • fact_ for fact tables

  • view_ for views

  • agg_ for aggregations

  • brt_ for bridge table that links two tables together

  • tableau_ for tables that are specific data sources for tableau

  • rep_ reporting tables or tables that have been created with the only purpose to provide data for a report or small set of reports

  • func_ for functions or procedures that are created in the database

• Append column names with data type, where possible

  • _ts - columns that contain timestamps

  • _dt - columns that contain dates

  • _id - unique identifier, such as user_id. Only use this in tables, where id is not the main key. For example, id of the user in table dim_user will be named “id” but in table fact_transactions the same column will be called “user_id”

Dimension column names can repeat parts of table names, so a dimension table containing countries can have the following DDL, where column name country is repeated from table name dim_country:

CREATE TABLE dwh.dim_country(
 id INT
 ,country varchar(124)
);

Update:

We actually started adding _name where possible

CREATE TABLE dwh.dim_country(
id INT
,country_name varchar(124)
);

• Separate words in entity name with “_” (example: dim_user)

• Avoid spaces in object names

• Avoid using SQL and database engine-reserved keywords as identifiers (i.e., names of databases, tables, indexes, columns, aliases, views, stored procedures, partitions, tablespaces, and other objects.)

If such must be used, please use it with an underscore, example:

SELECT COUNT(*) AS count_ FROM my_table

That is all folks! Learn it, live it, love it!