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

# SQL Execution: Querying CRM DataFrames with EXECUTE_SQL

> Run SQL SELECT queries over CRM DataFrames using EXECUTE_SQL — covering joins, COALESCE conflict resolution, bot filtering, and safety guardrails.

The `EXECUTE_SQL` action lets you run arbitrary SQL `SELECT` queries directly over the environment's in-memory DataFrames. Under the hood, the environment writes every current source DataFrame into a temporary in-memory SQLite database using `df.to_sql()`, executes your query via `pd.read_sql_query()`, and stores the result as a new named DataFrame accessible through `available_sources`. This makes `EXECUTE_SQL` the most powerful single action in the pipeline — it can perform multi-source joins, filter bot rows, apply `COALESCE` conflict resolution, and produce a final merged output all in one step.

***

## Action Payload

`EXECUTE_SQL` requires two fields: `query` (the SQL string to execute) and `output_table` (the name to assign to the result DataFrame):

```json theme={null}
{
  "action_type": "EXECUTE_SQL",
  "query": "<SQL SELECT statement>",
  "output_table": "<name_for_result_table>"
}
```

After a successful execution:

* The result DataFrame is stored as `self._sources[output_table]`
* `output_table` is appended to `available_sources` in the next observation
* `last_action_feedback` is set to: `"Executed SQL successfully. Wrote N rows to '<output_table>'."`
* A reward of `+0.1` is returned

You can then query the newly created table in a subsequent `EXECUTE_SQL` call by referencing `output_table` as a table name in your next query.

***

## Available Table Names

Use the exact source names as SQL table names. These are always the keys of `available_sources` in the observation:

| Source Name | Available In | SQL Table Name |
| - | - | - |
| `web_forms` | Tasks 1, 2 | `web_forms` |
| `legacy_db` | Tasks 2, 3 | `legacy_db` |
| `salesforce` | Task 3 | `salesforce` |
| `web_leads` | Task 3 | `web_leads` |
| Any `output_table` you created | After `EXECUTE_SQL` | Use the exact name you specified |

> [!NOTE]
>
> The environment serializes all DataFrame columns to strings before loading into SQLite (for columns with `object` dtype). This means `None`/`NaN` values appear as the string `"None"` in SQL. Use `IS NOT NULL` combined with `!= 'None'` checks where needed, or standardize/handle missing values before running SQL.

***

## SQL Safety Guardrails

The environment enforces strict read-only SQL access. Any query that does not start with `SELECT ` (case-insensitive, after stripping whitespace) is immediately rejected. Additionally, the following keywords are blocked anywhere in the query string:

| Blocked Keyword | Reason |
| - | - |
| `DROP` | Prevents table deletion |
| `DELETE` | Prevents row deletion |
| `UPDATE` | Prevents in-place mutation |
| `INSERT` | Prevents data injection |
| `ALTER` | Prevents schema modification |
| `CREATE` | Prevents table creation |
| `TRUNCATE` | Prevents table clearing |

> [!WARNING]
>
> Attempting a non-SELECT query or a query containing a forbidden keyword returns a reward of `-0.1` and sets `last_action_feedback` to `"Blocked Action: Only SELECT queries are allowed for security."` or `"Blocked: Query contains forbidden keyword."` The environment does NOT apply the query — your DataFrames remain unchanged. Always start your query with `SELECT`.

All DataFrame mutations you need — filtering, joining, COALESCE, renaming — are achievable with pure `SELECT` statements. Use subqueries or CTEs if you need intermediate transformations.

***

## Query Examples

### Simple Column Filter

Remove rows with null emails and known bot-domain emails from `web_forms`:

```json theme={null}
{
  "action_type": "EXECUTE_SQL",
  "query": "SELECT * FROM web_forms WHERE email IS NOT NULL AND email != 'None' AND 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%'",
  "output_table": "web_forms_clean"
}
```

### Filter by Bot Sentinels

Filter using multiple bot signals simultaneously for maximum coverage:

```json theme={null}
{
  "action_type": "EXECUTE_SQL",
  "query": "SELECT * FROM web_forms WHERE customer_id NOT LIKE 'BOT%' AND phone != '+00000000000' AND signup_date != '1970-01-01'",
  "output_table": "web_forms_no_bots"
}
```

### Two-Source Join (Task 2)

Merge `web_forms` and `legacy_db` on normalized email using a `FULL OUTER JOIN`. Use `COALESCE` to prefer whichever source has a non-null value for each column:

```json theme={null}
{
  "action_type": "EXECUTE_SQL",
  "query": "SELECT COALESCE(w.customer_id, l.customer_id) AS customer_id, COALESCE(w.name, l.name) AS name, COALESCE(w.email, l.email) AS email, COALESCE(w.signup_date, l.signup_date) AS signup_date, COALESCE(w.phone, l.phone) AS phone FROM web_forms w FULL OUTER JOIN legacy_db l ON LOWER(w.email) = LOWER(l.email)",
  "output_table": "merged_output"
}
```

> [!NOTE]
>
> SQLite supports `FULL OUTER JOIN` syntax. Applying `LOWER()` in the `ON` clause is critical here because `legacy_db` emails may still be uppercase if you have not yet run `LOWERCASE_STRIP`. Always standardize email columns before SQL joins, or apply `LOWER()` in the join condition as a fallback.

### Three-Source Merge with Conflict Rules (Task 3)

The most complex query implements the full `CONFLICT_RULES` from `data_generator.py`:

* `customer_id`: prefer `salesforce`
* `email`: prefer `salesforce`
* `phone`: prefer `web_leads` (fresher data), fall back to `salesforce`

Bot rows are removed in the `WHERE` clause using all four known bot domains:

```json theme={null}
{
  "action_type": "EXECUTE_SQL",
  "query": "SELECT sf.customer_id, sf.email, COALESCE(wl.phone, sf.phone) AS phone FROM salesforce sf LEFT JOIN web_leads wl ON sf.email = wl.email LEFT JOIN legacy_db ld ON sf.email = LOWER(ld.contact_email) WHERE sf.email NOT LIKE '%promo-alerts.net%' AND sf.email NOT LIKE '%click-rewards.xyz%' AND sf.email NOT LIKE '%freewinners.info%' AND sf.email NOT LIKE '%bulk-mail.co%'",
  "output_table": "merged_output"
}
```

#### Understanding the COALESCE(wl.phone, sf.phone) Pattern

The `CONFLICT_RULES` for Task 3 declare `"phone": "prefer_web_leads"`. The `COALESCE(wl.phone, sf.phone)` expression directly implements this rule:

* When `web_leads.phone` is non-null (present for \~60 % of web leads rows), `COALESCE` returns the web leads value — the fresher phone number.
* When `web_leads.phone` is null (40 % of web leads rows, or when the customer is absent from the 80 % sample), `COALESCE` falls back to `salesforce.phone`.
* When both are null, the column is null — handle this with `HANDLE_MISSING` afterward.

#### Understanding the Bot-Filtering WHERE Clause

The `WHERE` clause filters on `sf.email` (the canonical email from Salesforce) using `NOT LIKE` pattern matching against all four bot domains. Because we are joining `salesforce` as the driving table with `LEFT JOIN`, filtering on `sf.email` removes the entire joined row — including any matching `web_leads` or `legacy_db` data for that bot.

For `legacy_db` bot rows that have no matching Salesforce row, they are excluded automatically because we use `LEFT JOIN` starting from `salesforce`. The `legacy_db` join here contributes no columns to the select — it is available if you want to `COALESCE` with `ld.home_phone` as a third fallback.

***

## Best Practices

> [tab] Pre-SQL Preparation
>
> **Always standardize and clean before SQL.**
>
> SQL joins on raw, uncleaned columns silently produce incorrect results:
>
> * An uncleaned `email` column in `legacy_db` with uppercase values will fail to join against `salesforce.email` unless you use `LOWER()` in the `ON` clause.
> * Columns containing the string literal `"None"` (from pandas NaN → string conversion) require both `IS NOT NULL` and `!= 'None'` checks.
> * Phone columns with `"Invalid Number"` strings will survive into the output if not filtered.
>
> Recommended pre-SQL checklist:
>
> 1. `LOWERCASE_STRIP` on all `email` columns across all sources
> 2. `EXTRACT_NUMBERS` + `PHONE_E164` on all `phone` columns
> 3. `TO_DATETIME_ISO` on all `signup_date` columns
> 4. `PROFILE_SOURCE` to verify null counts before writing the join query

> [tab] Join Strategies
>
> **Choose your join type deliberately.**
>
> | Join Type | Use When |
> | - | - |
> | `INNER JOIN` | You only want customers present in ALL sources. Loses customers absent from any source. |
> | `LEFT JOIN` (from Salesforce) | Salesforce is canonical; include all SF customers even if web\_leads or legacy\_db lack them. Recommended for Task 3. |
> | `FULL OUTER JOIN` | You want to keep customers from either source even without a match. Best for Task 2 two-source merge. |
>
> In Task 3, starting with `salesforce` as the left table in a `LEFT JOIN` is the correct choice because Salesforce is the `prefer_salesforce` source for `customer_id` and `email`. Customers absent from Salesforce's 90 % sample are not part of the expected truth output.

> [tab] Output Verification
>
> **Always verify your SQL output.**
>
> After every `EXECUTE_SQL` call, immediately run `VIEW_SOURCE` on the output table to inspect the first few rows:
>
> ```json theme={null}
> {
>   "action_type": "VIEW_SOURCE",
>   "source": "merged_output"
> }
> ```
>
> Check for:
>
> * Unexpected `None` string values in key columns
> * Bot rows that slipped through the `WHERE` filter
> * Duplicate rows from a many-to-many join scenario
> * Column names that differ from your target schema
>
> If the output looks wrong, create a refined query and write to the same `output_table` name — the environment will overwrite the previous result.

> [tab] Chaining SQL
>
> **Chain multiple SQL operations for complex transformations.**
>
> Because `output_table` becomes immediately queryable, you can chain SQL operations:
>
> ```json theme={null}
> {
>   "action_type": "EXECUTE_SQL",
>   "query": "SELECT * FROM salesforce WHERE email NOT LIKE '%promo-alerts.net%'",
>   "output_table": "sf_clean"
> }
> ```
>
> Then in a second step, join the cleaned intermediate table:
>
> ```json theme={null}
> {
>   "action_type": "EXECUTE_SQL",
>   "query": "SELECT sf.customer_id, sf.email, COALESCE(wl.phone, sf.phone) AS phone FROM sf_clean sf LEFT JOIN web_leads wl ON sf.email = wl.email",
>   "output_table": "merged_output"
> }
> ```
>
> This is especially useful when a single complex query becomes difficult to debug — break it into smaller, verifiable stages.

***

## SQL Error Handling

When a query fails (syntax error, invalid column name, etc.), the environment catches the exception and returns:

* Reward: `-0.1`
* `last_action_feedback`: `"SQL Error: <sqlite3 error message>"`

The failed query does not modify any existing DataFrames. Common error causes:

| Error | Likely Cause | Fix |
| - | - | - |
| `no such table: <name>` | Typo in source name, or the source was not yet created | Check `available_sources` in the observation |
| `no such column: <name>` | Column name mismatch between sources | Run `PROFILE_SOURCE` to see actual column names |
| `ambiguous column name: <col>` | Column exists in multiple joined tables without alias | Prefix with table alias: `sf.email` instead of `email` |
| `near "DELETE": syntax error` | Blocked keyword in query | Rewrite using only `SELECT` statements |

> [!TIP]
>
> Run `PROFILE_SOURCE` on every source before writing a SQL query. The profile output shows the exact column names and null counts, preventing `no such column` errors from schema mismatches between `web_forms` (which has `customer_id`) and `legacy_db` Task 3 (which has `legacy_id` and `contact_email` instead).


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