skills.rest
Kaelio
KaelioOFFICIAL
Explore all skills by this author →
GitHub

historic_sql_patterns

Identifies recurring cross-table analytical intents from historic SQL shards and emits typed pattern evidence.

1.6k|101|Updated May 10, 2026
Search Tags:#sql#data warehouse#pattern mining#query logs#Semantic Layer#evidence emission#identifier verification
One-click install
npx skills add https://github.com/Kaelio/ktx --skill historic-sql-patterns
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: historic_sql_patterns
Source: https://github.com/Kaelio/ktx/tree/main/packages/cli/src/skills/historic_sql_patterns
Command: npx skills add https://github.com/Kaelio/ktx --skill historic-sql-patterns

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Historic SQL query logs contain valuable recurring analytical patterns, but manually mining them for cross-table business intents is slow and error-prone. This Skill automates the detection of durable multi-table query patterns so they can be projected into wiki documentation and semantic-layer definitions.

Core Features & Use Cases

  • Pattern Mining from Shards: Reads bounded patterns-input shard files from the historic-sql adapter and identifies recurring analytical intents spanning at least two tables.
  • Identifier Verification: Verifies every schema, table, and column reference via entity_details and optional SQL probes before emitting evidence, preventing fictional identifiers from reaching documentation.
  • Typed Evidence Emission: Emits structured pattern evidence objects (slug, title, narrative, definitionSql, tablesInvolved, slRefs) conforming to a strict output schema for deterministic wiki projection.
  • Use Case: A data team ingesting warehouse query history wants to document how analysts repeatedly join orders and customers tables for lifecycle analysis; this Skill extracts that intent as verified pattern evidence.

Quick Start

Analyze the historic SQL pattern shard in this WorkUnit and emit verified cross-table pattern evidence for each recurring analytical intent.

Frequently Asked Questions about historic_sql_patterns

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I extract recurring patterns from historic SQL query logs?▼

Read the pattern input shard listed in the WorkUnit rawFiles, then identify analytical intents spanning at least two tables with repeated usage signals. Emit one pattern evidence object per durable intent using the defined schema with slug, narrative, definitionSql, and tablesInvolved.

What makes a SQL pattern qualify for evidence emission?▼

A pattern must involve two or more tables and show repeated usage, preferably with executionsBucket of at least 10-100 and distinctUsersBucket above solo usage. Templates are merged into one pattern only when the underlying business intent is the same.

How are table and column identifiers verified before use?▼

Every schema.table or column reference is confirmed via entity_details to inspect native types, keys, and sampleValues. If resolution fails, a sql_execution probe tests the reference, and unresolvable identifiers are marked unverified with the source raw path cited.

Can this skill write wiki pages or semantic-layer sources directly?▼

No. The skill only emits pattern evidence objects; it is explicitly forbidden from calling wiki_write, sl_write_source, sl_edit_source, or context_candidate_write. Downstream deterministic projection handles the actual wiki and semantic-layer updates.

What are the limitations of historic SQL pattern mining?▼

Single-table patterns are excluded, each shard is processed independently without reading peer shards, and uncertain semantic-layer references are omitted rather than guessed. Credentials, tokens, emails, and unredacted literals must never appear in emitted evidence.

Related Skills

Explore More Data & Analytics Skills→
abhiroopbabhiroopb

query-expert

Discover Snowflake tables, search historical queries, and execute SQL via natural language.

Community
Advanced
AlexiosBluffMaraAlexiosBluffMara
2

llm-wiki

Ingest sources into an interlinked Markdown knowledge base with linting and search.

Community
Advanced
astronomerastronomer
419

init

Generate a version-controlled .astro/warehouse.md with per-table metadata from warehouse system catalogs.

Official
Advanced
Datus-aiDatus-ai
1.5k

semantic-sql-history-profiler

Mine historical SQL execution data to extract column usage patterns and join relationships.

Official
Intermediate