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

# 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: ```sql {% 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` ```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` ```yaml 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: