Skip to main content
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):
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:
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:
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:

Filter by Bot Sentinels

Filter using multiple bot signals simultaneously for maximum coverage:

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

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

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

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:
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).