ARCHITECTURAL PLAYBOOK // QUESTION 01
How do you architect BigQuery and Snowflake dbt marts for 99.9% reliability without runaway compute costs?
Direct Architectural Answer:
You achieve 99.9% warehouse reliability at minimal slot or credit cost by replacing full-table refreshes and naive static lookbacks with dynamic table watermark incremental strategies (insert_overwrite on BigQuery or merge with cluster keys on Snowflake). Querying the target table's own MAX(event_date) minus a 3-day conversion reconciliation window ensures late-arriving ad platform and GA4 events are captured automatically while scanning less than 2% of historical storage.
Production Engineering Rules & Implementation Standards:
- ✓Partition fact marts by date (event_date or order_date) and cluster by high-cardinality filter dimensions (channel_group, brand_id, campaign_id).
- ✓Enforce strict grain contracts in intermediate dbt models before joining multi-channel ad spend (daily campaign grain) with GA4 or Shopify events (transaction grain).
- ✓Use dynamic subqueries against {{ this }} in dbt so pipeline recovery after a third-party API outage automatically backfills the exact missing date range without manual intervention.
models/marts/fct_marketing_commercial_daily.sqlsql // Production Pattern
{{
config(
materialized = 'incremental',
incremental_strategy = 'insert_overwrite',
partition_by = {'field': 'metric_date', 'data_type': 'date', 'granularity': 'day'},
cluster_by = ['business_unit', 'channel_group', 'campaign_id'],
on_schema_change = 'append_new_columns'
)
}}
WITH dynamic_watermark AS (
{% if is_incremental() %}
SELECT DATE_SUB(MAX(metric_date), INTERVAL 3 DAY) AS cutoff_date
FROM {{ this }}
{% else %}
SELECT DATE('2023-01-01') AS cutoff_date
{% endif %}
),
ad_spend_grain AS (
SELECT
metric_date,
business_unit,
channel_group,
campaign_id,
SUM(spend_usd) AS spend_usd,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks
FROM {{ ref('int_paid_media_unified_daily') }}
WHERE metric_date >= (SELECT cutoff_date FROM dynamic_watermark)
GROUP BY 1, 2, 3, 4
),
commercial_outcomes_grain AS (
SELECT
order_date AS metric_date,
business_unit,
attributed_channel_group AS channel_group,
attributed_campaign_id AS campaign_id,
COUNT(DISTINCT order_id) AS net_orders,
SUM(net_revenue_usd) AS net_revenue_usd,
SUM(cm3_contribution_margin_usd) AS cm3_margin_usd
FROM {{ ref('int_orders_cm3_enriched') }}
WHERE order_date >= (SELECT cutoff_date FROM dynamic_watermark)
GROUP BY 1, 2, 3, 4
)
SELECT
COALESCE(a.metric_date, c.metric_date) AS metric_date,
COALESCE(a.business_unit, c.business_unit) AS business_unit,
COALESCE(a.channel_group, c.channel_group) AS channel_group,
COALESCE(a.campaign_id, c.campaign_id) AS campaign_id,
COALESCE(a.spend_usd, 0) AS spend_usd,
COALESCE(a.impressions, 0) AS impressions,
COALESCE(a.clicks, 0) AS clicks,
COALESCE(c.net_orders, 0) AS net_orders,
COALESCE(c.net_revenue_usd, 0) AS net_revenue_usd,
COALESCE(c.cm3_margin_usd, 0) AS cm3_margin_usd
FROM ad_spend_grain a
FULL OUTER JOIN commercial_outcomes_grain c
USING (metric_date, business_unit, channel_group, campaign_id)Production BigQuery dbt incremental mart using dynamic MAX(date) - 3 watermark lookback and insert_overwrite partitioning.
Warehouse Architecture Comparison: Legacy Agency Reporting vs. Database Marketer Production Lakehouse
| Architecture Dimension | Legacy BI / Standard Agency Setup | Database Marketer Production Standard |
|---|---|---|
| Incremental Strategy | Full table rebuilds or hardcoded 7-day lookbacks that miss outages | Dynamic watermark (MAX(date) - 3) with insert_overwrite partition pruning |
| Grain Management | Direct joins between session events and daily spend causing 3x–10x metric fanout | Pre-aggregated intermediate grain contracts with automated dbt uniqueness tests |
| B2B vs. DTC Domain Separation | Blended GA4 properties mixing practitioner/portal logins with consumer traffic | Strict hostname & loyalty-tier classification separating HCP/B2B from DTC acquisition |
| Compute & Slot Efficiency | Unpartitioned views queried directly by BI tools ($2,000+/mo wasted compute) | Partitioned & clustered physical marts delivering sub-second BI at <15% compute cost |