Event Schemas and Audience Compute in a Warehouse‑Native CDP: Modeling, Testing, and Idempotent Pipelines

Published by Bles Software, a custom software and AI company based in Yehud-Monoson, Israel, building web apps, AI agents and API integrations for clients in Israel, the US, the UK and the EU.

Event design and audience computation are the engine of a warehouse‑native CDP. If identity is the spine, events are the nerves that carry signals, and audiences are the muscles that act on those signals. This playbook drills deeply into how to design canonical event schemas, how to model and test them with dbt‑style patterns, and how to compute audiences that are explainable, incremental, and idempotent—so you can activate at scale from Snowflake or Databricks without duct tape.

We will anchor our discussion in search‑driven concepts teams explore—“customer 360 Snowflake,” “composable CDP,” and “reverse ETL”—and translate them into code and contracts you can run. By the end, you will have a blueprint for an event layer that supports analytics and activation equally well, and an audience engine that keeps promises to the business: clear semantics, bounded cost, and measurable freshness.

Principles of a canonical event schema

Your event schema is the lingua franca across analytics, product, marketing, and operations. To stay durable, design with five principles:

  1. Canonical nouns and verbs: choose a small set of event names that reflect domain concepts (e.g., account_created, user_signed_in, cart_viewed, checkout_completed, feature_used). Resist per‑team variations.
  2. Required envelope: every event row includes event_name, event_id (stable idempotency key), event_at (UTC), source, device, session_id, cluster_id/profile_id if known, and ingested_at. Avoid optional timestamps.
  3. Declarative payload: a properties JSON column for event‑specific fields, with a schema_version and contract for each event type. Keep high‑value properties normalized into columns for hot queries.
  4. Identity hooks: carry identifier hints (email, device id) even when not linked yet; the identity pipeline can backfill cluster_id later.
  5. Evolution discipline: changes happen via versioned schemas; deprecations have announced windows; telemetry includes a producer_version to catch stale SDKs.

These principles apply whether your source is Segment‑like tracking, product logs, or domain events from microservices.

Reference event tables

Organize events into a small number of tables keyed by time and idempotency:

Partition (or cluster) by event_date and, if supported, by event_name to accelerate typical queries. For Snowflake, cluster keys on (event_name, event_date) are common; for Databricks, partition directories by event_date.

Idempotency throughout the pipeline

Idempotency prevents duplicates when reprocessing or on retries. Enforce it at three levels:

  1. Ingestion: dedupe events_raw on event_id and source at write time.
  2. Canonicalization: in events_canonical, use MERGE or “insert unless exists” semantics keyed by event_id to prevent double normalization.
  3. Audience compute: eligibility tables are SCD2 with stable keys, so recomputation writes the same rows again (no ill effects) or closes/reopens windows deterministically.

Add a processed_at timestamp to each stage to trace when a specific event moved through the system.

Time zones, clocks, and ordering

Store event time in UTC (event_at) and also materialize a event_date derived column for partitioning. Maintain ingested_at (warehouse arrival time) and, when available, a source commit timestamp (from CDC). Audience logic should anchor to event_at to reflect the user’s action time but use ingested_at to measure latency and SLA.

Out‑of‑order events happen—especially with mobile telemetry and offline batching. Build audience windows with grace periods (e.g., allow a late checkout_completed to cancel a prior cart_viewed eligibility for up to 48 hours), and reconcile windows idempotently upon arrival.

Dimensions and keys

Dimensions give shape to events. Core dims for a warehouse‑native CDP:

Keys must be stable. For product entities, define surrogate keys for items whose natural keys drift (SKU re‑use, domain changes). For identity joins, prefer the cluster surrogate key from your identity pipeline.

Modeling with dbt‑style patterns

Organize transformation code with conventions that scale and remain readable:

Tests: at minimum, enforce unique keys (event_id), non‑nulls (critical envelope fields), and referential integrity between identity/artifacts and events.

Audience compute framework

An audience is a well‑defined set of profiles that satisfy a condition within a window and under consent and region policies. Implement a small framework with these elements:

  1. Registry: a table audience_registry listing audience_id, name, owner, description, sql_ref, purpose, default_window, version. The SQL reference points to the model or view that computes raw eligibility.
  2. Runner: a job that resolves the registry, executes each audience SQL with parameters (window start/end), and writes to audience_eligibility SCD2 table.
  3. Compactor: a job that closes stale windows (e.g., if a profile has been eligible for 90 days without action) and prunes or aggregates ledger rows for cost control.

Keep the audience SQL expressive but consistent. Example (Databricks‑flavored) for a PLG “feature adoption” audience:

INSERT INTO audience_eligibility
SELECT
  'aud_feature_adopted_v1' AS audience_id,
  p.profile_id,
  MIN(e.event_at) AS eligible_from,
  NULL AS eligible_to,
  to_json(named_struct('feature','report_builder','uses', COUNT(*))) AS reasons,
  cs.email_allowed AS consent_scope,
  p.region,
  current_timestamp() AS computed_at,
  'v1' AS version
FROM events_enriched e
JOIN id_profiles p ON p.cluster_id = e.cluster_id
JOIN current_consent cs ON cs.profile_id = p.profile_id
WHERE e.event_name = 'feature_used'
  AND e.properties['feature_key'] = 'report_builder'
  AND e.event_at >= date_sub(current_date(), 30)
  AND cs.email_allowed = TRUE
GROUP BY p.profile_id, cs.email_allowed, p.region;

This writes reasons that explain eligibility and enables destinations to render more personalized content (e.g., “show advanced templates to users who adopted the builder”).

Windows, change semantics, and closes

An audience must close when its condition ceases to hold (purchase, opt‑out, churn, entitlement change). Two patterns:

  1. Event‑driven close: create a job that watches for counter‑events (e.g., checkout_completed) and writes eligible_to for affected profiles.
  2. Recompute close: each eligibility run re‑computes the join condition and closes rows where the condition is now false. This is simpler and idempotent but heavier.

Favor recompute close unless the costs are prohibitive. With incremental windows (e.g., last 30 days) and partitioned eligibility, recompute is manageable.

Performance: making audiences fast and cheap

Performance work belongs in the data model, not in clever connector code.

Track cost per thousand eligible profiles and per destination to find expensive patterns and optimize.

Testing and validation at scale

Beyond dbt schema tests, add semantic checks:

Implement lightweight unit tests for complex macros using a tiny local DuckDB or Snowflake test schema with seeds. Store expected outputs as fixtures.

Consent, purpose, and region constraints

Carry consent state and region policy into the audience engine:

Consent: materialize current consent per profile and channel with valid_from/to so you can reconstruct what consent existed at compute time. Combine legal bases (opt‑in, contract) with channel rules.

Region constraints: mark profiles and events with data residency and legal entity. Some audiences cannot cross legal entities or regions. Enforce these with joins and explicit WHERE clauses, not by after‑the‑fact filters in connectors.

Purpose binding: annotate each audience with the processing purpose (e.g., “service,” “marketing”) and record it in eligibility rows. This makes audits straightforward and prevents purpose creep.

Edge cooperation without double brains

For sub‑minute decisions (web personalization), compute a small set of “fast traits” and publish to a cache (Redis, DynamoDB) with TTL. An edge worker evaluates a handful of rules and logs decisions back to the warehouse so eligibility can incorporate the fact. Keep the warehouse engine authoritative; the edge is a low‑latency projection.

Case study: multi‑brand, multi‑region ecommerce

A retailer running two brands across US and EU needs audiences that respect region residency and legal entities while allowing cross‑brand recognition when permitted. Event schema carries brand and region attributes; identity clusters encode legal entity. Audience engine computes brand‑specific abandoners and cross‑sell audiences only where consent and entity alignment permit. Eligibility diffs feed ESP, ads, and onsite personalization via connectors with idempotency keys. The team runs micro‑batches every five minutes and a nightly compaction. Result: a 10% lift in cross‑brand upsell in eligible regions and no audit findings on unlawful cross‑entity targeting.

Case study: B2B product usage and renewal risk

A SaaS vendor defines a renewal‑risk audience based on a 14‑day drop in feature usage and increased support tickets. Events from product telemetry and support systems normalize into events_enriched. A daily job computes rolling feature usage; an hourly job computes eligibility with reasons explaining which features dropped. Connectors tag accounts in CRM and open CS tasks. Over a quarter, churn in monitored cohorts declines by 8% with targeted outreach.

Implementation blueprint

Phase 1: schema and contracts

Publish the canonical event schema (names, envelope, required properties, schema versions). Set up ingestion with idempotency keys and minimal validation. Boot dbt with staging models and tests.

Phase 2: normalization and enrichment

Normalize events into events_canonical and enrich into events_enriched. Join identity outputs where available. Create reusable macros for time windows and feature rollups.

Phase 3: audience engine

Stand up the audience registry and runner. Build 2–3 founding audiences with explicit windows and consent enforcement. Write SCD2 eligibility and unit tests for determinism.

Phase 4: scale and optimize

Add pre‑aggregations and traits. Partition and cluster aggressively. Instrument cost per audience and destination. Build playbooks for backfill and recovery.

Pitfalls and durable fixes

Snowflake and Databricks specifics

Snowflake: Snowpipe Streaming ingests events quickly; Streams + Tasks drive micro‑batches. Use clustering on (event_name, event_date) and limit micro‑batch windows to control cost. Time travel simplifies backfills and audits of audience decisions.

Databricks: Auto Loader reads from cloud object stores as events land; Delta Change Data Feed powers incremental joins for eligibility. Optimize with Z‑ORDER on (event_name, event_date, cluster_id) and use Unity Catalog for PII governance and policy‑driven masking.

Security and privacy in the event layer

Minimize sensitive fields in event payloads; keep secrets out of telemetry. Use field‑level encryption where necessary and store keys in a KMS rather than in code. Add data classification tags to columns and enforce row‑ and column‑level permissions through the catalog. Maintain deletion pipelines that can locate all event rows linked to a profile for regulatory erasure.

Updated Best Practices (2025)

The bar has moved toward contract-first, near–real-time, privacy‑resilient pipelines. Teams modernizing in 2025 are converging on the following patterns.

Updated Best Practices (2025)

Recent Developments (2025)

Three shifts in 2025 are reshaping event schemas and audience compute in warehouse‑native CDPs.

  1. Open table formats moved from “nice to have” to the default contract. Snowflake made write support for externally managed Apache Iceberg tables generally available in October 2025, including partitioned writes and target file sizing—removing a common blocker for teams standardizing on Iceberg across engines and catalogs. For audience pipelines, this means you can persist canonical events once in Iceberg and query them from Snowflake, Spark, or Flink without bespoke copies. Treat Iceberg metadata (partition/spec, retention) as part of the event schema contract and test it alongside dbt models. (docs.snowflake.com)

  2. Streaming-to-table got simpler. Confluent’s Tableflow reached GA in March 2025 with native Iceberg support and early access for Delta Lake, plus integrations with Glue and Snowflake’s Open Catalog (Apache Polaris). Teams can now materialize Kafka topics directly as governed tables that update continuously, upstreaming schema enforcement and PII handling before data lands in the warehouse. For idempotency, use topic offsets + event_id as a composite key and configure table‑level MERGE semantics to guarantee exactly‑once writes. (nasdaq.com)

  3. The semantic layer consolidated. dbt Core 1.10 (June 16, 2025) introduced an upgrade path that many adapters adopted through the year; by October, dbt 1.11 entered beta, and dbt Labs open‑sourced MetricFlow at Coalesce 2025. For audience compute, define metrics and traits as semantic objects with enforced contracts, then have activation jobs pull compiled SQL rather than hand‑rolled queries—improving explainability and reducing drift between analytics and activation. (docs.getdbt.com)

Regulatory and market changes now require explicit consent and purpose fields in your event envelope:

Net effect: standardize on Iceberg for cross‑engine access, stream into governed tables, express audiences in a semantic layer, and promote consent/purpose fields to required envelope columns—so recomputation stays idempotent and compliant while activation remains explainable.

FAQ

How many event names should we have?

Fewer is better. Most products can capture 20–40 canonical events covering lifecycle, commerce, and usage. Explosion of names usually signals missing dimensions; fold variability into dimensions and properties rather than new event types.

Do we need a tracking SDK or can we log from services?

Either can work. SDKs reduce drift in client events, while service logs capture back‑office actions. The key is enforcing the canonical envelope and schema versions at ingestion and running the same normalization for both.

How do we debug a user’s journey across systems?

Persist idempotency keys, session IDs, and cluster IDs wherever possible. Provide an internal “profile timeline” view that joins events, eligibility changes, and connector deliveries by profile id. The ability to replay a single profile end‑to‑end is invaluable.

How do we prevent rule divergence between analytics and activation?

Keep shared definitions (e.g., “active user”) in a macros library and reuse in analytics marts and audience SQL. Version these definitions and treat changes as migrations with impact analysis.

Won’t idempotent SCD2 eligibility explode table size?

It can if you do not compact. Run a nightly job that coalesces contiguous windows, prunes old versions, and aggregates reasons where appropriate. Retain detailed rows for 30–90 days and summaries thereafter.

How does this relate to “composable CDP” and “customer 360 Snowflake”?

This is the compute heart of a composable CDP: canonical events feed identity and audiences; audiences drive connectors; everything is SQL and code in your repo. “Customer 360 Snowflake” often starts as analytics; this blueprint turns it into an operational engine you can audit and evolve.

Designing event contracts with JSON Schema or OpenAPI

Contracts make implicit expectations explicit. Use JSON Schema to specify allowed shapes for the properties payload per event_name. Include types, required fields, enums, and pattern constraints. For service‑generated domain events, describe the envelope and payload with OpenAPI so producers and consumers share a single source of truth.

Validation can run at two points: at ingest (reject or quarantine invalid events) and at canonicalization (mark an event as valid=false with an error code). Favor quarantine for client telemetry to avoid losing data due to transient client bugs; favor hard rejection for back‑office service events where correctness matters more.

Schema evolution strategies

Change is inevitable. Introduce fields as optional in a new schema_version. Deprecate fields by emitting both old and new for a release window, then remove the old field only after downstream consumers switch. Record producer_version and schema_version so you can find stale producers and plan rollouts.

When renaming an event (e.g., order_submitted → checkout_completed), create an aliasing normalization rule so analytics and activation see one canonical name. Maintain a migration note in the registry so stakeholders can search history across names.

Backfills and rollbacks without data loss

Backfills occur when late data arrives or a modeling bug needs correction. Plan for them:

Rollback is the mirror image: revert to a prior version of a model, recompute affected tables in a shadow path, and promote after checks pass. Keep “model version → artifact version” mappings so changes are auditable.

Advanced audience windows and SQL patterns

Real use cases require nuanced time logic. A few patterns:

Sessionized sequences: find sequences like “viewed pricing, then invited teammates, then completed billing within 48 hours.” Use window functions and MATCH_RECOGNIZE (if available) or stepwise joins with grace windows.

Rolling cohorts: compute “users whose 7‑day active streak ended yesterday” using SUM over windows and QUALIFY clauses to isolate transitions.

Suppressions with recency: exclude profiles that received an email in the last 7 days. Maintain a per‑channel delivery log and join it with a simple event_at > DATEADD(day, 7, last_delivery_at) condition.

dbt unit testing patterns for macros

Complex macros (e.g., weekly retention or attribution) deserve unit tests. Create a tiny seed dataset that captures edge cases—timezone boundaries, missing fields, out‑of‑order events—and compare macro output to expected rows stored as seeds. Run these tests in CI on every change to the macros library.

Add a “golden audiences” test suite: for a small set of profiles, pin expected eligibility rows across changes in upstream models. This catches accidental semantic drift.

Developer workflow and reviews

Treat event and audience modeling like application code:

Benchmarking and cost modeling

Measure cost and runtime per model and per audience. Snowflake’s query history or Databricks’ job metrics reveal hot spots—usually large joins or full scans. Add model‑level budgets (e.g., “this audience should cost < $0.50 per 100k evaluations”) and redesign when you exceed them.

Compute “cost per thousand eligible profiles” and per destination “cost per thousand deliveries.” Combine these with business impact (conversion, revenue) to steer investment toward audiences that move the needle.

Migration when renaming events or changing semantics

Sometimes the business changes meaning (e.g., “activation” becomes tied to a new set of steps). Handle this as a versioned audience (aud_activation_v2) with a parallel run period. Keep v1 for reporting continuity while v2 powers activation. Sunset v1 after agreement with stakeholders. For the event name change, keep aliasing logic in canonicalization for a release window, then remove after consumers migrate.

End‑user explainability

Business owners must understand why someone is in or out of an audience. Persist human‑readable reasons in eligibility rows (e.g., “viewed pricing 3× in 7 days and invited 2 teammates”). Build a basic internal UI or a simple dashboard that shows: events timeline, current traits, open eligibility rows with reasons, and recent connector deliveries. Explainability converts skepticism into trust and accelerates adoption.

Expanded security notes

PII minimization: even in the warehouse, minimize PII in event payloads; prefer stable identifiers (profile id) and keep raw emails and phones in identity tables with stricter access policies. Encrypt sensitive fields in motion and at rest, and restrict decryption UDFs by role.

Access control: grant least privilege to modeling roles and read‑only access to analytics consumers. Use row‑level policies to restrict events by brand/region where legal entities require separation. Audit access continually.

The path to a shared truth

The payoff of a disciplined event and audience layer is a single definition of customer behavior that powers analytics and activation consistently. Product teams ship instrumentation against contracts; data teams evolve models with tests; marketing teams define audiences in code they can review; and connectors deliver with ledgers and SLAs. With this foundation, a “composable CDP” is simply your data platform expressing operational behaviors—not a separate black box.

Event quality monitoring

Bad events degrade both analytics and activation. Add monitors that:

Freshness: alert when ingested_at - event_at exceeds a threshold for a source, indicating client clock drift or ingest delays. Trend the distribution per source SDK version to find regressions.

Volume anomalies: detect sudden drops or spikes in key events (signups, purchases). Validate against expected seasonality to avoid false alarms. For spikes, investigate bot traffic; for drops, check SDK outages or schema rejections.

Field null rates: track required property null rates per event_name. If a property becomes frequently null, quarantine or adjust logic before eligibility breaks.

Contract violations: count and sample invalid events by error code. Share these weekly with producing teams and track time‑to‑resolution.

Practical alternatives to MATCH_RECOGNIZE

Not every warehouse supports MATCH_RECOGNIZE. Equivalent patterns:

Sequential self‑joins: chain subqueries that join the next step with a bounded window relative to the prior step’s timestamp.

Window flags: compute flags like has_pricing_view and has_invite in windows, then select profiles where the timeline order holds (using min/max timestamps) within the desired window.

Materialized sequences: precompute sequence states per session or profile into a small table, then use it for audiences. This can reduce repeated scans dramatically.

Data residency and multi‑region deployment

If you operate globally, separate event storage and eligibility by region. Keep EU data in EU regions and US data in US regions. Identity can either be region‑scoped (simpler governance) or cross‑region via hashed cross‑refs with legal approval. Activation connectors run per region and only access region data.

For Snowflake, provision accounts per region with data sharing for non‑PII aggregates. For Databricks, use multiple workspaces and Unity Catalog to enforce cross‑region guards. Document which audiences are global vs. regional and why.

Tooling tradeoffs: Snowflake vs. Databricks

Both platforms succeed for warehouse‑native CDPs, but tradeoffs exist:

Snowflake: excels at micro‑batch orchestrations with Streams + Tasks, strong SQL ergonomics, time travel, and simple cloning. UDF options (JavaScript, Python via Snowpark) cover most similarity and enrichment needs. Streaming via Snowpipe Streaming is improving, but true continuous processing is still effectively micro‑batch.

Databricks: shines for high‑volume streaming with Structured Streaming and Delta Live Tables, plus massive batch connected components for identity. Delta CDF simplifies incremental joins. Unity Catalog centralizes governance across workspaces. SQL performance is excellent, but many teams rely on a mix of SQL and PySpark.

Pick based on event velocity, team skill set, and existing investments. Many enterprises run both, using Snowflake for business modeling and Databricks for heavy streaming or ML; treat the event and audience contracts as the portability layer.

Onboarding new teams and documentation

Document the event taxonomy, contracts, and audience registry as a first‑class artifact in your repo. Provide a “how to add an event” guide and a “how to propose a new audience” template. Require producers to run JSON Schema validation locally (pre‑commit) and in CI to catch errors early. Hold monthly office hours for stakeholders to review new events and audiences and to retire stale ones.

Create starter notebooks for analysts and marketers that demonstrate how to query common event patterns, join to identity and traits, and preview audience membership over time. Lowering the barrier to exploration builds trust and increases the organization’s fluency with the warehouse‑native CDP.

Worked example: complex audience with rolling logic

To make the concepts concrete, consider an “upsell eligible” audience for a SaaS product: users on the Basic plan who used an advanced feature twice in the last 14 days, invited at least one teammate, and have not triggered a downgrade support ticket. We also require email consent. A Snowflake‑style SQL could be:

WITH recent_feature AS (
  SELECT cluster_id, COUNT(*) AS uses_14d
  FROM events_enriched
  WHERE event_name = 'feature_used'
    AND properties:feature_key = 'advanced_export'
    AND event_at >= DATEADD(day, -14, CURRENT_TIMESTAMP())
  GROUP BY cluster_id
),
recent_invites AS (
  SELECT cluster_id, MAX(event_at) AS last_invite_at
  FROM events_enriched
  WHERE event_name = 'teammate_invited'
    AND event_at >= DATEADD(day, -14, CURRENT_TIMESTAMP())
  GROUP BY cluster_id
),
bad_tickets AS (
  SELECT DISTINCT cluster_id
  FROM events_enriched
  WHERE event_name = 'support_ticket_created'
    AND properties:category = 'downgrade'
    AND event_at >= DATEADD(day, -30, CURRENT_TIMESTAMP())
)
INSERT INTO audience_eligibility
SELECT 'aud_upsell_basic_v1' AS audience_id,
       p.profile_id,
       MIN(e.event_at) AS eligible_from,
       NULL AS eligible_to,
       OBJECT_CONSTRUCT('uses_14d', rf.uses_14d, 'last_invite_at', ri.last_invite_at) AS reasons,
       cs.email_allowed AS consent_scope,
       p.region,
       CURRENT_TIMESTAMP() AS computed_at,
       'v1' AS version
FROM id_profiles p
JOIN traits_current t ON t.profile_id = p.profile_id AND t.plan = 'basic'
JOIN recent_feature rf ON rf.cluster_id = p.cluster_id AND rf.uses_14d >= 2
JOIN recent_invites ri ON ri.cluster_id = p.cluster_id
LEFT JOIN bad_tickets bt ON bt.cluster_id = p.cluster_id
JOIN current_consent cs ON cs.profile_id = p.profile_id AND cs.email_allowed = TRUE
JOIN events_enriched e ON e.cluster_id = p.cluster_id AND e.event_name = 'feature_used'
WHERE bt.cluster_id IS NULL
GROUP BY p.profile_id, cs.email_allowed, p.region, rf.uses_14d, ri.last_invite_at;

This definition is explicit, testable, and explains itself through the reasons object. If the business later changes what counts as “advanced feature,” you update one predicate, and eligibility rows reflect the new semantics on the next run while historical rows preserve the prior logic.

More Warehouse Native Cdp Playbooks from Bles Software