Solve Asteria Fleet Data Quality Hub reconciliation/certification tasks — reconcile overlapping source snapshots of fuel, freight, maintenance, and contact collections, compute quality/normalization metrics, infer opaque control codes, and emit the JSON certification answer. Read BEFORE acting on any task that names the "Asteria Fleet Data Quality Hub", mentions source-snapshot reconciliation, business cutoffs, canonical entities, quarantine, mismatch/unrecognized categories, channel readiness, or opaque Asteria control codes (IC/OR/FP, RB/SB/LD, MS/HR).
Instalar com Codex ou Claude Copie este prompt, cole no Codex, Claude ou outro assistente e deixe que ele revise a página da skill e instale para você.
Um comando direto ignora o prompt de revisão. Verifique a origem antes de executá-lo.
Instruções da origem · Visualização somente leitura
name
asteria-fleet-data-quality-reconciliation
description
Solve Asteria Fleet Data Quality Hub reconciliation/certification tasks — reconcile overlapping source snapshots of fuel, freight, maintenance, and contact collections, compute quality/normalization metrics, infer opaque control codes, and emit the JSON certification answer. Read BEFORE acting on any task that names the "Asteria Fleet Data Quality Hub", mentions source-snapshot reconciliation, business cutoffs, canonical entities, quarantine, mismatch/unrecognized categories, channel readiness, or opaque Asteria control codes (IC/OR/FP, RB/SB/LD, MS/HR).
Asteria Fleet Data Quality Hub Reconciliation
A family of tasks asks you to audit one Asteria Fleet collection, reconcile its
overlapping source records as of a stated business cutoff, and return a single
JSON object matching an answer_template.json. The five collection families are:
Every collection is published as 2–3 snapshots (CERTIFIED + PROVISIONAL, sometimes a
second CERTIFIED source). Overlapping business records appear in more than one snapshot.
Your job is to collapse duplicates to logical records, classify data-quality issues,
normalize units/currency, infer opaque control codes, and certify.
This skill gives the procedure and the decoded reconciliation rules. It does NOT
contain candidate answers or task-specific final values — apply the rules to the live hub.
1. Inputs you are given
For each task directory train_tasks/<task>/input/:
prompt.txt — the business framing.
payloads/case_scope.json — the authoritative specification: collection_id,
business cutoff, focus seeds/anchors, decision-panel IDs, thresholds, sort orders.
Treat this as the source of truth for parameters.
payloads/answer_template.json — the JSON-Schema the answer must match exactly
(required keys, enums, patterns, item counts, ordering rules). Read it first.
/api/reference/conversions?kind=<volume|distance|weight|odometer> — unit factors.
/api/reference/fx — FX rates (no filter).
Discovery order that always works: read case_scope.json → GET schema (views+fields)
→ GET snapshots (?collection=) → SQL-query the relevant view filtering by
collection_id → fetch aliases/conversions/fx as needed.
3. Hub data model — field decoding
Snapshots & source systems
Each snapshot carries snapshot_id, snapshot_status (CERTIFIED > PROVISIONAL > STALE),
source_system, row_count, business_cutoff, created_at, ingested_at, checksum.
Transaction/event/charge views carry snapshot_id but notsource_system — join via
v_source_snapshots. Contact views DO carry source_system.
Authoritative snapshot = the one with the best snapshot_status (CERTIFIED first).
When two snapshots are both CERTIFIED (contacts), prefer by tie-break in the case scope
or by snapshot_id ascending. PROVISIONAL is supplementary feed data.
Overlap rule (the core reconciliation)
The same business record appears in multiple snapshots. The only field that differs
between duplicate occurrences is typically ingested_at (and for maintenance,
event_status/ingested_at; for contacts, the whole row is a fresh source-system view).
Collapse by the business key:
fuel: transaction_id
freight: charge_id
maintenance: event_id
contacts: by identity key (see §6), not by row_id (each snapshot invents fresh row_ids)
raw_row_count − logical_count = duplicate_raw_count. The authoritative occurrence is
retained; the others are dropped. For contacts, every source-system row is retained as
a member of the cluster.
Aliases (category resolution)
v_reference_aliases (domain, alias_id, alias_text, canonical_value,
valid_from, valid_to, reference_status, published_at). Map a free-text
description (purchased_description / description) to a recognized canonical category
by matching alias_text substrings (case-insensitive) in the description.
An alias is effective as-of the business cutoff only if valid_from <= cutoff_date
AND (valid_to is null OR valid_to >= cutoff_date) AND reference_status != INACTIVE.
The deliberate trap is a single alias_text that has more than one reference row
(e.g. an INACTIVE/expired one plus an ACTIVE one with later valid_from, or a short
generic token like priority used as a catch-all). A naive reference_status=ACTIVE
filter picks the wrong row when the cutoff falls before the new row's valid_from. The
ONLY safe selection is the row effective as-of the cutoff per the rule above. Always
GROUP alias rows by alias_text and check every row's validity window before deciding.
PROVISIONAL alias rows are candidate mappings not yet adopted — treat a description
whose only effective match is a PROVISIONAL alias as not-yet-recognized (do not promote it
to a canonical category for reconciliation). Scan reference_status for PROVISIONAL rows
in the live alias set rather than assuming any named text.
A description that matches alias_texts leading to two distinct canonical values =
ambiguous → unrecognized. A description with no effective alias match = unrecognized.
Resolution detail: if all matched alias_texts agree on one canonical value (even several
alias_texts), that is a unique match, not ambiguous (e.g. "ULSD road diesel" matches
both "ulsd" and "road diesel" but both → DIESEL → unique).
Unit conversions (v_unit_conversions, kind/from_unit/to_unit/factor/precision)
value_canonical = value_raw * factor. Canonical units: L (volume), KM
(distance & odometer), KG (weight).
weight: LB→KG 0.45359237, KG→KG 1.0
Round volume/weight/distance totals to 2 decimals; the precision field is the
per-value display precision, not the aggregate rounding.
FX (v_fx_rates, rate_date/currency/usd_per_unit/rate_status/published_at)
usd_amount = amount * usd_per_unit. USD's own rate is 1.0.
Match the rate by currency + rate_date == the transaction's business date
(purchased_at/service_date date portion). If the exact date is missing, use the
latest prior available date.
Prefer the CERTIFIED rate over PROVISIONAL for the same currency+date — when two rows
share a currency+date, keep the one with the better rate_status (CERTIFIED > PROVISIONAL)
and, on a tie, the later published_at. Verify the live v_fx_rates rows rather than
assuming any fixed publish time.
4. Universal certification gate
Status/action mapping (always present in case_scope as status_action_map or
status_thresholds/certification_gate):
PASS → RELEASE
PASS_WITH_EXCEPTIONS → REVIEW_EXCEPTIONS
HOLD → BLOCK_AND_REMEDIATE
Thresholds (contact tasks) use quarantine_rate = quarantined_rows / canonical_entity_count
rounded to 4 decimals: PASS if <= pass_max_quarantine_rate (usually 0.0);
PASS_WITH_EXCEPTIONS if <= pass_with_exceptions_max_quarantine_rate (usually 0.04); else HOLD.
Transaction/maintenance gates: the case_scope states the exact gate (e.g. maintenance's
certification_gate directly says HOLD/BLOCK_AND_REMEDIATE; freight/fuel infer HOLD
when invalid physical measures exist, else PASS_WITH_EXCEPTIONS if any mismatch/quarantine,
else PASS). When the scope states the gate explicitly, use it verbatim.
5. Reconciliation per family
Fuel & freight (parallel pipeline)
Collapse snapshots → logical records (retain CERTIFIED occurrence per business key).
Resolve each description → recognized canonical category via effective aliases.
Aggregates: per-category totals (sorted by category), total volume/weight/distance/spend,
focus-asset rollups, merchant/carrier exception ranking (exception_count = distinct
logical records with a mismatch OR quarantine; sort by exception_count desc then id asc
for merchants; for carriers by mismatch_spend_usd desc then carrier_id asc — mismatch
spend = USD on valid class-mismatch records only).
Duplicate groups (freight: one object per charge_id with >1 raw occurrence, retained
snapshot = authoritative).
Maintenance
Collapse snapshots → 1 retained row per event_id (retain CERTIFIED).
Issue counts (row-level unless scope says logical): missing event_time_raw (null/blank),
invalid timestamp (unparseable, e.g. 2026-99-45 25:61), invalid odometer (<=0),
negative labor (<0), extreme labor (a sentinel cap stated in the case_scope; flag any
labor value at/above it), odometer regression (later event's odometer < earlier event's
max for the same asset, chronological by time). Pull the concrete labor cap from the
case_scope rather than assuming a fixed value.
invalid_event_ids = events rejected for missing/unparseable time or invalid
odometer/labor range. Sequence-only regressions go in corrected_metrics, not invalid_event_ids.
corrected_metrics.total_distance_km = Σ over assets of (last reliable odometer − first
reliable odometer) in the reconstructed Q1 history, odometer converted to KM, kept
monotonically non-decreasing (drop regressed readings), 2 decimals.
asset_risk_ranking: sort by rejected_event_count DESC, regression_event_count DESC,
asset_id ASC; top 5.
duplicate_groups: one per event_id in >1 snapshot — logical_event_id, all snapshot_ids
(sorted), retained_event_id, retained_snapshot_id (authoritative).
event_decision_panel: one coded decision per scoped event_id (sorted asc).
Contacts (partner/dealer/warranty/field-service)
Build clusters of the same logical person across source systems using the identity
key: the numeric token in the clean @example-fleet.com email local-part
(e.g. sofia.smith.0 → token 0; token is stable across all 3 source-system rows of one
person). Rows whose email is a placeholder (null/none/n/a/blank) or a .part.NNN/
.fiel.NNN@mail.example decoy do NOT share a token — treat each as its own
single-source identity (these are deliberately-distinct decoys, NOT the 3rd snapshot of a
clean person).
Canonical fields per cluster: pick by survivorship precedence — prefer authoritative
snapshot, then verified_flag=1, then source priority (contacts: Compliance Master >
Partner Portal/HR Directory > CRM/Dispatch; field-service: HR Directory > Identity
Registry > Dispatch). Email = NFKC-trim-lowercase. Phone = digits only, stripping
spurious country codes (+1/+44/+21 leading) to the canonical 10-digit form. City/name
= trimmed NFKC; name title-cased.
Quarantine = cluster has no usable email AND no usable phone.
Channel readiness (over readiness-eligible = ACTIVE + ≥1 usable email/phone): bucket
by which channels are usable AND consent is GRANTED:
both / email_only / phone_only / not_ready (consent not GRANTED).
Contested / no-automerge: a shared identifier (any master_hint value shared by
1 distinct identity, or a phone number shared across distinct identities, or any field
the case flags as a shared business line) is contested → CONTESTED_NO_AUTOMERGE. Same
name+phone across sources that genuinely disagree = do not merge. Detect these by
scanning the live data: any identifier value used by rows belonging to >1 identity key is
contested — do not hardcode specific hint strings.
6. Opaque control codes — inference
Codes are not documented in the task materials; infer them from record characteristics.
The allowed enum values come from each task's answer_template.json. Use these mappings as
the starting interpretation and confirm against the enum set:
outreach_code (OR-15/35/60/80): consent status GRANTED→OR-80, PENDING→OR-60,
DENIED→OR-35, UNKNOWN→OR-15.
field_provenance_code (FP-20/55/75): authoritative source verified_flag=1 → FP-75;
≥2 contributing source systems → FP-55; single unverified source → FP-20.
Fuel/freight (RB / SB / LD):
reference_policy_code / reference-row decision (RB-17/42/83): ACTIVE alias effective
as-of cutoff → RB-83; ACTIVE but not-yet-effective-as-of-cutoff OR PROVISIONAL →
RB-42; INACTIVE/expired → RB-17. (The reference set is seeded so that across the
scoped alias_ids each of RB-17/42/83 appears at least once — apply the as-of rule per row
to distribute them, do not hardcode.)
source_basis_code / source-retention (SB-24/61/79): retained occurrence from the
CERTIFIED/authoritative snapshot → SB-79; from PROVISIONAL → SB-24.
maintenance_source_code (MS-12/47/86): retained from authoritative snapshot → MS-86;
from provisional → MS-47; (MS-12 reserved for the third/legacy source when present).
When a record could match two rules, prefer the most specific / most severe applicable
code, and always pick a value from that field's enum.
7. Output discipline
Produce one JSON object matching answer_template.json exactly: every required
key present, no extra top-level keys beyond the template, every array at its
minItems/maxItems, every value from its enum, every string matching its pattern.
Sort/Ordering is graded — follow each field's description/x-ordering_rules:
IDs lexicographically ascending; cluster/asset arrays by their stated sort key; ranking
arrays by rank ascending. Member-row-id lists are deduped + lexicographically sorted.
Numeric precision: counts are exact integers; currency/volume/distance/odometer rounded
to the stated decimals (usually 2; quarantine_rate to 4). No floats where the contract
says integer.
Stable IDs only: use IDs present in the live hub or supplied in the case_scope. Never
invent canonical classes, snapshot IDs, or codes outside the enums.
No commentary, no Markdown wrapper — the answer is bare JSON.
8. Transferable procedure (run this for every task)
Read case_scope.json + answer_template.json fully. Note item counts, enums, sort rules,
the cutoff date, and the certification gate/thresholds.
GET /api/catalog/schema; identify the family's view and its fields.
GET /api/source-snapshots?collection=<collection_id>; pick the authoritative snapshot;
record authoritative_snapshot_id.
SQL-dump the family view WHERE collection_id='<id>' (paginate). Fetch aliases
(?domain=), conversions (?kind=), fx as the family needs.
Collapse snapshots → logical records by business key; compute duplicate_raw_count.
