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):
- The result DataFrame is stored as
self._sources[output_table] output_tableis appended toavailable_sourcesin the next observationlast_action_feedbackis set to:"Executed SQL successfully. Wrote N rows to '<output_table>'."- A reward of
+0.1is returned
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 ofavailable_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 withSELECT (case-insensitive, after stripping whitespace) is immediately rejected. Additionally, the following keywords are blocked anywhere in the query string:
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 fromweb_forms:
Filter by Bot Sentinels
Filter using multiple bot signals simultaneously for maximum coverage:Two-Source Join (Task 2)
Mergeweb_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 fullCONFLICT_RULES from data_generator.py:
customer_id: prefersalesforceemail: prefersalesforcephone: preferweb_leads(fresher data), fall back tosalesforce
WHERE clause using all four known bot domains:
Understanding the COALESCE(wl.phone, sf.phone) Pattern
TheCONFLICT_RULES for Task 3 declare "phone": "prefer_web_leads". The COALESCE(wl.phone, sf.phone) expression directly implements this rule:
- When
web_leads.phoneis non-null (present for ~60 % of web leads rows),COALESCEreturns the web leads value — the fresher phone number. - When
web_leads.phoneis null (40 % of web leads rows, or when the customer is absent from the 80 % sample),COALESCEfalls back tosalesforce.phone. - When both are null, the column is null — handle this with
HANDLE_MISSINGafterward.
Understanding the Bot-Filtering WHERE Clause
TheWHERE 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
- Pre-SQL Preparation
- Join Strategies
- Output Verification
- Chaining SQL
Always standardize and clean before SQL.SQL joins on raw, uncleaned columns silently produce incorrect results:
- An uncleaned
emailcolumn inlegacy_dbwith uppercase values will fail to join againstsalesforce.emailunless you useLOWER()in theONclause. - Columns containing the string literal
"None"(from pandas NaN → string conversion) require bothIS NOT NULLand!= 'None'checks. - Phone columns with
"Invalid Number"strings will survive into the output if not filtered.
LOWERCASE_STRIPon allemailcolumns across all sourcesEXTRACT_NUMBERS+PHONE_E164on allphonecolumnsTO_DATETIME_ISOon allsignup_datecolumnsPROFILE_SOURCEto 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>"