Customer Data Platforms and Marketing Data Integration Questions

Requirements: Respect consent preferences across CDP and CRM, ensure suppression enforcement, and keep auditable consent records.

Storage & canonicalization:

• Central consent store (append-only) with records: subject_id, channel, consent_state, source, timestamp, policy_version, and jurisdiction tags. Store both raw and normalized consent events in the CDP.
• Expose derived materialized view for pipelines (current_consent_by_channel) with TTL and history links.

Propagation:

• On consent change: emit CDC/event (Kafka) to sync service. Consumers (CRM sync, campaign engines) subscribe and apply suppression rules immediately.
• Provide REST API for CRM to query current consent state and for one-off checks

Enforcement in pipelines:

• At sync-time compute effective_consent using rule engine (most-restrictive wins, recency, jurisdiction). Attach suppression flags to records and never send suppressed contacts to downstream marketing endpoints.
• For batch/stream jobs, apply consent filter as early as possible (ingest layer) to avoid storing or transmitting disallowed payloads.

Auditing & compliance:

• Record provenance and consent version on every sync action. Keep immutable logs for required retention.
• Implement soft-delete and masking in CRM when required by GDPR/CCPA with reversible tokens and deletion workflows.

Operational controls:

• Provide admin UI for consent overrides with audit trail; automate periodic reconciliation between CDP and CRM consent states; alert on drift.
• Test with synthetic scenarios and ensure eventual consistency windows are acceptable for legal requirements.

Monitoring & alerting layers

1) End-to-end latency

• Metric: ingestion_time -> visible_in_crm_time per record (p50/p95/p99). Alert if p95 > SLA threshold.

2) Data volume & drift

• Metric: daily/ hourly event counts by type vs baseline (rolling 7d). Alert on >=20% deviation or sustained drop.

3) Schema drift

• Metric: schema fingerprint/hash for incoming events; detect new/missing fields. Alert on field additions/removals or type changes.

4) Error rates

• Metric: failed deliveries per minute, retry queue length, poison message rate. Alert on >X errors/min or growing retry queue.

5) Data quality & completeness

• Metric: % of records with required keys (customer_id, timestamp). Alert on <99% completeness.

6) Business KPIs for stakeholders

• Marketing: leads synced per hour, time-to-first-sync for campaigns, percent of high-priority leads delivered within SLA.
• Sales: new accounts created, updates to contact ownership within 24h.

Alerting strategy

• Severity levels: P1 (end-to-end latency SLA breach, high error rates), P2 (data drift), P3 (minor schema changes).
• Escalation: automated paging for P1 with runbook links; P2/P3 to on-call and owners.

Dashboards & artifacts

• Real-time dashboard with latency histograms, delivery success trends, schema diffs, and business KPI tiles.
• Runbooks for remediation steps and playbooks for common failures (schema change, rate limit, downstream outage).

This mixes technical metrics for engineers and summarized business KPIs for marketing/sales to maintain trust and enable fast remediation.

Modeling account hierarchies & cross-account contacts for analytics

Approach:

• Canonical tables: accounts (account_id, parent_account_id, hierarchy_level) and contacts (contact_id, primary_account_id).
• Build a recursive hierarchy table/view (flattened rollup) using SQL (CTE or graph traversal) to resolve parent/ancestor chains for roll-up metrics.
• For cross-account contacts create a mapping table contact_account_links(contact_id, account_id, role, start_date, end_date, is_primary).

Roll-up logic:

• Aggregate contact/activity metrics by joining contact_account_links to the flattened account hierarchy, then group by ancestor account_id.
• Use windowed dedup rules to choose primary attribution (e.g., revenue gets attributed to primary account, activity can be multi-attributed with weights).

Implementation notes:

• Precompute and materialize roll-up views in the warehouse for performance.
• Maintain lineage and timestamps so historical roll-ups reflect the state at report time.

Canonical customer schema principles (Data Engineer view)

Naming & clarity:

• Use consistent, lowercase_snake_case, descriptive names (e.g., customer_id, email_primary).
• Prefix provenance for metadata (src_, src_timestamp).

Normalization vs denormalization:

• Normalize master entities (customers, accounts) for data integrity; denormalize curated analytical views/materialized tables for performance.

Versioning:

• Schema version field and backward-compatible additive changes. Use migration scripts and dataset version tags; keep previous schema snapshots.

Extensibility:

• Use flexible attribute store (key/value JSON column) for experimental fields, but keep core fields typed.

Other rules:

• Field ownership registry (which system is authoritative).
• Include provenance, confidence_score, consent flags, and last_updated timestamps.

These principles enable reliable ETL, reproducible analytics, and safe evolution of the customer model.

Goal
Synchronize lead scores from CDP to CRM with full auditability linking scores to source events and transformations.

Storage & lineage capture

• Raw events: append-only event store (Kafka + S3 for long-term).
• Feature/materialized scores: store in DW table and a score_history table with columns: lead_id, score, source_version, timestamp, contributing_events_reference.
• Contributing events reference: store as pointers (event ids) or aggregated fingerprints with Bloom filters and event counts.

Pipeline

1. Ingest events to Kafka; persist raw events to S3.
2. Streaming/Batch feature computation in Spark/Flink; emit intermediate feature provenance: which events (ids/time ranges) produced feature deltas.
3. Scoring job produces new score with a provenance blob (list of event ids, transformation version, model id, parameters). Write to score_history and push diff to CRM via idempotent API.

Lineage & queryability

• Index event-to-score mapping in a queryable store (Elasticsearch or DW mapping table) to answer queries like "which events contributed to score change X" by filtering event ids in provenance.
• Store model & transform artifacts versioned in model registry; link score rows to artifact ids.

Auditing & UI

• Provide a UI that shows a score timeline, underlying contributing events, and transformation versions; enable export of full provenance for audits.

This guarantees every score update is traceable to raw events and transformation logic, enabling reproducibility and investigation.