dq-tier-status

Instructions

Implementation Reference

• src/tools/quality-scores.ts:50-118 (handler)

  Main handler function for dq-tier-status. Queries the DQ score table for today's (or given date's) overall_score per scope, compares against Tier SLA targets (Tier 1=99.5, Tier 2=99.0, Tier 3=95.0), and returns meeting/missing counts. Has two code paths: 'generic' flavor (per-scope tier column) vs 'us-all' flavor (single daily row, compares against DQ_TIER1_TARGET_PCT).

  export async function dqTierStatus(args: z.infer<typeof dqTierStatusSchema>): Promise<unknown> {
    const flavor = getDqFlavor();
    const cols = getDqColumns(flavor);
    const backend = config.dq.backend;
  
    if (cols.tier) {
      // Generic schema: original Tier 1/2/3 rollup against per-scope rows.
      const useDate = args.date ?? null;
      const todayClause =
        backend === "bigquery" ? "CURRENT_DATE()" : "CURRENT_DATE";
      const sql = useDate
        ? `
          SELECT ${cols.tier} AS tier, ${cols.scope} AS scope, overall_score
          FROM ${scoreTable()}
          WHERE ${cols.scoreDate} = ?
          ORDER BY ${cols.tier}, ${cols.scope}`
        : `
          SELECT ${cols.tier} AS tier, ${cols.scope} AS scope, overall_score
          FROM ${scoreTable()}
          WHERE ${cols.scoreDate} = ${todayClause}
          ORDER BY ${cols.tier}, ${cols.scope}`;
      const result = await dqQuery(sql, useDate ? [useDate] : []);
      const targets: Record<string, number> = { "1": 99.5, "2": 99.0, "3": 95.0 };
      const summary: Record<string, { target: number; observations: number; meeting: number; missing: number }> = {};
      for (const row of result.rows) {
        const tier = String(row.tier ?? "");
        const score = Number(row.overall_score ?? 0);
        if (!summary[tier]) {
          summary[tier] = { target: targets[tier] ?? 0, observations: 0, meeting: 0, missing: 0 };
        }
        summary[tier].observations += 1;
        if (targets[tier] != null && score >= targets[tier]) summary[tier].meeting += 1;
        else summary[tier].missing += 1;
      }
      return { backend: result.backend, schema: flavor, rowsExamined: result.rowCount, tiers: summary, rows: result.rows };
    }
  
    // us-all flavor: no per-scope tier column. Fetch latest single row and
    // compare overall_score vs DQ_TIER1_TARGET_PCT (default 99.5).
    const useDate = args.date ?? null;
    const todayClause = backend === "bigquery" ? "CURRENT_DATE()" : "CURRENT_DATE";
    const sql = useDate
      ? `
        SELECT ${cols.scoreDate} AS score_date, overall_score, total_checks, failed_checks
        FROM ${scoreTable()}
        WHERE ${cols.scoreDate} = ?`
      : `
        SELECT ${cols.scoreDate} AS score_date, overall_score, total_checks, failed_checks
        FROM ${scoreTable()}
        WHERE ${cols.scoreDate} = ${todayClause}`;
    const result = await dqQuery(sql, useDate ? [useDate] : []);
    const target = defaultTier1TargetPct();
    const row = result.rows[0];
    const score = row ? Number(row.overall_score ?? 0) : null;
    const meeting = score == null ? null : score >= target;
    return {
      backend: result.backend,
      schema: flavor,
      target,
      score,
      meeting,
      totalChecks: row?.total_checks ?? null,
      failedChecks: row?.failed_checks ?? null,
      scoreDate: row?.score_date ?? null,
      notes: [
        `${flavor} schema has a single overall_score per day (no per-scope tiers). Comparing against DQ_TIER1_TARGET_PCT=${target}.`,
      ],
    };
  }

• src/tools/quality-scores.ts:43-48 (schema)

  Zod schema for dq-tier-status input: optional 'date' (ISO YYYY-MM-DD), defaults to today.

  export const dqTierStatusSchema = z.object({
    date: z
      .string()
      .optional()
      .describe("ISO date (YYYY-MM-DD) to check, default = today"),
  });

• src/index.ts:104-104 (registration)

  Registration of the 'dq-tier-status' tool with description and schema binding. Imported from quality-scores.ts on line 43.

  tool("dq-tier-status", "Compare today's overall_score per scope against Tier SLA targets (Tier 1 99.5 / Tier 2 99.0 / Tier 3 95.0) and report meeting vs missing counts", dqTierStatusSchema.shape, wrapToolHandler(dqTierStatus));

• src/clients/dq-schema.ts:119-122 (helper)

  Helper function defaultTier1TargetPct() used by the us-all flavor branch of dqTierStatus. Returns DQ_TIER1_TARGET_PCT env var or defaults to 99.5.

  export function defaultTier1TargetPct(): number {
    const raw = parseFloat(process.env.DQ_TIER1_TARGET_PCT ?? "99.5");
    return Number.isFinite(raw) ? raw : 99.5;
  }

• src/tools/aggregations.ts:93-93 (helper)

  The dq-score-snapshot aggregation tool calls dqTierStatus({}) as a sub-component to include tier compliance data in the snapshot result.

  tier: () => dqTierStatus({}),