icedq-mapping-doc-rules

GOAL

Extract ALL quality rules from a column mapping spec. ONE Validation rule per table (all checks combined). Rule types: Validation, Duplicate, Checksum, Recon, Pushdown.

---

STEP 0 — PARSE DOC

Format detection:

• Excel/CSV/Word table: find header row; map labels to signal fields below. Ignore decorative title rows.
• dbt schema.yml: not_null→Mandatory; unique→UniqueKey; accepted_values→ApprovedValues; relationships→FK Pushdown
• JSON Schema: required→Mandatory; enum→ApprovedValues; maxLength→MaxLen; pattern→Format; $ref→FK Pushdown
• Prose: scan for "Validations:", "Business Rules:", "Constraints:", "Data Requirements:" headings

Signal field aliases:

Signal	Common column names
Source col	Source Column, SRC Column, From Column, Source Field
Target col	Target Column, TGT Column, DW Column, Destination
Mandatory	Mandatory?, Required?, Nullable?, Not Null
Unique key	Unique Key?, PK?, Business Key?, Natural Key?
Transform	Transformation, Logic, Mapping Rule, ETL Logic
Approved values	Approved Values, Domain, Valid Values, Lookup
Format	Format, Pattern, Regex, Data Format
Min/Max Length	Min Length, Max Length, Length
FK	Cross-Table, FK Reference, References, Lookup Table
Business rule	Business Rule, Acceptance Criteria, DQ Rule, Notes
Severity	Severity, Priority, Criticality, Risk Level

YES synonyms: Y YES TRUE 1 Required NOT NULL Mandatory. NO synonyms: N NO FALSE 0 Optional NULL Nullable blank.

Missing columns — infer:

• No Mandatory col → check target DDL for NOT NULL; scan Business Rule text for "must not be null"
• No Unique Key → scan Business Rule for "must be unique"/"primary key"/"no duplicate"
• No Approved Values → extract from Business Rule or Transform text
• No Min/Max Length → infer from type: VARCHAR(N)→max=N; CHAR(N)→exact=N
• No Format → extract regex/format strings from Business Rule text

Table metadata (above column rows): source schema.table + connection; target schema.table + connection; join key. No join key → ask user. If none: Validation + Duplicate + Checksum only (skip Recon).