AT&T Business Intelligence & Self-Service Analytics Platform

Executive Summary

Built AT&T Marketing Operations' Business Intelligence platform, consolidating data from Workfront (project management), Salesforce (CRM), and ZoomInfo (contact data) via Fivetran into Snowflake and powering self-service analytics for 150+ users. Designed and built 25+ Power BI dashboards for executive leadership tracking campaign performance, workforce costs, budget allocations, and operational KPIs. Developed DAX measures for YoY growth, rolling averages, cohort analysis, and variance-to-budget calculations. Reduced dashboard query times from 45 seconds to 3 seconds (93% improvement) through optimized dimensional models. Reduced IT data request tickets by 60% and manual data retrieval by 40%.

BI Platform Architecture

Fivetran syncs all three sources into Snowflake raw schemas on a scheduled cadence. dbt staging models clean and normalize the raw data; intermediate models apply business logic (campaign attribution, SCD Type 2 organizational tracking, date spine joins); mart models produce the final star schema consumed by Power BI.

Key Performance Indicators

Technical Deep Dive

Dimensional Data Model Architecture

Incremental Models for Campaign Data

With 200+ concurrent campaigns producing daily volume metrics, full-refresh models were impractical. Each incremental dbt model uses a unique_key composite (campaign_id + date) and a max(updated_at) filter → meaning only new or changed records are processed per run. This pattern scaled campaign fact tables to handle daily Workfront sync without warehouse timeouts.

-- models/marts/marketing/fct_campaign_daily.sql
-- Incremental model: processes only new/updated campaign records each run
-- Grain: one row per campaign per day

{{
    config(
        materialized='incremental',
        unique_key='campaign_day_key',
        on_schema_change='sync_all_columns',
        cluster_by=['campaign_date', 'department_key']
    )
}}

with workfront_tasks as (
    select * from {{ ref('int_workfront_tasks_normalized') }}
    {% if is_incremental() %}
        where updated_at > (select max(loaded_at) from {{ this }})
    {% endif %}
),

salesforce_campaigns as (
    select * from {{ ref('int_salesforce_campaigns_active') }}
),

date_spine as (
    -- reusable macro: generates daily date records for a rolling window
    {{ dbt_utils.date_spine(
        datepart   = "day",
        start_date = "cast('2024-01-01' as date)",
        end_date   = "current_date()"
    ) }}
),

campaign_daily as (
    select
        {{ dbt_utils.generate_surrogate_key([
            'wt.campaign_id', 'ds.date_day'
        ]) }}                                               as campaign_day_key,

        ds.date_day                                         as campaign_date,
        wt.campaign_id,
        wt.campaign_name,
        wt.department_key,
        wt.owner_employee_key,
        sc.campaign_type,
        sc.budget_amount,

        -- calculated daily metrics
        sum(wt.planned_hours)                               as planned_hours_day,
        sum(wt.actual_hours)                                as actual_hours_day,
        count(distinct wt.task_id)                          as task_count,
        count(distinct case when wt.status = 'COMPLETE'
              then wt.task_id end)                          as tasks_completed,

        -- budget variance
        coalesce(sc.budget_amount, 0) -
            coalesce(sc.actual_spend_to_date, 0)            as budget_variance,

        current_timestamp()                                 as loaded_at

    from date_spine ds
    join workfront_tasks wt
        on ds.date_day between wt.planned_start_date and wt.planned_end_date
    left join salesforce_campaigns sc
        on wt.campaign_id = sc.campaign_id

    group by 1,2,3,4,5,6,7,8
)

select * from campaign_daily

SCD Type 2 → Organizational Change Tracking

AT&T underwent multiple reorganizations during 2024. Department hierarchies, team assignments, and campaign ownership changed frequently. Rather than overwrite historical records, I implemented SCD Type 2 in a dbt macro to track changes with valid_from / valid_to effective dates, allowing Power BI to report on "who owned what, when" across reorg timelines.

-- macros/scd_type_2.sql
-- Tracks slowly changing dimension changes with effective dating
-- Usage: {{ scd_type_2(source_model='stg_salesforce_accounts', natural_key='account_id', tracked_columns=['department', 'owner_id', 'territory']) }}

{% macro scd_type_2(source_model, natural_key, tracked_columns) %}

with source as (
    select * from {{ ref(source_model) }}
),

changes_detected as (
    select
        {{ natural_key }},
        {% for col in tracked_columns %}
        {{ col }},
        {% endfor %}
        updated_at,

        -- hash all tracked columns to detect any change
        md5(concat_ws('|',
            {% for col in tracked_columns %}
            coalesce(cast({{ col }} as varchar), 'NULL')
            {{ ',' if not loop.last }}
            {% endfor %}
        ))                                              as row_hash,

        lag(md5(concat_ws('|',
            {% for col in tracked_columns %}
            coalesce(cast({{ col }} as varchar), 'NULL')
            {{ ',' if not loop.last }}
            {% endfor %}
        ))) over (partition by {{ natural_key }}
                  order by updated_at)                  as prev_row_hash

    from source
),

versioned as (
    select
        *,
        updated_at                                      as valid_from,
        coalesce(
            lead(updated_at) over (partition by {{ natural_key }}
                                   order by updated_at),
            '9999-12-31'::date
        )                                               as valid_to,
        case when lead(updated_at) over (partition by {{ natural_key }}
                                         order by updated_at) is null
             then true else false end                   as is_current

    from changes_detected
    where row_hash != coalesce(prev_row_hash, '')
       or prev_row_hash is null
)

select * from versioned

{% endmacro %}

Results & Business Impact

Technologies & Tools