Build a Composable CDP with Snowflake, dbt & Reverse ETL

November 4, 2026

Building a composable CDP involves leveraging your central data warehouse like Snowflake as the single source of truth, using dbt to transform raw tables into unified customer profiles, and deploying a Reverse ETL tool such as Hightouch or Census to operationalize those audiences into ad platforms and CRMs. Decoupling storage, modeling, and activation eliminates vendor lock-in, reduces software overhead, and keeps full data ownership inside your security perimeter.

Key Takeaways:

• A composable CDP architecture separates data storage, transformation, and sync layers, preventing vendor lock-in and drastically reducing licensing costs.
• dbt transforms raw event streams and CRM tables into deterministic identity graphs and aggregated customer metrics directly inside Snowflake.
• Reverse ETL engines sync SQL-defined cohorts to marketing APIs (Meta CAPI, Google Ads, Braze, Salesforce) without requiring custom script maintenance.
• Centralizing operational control inside your cloud warehouse guarantees consistent privacy enforcement, consent filtering, and auditability across all marketing destinations.

Why Choose a Composable CDP Architecture?

Traditional packaged Customer Data Platforms (CDPs) like Segment, Treasure Data, or ActionIQ combine ingestion, storage, identity resolution, and activation into a single proprietary bundle. While convenient at first, packaged platforms create secondary data silos. Data engineering teams spend hours building pipelines to copy CRM data into the CDP, only for the CDP to build its own isolated customer profiles that do not match the company enterprise warehouse.

As Andreessen Horowitz noted in their analysis of modern enterprise data stacks, companies are increasingly shifting away from monolithic software bundles toward modular architectures built around the data warehouse. A composable CDP decouples the stack into three distinct, best-in-class components:

• Data Storage & Warehouse: Snowflake stores structured, semi-structured, and unstructured customer interaction data at scale.
• Data Modeling & Identity: dbt (data build tool) transforms raw tables into standardized customer dimensional models, churn scores, and calculated metrics.
• Operationalization & Activation: Reverse ETL platforms like Hightouch, Census, or RudderStack query those dbt models and push targeted audiences to external APIs.

By owning the underlying architecture, marketing ops and ad ops leads retain total flexibility. If you change your marketing automation tool or ad channels, you do not need to re-architect your data ingestion or identity graph. You simply point your Reverse ETL syncs to the new endpoints.

Step 1: Ingest and Centralize Raw First-Party Data in Snowflake

A composable CDP is only as strong as its raw data inputs. Your first task is landing every critical touchpoint—online web events, offline store transactions, mobile app activity, and CRM updates—into structured Snowflake schemas.

Set up dedicated database schemas within Snowflake to isolate ingestion sources:

• RAW_WEB_EVENTS: Ingested via streaming tools like RudderStack, Snowplow, or server-side Google Tag Manager.
• RAW_CRM: Replicated from Salesforce, HubSpot, or Microsoft Dynamics using automated ingestion connectors like Fivetran or Airbyte.
• RAW_TRANSACTIONS: Synced directly from production transactional databases (PostgreSQL, MySQL, or DynamoDB).

For example, if you collect event streams from web and mobile apps, ensure web analytics events land with standardized identifier keys: anonymous_id, user_id, device_id, and timestamp tracking. If you are importing conversions from web tracking pipelines, review how to sync GA4 BigQuery data to Salesforce for attribution to ensure offline revenue and lead statuses align cleanly with online behavior.

Establish strict schema migration rules. Avoid dumping unstructured JSON payloads into raw variant columns without creating downstream view layers. Standardize timestamp formatting to UTC across all incoming pipelines to prevent cross-timezone attribution skew during modeling.

Step 2: Model Customer 360 and Identity Graphs in dbt

Raw data in Snowflake is messy, duplicated, and unorganized. dbt serves as the modeling engine that aggregates raw tables into clean dimensional models ready for activation.

1. Deterministic Identity Resolution

Build an identity graph model in dbt that links anonymous user activity to known customer profiles. You can achieve this by creating a mapping table that links persistent identifiers (such as CRM ID or SHA-256 hashed email) with volatile session identifiers (such as anonymous_id or client IDs).

When resolving identities across anonymous browser sessions and authenticated account records, account for cross-device stitching rules to prevent inflated user counts. Inspect our detailed guide to fix GA4 User-ID cross-device tracking discrepancies in BigQuery for identity graph mapping patterns that apply directly to Snowflake SQL models.

2. Aggregating Customer Metrics

Create a centralized dbt model named dim_customers. This table will compile every key business attribute into a single row per individual customer. Here is a simplified SQL architecture demonstrating how to model churn risk, lifetime spend, and consent status inside dbt:

-- models/marts/dim_customers.sql

WITH raw_users AS (
    SELECT user_id, email, created_at, consent_marketing
    FROM {{ ref('stg_crm_users') }}
),

transaction_metrics AS (
    SELECT 
        user_id,
        COUNT(order_id) AS total_orders,
        SUM(order_value_usd) AS lifetime_value,
        MAX(created_at) AS last_purchase_timestamp
    FROM {{ ref('fct_orders') }}
    GROUP BY 1
),

web_engagement AS (
    SELECT 
        user_id,
        MAX(event_timestamp) AS last_active_timestamp
    FROM {{ ref('stg_web_events') }}
    WHERE user_id IS NOT NULL
    GROUP BY 1
)

SELECT
    u.user_id,
    LOWER(TRIM(u.email)) AS email_raw,
    SHA2(LOWER(TRIM(u.email)), 256) AS email_sha256,
    u.created_at,
    u.consent_marketing,
    COALESCE(t.total_orders, 0) AS total_orders,
    COALESCE(t.lifetime_value, 0.00) AS lifetime_value,
    t.last_purchase_timestamp,
    w.last_active_timestamp,
    CASE 
        WHEN t.lifetime_value > 1000 THEN TRUE 
        ELSE FALSE 
    END AS is_high_value_customer,
    CASE 
        WHEN w.last_active_timestamp < DATEADD('day', -60, CURRENT_TIMESTAMP()) THEN TRUE 
        ELSE FALSE 
    END AS is_at_risk_churn
FROM raw_users u
LEFT JOIN transaction_metrics t ON u.user_id = t.user_id
LEFT JOIN web_engagement w ON u.user_id = w.user_id
WHERE u.consent_marketing = TRUE;

This dbt model transforms fragmented raw tables into an actionable customer dimensional table. Every time dbt runs—whether on a 15-minute cron or a daily dbt Cloud schedule—it updates customer traits dynamically.

Step 3: Operationalize Audiences via Reverse ETL

Once dbt updates dim_customers inside Snowflake, Reverse ETL tools bridge the gap between your data warehouse and downstream marketing tools. Platforms like Hightouch or Census use native Snowflake connectors to poll for changes and sync data directly to destination APIs.

Configuring Reverse ETL Destinations

Set up syncs within your Reverse ETL UI using the following steps:

1. Connect Warehouse: Provide Reverse ETL read access to your production Snowflake schema using a dedicated virtual warehouse (e.g., REVERSE_ETL_WH).
2. Define Source Query: Select your dbt model (e.g., SELECT * FROM ANALYTICS_PROD.DIM_CUSTOMERS WHERE is_high_value_customer = TRUE).
3. Choose Destination: Select the target ad platform or CRM (Google Ads Customer Match, Meta Conversions API / Custom Audiences, Braze, or Salesforce Marketing Cloud).
4. Map Identifiers: Map email_sha256 to Meta's or Google's identifier parameters. Map internal metrics (such as lifetime_value) to custom profile attributes in Braze or HubSpot.
5. Select Sync Strategy: Choose between "Upsert" (adds new record or updates existing traits) or "Mirror" (dynamically adds and removes users from target lists based on SQL results).

To capture direct user preferences for advanced audience segmenting, weave interactive data points straight into your marketing campaigns. Implement a zero-party data strategy in SFMC with interactive forms to capture customer preferences directly and pass them into Snowflake for instant Reverse ETL mapping.

For ad platform targeting, use dynamic list mirroring. When a customer's is_at_risk_churn flag flips from FALSE to TRUE in dbt, the Reverse ETL tool automatically adds that identifier to a "Winback" campaign in Meta Ads within minutes. As soon as the user completes a transaction, dbt updates their profile, and the Reverse ETL tool suppresses them from the ad target list, eliminating wasted ad spend.

Step 4: Establish Governance, Consent, and Performance Controls

A composable CDP provides unmatched architectural control, but it requires data teams to manage governance, compliance, and cloud costs intentionally.

1. Consent Management and Privacy Enforcement

Unlike packaged CDPs that attempt to manage consent using proprietary cookies, a composable CDP lets you enforce consent programmatically inside Snowflake SQL code. Ensure that your dbt staging models apply hard filters based on user opt-in tables before exposure to activation layers.

Always format PII values using SHA-256 cryptographic hashing inside dbt models prior to sending identifiers to advertising platforms like Google Ads, Meta, or TikTok. Store raw PII only in restricted, role-access schemas within Snowflake.

2. Snowflake Warehouse Cost Optimization

Reverse ETL syncs can consume significant Snowflake compute if not properly managed. Follow these cost-containment measures:

• Dedicated Virtual Warehouses: Create an isolated X-SMALL virtual warehouse specifically for Reverse ETL queries. Set AUTO_SUSPEND = 60 seconds to prevent idle compute spending.
• Incremental Modeling in dbt: Use dbt's incremental materialized strategy so models only reprocess records updated since the last run, rather than rebuilding full tables.
• CDC (Change Data Capture) Syncs: Leverage Reverse ETL delta syncs. Instead of re-uploading 5 million rows to Google Ads every hour, configured Reverse ETL tools only send the incremental 1,200 rows that changed since the last execution.

3. Monitoring and Failover Alerting

Set up automated monitoring for API rate limits and data type failures. If an ad network API drops connection, your Reverse ETL tool should trigger Slack or PagerDuty alerts. Because the state remains securely stored inside Snowflake, retrying failed sync batch jobs will never corrupt downstream customer records or cause missing conversion records.

Frequently Asked Questions

How much does a composable CDP cost compared to a packaged CDP?

Packaged CDPs often cost between $100,000 and $300,000+ per year based on event volume or monthly tracked users. A composable CDP leverages infrastructure you likely already pay for (Snowflake and dbt), with Reverse ETL platform pricing typically scaling per connector, often resulting in a 40% to 70% reduction in total cost of ownership.

Can a composable CDP support real-time streaming triggers?

Yes. By pairing streaming ingestion engines (like RudderStack or Snowplow) with Snowflake micro-batching or Reverse ETL platforms that support stream-based execution, you can trigger operational messages (such as password reset emails or transactional abandoned cart pushes) in under 60 seconds.

How do you handle identity resolution without built-in packaged CDP features?

Identity resolution is handled transparently inside SQL using dbt. You can build deterministic matching rules using primary keys (e.g., matching authenticated user_id to email hashes) or leverage open-source identity packages (such as dbt-expectations or specialized entity-resolution dbt packages) to merge identity graph nodes programmatically.

What is the primary difference between traditional ETL and Reverse ETL?

Traditional ETL (Extract, Transform, Load) extracts operational data from transactional databases and APIs to load it into a central data warehouse. Reverse ETL extracts modeled analytics data from the central data warehouse and writes it out into operational SaaS applications, ad channels, and CRMs.