finance-data-qc

Financial Investment Research Data Quality Inspection

Serves analysts and researchers at brokerages and funds, and the people who supply data to them. Default assumptions:There is no quality monitoring upstream, field comments are untrustworthy, and the test database is inconsistent with the production database. Your task is not to trust the data, but to use the fewest queries to find the places that would make research conclusions wrong.

The task proceeds intwo phases:

• Phase One · Exploration and Exploratory Detection: Figure out what ways each table can go wrong (complete coverage, non-overlapping). Corresponds to the "Discover and Grade" sub-process: boundaries → engine → exploration → scenarios/cross-cutting → filter defects → execute counter-evidence → grading.
• Phase Two · Screening, Consolidation, and Strategy Design: Take the findings from Phase One andscreenthem into two categories—those worth consolidating into standing monitoring, and those that go only into the report—and give the consolidated checks a run strategy. Corresponds to the "Consolidation and Design" sub-process: pass the screening gate → set consolidation baselines → define run strategy → deliver.
• Conditional trigger: Phase Twois not necessarily executed—it is entered only when Phase One has findings of "high recurrence × high silence cost × affordable cost"; if there are no findings worth consolidating, deliver the report directly and skip Phase Two.

Quality control stops at "discovery"; only a few worthwhile findings enter "continuous monitoring".

What this skill delivers

The user will specify a batch of tables and ask you to produce two things:

1. Data Quality Review Report（report.md + evidence.json): observations of each defect, evidence level, PASS/BLOCK/WATCH verdict, impact scope, and evidence still to be supplemented.
2. Monitoring Scripts / SQL（checks/): consolidate the defects found this time into scheduled re-runnable checks, and hand them to the user to implement as standing monitoring (corresponding to defect category 10, "missing monitoring").

The review report is a one-off conclusion; the monitoring scripts are reusable assets. Both must be genuinely re-runnable and must not be natural-language descriptions.

How to use this skill (routing overview)

SKILL.md only gives the process skeleton and dispatch rules. The specific methods are under references/ ,loaded on demand according to the trigger conditions below, not all at once。

Phase One: Exploration and Exploratory Detection(MECE: complete coverage, non-overlapping). The sub-process must be followed, taking a reference step by step:

Sub-step	Action	When to load which reference
Boundaries	Confirm the table list, purpose, market, entities, time range, and delivery requirements	No reference needed
Engine	Identify the storage engine of each table, and establish read-only, pagination, and cost estimation methods	Required reading references/engine-adapters.md
Exploration	Read metadata, estimate cost, plan layered sampling	Required reading references/exploration.md and references/sampling-statistics.md
Scenario/Cross-cutting	Determine which investment research scenario the table belongs to, and whether cross-table/timeliness/incident library is triggered	Read the scenario reference by scenario; read the cross-cutting reference by trigger condition
Screen defects	Judge applicability for each of the ten defect categories one by one;each observation belongs to exactly one check_id	Required reading references/defect-catalog.md
Execute counter-evidence	Run checks, find counter-evidence, adjudicate by tier	Go back to defect-catalog / scenario reference to get counter-evidence paths

Phase 2: Screening, consolidation, and strategy design(Condition-triggered: entered only when Phase 1 has findings of "high recurrence × high silent cost × affordable cost"). Sub-processes as needed:

Sub-step	Action	Which reference to load and when
Pass screening gate	For each Phase 1 finding ask three questions: recurrence risk × silent cost × detection cost	Required reading references/deliverables.md screening basis section
Set consolidation baseline	Can be cross-checked against contract.semantics.formula recalculation verification and high-recurrence ones are prioritized for consolidation; those without recalculation basis are marked "contract needs to be supplemented first"	Return to finance-ddl-design contract; if none, references/freshness.md take from policy
Set run strategy	Set schedule (follow freshness.md four types of policy), threshold, alert_on, depends_on	Required reading references/freshness.md
Deliver	Produce report + checks/ that passed the screening gate + run strategy	references/deliverables.md

When there are no findings worth consolidatingSkip Phase 2and deliver the report directly.

Scenario reference (Phase 1 scenario/cross-cutting sub-step, multiple selections allowed, but one primary scenario must be declared)

Table content	Which one to read
Market quotes, trades, trading calendar, securities master data, indices, fund NAV, forex, corporate actions, adjustment	references/market-reference.md
Financial statements and metrics, earnings forecasts and flash reports, valuation metrics, earnings estimates, periodic fund holdings disclosure	references/fundamental-valuation.md
Exchange announcements, prospectus and periodic report texts, research reports, structured events extracted from text	references/disclosure-event.md
Macroeconomic indicators, industry and value chain, industry classification, theme/concept to instrument mapping	references/macro-industry.md

When one table spans multiple scenarios (such as "financial report announcement table" or "index industry classification table"), read all relevant scenario references, but designate one of them as primary responsibility and the others as supplementary judgment premises.

Cross-cutting reference (Phase 1 scenario/cross-cutting sub-step, loaded by trigger condition)

Trigger condition	Which one to read
The user specified more than two tables, or join, synonym field comparison, or test database versus production database comparison is needed	references/cross-table-consistency.md
Involves missingness, continuity, latest data, update delay, due but not arrived, SLA, point-in-time availability (PIT)	references/freshness.md
Facing an unfamiliar database and not knowing where to start, or wanting to check common pitfalls	references/real-world-cases.md(Use as a checklist; no need to read through when clear defects already exist)

Simple acceptance of a single table with no timeliness requirement may skip loading the cross-cutting reference; do not read it just to be complete.

Ten defect categories: judge applicability category by category

Go through these ten categories for each table, butRun only checks that are "applicable and have a judgment premise". Mark each category:执行 / 不适用 / 前提不足暂挂. Do not mechanically run all ten categories on every table. For detection methods, counter-evidence paths, and "when no conclusion can be drawn", see references/defect-catalog.md。

Mutually exclusive attribution discipline (the ME half of MECE): each observation is attributed to only one check_id, and double-reporting is not allowed. In double-reporting situations (such as a unit mismatch that looks like both "naming and units" and "numeric error"), attribute it singly by primary responsibility, and in the other category note "captured by category X"; do not list the item again. Reason: ensure the report has no duplicate entries and each conclusion can be traced to a single check.

Defect family	One sentence	What to look at first
Duplicate primary key	The business primary key has multiple rows, one of which has incomplete information	Declared primary key count vs distinct, field differences within duplicate groups
Missing	The source table has values but downstream fields are empty, or an entire category of scope was never built	Null rate by entity, by date, by field, compared with upstream
Continuity	Sequence gaps; it looks like there is "data" but there are only a few records per month on average	Record count distribution by entity × calendar, minimum/maximum interval
Numeric error	Absurd magnitude, wrong unit, wrong sign, cross-field contradiction	Extreme values, magnitude quantiles, cross-field recomputable relationships
Timeliness	Expected but not arrived, or arrived later than promised	Last update time, arrival delay distribution, batch cadence
Naming and units	Synonymous fields have different names, different units, or different time zones across tables	List of synonymous fields, magnitude comparison, time zone sampling
Comment semantics	A comment can be read in two ways, or does not match the actual computation	Ambiguous wording in comments; use data to infer the true semantics
Dimension contamination	Classification fields are mixed with labels from other systems, and any statistic doubles	Enum value domains, group sums reconciled against totals
Interface layer	Pass 20 parameters but fewer are returned, expired instruments are retrieved, query times out	Reconcile request count and return count, boundaries and pagination, slow queries
Missing monitoring	The above defects have no standing validation and are found manually	Whether there is implemented continuous validation and alerting

Grading recognizes only three kinds of evidence

• Observed (OBSERVED): what queries, files, and metadata actually return.
• Inferred (INFERRED): assumptions about field meanings, causes of anomalies, and whether rules apply.
• Confirmed (CONFIRMED): a versioned specification or data contract, explicit user confirmation of that scope, successful recomputation using an independent data source or business relationship.

Grading rules:

• Only CONFIRMED semantics can give PASS or BLOCK, and it must be traceable to a rerunnable check and the same data scope.
• Conclusions drawn only from field names, comments, or value distributions can only be given WATCH, and must not be directly judged BLOCK。
• If a check was not run, could not be completed, or the budget was insufficient, state the reason for stopping.Do not write "no problems found" as PASS.

Before starting, settle four things first

1. Read-only. Use only read-only accounts and read-only statements. DDL/DML are prohibited, and unbounded SELECT *。
2. Scope. Record the table list, market, instrument type, entity set, time range, frequency specified by the user, and what this batch of data will be used for (stock selection, backtesting, valuation, report data retrieval).
3. Query budget. Declare the row count, scan volume, execution time, or API call limit before issuing the first query. For each round of queries, state the purpose, projected columns, filter conditions, and limit.
4. If unknown, write unknown. List unclear conventions in the unknown list; do not guess based on field names, and do not apply thresholds from other markets.

Execution flow

Execution follows a two-stage sub-flow: Stage 1 is mandatory (discover and grade); Stage 2 isconditionally triggered(entered only when there are findings worth consolidating; otherwise deliver the report directly).

Stage 1: Exploration and tentative detection (discover and grade)

1. Confirm boundaries. Register each table's purpose, market, instrument type, entity scope, time range, and the deliverables the user wants (report, monitoring script, or both).
2. Identify the engine. First determine each table's storage engine (relational database, data warehouse, ES, Mongo, time-series database, vector database, file, API), and based on that determine the read-only method, pagination, sampling, and cost estimation. See references/engine-adapters.md. This step must be completed before the first business query.
3. Explore the structure and estimate the cost. Read metadata: field types, partitions, indexes, declared constraints, row count order of magnitude; use execution plans or the engine's equivalent capability to confirm it will not scan the full table. See references/exploration.md。
4. Funnel-style sampling statistics. Proceed as "metadata → small-scope reconnaissance → stratified statistics → anomaly drill-down → justified expansion".LIMIT does not mean restricting scanning; do not start by running full-history COUNT DISTINCT, high-cardinality GROUP BY , or large-table joins. See references/sampling-statistics.md。
5. Select references (scenario/cross-cutting). Load the corresponding scenario references based on the table's content, and load cross-cutting references based on trigger conditions (cross-table, timeliness, incident library); take the judgment premises, do not copy thresholds.
6. Screen for defects. Judge applicability for each of the ten categories, retaining only checks that are applicable and have premises; each observation belongs to exactly one check_id. See references/defect-catalog.md。
7. Execute counter-evidence and grading. Prefer closed-loop relational recomputation (price × quantity, group sum against total, minute-level recomputation of daily frequency, upstream raw tables against downstream wide tables) rather than continuing to pile up distribution statistics; assign an evidence level and PASS/BLOCK/WATCH to each item, and clearly state the impact scope and evidence still needed.

Stage 1 outputReview report and grading of all findings(one-time conclusion).

Stage 2: Screening consolidation and strategy design (conditionally triggered)

Entry condition: Stage 1 contains findings with "high recurrence × high silence cost × affordable cost"; if not, skip this stage and deliver the report directly.

1. Pass the screening gate. For each finding ask three questions: recurrence risk? cost of silent failure? detection cost? One-off incidents go into the report only and are not hardened; periodic recurrence × high silence × affordable is hardened first; optimizations where the cost is disproportionate (reduce frequency/downsample) or abandoned.
2. Set the hardening baseline. Checks that can be cross-checked against contract.semantics.formula and recomputed/verified and that recur frequently are hardened first as checks/; those without a basis for recomputation are marked \"contract must be supplied first\" and written into the design notes, and are not silently hardened.
3. Define the run strategy. For each hardened check define the schedule (following the four regimes of references/freshness.md ), threshold, alert_on, depends_on.
4. Deliver. Produce the checks/ that passed the screening gate and the run strategy, and save them versioned by run along with the report; historical runs must not be overwritten. See the next section and references/deliverables.md。

For positive and negative examples see examples/few-shots.md。

Deliverable requirements

• Review report report.md: data scope and snapshot, query budget and actual consumption, item-by-item check_id observations/evidence level/verdict,INFERRED the basis for assumptions and conditions for refutation, timeliness and coverage observations, and differences from the last run; each observation belongs to exactly one check_id。
• Machine-readable evidence evidence.json: for each check, the rerunnable file reference, parameters, observation results, evidence level, verdict.
• Monitoring scripts / SQL checks/：Contains only checks that passed the screening gate(stage-two output) — high recurrence × high silent cost × affordable cost; those that can be cross-checked against contract.semantics.formula and recomputed/verified are preferred. Each is hardened into a format natively executable by that engine (for SQL use .sql, for ES/Mongo/time-series/vector use their native requests, for Python use .py), with a stable check_id, parameterized data scope, criteria and alert threshold recommendations, for the user to plug directly into scheduling. Do not disguise non-SQL checks as .sql。
• Run strategy(along with checks/): for each hardened check's schedule(following the four update regimes of freshness.md , write unconfirmed as null）、threshold、alert_on(alert routing/level/consecutive failure count),depends_on(data readiness dependencies).
• Monitoring scripts must be able to run independently of this session: parameterize the time window and entity scope, do not hard-code one-off snapshots. One-off incidents, low-frequency affordable findings, and findings with disproportionate costgo into the report only, and are not hardened。

Situations that must stop

• The connection information contains plaintext passwords, private keys, or tokens that would be written into the report.
• Only an account with write permissions can be obtained, or the task requires executing DDL/DML.
• The query cannot limit projection, entity, time, partition, or result cap, and may form an unbounded scan.
• The used or estimated scan volume exceeds the approved query budget.
• The sample would expose unmasked account, position, transaction, or customer information.
• The data is compressed, encrypted, proprietary binary, or streaming, with no safe read-only parsing method.
• The task is essentially asking for final conclusions on DDL design, pipeline orchestration, query interface design, governance, or compliance: first save the existing observations, then return to using-data-dev and select the corresponding responsibility.