SUBMISSION: Project 3 — Migrate an Overnight Full-Refresh Model to Incremental

🎯 Hands-on Project Deliverable: Optimize a multi-billion row table. Transform an expensive full-refresh dbt model into a performant incremental merge model with defenses against late-arriving data.

---

🏢 Business Scenario & Executive Prompt

Your analytics warehouse monthly Snowflake bill just surged past $14,000. Investigation reveals that fct_events (2.4 billion clickstream rows) runs every night as materialized='table', burning 45 minutes of expensive multi-cluster compute on every execution.

The model reads from an upstream event streaming pipeline (raw.app_events.events) where mobile apps buffer user actions offline and sync hours or days later.

You are tasked with converting this model to materialized='incremental' while guaranteeing:

1. Zero Data Loss: Late-arriving events within a 3-day lookback window must be processed.
2. Idempotency: Re-running the pipeline must never produce duplicate events.
3. Schema Evolution: New event properties added by mobile releases must be accommodated without breaking runs.

---

📋 Upstream Raw Schema (raw.app_events.events)

The raw streaming events table contains:

• event_id (varchar): Unique UUID generated on the mobile device or web client.
• user_id (bigint): Identifier of the user triggering the event.
• event_name (varchar): e.g., 'page_view', 'add_to_cart', 'checkout_completed'.
• event_timestamp (timestamp): Client-side event generation timestamp.
• event_properties (variant / json): Payload of event attributes (screen name, SKU, price).
• _fivetran_synced (timestamp): Warehouse ingestion timestamp.

---

🎯 Required Deliverables

Submit a .zip archive or GitHub PR link containing:

1. models/marts/fct_events.sql: Production incremental model configured with unique_key, is_incremental(), lookback logic, and schema change policies.
2. models/marts/fct_events.yml: Model documentation, uniqueness assertions, and column descriptions.
3. migration_runbook.md: Operational runbook documenting initial --full-refresh execution, ongoing schedule cadence, and weekly drift mitigation.

---

🛠️ Step-by-Step Implementation Guide

Step 1 — Configure the Incremental Materialization

In models/marts/fct_events.sql, configure:

• materialized='incremental'
• unique_key='event_id' (tells dbt to perform an UPSERT / MERGE statement instead of an INSERT)
• incremental_strategy='merge' (or 'delete+insert' depending on warehouse support)
• on_schema_change='append_new_columns' (automatically adds newly introduced columns without failing the build)

Step 2 — Construct the Conditional Filter (is_incremental())

Wrap your incremental filter inside Jinja:

{% if is_incremental() %}
  -- Only scan events ingested in the lookback window
  WHERE _fivetran_synced >= (
      SELECT DATEADD(hour, -72, MAX(_fivetran_synced)) FROM {{ this }}
  )
{% endif %}

Why 72 hours? Mobile devices operating in airplane mode or spotty connectivity may take up to 3 days to sync their offline event queue. A 72-hour lookback window guarantees late-arriving events are captured and merged without re-scanning 2.4 billion rows.

Step 3 — Author Schema Tests (fct_events.yml)

Add tests asserting:

• event_id is unique and not_null.
• user_id is not_null.
• event_timestamp is not_null.

Step 4 — Author the Operational Runbook (migration_runbook.md)

Detail the step-by-step commands for the engineering team:

1. Initial full-refresh: dbt run --select fct_events --full-refresh
2. Hourly production schedule: dbt run --select fct_events
3. Weekly drift reconciliation (running every Sunday at 2:00 AM UTC): dbt run --select fct_events --full-refresh

---

💻 Starter Scaffold

1. models/marts/fct_events.sql

{{
    config(
        materialized='incremental',
        unique_key='event_id',
        incremental_strategy='merge',
        on_schema_change='append_new_columns',
        cluster_by=['event_date', 'event_name']
    )
}}

WITH raw_events AS (
    SELECT * FROM {{ source('app_events', 'events') }}
    {% if is_incremental() %}
        -- 3-day (72 hour) lookback window for late-arriving mobile syncs
        WHERE _fivetran_synced >= (
            SELECT DATEADD(hour, -72, MAX(_fivetran_synced)) FROM {{ this }}
        )
    {% endif %}
),

transformed AS (
    SELECT
        event_id,
        user_id,
        event_name,
        event_timestamp,
        DATE(event_timestamp) AS event_date,
        event_properties,
        _fivetran_synced
    FROM raw_events
)

SELECT * FROM transformed

2. models/marts/fct_events.yml

version: 2

models:
  - name: fct_events
    description: |
      Enterprise clickstream fact table. Materialized incrementally via merge strategy.
      Includes a 72-hour lookback buffer on _fivetran_synced for offline mobile data.
    columns:
      - name: event_id
        description: "Primary key UUID for the clickstream event."
        tests:
          - unique
          - not_null
      - name: user_id
        description: "User who triggered the event."
        tests:
          - not_null
      - name: event_timestamp
        tests:
          - not_null

3. migration_runbook.md

# fct_events Incremental Migration Runbook

## 1. Initial Deployment
Deploying an incremental model for the first time requires an initial full-refresh build:
```bash
dbt run --select fct_events --full-refresh

Expected duration: ~40 minutes. Scans full 2.4 billion raw records and establishes target table.

2. Production Schedule (Hourly)

dbt run --select fct_events

Expected duration: ~2.5 minutes. Scans only rows where _fivetran_synced >= MAX - 72h.

3. Weekly Drift Reconciliation

To heal any upstream record deletions or retroactively edited records:

• Cron Schedule: Every Sunday at 02:00 UTC
• Command: dbt run --select fct_events --full-refresh

---

## 📊 100-Point Evaluation Rubric
| Evaluation Dimension | Weight | Target Standard (Pass ≥ 80%) |
|---|:---:|---|
| **1. Incremental Configuration Syntax** | 25 pts | Correct `materialized='incremental'`, `unique_key='event_id'`, and `incremental_strategy='merge'` declared in config block. |
| **2. Safe is_incremental() Logic** | 25 pts | Dynamic `{{ this }}` reference querying `MAX(_fivetran_synced)` with an explicit lookback buffer (e.g. 72 hours) to avoid scanning the whole table. |
| **3. Schema Evolution Handling** | 25 pts | `on_schema_change` config properly specified (`append_new_columns` or `sync_all_columns`) to prevent pipeline failure on upstream schema shifts. |
| **4. Operational Runbook & Verification** | 25 pts | Clear instructions provided for the `--full-refresh` cutover, ongoing hourly runs, and Sunday maintenance schedule. |

---

## 📤 Submission Instructions & Guidelines
1. **Deliverable Formats:** Upload your `fct_events.sql` file and `migration_runbook.md` as a `.zip` archive, or upload `fct_events.sql` and paste your runbook notes or GitHub PR URL in the Submission Notes.
2. **Dialect Compatibility:** Ensure query syntax matches your target warehouse (Snowflake, BigQuery, Databricks, PostgreSQL).
3. **Self-Review:** Cross-check your solution against the 100-Point Evaluation Rubric above before submitting (passing threshold is 80/100 points).
4. **Upload:** Use the drag-and-drop file upload zone below.
5. **Confirmation:** Click **Submit Assignment** to complete the project.