> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dqlabs.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Use Cases

> Real-world examples of Query metrics — custom SQL checks and parameterized patterns.

<script type="application/ld+json">
  {`{
            "@context": "https://schema.org",
            "@type": "TechArticle",
            "headline": "Query Metric Examples",
            "description": "Real-world examples of Query metrics - custom SQL checks and parameterized patterns.",
            "url": "https://docs.dqlabs.ai/architecture/metrics/query/examples",
            "publisher": {
              "@type": "Organization",
              "name": "DQLabs Inc",
              "logo": "https://media.brand.dev/332adc35-5bc4-4d2b-bf78-256aa4a5e414.svg"
            }
            }`}
</script>

## Standard query metric examples

**Order line item reconciliation:** Compute the relative discrepancy between line item totals and order totals. A result above 0.01 (1%) triggers an alert — catching ETL bugs where line items were loaded without the parent order record updating.

```sql theme={null}
SELECT ABS(SUM(line_amount) - SUM(order_total)) / SUM(order_total)
FROM orders
JOIN order_line_items USING (order_id)
WHERE order_date = CURRENT_DATE
```

**Detecting negative balances:** Count active customer accounts with a negative balance. Set threshold to `> 0` for zero tolerance — any active account with a negative balance is a business rule violation.

```sql theme={null}
SELECT COUNT(*)
FROM customer_accounts
WHERE balance < 0
  AND account_status = 'active'
```

<Note>
  Query metrics run with the credentials of the connected source. Ensure the service account has `SELECT` access to all tables referenced in the query. Queries that modify data (`INSERT`, `UPDATE`, `DELETE`) are not permitted and will be rejected.
</Note>

***

## Parameterized query metric examples

### Rolling window checks

**Goal:** Run the same check for the last 7, 30, or 90 days without creating separate metrics.

```sql theme={null}
SELECT COUNT(*) AS value
FROM {{table}}
WHERE business_date >= CURRENT_DATE - {{window_days}}
  AND status = 'failed'
```

| Parameter     | Type   | Values                        |
| ------------- | ------ | ----------------------------- |
| `table`       | SOURCE | Resolved from connected asset |
| `window_days` | number | `7`, `30`, `90`               |

Run for different windows via the popup or CLI:

```bash theme={null}
dqlabs measures run --id row_fail_check --param window_days=7
dqlabs measures run --id row_fail_check --param window_days=30
```

Each run produces a separate result entry with the parameter values recorded in the audit log.

***

### Partitioned execution by date

**Goal:** Validate data quality for a specific partition (e.g., effective date) and re-run for historical dates independently.

```sql theme={null}
SELECT COUNT(*) AS value
FROM {{table}}
WHERE effective_date = {{effective_date}}
  AND record_status IS NULL
```

| Parameter        | Type   | Required      |
| ---------------- | ------ | ------------- |
| `table`          | SOURCE | Auto-resolved |
| `effective_date` | date   | Yes           |

Re-run for a different date by changing `effective_date` in the popup — the metric definition stays the same.

***

### Segmented KPI validation

**Goal:** Apply the same revenue completeness check for each sales region using one metric definition.

```sql theme={null}
SELECT SUM(revenue) AS value
FROM {{table}}
WHERE region = {{region}}
  AND business_date BETWEEN {{start_date}} AND {{end_date}}
```

| Parameter    | Type   | Values               |
| ------------ | ------ | -------------------- |
| `table`      | SOURCE | Auto-resolved        |
| `region`     | ENUM   | `NA`, `EMEA`, `APAC` |
| `start_date` | date   | Required             |
| `end_date`   | date   | Required             |

Both runs share the same metric ID — results are tagged with their effective parameter values.

***

### Threshold injection (pass/fail in-query)

**Goal:** Encode the pass/fail decision directly into the SQL so the warehouse handles it rather than Prizm's threshold layer.

```sql theme={null}
WITH agg AS (
  SELECT SUM(revenue) AS revenue
  FROM {{table}}
  WHERE business_date BETWEEN {{start_date}} AND {{end_date}}
)
SELECT
  revenue AS value,
  CASE WHEN revenue >= {{min_revenue}} THEN 'PASS' ELSE 'FAIL' END AS status
FROM agg
```

| Parameter     | Type   | Default       |
| ------------- | ------ | ------------- |
| `table`       | SOURCE | Auto-resolved |
| `start_date`  | date   | —             |
| `end_date`    | date   | —             |
| `min_revenue` | number | `500000`      |

Different teams can supply different `min_revenue` thresholds for the same underlying table without duplicating metric definitions.

***

### Region-specific REGEX validation

**Goal:** Validate that a material type code (MTART) matches the correct format for each region — format differs per region.

```sql theme={null}
SELECT COUNT(*) AS failing_rows
FROM {{table}}
WHERE MTART IS NOT NULL
  AND NOT REGEXP_LIKE(MTART, {{mtart_regex}})
```

| Parameter     | Type     | Required      |
| ------------- | -------- | ------------- |
| `table`       | SOURCE   | Auto-resolved |
| `mtart_regex` | CONSTANT | Yes           |

```bash theme={null}
# EMEA: FERT, HALB, ROH
dqlabs measures run --id mtart_check --param mtart_regex='^(FERT|HALB|ROH)$'

# APAC: FERT, VERP
dqlabs measures run --id mtart_check --param mtart_regex='^(FERT|VERP)$'
```

<Tip>
  When the same REGEX rule needs to run against many tables — each with its own override — use a Metric Template instead. See the [Metric Template overview](/architecture/metrics/metric-template/overview) for template-based use cases.
</Tip>