Dimensional Modeling - Table Naming Convention Policy

📌 Purpose

This document defines a standardized naming convention for all database tables used in the data warehouse or data mart. The goal is to improve clarity, consistency, and maintainability across teams and tools.

---

🔠 General Guidelines

1. Use lowercase letters with underscores (_) as word separators.

2. Avoid spaces, special characters, and camelCase.

3. Keep names descriptive but concise.

4. Follow prefix-based naming to identify table types.

5. Use singular nouns for table names (e.g., dim_customer, not dim_customers).

---

🧩 Prefix-Based Table Types

Table Type	Prefix	Description
Dimension Table	dim_	Stores descriptive attributes; used for filtering, grouping, and slicing.
Fact Table	fact_	Stores measurable metrics/events (facts); typically has foreign keys.
Bridge Table	brg_	Resolves many-to-many relationships between dimension tables.
Staging Table	stg_	Holds raw data during the ETL process.
Snapshot Table	snap_	Stores time-based snapshots of fact/dimension data.
Aggregate Table	agg_	Stores pre-aggregated or summarized data for performance.
Junk Dimension	dim_junk_	Combines miscellaneous low-cardinality flags or indicators.
Mini-Dimension	dim_mini_	Isolates rapidly changing attributes from core dimensions.
Audit / Log Table	log_ or audit_	Stores ETL process metadata or data change history.

 

---

📚 Naming Examples

Table Purpose	Table Name
Customer Dimension	dim_customer
Sales Fact	fact_sales
Customer-Segment Bridge	brg_customer_segment
Raw Order Data	stg_order_raw
Monthly Inventory Snapshot	snap_inventory_monthly
Aggregated Sales by Region	agg_sales_region_monthly
Miscellaneous Flags	dim_junk_customer_flags
Loyalty Mini-Dimension	dim_mini_loyalty
ETL Job Logs	log_etl_job_execution

 

---

🔁 Other Conventions

Table Columns

• Primary Key: table_name_id (e.g., customer_id)

• Foreign Key: Matches referenced table’s PK (e.g., product_id)

• Timestamps: created_at, updated_at, loaded_at

Suffixes (optional)

• *_hist: For historical tracking (e.g., fact_sales_hist)

• *_tmp: For temporary/working tables (e.g., stg_sales_tmp)

---

✅ Best Practices

• Define table naming rules as part of your ETL/data modeling process.

• Store this policy in your data catalog or team documentation.

• Enforce naming conventions in CI/CD pipelines or code reviews.

• Review and update naming conventions as your architecture evolves.