Skip to content
Skills Library
Data & analytics · Testing

query

/query

Run a semantic query against the database via airlayer. Use when the user wants to query data through the semantic layer, test view definitions, or debug query results.

Repository
airlayer ↗
Licence
Apache-2.0
Last repository change
24.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 24.10.2026, without references to retired models or APIs.

Licence

Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.

What it does and when to use it

Run a semantic query against the database via airlayer. Use when the user wants to query data through the semantic layer, test view definitions, or debug query results.

Original author description

Run a semantic query against the database via airlayer. Use when the user wants to query data through the semantic layer, test view definitions, or debug query results.

How it works

  1. Usage
  2. Filter operators
  3. Reading the envelope
  4. Interpreting errors
  5. Motifs
  6. Custom motifs
  7. Discovery
  8. Peer cohorts
  9. Saved queries
  10. JSON query format
Original SKILL.md headings · 214 lines, 1,355 words
SKILL.md preview
---
name: query
description: Run a semantic query against the database via airlayer. Use when the user wants to query data through the semantic layer, test view definitions, or debug query results.
---

# Run a Semantic Query

You are running a semantic query through airlayer's execution interface.

## Usage

```bash
# Compile + execute (returns structured JSON envelope)
airlayer query -x \
  --dimension <view>.<dim> \
  --measure <view>.<measure> \
  [--filter <view>.<dim>:<operator>:<value>] \
  [--order <view>.<member>:asc|desc] \
  [--limit N] \
  [--segments <view>.<segment>] \
  [--motif <motif_name>] \
  [--motif-param <key>=<value>]
```

## Filter operators

Format: `member:operator:value` (multiple values comma-separated)

| Operator | Example | Description |
|----------|---------|-------------|
| equals | `orders.status:equals:completed` | Exact match |
| notEquals | `orders.status:notEquals:cancelled` | Not equal |
| contains | `orders.name:contains:widget` | String contains |
| notContains | `orders.name:notContains:test` | String doesn't contain |
| startsWith | `orders.name:startsWith:Pro` | String starts with |
| endsWith | `orders.name:endsWith:Plan` | String ends with |
| gt | `orders.amount:gt:100` | Greater than |
| gte | `orders.amount:gte:100` | Greater than or equal |
| lt | `orders.amount:lt:1000` | Less than |
| lte | `orders.amount:lte:1000` | Less than or equal |
| in | `orders.status:in:completed,shipped` | In list |
| notIn | `orders.status:notIn:cancelled,returned` | Not in list |
| set | `orders.email:set` | Is not null |
| notSet | `orders.email:notSet` | Is null |
| beforeDate | `orders.created_at:beforeDate:2025-01-01` | Before date |
| afterDate | `orders.created_at:afterDate:2025-01-01` | After date |

## Reading the envelope

The `--execute` flag returns a JSON envelope:

```json
{
  "status": "success",           // or "parse_error", "compile_error", "execution_error"
  "sql": "SELECT ...",           // the compiled SQL
  "columns": [...],              // column metadata (name, member, kind)
  "data": [...],                 // result rows (max 50)
  "row_count": 3,                // true total row count
  "views_used": ["orders"],      // which .view.yml files were involved
  "error": null                  // error message if status != "success"
}
```

## Interpreting errors

- **parse_error**: Bad YAML in view files, or invalid query input. Fix the YAML syntax.
- **compile_error**: Member path doesn't exist, or join can't be resolved. Check dimension/measure names.
- **execution_error**: Database rejected the SQL. Check `expr` fields — the column names may be wrong. The `sql` field shows exactly what was sent.

## Motifs

Motifs add post-aggregation analytical columns by wrapping the base query as a CTE. Use `--motif <name>` on the CLI or `"motif": "<name>"` in JSON queries.

**Builtin motifs:** yoy, qoq, mom, wow, dod, anomaly, contribution, trend, moving_average, rank, percent_of_total, cumulative.

- **contribution**: adds `total` and `share` columns (what % does each group contribute?)
- **rank**: adds `rank` column (ordered by the measure descending)
- **percent_of_total**: adds `percent_of_total` column (100 * measure / total)
- **anomaly**: adds `mean_value`, `stddev_value`, `z_score`, `is_anomaly` columns (default z-score threshold: 2)
- **yoy**: adds `previous_value`, `growth_rate` — use with `granularity: year`
- **qoq**: adds `previous_value`, `growth_rate` — use with `granularity: quarter`
- **mom**: adds `previous_value`, `growth_rate` — use with `granularity: month`
- **wow**: adds `previous_value`, `growth_rate` — use with `granularity: week`
- **dod**: adds `previous_value`, `growth_rate` — use with `granularity: day`
- **moving_average**: adds `moving_avg` column (7-period rolling average, requires time dimension)
- **cumulative**: adds `cumulative_value` column (running sum, requires time dimension)
- **trend**: adds `row_n`, `slope`, `intercept`, `trend_value` columns (linear regression, requires time dimension)

**Critical:** PoP motifs use `LAG(1)`, so granularity MUST match: `yoy` needs `year`, `mom` needs `month`, etc.

```bash
# Non-time motif (contribution analysis)
airlayer query -x \
  --dimension orders.category \
  --measure orders.total_revenue \
  --motif contribution

# Period-over-period (granularity must match motif)
airlayer query -x -q '{
  "measures": ["orders.total_revenue"],
  "time_dimensions": [{"dimension": "orders.order_date", "granularity": "day"}],
  "motif": "dod"
}'

# Anomaly with custom threshold
airlayer query -x -q '{
  "measures": ["orders.total_revenue"],
  "motif": "anomaly",
  "motif_params": {"threshold": 3}
}'
```

### Motif params

Motif params control which measure/dimension a motif operates on. Pass them via `motif_params` in JSON queries or `--motif-param` on the CLI.

**Auto-binding:** When a query has exactly one measure, `{{ measure }}` auto-binds to it. When there are multiple measures, you MUST specify which one via `motif_params`. Same rule for `{{ time }}` with multiple time dimensions.

```bash
# Single measure — auto-binds, no motif_params needed
airlayer query -x \
  --measure orders.total_revenue \
  --motif rank

# Multiple measures — must specify which measure the motif operates on
airlayer query -x \
  --measure orders.total_revenue --measure orders.order_count \
  --motif rank --motif-param measure=orders.total_revenue
```

**motif_params values are semantic member names** (e.g., `orders.total_revenue`), not SQL aliases. They are resolved to CTE column aliases internally.

Other params:
- `anomaly`: `threshold` — z-score threshold (default: 2)
- `moving_average`: `window` — periods preceding (default: 6, meaning 7-period window)

## Custom motifs

Custom motifs (`.motif.yml` in `motifs/`) extend the builtin catalog. They use `{{ param }}` substitution and are always single-stage. Custom motifs can declare multiple `type: measure` params for different roles:

```yaml
name: ratio
params:
  numerator: { type: measure }
  denominator: { type: measure }
outputs:
  - name: ratio
    expr: "CAST({{ numerator }} AS DOUBLE) / NULLIF({{ denominator }}, 0)"
```

```bash
# Custom motif with two measure params
airlayer query -x \
  --measure orders.total_revenue --measure orders.order_count \
  --motif ratio \
  --motif-param numerator=orders.total_revenue \
  --motif-param denominator=orders.order_count
```

## Discovery

Before querying, discover what's available. All commands auto-detect the project root — no `--config` needed from inside the project.

```bash
# List all views, dimensions, measures (machine-readable)
airlayer inspect --json

# List all motifs with params and outputs
airlayer inspect --motifs

# List all saved queries with steps
airlayer inspect --queries
```

`inspect --json` also reports which entities declare peer cohorts — per entity under `views[].hierarchy[].cohorts`, and lifted into `ontology.comparability` as its own edge kind (with `banded`, `band_measure`, `band_per`, `tolerance`, `require`, `min_peers`, `exclude_self`, `reciprocal: false`, and `band_window` when the band declares one).

## Peer cohorts

A regular query aggregates rows into groups. A **cohort** does something a query cannot express: it compares each instance of an entity against its own peer group, where "peer" is defined per subject by a size band plus exact-match dimensions. Because the band is centred on the subject, membership is deliberately non-reciprocal — it is not a `GROUP BY` and cannot be written as one.

```bash
airlayer cohort sales.wage_pct --time sales.sale_date --period 2025-01-01:2025-03-31
airlayer cohort sales.wage_pct --cohort store_id.size_matched \
  --time sales.sale_date --period 2025-01-01:2025-03-31 --statistic p75 --json
```

`--cohort entity.cohort_name` is optional: without it the measure's `default_cohort` is used, and the result names whichever cohort was actually used. `--statistic median|p75|best_peer` (default `median`) picks the baseline over the peer group. A promoted measure (`stores.wage_pct`, induced from `sales`) is a valid target, but needs `--cohort` spelled out — `default_cohort` is only read off the view that literally declares the measure.

A cohort whose band declares a `window:` measures the band over a lookback window anchored at the period start (so it contains the period), while the compared measure stays on the period you asked for — so `band_window` in the result names a different range from `period`. Say which is which when you report it; "compared against stores of similar size" means similar over the band window, not over the reporting period. That cohort also excludes any entity present in one window and not the other, with a reason naming which — those are not missing stores, they are stores that cannot be placed on the size axis or cannot be read over the period.

Read the result carefully: `gap` is positive-means-opportunity in both polarities; `sufficient: false` marks a peer group thinner than `min_peers` (returned, not filtered); `peer_count: 0` means there is no baseline at all, so its `baseline`/`gap` of `0.0` are placeholders, not a verdict; and `excluded` lists every pulled row that could not be compared, with a reason — a NULL `require` value, an un-normalisable band, an unreadable measure, or a NULL entity key (an orphaned fact row, which is no entity at all, reported under a synthesized `(null) [<require values>]` id rather than as a subject). Report them rather than letting them disappear, and describe a `(null)` entry as a row with no entity, not as a missing store.

## Saved queries

Run a saved query by filepath:

```bash
# Compile all steps to SQL (dry run)
airlayer query queries/revenue_investigation.query.yml

# Execute all steps against the database
airlayer query queries/revenue_investigation.query.yml -x
```

## JSON query format

For complex queries, use `-q` with JSON:

```bash
airlayer query -x -q '{
  "dimensions": ["orders.category", "orders.region"],
  "measures": ["orders.total_revenue"],
  "filters": [{"member": "orders.status", "operator": "equals", "values": ["completed"]}],
  "order": [{"id": "orders.total_revenue", "desc": true}],
  "limit": 10
}'
```

Versions

RepositoryScoreLicence
This page · oxy-hq/airlayer100Apache-2.0
h1994st/rllvm100Apache-2.0
garrytan/gbrain100MIT
garrytan/gbrain100MIT
proteanhq/protean100Apache-2.0
erayozenn/BigHead-2ndBrain100MIT
lambdalisue/cclens100MIT
oxy-hq/airlayer100Apache-2.0
kouko/monkey-skills100MIT

Database snapshot: 13.11.2026 · package version 4f413f2d409c (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100bootstrap
    Bootstrap a semantic layer from a database. Use when the user wants to create .view.yml files from their warehouse schema, or when starting a new airlayer project from scratch.
  • 100migrate-from-cube
    Migrate a Cube.js semantic layer to airlayer .view.yml files. Use when the user has existing Cube.js schema files (.js or .yml) they want to convert to airlayer format.
  • 95omni-query
    Run queries against Omni Analytics' semantic layer using the Omni CLI, interpret results, and chain queries for multi-step analysis. Use this skill whenever someone wants to query data through Omni, run a report, get metrics, pull numbers, analyze data, ask "how many" / "what's…
  • 100semantic-model
    List, read, and interpret Microsoft Fabric Semantic Models (Power BI datasets). Use when an agent needs to understand available business metrics, DAX measures, table relationships, or column definitions before writing DAX queries, building reports, or validating Gold-layer…
  • 100query-optimizer
    Optimize SQL queries for performance with indexing strategies, query rewriting, and execution plan analysis. Use when queries are slow, optimizing database performance, or analyzing query execution.
  • 100power-query
    Author, validate, and test Power Query M expressions in semantic model partitions. Automatically invoke when the user mentions "Power Query", "M code", "M expression", "partition expression", "query folding", or asks to "write Power Query", "fix Power Query", "test a partition",…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.