Skip to content

Query Definition Files

Reusable analytics queries now live beside your workflows, dashboards, and agents. Defining them as code means every tenant (and marketplace pack) can share the same vetted dbt-backed queries without duplicating YAML across dashboards.

Where to store them

Create .yaml files under one of the configuration roots that ConfigurationLoader scans:

  • .kiket/queries/*.yaml
  • .kiket/analytics/queries/*.yaml
  • definitions/<template>/.kiket/queries/*.yaml

Files must declare model_version: "1.0". During a sync the loader parses each file, normalises the contents, and saves the results under project.settings["queries"] keyed by query.id.

Example

model_version: "1.0"
query:
  id: cycle_time_trend
  name: Cycle Time Trend
  description: Daily average cycle time per project.
  model: fct_cycle_time
  tags:
    - delivery
  select:
    - completed_on
    - avg_cycle_days
  filters:
    project_id: ":project_id"
    start_date: ":start_date"
  parameters:
    - name: project_id
      type: integer
      required: true
    - name: start_date
      type: datetime
      default: "<%= 30.days.ago.beginning_of_day.iso8601 %>"
  order_by:
    - completed_on DESC
  group_by:
    - completed_on
  limit: 500

Schema keys

Key Type Required Notes
query.id String ✅ Lowercase slug used by dashboards, agents, and CLI helpers.
query.name String ➖ Human-readable label in the UI.
query.description String ➖ Short summary for catalogs.
query.model String ✅ dbt model/table inside your tenant warehouse.
query.tags Array\<String> ➖ Filter/group queries (delivery, finance, adoption, etc.).
query.select Array\<String> ➖ Columns to project (default *).
query.filters Object ➖ Column/value pairs. Use placeholders (":project_id") to mark parameters.
query.parameters Array\<Parameter> ➖ Metadata for bind parameters. Supports string, integer, float, boolean, and datetime.
query.order_by Array\<String> ➖ Sort clauses (e.g. completed_on DESC).
query.group_by Array\<String> ➖ Grouping columns.
query.limit Integer ➖ Safety cap for large result sets.

Strings can be used as shorthand parameters (- project_id). They are expanded to { name: "project_id", type: "string", required: false }.

Using queries

  • Dashboards: Continue to specify query_config inside each widget/alert. The query attribute should match the definition ID so tooling can pull metadata (parameters, tags, docs links) and display usage hints.
  • Agents & CLI: Agents referenced in .kiket/agents and CLI commands (kiket analytics run-query) can look up project.settings["queries"][id] to understand required parameters before issuing API calls.
  • Docs & Catalogs: Because every definition includes tags, description, and source, the admin UI and docs-site can render query catalogs automatically.

Validation tips

  • Use kiket analytics lint (or kiket sync) to validate locally.
  • Keep IDs stable—changing the slug requires updating every dashboard widget/alert referencing it.
  • Prefer aggregated dbt models over raw tables, and enforce reasonable limits to control warehouse spend.
  • Use placeholders instead of hard-coded IDs so queries remain portable across environments.

Surfacing the catalog

  • Admin UI: The Analytics admin page now includes a “Query Catalog” picker—select a project to preview each query’s metadata, parameters, and source path.
  • CLI: kiket analytics queries <project-id> hits /api/v1/projects/:id/queries so you can audit query definitions directly from a terminal or export them as JSON/CSV.