ADR 009: Naming Conventions - Semantic Prefixes (No Redundancy)

Date: 2026-01-05 Status: ✅ Accepted Deciders: Data Engineering Team

---

Context

Need consistent naming convention across all database layers that: 1. Avoids redundancy (e.g., SILVER.SILVER_* or SILVER.WH_*) 2. Indicates model type (what it is, not where it lives) 3. Follows industry standards (dbt Labs, GitLab, Snowflake) 4. Is self-documenting (users immediately understand purpose)

Problem with current naming: - ❌ SILVER.SILVER_WARBYPARKER_CONTACT_TIMESTAMPS - redundant prefix - ❌ SILVER.WH_CONTACT_TIMESTAMPS - WH (warehouse) just means Silver - ❌ GOLD.DM_SCORECARD - DM (data mart) just means Gold

Key principle: Schema name already indicates layer - don't repeat it in table name.

---

Decision

Use semantic prefixes that indicate model type, not layer:

BRONZE Layer

Naming: {SOURCE}_{OBJECT}

No additional prefix needed - source system name acts as prefix

Examples:

BRONZE.GLADLY_CONTACT_TIMESTAMPS
BRONZE.GLADLY_CONVERSATION_TIMESTAMPS
BRONZE.SPROUT_MESSAGES
BRONZE.GSPREAD_INBOX_GLOSSARY_RAW

Rationale: Source name (GLADLY, SPROUT, GSPREAD) already provides context.

---

SILVER Layer

Naming: {OBJECT} (no prefix)

Examples:

SILVER.CONTACT_TIMESTAMPS
SILVER.CONVERSATION_TIMESTAMPS
SILVER.INBOX_GLOSSARY
SILVER.QUEUE_GLOSSARY

Rationale: - Silver is 1:1 with Bronze (same grain, just typed/cleaned) - Schema name SILVER already indicates it's historical preservation - No need for additional prefix

Alternative considered: base_{object} - Rejected as unnecessary.

---

GOLD Layer (Four Types)

Gold has multiple model types that need differentiation:

Type 1: Facts (fct_ prefix)

Naming: fct_{object} (plural)

Purpose: Enriched granular data (same grain as Silver but with JOINs)

Examples:

GOLD.FCT_CONTACTS              -- Contact-level facts
GOLD.FCT_CONVERSATIONS         -- Conversation-level facts
GOLD.FCT_MESSAGES              -- Message-level facts

---

Type 2: Dimensions (dim_ prefix)

Naming: dim_{entity} (singular)

Purpose: Current state entities and reference data

Examples:

GOLD.DIM_AGENT                 -- Current agent roster
GOLD.DIM_INBOX                 -- Current inbox configuration
GOLD.DIM_QUEUE                 -- Current queue definitions

---

Type 3: Aggregates (agg_ prefix)

Naming: agg_{subject}_{grain}

Purpose: Pre-aggregated base metrics (building blocks for reports)

Examples:

GOLD.AGG_AGENT_DAILY           -- Agent metrics by day
GOLD.AGG_AGENT_HOURLY          -- Agent metrics by hour
GOLD.AGG_CHANNEL_DAILY         -- Channel metrics by day
GOLD.AGG_QUEUE_DAILY           -- Queue performance by day
GOLD.AGG_EXECUTIVE_WEEKLY      -- Executive summary by week

---

Type 4: Reports (rpt_ prefix)

Naming: rpt_{purpose} or rpt_{consumer}_{purpose}

Purpose: End-user consumption layer (views)

Examples:

GOLD.RPT_AGENT_DASHBOARD       -- Tableau agent dashboard
GOLD.RPT_AGENT_AD_HOC          -- Ad-hoc analyst exploration
GOLD.RPT_EXECUTIVE_SUMMARY     -- Executive summary view
GOLD.RPT_MANAGER_SCORECARD     -- Manager scorecard
GOLD.RPT_QA_ANALYSIS           -- QA team analysis

---

Complete Convention Summary

Layer	Prefix	Pattern	Example
BRONZE	{SOURCE}_	Source system name	GLADLY_CONTACTS
SILVER	None	Object name	CONTACT_TIMESTAMPS
GOLD Facts	fct_	Fact tables	fct_contacts
GOLD Dims	dim_	Dimensions	dim_agent
GOLD Aggs	agg_	Aggregates	agg_agent_daily
GOLD Reports	rpt_	Reports/views	rpt_agent_dashboard

Consistency: All Gold prefixes are 3-4 characters (fct_, dim_, agg_, rpt_)

---

Rationale

Why semantic prefixes?

✅ Self-documenting:

SELECT * FROM GOLD.FCT_CONTACTS;
-- "fct_" → Immediately know it's a fact table (granular, enriched)

SELECT * FROM GOLD.AGG_AGENT_DAILY;
-- "agg_" → Immediately know it's aggregated metrics

SELECT * FROM GOLD.RPT_AGENT_DASHBOARD;
-- "rpt_" → Immediately know it's a report for consumption

✅ Industry standard: - dbt Labs uses fct_, dim_, agg_ - GitLab dbt project uses fct_, dim_, agg_ - Kimball methodology uses FACT_, DIM_, AGG_

✅ Easy filtering:

-- Show all fact tables
SHOW TABLES IN WBP_DB.GOLD LIKE 'FCT_%';

-- Show all aggregates
SHOW TABLES IN WBP_DB.GOLD LIKE 'AGG_%';

-- Show all reports
SHOW TABLES IN WBP_DB.GOLD LIKE 'RPT_%';

✅ Tableau organization:

Data Sources:
├── Facts (FCT_*)
│   ├── FCT_CONTACTS
│   └── FCT_CONVERSATIONS
├── Dimensions (DIM_*)
│   ├── DIM_AGENT
│   └── DIM_INBOX
├── Metrics (AGG_*)
│   ├── AGG_AGENT_DAILY
│   └── AGG_CHANNEL_DAILY
└── Reports (RPT_*)
    ├── RPT_AGENT_DASHBOARD
    └── RPT_EXECUTIVE_SUMMARY

Why NOT redundant prefixes?

❌ Redundant: - SILVER.SILVER_* - Schema name already says "SILVER" - SILVER.WH_* - WH (warehouse) is just another word for SILVER - GOLD.DM_* - DM (data mart) is just another word for GOLD

✅ Semantic prefixes add information: - GOLD.FCT_* - Tells you it's a FACT (granular) - GOLD.AGG_* - Tells you it's AGGREGATED (rolled up) - GOLD.RPT_* - Tells you it's a REPORT (consumption layer)

Why rpt_ instead of v_?

Considered alternatives:

Option	Pros	Cons
v_	Traditional database naming	Only 1 char (inconsistent with fct_, dim_, agg_)
rpt_	3-4 chars (consistent), business-friendly	None
report_	Most explicit	Too long (7 chars)

Decision: Use rpt_ for consistency with other prefixes.

---

Consequences

Positive

✅ No redundancy: Schema name indicates layer, prefix indicates type

✅ Self-documenting: Users immediately understand what each table is

✅ Industry-aligned: Follows dbt Labs and GitLab patterns

✅ Tool-friendly: Easy to filter in Snowflake, Tableau, dbt docs

✅ Onboarding: New team members already familiar with pattern

Negative

⚠️ Migration needed: Must rename existing tables

Mitigation: Create views for backward compatibility during transition.

---

Implementation

Phase 1: New tables use new convention

All new dbt models follow this convention immediately.

Phase 2: Rename existing tables

-- Example migration
ALTER TABLE GOLD.SILVER_WARBYPARKER_CONTACT_TIMESTAMPS
  RENAME TO GOLD.CONTACT_TIMESTAMPS;

-- Or create views for backward compatibility
CREATE OR REPLACE VIEW GOLD.SILVER_WARBYPARKER_CONTACT_TIMESTAMPS AS
SELECT * FROM SILVER.CONTACT_TIMESTAMPS;

Phase 3: Update Tableau connections

Point Tableau workbooks to new table names.

Phase 4: Deprecate old names

After 30-day grace period, drop backward-compatibility views.

---

dbt Configuration

# dbt_project.yml
models:
  xo_data:
    silver:
      # No prefix needed
      +schema: silver
      +tags: ['silver']

    gold:
      facts:
        # Tables named fct_*
        +materialized: table
        +schema: gold
        +tags: ['gold', 'facts']

      dimensions:
        # Tables named dim_*
        +materialized: table
        +schema: gold
        +tags: ['gold', 'dimensions']

      aggregates:
        # Tables named agg_*
        +materialized: table
        +schema: gold
        +tags: ['gold', 'aggregates']

      reports:
        # Views named rpt_*
        +materialized: view
        +schema: gold
        +tags: ['gold', 'reports']

Model file naming:

models/
├── silver/
│   └── warbyparker/
│       ├── contact_timestamps.sql          # → SILVER.CONTACT_TIMESTAMPS
│       └── conversation_timestamps.sql
│
└── gold/
    └── warbyparker/
        ├── facts/
        │   ├── fct_contacts.sql            # → GOLD.FCT_CONTACTS
        │   └── fct_conversations.sql
        ├── dimensions/
        │   ├── dim_agent.sql               # → GOLD.DIM_AGENT
        │   └── dim_inbox.sql
        ├── aggregates/
        │   ├── agg_agent_daily.sql         # → GOLD.AGG_AGENT_DAILY
        │   └── agg_channel_daily.sql
        └── reports/
            ├── rpt_agent_dashboard.sql     # → GOLD.RPT_AGENT_DASHBOARD
            └── rpt_executive_summary.sql

---

Examples

Complete table structure for Warby Parker:

-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- BRONZE (source prefix built-in)
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
WBP_DB.BRONZE.GLADLY_CONTACT_TIMESTAMPS
WBP_DB.BRONZE.GLADLY_CONVERSATION_TIMESTAMPS
WBP_DB.BRONZE.GSPREAD_INBOX_GLOSSARY_RAW

-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- SILVER (no prefix - clean and simple)
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
WBP_DB.SILVER.CONTACT_TIMESTAMPS
WBP_DB.SILVER.CONVERSATION_TIMESTAMPS
WBP_DB.SILVER.INBOX_GLOSSARY
WBP_DB.SILVER.QUEUE_GLOSSARY

-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- GOLD (semantic prefixes by type)
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- Facts (fct_ = enriched granular data)
WBP_DB.GOLD.FCT_CONTACTS
WBP_DB.GOLD.FCT_CONVERSATIONS
WBP_DB.GOLD.FCT_MESSAGES

-- Dimensions (dim_ = current state entities)
WBP_DB.GOLD.DIM_AGENT
WBP_DB.GOLD.DIM_INBOX
WBP_DB.GOLD.DIM_QUEUE

-- Aggregates (agg_ = pre-calculated metrics)
WBP_DB.GOLD.AGG_AGENT_DAILY
WBP_DB.GOLD.AGG_AGENT_HOURLY
WBP_DB.GOLD.AGG_CHANNEL_DAILY
WBP_DB.GOLD.AGG_EXECUTIVE_WEEKLY

-- Reports (rpt_ = consumption views)
WBP_DB.GOLD.RPT_AGENT_DASHBOARD
WBP_DB.GOLD.RPT_AGENT_AD_HOC
WBP_DB.GOLD.RPT_EXECUTIVE_SUMMARY
WBP_DB.GOLD.RPT_MANAGER_SCORECARD

---

Related Decisions

• ADR 008: Gold layer architecture (facts, dimensions, aggregates, reports)
• ADR 010: Reference data placement (rosters, glossaries)

---

References

• dbt Style Guide
• GitLab Analytics dbt Project
• Snowflake Best Practices documentation
• "The Data Warehouse Toolkit" by Ralph Kimball