ktx-analytics
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.
Choose how to use this skill
You do not need every option. Choose the path your AI client supports. The stable page stays the same; versioned files are immutable.
1. Native installer
This listing has no registered native installer command. Use the complete package or source fallback below, depending on what your client supports.
Do not guess an installer command or replace an existing version without reviewing the diff.
2. Complete package recommended
Download the ZIP when available. It includes SKILL.md plus the references, security notes and version metadata.
No complete ProSkills package is published for this listing yet.3. Prompt-only
Copy the prompt above when the agent can read the stable page or when you want to adopt the workflow without installing a skill.
Need only the instruction file?
Download SKILL.md only if your client requires a single file. The complete ZIP is safer for a full installation because it preserves the references and release context.
No path installs or executes anything by itself. Your agent still needs access to the project files. Before updating, compare the installed version and review the diff.
// RATINGS
Not yet listed on ClawHub or SkillsMP
// README
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.
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 -
psqlor 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
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:
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:
Run npx skills add Kaelio/ktx --skill ktx and use the ktx skill to install and configure ktx in this project.
[!IMPORTANT] If
ktx statusprintsktx mcp start --project-dir ..., run it before opening your agent client.
Upgrading
Re-run the global install with the @latest tag:
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 for every command, flag, and option.
Project Layout
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
// HOW IT'S BUILT
// FILES
2 files shipped with this skill. Inline text is the catalog copy. The upstream link is the source repository.
packages/cli/src/skills/analytics/SKILL.md7.8 KBupstream ↗
--- 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 impoREADME.mdupstream ↗
Contents are not inlined in the catalog. Open the upstream file.
Plain-text copy, no login: /skill-bundles/kaelio-ktx-packages-cli-src-skills-analytics.md