Define The Naming Conventions

Editorial

Introduction

Before we write a single line of SQL, we need to establish our naming conventions. Naming conventions are a set of rules that define how we name everything in our data warehouse — tables, columns, schemas, stored procedures, and more.

Why does this matter? Because a data warehouse without consistent naming is like a library without a cataloging system. You might find what you need eventually, but it will take much longer and cause a lot of frustration. Good naming conventions make your project readable, predictable, and maintainable.

In this lesson, we will define every naming rule for our project and see concrete examples of each one.

Why Naming Conventions Matter

Imagine you are working on a project and you see these table names:

CustData_NEW
tbl_Products2
SALES_FINAL_v3
customer_information

Can you tell which tables belong to which layer? Which ones are current? What source system they came from? Probably not. That is the problem inconsistent naming creates.

Now imagine you see these table names instead:

bronze.crm_cust_info
silver.crm_prd_info
gold.dim_customers
gold.fact_sales

Instantly, you can tell which layer each table belongs to, what source system it came from, and what entity it represents. That is the power of good naming conventions.

Good naming conventions provide these benefits:

Readability — Anyone can understand the purpose of a table or column at a glance.
Consistency — Every object follows the same pattern, reducing confusion.
Discoverability — You can find what you need quickly without searching through documentation.
Maintainability — When you return to the project months later, everything still makes sense.
Collaboration — Team members can work together without disagreements about how to name things.

General Naming Rules

These are the foundational rules that apply to everything in our data warehouse — every table, every column, every schema, every stored procedure.

Rule	Description	Good Example	Bad Example
Use snake_case	Separate words with underscores	`cust_info`	`CustInfo`, `custInfo`
All lowercase	Never use uppercase letters	`sales_details`	`Sales_Details`, `SALES`
English only	All names must be in English	`customer_name`	`nombre_cliente`
Avoid reserved words	Do not use SQL keywords as names	`order_date`	`date`, `select`, `table`

Why snake_case? It is the most readable format for SQL objects. Words are clearly separated by underscores, and lowercase letters make everything uniform. Unlike camelCase or PascalCase, snake_case does not depend on capitalization to separate words — which is important because SQL Server is often case-insensitive.

Why avoid reserved words? If you name a column date or order, SQL Server might get confused between your column name and the SQL keyword. This leads to errors or forces you to wrap names in square brackets [date], which is messy and error-prone.

Schema Naming

Schemas in our project directly correspond to the layers of the Medallion Architecture. Each layer gets its own schema, which acts as a logical container for all the tables in that layer.

Schema	Purpose	Description
`bronze`	Raw data layer	Stores data exactly as it arrives from source systems
`silver`	Cleansed data layer	Stores cleaned, standardized, and validated data
`gold`	Business-ready layer	Stores the final dimensional model for analytics

Using schemas this way gives us a clear, visual separation between layers. When you see bronze.crm_cust_info, you immediately know you are looking at raw CRM customer data. When you see gold.dim_customers, you know it is the final, analytics-ready customer dimension.

-- Creating our three schemas
CREATE SCHEMA bronze;
GO

CREATE SCHEMA silver;
GO

CREATE SCHEMA gold;
GO

Bronze Layer Table Naming

Bronze tables use the pattern:

<sourcesystem>_<entity>

The source system prefix tells you where the data came from, and the entity name tells you what the data represents. Since Bronze is a raw copy of the source, we keep the original entity names from the source systems.

Source System	Source Table	Bronze Table Name	Full Qualified Name
CRM	cust_info	`crm_cust_info`	`bronze.crm_cust_info`
CRM	prd_info	`crm_prd_info`	`bronze.crm_prd_info`
CRM	sales_details	`crm_sales_details`	`bronze.crm_sales_details`
ERP	cust_az12	`erp_cust_az12`	`bronze.erp_cust_az12`
ERP	loc_a101	`erp_loc_a101`	`bronze.erp_loc_a101`
ERP	px_cat_g1v2	`erp_px_cat_g1v2`	`bronze.erp_px_cat_g1v2`

Notice how the ERP table names look a bit cryptic (cust_az12, loc_a101, px_cat_g1v2). That is perfectly fine for the Bronze layer — we keep the original names as-is, and Silver keeps them too so the lineage stays clear. The Gold layer is where names become business-friendly.

Silver Layer Table Naming

Silver tables use the same pattern as Bronze:

<sourcesystem>_<entity>

This makes it easy to trace data lineage between layers. A Silver table has the same name as its corresponding Bronze table — the difference is the schema it lives in and the fact that the data has been cleaned.

Bronze Source	Silver Table Name	Full Qualified Name
`bronze.crm_cust_info`	`crm_cust_info`	`silver.crm_cust_info`
`bronze.crm_prd_info`	`crm_prd_info`	`silver.crm_prd_info`
`bronze.crm_sales_details`	`crm_sales_details`	`silver.crm_sales_details`
`bronze.erp_cust_az12`	`erp_cust_az12`	`silver.erp_cust_az12`
`bronze.erp_loc_a101`	`erp_loc_a101`	`silver.erp_loc_a101`
`bronze.erp_px_cat_g1v2`	`erp_px_cat_g1v2`	`silver.erp_px_cat_g1v2`

Why keep the same name? It makes data lineage crystal clear. If someone asks "where does silver.crm_cust_info get its data?" — the answer is obviously bronze.crm_cust_info. No guessing, no lookup tables needed.

Gold Layer Table Naming

Gold tables use a different pattern because they represent the business model, not the source systems:

<category>_<entity>

The category tells you what type of table it is in the dimensional model, and the entity is a clear, business-friendly name.

We use two category prefixes:

Prefix	Type	Purpose	Examples
`dim_`	Dimension	Descriptive attributes (who, what, where)	`dim_customers`, `dim_products`
`fact_`	Fact	Measurable events and transactions	`fact_sales`

Gold Table	Full Qualified Name	Source Tables
`dim_customers`	`gold.dim_customers`	`silver.crm_cust_info` + `silver.erp_cust_az12` + `silver.erp_loc_a101`
`dim_products`	`gold.dim_products`	`silver.crm_prd_info` + `silver.erp_px_cat_g1v2`
`fact_sales`	`gold.fact_sales`	`silver.crm_sales_details` + dimension lookups

Notice how Gold table names are clean and business-friendly. There is no mention of CRM or ERP — those details are hidden away. A business user looking at gold.dim_customers does not need to know that the data came from two different source systems. They just see a clean, unified customer dimension.

Column Naming Conventions

Surrogate Keys

Surrogate keys are artificial identifiers that we create in the Gold layer to uniquely identify each row in a dimension table. They follow this pattern:

<entity>_key

Table	Surrogate Key Column
`gold.dim_customers`	`customer_key`
`gold.dim_products`	`product_key`

Surrogate keys are typically integers that are automatically generated. They replace the original business keys (like customer IDs from the source systems) as the primary way to link dimension tables to fact tables.

Why use surrogate keys? Source system keys can be unreliable — they might change, have duplicates across systems, or use unfriendly formats. Surrogate keys give us a clean, consistent, and reliable way to join tables.

Technical / Metadata Columns

Technical columns are columns that the data warehouse system adds for tracking and auditing purposes. They are not part of the original source data. They follow this pattern:

dwh_<descriptor>

The dwh_ prefix makes it immediately clear that these columns are system-generated, not from the source.

Column Name	Purpose	Example Value
`dwh_create_date`	When the row was loaded into the Silver table (filled by `DEFAULT GETDATE()`)	`2025-01-15 08:30:00`

Every Silver table gets this one column. Bronze tables have none — they mirror the source files exactly.

Stored Procedure Naming

Stored procedures in our project follow a simple and descriptive pattern:

load_<layer>, created in that layer's schema

Bronze and Silver each have one main stored procedure that loads all of that layer's tables. Gold has none — its objects are views, which read the Silver tables whenever they are queried.

Stored Procedure	Purpose	What It Does
`bronze.load_bronze`	Load raw data	Bulk-loads data from CSV source files into all Bronze tables
`silver.load_silver`	Transform data	Cleanses and standardizes data from Bronze into all Silver tables

This pattern is clean and self-documenting. When you see silver.load_silver, you know exactly what it does — it loads the Silver layer. No ambiguity.

-- Example: Creating a stored procedure with proper naming
CREATE OR ALTER PROCEDURE bronze.load_bronze
AS
BEGIN
    -- Load all bronze tables from source files
    PRINT 'Loading Bronze Layer...';
    
    -- Truncate and reload each table
    TRUNCATE TABLE bronze.crm_cust_info;
    -- BULK INSERT from the CSV file here...
    
    PRINT 'Bronze Layer Load Complete.';
END;
GO

Complete Naming Convention Reference

Here is the complete reference table for all naming conventions in our project. Bookmark this — you will refer to it throughout the entire build.

Category	Pattern	Examples
General	snake_case, lowercase, English	`customer_name`, `order_date`
Schemas	Layer name	`bronze`, `silver`, `gold`
Bronze Tables	`<sourcesystem>_<entity>`	`crm_cust_info`, `erp_loc_a101`
Silver Tables	`<sourcesystem>_<entity>`	`crm_cust_info`, `erp_cust_az12`
Gold Tables	`<category>_<entity>`	`dim_customers`, `dim_products`, `fact_sales`
Surrogate Keys	`<entity>_key`	`customer_key`, `product_key`
Technical Columns	`dwh_<descriptor>`	`dwh_create_date`
Stored Procedures	`load_<layer>`, in the layer's schema	`bronze.load_bronze`, `silver.load_silver`

Common Naming Mistakes to Avoid

Here are some common mistakes that violate our naming conventions, along with the correct alternatives:

❌ Wrong	✅ Correct	Rule Violated
`CRM_CustInfo`	`crm_cust_info`	Must be lowercase snake_case
`tbl_customers`	`dim_customers`	Do not use generic prefixes like `tbl_`
`bronze.CustomerData`	`bronze.crm_cust_info`	Use source system prefix, not generic names
`gold.sales`	`gold.fact_sales`	Gold tables need dim_ or fact_ prefix
`sp_LoadBronze`	`bronze.load_bronze`	Use snake_case, no `sp_` prefix
`ID`	`customer_key`	Use descriptive _key suffix for surrogate keys
`created_at`	`dwh_create_date`	Technical columns need dwh_ prefix
`SELECT` (as column)	`selection_type`	Never use SQL reserved words

Summary

In this lesson, we established the complete set of naming conventions for our data warehouse project. These rules will keep our project clean, consistent, and professional throughout the entire build.

Here is a quick recap of the key rules:

General: Always use snake_case, lowercase, English, and avoid SQL reserved words.
Schemas: Match the Medallion Architecture layers — bronze, silver, gold.
Bronze & Silver tables: Use <sourcesystem>_<entity> to preserve lineage.
Gold tables: Use <category>_<entity> with dim_ for dimensions and fact_ for facts.
Surrogate keys: Use <entity>_key for artificial identifiers in the Gold layer.
Technical columns: Use dwh_ prefix for system-generated metadata.
Stored procedures: Use load_<layer> in the layer's schema — bronze.load_bronze, silver.load_silver.

Stick to these conventions for every single object you create. Consistency is not optional — it is what separates a professional data warehouse from a messy collection of tables.