Skip to content
Sarj Code Standards

no-analytical-queries-in-transactional-db

Keep DISTINCT, GROUP BY, and aggregate queries in analytical databases.

SARJ122 Rule source Tests Catalog JSON

Why

Project architecture reserves SQL deduplication, grouping, and aggregates for ClickHouse or another analytical database, preventing reporting work from competing with transactional reads. This is an explicit datastore policy, not an execution-cost estimate.

Fix

Move reporting queries to ClickHouse or another analytical database with an explicit freshness contract. Use EXISTS or indexed row lookups for operational reads. Preserve atomicity; a reviewed transactional invariant needs an exact, explained SARJ122 exception.

Examples

Before — flagged Transactional SQL deduplicates reporting rows
queries/events.sql
SELECT DISTINCT
  team_id
FROM
  event;
After — preferred ClickHouse owns a reporting rollup
queries/events.sql
-- dialect: clickhouse
SELECT
  team_id,
  COUNT(*)
FROM
  event
GROUP BY
  team_id;
Before — flagged A transactional count still violates the datastore policy
queries/events.sql
SELECT
  COUNT(*)
FROM
  event
WHERE
  id = $1;
After — preferred A transactional existence check uses an indexed predicate
queries/events.sql
SELECT
  EXISTS (
    SELECT
      1
    FROM
      event
    WHERE
      id = $1
  );
Before — flagged A temporary table does not exempt a transactional reporting query
queries/events.sql
CREATE TEMP TABLE report ON
COMMIT
DROP AS
WITH
  rows AS (
    SELECT DISTINCT
      team_id
    FROM
      event
  )
SELECT
  team_id
FROM
  rows;
After — preferred A temporary table can materialize an operational row lookup
queries/events.sql
CREATE TEMP TABLE report ON
COMMIT
DROP AS
WITH
  rows AS (
    SELECT
      team_id
    FROM
      event
    WHERE
      id = $1
  )
SELECT
  team_id
FROM
  rows;