# ktx-analytics

Adopt this skill: https://proskills.md/skills/other/kaelio-ktx-packages-cli-src-skills-analytics

Page: https://proskills.md/skills/other/kaelio-ktx-packages-cli-src-skills-analytics
This document is the public skill bundle for that page. It contains SKILL.md and every helper file the catalog carries. No login is required.

## File: packages/cli/src/skills/analytics/SKILL.md
size: 8016
upstream: https://github.com/kaelio/ktx/blob/main/packages/cli/src/skills/analytics/SKILL.md

```text
---
name: ktx-analytics
description: Use when answering a question that needs data from a ktx-connected database - investigating, analyzing, "how many", "show me", "what's the breakdown of", finding records by value, exploring tables, comparing periods, explaining metrics, or any data-analysis request. Triggers even when the user does not say "analytics"; if the answer requires querying a configured ktx connection, this skill applies.
---

# 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 impo
```

## File: README.md
upstream: https://github.com/kaelio/ktx/blob/main/README.md

````text
<h1 align="center">
  <img src="assets/ktx-lockup.svg" alt="ktx" width="500" />
</h1>

<h1 align="center">
  The context layer for data agents
</h1>

<p align="center">
  <a href="https://www.npmjs.com/package/@kaelio/ktx"><img src="https://img.shields.io/npm/v/@kaelio/ktx?style=flat-square&color=f97316" alt="npm version" /></a>
  <a href="https://codecov.io/gh/Kaelio/ktx"><img src="https://codecov.io/gh/Kaelio/ktx/graph/badge.svg?branch=main" alt="Codecov" /></a>
  <a href="https://github.com/Kaelio/ktx/actions/workflows/ci.yml?query=branch%3Amain"><img src="https://img.shields.io/github/actions/workflow/status/Kaelio/ktx/ci.yml?branch=main&label=tests&style=flat-square" alt="Tests" /></a>
  <a href="https://docs.kaelio.com/ktx/docs/"><img src="https://img.shields.io/badge/docs-ktx-22c55e?style=flat-square" alt="Documentation" /></a>
  <a href="https://join.slack.com/t/ktxcommunity/shared_invite/zt-3y9b44m1x-LVyNNJD5nwaZHq4XS29LMQ"><img src="https://img.shields.io/badge/slack-join%20community-4A154B?style=flat-square&logo=slack&logoColor=white" alt="Join the ktx Slack community" /></a>
  <a href="https://github.com/Kaelio/ktx/blob/main/LICENSE"><img src="https://img.shields.io/badge/license-Apache%202.0-blue?style=flat-square" alt="License" /></a>
  <a href="https://www.ycombinator.com/companies/kaelio"><img src="https://img.shields.io/badge/Y%20Combinator-P25-orange?style=flat-square" alt="Y Combinator P25" /></a>
</p>

<p align="center">
  <a href="https://docs.kaelio.com/ktx/docs/getting-started/quickstart"><b>Quickstart</b></a> ·
  <a href="https://docs.kaelio.com/ktx/docs/cli-reference/ktx"><b>CLI Reference</b></a> ·
  <a href="https://docs.kaelio.com/ktx/docs/community/ai-resources"><b>Agent Setup</b></a> ·
  <a href="https://join.slack.com/t/ktxcommunity/shared_invite/zt-3y9b44m1x-LVyNNJD5nwaZHq4XS29LMQ"><b>Slack</b></a>
</p>

<p align="center">
  <sub>Built and maintained by <a href="https://www.kaelio.com"><b>Kaelio</b></a></sub>
</p>

---

**ktx** is a self-improving context layer that teaches agents how to query your
warehouse accurately - from approved metric definitions, joinable columns, and
business knowledge it builds and maintains for you.

> [!NOTE]
> Run **ktx** with your own LLM API keys or a local agent sign-in — a
> **Claude Pro/Max** subscription through Claude Code, or your local Codex
> authentication. No extra usage billing from **ktx**.

<p align="center">
  <a href="https://youtu.be/5V4TuzYVlrA">
    <img src="assets/launch-video-thumb.png" alt="Watch the ktx launch video (1:56)" width="820" />
  </a>
</p>

<p align="center">
  <img src="docs-site/public/images/ingestion-flow.png" alt="Ingestion: ktx ingests databases, BI tools, modeling code, and docs through its context engine (source connectors, context builder, reconciliation, validation) into wiki Markdown and semantic-layer YAML" width="900" />
</p>

<p align="center">
  <img src="docs-site/public/images/mcp-runtime-flow.png" alt="Serving: an agent queries ktx through MCP, which searches the wiki and semantic layer, returns approved metrics, and compiles them into read-only SQL run against the warehouse" width="900" />
</p>

## Why ktx

General-purpose agents struggle on data tasks. They re-explore your warehouse
on every question, invent their own metric logic, and return numbers that
don't match approved definitions.

Traditional semantic layers don't fix this. They demand constant manual
upkeep and don't absorb the rest of your company's knowledge.

**ktx** does both, automatically:

- **Learns from company knowledge.** Ingests wiki content, organizes it,
  removes duplicates, and flags contradictions for human review.
- **Maps the data stack.** Samples tables, captures metadata and usage
  patterns, detects joinable columns, and annotates sources so agents write
  better queries.
- **Builds a semantic layer.** Combines raw tables and high-level metrics
  through a join graph that automatically resolves chasm and fan traps, so
  agents fetch metrics declaratively instead of rewriting canonical SQL each
  time.
- **Serves agents at execution.** Exposes CLI and MCP tools with combined
  full-text and semantic search across wiki and semantic-layer entities.

## How ktx compares

|  | General-purpose agent | Traditional semantic layer | **ktx** |
| --- | :---: | :---: | :---: |
| Builds warehouse context automatically | — | — | ✓ |
| Detects joinable columns + resolves fan/chasm traps | — | Manual | ✓ |
| Approved, reusable metric definitions | — | ✓ | ✓ |
| Absorbs wiki / Notion / team knowledge | — | — | ✓ |
| Flags contradictions across sources | — | — | ✓ |
| Ships CLI + MCP for agent execution | Partial | — | ✓ |
| Read-only by design | n/a | n/a | ✓ |

## Who is ktx for

**Use ktx if you:**

- Want agents like Claude Code, Codex, Cursor, or OpenCode to query your
  warehouse with approved metric definitions
- Have business knowledge scattered across dbt, Looker, Metabase, Notion, and
  team wikis
- Need agents to reuse canonical SQL instead of inventing it on every prompt

**Skip ktx if you:**

- You don't have a SQL warehouse - **ktx** sits on top of one
- You only need one ad-hoc query - `psql` or a notebook will do

Works with PostgreSQL, Snowflake, BigQuery, ClickHouse, MySQL, SQL Server,
SQLite, DuckDB, Amazon Athena, and MongoDB. Integrates with dbt, MetricFlow,
LookML, Looker, Metabase, Sigma, Notion, and Google Drive.

## Quick Start

```bash
npm install -g @kaelio/ktx
ktx setup
ktx status
```

`ktx setup` creates or resumes a local **ktx** project, configures providers
and connections, builds context, and installs agent integration.

Example `ktx status` after setup:

```text
ktx project: /home/user/analytics
Project ready: yes
LLM ready: yes (claude-sonnet-4-6)
Embeddings ready: yes (text-embedding-3-small)
Databases configured: yes (warehouse)
Context sources configured: yes (dbt_main)
ktx context built: yes
Agent integration ready: yes (codex:project)
```

> [!TIP]
> Already using an agent? Ask Claude Code, Codex, Cursor, or OpenCode from
> your project directory:
>
> ```text
> Run npx skills add Kaelio/ktx --skill ktx and use the ktx skill to install
> and configure ktx in this project.
> ```

> [!IMPORTANT]
> If `ktx status` prints `ktx mcp start --project-dir ...`, run it before
> opening your agent client.

## Upgrading

Re-run the global install with the `@latest` tag:

```bash
npm install -g @kaelio/ktx@latest
```

## First commands

| Command | Purpose |
| --- | --- |
| `ktx setup` | Create, resume, or update a **ktx** project |
| `ktx status` | Check project readiness |
| `ktx ingest` | Build context for every configured connection |
| `ktx sl "revenue"` | Search semantic sources |
| `ktx wiki "refund policy"` | Search local wiki pages |
| `ktx mcp start` | Start the MCP server for agent clients |

See the [CLI Reference](https://docs.kaelio.com/ktx/docs/cli-reference/ktx)
for every command, flag, and option.

## Project Layout

```text
my-project/
├── ktx.yaml                         # Project configuration
├── semantic-layer/<connection-id>/  # YAML semantic sources
├── wiki/global/                     # Shared business context
├── wiki/user/<user-id>/             # User-scoped notes
├── raw-sources/<connection-id>/     # Ingest artifacts and reports
└── .ktx/                            # Local state and secrets, git-ignored
```

Commit `ktx.yaml`, `semantic-layer/`, and `wiki/`. Keep `.ktx/` local.

Project resolution defaults to `KTX_PROJECT_DIR`, then the nearest `ktx.yaml`,
then the current directory. Pass `--project-dir <path>` when scripting.

## FAQ

- **Does ktx send my schema or query results to a hosted service?**
  No. **ktx** runs locally. The only data leaving your machine is what you
  send to the LLM provider you configured.
- **Which LLM backends are supported?**
  Anthropic API, Google Vertex AI, AI Gateway, the local Claude Code session
  through the Claude Agent SDK, and your local Codex authentication through the
  C
````
