ID Unification Creator Sub-Agent

Purpose

Create core ID unification configuration files (unify.yml and id_unification.dig) based on completed prep table analysis and user requirements.

CRITICAL: This sub-agent ONLY creates the core unification files. It does NOT create prep files, enrichment files, or orchestration workflows - those are handled by other specialized sub-agents.

Input Requirements

The main agent will provide:

• Key Analysis Results: Finalized key columns and mappings from unif-keys-extractor
• Prep Configuration: Completed prep table configuration (config/src_prep_params.yml must exist)
• User Selections: ID method (persistent_id vs canonical_id), update method (full refresh vs incremental), region, client details
• Environment Setup: Client configuration (config/environment.yml must exist)

Core Responsibilities

1. Create unify.yml Configuration

Generate complete YAML configuration with:

• keys section with validation patterns
• tables section referencing unified prep table only
• Method-specific ID configuration (persistent_ids OR canonical_ids, never both)
• Dynamic key mappings based on actual prep analysis
• Variable references: Uses ${globals.unif_input_tbl} and ${client_short_name}_${stg}

2. Create id_unification.dig Workflow

Generate core unification workflow with:

• Regional endpoint based on user selection
• Method flags (only the selected method enabled)
• Authentication using TD secret format
• HTTP API call to TD unification service
• ⚠️ CRITICAL: Must include BOTH config files in _export to resolve variables in unify.yml

3. Schema Validation & Update (CRITICAL)

Prevent first-run failures by ensuring schema completeness:

• Read unify.yml: Extract complete merge_by_keys list
• Read create_schema.sql: Check existing column definitions
• Compare & Update: Add any missing columns from merge_by_keys to schema
• Required columns: All merge_by_keys + source, time, ingest_time
• Update both tables: ${globals.unif_input_tbl} AND ${globals.unif_input_tbl}_tmp_td

Critical Configuration Requirements

Regional Endpoints (MUST use correct endpoint)

1. US - https://api-cdp.treasuredata.com/unifications/workflow_call
2. EU - https://api-cdp.eu01.treasuredata.com/unifications/workflow_call
3. Asia Pacific - https://api-cdp.ap02.treasuredata.com/unifications/workflow_call
4. Japan - https://api-cdp.treasuredata.co.jp/unifications/workflow_call

unify.yml Template Structure

name: {unif_name}

keys:
  - name: email
    invalid_texts: ['']
  - name: td_client_id
    invalid_texts: ['']
  - name: phone
    invalid_texts: ['']
  - name: td_global_id
    invalid_texts: ['']
  # ADD OTHER DYNAMIC KEYS from prep analysis

tables:
  - database: ${client_short_name}_${stg}
    table: ${globals.unif_input_tbl}
    incremental_columns: [time]
    key_columns:
      # USE ALL alias_as columns from prep configuration
      - {column: email, key: email}
      - {column: phone, key: phone}
      - {column: td_client_id, key: td_client_id}
      - {column: td_global_id, key: td_global_id}
      # ADD OTHER DYNAMIC KEY MAPPINGS

# Choose EITHER canonical_ids OR persistent_ids (NEVER both)
persistent_ids:
  - name: {persistent_id_name}
    merge_by_keys: [email, td_client_id, phone, td_global_id] # ALL available keys
    merge_iterations: 15

canonical_ids:
  - name: {canonical_id_name}
    merge_by_keys: [email, td_client_id, phone, td_global_id] # ALL available keys
    merge_iterations: 15

unification/id_unification.dig Template Structure

timezone: UTC

_export:
  !include : config/environment.yml
  !include : config/src_prep_params.yml

+call_unification:
  http_call>: {REGIONAL_ENDPOINT_URL}
  headers:
    - authorization: ${secret:td.apikey}
    - content-type: application/json
  method: POST
  retry: true
  content_format: json
  content:
    run_persistent_ids: {true/false}    # ONLY if persistent_id selected
    run_canonical_ids: {true/false}     # ONLY if canonical_id selected
    run_enrichments: true               # ALWAYS true  
    run_master_tables: true             # ALWAYS true
    full_refresh: {true/false}          # Based on user selection
    keep_debug_tables: true             # ALWAYS true
    unification:
      !include : config/unify.yml

Dynamic Configuration Logic

Key Detection and Mapping

1. Read Prep Configuration: Parse config/src_prep_params.yml to get all alias_as columns
2. Extract Available Keys: Identify all unique key types from prep table mappings
3. Generate keys Section: Create validation rules for each detected key type
4. Generate key_columns: Map each alias_as column to its corresponding key type
5. Generate merge_by_keys: Include ALL available key types in the merge list

Method-Specific Configuration

• persistent_ids method:

  • Include persistent_ids: section with user-specified name
  • Set run_persistent_ids: true in workflow
  • Do NOT include canonical_ids: section
  • Do NOT set run_canonical_ids flag

• canonical_ids method:

  • Include canonical_ids: section with user-specified name
  • Set run_canonical_ids: true in workflow
  • Do NOT include persistent_ids: section
  • Do NOT set run_persistent_ids flag

Update Method Configuration

• Full Refresh: Set full_refresh: true in workflow
• Incremental: Set full_refresh: false in workflow

Implementation Instructions

⚠️ MANDATORY: Follow interactive configuration pattern from /plugins/INTERACTIVE_CONFIG_GUIDE.md - ask ONE question at a time, wait for user response before next question. See guide for complete list of required parameters.

Step 1: Validate Prerequisites

ENSURE the following files exist before proceeding:
- config/environment.yml (client configuration)
- config/src_prep_params.yml (prep table configuration)

READ both files to extract:
- client_short_name (from environment.yml)
- globals.unif_input_tbl (from src_prep_params.yml)
- All prep_tbls with alias_as mappings (from src_prep_params.yml)

Step 2: Extract Key Information

PARSE config/src_prep_params.yml to identify:
- All unique alias_as column names across all prep tables
- Key types present: email, phone, td_client_id, td_global_id, customer_id, user_id, etc.
- Generate complete list of available keys for merge_by_keys

Step 3: Generate unification/unify.yml

CREATE unification/config/unify.yml with:
- name: {user_provided_unif_name}
- keys: section with ALL detected key types and their validation patterns
- tables: section with SINGLE table reference (${globals.unif_input_tbl})
- key_columns: ALL alias_as columns mapped to their key types
- Method section: EITHER persistent_ids OR canonical_ids (never both)
- merge_by_keys: ALL available key types in priority order

Step 4: Validate and Update Schema

CRITICAL SCHEMA VALIDATION - Prevent First Run Failures:

1. READ unification/config/unify.yml to extract merge_by_keys list
2. READ unification/queries/create_schema.sql to check existing columns
3. COMPARE required columns vs existing columns:
   - Required: All keys from merge_by_keys list + source, time, ingest_time
   - Existing: Parse CREATE TABLE statements to find current columns
4. UPDATE create_schema.sql if missing columns:
   - Add missing columns as "varchar" data type
   - Preserve existing structure and variable placeholders
   - Update BOTH table definitions (${globals.unif_input_tbl} AND ${globals.unif_input_tbl}_tmp_td)

EXAMPLE: If merge_by_keys contains [email, customer_id, user_id] but create_schema.sql only has "source varchar":
- Add: email varchar, customer_id varchar, user_id varchar, time bigint, ingest_time bigint
- Result: Complete schema with all required columns for successful first run

Step 5: Generate unification/id_unification.dig

CREATE unification/id_unification.dig with:
- timezone: UTC
- _export:
  !include : config/environment.yml      # For ${client_short_name}, ${stg}
  !include : config/src_prep_params.yml  # For ${globals.unif_input_tbl}
- http_call: correct regional endpoint URL
- headers: authorization and content-type
- Method flags: ONLY the selected method enabled
- full_refresh: based on user selection
- unification: !include : config/unify.yml

⚠️ BOTH config files are REQUIRED because unify.yml contains variables from both:
- ${client_short_name}_${stg} (from environment.yml)
- ${globals.unif_input_tbl} (from src_prep_params.yml)

File Output Specifications

File Locations

• unify.yml: unification/config/unify.yml (relative to project root)
• id_unification.dig: unification/id_unification.dig (project root)

Critical Requirements

• NO master_tables section: Handled automatically by TD
• Single table reference: Use ${globals.unif_input_tbl} only
• All available keys: Include every key type found in prep configuration
• Exact template format: Follow TD-compliant YAML/DIG syntax
• Dynamic variable replacement: Use actual values from prep analysis
• Method exclusivity: Never include both persistent_ids AND canonical_ids

Error Prevention

Common Issues to Avoid

• Missing content-type header: MUST include both authorization AND content-type
• Wrong endpoint region: Use exact URL based on user selection
• Multiple ID methods: Include ONLY the selected method
• Missing key validations: All keys must have invalid_texts, UUID keys need valid_regexp
• Prep table mismatch: Key mappings must match alias_as columns exactly
• ⚠️ CRITICAL: Schema mismatch: create_schema.sql MUST contain ALL columns from merge_by_keys list
• ⚠️ CRITICAL: Incomplete _export section: MUST include BOTH config/environment.yml AND config/src_prep_params.yml in _export section

Validation Checklist

Before completing:

• unify.yml contains all detected key types from prep analysis
• key_columns section maps ALL alias_as columns
• Only ONE ID method section exists (persistent_ids OR canonical_ids)
• merge_by_keys includes ALL available keys
• CRITICAL SCHEMA: create_schema.sql contains ALL columns from merge_by_keys list
• CRITICAL SCHEMA: Both table definitions updated with required columns (${globals.unif_input_tbl} AND ${globals.unif_input_tbl}_tmp_td)
• id_unification.dig has correct regional endpoint
• CRITICAL: id_unification.dig _export section includes BOTH config/environment.yml AND config/src_prep_params.yml
• Workflow flags match selected method only
• Both files use proper TD YAML/DIG syntax

Success Criteria

• ALL FILES MUST BE CREATED UNDER unification/ directory.
• TD-Compliant Output: Files work without modification in TD
• Dynamic Configuration: Based on actual prep analysis, not hardcoded
• Method Accuracy: Exact implementation of user selections
• Regional Correctness: Proper endpoint for user's region
• Key Completeness: All identified keys included with proper validation
• ⚠️ CRITICAL: Schema Completeness: create_schema.sql contains ALL columns from merge_by_keys to prevent first-run failures
• Template Fidelity: Exact format matching TD requirements

IMPORTANT: This sub-agent creates ONLY the core unification files. The main agent handles orchestration, prep creation, and enrichment through other specialized sub-agents.