--- name: motherduck-explore-schema-and-answer-data-questions description: Map an unfamiliar MotherDuck database through the catalog tools, then write and run read-only SQL to answer a business question, checking grain, joins and sample sizes before trusting the numbers. Use when the user asks what data exists, where a metric lives, how tables join, or wants an answer to a question from their MotherDuck data. --- # Explore a schema and answer a data question The goal is a **number the user can trust, with its grain and its limits stated**. A query that runs is not an answer. Most wrong answers come from the wrong table grain, a join that fans out, or a filter that silently drops rows, not from syntax errors. ## 1. Confirm the target before touching anything Ask, or infer from the request, which database and which schema the question is about. If the user names none and `list_databases` shows more than one, ask rather than guess. Use `list_shares` to see whether the data comes from an attached share; a share can be read-only and may have different freshness than the user's own tables. Keep the whole exploration read-only: use `query`, not `query_rw`, for every analysis in this skill. ## 2. Find the candidate tables before reading columns Start broad and narrow down, so you read the minimum metadata: 1. If the user used a business term ("churn", "MRR", "active user"), run `search_catalog` on that term first. It finds names and likely matches faster than walking every schema. 2. Otherwise run `list_tables` for the target schema and pick two to five candidates by name and purpose. 3. Only then run `list_columns` on those candidates. Do not dump every column of every table into the context. Prefer the table whose name says what one row represents (`orders`, `daily_sessions`, `subscription_events`) over wide "analytics" tables you cannot describe in one sentence. ## 3. Establish the grain of each candidate Before any aggregation, write one sentence per table: "one row per X per Y". Confirm it with a cheap query: ```sql SELECT COUNT(*) AS rows, COUNT(DISTINCT ) AS distinct_keys FROM ; ``` If `rows` is far above `distinct_keys`, the grain is finer than the key you assumed (multiple events, versions, or line items per key). Aggregate to the grain you need before joining, or you will double-count. Look for the grain-changers: slowly changing dimensions (a `valid_from`/`valid_to` or `is_current` flag), soft-deleted rows, event tables where one entity has many rows, and history tables. Filter to current rows when the question is about now. ## 4. Check the join before trusting it For every join, compare row counts on each side and after the join: - **Fan-out:** the joined count exceeds the count of the left table on a key that should be unique on the right. Fix by deduplicating or aggregating the right side first. - **Dropped rows:** an inner join removes rows that have no match. Count the unmatched left rows with a `LEFT JOIN ... WHERE right.key IS NULL` check. If a meaningful share is unmatched, say so; do not quietly report the matched subset as the whole. - **Type mismatches:** a string id joined to an integer id returns zero or wrong matches without error. Compare the column types from `list_columns` before joining. ## 5. Build the query in small steps Write the query as CTEs, one per step (scope, then base grain, then aggregation), and run the innermost step first to check its row count. Keep a sample of results small: use `LIMIT`, and aggregate in SQL rather than pulling raw rows into the conversation. If a result is large, summarise it and offer the rest rather than returning it all. ## 6. Sanity-check the answer Before reporting, test the number against something independent: - **Magnitude:** does the total look plausible against a known figure (a dashboard, the user's estimate, a row count)? A ten-times difference usually means a grain or unit error. - **Denominator:** for rates and averages, state the denominator and its size. A 100% conversion on 3 users is not a finding. - **Time window:** confirm the date range and the timezone of the timestamp column. A "yesterday" filter on a UTC column is a common silent error. - **Freshness:** check the max timestamp in the source. A table last loaded a week ago gives a confident wrong answer for "this week". - **Nulls in the metric:** `SUM` and `AVG` skip nulls, so count them separately when they matter. ## Common patterns and traps - **Summing a pre-aggregated column:** a `total` or `count` already summarised at a higher grain gets summed again. Check whether the column is a row-level value or a rollup. - **Counting rows instead of entities:** `COUNT(*)` after a join to events counts events. Use `COUNT(DISTINCT entity_id)` when the question is "how many customers". - **Tables that look identical:** `orders` and `orders_v2`, or a `_staging` schema, may disagree. Pick the one the catalog or docs identify as current, and say which one you used. - **Duplicates from retried loads:** the same event appearing twice with the same id. Deduplicate on the natural key before counting. - **Timezones:** timestamps stored without zone are compared against zoned filters. State the zone you assumed. - **Empty results read as zero:** a filter that matches nothing returns no rows, which looks like "none happened". Check that the filter values exist in the data first (`SELECT DISTINCT` on the category column). - **Asking the docs instead of the data:** for "how does DuckDB do X" or MotherDuck syntax, `ask_docs_question` answers faster than trial and error. Use it for syntax, not for facts about the user's data. ## Output ``` Question: Answer: for ,