Consumer Graph

Specifications

Release Date: September 19, 2025 Total Rows: 289.9M Version: 3.1.8 File Size: 249.0 GB (parquet)

Purpose

A governed, person‑level record used to power analytics, measurement, and policy‑compliant segmentation. It consolidates identity, contact metadata, firmographics, location context, modeled psychographic/behavioral scores, and neighborhood indices. Activation against protected or sensitive attributes is blocked; those fields exist only for compliance auditing, fairness analysis, and model QA.

Row Granularity & Keys

  • Grain: one row per internal person (MOONBRUSH_ID).

  • Primary key: MOONBRUSH_ID (opaque, non‑PII). Recommended NOT NULL constraint in DDL.

  • Surrogate IDs: None. Use MOONBRUSH_ID for joins to decisioning, intent feed, and persona outputs.

Update Cadence & SLAs

  • Upserts: event‑driven plus scheduled batch compaction.

  • Timeliness targets: profile attributes ≤ 24h; streaming scores ≤ 1h; LAST_UPDATED always refreshed on write.

Schema Overview (Groups)

The full DDL is provided in engineering assets. Below is a grouped view of major columns and intent. Example columns listed are intentionally not exhaustive.

1) Identity & Contact (PII → masked)

  • MOONBRUSH_ID, FIRST_NAME, LAST_NAME

  • Emails: BUSINESS_EMAIL, ADDITIONAL_PERSONAL_EMAILS[], PROGRAMMATIC_BUSINESS_EMAILS[]

  • Phones: MOBILE_PHONE, DIRECT_NUMBER, PERSONAL_PHONE

  • Links: LINKEDIN_URL

  • Timestamps & statuses: BUSINESS_EMAIL_VALIDATION_STATUS, BUSINESS_EMAIL_LAST_SEEN, PERSONAL_EMAILS_VALIDATION_STATUS, PERSONAL_EMAILS_LAST_SEEN

2) Personal Location (PII → masked)

  • PERSONAL_ADDRESS, PERSONAL_ADDRESS_2, PERSONAL_CITY, PERSONAL_STATE, PERSONAL_ZIP, PERSONAL_ZIP4

  • Geo enrichments: METRO_NAME, COUNTY_NAME, COUNTY_FIPS, CBSA_FIPS, ZIPCODE_LAT, ZIPCODE_LONG, TIMEZONE_FROM_ZIP, DENSITY

3) Demographic Proxies (Governed)

  • GENDER, AGE_RANGE, MARRIED, CHILDREN, HOMEOWNER, INCOME_RANGE, NET_WORTH

4) Sensitive/Protected Attributes (Quarantined)

  • MODELED_ETHNICITY, POLITICAL_IDEOLOGY, RELIGIOUS_ALIGNMENT_SCORE, and related ideology/values scores. Policy: these fields are never used for targeting, bidding, or personalization decisions. They live in restricted views for fairness monitoring and compliance only.

5) Firmographics (B2B context)

  • Person role: JOB_TITLE, JOB_TITLE_NORMALIZED, SENIORITY_LEVEL, SENIORITY_LEVEL_2, DEPARTMENT, DEPARTMENT_2

  • Company: COMPANY_NAME, PRIMARY_INDUSTRY, COMPANY_DOMAIN, COMPANY_LINKEDIN_URL, COMPANY_PHONE, COMPANY_REVENUE, COMPANY_EMPLOYEE_COUNT, COMPANY_SIC, COMPANY_NAICS, COMPANY_ADDRESS*, COMPANY_COUNTRY

  • Change stamps: COMPANY_LAST_UPDATED, JOB_TITLE_LAST_UPDATED

6) Behavioral & Engagement Scores

  • Channel: EMAIL_ENGAGEMENT_SCORE, SMS_ENGAGEMENT_SCORE, DIGITAL_AD_ENGAGEMENT_SCORE

  • Shopping & subscription: SUBSCRIPTION_PURCHASE_SCORE, LOYALTY_PROGRAM_RESPONSIVENESS_SCORE, SUBSCRIPTION_FATIGUE_SCORE, COUPON_CONVERSION_SCORE

  • Device/context: MOBILE_DEVICE_ENGAGEMENT_SCORE, DESKTOP_DEVICE_ENGAGEMENT_SCORE, DAYPART_RESPONSIVENESS_SCORE, WEEKDAY_VS_WEEKEND_ACTIVITY_SCORE, RECENCY_FREQUENCY_WINDOW_RFW_SCORE

7) Psychographic/Decision‑Style Scores (modeled)

  • Examples: RISK_TOLERANCE_SCORE, NOVELTY_SEEKING_BEHAVIOR_SCORE, PRICE/QUALITY BIASES (e.g., BARGAIN_VS_QUALITY_BIAS_SCORE), AUTHORITY_RESPONSE_SCORE, SKEPTICISM_SCORE, RELATIONSHIP_RELIANCE_SCORE, TRUST_SIGNAL_SENSITIVITY_SCORE, DEAL_HUNTING_SCORE, BRAND_LOYALTY_SCORE, BRAND_SWITCHING_SCORE, ETHICAL_CONSUMPTION_SENSITIVITY_SCORE, PRIVACY_SENSITIVITY_SCORE, PURCHASE_RATIONALITY_SCORE, INFORMATION_OVERLOAD_TOLERANCE_SCORE, and others as defined in DDL.

8) Civic/Values/Ideation Indices (Governed)

  • Examples: CIVIC_ENGAGEMENT_INTENSITY_SCORE, POLITICAL_MOBILIZATION_SCORE, DEI_ALIGNMENT_SCORE, PATRIOTIC_ALIGNMENT_SCORE, ENVIRONMENTAL_RISK_AVERSION_SCORE. Use: monitoring/model QA only; blocked for activation.

9) Neighborhood & Geo‑Social Indices

  • Examples: LOCAL_OUTLIER_SCORE, NEIGHBORHOOD_HOMOGENEITY_SCORE, SOCIAL_EXPOSURE_SCORE, CULTURAL_AFFINITY_INDEX, COMMUNITY_VELOCITY_SCORE, CONFORMITY_PRESSURE_INDEX, GEO_SOCIAL_INFLUENCE_DENSITY, BEHAVIORAL_DISPARITY_SCORE, CIVIC_COHESION_SCORE, SPRAWL_INTENSITY_INDEX, GEO_ECONOMIC_PRESSURE_SCORE, LOCAL_IDEOLOGY_CONSISTENCY_SCORE.

10) Program Flags & Attributes

  • P0001…P0407 (char(1)) and A0001…A0102 (char(1)) represent binary program/attribute flags. Values are "Y"/"N" or "1"/"0" depending on upstream. The codebook defines each flag’s meaning and governance tier.

11) History & Graph Adjacent

  • Arrays: WORK_HISTORY[], EDUCATION_HISTORY[], RELATED_DOMAINS[], SOCIAL_CONNECTIONS[]

  • Free‑text: COMPANY_DESCRIPTION

12) Operational Columns

  • LAST_UPDATED (row timestamp), CC_ID, STATE_ID, STATE

Governance & Policy

  • Consent & Purpose Limiting: all writes require aligned purpose; downstream views enforce consent scopes.

  • Sensitive Attributes: segregated in quarantined schema/views; accessible only to approved roles for fairness, bias monitoring, and regulatory reporting. Not surfaced to decisioning APIs.

  • PII Masking: dynamic masking on emails, phones, and street addresses by role.

  • Row Access: regional/tenant scoping via row access policies.

Example (Masking & Row Policies — illustrative)

-- Mask emails/phones for non-privileged roles
CREATE OR REPLACE MASKING POLICY mp_mask_email AS (val STRING) RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() IN ('ANALYST_PII','ADMIN') THEN val ELSE REGEXP_REPLACE(val, '(^.).+(@.+$)', '\\1***\\2') END;

ALTER TABLE UNIVERSAL_PERSON_ENRICHED
  MODIFY COLUMN BUSINESS_EMAIL SET MASKING POLICY mp_mask_email;

-- Row Access by region consent (example predicate)
CREATE OR REPLACE ROW ACCESS POLICY rap_region AS (PERSONAL_STATE STRING) RETURNS BOOLEAN ->
  CURRENT_ROLE() IN ('ADMIN','ANALYST_US') AND PERSONAL_STATE IN ('CA','NY','TX');

ALTER TABLE UNIVERSAL_PERSON_ENRICHED ADD ROW ACCESS POLICY rap_region ON (PERSONAL_STATE);

The exact predicates/roles are maintained by security engineering and may differ by tenant, region, and operation.

Recommended Physical Design (SnowSQL)

  • Clustering: CLUSTER BY (MOONBRUSH_ID, LAST_UPDATED) for point lookups and incremental updates.

  • Search Optimization: enable on commonly filtered fields (e.g., COMPANY_DOMAIN, PERSONAL_ZIP, BUSINESS_EMAIL) for selective queries.

  • Micro‑batch Upserts: MERGE on MOONBRUSH_ID with LAST_UPDATED winner rules.

Example Upsert (illustrative)

MERGE INTO UNIVERSAL_PERSON_ENRICHED t
USING @stage/person_delta.parquet s
ON t.MOONBRUSH_ID = s.MOONBRUSH_ID
WHEN MATCHED AND s.LAST_UPDATED > t.LAST_UPDATED THEN UPDATE SET
  (FIRST_NAME, LAST_NAME, JOB_TITLE, LAST_UPDATED) = (s.FIRST_NAME, s.LAST_NAME, s.JOB_TITLE, s.LAST_UPDATED)
WHEN NOT MATCHED THEN INSERT (MOONBRUSH_ID, FIRST_NAME, LAST_NAME, JOB_TITLE, LAST_UPDATED)
VALUES (s.MOONBRUSH_ID, s.FIRST_NAME, s.LAST_NAME, s.JOB_TITLE, s.LAST_UPDATED);

Access Patterns

  • Person lookup

SELECT MOONBRUSH_ID, EMAIL_ENGAGEMENT_SCORE, BRAND_LOYALTY_SCORE, LAST_UPDATED
FROM UNIVERSAL_PERSON_ENRICHED
WHERE MOONBRUSH_ID = 'SUB-7f91b1c6';

  • Join to Intent Feed & Personas (safe view)

SELECT p.MOONBRUSH_ID, i.occurred_at, pr.persona_top, pr.p_top
FROM SAFE_PERSON_VIEW p
JOIN ENRICHED_INTENT_STREAM i USING (MOONBRUSH_ID)
JOIN PERSONA_ASSIGNMENTS pr USING (MOONBRUSH_ID)
WHERE i.occurred_at >= DATEADD(day, -7, CURRENT_TIMESTAMP());

  • Neighborhood index query

SELECT PERSONAL_ZIP, AVG(CIVIC_COHESION_SCORE) cc, AVG(SPRAWL_INTENSITY_INDEX) sprawl
FROM SAFE_PERSON_VIEW
GROUP BY 1
ORDER BY cc DESC;

Views & Exposure

To prevent accidental misuse, exposure is via tiered views:

  1. SAFE_PERSON_VIEW — drops PII, sensitive attributes, and all ideology/religion proxies; retains engagement and non‑sensitive psychographic scores.

  2. RESEARCH_PERSON_VIEW — access‑controlled; includes additional modeled features for R&D (no activation).

  3. QUARANTINED_PERSON_VIEW — restricted; includes sensitive attributes for fairness/QA only.

Illustrative creation:

CREATE OR REPLACE VIEW SAFE_PERSON_VIEW AS
SELECT
  MOONBRUSH_ID,
  EMAIL_ENGAGEMENT_SCORE, SMS_ENGAGEMENT_SCORE,
  BRAND_LOYALTY_SCORE, RISK_TOLERANCE_SCORE, NOVELTY_SEEKING_BEHAVIOR_SCORE,
  DAYPART_RESPONSIVENESS_SCORE, WEEKDAY_VS_WEEKEND_ACTIVITY_SCORE,
  -- ... (approved columns only)
  LAST_UPDATED
FROM UNIVERSAL_PERSON_ENRICHED;

Data Quality & Monitoring

  • Completeness: non‑null rate of MOONBRUSH_ID, freshness of LAST_UPDATED.

  • PII Hygiene: valid email/phone patterns, DPV codes for addresses.

  • Drift: distribution shifts on key scores; PSI/KS alerts.

  • Governance: audit trail of column access; query pol

PreviousWelcomeNextStandard Psychographic Models

Last updated