# Consumer Graph

<details>

<summary>Specifications</summary>

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

</details>

### 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)

```sql
-- 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)

```sql
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**

```sql
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)

```sql
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**

```sql
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:

```sql
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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.moonbrush.ai/moonbrush-core-features/consumer-graph.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.