Naming Convention Best Practices - Industry Research¶

Date: 2026-01-05 Status: Research & Recommendation Sources: dbt Labs, Snowflake, GitLab dbt project, Fishtown Analytics, Kimball methodology

Key Principle: Prefixes Should Add Information, Not Repeat It¶

❌ Redundant (BAD):

SILVER.SILVER_CONTACT_EXPORT    -- "SILVER" is already in schema name
SILVER.WH_CONTACT_EXPORT        -- "WH" (warehouse) is just another word for SILVER
GOLD.DM_SCORECARD               -- "DM" (data mart) is just another word for GOLD

✅ Semantic (GOOD):

GOLD.REPORT_AGENT_KPIS          -- "REPORT" indicates it's aggregated for reporting
GOLD.ENRICHED_CONTACTS          -- "ENRICHED" indicates it has joins/enrichment
GOLD.BASE_CONTACTS              -- "BASE" indicates it's the foundational table

Principle: Prefix adds semantic meaning about what type of model it is, not where it lives.

Industry Best Practices Research¶

1. dbt Labs Official Recommendations¶

Source: dbt Style Guide

Their recommended prefixes:

Prefix	Layer	Meaning
`stg_`	Staging	One-to-one with source tables, light transformations
`int_`	Intermediate	Reusable logic, not exposed to end users
`fct_`	Marts	Fact tables (events, transactions)
`dim_`	Marts	Dimension tables (entities, reference data)

Key insight from dbt Labs:

"Use prefixes to indicate the type of model, not the layer. The folder structure indicates the layer."

Example from dbt Labs:

models/
├── staging/
│   ├── stg_customers.sql        -- Prefix indicates staging model
│   └── stg_orders.sql
├── intermediate/
│   ├── int_orders_pivoted.sql   -- Prefix indicates intermediate
│   └── int_customer_orders.sql
└── marts/
    ├── fct_orders.sql           -- Prefix indicates fact table
    ├── dim_customers.sql        -- Prefix indicates dimension
    └── dim_products.sql

Translation to our architecture: - Staging → Our SILVER layer - Intermediate → We skip this (no intermediate layer) - Marts → Our GOLD layer

2. GitLab's dbt Project (Large Production Example)¶

Source: GitLab Analytics dbt project

Their naming convention:

models/
├── legacy/
│   └── staging/
│       ├── sfdc/
│       │   ├── stg_sfdc_account.sql
│       │   └── stg_sfdc_opportunity.sql
├── common/
│   ├── facts/
│   │   ├── fct_event.sql
│   │   ├── fct_usage_ping.sql
│   │   └── fct_mrr.sql
│   ├── dimensions/
│   │   ├── dim_date.sql
│   │   ├── dim_user.sql
│   │   └── dim_namespace.sql
│   └── aggregates/
│       ├── agg_usage_metrics_monthly.sql
│       └── agg_revenue_daily.sql

Key patterns: - stg_ for staging models - fct_ for fact tables (granular events) - dim_ for dimension tables (entities) - agg_ for aggregated tables (rolled up metrics)

Insight: They use semantic prefixes to indicate grain and purpose, not layer.

3. Snowflake Best Practices¶

Source: Snowflake documentation & field guides

Snowflake recommendations: - Use descriptive names that indicate purpose - Avoid cryptic abbreviations - Use consistent patterns - Schema provides layer context, table name provides entity/purpose

Example from Snowflake customers:

-- Layer indicated by schema
ANALYTICS.FCT_SALES            -- Fact table
ANALYTICS.DIM_CUSTOMER         -- Dimension table
ANALYTICS.AGG_REVENUE_DAILY    -- Aggregate table
ANALYTICS.RPT_EXECUTIVE_DASHBOARD  -- Report/view

Pattern: {TYPE}_{ENTITY}_{GRAIN} (optional grain)

4. Fishtown Analytics (dbt Creators)¶

Source: dbt Discourse forums

Their philosophy:

"Prefixes should answer: What kind of model is this? Not: Where does it live?"

Recommended prefixes for marts:

Type	Prefix	Example	Purpose
Fact tables	`fct_`	`fct_orders`, `fct_events`	Immutable events/transactions
Dimension tables	`dim_`	`dim_customers`, `dim_products`	Entities/reference data
Aggregates	`agg_` or `rpt_`	`agg_sales_daily`, `rpt_revenue`	Pre-aggregated metrics

Key quote:

"Think about what your end users need to understand. 'fct_orders' tells them it's granular order data. 'orders' could be anything."

5. Kimball Methodology (Traditional Data Warehousing)¶

Source: "The Data Warehouse Toolkit" by Ralph Kimball

Classic Kimball prefixes: - FACT_ - Fact tables (measurements, metrics) - DIM_ - Dimension tables (descriptive attributes) - AGG_ - Aggregate tables (pre-summarized) - BRG_ - Bridge tables (many-to-many relationships)

Still relevant today: The semantic meaning behind fact vs dimension is useful.

Recommended Naming Convention for XO-Data¶

Principle: Use semantic prefixes that indicate model TYPE, not layer¶

BRONZE Layer¶

Schema: {CLIENT}_DB.BRONZE

Naming: {SOURCE}_{OBJECT}

No prefix needed - source name already provides context

Examples:

WBP_DB.BRONZE.GLADLY_CONTACT_TIMESTAMPS
WBP_DB.BRONZE.GLADLY_CONVERSATION_TIMESTAMPS
WBP_DB.BRONZE.SPROUT_MESSAGES
WBP_DB.BRONZE.GSPREAD_ROSTER_RAW

Rationale: Source system name (GLADLY, SPROUT, GSPREAD) serves as the prefix.

SILVER Layer¶

Schema: {CLIENT}_DB.SILVER

Naming: {OBJECT} (no prefix) OR base_{OBJECT}

Examples:

-- Option A: No prefix (my current recommendation)
WBP_DB.SILVER.CONTACT_TIMESTAMPS
WBP_DB.SILVER.CONVERSATION_TIMESTAMPS
WBP_DB.SILVER.INBOX_GLOSSARY

-- Option B: With base_ prefix (dbt-style)
WBP_DB.SILVER.BASE_CONTACT_TIMESTAMPS
WBP_DB.SILVER.BASE_CONVERSATION_TIMESTAMPS
WBP_DB.SILVER.BASE_INBOX_GLOSSARY

Rationale: - Silver is 1:1 with Bronze (same grain, just typed/cleaned) - Schema name already indicates layer - base_ prefix (if used) indicates "baseline historical table"

Recommendation: No prefix - keep it simple. Silver tables are foundational by definition.

GOLD Layer (THREE TYPES - Need Differentiation)¶

Schema: {CLIENT}_DB.GOLD

Challenge: Gold has three types of models that need clear differentiation.

Type 1: Fact Tables (Enriched grain-level data)¶

Naming: fct_{OBJECT} or {OBJECT} (plural)

Purpose: Granular fact tables with enrichment (same grain as Silver but with JOINs)

Examples:

-- With prefix (dbt-style)
GOLD.FCT_CONTACTS               -- Contact-level facts
GOLD.FCT_CONVERSATIONS          -- Conversation-level facts
GOLD.FCT_MESSAGES               -- Message-level facts

-- Without prefix (simpler)
GOLD.CONTACTS                   -- Contact facts
GOLD.CONVERSATIONS              -- Conversation facts
GOLD.MESSAGES                   -- Message facts

Characteristics: - Same grain as Silver (row per contact/conversation) - Enriched with employee metadata + glossaries - Filtered to relevant records (XO agents only) - This is the SOT for ad-hoc analysis

Type 2: Dimension Tables (Current state entities)¶

Naming: dim_{ENTITY} (singular)

Purpose: Current state of entities (SCD Type 1 or latest snapshot)

Examples:

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

Characteristics: - Slowly changing dimensions - Usually Type 1 (latest state) or Type 2 (historical) - Reference data for joining with facts

Type 3: Aggregate Tables (Metrics/KPIs)¶

Naming: agg_{SUBJECT}_{GRAIN} or rpt_{SUBJECT}_{GRAIN}

Purpose: Pre-aggregated metrics for reporting/dashboards

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

-- OR with "report" prefix
GOLD.RPT_AGENT_DAILY            -- Agent daily report
GOLD.RPT_EXECUTIVE_WEEKLY       -- Executive weekly report

Characteristics: - Aggregated from fact tables - Pre-calculated for performance - Standard KPI schema + client-specific columns

Type 4: Views (Consumption layer)¶

Naming: v_{PURPOSE} or just {PURPOSE} with VIEW materialization

Purpose: Tableau/analyst consumption views

Examples:

-- With v_ prefix (traditional)
GOLD.V_AGENT_DASHBOARD          -- Tableau dashboard
GOLD.V_AGENT_AD_HOC             -- Ad-hoc analysis
GOLD.V_EXECUTIVE_SUMMARY        -- Executive view

-- Without prefix (rely on materialization type)
GOLD.AGENT_DASHBOARD            -- VIEW (shown in Snowflake metadata)
GOLD.AGENT_AD_HOC               -- VIEW
GOLD.EXECUTIVE_SUMMARY          -- VIEW

Characteristics: - Always VIEW materialization (no storage) - JOINs with targets, variance calculations - Tableau-optimized

✅ Final Recommendation: Use Semantic Prefixes in Gold¶

Recommended Convention (Industry Standard)¶

Type	Prefix	Example	Materialization
Bronze	`{SOURCE}_`	`GLADLY_CONTACTS`	table
Silver	None	`CONTACT_TIMESTAMPS`	table/incremental
Gold Facts	`fct_`	`fct_contacts`	table/incremental
Gold Dims	`dim_`	`dim_agent`	table/incremental
Gold Aggs	`agg_` or `rpt_`	`agg_agent_daily`	table
Gold Views	`v_` or none	`v_agent_dashboard`	view

Applied to Your Use Case¶

-- 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 - foundational tables)
WBP_DB.SILVER.CONTACT_TIMESTAMPS
WBP_DB.SILVER.CONVERSATION_TIMESTAMPS
WBP_DB.SILVER.INBOX_GLOSSARY
WBP_DB.SILVER.QUEUE_GLOSSARY

-- GOLD FACTS (fct_ prefix - grain-level enriched data)
WBP_DB.GOLD.FCT_CONTACTS              -- Enriched contacts (SOT for analysts)
WBP_DB.GOLD.FCT_CONVERSATIONS         -- Enriched conversations
WBP_DB.GOLD.FCT_MESSAGES              -- Enriched messages

-- GOLD DIMENSIONS (dim_ prefix - current state entities)
WBP_DB.GOLD.DIM_AGENT                 -- Current agent roster
WBP_DB.GOLD.DIM_INBOX                 -- Current inbox config
WBP_DB.GOLD.DIM_QUEUE                 -- Current queue definitions

-- GOLD AGGREGATES (agg_ prefix - pre-aggregated metrics)
WBP_DB.GOLD.AGG_AGENT_DAILY           -- Agent KPIs by day
WBP_DB.GOLD.AGG_AGENT_HOURLY          -- Agent KPIs by hour
WBP_DB.GOLD.AGG_CHANNEL_DAILY         -- Channel metrics by day
WBP_DB.GOLD.AGG_EXECUTIVE_WEEKLY      -- Executive summary by week

-- GOLD VIEWS (v_ prefix - consumption layer)
WBP_DB.GOLD.V_AGENT_DASHBOARD         -- Tableau agent view
WBP_DB.GOLD.V_AGENT_AD_HOC            -- Ad-hoc analysis view
WBP_DB.GOLD.V_EXECUTIVE_SUMMARY       -- Executive view

Why This Convention Works¶

✅ Benefit 1: Self-Documenting¶

-- What is this table?
SELECT * FROM WBP_DB.GOLD.FCT_CONTACTS;
-- "fct_" → It's a fact table (granular, enriched)

SELECT * FROM WBP_DB.GOLD.AGG_AGENT_DAILY;
-- "agg_" → It's aggregated metrics

SELECT * FROM WBP_DB.GOLD.DIM_AGENT;
-- "dim_" → It's a dimension table (current state)

User immediately knows grain and purpose.

✅ Benefit 2: Consistent with Industry Standards¶

dbt Labs uses fct_, dim_, agg_
GitLab uses fct_, dim_, agg_
Kimball uses FACT_, DIM_, AGG_

New team members already understand the pattern.

✅ Benefit 3: Easy Filtering in Tools¶

-- 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 views
SHOW VIEWS IN WBP_DB.GOLD;

Tooling and queries can filter by prefix.

✅ Benefit 4: Tableau Data Source Organization¶

Tableau Data Sources:
├── 📊 Facts
│   ├── FCT_CONTACTS
│   ├── FCT_CONVERSATIONS
│   └── FCT_MESSAGES
├── 📐 Dimensions
│   ├── DIM_AGENT
│   ├── DIM_INBOX
│   └── DIM_QUEUE
└── 📈 Reports
    ├── AGG_AGENT_DAILY
    └── V_AGENT_DASHBOARD

Tableau users can easily find what they need.

✅ Benefit 5: Clear Lineage in dbt Docs¶

dbt automatically generates docs showing: - Table type (based on prefix) - Grain (rows per contact vs rows per agent-day) - Upstream dependencies

Alternative: Shorter Prefixes (Modern Style)¶

Some teams prefer shorter prefixes for readability:

Type	Traditional	Modern Short	Example
Fact	`fct_`	`f_`	`f_contacts`
Dimension	`dim_`	`d_`	`d_agent`
Aggregate	`agg_`	`a_` or `m_` (metrics)	`a_agent_daily`, `m_agent_daily`
View	`v_`	`v_`	`v_dashboard`

Example with short prefixes:

GOLD.F_CONTACTS              -- Fact
GOLD.D_AGENT                 -- Dimension
GOLD.M_AGENT_DAILY           -- Metrics (aggregate)
GOLD.V_AGENT_DASHBOARD       -- View

Trade-off: Shorter is cleaner, but less immediately obvious to new users.

Hybrid Approach (Recommended for XO-Data)¶

Use full prefixes for clarity (you have diverse clients, make it obvious):

-- BRONZE: Source name is the prefix
BRONZE.GLADLY_CONTACT_TIMESTAMPS
BRONZE.SPROUT_MESSAGES

-- SILVER: No prefix (foundational tables)
SILVER.CONTACT_TIMESTAMPS
SILVER.INBOX_GLOSSARY

-- GOLD: Semantic prefixes
GOLD.FCT_CONTACTS              -- Fact (enriched, granular)
GOLD.DIM_AGENT                 -- Dimension (current state)
GOLD.AGG_AGENT_DAILY           -- Aggregate (rolled up)
GOLD.V_AGENT_DASHBOARD         -- View (consumption)

Why full prefixes: - Multiple clients (consistency across clients is important) - Business users (non-technical folks need clarity) - Tableau (clear organization in data sources)

Special Case: When to Skip Prefixes¶

Dimensions that are really reference tables:

-- These could skip dim_ prefix (they're clearly reference data)
SILVER.INBOX_GLOSSARY          -- Not DIM_INBOX_GLOSSARY
SILVER.QUEUE_GLOSSARY          -- Not DIM_QUEUE_GLOSSARY
SILVER.SCORECARD_GLOSSARY      -- Not DIM_SCORECARD_GLOSSARY

Rationale: The word "GLOSSARY" already indicates it's reference data.

But if used in Gold as current state:

-- In Gold, use dim_ for consistency
GOLD.DIM_INBOX                 -- Current inbox config (from SILVER.INBOX_GLOSSARY)

Summary: Final Recommendation¶

✅ Adopt Industry-Standard Prefixes¶

Layer	Prefix Pattern	Example
BRONZE	`{SOURCE}_{OBJECT}`	`GLADLY_CONTACT_TIMESTAMPS`
SILVER	`{OBJECT}`	`CONTACT_TIMESTAMPS`
GOLD Facts	`fct_{OBJECT}`	`fct_contacts`
GOLD Dims	`dim_{ENTITY}`	`dim_agent`
GOLD Aggs	`agg_{SUBJECT}_{GRAIN}`	`agg_agent_daily`
GOLD Views	`v_{PURPOSE}`	`v_agent_dashboard`

Rationale: - ✅ Industry standard (dbt Labs, GitLab, Kimball) - ✅ Self-documenting (grain and purpose clear) - ✅ Not redundant (prefix adds semantic info) - ✅ Easy to filter and organize - ✅ Consistent across clients

This is the best practice that balances clarity with convention.

dbt Implementation Example¶

# models/gold/warbyparker/schema.yml
models:
  - name: fct_contacts
    description: "Enriched contact-level facts. Grain: one row per contact event."
    config:
      tags: ['gold', 'facts', 'warbyparker']

  - name: dim_agent
    description: "Current agent dimension. Grain: one row per active agent."
    config:
      tags: ['gold', 'dimensions', 'warbyparker']

  - name: agg_agent_daily
    description: "Agent KPIs by day. Grain: one row per agent per day."
    config:
      tags: ['gold', 'aggregates', 'warbyparker']

  - name: v_agent_dashboard
    description: "Tableau dashboard view with targets and variance."
    config:
      materialized: view
      tags: ['gold', 'views', 'warbyparker']

Migration Path¶

If you want to migrate from current naming:

-- Current (no prefixes)
GOLD.CONTACTS
GOLD.AGENT_KPIS_DAILY
GOLD.AGENT_DASHBOARD

-- New (with semantic prefixes)
GOLD.FCT_CONTACTS              -- Enriched fact table
GOLD.AGG_AGENT_DAILY           -- Aggregated metrics
GOLD.V_AGENT_DASHBOARD         -- View for consumption

-- Migration strategy
CREATE OR REPLACE VIEW GOLD.CONTACTS AS SELECT * FROM GOLD.FCT_CONTACTS;
-- Keep old views for backwards compatibility, deprecate after 30 days

Questions to Finalize¶

Do you prefer full prefixes (fct_, dim_, agg_) or short (f_, d_, m_)?
Should we add prefixes to Silver (base_) or keep clean (no prefix)?
Views: prefix (v_) or rely on materialization type (no prefix)?

My recommendation: Full prefixes (fct_, dim_, agg_, v_) for maximum clarity.