ADR 026 — Gold Feed Layer (feed_): External System Export Views¶

Date: 2026-05-22 Status: Accepted Deciders: Data Engineering

Context¶

The Snowflake-to-Google Sheets pipeline (ADR 025) requires source views that are shaped and windowed specifically for export to external consumers. These views have a distinct set of requirements that do not fit either of the two existing Gold view prefixes:

Why not exp_: exp_ views are exploratory and carry no SLA — they may be modified or dropped without notice. Feed views are production-grade: a Google Sheet that stakeholders rely on daily cannot be backed by a no-SLA view.

Why not rpt_: rpt_ views are Tableau-ready reports with a stable public column contract. Feed views are shaped for a different destination (Google Sheets or other external systems) and carry a rollup contract — they blend daily and weekly grain within a single view to manage row count. This is not appropriate for a Tableau data source, which expects a single consistent grain.

The rollup requirement: Google Sheets has a 10 million cell limit (ADR 025). A daily-grain view accumulating over multiple years would eventually breach this limit. The solution is to bake a conditional grain rollup into the view itself:

• Data within the recent window (e.g., last 3 months) → daily grain (preserved from the underlying agg_)
• Data outside the recent window → weekly grain (DATE_TRUNC('week', date) + SUM())

The window boundary and total history depth vary by source: - Some datasets use YTD (year-to-date) as the recent window - Others use a rolling N months (e.g., last 3 months) - Total history may span 12+ months

This rollup logic is a modeling concern — it belongs in dbt, not in the Airflow DAG or Sheets sync task (see ADR 025 for the rationale).

Decision¶

Introduce a new feed_ prefix for Gold views that serve as data feeds for external systems.

feed_ views:

• Are materialized as view (zero storage — identical to rpt_ and exp_)
• Live in the same GOLD schema — no new Snowflake schema required
• Use tags: ['feed'] in their config() block
• Follow the naming pattern feed_{client}_{purpose} — e.g., feed_cnd_agent_performance_channel
• Are production-grade — backed by an active Google Sheets export pipeline; column contracts are stable and breaking changes require a migration or stakeholder notice
• Implement conditional grain rollup — daily within the recent window, weekly (or YTD) outside it — to manage row count within the Google Sheets cell limit
• May serve multiple destination types — Google Sheets today, S3 exports or other systems in the future; the prefix is destination-agnostic

Rollup implementation (current approach): Each client source has its own per-source SQL view that implements the rollup directly in the model body. No shared macro is used at this stage. See "Options Considered" for the macro approach reserved for future development.

Rollup Pattern¶

The standard conditional grain rollup in a feed_ view:

{{ config(materialized='view', tags=['feed']) }}

SELECT
    CASE
        WHEN date >= DATEADD('month', -3, CURRENT_DATE()) THEN date
        ELSE DATE_TRUNC('week', date)
    END                                    AS reporting_date,
    eid,
    channel,
    SUM(contacts_fulfilled)                AS contacts_fulfilled,
    SUM(total_handle_time_sec)             AS total_handle_time_sec,
    -- ... remaining additive columns
FROM {{ ref('agg_cnd_agent_summary_channel_daily') }}
WHERE date >= DATEADD('month', -12, CURRENT_DATE())   -- total history bound
GROUP BY 1, 2, 3

Key rules: - CURRENT_DATE() in a Snowflake view evaluates at query time — the view is always current without any Airflow parameter injection - Only additive metrics (sums and counts) survive the weekly rollup correctly; averages and rates must be recomputed from their component sums in the view - The WHERE clause bounds total history; the CASE expression controls grain within that window - Window boundary (3 months, YTD, etc.) and history depth (12 months, etc.) are set per source in the model body

Layer Summary¶

Promotion and Deprecation¶

Promotion from exp_: If a view was initially built as exp_ during validation and is now confirmed correct and wired to a production export, rename it to feed_. Update the schema.yml name and the Airflow DAG config source reference. The old Snowflake view (EXP_{CLIENT}_*) must be dropped manually after the new FEED_{CLIENT}_* view is verified.

Deprecation: Because feed_ views back active export pipelines, they cannot be dropped silently. Deprecate by: 1. Removing or redirecting the Airflow DAG config that sources from the view 2. Dropping the Snowflake view via schemachange migration 3. Removing the dbt model and schema.yml entry in the same PR

Consequences¶

• Naming is unambiguous: rpt_ = Tableau production, exp_ = exploratory, feed_ = external export with rollup
• Zero storage cost: All three use view materialization
• Rollup logic is colocated with the model: The view is self-contained — the Airflow DAG does not need to know the window boundary
• Per-source views: Each client source implements its own rollup. This avoids premature abstraction at the cost of some duplication across clients when rollup boundaries are identical
• Macro path available: See "Options Considered" for the future-development macro approach

Options Considered¶

Shared rollup macro (rollup_by_recency)¶

A dbt macro that accepts recent_window_months, history_months, and the source ref as parameters, and generates the conditional CASE + GROUP BY SQL inline. This would eliminate duplication when multiple feed_ views share the same rollup boundary.

Deferred, not rejected. The macro approach is the right long-term direction once the pattern is proven across multiple sources and the parameter surface stabilizes. Implementing it before building more than one or two feed views risks premature abstraction. A backlog issue tracks this work.

Manage rollup in the Airflow DAG¶

Pass start_date and rollup_boundary_date as query parameters from the DAG config, keeping the base view grain-agnostic.

Rejected. The conditional grain rollup is a transformation concern, not an orchestration concern. Putting it in the DAG splits transformation logic across two systems, makes the DAG config harder to read, and means changing the window requires a DAG config change rather than a model change. Both require a PR — the model is the right place for transformation logic.

Use rpt_ with a naming convention (e.g., rpt_cnd_agent_performance_weekly)¶

Keep the rpt_ prefix but distinguish feed views by embedding _weekly or _rollup in the model name.

Rejected. rpt_ implies a Tableau-backed report with a stable single-grain column contract. A mixed-grain rollup view is not appropriate as a Tableau data source and should not carry the same stability signal. The prefix is the right disambiguation mechanism.

See also: ADR 021 — exp_ exploration layer | ADR 025 — Snowflake-to-GSheets sync strategy | ADR 008 — Gold layer architecture | ADR 019 — Gold layer standardization