Analytics Engineering: dbt, Looker, Tableau

# dbt_project.yml
name: analytics_warehouse
version: '1.0.0'

config-version: 2

vars:
  source_database: raw
  analytics_schema: analytics

models:
  analytics:
    +materialized: table
    staging:
      +materialized: view
    intermediate:
      +materialized: ephemeral
    marts:
      +materialized: table
      +schema: marts

-- staging/stg_customers.sql
{{ config(materialized='view') }}

SELECT
    customer_id,
    email,
    first_name,
    last_name,
    created_at,
    status,
    LOWER(email) AS email_normalized,
    CONCAT(first_name, ' ', last_name) AS full_name,
    DATE_TRUNC('month', created_at) AS cohort_month
FROM {{ source('ecom', 'customers') }}
WHERE deleted_at IS NULL

-- intermediate/int_customer_orders.sql
{{ config(materialized='table') }}

WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
    WHERE status = 'completed'
),

customer_orders AS (
    SELECT
        customer_id,
        COUNT(*) AS total_orders,
        SUM(amount) AS lifetime_value,
        AVG(amount) AS avg_order_value,
        MIN(created_at) AS first_order_date,
        MAX(created_at) AS last_order_date,
        DATEDIFF('day', MIN(created_at), MAX(created_at)) AS customer_tenure_days
    FROM orders
    GROUP BY customer_id
)

SELECT * FROM customer_orders

-- marts/fct_customer_summary.sql
{{ config(materialized='table') }}

WITH customer_base AS (
    SELECT * FROM {{ ref('stg_customers') }}
),

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

subscription AS (
    SELECT * FROM {{ ref('stg_subscriptions') }}
    WHERE status = 'active'
)

SELECT
    c.customer_id,
    c.email,
    c.full_name,
    c.cohort_month,
    COALESCE(o.total_orders, 0) AS total_orders,
    COALESCE(o.lifetime_value, 0) AS lifetime_value,
    COALESCE(o.avg_order_value, 0) AS avg_order_value,
    COALESCE(s.monthly_recurring_revenue, 0) AS mrr,
    CASE 
        WHEN o.lifetime_value > 1000 THEN 'high_value'
        WHEN o.lifetime_value > 100 THEN 'medium_value'
        ELSE 'low_value'
    END AS customer_tier
FROM customer_base c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN subscription s ON c.customer_id = s.customer_id

# schema.yml
version: 2

models:
  - name: stg_customers
    description: Staged customer data
    columns:
      - name: customer_id
        description: Primary key
        tests:
          - unique
          - not_null
      
      - name: email
        description: Customer email
        tests:
          - unique
          - not_null
          - dbt_utils.expression_is_true:
              expression: "LIKE '%@%.%'"

  - name: fct_customer_summary
    description: Customer summary metrics
    tests:
      - dbt_utils.recency:
          datepart: day
          field: last_order_date
          interval: 2
          config:
            where: "total_orders > 0"

<!-- Tableau Data Source -->
<datasource hasconnection='false' inline='true' name='Analytics' caption='Customer Analytics'>
  <connection class='postgres' dbname='analytics' schema='marts' host='warehouse.example.com'>
    <named-connections>
      <named-connection name='Analytics DB'>
        <connection class='postgres' dbname='analytics' schema='marts' port='5432' username='tableau_user' />
      </named-connections>
    </named-connections>
  </connection>
  
  <columns>
    <column datatype='string' name='customer_id' param-domain-type='string' role='measure' type='nominal' />
    <column datatype='string' name='full_name' role='dimension' type='nominal' />
    <column datatype='real' name='lifetime_value' role='measure' type='quantitative' />
    <column datatype='datetime' name='created_at' role='dimension' type='ordinal' />
  </columns>
  
  <aliases enabled='yes'>
    <alias key='customer_id' value='Customer ID' />
    <alias key='lifetime_value' value='Lifetime Value' />
  </aliases>
</datasource>

-- Tableau Calculations

-- Customer Tier
IF [Lifetime Value] > 10000 THEN 'Platinum'
ELSEIF [Lifetime Value] > 5000 THEN 'Gold'
ELSEIF [Lifetime Value] > 1000 THEN 'Silver'
ELSE 'Bronze'
END

-- Month-over-Month Growth
(ZN(SUM([Revenue])) - LOOKUP(ZN(SUM([Revenue])), -1)) 
/ ABS(LOOKUP(ZN(SUM([Revenue])), -1))

-- Running Total
RUNNING_SUM(SUM([Revenue]))

-- Cohort Retention
COUNTD(IF [Acquisition Month] = [Current Month] THEN [Customer ID] END) 
/ COUNTD([Customer ID])