Naming Conventions And Model Maintainability

Who this is for

Analytics Engineers, BI Developers, and anyone building dimensional star schemas who want models that are easy to read, change, and trust.

Prerequisites

• Basic SQL (SELECT, JOIN, GROUP BY)
• Familiarity with dimensional terms: fact, dimension, grain
• Comfort with your warehouse schemas/environments

Why this matters

Real tasks you will do:

• Standardize table and column names so teammates can join facts to dimensions without guesswork.
• Refactor old models while keeping dashboards working.
• Add new attributes and versions without breaking downstream users.

Good conventions prevent broken joins, duplicate metrics, and brittle pipelines. They make reviews faster and onboarding smoother.

Concept explained simply

Naming conventions are the house style for your warehouse: how you name schemas, tables, columns, and files. Maintainability is how easily humans can change, test, and understand models over time.

Mental model

Think of your warehouse like a library. Conventions are the cataloging rules. If every book is labeled consistently, anyone can find and reference the right one quickly. Maintainability is the ability to add new books, reorganize shelves, and retire old editions without losing readers.

Core conventions for dimensional star models

Schemas and layers

• raw: as-ingested source data
• staging: lightly cleaned and typed, table names start with stg_
• analytics: dimensional models, dim_ and fct_ tables
• marts/reporting: curated outputs, often rpt_ tables or views

Tables and files

• Fact tables: fct_{process} (e.g., fct_orders, fct_sessions). Grain is the process event.
• Dimension tables: dim_{entity} (e.g., dim_customer, dim_product). One row per entity.
• Staging tables: stg_{source}_{entity} (e.g., stg_stripe_charge)

Columns

• Primary keys: entity_id (customer_id). Use integers where possible.
• Foreign keys: same name as the dimension PK (customer_id) for easy joins.
• Dates vs timestamps: order_date (DATE), created_at (TIMESTAMP UTC).
• Booleans: is_, has_ prefixes.
• Units and semantics in names if ambiguity exists: total_amount_usd, weight_kg.

Versioning and deprecation

• Additive, non-breaking changes: add new columns, keep names stable.
• Breaking changes: create dim_customer_v2 or fct_orders_v2; dual-run both for a defined window; communicate and deprecate.
• Deprecation plan: announce, freeze old table (no new features), sunset date, migration guide.

Documentation and lightweight tests

• One-sentence table purpose, grain, and key columns in a doc block or model header.
• Column descriptions for any non-obvious field.
• Basic tests: primary key uniqueness, not null for join keys, referential integrity checks.

Worked examples

Example 1 — Cleaning names

Before:

CustomerDim (PK: CustID, cols: CreatedDate, ActiveFlag)
Orders (PK: id, FK: Cid, cols: orderDate, Total)

After:

dim_customer (customer_id, created_at, is_active)
fct_orders (order_id, customer_id, order_date, total_amount_usd)

Notes: snake_case, aligned key names (customer_id), clear units, timestamp suffix.

Example 2 — Readable joins

SELECT
  o.order_id,
  o.order_date,
  c.customer_id,
  c.is_active
FROM analytics.fct_orders o
LEFT JOIN analytics.dim_customer c USING (customer_id);

Using USING(customer_id) works because key names match across tables.

Example 3 — Breaking change with versioning

You decide to replace natural product_id with a surrogate key and add SCD attributes.

• Create dim_product_v2 with product_id as surrogate and new attributes.
• Add product_id mapping to fct_orders. Run both dim_product and dim_product_v2 for 30 days.
• Announce sunset of dim_product, migrate dashboards, then drop the old table.

Exercises

These mirror the interactive tasks below. Do them here first, then open the exercise inputs and solutions. Tip: write answers in a scratchpad, then compare.

Exercise 1 — Refactor to conventions

Given:

Tables:
- CustomerDIM (PK: custId, cols: signupDate, active)
- ORDERS_FACT (PK: id, FK: custID, cols: orderDT, total)
- stg_payments (cols: chargeID, amount)

Tasks:

• Rename tables and columns to match the conventions above.
• Provide a sample join query between orders and customers.
• Indicate data types for dates/timestamps.

Exercise 2 — Versioning and deprecation plan

Breaking change: You want to split total into subtotal_usd, tax_usd, shipping_usd in fct_orders.

• Propose table/column changes.
• Write a short, bullet-point deprecation plan (timeline, comms, migration notes).

Self-check checklist

• All identifiers are snake_case.
• Fact tables start with fct_, dimensions with dim_, staging with stg_.
• Primary/foreign keys share exact names (e.g., customer_id) across tables.
• Date vs timestamp suffixes are correct (_date vs _at) and timestamps are UTC.
• Booleans use is_/has_ prefixes.
• Units are explicit where needed (e.g., _usd, _kg, _pct).
• Breaking changes are versioned (_v2) with a deprecation window.
• Each table states grain and key in a short doc block.

Common mistakes and how to catch them

• Mixing cases and styles: customerId vs customer_id. Fix by running naming linters or code reviews focused on identifiers.
• PK/FK name mismatches: cust_id vs customer_id. Catch with referential integrity checks and consistent naming during reviews.
• Encoding materialization in names: orders_view. Keep materialization in config, not names.
• Ambiguous time fields: created vs created_at. Enforce suffix rules and add descriptions.
• Hidden breaking changes: renaming columns without a plan. Use _v2 versions and communicate deprecation timelines.
• Unclear units: total vs total_amount_usd. Add units to names and docs.

Learning path

1. Review dimensional modeling basics (facts, dimensions, grain).
2. Adopt a naming style guide (this lesson).
3. Add lightweight tests for keys and nulls.
4. Introduce versioning and deprecation practices.
5. Expand to SCD strategies and data contracts.

Practical projects

• Refactor a small domain (customers and orders) to dim_/fct_ with consistent keys and timestamps.
• Write a 1-page style guide and apply it to 10 existing tables.
• Implement a v2 change in a safe sandbox, run both versions, and draft a deprecation notice.

Mini challenge

Rename these to match the conventions: UserDim(userID, created, ActiveFlag), FACT_Login(id, userId, loginTime), stg_web(clickID, ts).

dim_user (user_id, created_at, is_active)
fct_login (login_id, user_id, login_at)
stg_web (click_id, occurred_at)

Next steps

• Apply the checklist to one real model this week.
• Create a shared glossary of entities and key names.
• Schedule a 30-minute review with a teammate to validate your style guide.

Quick test

Everyone can take the quick test below. Sign in to save your progress and see it on your learning dashboard.