> ## Documentation Index
> Fetch the complete documentation index at: https://ritika-fa61c43f.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# CRM Data Sources: Schemas, Formats, and Corruption Patterns

> Reference for all four CRM data sources — web_forms, salesforce, web_leads, and legacy_db — schemas, column types, and per-source corruption patterns.

Every episode in the CRM Data Pipeline Environment exposes one or more named data sources through the `available_sources` field of the observation. Each source is a distinct in-memory Pandas DataFrame with its own column schema, data quality characteristics, and corruption patterns introduced by the chaos engine. Understanding which sources appear in which tasks — and exactly how their data is distorted — is the prerequisite for building a reliable cleaning pipeline.

## Source Availability by Task

The three task tiers progressively introduce more sources and more complex inter-source conflicts. Use this table as a quick reference when deciding which sources to load and clean for a given episode.

| Task ID | Sources Available | Pipeline Objective |
| - | - | - |
| `t1` | `web_forms` | Normalize, deduplicate, and drop bots from a single file |
| `t2` | `web_forms`, `legacy_db` | Merge two sources; resolve duplicates and missing data |
| `t3` | `salesforce`, `web_leads`, `legacy_db` | Three-way merge with explicit conflict resolution rules |

> [!NOTE]
>
> At the start of each episode the environment sets `available_sources` in the observation to exactly these source names. Pass the exact name string (e.g., `"web_forms"`) as the `source` field in every action payload.

***

## web\_forms

`web_forms` is the primary source in Tasks 1 and 2. It is produced by running `create_base_truth()` through the full `correlate_truth()` chaos engine, then appending synthetic bot rows. Expect severe corruption across every column.

### Schema

| Column | Type | Corruption Patterns |
| - | - | - |
| `customer_id` | string | Legitimate rows use `CUST_NNNN` (e.g., `CUST_1042`). Bot rows use `BOT{NNNNN}` (e.g., `BOT73214`). No numeric chaos — the value is either well-formed or a bot sentinel. |
| `name` | string | 50 % chance of leading/trailing whitespace (`"  Jane Doe  "`). 50 % chance of full uppercase (`"JANE DOE"`). Fuzzy duplicate rows (≈25 % chance) receive a different first name but the same last name. |
| `email` | string | 20 % chance of `None`. When present: 50 % chance of full uppercase (`"JANE.DOE@GMAIL.COM"`). Bot rows use a known bulk-mail domain. |
| `signup_date` | string | Four distinct corrupt forms — see Date Chaos below. |
| `phone` | string | 20 % chance of `None`. When present: three distinct corrupt forms — see Phone Chaos below. Bot rows always contain `"+00000000000"`. |

### Date Chaos — signup\_date

The chaos engine applies one of four mutations to every `signup_date` value based on a uniform random draw:

| Probability Range | Result | Example |
| - | - | - |
| `random > 0.85` | Completely unparseable sentinel | `"??/??/????"` |
| `0.70 < random ≤ 0.85` | Invalid month 13 | `"2023-13-12"` |
| `0.55 < random ≤ 0.70` | Null — field dropped entirely | `None` |
| `random ≤ 0.55` | Slash-separated date | `"2023/05/15"` |

> [!TIP]
>
> The `TO_DATETIME_ISO` standardization strategy handles all four cases: it parses valid formats to `YYYY-MM-DD`, and maps `"??/??/????"`, invalid months, and `None` to an empty string `""`.

### Phone Chaos — phone

When a phone value is present (80 % of rows), the engine applies one of three mutations:

| Probability Range | Result | Example |
| - | - | - |
| `random > 0.85` | Extension suffix appended | `"15551234567 ext. 42"` |
| `0.70 < random ≤ 0.85` | US parenthetical format | `"(155) 512-34567"` |
| `0.55 < random ≤ 0.70` | Literal bad string | `"Invalid Number"` |
| `random ≤ 0.55` | Stripped of `+` prefix only | `"15551234567"` |

***

## salesforce

`salesforce` appears only in **Task 3**. It is the canonical source for `email` and `customer_id` according to the task's `CONFLICT_RULES`. The data generator samples 90 % of the base truth rows into Salesforce, so up to 10 % of real customers may be absent from this source.

### Schema

| Column | Type | Notes |
| - | - | - |
| `customer_id` | string | `CUST_NNNN` format. Always the authoritative customer ID per conflict rules. Bot rows use `BOT{NNNNN}`. |
| `email` | string | Lowercase, canonical. This is the field you should trust and join on from other sources. |
| `phone` | string | E.164 format when present. Populated for \~70 % of rows (`random > 0.3` in the generator). `None` otherwise. Bot rows always have `"+00000000000"`. |

> [!NOTE]
>
> `salesforce` does **not** go through `correlate_truth()`. Its data is cleaner than `web_forms`, but it still contains bot rows and can have `None` phone values.

***

## legacy\_db

`legacy_db` appears in both **Task 2** and **Task 3**, but its schema differs between the two tasks.

### Task 2 Schema

In Task 2, `legacy_db` is generated from a second independent call to `create_base_truth(20)` followed by the full `correlate_truth()` chaos engine. Its schema is **identical to `web_forms`**:

| Column | Type | Corruption Patterns |
| - | - | - |
| `customer_id` | string | `CUST_NNNN` or `BOT{NNNNN}` |
| `name` | string | Uppercase, extra whitespace, fuzzy duplicates |
| `email` | string | Uppercase, 20 % `None` |
| `signup_date` | string | All four date chaos patterns |
| `phone` | string | All three phone chaos patterns, 20 % `None` |

### Task 3 Schema

In Task 3, `legacy_db` is generated directly from the base truth using a different mapping that preserves old enterprise conventions. It is sampled at **85 %** of the 60 base rows.

| Column | Type | Notes |
| - | - | - |
| `legacy_id` | string | `OLD-NNNN` format (e.g., `OLD-1042`), derived by replacing `CUST_` with `OLD-`. Bot rows use `BOT{NNNNN}`. |
| `contact_email` | string | **Full uppercase** (e.g., `"JANE.DOE@GMAIL.COM"`). Requires `LOWERCASE_STRIP` before joining against other sources. Bot rows use `"BULK@{DOMAIN.UPPER()}"`. |
| `home_phone` | string | May be valid E.164. Sourced from the base truth phone without chaos mutations. Bot rows always `"+00000000000"`. |

> [!WARNING]
>
> In Task 3, `legacy_db` has **no `customer_id` column** — only `legacy_id`. You cannot join it to other sources on `customer_id` directly. Join via `LOWER(contact_email)` matching against `salesforce.email` or `web_leads.email` instead.

***

## web\_leads

`web_leads` appears only in **Task 3**. It contains fresher phone data than Salesforce, making it the preferred source for the `phone` column per `CONFLICT_RULES`. The generator samples **80 %** of base truth rows, so 20 % of customers may be absent.

### Schema

| Column | Type | Notes |
| - | - | - |
| `customer_id` | string | Nullable — 20 % of rows have `None` customer ID. When present, uses `CUST_NNNN` format. |
| `email` | string | Lowercase, matches Salesforce canonical email. Use as the join key. |
| `phone` | string | Fresh phone number (intentionally different from the Salesforce phone). 60 % populated (`random > 0.4`). E.164 format when present. Bot rows have `None` phone. |

> [!TIP]
>
> The `CONFLICT_RULES` for Task 3 set `"phone": "prefer_web_leads"`. When building your SQL merge, use `COALESCE(web_leads.phone, salesforce.phone)` to implement this rule — `web_leads` phone takes precedence, falling back to Salesforce when the `web_leads` value is missing.

***

## Bot Injection Patterns

Both the easy/medium tasks and the hard task inject synthetic bot rows into every source. Bots are designed to look plausible at a glance — valid email format, reasonable-looking names — but carry several identifying signals.

### Bot Identifiers

| Signal | Value | Notes |
| - | - | - |
| `customer_id` | `BOT{NNNNN}` | Five-digit random suffix, no `CUST_` prefix. |
| `name` | One of four fixed strings | `"Support Team"`, `"Newsletter Bot"`, `"Promo Sender"`, `"Deals Alert"` |
| `email` domain | One of four bulk-mail domains | `promo-alerts.net`, `click-rewards.xyz`, `freewinners.info`, `bulk-mail.co` |
| `signup_date` | `"1970-01-01"` | Unix epoch — highly suspicious for a CRM signup. |
| `phone` | `"+00000000000"` | Eleven zeros — invalid real-world number. |

### Bot Counts per Task

| Task | Source | Bot Rows Injected |
| - | - | - |
| `t1` | `web_forms` | 8 |
| `t2` | `web_forms` | 8 (inherited from t1) |
| `t2` | `legacy_db` | 5 |
| `t3` | `salesforce` | 5 |
| `t3` | `web_leads` | 5 |
| `t3` | `legacy_db` | 5 |

### Filtering Bots in SQL

Use the known bot domains in a `WHERE` clause to exclude bot rows during SQL-based merges:

```sql theme={null}
WHERE email NOT LIKE '%promo-alerts.net%'
  AND email NOT LIKE '%click-rewards.xyz%'
  AND email NOT LIKE '%freewinners.info%'
  AND email NOT LIKE '%bulk-mail.co%'
```

For `legacy_db` in Task 3 where emails are uppercase, apply `LOWER()` first:

```sql theme={null}
WHERE LOWER(contact_email) NOT LIKE '%promo-alerts.net%'
  AND LOWER(contact_email) NOT LIKE '%click-rewards.xyz%'
  AND LOWER(contact_email) NOT LIKE '%freewinners.info%'
  AND LOWER(contact_email) NOT LIKE '%bulk-mail.co%'
```

> [!NOTE]
>
> You can also filter bots using `customer_id LIKE 'BOT%'` or `phone = '+00000000000'` or `signup_date = '1970-01-01'`. Combining multiple signals in a single `WHERE` clause produces the most robust removal.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.