Aggregates vs Views: Clarification & Naming¶

Date: 2026-01-05 Status: Recommendation Purpose: Clarify the difference between AGG tables and views, and define consistent naming

Key Question: What's the Difference?¶

Both can show aggregated data to end users. So what's the distinction?

Technical Difference¶

AGG Tables (Materialized)¶

Materialization: table or incremental

Characteristics: - ✅ Stored physically in Snowflake (takes storage space) - ✅ Pre-calculated (computed once, read many times) - ✅ Fast queries (data already aggregated) - ✅ Scheduled refresh (dbt runs, Airflow triggers) - ❌ Storage cost (GB consumed) - ❌ Can be stale (refresh lag)

Example:

-- dbt config
{{
    config(
        materialized='table'  -- OR 'incremental'
    )
}}

-- This query runs during dbt, result is stored
SELECT
    KPI_DATE,
    AGENT_ID,
    COUNT(*) AS CONTACT_COUNT,
    AVG(HANDLE_TIME) AS AVG_HANDLE_TIME
FROM {{ ref('fct_contacts') }}
GROUP BY 1, 2

Result: Physical table AGG_AGENT_DAILY exists in Snowflake with aggregated data stored.

VIEWS (Virtual)¶

Materialization: view

Characteristics: - ✅ No storage (just a saved SQL query) - ✅ Always fresh (computed on every query) - ✅ Zero storage cost (no GB consumed) - ❌ Slower queries (computation happens at query time) - ❌ Repeated calculations (same work done each time)

Example:

-- dbt config
{{
    config(
        materialized='view'
    )
}}

-- This query runs EVERY TIME someone queries the view
SELECT
    agg.*,
    roster.DIRECT_LEADER,
    targets.AHT_TARGET,
    agg.AVG_AHT - targets.AHT_TARGET AS AHT_VARIANCE
FROM {{ ref('agg_agent_daily') }} agg
LEFT JOIN {{ source('core', 'roster_warbyparker') }} roster
    ON agg.AGENT_ID = roster.EID
LEFT JOIN {{ source('core', 'targets') }} targets
    ON agg.KPI_DATE = targets.TARGET_DATE

Result: No physical table. When Tableau queries V_AGENT_DASHBOARD, this SQL runs on-the-fly.

Functional Difference (More Important)¶

The technical difference (stored vs virtual) is less important than the functional difference (what each is used for).

AGG Tables = Base Aggregates (Building Blocks)¶

Purpose: Pre-aggregated base tables that serve as building blocks for reports

Characteristics: - Single grain (e.g., agent-day, channel-hour) - Standard KPI calculations - Reusable across multiple reports - Few or no JOINs with other tables - Not directly consumed by end users

Examples:

AGG_AGENT_DAILY           -- Agent metrics by day (base aggregate)
AGG_CHANNEL_HOURLY        -- Channel metrics by hour (base aggregate)
AGG_QUEUE_DAILY           -- Queue metrics by day (base aggregate)

Users: Other dbt models, power users who know SQL

VIEWS/REPORTS = Consumption Layer (End-User Facing)¶

Purpose: End-user facing views that combine aggregates with context (rosters, targets, etc.)

Characteristics: - JOINs multiple tables (aggregates + dimensions + targets) - Calculated fields (variances, performance flags) - Tableau-optimized (all columns needed for dashboard) - Directly consumed by end users (Tableau, analysts) - Customized per use case

Examples:

RPT_AGENT_DASHBOARD       -- Tableau agent dashboard (joins AGG + roster + targets)
RPT_EXECUTIVE_SUMMARY     -- Executive view (joins multiple AGG tables)
RPT_MANAGER_SCORECARD     -- Manager view (joins AGG + roster + goals)

Users: Tableau users, business analysts, executives

Analogy¶

Think of it like a restaurant:

Layer	Restaurant Analogy	Data Warehouse
FACTS	Raw ingredients	`fct_contacts` (granular data)
AGGREGATES	Prepared components	`agg_agent_daily` (pre-cooked metrics)
VIEWS/REPORTS	Plated dishes	`rpt_agent_dashboard` (ready to consume)

AGG tables = Prep kitchen (chop vegetables, cook rice, marinate meat)
VIEWS = Plating station (combine components, add garnish, serve)

You don't serve raw ingredients (FACTS) directly to customers. You don't serve prep components (AGG) directly either. You serve plated dishes (REPORTS) that combine everything beautifully.

Example Flow¶

-- Step 1: Fact table (granular contact data)
fct_contacts
  - CONTACT_ID
  - AGENT_ID
  - TIMESTAMP
  - HANDLE_TIME
  - CHANNEL
  - (millions of rows)

     ↓ Aggregate (dbt table materialization)

-- Step 2: Base aggregate (pre-calculated metrics)
agg_agent_daily
  - KPI_DATE
  - AGENT_ID
  - CONTACT_COUNT
  - AVG_HANDLE_TIME
  - AVG_AHT
  - (thousands of rows - much smaller)

     ↓ Join and enhance (dbt view materialization)

-- Step 3: Report view (consumption ready)
rpt_agent_dashboard
  - KPI_DATE
  - AGENT_ID
  - AGENT_NAME (from roster)
  - AGENT_ROLE (from roster)
  - DIRECT_LEADER (from roster)
  - CONTACT_COUNT (from agg)
  - AVG_HANDLE_TIME (from agg)
  - AHT_TARGET (from targets)
  - AHT_VARIANCE (calculated)
  - AHT_PERFORMANCE_FLAG (calculated: "Meeting", "Close", "Below")
  - (thousands of rows, many columns)

Flow: 1. fct_contacts (millions of rows) → too slow to aggregate on-the-fly 2. agg_agent_daily (thousands of rows) → pre-aggregated, fast base 3. rpt_agent_dashboard (view) → combines agg + roster + targets, zero storage cost

When to Use Each¶

Use AGG Table When:¶

✅ Expensive aggregation (millions of rows → thousands)
✅ Reusable base (multiple reports use same aggregation)
✅ Scheduled refresh is acceptable (not real-time)
✅ Query performance matters (dashboard load time)

Example:

-- This aggregation is expensive and reused by multiple reports
AGG_AGENT_DAILY  (materialized as table)
  - Used by: rpt_agent_dashboard, rpt_manager_scorecard, rpt_executive_summary

Use VIEW When:¶

✅ Always need fresh data (roster changes, target updates)
✅ Simple JOINs (not expensive aggregations)
✅ Storage cost is a concern
✅ Customized per consumer (Tableau-specific columns)

Example:

-- This view just joins pre-aggregated data with current roster
RPT_AGENT_DASHBOARD  (materialized as view)
  - Queries AGG_AGENT_DAILY (already aggregated, fast)
  - JOINs with current roster (always fresh)
  - Adds calculated fields (variance, flags)

Naming Convention: `rpt_` vs `v_` vs `report_`¶

Option 1: `v_` (Traditional Database Naming)¶

Pattern: v_{purpose}

Examples:

v_agent_dashboard
v_executive_summary
v_manager_scorecard

Pros: - ✅ Short, clean - ✅ Traditional database convention - ✅ Snowflake SHOW VIEWS will list them

Cons: - ❌ Inconsistent with other prefixes (fct_, dim_, agg_ are 3-4 chars, v_ is 1 char) - ❌ Doesn't indicate it's a report (could be any view)

Option 2: `rpt_` (Report Prefix - RECOMMENDED)¶

Pattern: rpt_{purpose}_{grain} or rpt_{consumer}

Examples:

rpt_agent_dashboard
rpt_agent_daily            -- If view also aggregates
rpt_executive_summary
rpt_manager_scorecard

Pros: - ✅ Consistent length with other prefixes (fct_, dim_, agg_, rpt_ all 3-4 chars) - ✅ Clearly indicates consumption layer (report for end users) - ✅ Common in BI tools (Tableau, Power BI users understand "report") - ✅ Distinguishes from generic views

Cons: - None significant

Option 3: `report_` (Full Word)¶

Pattern: report_{purpose}

Examples:

report_agent_dashboard
report_executive_summary
report_manager_scorecard

Pros: - ✅ Most explicit - ✅ Business-friendly (non-technical users understand)

Cons: - ❌ Longer (7 characters vs 4) - ❌ Less common in data warehousing

✅ Recommended Convention: Use `rpt_`¶

Why rpt_ is best: 1. Consistent prefix length: fct_, dim_, agg_, rpt_ (all 3-4 chars) 2. Semantic clarity: Indicates consumption layer (end-user reports) 3. Industry common: Many BI projects use rpt_ prefix 4. Distinguishes purpose: Not just a view, it's a report

Complete Gold Layer Naming Convention¶

Type	Prefix	Purpose	Materialization	Example
Facts	`fct_`	Enriched granular data	`table`/`incremental`	`fct_contacts`
Dimensions	`dim_`	Current state entities	`table`/`incremental`	`dim_agent`
Aggregates	`agg_`	Base aggregated metrics	`table`/`incremental`	`agg_agent_daily`
Reports	`rpt_`	End-user consumption	`view`	`rpt_agent_dashboard`

Applied to Your Use Case¶

Gold Layer Structure:¶

-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- FACTS (granular enriched data)
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- Materialized: table or incremental
-- Purpose: Source of truth for analysts (most granular)
-- Users: Power users, data scientists

GOLD.FCT_CONTACTS
  - Row per contact event
  - Enriched with employee + glossary metadata
  - Filtered to XO agents
  - Materialized as table (fast queries)

GOLD.FCT_CONVERSATIONS
GOLD.FCT_MESSAGES

-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- DIMENSIONS (current state entities)
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- Materialized: table or incremental
-- Purpose: Reference data for joins
-- Users: Joined with facts and aggregates

GOLD.DIM_AGENT
  - Row per active agent
  - Current roster state
  - Materialized as table

GOLD.DIM_INBOX
GOLD.DIM_QUEUE

-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- AGGREGATES (base metrics - building blocks)
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- Materialized: table or incremental
-- Purpose: Pre-calculated metrics (reusable)
-- Users: Report views, power users

GOLD.AGG_AGENT_DAILY
  - Row per agent per day
  - Standard KPIs + client-specific KPIs
  - Materialized as table (expensive aggregation)
  - Reused by multiple reports

GOLD.AGG_AGENT_HOURLY
  - Row per agent per hour
  - For intraday analysis

GOLD.AGG_CHANNEL_DAILY
  - Row per channel per day
  - Channel performance metrics

GOLD.AGG_QUEUE_DAILY
  - Row per queue per day
  - Queue performance metrics

GOLD.AGG_EXECUTIVE_WEEKLY
  - Row per week
  - High-level executive metrics

-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- REPORTS (consumption views - end-user facing)
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- Materialized: view
-- Purpose: Tableau/analyst ready (joins + calculations)
-- Users: Business users, Tableau, executives

GOLD.RPT_AGENT_DASHBOARD
  - Tableau agent performance dashboard
  - Joins: agg_agent_daily + dim_agent + targets
  - Calculated: variance, performance flags
  - Always fresh (view = no storage)

GOLD.RPT_AGENT_AD_HOC
  - Ad-hoc analyst exploration
  - Joins: fct_contacts + all dimensions
  - Wide table with all columns

GOLD.RPT_EXECUTIVE_SUMMARY
  - Executive high-level metrics
  - Joins: agg_executive_weekly + targets
  - Calculated: trends, year-over-year

GOLD.RPT_MANAGER_SCORECARD
  - Manager team performance view
  - Joins: agg_agent_daily + dim_agent + roster
  - Calculated: team rankings, leader performance

GOLD.RPT_QA_ANALYSIS
  - QA team analysis view
  - Joins: fct_contacts + qa_scores
  - Filtered: only QA-relevant fields

Typical Query Patterns¶

Analyst queries FACT table (granular analysis):¶

-- Ad-hoc deep dive
SELECT
    AGENT_ID,
    CONTACT_ID,
    TIMESTAMP,
    HANDLE_TIME,
    CHANNEL
FROM GOLD.FCT_CONTACTS
WHERE KPI_DATE = '2026-01-05'
  AND AGENT_SITE = 'New York';

Dashboard queries AGGREGATE table (if need custom viz):¶

-- Tableau custom viz (not using pre-built report)
SELECT
    KPI_DATE,
    AGENT_ID,
    CONTACT_COUNT,
    AVG_HANDLE_TIME
FROM GOLD.AGG_AGENT_DAILY
WHERE KPI_DATE BETWEEN '2026-01-01' AND '2026-01-31';

Dashboard queries REPORT view (most common):¶

-- Tableau using pre-built report
SELECT *
FROM GOLD.RPT_AGENT_DASHBOARD
WHERE KPI_DATE BETWEEN '2026-01-01' AND '2026-01-31';

-- Gets everything: metrics + roster + targets + calculated fields
-- No JOINs needed in Tableau (already done in view)

Performance Consideration¶

Why use both AGG tables AND report views?¶

Option A: Just use AGG tables (no views)

-- Tableau does the joins
SELECT
    agg.KPI_DATE,
    agg.AGENT_ID,
    agg.CONTACT_COUNT,
    roster.AGENT_NAME,
    roster.DIRECT_LEADER,
    targets.AHT_TARGET,
    agg.AVG_AHT - targets.AHT_TARGET AS VARIANCE  -- Calculated field in Tableau
FROM GOLD.AGG_AGENT_DAILY agg
LEFT JOIN CORE.ROSTER_WARBYPARKER roster ON agg.AGENT_ID = roster.EID
LEFT JOIN CORE.TARGETS targets ON agg.KPI_DATE = targets.TARGET_DATE;

Problems: - ❌ Tableau users must know SQL - ❌ Repeated JOINs on every dashboard load - ❌ Variance calculations in Tableau (less performant) - ❌ Hard to maintain (business logic in Tableau, not dbt)

Option B: Use AGG tables + report views (RECOMMENDED)

-- View does all the work once
CREATE VIEW GOLD.RPT_AGENT_DASHBOARD AS
SELECT
    agg.KPI_DATE,
    agg.AGENT_ID,
    agg.CONTACT_COUNT,
    roster.AGENT_NAME,
    roster.DIRECT_LEADER,
    targets.AHT_TARGET,
    agg.AVG_AHT - targets.AHT_TARGET AS VARIANCE  -- Calculated in SQL (fast)
FROM GOLD.AGG_AGENT_DAILY agg
LEFT JOIN CORE.ROSTER_WARBYPARKER roster ON agg.AGENT_ID = roster.EID
LEFT JOIN CORE.TARGETS targets ON agg.KPI_DATE = targets.TARGET_DATE;

-- Tableau just selects
SELECT * FROM GOLD.RPT_AGENT_DASHBOARD;

Benefits: - ✅ Tableau users just SELECT (no SQL knowledge needed) - ✅ Business logic in dbt (version controlled, tested) - ✅ Consistent calculations across all consumers - ✅ Easy to modify (change view, all dashboards updated)

When to Materialize a Report as TABLE (not view)¶

Normally reports are views, but materialize as table if:

Complex joins (many tables, expensive operations)
Very large result set (millions of rows even after aggregation)
Used very frequently (hundreds of queries per day)
Stale data is acceptable (refresh lag OK)

Example:

-- This report joins 10+ tables, takes 30 seconds to compute
-- Materialize as table, refresh nightly
{{
    config(
        materialized='table'  -- Not a view
    )
}}

-- Complex report with many joins
SELECT ... FROM ... (many JOINs)

Then use a different naming: - If materialized as table: Could use agg_ or rpt_ (your choice) - If materialized as view: Use rpt_

Summary: AGG vs RPT¶

Aspect	AGG (Aggregate)	RPT (Report)
Purpose	Building blocks	End-user consumption
Materialization	`table` (stored)	`view` (virtual)
Grain	Single grain	May combine multiple grains
JOINs	Few or none	Multiple (agg + dims + targets)
Calculations	Base metrics	Enhanced (variance, flags)
Reusability	High (many reports use)	Low (specific to one use case)
Storage	Yes (cost)	No (zero cost)
Freshness	Refresh lag	Always current
Users	Power users, reports	Business users, Tableau
Example	`agg_agent_daily`	`rpt_agent_dashboard`

Final Recommendation¶

✅ Use `rpt_` prefix for reports/views¶

Complete naming convention:

Layer	Prefix	Example
Bronze	`{SOURCE}_`	`GLADLY_CONTACTS`
Silver	None	`CONTACT_TIMESTAMPS`
Gold Facts	`fct_`	`fct_contacts`
Gold Dimensions	`dim_`	`dim_agent`
Gold Aggregates	`agg_`	`agg_agent_daily`
Gold Reports	`rpt_`	`rpt_agent_dashboard`

All prefixes are 3-4 characters - consistent!

Semantic meaning is clear: - fct_ → Granular enriched data (facts) - dim_ → Current state entities (dimensions) - agg_ → Pre-aggregated metrics (building blocks) - rpt_ → End-user reports (consumption layer)

Questions?¶

Should all report views use rpt_ prefix? (Yes, recommended)
What if a report is materialized as table (not view)? (Still use rpt_ or consider agg_)
Can we have both agg_agent_daily (table) and rpt_agent_daily (view)? (Yes! Aggregate is base, report adds context)

This convention is: - ✅ Consistent (all 3-4 char prefixes) - ✅ Semantic (prefix indicates purpose) - ✅ Industry-aligned (dbt, GitLab, Snowflake use similar) - ✅ Self-documenting (users know what to expect)

Aggregates vs Views: Clarification & Naming¶

Key Question: What's the Difference?¶

Technical Difference¶

AGG Tables (Materialized)¶

VIEWS (Virtual)¶

Functional Difference (More Important)¶

AGG Tables = Base Aggregates (Building Blocks)¶

VIEWS/REPORTS = Consumption Layer (End-User Facing)¶

Analogy¶

Example Flow¶

When to Use Each¶

Use AGG Table When:¶

Use VIEW When:¶

Naming Convention: rpt_ vs v_ vs report_¶

Option 1: v_ (Traditional Database Naming)¶

Option 2: rpt_ (Report Prefix - RECOMMENDED)¶

Option 3: report_ (Full Word)¶

✅ Recommended Convention: Use rpt_¶

Complete Gold Layer Naming Convention¶

Applied to Your Use Case¶

Gold Layer Structure:¶

Typical Query Patterns¶

Analyst queries FACT table (granular analysis):¶

Dashboard queries AGGREGATE table (if need custom viz):¶

Dashboard queries REPORT view (most common):¶

Performance Consideration¶

Why use both AGG tables AND report views?¶

When to Materialize a Report as TABLE (not view)¶

Summary: AGG vs RPT¶

Final Recommendation¶

✅ Use rpt_ prefix for reports/views¶

Questions?¶

Naming Convention: `rpt_` vs `v_` vs `report_`¶

Option 1: `v_` (Traditional Database Naming)¶

Option 2: `rpt_` (Report Prefix - RECOMMENDED)¶

Option 3: `report_` (Full Word)¶

✅ Recommended Convention: Use `rpt_`¶

✅ Use `rpt_` prefix for reports/views¶