---
name: KTX Analytics
slug: ktx-analytics
category: Data
description: KTX Analytics queries a KTX-connected database using MCP tools for discovery, semantic-layer analysis, and raw read-only SQL to investigate metrics, explore tables, compare periods, and explain findings. Use it for any data-analysis request against a configured KTX connection.
github: "https://github.com/Kaelio/ktx/tree/main/packages/cli/src/skills/analytics"
language: TypeScript
stars: 1547
forks: 99
install: "npx degit https://github.com/Kaelio/ktx/tree/main/packages/cli/src/skills/analytics ~/.claude/skills/analytics"
installs_to: ~/.claude/skills/analytics
source_path: packages/cli/src/skills/analytics/SKILL.md
collection_size: 17
category_size: 668
collection_url: "https://dirskills.com/collections/Kaelio/ktx"
added: 2026-08-19T07:26:30.201Z
last_synced: 2026-08-19T07:26:30.201Z
canonical_url: "https://dirskills.com/skills/ktx-analytics"
---

# KTX Analytics

KTX Analytics queries a KTX-connected database using MCP tools for discovery, semantic-layer analysis, and raw read-only SQL to investigate metrics, explore tables, compare periods, and explain findings. Use it for any data-analysis request against a configured KTX connection.

**Install:**

```bash
npx degit https://github.com/Kaelio/ktx/tree/main/packages/cli/src/skills/analytics ~/.claude/skills/analytics
```

## README

# ktx Analytics Workflow

You have access to ktx MCP tools for data discovery, semantic-layer analysis, raw read-only SQL, wiki context, and memory ingest. Follow this workflow.

<workflow>
1. **Discover** - call `discover_data` first to see what exists across wiki pages, semantic-layer sources, metrics, dimensions, raw tables, and columns. Returns refs only.
2. **Inspect top hits in parallel** - for each promising ref:
   - `kind: 'wiki'` -> `wiki_read`
   - `kind: 'sl_source'`, `kind: 'sl_measure'`, or `kind: 'sl_dimension'` -> `sl_read_source`
   - `kind: 'table'` or `kind: 'column'` -> `entity_details`
   - For tables you intend to query, sample a few rows (`entity_details` plus a small `sql_execution` sample) to confirm date encoding, null prevalence in join/filter keys, and the real enum values — see the `<sql_craft>` Schema-discovery rules.
3. **Resolve business values** - if the user named a value such as "Acme Corp", "enterprise", or "status=shipped", call `dictionary_search` to find which column holds it.
4. **Plan the analysis** - identify the grain, metrics, dimensions, filters, time window, and expected row limits before querying. Confirm each filter/join column's real type before comparing it (see the `<sql_craft>` Schema-discovery rules). **Write down the exact output-column list first** — enumerate, from the question, every column the answer must have (each requested metric/attribute; for every grouped or named entity BOTH its id and its name; every input to each derived value) and treat that list as the contract your final `SELECT` must match column-for-column. Decide this list *before* writing SQL, not after — building the projection to a pre-stated list is far more reliable than reviewing for omissions at the end.
5. **Query** -
   - Prefer `sl_query` when the semantic layer covers the question.
   - Use `sql_execution` only for questions the semantic layer does not cover.
   - Before writing raw `sql_execution` SQL against a connection, call `sql_dialect_notes` with its connection id to get that engine's FQTN, identifier-quoting, date, top-N, series/calendar, rolling-window, safe-cast, and JSON conventions.
   - When authoring raw SQL, apply the `<sql_craft>` rules: build incrementally, keep window ordering deterministic, compute at full precision, and match the answer's grain to the question.
6. **Validate and explain** - sanity-check totals, filters, null handling, and time zones. **Always run the final completeness check before emitting:** re-read the question and confirm every requested output, each named entity's identity, each derived value's inputs, and the question's grain are all in the projection — see the `<sql_craft>` Final completeness check. If a result is unexpectedly empty or its grain looks wrong, work through the `<sql_craft>` Answer-completeness rules to diagnose. State the source tables or semantic-layer objects used.
7. **Capture durable learnings** - call `memory_ingest` whenever a turn produces something worth remembering (business rules, metric definitions, schema gotchas, recurring findings) **or** whenever the user asks you to remember something. Pass markdown in `content` including any source context the memory agent should weigh. Each call is a feedback loop; better notes today mean smarter `discover_data` and `wiki_search` results tomorrow.
</workflow>

<rules>
- Always run `discover_data` before writing SQL. Do not guess table names.
- Prefer the semantic layer over raw SQL when both can answer the question; measures are the source of truth.
- Read entity details before writing SQL against an unfamiliar table. Do not assume column names.
- Treat `sql_execution` as read-only. Writes are rejected by the server.
- Validate value mentions with `dictionary_search` instead of guessing case or spelling. Treat a `dictionary_search` miss as non-authoritative. The index is built from profile-sampled values, so a missing value may simply have been outside the sample. Follow up with `sql_execution` against the most plausible columns before concluding the value is absent.
- `connectionId` scoping when `connection_list` shows multiple connections:
  - Always pass it: `entity_details`, `sl_read_source`, `sql_execution`.
  - Pass it when intent pins a warehouse, otherwise omit for unscoped discovery: `sl_query`, `discover_data`, `dictionary_search`.
  - `memory_ingest`: pass it for warehouse-specific knowledge (e.g. "in our warehouse"); without it the memory lands as wiki-only and cannot update the semantic layer.
  - Never pass it: `connection_list`, `wiki_search`, `wiki_read`, `memory_ingest_status`.
  - If scoping is required but intent is ambiguous, ask which warehouse before calling.
- Show compact result tables for small outputs. For broad results, summarize the top findings and mention the applied limit.
- Ask a concise clarification only when the metric, date range, entity, or grain is genuinely ambiguous and cannot be inferred from context.
</rules>

<sql_craft>
Heuristics for writing *correct* (not merely runnable) SQL. Each is a default plus the reason it holds on any database; apply judgment to the question and the data.

**Schema discovery before writing SQL**
- **Sample before you compose.** Inspect representative rows of every table you will touch (`entity_details` plus a small `sql_execution` sample) to confirm date/time encoding (`YYYYMMDD` integer vs ISO text vs epoch), null prevalence in join/filter keys, and the real set of categorical/enum values. Assumptions about encoding and nullability are the most common source of silently-wrong filters.
- **Cast to the real type before comparing.** Compare a column against a literal of its actual type in `WHERE`/`JOIN`. A string column compared to a numeric literal (or the reverse) can silently match nothing instead of raising an error.
- **Parse text-encoded numerics before doing math on them.** When a column the question treats as a number is stored as text, sample its **distinct** values (the *Sample before you compose* habit) to learn the encodings actually present — unit suffixes (`K`/`M`/`B`), currency symbols, thousands separators, percent signs, and non-numeric sentinels (`-`, `N/A`, empty) — and never infer the format from the column name. *Why:* aggregated or compared as-is the text sorts lexically (`'100' < '9'`) and a naive cast collapses formatted values to `0`/NULL, so the query runs but the number is silently wrong instead of erroring.
- **Strip, scale, and cast in one early CTE.** Strip currency/separator/percent characters, multiply by the suffix scale (`K`=10^3, `M`=10^6, `B`=10^9), map sentinels to `0` **or** `NULL` (by the *Default by additivity* rule below), then cast to a numeric type — all in a single early CTE so every layer above sees clean numbers. This is the *meaning-is-numeric* complement to *Cast to the real type before comparing*. *Why:* one clean conversion at the base keeps the lexical-sort-and-cast-to-0 failure out of every downstream layer.
- **Confirm the parse covered every value.** After parsing, count the non-sentinel rows that failed to parse — a failed parse should surface as `NULL`, visible only with a **failure-detecting cast** from `sql_dialect_notes` (a plain `CAST` errors on some engines and on sqlite silently returns `0`/partial, so an `IS NULL` check is meaningless there). *Why:* an encoding the sample missed would otherwise vanish into `0`/NULL instead of being caught.
- **Parse code/dependency text by its real grammar, not one broad regex.** When a question extracts imported/required/loaded packages or modules from stored source text or dependency manifests, parse by the *language or format*, not a single pattern: Java `import`/`import static` — drop the terminal class/member, keep the package path, and allow valid identifier segments with underscores and mixed case (e.g. com.planet_ink.coffee_mud); Python — handle both `import a, b as c` and `from a.b import c`, stripping aliases; R — handle `library(...)` and `require(...)`; notebooks (`.ipynb`) — parse the JSON and read each cell's `source` lines *before* applying the language rules (never regex the raw notebook file, whose prose contains the words "import"/"from"); JSON/manifest files — `PARSE_JSON` and flatten the dependency object's keys (e.g. `require`). Strip comments/prose lines first and split multi-import lines so each declared dependency is counted once. *Why:* a single lowercase-segment regex silently drops real identifiers and matches prose, so the ranking is wrong though the query runs.
- **Decide the counting population explicitly when a table is deduplicated.** If the source table is de-duplicated and carries a documented copy/occurrence count (e.g. a `copies` column = "repositories sharing this exact content"), the count grain is a real modeling choice: weight by that column only when the question's population is clearly the represented files/repositories; otherwise count the distinct stored rows. State which population the question names and match it — do not default to one silently. *Why:* on a deduplicated table `COUNT(*)` and `SUM(copies)` give different rankings, so the right metric depends on the population the question asks about, not on which is larger.

```sql
-- "Total trade volume" where value_text holds '1.2K', '3M', '$1,200', '-'.
-- WRONG: a naive cast collapses the formatted values ('1.2K'->1.2, '$1,200'->0,
-- '-'->0) instead of erroring, so the SUM comes back silently far too low.
SELECT SUM(CAST(value_text AS REAL)) AS total_volume FROM metrics;

-- RIGHT: strip symbols/suffixes, scale by the K/M/B suffix, map sentinels to 0, and
-- cast once in an early CTE; the SUM then runs over clean numbers.
WITH parsed AS (
  SELECT CASE WHEN value_text IN ('-', 'N/A', '') THEN 0
    ELSE CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(value_text,
                '$', ''), ',', ''), 'K', ''), 'M', ''), 'B', '') AS DECIMAL(18, 4))
         * CASE WHEN value_text LIKE '%K' THEN 1000
                WHEN value_text LIKE '%M' THEN 1000000
                WHEN value_text LIKE '%B' THEN 1000000000 ELSE 1 END
    END AS volume
  FROM metrics
)
SELECT SUM(volume) AS total_volume FROM parsed;
```

- **Canonicalize observed URL-path variants before page-level analysis.** When a question groups, filters, or sequences web pages by a `path`/`url` column, sample its distinct values first. If the data itself shows route-label variants — `/route` and `/route/` for the same page context — define a canonical page-path expression in an early CTE and use it everywhere above that CTE: preserve `/` as root, strip trailing slashes only from non-root paths, and map an observed empty path to `/` *only* when the column is a URL path and the sampled rows show blank root-page events. Do **not** merge different route names (`/input` ≠ `/regist/input`), strip query strings/fragments/host/scheme, lowercase paths, or canonicalize at all when the question asks for the raw stored URL/path or for slash-vs-no-slash differences. *Why:* raw request logs routinely store the same user-visible page both with and without a trailing slash, so grouping or sequencing the raw labels silently splits one page into several — but inventing aliases the data doesn't show would just as silently merge distinct pages.

**Composition**
- **Build incrementally.** Assemble complex queries one CTE at a time, checking each layer's output on a small sample before stacking the next; a wrong intermediate layer is far cheaper to catch early than to debug in the final number.
- **Avoid fan-out joins — the danger is cumulative.** Any one-to-many hop on the path between a measure's owning table and the aggregate inflates that measure, even when the offending join sits several hops below the `SUM`/`COUNT` and is easy to miss. The fix is the single-hop one applied per measure-owning table along the whole chain: pre-aggregate each coarse-grained measure to its own grain in a CTE, then join the already-aggregated result.
- **Verify the grain holds across each join.** As you compose, confirm a join you intend to be one-to-one / many-to-one did not change the grain you aggregate at — e.g. the row count (or the count of the aggregate's key) is unchanged across it. When a join is genuinely one-to-many, reach for the default fix (pre-aggregate to grain); for a pure count, `COUNT(DISTINCT key)` is an acceptable escape hatch. A `SUM`/`AVG` of a fanned-out measure must pre-aggregate — `DISTINCT` cannot de-duplicate a sum.
- **A join that only attaches a label must not drop rows — `LEFT JOIN` it, and key the aggregate on the fact column.** Fan-out's mirror image is just as silent: when you join a dimension table *only to fetch a display attribute* (a name for an id, a category for a product), an **incomplete** dimension — and dimensions are routinely incomplete: trimmed catalogs, late-arriving rows, slowly-changing-dimension gaps — makes a plain inner `JOIN` quietly **discard every fact row whose key has no parent**, shrinking the counts, sums, and the universe over which any share / average / median is computed (a measure halves with no error and no empty result). Two guards: (1) inner-join a dimension only when you *intend it as a filter* — you want exactly the rows that have a parent — never merely to read a column off it; for pure enrichment use `LEFT JOIN`. (2) Key the aggregation and `GROUP BY` on the **fact** column (`sales.prod_id`), not the dimension column (`products.prod_id`), so an unmatched key yields a `NULL` label on its own row rather than dropping or collapsing it. Use the same row-count check as above, but for an enrichment join confirm the fact row count is *unchanged* (not merely un-inflated); if a dimension you only wanted a name from removed rows, that is the bug.
- **Source each filter, date, and measure from the table that OWNS it at the question's grain.** When two joined fact tables carry similarly-named columns at *different* grains — a parent (one row per order: its `status`, placement `created_at`, `num_of_item`) and its child (one row per line item: line `created_at`, `sale_price`, `cost`) — read each predicate/measure from the table whose grain the question names, not from whichever is in scope after the join. "Orders that are Complete", "for each month of the orders", "the order's creation date" are *order*-grain, so the status filter and the month bucket come from the parent order row, even though the child also has `status`/`created_at` columns; line price and cost come from the child. *Why:* the parent's and child's copies of a column diverge (an item's placement month or status can differ from its order's), so anchoring an order-grain filter or calendar on the line table silently buckets/filters the wrong rows. The mirror at metric grain: never combine a parent-grain count with child rows after the join (e.g. `num_of_item * SUM(line_price)` once per line) — compute each measure at its own grain (sum line prices to the order, take `num_of_item` once per order) before combining.

```sql
-- "How many orders per region contain a returned item?" — count each order once.
-- WRONG: order_lines is joined to apply the line-level filter, which multiplies
-- orders; an order with two returned lines is counted twice, three joins below
-- the COUNT, where the inflation is easy to miss.
SELECT r.region_id, COUNT(*) AS n_orders
FROM regions r
JOIN stores s      ON s.region_id = r.region_id
JOIN orders o      ON o.store_id  = s.store_id
JOIN order_lines l ON l.order_id  = o.order_id
WHERE l.status = 'returned'
GROUP BY r.region_id;

-- RIGHT: collapse order_lines to one row per qualifying order first, then join up
-- so each order contributes exactly once.
WITH returned_orders AS (
  SELECT order_id FROM order_lines WHERE status = 'returned' GROUP BY order_id
)
SELECT r.region_id, COUNT(*) AS n_orders
FROM regions r
JOIN stores s           ON s.region_id = r.region_id
JOIN orders o           ON o.store_id  = s.store_id
JOIN returned_orders ro ON ro.order_id = o.order_id
GROUP BY r.region_id;
-- A pure count could also use COUNT(DISTINCT o.order_id); a SUM/AVG of an
-- order-level measure fanned out this way must pre-aggregate — DISTINCT can't
-- de-duplicate a sum.
```

**Ordering & aggregation determinism**
- **Make the ordering deterministic.** Give every ranking/ordering window a complete tie-breaker by appending unique key column(s) to `ORDER BY`, so `RANK`/`ROW_NUMBER`/`LAG` results are stable instead of flickering between runs.
- **Order inside string/array aggregation.** When concatenating rows into a delimited string or building an ordered array (`GROUP_CONCAT` / `string_agg` / `array_agg`), the element order is **undefined unless you specify it** — put an explicit `ORDER BY` on the aggregate. Be deliberate about collation: the default text sort is **binary/case-sensitive** (so `'BBQ'` sorts before `'Bacon'` because uppercase code points precede lowercase), which differs from a case-insensitive sort; pick the one the question implies and apply it consistently (`ORDER BY ... COLLATE NOCASE` for case-insensitive). *Why:* an unordered or differently-collated concatenation produces a string with the right elements in the wrong order — runnable but not matching the expected text.
- **Emit a list-valued answer cell as a delimited STRING, not a raw ARRAY/repeated column.** When the answer needs several values in one cell (a set of names/codes/tags for an entity), build a delimited scalar with `STRING_AGG(x, ',' ORDER BY x)` (or `ARRAY_TO_STRING(ARRAY_AGG(x ORDER BY x), ',')`) — do not return a SQL `ARRAY`/repeated column. *Why:* an array column serializes to an engine-specific representation (e.g. `['a' 'b']` or `["a","b"]`) that won't compare equal to a plain delimited list (`a,b`), so a values-correct answer still mismatches when materialized to rows.
- **Filter after the window, not before**, for sequence / "first" / "most recent" / "since" questions: compute the window over the full partition, then keep the rows you want. A pre-filter shrinks the partition the window ranks over, so "first"/"most recent" is measured against the wrong set.

```sql
-- "Each customer's first order, restricted to orders since 2024-01-01."
-- Wrong: the filter runs before the window, so it ranks only 2024 rows and
-- misses customers whose true first order was earlier.
SELECT customer_id, order_id,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS seq
FROM orders
WHERE order_date >= '2024-01-01';   -- then keep seq = 1

-- Right: rank the full partition in a CTE, then filter in the outer query.
WITH ranked AS (
  SELECT customer_id, order_id, order_date,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS seq
  FROM orders
)
SELECT customer_id, order_id, order_date
FROM ranked
WHERE seq = 1 AND order_date >= '2024-01-01';
```

- **Cumulative / running total.** Use an explicit frame — `SUM(x) OVER (PARTITION BY k ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)` — with a complete tie-breaker on the `ORDER BY` (per the deterministic-ordering rule above). *Why:* a bare `ORDER BY` defaults to a `RANGE`-based frame bounded at the current row, which on ties in the order key folds every tied peer into one cumulative value — it runs and looks plausible, but the running total jumps at each tie boundary.
- **Rolling window over calendar time, plus minimum periods.** "Rolling N days/months" spans a *calendar range*, not a fixed row count: a `ROWS BETWEEN n-1 PRECEDING` frame silently measures the wrong span when days are missing. Two sanctioned paths — (a) build a gap-free date spine first (the **Series** idiom from `sql_dialect_notes`) so one row exists per calendar unit, then a `ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW` frame equals the intended span (fully portable); or (b) where the engine supports it, a native calendar range frame — or a date-keyed self-join — expresses the window directly: get the