schema-mapping

# Skill: Semantic Schema Mapping Planning Follow this structured process and procedures for analyzing source-to-target data relationships and creating a high-fidelity **Mapping Plan** (also known as a **Mapping Manifesto**). This process is platform-agnostic and should be used before generating any target-specific pipeline code. When this skill is loaded, you MUST use this plan and replace the existing generic plan. ## When to Use Use this skill when: - You need to map schemas between a source dataset/database and a target destination database/warehouse. - You are initiating an ETL, ELT, or data integration task. - You need to identify schema gaps, data type conflicts, or aggregation requirements. -------------------------------------------------------------------------------- ## Required Input Variables Before creating the plan, you must obtain or request: 1. **`SOURCE_SCHEMAS`**: Definitions (schemas, tables, fields, types) of the source data. 2. **`TARGET_SCHEMAS`**: Definitions of the desired target/destination schemas. 3. **`BUSINESS_CONTEXT`**: Domain details, business rules, or use case description. 4. **`TARGET_PLATFORM`**: The database or execution engine (e.g., BigQuery, Snowflake, Postgres, Spark, Beam). ## Optional Input Variables 1. **`KNOWLEDGE_GRAPH`**: If there's a knowledge graph / property graph available, always inspect the graph for node table definitions, edge table definitions, and foreign key bindings (`SOURCE` / `DESTINATION` key references), etc. -------------------------------------------------------------------------------- ## The Schema Mapping Planning Procedure > [!IMPORTANT] **Execution Strategy: Table-by-Table Iteration** > > You MUST execute this procedure **iteratively, one target table at a time**. > For each individual table in the `TARGET_SCHEMAS`, complete Steps 1 through 6 > sequentially before moving to the next table. Do not attempt to map or > summarize multiple tables in a single batch, as this leads to hallucinations, > overlooked constraints, and context window bloat. ### Step 1: Semantic & Terminology Translation Analyze the entity names and attributes in the `SOURCE_SCHEMAS` against the `TARGET_SCHEMAS`. 1. **Synonym Resolution**: Using the `BUSINESS_CONTEXT`, map matching concepts with different names (e.g., `client_id` vs `customer_num`). 2. **Identify Domain Standards**: Match field values or formats to known standards (e.g., ISO country codes, currency codes, UN/LOCODE, UUIDs) based on the business context. 3. **Verify Domain Semantics**: Do not rely purely on lexical matching (name similarity). Verify the functional business purpose of the entities in both schemas. Ensure that a target table representing a specific business resource maps to a source table modeling that same resource rather than an unrelated administrative log or generic list table sharing a similar name. ### Step 2: Establish the Anchor Table For each table or collection in the `TARGET_SCHEMAS`: 1. Identify the **primary source table** (the "Anchor Table") that holds the core records for this target. 2. Identify **contributing/lookup tables** in the source that will enrich the target records. 3. **Prefer Structured Tables over Generic Key-Value Tables**: If the same attribute exists in both a structured column in a domain table and as a generic property in an Entity-Attribute-Value (EAV) key-value/properties table, always anchor on the structured table to ensure schema stability and performant joins. ### Step 3: Proactive Data Sampling & Inspection If a target field mapping is ambiguous or schema types do not tell the whole story (e.g., verifying if a timestamp is ISO-8601, if a string is a JSON array, or checking the distribution of values): 1. **Proactive Inspection**: If environment access allows, run platform-specific queries (e.g., `SELECT ... LIMIT 10`, `SELECT COUNT(DISTINCT ... )`) to sample values. 2. **User Inquiry**: If direct access is not possible, output sample queries and ask the user to provide the output to confirm assumptions before finalizing the plan. ### Step 4: Perform Field-Level Gap Analysis & Cleanliness Design Evaluate every column in each target table to determine its source mapping. Categorize mappings and plan cleanliness transformations: * **Direct Mapping**: A 1-to-1 match. * **Derived Mapping**: Requires type casting, string manipulation, date formatting, mathematical derivation, or case statement logic. * **Joined Mapping**: Requires looking up values from contributing tables using defined join keys. * **Aggregated Mapping**: Requires collapsing 1-to-many relationships (e.g., calculating `SUM`, `COUNT`, `ARRAY_AGG` or string concatenation). * **Gaps (Unmapped fields)**: Target fields that do not exist in the source. * **Constraint Checking**: Verify whether the target column has a `NOT NULL` or `REQUIRED` constraint in the target schema. * **Handling Nullable Gaps**: If the target column is nullable, explicitly flag it as `NULL` or define a default value. * **Handling Non-Nullable Gaps**: If the target column is `NOT NULL`, you MUST NOT map it to `NULL`. *(Rationale: Mapping NOT NULL target columns to NULL will cause execution-time database constraint violations and pipeline failures).* You must identify a source field to derive it from, default it to a valid non-null placeholder (e.g., `'UNKNOWN'`, `0`, or default dates), or define logic to generate a valid unique reference. #### Universal Data Cleanliness Rules to Incorporate in Mappings: 1. **Null Standardization**: Map source strings like `"NULL"`, `"None"`, `"N/A"`, or empty spaces to true database `NULL` values. 2. **Trim & Casing**: Plan to trim leading/trailing whitespaces. Convert standardized codes (e.g., ISO codes, status strings) to uppercase. 3. **Temporal Consistency**: Plan to parse all source timestamps into standard ISO-8601 format (`YYYY-MM-DDTHH:MM:SSZ`) or standard destination `TIMESTAMP` format. Plan checks to ensure logical temporal progression (e.g., `start_time <= end_time`). 4. **Defensive Checks**: Plan checks for strict destination types (e.g., checking if string is a valid number before casting to `DECIMAL`). ### Step 5: Map Relationships & Joins Specify the logical join path to connect the Anchor Table with all contributing source tables: 1. Define the join condition/keys (e.g., `source_order.customer_id = source_customer.id`). 2. Identify join scale properties: - **Large-to-Large**: Joining two high-volume transaction tables. - **Large-to-Small**: Joining a transaction table to a static lookup table (ideal for Map-side/Broadcast joins to optimize speed/cost). 3. Document potential join challenges: - Many-to-many risks or potential duplicate generation. - Type mismatches on join keys (e.g., joining an `INT` column to a `STRING` column). 4. **Graph Validation**: If a graph was identified in input, cross-reference the proposed join conditions with the graph's edge table definitions to validate foreign key relationships. ### Step 6: Draft the "Mapping Manifesto" (Output Format & Example) Analyze the schemas and reference the `ONE_SHOT_EXAMPLE` below to structure your output. #### ONE_SHOT_EXAMPLE (How to Structure the Mapping Manifesto) ##### Example Source Schemas ```markdown dataset: my_music_library style: {id: int, style: string} band: {name: string, biography: string, style: int} cd: {name: string, year: int, artist: string, numbers: array<string>} track: {id: string, number: int, name: string} ``` ##### Example Target Schemas ```markdown dataset: music_standard artist: {name: string, albums: array<string>} album: {name: string, year: int, genre: string, tracks: array<string>} ``` ##### The Mapping Manifesto (Expected Output Format) For each target table, document your column-to-column reasoning: ```markdown # Mapping Plan: `album` * **Anchor Source Table**: `cd` * **Join Paths & Optimization**: * `cd` JOIN `band` ON `cd.artist = band.name` * `band` JOIN `style` ON `band.style = style.id` * `cd` JOIN `track` ON `track.id IN UNNEST(cd.numbers)` ### Field Mappings | Target Field | Source Field / Logic | Mapping Type | Rationale / Transformation Details | | :--- | :--- | :--- | :--- | | `album.name` | `cd.name` | Direct | A CD is a physical medium representing an album; direct semantic match | | `album.year` | `cd.year` | Direct | Direct semantic match for release year | | `album.genre` | `style.style` | Joined | Resolved via `cd.artist` -> `band.name` -> `band.style` (ID) -> `style.id` -> `style.style` (String) | | `album.tracks` | `ARRAY_AGG(track.name)` | Aggregated | Aggregation Point: Collapses 1-to-many track IDs in `cd.numbers` into an array of track names | ``` -------------------------------------------------------------------------------- ## Critical Operational Rules * **Plan Before Execution**: You MUST NOT generate any ETL code, target-specific pipeline configurations, or migration scripts until the Mapping Manifesto has been presented to and approved by the user. *(Rationale: Establishing clear mapping logic first prevents coding errors, avoids circular dependencies, and ensures user alignment on semantic mappings before wasting resources on implementation).* * **Strict Schema Grounding**: Every source table and field name referenced in the mapping plan MUST exactly match the names and data types present in the provided `SOURCE_SCHEMAS`. You MUST NOT reference non-existent columns, guess field names, or make assumptions about source schemas without explicitly confirming them in the schema definitions. *(Rationale: Proposing guesses leads to compilation errors and invalid mapping specifications).* * **Target Schema Constraint Integrity**: You must never propose a mapping that writes `NULL` to a column defined as `NOT NULL` or `REQUIRED` in the `TARGET_SCHEMAS`. * **Exhaustive Field Search**: Before declaring a target field as an "Unmapped / Gap", search all available source schemas to verify the data is not in a less-obvious table. *(Rationale: Lazy mappings that default target fields to NULL lead to downstream data loss and incomplete pipelines).* * **Defensive Type Mapping**: Explicitly plan the transformation rules for strict destination types (e.g. `TIMESTAMP`, `BOOLEAN`, `DECIMAL`) to avoid load failures. *(Rationale: Different databases and platforms handle type validation strictly; pre-planning casts prevents execution-time runtime errors).* * **Data Integrity Check**: Identify join conditions that could cause Cartesian product expansion or data duplication, and note prevention strategies in the plan. *(Rationale: Unvalidated joins can distort aggregated metrics or exhaust processing memory on large datasets).*