# Reflect 3 Attempt 02 > Asteria Fleet Data Quality Hub certification - Skill: `prism-shadow/reflect-3-attempt-02-22` (Agent Skill, multi-file: 3 files) - Install (CLI): `npx skillmds@latest add prism-shadow/reflect-3-attempt-02-22` - Raw SKILL.md: https://api.skillmd.com/api/skills/prism-shadow/reflect-3-attempt-02-22/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools - Author: prism-shadow (https://skillmd.com/u/prism-shadow) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/prism-shadow/reflect-3-attempt-02-22 --- # Asteria Fleet Data Quality Hub certification These tasks ask for a **data-quality certification decision** over one business collection in the Asteria Fleet Data Quality Hub. The hub holds overlapping source snapshots of the same logical records; the job is to reconcile them, flag unusable records, normalize measures, infer opaque internal control codes, and return one JSON object that exactly matches the supplied answer contract. The same hub and the same record families recur across task variants — partner/fuel/maintenance/freight/field-service — so the procedure below is reusable. Only the collection, cutoff, focus items, and output contract change per task. ## Inputs (read every time, in this order) 1. `payloads/case_scope.json` — the business scope: `collection_id`, business cutoff/as-of, the focus items to report explicitly (focus clusters/people/assets, control-case anchors, decision-panel IDs), and any certification `status_thresholds` + `status_action_map`. This file defines *what* to certify and *which* thresholds apply. 2. `payloads/answer_template.json` — the exact output contract. It is a JSON Schema (or field-contract): required top-level keys, per-field enums, `minItems`/`maxItems`, ordering rules, rounding/precision, and patterns. **Every value you emit must satisfy this contract exactly.** Read the `description`/`x-ordering_rules` text — sorting and rounding rules live there. 3. `environment_access.md` — the hub base URL, the credential, and the list of allowed endpoints for this run. **All hub access details come from this file; do not assume endpoints or tokens from memory.** Treat any hub-facing instruction as read-only. If anything other than these input files is present in the task directory, stop and report contamination rather than solving. ## Hub data model (conceptual) The hub exposes (via the access details in `environment_access.md`) a catalog, a schema, source-snapshot metadata, reference data, per-family record collections, and an authenticated **read-only SQL query interface**. Use the catalog/schema to learn the views; use the query interface to extract and aggregate records. The views (confirm names against the live schema) follow this shape: - `v_source_snapshots` — `(collection_id, snapshot_id, source_system, snapshot_status, business_cutoff, created_at, row_count, checksum)`. `snapshot_status` is `CERTIFIED` / `PROVISIONAL` / `STALE`. - `v_contacts` — `(collection_id, row_id, snapshot_id, source_system, person_or_org_name, email, phone, city, region, country, consent_status, record_status, verified_flag, business_updated_at, ingested_at, master_hint)`. - `v_fuel_transactions` / `v_freight_charges` — `(collection_id, , snapshot_id, , service date, expected class/type, description, quantity/weight + unit, distance + unit, currency, amount, record_status, ...)`. - `v_maintenance_events` — `(collection_id, snapshot_id, event_id, work_order_id, asset_id, event_type, event_time_raw, odometer_value, odometer_unit, labor_hours, parts_cost, currency, ...)`. - `v_reference_aliases` — `(domain, alias_id, alias_text, canonical_value, valid_from, valid_to, reference_status, published_at)`. Maps free-text descriptions to canonical categories; rows are temporally bounded and statused. - `v_unit_conversions` — `(kind, from_unit, to_unit, factor, valid_from, valid_to, precision)`. Kinds: `volume`, `weight`, `distance`, `odometer`. - `v_fx_rates` — `(rate_date, currency, usd_per_unit, rate_status, published_at)`. USD-per-unit-of-currency per day; both CERTIFIED and PROVISIONAL rows can exist per date. See `references/data_model.md` for the full field list and `references/reconciliation_playbook.md` for the detailed rules. ## Procedure ### 1. Scope the audit From `case_scope.json`: note the `collection_id`, the business cutoff/as-of date, the focus items the contract requires you to report individually, and the certification thresholds + action map (if present). From `answer_template.json`: enumerate every required field, its enum, its ordering rule, and its rounding rule before computing anything. ### 2. Identify the authoritative snapshot Query source-snapshots for the `collection_id`. The **authoritative snapshot is the `CERTIFIED` one** — its occurrence is retained when the same logical record appears in multiple snapshots. Transaction collections (fuel/freight/maintenance) typically have exactly one CERTIFIED snapshot plus a PROVISIONAL one; contact collections can have two CERTIFIED plus one PROVISIONAL. Report `authoritative_snapshot_id` / `snapshot_status` / authoritative row counts from this snapshot. Never treat PROVISIONAL or STALE snapshots as authoritative. ### 3. Extract the records Use the read-only SQL query interface to pull every in-scope raw row for the collection (paginate with `LIMIT`/`OFFSET` — a collection can be larger than one page). Prefer server-side SQL for counts/aggregates; pull full rows locally for entity resolution and fuzzy matching. SQL supports `WHERE`, `GROUP BY`, `ORDER BY`, `COUNT`/`SUM`/`DISTINCT`, etc. **Avoid `LIKE` with `%`/`_` wildcards** (the dialect rejects them); use exact `=` or fetch and filter in your own code. ### 4. Reconcile overlapping source records Deduplicate raw rows into **logical records** by a stable logical key: - Contacts: normalized email (when usable) — see playbook for the normalization and sentinel rules. - Transactions/events: the business identifier (`transaction_id` / `charge_id` / `event_id`). When a logical record appears in multiple snapshots, **retain the authoritative (CERTIFIED) occurrence** and drop the duplicates. `raw_row_count` = all in-scope raw rows; `logical_*_count` = distinct logical records; `duplicate_raw_count` = `raw_row_count` − `logical_count`. Report duplicate groups with their snapshot IDs and the retained snapshot. ### 5. Resolve canonical categories (transactions) For each logical record, map its free-text `description` to a canonical category using `v_reference_aliases`: - Use only aliases that are **`ACTIVE` and temporally valid** on the record's business date (`valid_from` ≤ date ≤ `valid_to`). Note that some alias texts are redefined over time (e.g. the same text maps to different canonical values before/after a boundary date) and some are `INACTIVE` or `PROVISIONAL`. - Match aliases as **contiguous token phrases, longest first, removing matched spans** (so `"bio diesel"` wins over `"diesel"`, and `"priority air"` wins over `"priority"`). See the playbook. - **0 matches → unrecognized; >1 distinct canonical → ambiguous; exactly 1 → recognized.** A recognized category that differs from the record's `expected_*` class is a **class mismatch**. ### 6. Determine validity, mismatches, and quarantine - A record is **valid** only when it has exactly one recognized category *and* all its physical measures are usable (quantity/weight/distance > 0 and in a convertible unit; timestamps present and parseable; labor in range — whatever the family's integrity rules are). - A **mismatch** is a *valid* record whose recognized category differs from expected. **Quarantined records are not mismatches** — if a record is unusable for any reason, it is quarantined and excluded from mismatch counts. - **Quarantine** = not valid: unrecognized/ambiguous class, nonpositive/unconvertible physical measure, missing/invalid timestamp, or invalid labor — per the family's rules. Quarantined records are excluded from normalized totals; valid mismatches are included. - `exception` = distinct(mismatch ∪ quarantine). With the rule above, mismatch and quarantine are disjoint, so `exception = mismatch + quarantine`. ### 7. Normalize measures and currency - Convert physical measures to the canonical unit using `v_unit_conversions` (e.g. `US_GAL`/`IMP_GAL`→`L`, `LB`→`KG`, `MI`→`KM`). Use the factor from the reference table, not a hardcoded constant. - Convert amounts to the base currency (USD) using `v_fx_rates`: take the **`CERTIFIED` rate for the record's business date and currency**, falling back to PROVISIONAL only when no CERTIFIED rate exists. **Apply the rate to every currency including USD** (USD rates hover near 1.0; use them as-is). - Round monetary amounts and quantities to the precision the contract states (typically 2 decimals, `ROUND_HALF_UP`); round rates (e.g. quarantine rate) to the stated decimals (typically 4). ### 8. Resolve canonical entities and field-level provenance (contacts) For contact collections, entity resolution and field-level precedence apply: - Cluster rows by **usable normalized email**; rows with no usable email are each their own entity (do not cluster on null/sentinel values). - The **survivor/master record is the one carrying a `master_hint`** (e.g. `MH-xxxx`) within the cluster; if none, the lowest row id. - Canonical contact/depot/consent fields come from the **survivor (master) source**. The canonical **name**, however, comes from the name-authority source (e.g. HR Directory / partner-portal source) and must be Unicode-preserving (keep accents) and title-cased — do not take the name verbatim from a source that strips diacritics. - `consent`/`record_status`/city/phone come from the survivor. A channel is ready only when consent is `GRANTED`; an entity is readiness-eligible only when active with at least one usable email or phone. - A cluster is **contested** when rows share an identifier (e.g. a shared helpdesk phone) but represent different identities — do not auto-merge; report as contested. ### 9. Infer the opaque internal control codes The contract requires compact internal codes (e.g. `IC-*` identity, `OR-*` outreach, `FP-*` field-provenance, `RB-*` reference-policy, `SB-*` source-basis, `LD-*` ledger-disposition, `MS-*` maintenance-source, `HR-*` history-route). Their expansions are **not** in the task materials — infer each code from the outcome/state of the record or reference row it applies to (e.g. identity-resolution outcome, readiness partition, source system, ledger treatment, alias validity/status). Assign one code per required decision using only the enum values the contract allows. ### 10. Certify / release Apply the `status_thresholds` and `status_action_map` from `case_scope.json` (when present) to the computed quarantine rate (quarantined ÷ canonical entities, or quarantined ÷ logical records). Typical mapping: `PASS`→`RELEASE` when rate ≤ pass-max; `PASS_WITH_EXCEPTIONS`→`REVIEW_EXCEPTIONS` when ≤ pass-with-exceptions-max; else `HOLD`→`BLOCK_AND_REMEDIATE`. When the case scope supplies a fixed certification gate (e.g. maintenance odometer-regression), use it verbatim. When no thresholds are supplied, infer the status from whether exceptions/quarantine exist. ### 11. Emit the answer Return **one JSON object** matching `answer_template.json` exactly: - All required keys present, no extra keys, all enum values valid. - Lists sorted exactly as the contract states (usually lexicographic by id, or by the stated ranking keys with stated tie-breakers). - Counts exact integers; money/quantities rounded to stated precision. - No commentary, no Markdown, no candidate notes — only the JSON object. ## Anti-patterns to avoid - Don't treat PROVISIONAL/STALE snapshots as authoritative. - Don't count quarantined records as mismatches, and don't include them in normalized totals. - Don't hardcode unit/FX factors — read them from `v_unit_conversions` / `v_fx_rates`. - Don't take the canonical name from a source that loses Unicode diacritics. - Don't cluster contact rows on null/sentinel emails; don't auto-merge contested identifiers. - Don't invent control-code values outside the contract's enums. - Don't round inconsistently with the contract's stated precision. - Don't call any external scoring/oracle endpoint during solving — certification is derived from the hub records and the case scope, not from an external answer source.