SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsdata-solid

data-solid

Data & analytics

View repository

Updated 10-15 22:11

About this skill

Data Warehouse Structure Review

Review whether the data structure is "well-formed": clear layering, single grain, unique primary keys, trustworthy temporality, no contract drift, no duplicated logic. The goal is not to have you tear it down and rebuild, but to locate structural lesions—fixing one place can save a whole bunch of queries.

What this skill delivers

The user gives a set of tables/data warehouse structure, and you produce:

  1. Structure review report structure-review.md: item-by-item observations by structural facet, evidence level, PASS/WATCH conclusions.
  2. Improvement suggestions: for each structural issue, give "what to change, how to change it, who is affected"; do not change code—suggestions are executable descriptions.

Review is read-only: use only metadata and structural information, do not run full data.

Relationship with general /solid /data warehouse standards

  • General /solid doesmodule dependency direction/testability；data-solid doesthe five facets of data warehouse structure(below).
  • Borrow solid's principles: single responsibility (one layer one boundary? no, data layers are interpreted as "single grain + no contract drift"), dependency direction (downstream does not reversely depend on vague definitions produced upstream), open-closed (adding new definitions does not break existing tables; use new tables/new columns rather than rewriting history).
  • Reuse suite evidence grading:OBSERVED(metadata/structural facts),INFERRED(semantic inference),CONFIRMED(contract confirmation); conclusions only PASS/WATCH(structural review does not easily BLOCK; changing tables is the executor's responsibility).

Five structural facets (review one at a time, in order)

# Structural facet What to review Lesion signals
1 Layering completeness Whether ODS/DWD/DWS/ADS form a chain, whether there is layer skipping/direct use of source-adjacent data Reports query ods directly, no dwd detail persistence
2 Grain and primary key Whether each table has a single grain, whether the primary key is unique and covers the partition One table mixes daily/minute grain; primary key lacks partition column causing duplicates
3 Temporality/PIT Whether event/availability/ingestion are separate columns, whether the available point in time can be reconstructed end_date merged with ann_date; rewriting history overwrites what was visible at the time
4 Contract consistency Whether fields have the same name and meaning as contract.yaml; units/enums/time zones consistent Synonymous fields have different names across tables; unit drift (yuan vs ten-thousand yuan)
5 Reuse and duplication Whether the same definition is centrally defined, whether calculations are copied repeatedly Gross margin/PE(TTM) formula copied in N places; change one and miss another

Strictly one facet at a time, in dependency order: layering → grain → temporality → contract → reuse. Give the structural facet conclusion first, then the action items.

Evidence discipline

  • Structural facts(field types, primary keys, partitions, comments) → OBSERVED。
  • Semantic inference("this is probably an adjustment factor") → INFERRED, criteria and counter-evidence conditions.
  • Contract confirmation(contract.yaml / specification-backed) → CONFIRMED。
  • Only CONFIRMED can determine PASS; naming/comments/single distribution clues can only WATCH, and are not easily BLOCK—changing tables is the executor's responsibility, data-solid only provides review points.

Review process

  1. Enumerate objects. The table/database list, purpose, market/entity scope provided by the user.
  2. Read structure. Metadata: table comments, columns, types, primary key/unique, partitions, contract (contract.yaml if present).
  3. Choose surface. From the five surfaces determine which to review first (in dependency order, without skipping).
  4. Provide evidence. Mark each observation as OBSERVED, semantic inferences as INFERRED + basis + counter-evidence.
  5. Determine conclusion. PASS/WATCH; unconfirmed does not BLOCK.
  6. Deliver. structure-review.md + improvement suggestion list (what to change/how to change/who is affected), do not run table creation on behalf.

Improvement suggestion template

issue_id: DWS-001
structure_surface: "契约一致性"
observed: "close_price 在 A 表带单位元、B 表是万元，命名相同"
evidence_level: OBSERVED
decision: WATCH
suggested: "统一到 contract.yaml 的 close_price 口径；单位差异改名 close_price_wan 或统一元"
impact: "下游估值/行情 join 会算错量级"
owner_executor: "finance-ddl-design（改表）"

Red Flags

  • Reviewing one structural surface but dragging in other surfaces (return to one surface at a time)
  • Directly BLOCK based on naming/comment clues (unconfirmed can only WATCH)
  • Running the executor to create/alter tables during the review phase
  • Only giving "code" without "what to change/who is affected"
  • Not marking evidence level

Checklist

  • Five surfaces in dependency order, one surface at a time
  • Structural facts OBSERVED, semantics INFERRED, contract CONFIRMED
  • Conclusion PASS/WATCH, no BLOCK without confirmation
  • Suggestions include what to change/how to change/who is affected
  • Review only, leave table changes to the executor