Enterprise Data Analytics Architecture

Enterprise analytics failed for decades because of the same pattern: build a data warehouse, throw Tableau on top, hope people use it. Self-service analytics doesn’t mean giving everyone access to raw SQL. It means building a governed semantic layer that lets business users answer their own questions without corrupting the data or misinterpreting metrics.

---

Modern Analytics Architecture

Data Sources          Ingestion        Warehouse        Semantic        Consumption
┌──────────┐        ┌──────────┐     ┌──────────┐     ┌──────────┐   ┌──────────┐
│ ERP      ├──CDC──▶│          │     │          │     │          │   │ BI Tool  │
│ CRM      ├──API──▶│ Fivetran │────▶│ Snowflake│────▶│ dbt      │──▶│ Dashboards│
│ SaaS     ├──File─▶│ Airbyte  │     │ BigQuery │     │ Metrics  │   │ Self-svc │
│ Events   ├──Event▶│ Custom   │     │ Redshift │     │ Layer    │   │ Embedded │
└──────────┘        └──────────┘     └──────────┘     └──────────┘   └──────────┘

---

dbt Semantic Layer

-- models/marts/finance/fct_revenue.sql

WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
),

payments AS (
    SELECT * FROM {{ ref('stg_payments') }}
)

SELECT
    orders.order_id,
    orders.customer_id,
    orders.order_date,
    orders.product_id,
    payments.payment_method,
    payments.amount AS revenue,
    
    -- Derived metrics (single source of truth)
    CASE
        WHEN payments.amount > 1000 THEN 'enterprise'
        WHEN payments.amount > 100 THEN 'mid-market'
        ELSE 'smb'
    END AS customer_segment,
    
    -- Date dimensions
    DATE_TRUNC('month', orders.order_date) AS order_month,
    DATE_TRUNC('quarter', orders.order_date) AS order_quarter

FROM orders
LEFT JOIN payments ON orders.order_id = payments.order_id
WHERE payments.status = 'completed'

---

Metric Definitions

Principle	Implementation
Single definition	Each metric defined once in the semantic layer
Version controlled	Metric logic in Git, reviewed in PRs
Documented	Description, formula, owner for every metric
Tested	dbt tests validate data quality
Governed	Access controlled by role and data classification

# dbt metric definition
metrics:
  - name: monthly_recurring_revenue
    label: "Monthly Recurring Revenue (MRR)"
    description: "Sum of all active subscription amounts, normalized to monthly"
    type: simple
    type_params:
      measure: revenue
    filter:
      - "subscription_status = 'active'"
    time_grains: [day, week, month, quarter]
    dimensions: [customer_segment, product_tier, region]
    owner: "finance-analytics@company.com"

---

Self-Service Tiers

Tier	Users	Tools	Governance
Curated dashboards	All employees	Pre-built Tableau/Looker dashboards	High — metric definitions locked
Explore/drill-down	Business analysts	Explore mode in BI tool	Medium — explore curated models only
SQL access	Data analysts	SQL interface on warehouse	Medium — read-only, governed schemas
Raw data access	Data engineers	Direct warehouse access	Low — for pipeline development only

---

Anti-Patterns

Anti-Pattern	Problem	Fix
Multiple metric definitions	”Revenue” means different things in different tools	Single semantic layer (dbt metrics)
Raw table access for everyone	Wrong joins, data misinterpretation	Governed views and curated models
No data dictionary	Nobody knows what columns mean	dbt docs + data catalog
Dashboard sprawl	500 dashboards, 50 are used	Dashboard lifecycle management, retire unused
Extract to Excel	Data leaves governance, becomes stale	Embedded analytics, live connections

---

Checklist

• Data warehouse selected and configured
• Ingestion pipeline for all critical data sources
• dbt project: staging → intermediate → marts layers
• Semantic layer: all metrics defined once, documented
• BI tool connected to semantic layer (not raw tables)
• Access controls: tiered by role (curated → explore → SQL)
• Data quality: dbt tests on all critical models
• Documentation: data dictionary, metric ownership
• Dashboard governance: review cycle, retire unused

---

:::note[Source] This guide is derived from operational intelligence at Garnet Grid Consulting. For analytics consulting, visit garnetgrid.com. :::