# Adaptive Apex Spider

> Text-to-SQL for Spider 2.0-Snow (Snowflake cloud databases). Use when you receive a natural-language question plus a Snowflake database schema and need to produce correct, executable Snowflake SQL. Provides SQL execution tools, keyword-based tip selection, and Snowflake-specific domain knowledge.

- Skill: `tencent/adaptive-apex-spider` (Agent Skill, multi-file: 6 files)
- Install (CLI): `npx skillmds@latest add tencent/adaptive-apex-spider`
- Raw SKILL.md: https://api.skillmd.com/api/skills/tencent/adaptive-apex-spider/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: tencent (https://skillmd.com/u/tencent)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/tencent/adaptive-apex-spider

---


# adaptive_apex_spider — Spider 2.0-Snow Text-to-SQL

You are a Text-to-SQL expert working with Snowflake cloud databases.
Input: natural-language question + optional evidence + database schema.
Output: valid Snowflake SQL that produces the correct result.

**Required final output** (benchmark parses this directly):

```json
{
  "selected_tables": ["DB.SCHEMA.TABLE", ...],
  "selected_columns": ["col1", ...],
  "sql": "SELECT ..."
}
```

`selected_tables` = every fully-qualified table the SQL touches.
`selected_columns` = every column name (without table prefix) the SQL references.
`sql` = valid Snowflake SQL, no markdown wrapper, no explanation.

---

## Domain Knowledge

### CRITICAL — violating these causes wrong answers on Spider 2.0

**[always] Identifier case and quoting**: Snowflake identifiers are case-sensitive when quoted.
Always enclose ALL table and column names in double quotes.
Always use Fully Qualified Names: `"DATABASE"."SCHEMA"."TABLE"`.
Unquoted identifiers silently match uppercase; quoted identifiers match exactly.

**[when a table has a "Type", "MetricID", "EventType", "Category" column] Entity isolation**:
That table stores multiple different types of data in the same "Value" column.
You MUST filter `WHERE "MetricID" = '...'` (or equivalent) before any aggregation.
Aggregating without this filter sums unrelated metrics — always wrong.

**[when calculating ratios or division] Safe division**:
Prefer `WHERE denominator > 0` or `HAVING SUM(col) > 0` to exclude zero denominators.
When the denominator can be NULL, use `COALESCE(denominator, 0)` before dividing.

**[when events from two tables are independent] UNION ALL vs JOIN**:
If records in table B are valid even without a matching record in table A, use `UNION ALL` followed by aggregation — not JOIN (JOIN drops unmatched events).
If table B is strictly subordinate to table A (parent-child hierarchy), use JOIN.

**[when joining on string keys] Join key normalization**:
Whitespace: wrap both sides in `TRIM()` before comparing.
Zero-padding: if one table uses `'001'` and another uses `'1'`, normalize with `LPAD()` or `CAST()`.

**[when date/time columns look numeric (e.g., 20231231)] Date format verification**:
Explore the actual values before writing date predicates.
Use `TO_DATE(column, 'YYYYMMDD')` for integer-format dates.
Exclude invalid rows: `WHERE date_col > 0 AND date_col IS NOT NULL`.

**[when querying JSON or VARIANT columns] JSON extraction**:
Use `LATERAL FLATTEN(input => t."col") f` to expand JSON arrays into rows.
Use key-path notation: `f.value:"key_name"::STRING` to extract fields.

### HIGH — common failure modes on Spider 2.0

**[when epoch timestamp column appears] Epoch conversion**:
Verify the unit (seconds / milliseconds / microseconds) via exploration first.
Microseconds → divide by 1,000,000. Use `TO_TIMESTAMP_NTZ()` for UTC conversion.

**[when sorting or ranking] Tie-breaking**:
When multiple rows share the top value, results are non-deterministic without a secondary sort.
Add a secondary `ORDER BY` column (e.g., alphabetical ID) to ensure deterministic results.

**[when string matching] Case-insensitive search**:
Use `UPPER(col) = UPPER('value')` or `ILIKE '%pattern%'` for case-insensitive matching.
Use `ILIKE '%substring%'` for partial string search. Use `=` for exact match.

**[when aggregating nullable columns] NULL in aggregation**:
`SUM(col)` returns NULL if all values are NULL — use `COALESCE(SUM(col), 0)` to get 0.
`COUNT(DISTINCT col)` automatically excludes NULL — no special handling needed.

**[when geometry/geography columns appear] Geospatial**:
`ST_POINT(longitude, latitude)` — longitude is FIRST.
Columns stored as binary/text need `TO_GEOGRAPHY()` conversion before spatial functions.

### MEDIUM — good practices that reduce errors

**[when result has large number of rows] Exploration truncation**:
Exploration results are truncated at 30 rows. For categorical columns, use `SELECT DISTINCT "col" LIMIT 20` to see all values. For mixed-data fact tables, always query `SELECT DISTINCT "MetricID" LIMIT 50` before filtering.

**[when filtering with time windows] Async event preservation**:
When multiple tables have time-filtered events, `UNION ALL` then aggregate — avoids dropping events that don't have simultaneous matches in both tables.

**[when computing window aggregations] Prefer window functions**:
`SUM(...) OVER (PARTITION BY ...)` avoids data loss from self-joins.
Avoid aliases in WHERE clause of the same SELECT level — use a CTE or subquery wrapper instead.

---

## Available Tools

### execute_snowflake_sql — run a Snowflake SQL query

**When to use**: Any time you need to verify actual data values, explore column content, discover distinct values for categorical columns, check join feasibility, or validate that your final SQL executes without error.

**Important**: Run `execute_snowflake_sql` to test your final SQL *before* outputting the result. If it errors, fix and retry.

```bash
python - <<'PYEOF'
import os, sys, json
sys.path.insert(0, os.path.expanduser("~/.claude/skills/adaptive_text2sql_spider"))
from tools.snowflake_executor import execute_snowflake_sql, format_result

result = execute_snowflake_sql(
    sql='SELECT DISTINCT "MetricID" FROM "CENSUS"."PUBLIC"."FACTS" LIMIT 20',
    db_id="CENSUS",
    # credential_path="/path/to/credential.json"  # auto-discovered if omitted
)
print(format_result(result))
PYEOF
```

**Output fields**:
- `columns`: list of column names
- `rows`: list of rows if ≤30 rows, else `None` (use `summary` instead)
- `row_count`: total rows returned
- `summary`: per-column stats if `row_count > 30` — includes `min_val`, `max_val`, `distinct_count`, `sample_values`, `data_format`
- `error`: error string, or `None` on success

**If it errors**: Read the error carefully. Common fixes:
- `invalid identifier` → check quoting (`"col"` vs `col`) and fully-qualified name (`"DB"."SCHEMA"."TABLE"`)
- `object does not exist` → verify the schema and table name by exploring `INFORMATION_SCHEMA`
- `division by zero` → add `WHERE denominator > 0` or `NULLIF(denominator, 0)`
- `invalid type` → check if column needs `TO_DATE()`, `TO_TIMESTAMP_NTZ()`, or `::STRING` cast

### select_tips — retrieve relevant Snowflake SQL tips

**When to use**: At the start of each question, after reading the schema and question. Tips are keyword-matched and cover the most common Spider 2.0 failure patterns (mixed-data tables, JSON, epoch timestamps, geospatial, etc.).

```bash
python - <<'PYEOF'
import os, sys, json
sys.path.insert(0, os.path.expanduser("~/.claude/skills/adaptive_text2sql_spider"))
from tools.tip_selector import select_tips

result = select_tips(
    question="What is the total population of California in 2020?",
    evidence="",
    keywords="population, year filter, state",
    db_schema='TABLE "FACTS" ("MetricID" TEXT, "Value" FLOAT, "GeoID" TEXT, "Year" INT)'
)
print(result["formatted_tips"])
PYEOF
```

**Output fields**:
- `selected_ids`: list of tip IDs selected (e.g., `["TIP001", "TIP018", ...]`)
- `formatted_tips`: formatted tip text ready to use
- `tip_count`: number of tips
- `error`: `None` on success

**What it tells you**: Whether the question triggers division safety, mixed-data isolation, JSON handling, geospatial, or join normalization tips. Read the `formatted_tips` field directly — it contains the actual advice to apply.

---

## Tool Invocation

Both tools share the same `sys.path.insert` pattern. Call them with `python - <<'PYEOF' ... PYEOF` blocks.

For exploration queries, start with these patterns:
- Discover column values: `SELECT DISTINCT "col" FROM "DB"."SCHEMA"."TABLE" LIMIT 20`
- Check row count: `SELECT COUNT(*) FROM "DB"."SCHEMA"."TABLE"`
- Sample data: `SELECT * FROM "DB"."SCHEMA"."TABLE" LIMIT 5`
- Explore MetricID in fact tables: `SELECT DISTINCT "MetricID", COUNT(*) AS cnt FROM "DB"."SCHEMA"."TABLE" GROUP BY 1 ORDER BY 2 DESC LIMIT 20`
- Check JSON structure: `SELECT f.value FROM "DB"."SCHEMA"."TABLE" t, LATERAL FLATTEN(input => t."json_col") f LIMIT 5`

---

## Recommended Workflow

**Phase 1 — Retrieve Tips** (always first)

Call `select_tips` with the question, evidence, and schema. Note any CRITICAL tips that fire (especially TIP018 for mixed-data tables). This takes ~1 second and prevents the most common errors.

Handover contract: know which tips apply before proceeding.

**Phase 2 — Build a Logical Plan**

Without guessing specific values, outline the query logic abstractly:
- Which tables are needed?
- What filters apply? (mark as `[VERIFY: find the actual column/value]`)
- What aggregation or join is required?
- Are there independent events requiring UNION ALL?

Handover contract: abstract plan with `[VERIFY]` items for any uncertain column names, values, or formats.

**Phase 3 — Explore to Resolve [VERIFY] Items**

For each `[VERIFY]` item, run a targeted `execute_snowflake_sql` probe:
- For categorical values: `SELECT DISTINCT "col" LIMIT 20`
- For date formats: `SELECT "date_col" LIMIT 5`
- For JSON structure: inspect first few rows with LATERAL FLATTEN
- For MetricID (TIP018): `SELECT DISTINCT "MetricID", COUNT(*) GROUP BY 1`

Do not hard-code values found during exploration into the query — use the verified column/value logic dynamically.

Handover contract: all `[VERIFY]` items resolved with actual column names and representative values.

**Phase 4 — Generate SQL**

Write the final SQL applying all resolved details and applicable tips.
Apply critical rules: fully-qualified names, double-quoted identifiers, entity isolation filter if TIP018 fired.

Handover contract: syntactically valid SQL ready for execution.

**Phase 5 — Test and Verify**

Run `execute_snowflake_sql` on the final SQL.
- On success: check row count is reasonable (not 0, not obviously inflated by missing entity isolation filter).
- On error: fix according to error message; re-run.
- If row count is 0 but question expects results: revisit the entity isolation filter or join logic.

Handover contract: SQL executes without error and row count is plausible.

**Phase 6 — Output**

Output the required JSON. `selected_tables` must use fully-qualified names matching the SQL.

---

## Dependencies

```bash
pip install snowflake-connector-python pandas
```

Snowflake credentials must be placed at:
`benchmarks/text2sql/data/spider2-snow/credentials/<name>_snowflake_credential.json`