Primary Sources

ktx connects to your data warehouse or database to build schema context, discover relationships, and execute semantic layer queries. Each connection is defined in ktx.yaml under the connections key.

For analytics tools and knowledge systems such as dbt, MetricFlow, LookML, Metabase, Looker, and Notion, use Context Sources. For Claude Code, Codex, Cursor, OpenCode, and other agent clients, use Agent Clients.

All connectors share these conventions:

• Sensitive values support env:VAR_NAME (read from environment) and file:/path/to/secret (read from file) references
• Connections are read-only; ktx never writes to your database
• Database ingest discovers tables, columns, types, and constraints automatically

Connection field reference

Agents should prefer environment or file references over literal secrets.

PostgreSQL

The most full-featured connector. Supports schema introspection, foreign key detection, column statistics, and query history via pg_stat_statements.

Connection config

connections:
  my-postgres:
    driver: postgres
    url: env:DATABASE_URL
    schema: public

Or with individual fields:

connections:
  my-postgres:
    driver: postgres
    host: localhost
    port: 5432
    database: analytics
    username: ktx_reader
    password: env:PG_PASSWORD
    schemas:
      - public
      - analytics
    ssl: true

Authentication

Features

Query history

PostgreSQL query history mines real query patterns from pg_stat_statements. This helps ktx understand how your team actually queries the data.

Requirements:

• pg_stat_statements extension enabled
• pg_read_all_stats role granted to the ktx user

Config options:

    context:
      queryHistory:
        enabled: true
        minExecutions: 5
        filters:
          dropTrivialProbes: true

Dialect notes

• SQL compilation uses LIMIT/OFFSET pagination
• Named parameters converted to positional ($1, $2, ...)
• Supports COUNT(*) FILTER (WHERE ...) for null analysis
• Full support for PostgreSQL types: uuid, jsonb, timestamptz, numeric, text[], etc.

Snowflake

Connects via the Snowflake SDK. Supports multi-schema scanning, RSA key authentication, and query-history configuration for Snowflake query history.

Connection config

connections:
  my-snowflake:
    driver: snowflake
    account: xy12345
    warehouse: ANALYTICS_WH
    database: PROD
    schema_name: PUBLIC
    username: KTX_SERVICE
    password: env:SNOWFLAKE_PASSWORD
    role: ANALYST

For multiple schemas:

    schema_names:
      - PUBLIC
      - ANALYTICS
      - STAGING

Authentication

Features

Query history

Snowflake query history reads aggregated query-history templates from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY and feeds the same unified staged artifact shape as Postgres and BigQuery.

    context:
      queryHistory:
        enabled: true
        windowDays: 90
        minExecutions: 5
        filters:
          dropTrivialProbes: true
          serviceAccounts:
            patterns: ['^svc_']
            mode: exclude
        redactionPatterns: []

Dialect notes

• All identifiers are uppercase by default (case-insensitive matching)
• Connection context set per query (USE ROLE, USE WAREHOUSE, USE DATABASE, USE SCHEMA)
• Parameter binding uses positional ? placeholders
• Date values normalized to ISO 8601 strings

BigQuery

Authenticates via GCP service account credentials. Supports multi-dataset scanning and query-history configuration for INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Connection config

connections:
  my-bigquery:
    driver: bigquery
    credentials_json: file:~/.config/gcloud/bq-service-account.json
    dataset_id: analytics
    location: US

For multiple datasets:

    dataset_ids:
      - analytics
      - marketing
      - finance

Authentication

The project ID is extracted automatically from the service account JSON file. If you set project_id in ktx.yaml, ktx treats it as local descriptor and mapping metadata. The BigQuery connector still authenticates with the project_id inside credentials_json.

Features

Query history

BigQuery query history reads aggregated query-history templates from region-scoped INFORMATION_SCHEMA.JOBS_BY_PROJECT and feeds the same unified staged artifact shape as Postgres and Snowflake.

    context:
      queryHistory:
        enabled: true
        windowDays: 90
        minExecutions: 5
        filters:
          dropTrivialProbes: true
          serviceAccounts:
            patterns: ['@bot\\.']
            mode: exclude
        redactionPatterns: []

Dialect notes

• Parameter binding uses named @param syntax
• Arrays flattened to comma-separated strings in results
• Location specified at query execution time
• Supports max_bytes_billed and job_timeout_ms limits from ktx.yaml

MySQL

Standard MySQL/MariaDB connector with full foreign key support and schema introspection.

Connection config

connections:
  my-mysql:
    driver: mysql
    url: env:MYSQL_DATABASE_URL

Or with individual fields:

connections:
  my-mysql:
    driver: mysql
    host: mysql.internal
    port: 3306
    database: analytics
    username: ktx_reader
    password: env:MYSQL_PASSWORD
    ssl: true

Authentication

Features

Dialect notes

• Parameter binding uses positional ? placeholders
• Uses LIMIT X OFFSET Y for pagination
• Single database per connection (no multi-schema)
• Supports 20+ MySQL types including enum, json, datetime, decimal
• Table comments extracted with InnoDB metadata prefix stripping

SQL Server

Connects to Microsoft SQL Server and Azure SQL. Supports multi-schema scanning with dbo as the default schema.

Connection config

connections:
  my-sqlserver:
    driver: sqlserver
    url: env:SQLSERVER_DATABASE_URL

Or with individual fields:

connections:
  my-sqlserver:
    driver: sqlserver
    host: sql.internal
    port: 1433
    database: Analytics
    username: ktx_reader
    password: env:MSSQL_PASSWORD
    schema: dbo
    trustServerCertificate: true

For multiple schemas:

    schemas:
      - dbo
      - analytics
      - staging

Authentication

Features

Dialect notes

• Parameter binding uses @paramName syntax
• Row limiting uses SELECT TOP N * FROM (query) AS ktx_query_result
• Encryption is always required; certificate validation is optional
• Multi-schema support with per-schema isolation

SQLite

File-based connector using better-sqlite3. Ideal for local development, embedded analytics, or testing.

Connection config

connections:
  my-sqlite:
    driver: sqlite
    path: ./data/warehouse.sqlite

Path supports multiple formats:

# Relative path (resolved against project directory)
path: ./warehouse.sqlite

# Absolute path
path: /var/data/analytics.db

# Home directory expansion
path: ~/data/warehouse.sqlite

# Environment variable
path: env:SQLITE_DB_PATH

# URL format
url: sqlite:///path/to/db.sqlite

Authentication

No authentication required - SQLite is file-based. The file must be readable by the process running ktx.

Features

Dialect notes

• Synchronous query execution (no connection pooling)
• Parameter binding uses :paramName syntax
• Uses LIMIT X OFFSET Y for pagination
• SQLite type affinity system: TEXT, NUMERIC, INTEGER, REAL, BLOB
• Foreign key enforcement requires explicit PRAGMA foreign_keys = ON
• Database file must exist before ktx connection test or ingest runs

Common errors