ktx AI Data Agents Skill

Skill by ara.so — AI Agent Skills collection.

Overview

ktx is an executable context layer that teaches AI agents how to query data warehouses accurately. It automatically builds and maintains:

Semantic layer with approved metric definitions, join graphs, and fan/chasm trap resolution
Wiki knowledge from Notion, dbt docs, BI tools, and team documentation
Warehouse metadata including table schemas, joinable columns, and usage patterns
MCP integration for Claude Code, Codex, Cursor, and other AI agents

Unlike general-purpose agents that reinvent SQL logic on every query, ktx provides agents with canonical definitions and business context through a searchable interface.

Installation

bash

# Install globally
npm install -g @kaelio/ktx

# Or use npx
npx @kaelio/ktx setup

Requirements:

Node.js 18+
Access to a SQL warehouse (PostgreSQL, Snowflake, BigQuery, ClickHouse, MySQL, SQL Server, or SQLite)
LLM API key (Anthropic, Google Vertex AI) or Claude Pro/Max subscription

Quick Start

Initial Setup

bash

# Create or resume a ktx project in current directory
ktx setup

# Check project status
ktx status

The

ktx setup

wizard will:

Create
```
ktx.yaml
```
configuration
Configure LLM and embedding providers
Set up database connections
Configure context sources (dbt, Looker, Metabase, Notion)
Build initial context
Install agent integration (MCP)

Project Structure

my-project/
├── ktx.yaml                         # Project configuration
├── semantic-layer/<connection-id>/  # YAML metric/dimension definitions
├── wiki/global/                     # Shared business knowledge
├── wiki/user/<user-id>/             # User-scoped notes
├── raw-sources/<connection-id>/     # Ingest artifacts and reports
└── .ktx/                            # Local state (git-ignored)

Commit:

ktx.yaml

semantic-layer/

wiki/

Ignore:

.ktx/

Configuration

ktx.yaml Structure

yaml

version: 1.0
name: my-analytics-project
llm:
  provider: anthropic
  model: claude-sonnet-4-6
embeddings:
  provider: openai
  model: text-embedding-3-small
connections:
  - id: warehouse
    type: postgres
    host: ${DATABASE_HOST}
    port: 5432
    database: analytics
    user: ${DATABASE_USER}
    password: ${DATABASE_PASSWORD}
    ssl: true
context_sources:
  - id: dbt_main
    type: dbt
    connection_id: warehouse
    manifest_path: ./target/manifest.json
    catalog_path: ./target/catalog.json
  - id: notion_docs
    type: notion
    token: ${NOTION_TOKEN}
    page_ids:
      - 3fa85f64-5717-4562-b3fc-2c963f66afa6

Environment Variables

bash

# LLM providers
export ANTHROPIC_API_KEY=your-key-here
export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account.json

# Embeddings
export OPENAI_API_KEY=your-key-here

# Database credentials
export DATABASE_HOST=warehouse.example.com
export DATABASE_USER=readonly_user
export DATABASE_PASSWORD=secure-password

# Context sources
export NOTION_TOKEN=secret_notion_token
export LOOKER_API_TOKEN=looker-token

Supported Databases

PostgreSQL:

yaml

connections:
  - id: postgres_warehouse
    type: postgres
    host: ${PG_HOST}
    port: 5432
    database: analytics
    user: ${PG_USER}
    password: ${PG_PASSWORD}
    ssl: true

Snowflake:

yaml

connections:
  - id: snowflake_warehouse
    type: snowflake
    account: ${SNOWFLAKE_ACCOUNT}
    user: ${SNOWFLAKE_USER}
    password: ${SNOWFLAKE_PASSWORD}
    warehouse: COMPUTE_WH
    database: ANALYTICS
    schema: PUBLIC

BigQuery:

yaml

connections:
  - id: bigquery_warehouse
    type: bigquery
    project_id: ${GCP_PROJECT_ID}
    dataset: analytics
    credentials_path: ${GOOGLE_APPLICATION_CREDENTIALS}

Core Commands

Context Management

bash

# Build context from all configured sources
ktx ingest

# Build context for specific connection
ktx ingest --connection warehouse

# Force rebuild ignoring cache
ktx ingest --force

# Dry run to preview changes
ktx ingest --dry-run

Search and Query

bash

# Search semantic layer (metrics, dimensions)
ktx sl "revenue"
ktx sl "customer churn rate"

# Search wiki knowledge
ktx wiki "refund policy"
ktx wiki "data retention rules"

# Get detailed entity information
ktx describe metric monthly_recurring_revenue
ktx describe dimension customer_segment

MCP Server

bash

# Start MCP server for agent clients
ktx mcp start

# Start with specific project directory
ktx mcp start --project-dir /path/to/project

# Check MCP server status
ktx mcp status

# Stop MCP server
ktx mcp stop

Project Management

bash

# Validate configuration
ktx validate

# Show project status
ktx status

# List all configured connections
ktx connections list

# Test connection
ktx connections test warehouse

Semantic Layer Usage

Defining Metrics

Create

semantic-layer/warehouse/metrics.yaml

yaml

metrics:
  - name: monthly_recurring_revenue
    label: Monthly Recurring Revenue
    description: Sum of all active subscription values normalized to monthly
    type: sum
    sql: |
      CASE 
        WHEN billing_period = 'monthly' THEN amount
        WHEN billing_period = 'annual' THEN amount / 12
      END
    table: subscriptions
    filters:
      - column: status
        operator: equals
        value: 'active'
    dimensions:
      - customer_segment
      - plan_type
    timestamp_column: created_at
    
  - name: customer_count
    label: Active Customers
    description: Count of distinct active customer IDs
    type: count_distinct
    sql: customer_id
    table: subscriptions
    filters:
      - column: status
        operator: equals
        value: 'active'

Defining Dimensions

Create

semantic-layer/warehouse/dimensions.yaml

yaml

dimensions:
  - name: customer_segment
    label: Customer Segment
    description: Business vs. enterprise customer classification
    type: categorical
    sql: |
      CASE 
        WHEN annual_revenue > 100000 THEN 'Enterprise'
        WHEN annual_revenue > 10000 THEN 'Business'
        ELSE 'Startup'
      END
    table: customers
    
  - name: signup_date
    label: Signup Date
    description: Date customer first signed up
    type: time
    sql: DATE(created_at)
    table: customers
    granularities:
      - day
      - week
      - month
      - quarter
      - year

Join Configuration

Define table relationships in

semantic-layer/warehouse/joins.yaml

yaml

joins:
  - left_table: subscriptions
    right_table: customers
    type: left
    conditions:
      - left_column: customer_id
        right_column: id
    
  - left_table: subscriptions
    right_table: plans
    type: left
    conditions:
      - left_column: plan_id
        right_column: id

Wiki Management

Creating Wiki Pages

bash

# Create global wiki page
cat > wiki/global/refund-policy.md << 'EOF'
# Refund Policy

## Overview
Customers can request refunds within 30 days of purchase.

## Rules
- Full refund: < 7 days
- Prorated refund: 7-30 days
- No refund: > 30 days

## Database Impact
Refunds update `transactions.status` to 'refunded' and create 
negative entries in `revenue_events`.
EOF

# Create user-scoped note
mkdir -p wiki/user/$(whoami)
cat > wiki/user/$(whoami)/analysis-notes.md << 'EOF'
# Analysis Notes

## 2025-05 Revenue Analysis
Found discrepancy in EMEA revenue - missing Stripe events.
Tracked in JIRA-1234.
EOF

Ingesting from Notion

yaml

context_sources:
  - id: product_docs
    type: notion
    token: ${NOTION_TOKEN}
    page_ids:
      - 3fa85f64-5717-4562-b3fc-2c963f66afa6  # Product Roadmap
      - 7c9e6679-7425-40de-944b-e07fc1f90ae7  # Data Dictionary
    recursive: true  # Include child pages

Run

ktx ingest

to sync Notion content into

wiki/global/

Agent Integration (MCP)

Starting MCP Server

bash

# In your ktx project directory
ktx mcp start

# Or specify project location
ktx mcp start --project-dir ~/my-analytics

Claude Desktop Configuration

Add to

~/Library/Application Support/Claude/claude_desktop_config.json

json

{
  "mcpServers": {
    "ktx": {
      "command": "ktx",
      "args": ["mcp", "start", "--project-dir", "/absolute/path/to/project"]
    }
  }
}

Restart Claude Desktop to load the MCP server.

Using ktx from Claude

Once configured, you can prompt Claude:

What was our MRR last month by customer segment?

Claude will use ktx MCP tools to:

Search semantic layer for
```
monthly_recurring_revenue
```
metric
Find relevant dimensions (
```
customer_segment
```
)
Retrieve approved SQL definitions
Execute query using canonical metric logic

Available MCP Tools

```
ktx_search_semantic_layer
```
- Search metrics, dimensions, tables
```
ktx_search_wiki
```
- Search business knowledge and documentation
```
ktx_describe_entity
```
- Get detailed entity information
```
ktx_list_connections
```
- List available database connections
```
ktx_get_context_summary
```
- Get project context overview

Common Patterns

Initial Project Setup Workflow

bash

# 1. Navigate to analytics project
cd ~/projects/analytics

# 2. Run setup wizard
ktx setup
# Select: Anthropic Claude, OpenAI embeddings, configure Postgres connection

# 3. Add dbt context source
# Edit ktx.yaml to add dbt manifest/catalog paths

# 4. Build context
ktx ingest

# 5. Verify
ktx status
ktx sl "revenue"

# 6. Start MCP for agents
ktx mcp start

Incremental Context Updates

bash

# After dbt run or schema changes
dbt run
dbt docs generate
ktx ingest --connection warehouse

# After updating wiki pages
ktx ingest --source notion_docs

# Check for conflicts or issues
ktx validate

Searching Before Agent Queries

bash

# Find available metrics
ktx sl "churn"

# Output:
# Metrics:
#   - customer_churn_rate (Monthly customer churn percentage)
#   - mrr_churn (Monthly recurring revenue lost to churn)
# 
# Dimensions:
#   - churn_reason (Categorical reason for cancellation)

# Get metric details
ktx describe metric customer_churn_rate

# Now prompt agent with context:
# "Calculate customer_churn_rate for Q1 2025 by churn_reason"

Multi-Warehouse Setup

yaml

connections:
  - id: production
    type: snowflake
    account: ${SNOWFLAKE_PROD_ACCOUNT}
    # ... prod credentials
    
  - id: staging
    type: snowflake
    account: ${SNOWFLAKE_STAGING_ACCOUNT}
    # ... staging credentials

context_sources:
  - id: dbt_prod
    type: dbt
    connection_id: production
    manifest_path: ./prod/target/manifest.json
    
  - id: dbt_staging
    type: dbt
    connection_id: staging
    manifest_path: ./staging/target/manifest.json

bash

# Ingest specific warehouse
ktx ingest --connection production

# Search scoped to connection
ktx sl "revenue" --connection production

Troubleshooting

MCP Server Not Starting

Check project directory:

bash

ktx status
# If shows "ktx mcp start --project-dir ...", copy and run that command

Verify ktx.yaml exists:

bash

ls ktx.yaml
# If missing, run: ktx setup

Check Claude Desktop config:

bash

cat ~/Library/Application\ Support/Claude/claude_desktop_config.json
# Ensure path is absolute, not relative

Database Connection Issues

bash

# Test connection directly
ktx connections test warehouse

# Common fixes:
# - Verify environment variables are set
# - Check firewall/VPN for warehouse access
# - Ensure user has SELECT permissions
# - For Snowflake, verify warehouse is running

Context Ingestion Failures

bash

# Enable verbose logging
ktx ingest --verbose

# Check specific source
ktx ingest --source dbt_main --verbose

# Validate configuration
ktx validate

# Common issues:
# - dbt manifest/catalog paths incorrect
# - Missing environment variables
# - LLM API rate limits (retry with backoff)

Semantic Layer Errors

Undefined metric:

bash

# List all metrics
ktx sl "*" --type metric

# Ensure YAML is valid
cat semantic-layer/warehouse/metrics.yaml
ktx validate

Join graph errors:

bash

# Check join definitions
cat semantic-layer/warehouse/joins.yaml

# Common issues:
# - Missing join between tables
# - Ambiguous join paths (fan trap)
# - Incorrect column names

Agent Not Finding Context

Restart MCP server:

bash

ktx mcp stop
ktx mcp start

Rebuild context:

bash

ktx ingest --force

Check search results:

bash

ktx sl "your search term"
ktx wiki "your search term"
# If empty, context may not have been ingested

TypeScript API Usage

For programmatic usage in Node.js:

typescript

import { KtxProject } from '@kaelio/ktx';

// Load project
const project = await KtxProject.load('/path/to/project');

// Search semantic layer
const metrics = await project.searchSemanticLayer('revenue', {
  type: 'metric',
  limit: 10
});

// Search wiki
const wikiPages = await project.searchWiki('refund policy', {
  scope: 'global',
  limit: 5
});

// Get entity details
const metric = await project.describeEntity('metric', 'monthly_recurring_revenue');

// Execute ingestion
await project.ingest({
  connectionId: 'warehouse',
  force: false
});

Best Practices

Version control semantic layer: Commit
```
semantic-layer/
```
and
```
wiki/global/
```
to track metric definitions
Use environment variables: Never hardcode credentials in
```
ktx.yaml
```
Regular ingestion: Run
```
ktx ingest
```
after dbt runs or schema changes
Descriptive names: Use clear metric/dimension names that match business language
Document assumptions: Add descriptions to all semantic layer entities
Test connections: Use
```
ktx connections test
```
before ingestion
User wiki for ephemeral notes: Keep temporary analysis in
```
wiki/user/
```
Review ingest logs: Check for warnings about duplicate or conflicting definitions

Advanced Configuration

Custom LLM Configuration

yaml

llm:
  provider: vertex
  project_id: ${GCP_PROJECT_ID}
  location: us-central1
  model: claude-3-5-sonnet@20241022
  max_tokens: 8192
  temperature: 0.0

Embedding Configuration

yaml

embeddings:
  provider: openai
  model: text-embedding-3-large
  dimensions: 1536
  batch_size: 100

Ingest Scheduling

bash

# Cron example: Daily at 2 AM
0 2 * * * cd /path/to/project && ktx ingest --connection warehouse >> /var/log/ktx.log 2>&1

Custom Python Queries

ktx includes a Python semantic layer query planner:

python

from ktx_sl import SemanticLayer

# Load semantic layer
sl = SemanticLayer.from_project("/path/to/project")

# Build query
query = sl.query(
    metrics=["monthly_recurring_revenue"],
    dimensions=["customer_segment", "signup_month"],
    filters=[
        {"dimension": "signup_date", "operator": ">=", "value": "2025-01-01"}
    ],
    order_by=[{"metric": "monthly_recurring_revenue", "desc": True}]
)

# Get SQL
sql = query.to_sql()
print(sql)

Resources

Documentation: https://docs.kaelio.com/ktx
CLI Reference: https://docs.kaelio.com/ktx/docs/cli-reference/ktx
Agent Setup: https://docs.kaelio.com/ktx/docs/ai-resources/agent-quickstart
Slack Community: https://join.slack.com/t/ktxcommunity/shared_invite/zt-3y9b44m1x-LVyNNJD5nwaZHq4XS29LMQ
GitHub Issues: https://github.com/Kaelio/ktx/issues

ktx-ai-data-agents

NPX Install

Tags

SKILL.md Content

ktx AI Data Agents Skill

Overview

Installation

Quick Start

Initial Setup

Project Structure

Configuration

ktx.yaml Structure

Environment Variables

Supported Databases

Core Commands

Context Management

Search and Query

MCP Server

Project Management

Semantic Layer Usage

Defining Metrics

Defining Dimensions

Join Configuration

Wiki Management

Creating Wiki Pages

Ingesting from Notion

Agent Integration (MCP)

Starting MCP Server

Claude Desktop Configuration

Using ktx from Claude

Available MCP Tools

Common Patterns

Initial Project Setup Workflow

Incremental Context Updates

Searching Before Agent Queries

Multi-Warehouse Setup

Troubleshooting

MCP Server Not Starting

Database Connection Issues

Context Ingestion Failures

Semantic Layer Errors

Agent Not Finding Context

TypeScript API Usage

Best Practices

Advanced Configuration

Custom LLM Configuration

Embedding Configuration

Ingest Scheduling

Custom Python Queries

Resources