Data Question

Use the available read-only tools to answer the user's question. Source databases and project files stay unchanged; when exposed, sql_materialize may create session-local tables in the scratch warehouse. All examples below use synthetic tables. Adapt names and dialects after inspection. This skill is self-contained.

Workflow

1. Inspect. Use warehouse_list, schema_inspect and small sql_execute queries as needed. Inspect actual keys and text before choosing joins, casts or parsing rules: schemas alone hide whitespace, case, prefixes and mixed formats. Preserve meaningful leading zeros; check that normalization does not merge distinct identities.
2. Plan. State the population, row grain, denominator, units and material assumptions. Decide which joins and aggregation levels preserve them. Keep this brief; a trivial lookup does not need a checklist or several validation scans.
3. Compute in-engine. Filter and aggregate close to the source. Derive large intermediate sets with joins/CTEs or the scratch bridge below, rather than copying rows into IN (...) or VALUES. Small user-supplied constants are fine. Keep LIMIT, sampling and approximate functions exploratory unless the user requests an estimate, or an exact answer would need per-row model labeling of a large population (see "Batch semantic labels, then count"); then say the answer is an estimate. For exact final answers, aggregate the full intended population in SQL.
4. Verify relevant invariants. Check key uniqueness, before/after join counts, missing matches and null share when they affect the answer. Use sql_check for these invariants and extraction coverage; for cross-warehouse comparisons, first materialize both sides into scratch with sql_materialize. Reconcile components with totals and preserve weighting. SUM(group_mean * contributing_count) / SUM(contributing_count) recovers the overall mean for disjoint, complete groups using unrounded means and counts of the same non-null measure; AVG(group_mean) gives each group equal weight. sql_analyze is optional. If available, a read-only data-explorer via task can independently re-derive a difficult result.
5. Answer. Explain the basis in one or two sentences (population, grain, caveats), retaining useful tables or explanation before it. Finish with **Answer:** <requested value(s), with units>. If evidence or available tools are insufficient, finish with **Answer:** unable to determine — <why>. Never guess a missing result.

Select needed columns and useful partition filters to control cost; LIMIT need not reduce scanned bytes. Report measured costs, not invented estimates. After a timeout, revise the query or split work along exhaustive, disjoint partitions; a failed query is not a zero count.

Cross-engine bridge

Prerequisites: both source warehouses are connected; scratch is available and uses DuckDB; sql_materialize is exposed. Source queries use their own engine's dialect. The synthetic source tables here are orders(order_id, customer_code, amount, status) on sales_db and customers(customer_code, region) on crm_db, with text customer codes.

After inspecting examples and checking normalization collisions, invoke sql_materialize twice:

• source_warehouse: "sales_db", target_table: "orders_stage", sql:

  SELECT order_id, LOWER(TRIM(customer_code)) AS customer_key, amount
  FROM orders
  WHERE status = 'paid'
  

• source_warehouse: "crm_db", target_table: "customers_stage", sql:

  SELECT LOWER(TRIM(customer_code)) AS customer_key, region
  FROM customers
  

These are complete derived relations, without exploratory LIMITs. Check reported materialization completeness/counts against the intended scope. A partial transfer cannot support an exact cross-engine answer. Query with sql_execute on warehouse scratch, using the returned table names:

SELECT c.region, COUNT(*) AS paid_orders,
       SUM(o.amount) AS total_amount, AVG(o.amount) AS mean_order_amount
FROM orders_stage AS o
LEFT JOIN customers_stage AS c ON c.customer_key = o.customer_key
GROUP BY c.region;

First verify the customer key is unique and quantify unmatched orders with the next pattern. Do not silently remove unmatched orders; decide how they affect the requested population.

If the bridge is unavailable: use existing views, federation or an already connected engine that can access both relations. Independent aggregates can be combined only if sufficient for the requested statistic (for example, sums and contributing counts over disjoint partitions). Otherwise state that the cross-engine join cannot be completed with available tools. Do not invent a local scripting/write capability or hand-transcribe a large key list.

Fan-out and nulls

Prerequisites: ordinary SELECT/CTE support; this pattern works in DuckDB and SQLite. order_id is the intended unique left-side grain. Run on the warehouse holding both staged relations, or adapt to source tables on one engine.

SELECT customer_key, COUNT(*) AS occurrences
FROM customers_stage
GROUP BY customer_key
HAVING COUNT(*) > 1;

WITH joined AS (
  SELECT o.order_id, o.customer_key AS source_key,
         c.customer_key AS matched_key
  FROM orders_stage AS o
  LEFT JOIN customers_stage AS c ON c.customer_key = o.customer_key
)
SELECT
  (SELECT COUNT(*) FROM orders_stage) AS rows_before,
  COUNT(*) AS rows_after,
  COUNT(DISTINCT order_id) AS distinct_orders_after,
  SUM(CASE WHEN matched_key IS NULL THEN 1 ELSE 0 END) AS unmatched_rows,
  1.0 * SUM(CASE WHEN source_key IS NULL THEN 1 ELSE 0 END)
      / NULLIF(COUNT(*), 0) AS source_key_null_share
FROM joined;

Submit the two SELECTs separately if the connector accepts only one statement. For a many-to-one left join, row counts should match; also verify left-side IDs are unique and non-null. Resolve unintended fan-out at the correct business grain, not by adding an arbitrary DISTINCT. The reported null share is at joined grain; interpret it as source share only after ruling out fan-out. Empty-population ratios are undefined, not zero.

Many similarly shaped tables

Prerequisites: inspect the engine/version and table schemas. information_schema, DuckDB query_table and UNION ALL BY NAME are not portable to SQLite. Confirm compatible types and that all intended tables are included exactly once; name-based unions fill absent columns with NULL. Reconcile union counts with the sum of source counts and account for overlapping row identities.

For DuckDB, first generate a single SELECT from the catalog of synthetic orders_* partitions:

WITH catalog AS (
  SELECT '"' || replace(table_catalog, '"', '""') || '"."' ||
         replace(table_schema, '"', '""') || '"."' ||
         replace(table_name, '"', '""') || '"' AS qualified_name
  FROM information_schema.tables
  WHERE table_catalog = current_database()
    AND table_schema = 'main' AND table_type = 'BASE TABLE'
    AND starts_with(table_name, 'orders_')
)
SELECT COUNT(*) AS table_count,
       'SELECT COUNT(*) AS order_count, SUM(amount) AS total_amount ' ||
       'FROM query_table([' ||
       string_agg('''' || replace(qualified_name, '''', '''''') || '''',
                  ', ' ORDER BY qualified_name) || '], true)' AS sql
FROM catalog;

Check the catalog scope and table_count, then execute the complete generated sql. query_table([...], true) requires a constant list and unions by name; it does not accept a catalog subquery as its argument. It adds no source-table column. If provenance matters, or table names contain embedded double quotes, generate ordinary quoted SELECTs joined with UNION ALL BY NAME instead. For two known synthetic partitions:

SELECT 'orders_jan' AS source_table, * FROM "main"."orders_jan"
UNION ALL BY NAME
SELECT 'orders_feb' AS source_table, * FROM "main"."orders_feb";

For a large family, generate those SELECT branches from the catalog, escaping SQL string literals and identifiers separately; do not enumerate tables from memory. Reserve a provenance column name absent from the input tables. The star here intentionally aligns the inspected partition schemas; project needed columns for the final computation.

SQLite has no query_table or UNION ALL BY NAME. Query sqlite_master to build a complete statement with quoted identifiers and a consistent explicit column order:

SELECT COUNT(*) AS table_count,
       'WITH combined AS (' ||
       group_concat(
         'SELECT ' || quote(name) || ' AS source_table, order_id, amount ' ||
         'FROM main."' || replace(name, '"', '""') || '"',
         ' UNION ALL '
       ) || ') SELECT COUNT(*) AS order_count, SUM(amount) AS total_amount ' ||
       'FROM combined' AS sql
FROM main.sqlite_master
WHERE type = 'table' AND substr(name, 1, 7) = 'orders_';

Inspect the catalog, then execute the returned statement. Every SQLite partition must have order_id and amount with compatible meaning/types, or each generated branch needs explicit casts/NULL placeholders. UNION ALL retains rows; UNION would silently deduplicate them. Respect the engine's compound-query limit; if batching is necessary, combine disjoint batches using sufficient statistics (sum/count, not averages of averages).

In both generators, zero matching tables yields table_count = 0 and sql = NULL: investigate coverage instead of claiming a zero total. Never execute clipped generated SQL. Read its spill if available, or generate smaller catalog batches if read also clips a long line. These queries generate SQL from metadata, not lists of copied business keys.

Dialect references: DuckDB query_table, DuckDB union-by-name option, SQLite compound SELECT. All essential examples are above; no external file is required.

Batch semantic labels, then count

Treat inferred labels as new data, not ground truth. Define a fixed rubric, allowed labels and uncertainty policy before classifying. For synthetic messages(message_id, body), a rubric might classify a request to reverse a charge as refund, a question about shipping progress as delivery, and ambiguous text as uncertain. Specify tie-breaking for multi-topic messages.

Inspect text, then define the complete population of stable IDs and count it before classifying. Labeling with a model costs one call per batch, so it grows with the table and can cost far more than the analysis itself. Without the user's confirmation, label at most 500 rows in total by any path: your own reading, subagents, or warehouse AI functions (for example classification functions in Snowflake, Databricks or BigQuery), which are billed per row too. To label more, first tell the user the row count, how the labels would be produced and the size of the job (about one model call per 50 rows, or a per-row charge on their warehouse bill for warehouse AI functions), and wait for their confirmation. When the population is larger than 500 rows, by default label a documented random sample of up to 500 rows (roughly ±4.5 percentage points at 95% confidence for a proportion) and report the result as an estimate with its sample size, not as an exact count. Review uncertain cases consistently.

To reuse the checks below, form labels(message_id, label, rubric_version) for the labeled IDs; population must cover the same intended IDs (for a sample, the sampled IDs). Small audited annotation batches can use an available permitted ingestion path. When scratch and sql_materialize are available, these small batches can be expressed as a SELECT relation and materialized there. For example (DuckDB/SQLite SELECT syntax):

WITH annotations(message_id, label, rubric_version) AS (
  VALUES (101, 'refund', 'v1'),
         (102, 'delivery', 'v1'),
         (103, 'uncertain', 'v1')
)
SELECT * FROM annotations;

These literals are newly inferred annotations, not a copied intermediate database result. Do not scale this into hand-copying thousands of IDs. When the full population cannot be labeled within these limits, label a sample or report the coverage limitation. sql_materialize alone does not imply an arbitrary file writer. Never claim labels or counts you did not compute and persist; with no persistence tool, a small complete annotation CTE can still be checked and counted in a read-only query.

Prerequisites for the following SQL: DuckDB/SQLite; population(message_id) contains exactly the intended unique IDs (the sampled IDs for a sample) and labels contains the annotation records in the same engine. They may be scratch tables or CTEs. Run each SELECT separately as needed:

-- Must return no rows: includes duplicates across batches.
SELECT message_id, COUNT(*) AS copies
FROM labels GROUP BY message_id HAVING COUNT(*) <> 1;

-- Missing IDs, extra IDs and invalid records must all be zero.
SELECT
  (SELECT COUNT(*) FROM population AS p
   WHERE NOT EXISTS (SELECT 1 FROM labels AS l
                     WHERE l.message_id = p.message_id)) AS missing_ids,
  (SELECT COUNT(*) FROM labels AS l
   WHERE NOT EXISTS (SELECT 1 FROM population AS p
                     WHERE p.message_id = l.message_id)) AS extra_ids,
  (SELECT COUNT(*) FROM labels
   WHERE message_id IS NULL OR label IS NULL
      OR label NOT IN ('refund', 'delivery', 'uncertain')
      OR rubric_version IS NULL OR rubric_version <> 'v1') AS invalid_records;

-- Only count after the invariants pass; retain uncertain in the denominator.
SELECT label, COUNT(*) AS messages
FROM labels GROUP BY label ORDER BY label;

Verify the label counts sum to the population count (the sample size when you labeled a sample). Use these computed counts in the answer; do not re-count labels by eye or combine unverified child summaries. For a sample, report proportions with the sample size and the size of the full population, and do not present scaled-up counts as exact.

Reading spilled results

When sql_execute reports Preview: N of M returned rows ... saved to <path>, N is the displayed preview and M is the connector-returned count, not necessarily the number of matches. A row-limit warning still applies to the spill. If a JSONL path is supplied, inspect it with read, for example:

{"filePath":"<absolute path reported by sql_execute>","offset":1,"limit":50}

Inspect any metadata/schema record before interpreting row records; preserve encoded types and distinguish NULL from the string "NULL". Continue using line offsets from the read output, accounting for metadata lines. Read may itself clip long lines or total output: recover only the needed fields with a SQL projection/filter instead of guessing missing cell contents. For exact totals, compute COUNT(*), SUM or other requested aggregates in the engine; a spill cannot recover rows discarded by a connector limit. If no spill is available, narrow or aggregate the query and respect its returned-row limit.