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) );
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!