Use Cases

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

SELECT COUNT(*)
FROM customer_accounts
WHERE balance < 0
  AND account_status = 'active'

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

dqlabs measures run --id row_fail_check --param window_days=7
dqlabs measures run --id row_fail_check --param window_days=30

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

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

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

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

# 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)$'