Build the Asset: A dbt Model Spec and Reverse-ETL Sync Plan
Objective: Design a documented dbt-model spec for a 'high-intent policy renewal' audience from raw GA4 and CRM fields, then write the reverse-ETL sync plan that activates it to a paid-ads platform and a CRM, without touching a live warehouse.
You're the growth marketer at Go Digit General Insurance, the Bengaluru-founded general insurer that listed on the NSE/BSE in 2024. Renewal season is 6 weeks out and the data team needs your model spec before they'll build it.
Write the model logic a data engineer could actually implement, then write the sync plan that gets it into the two places it needs to activate.
Before you start
What you'll need
Free path (everything below is enough to finish)
Free, a spec doc needs no special tooling
Free tier is enough for a single logic-review pass
Paid upgrades (optional, faster/deeper)
This exercise documents the spec and sync plan by hand; a real reverse-ETL tool (e.g. Hightouch, Census) would automate the actual daily sync once the model is built.
Real CRM the sales/retention team already works in
The process
2 steps
Step 01 of 02
The lesson's dbt layer takes raw fields from separate sources (a policy record, a site-visit event) and joins/filters them into one named, reusable model that every downstream tool references instead of each redefining the logic itself.
You have raw fields from GA4 (policy_page_visit, quote_calculator_used) and the CRM (renewal_date, prior_claims_count, policy_tier). What's the actual filter logic for 'high-intent policy renewal'?
Procedure
- List every raw field needed from GA4 and the CRM
- Write the join key (customer_id) connecting the two sources
- Define the filter: renewal_date within 45 days AND (policy_page_visit in last 14 days OR quote_calculator_used)
- Exclude prior_claims_count > 2 (high-risk renewals route to a retention specialist, not a self-serve campaign)
- Ask Claude to review the logic for an edge case you missed (e.g. customers with a renewal_date but no site visit at all)
MODEL: high_intent_policy_renewal SOURCE: ga4.events JOIN crm.policies ON customer_id FILTER: renewal_date <= today + 45 days AND (policy_page_visit_last_14d = true OR quote_calculator_used = true) AND prior_claims_count <= 2 EDGE CASE (flagged by Claude): customers with a renewal_date in range but zero GA4 events, likely offline/branch-only customers, model excludes them; needs a separate branch-sourced flag before the sync fires.
Healthy
The spec has a named model, explicit join key, explicit filter, and one documented edge case the data team can decide how to handle.
Unhealthy
The spec says 'sync anyone likely to renew' with no field-level definition, forcing the data engineer to guess.
What this means
A model spec a data engineer can implement without a follow-up meeting is the actual deliverable, not a description of the goal.
So what do I do about it?
| Symptom | Action | Effort |
|---|---|---|
| Data team keeps asking clarifying questions before building the model | Rewrite the spec with explicit field names and a stated join key | 30 min |
Step 02 of 02
The lesson's reverse-ETL layer pushes a warehouse model back out to the tools people actually work in, on a schedule, so the audience stays current instead of going stale between manual exports.
The model refreshes daily at 2 AM. Where does this audience need to land, and how fresh does each destination actually need to be?
Procedure
- List each activation destination: Meta/Google Ads custom audience, HubSpot CRM 'renewal-ready' flag
- For each, state the sync cadence (matches the daily dbt run, or faster if the destination supports it)
- State exactly which fields pass through (customer_id, renewal_date, policy_tier, no raw event data)
- Note the one destination requiring a paid connector vs. one that can be handled with a scheduled CSV export as a fallback
SYNC PLAN: high_intent_policy_renewal DESTINATION CADENCE FIELDS PASSED FALLBACK IF NO REVERSE-ETL TOOL Google/Meta Ads Daily 3am customer_id, policy_tier Manual CSV upload to Ads audience, weekly HubSpot CRM flag Daily 3am customer_id, renewal_date Scheduled HubSpot import, weekly NOTE: weekly manual fallback means the ads audience is up to 6 days stale during renewal season, flag this as the reason to prioritize a real reverse-ETL connector before next renewal cycle.
Healthy
The plan states cadence, fields, and an honest fallback for teams without a reverse-ETL tool yet.
Unhealthy
The plan just says 'sync everywhere in real time' without naming a fallback for teams still on manual exports.
What this means
A sync plan needs a real answer for 'what happens without the paid tool,' not just the ideal-state architecture.
So what do I do about it?
| Symptom | Action | Effort |
|---|---|---|
| No reverse-ETL budget approved yet | Ship the weekly manual-CSV fallback for this renewal cycle, revisit budget after | half day |
Final deliverable
A documented dbt-model spec (fields, join key, filter, one flagged edge case) and a reverse-ETL sync plan (destination, cadence, fields passed, manual fallback) for a renewal-season audience.
See a reference example
high_intent_policy_renewal, Acko General Insurance renewal audience spec MODEL: join GA4 site events to CRM policy records on customer_id, filter to renewal within 30 days plus a calculator visit in the last 10 days, exclude 3+ prior claims. SYNC: daily push to Google Ads custom audience and a CRM 'renewal-ready' tag; weekly manual CSV fallback confirmed for the first cycle since no reverse-ETL tool is live yet.
Success criteria
You're done when you can:
- Model spec names explicit fields and a join key, not a vague description
- Sync plan states cadence and a fallback for teams without a reverse-ETL tool