Auto-generate unify.yml configuration for Snowflake/Databricks by extracting user identifiers from actual tables using strict PII detection
Generates production-ready unify.yml configuration for Snowflake/Databricks by analyzing actual tables and extracting user identifiers with strict PII detection.
/plugin marketplace add treasure-data/aps_claude_tools/plugin install treasure-data-cdp-hybrid-idu-plugins-cdp-hybrid-idu@treasure-data/aps_claude_toolsI'll automatically generate a production-ready unify.yml configuration file for your Snowflake or Databricks ID unification by:
This command uses STRICT analysis - only tables with actual user identifiers will be included.
Provide tables you want to analyze for ID unification:
database.schema.table or schema.table or tablecatalog.schema.table or schema.table or tablecustomer_data.public.customers, orders, web_events.user_activitytd_id)unify.yml)1. Confirm platform (Snowflake or Databricks)
2. Verify MCP tools are available for the platform
3. Set up platform-specific query patterns
4. Inform you of the analysis approach
I'll launch the hybrid-unif-keys-extractor agent to:
Schema Analysis:
User Identifier Detection:
Data Validation:
Table Classification:
Expert Analysis:
CRITICAL: Using the EXACT BUILT-IN template structure (embedded in hybrid-unif-keys-extractor agent)
Template Usage Process:
1. Receive structured data from hybrid-unif-keys-extractor agent:
- Keys with validation rules
- Tables with column mappings
- Canonical ID configuration
- Master tables specification
2. Use BUILT-IN template structure (see agent documentation)
3. ONLY replace these specific values:
- Line 1: name: {canonical_id_name}
- keys section: actual keys found
- tables section: actual tables with actual columns
- canonical_ids section: name and merge_by_keys
- master_tables section: [] or user specifications
4. PRESERVE everything else:
- ALL comment blocks (#####...)
- ALL comment text ("Declare Validation logic", etc.)
- ALL spacing and indentation (2 spaces per level)
- ALL blank lines
- EXACT YAML structure
5. Use Write tool to save populated unify.yml
I'll generate:
Section 1: Canonical ID Name
name: {your_canonical_id_name}
Section 2: Keys with Validation
keys:
- name: email
valid_regexp: ".*@.*"
invalid_texts: ['', 'N/A', 'null']
- name: customer_id
invalid_texts: ['', 'N/A', 'null']
- name: phone_number
invalid_texts: ['', 'N/A', 'null']
Populated with actual keys found in your tables
Section 3: Tables with Key Column Mappings
tables:
- database: {database/catalog}
table: {table_name}
key_columns:
- {column: actual_column_name, key: mapped_key}
- {column: another_column, key: another_key}
Only tables with valid user identifiers, with EXACT column names from schema analysis
Section 4: Canonical IDs Configuration
canonical_ids:
- name: {your_canonical_id_name}
merge_by_keys: [email, customer_id, phone_number]
merge_iterations: 15
Based on extracted keys and your configuration
Section 5: Master Tables (Optional)
master_tables:
- name: {canonical_id_name}_master_table
canonical_id: {canonical_id_name}
attributes:
- name: best_email
source_columns:
- {table: table1, column: email, order: last, order_by: time, priority: 1}
- {table: table2, column: email_address, order: last, order_by: time, priority: 2}
If you request master table configuration, I'll help set up attribute aggregation
After generation:
1. Show complete unify.yml content
2. Highlight key sections:
- Keys found: [list]
- Tables included: [count]
- Tables excluded: [count] with reasons
- Merge strategy: [keys and priorities]
3. Provide recommendations for optimization
4. Ask for your approval before saving
1. Write unify.yml to specified location
2. Create backup of existing file if present
3. Provide file summary:
- Keys configured: X
- Tables configured: Y
- Validation rules: Z
4. Show next steps for using the configuration
Input:
Platform: Snowflake
Tables:
- customer_data.public.customers
- customer_data.public.orders
- web_data.public.events
Canonical ID Name: unified_customer_id
Output: snowflake_unify.yml
Process:
✓ Platform: Snowflake MCP tools detected
✓ Analyzing 3 tables...
Schema Analysis:
✓ customer_data.public.customers - 12 columns
✓ customer_data.public.orders - 8 columns
✓ web_data.public.events - 15 columns
User Identifier Detection:
✓ customers: email, customer_id (2 identifiers)
✓ orders: customer_id, email_address (2 identifiers)
✗ events: NO user identifiers found
Available columns: event_id, session_id, page_url, timestamp, ...
Reason: Contains only event tracking data - no PII
Data Analysis:
✓ email: 45,123 unique values, format valid
✓ customer_id: 45,089 unique values, numeric
✓ email_address: 12,456 unique values, format valid
Expert Analysis Complete:
Priority 1: customer_id (most stable, highest coverage)
Priority 2: email (good coverage, some quality issues)
Priority 3: phone_number (not found)
Generating unify.yml...
✓ Keys section: 2 keys configured
✓ Tables section: 2 tables configured
✓ Canonical IDs: unified_customer_id
✓ Validation rules: Applied based on data patterns
Tables EXCLUDED:
- web_data.public.events: No user identifiers
Output (snowflake_unify.yml):
name: unified_customer_id
keys:
- name: email
valid_regexp: ".*@.*"
invalid_texts: ['', 'N/A', 'null']
- name: customer_id
invalid_texts: ['', 'N/A', 'null']
tables:
- database: customer_data
table: customers
key_columns:
- {column: email, key: email}
- {column: customer_id, key: customer_id}
- database: customer_data
table: orders
key_columns:
- {column: email_address, key: email}
- {column: customer_id, key: customer_id}
canonical_ids:
- name: unified_customer_id
merge_by_keys: [customer_id, email]
merge_iterations: 15
master_tables: []
The generated unify.yml will have EXACTLY this structure:
name: {canonical_id_name}
#####################################################
##
##Declare Validation logic for unification keys
##
#####################################################
keys:
- name: {key1}
valid_regexp: "{pattern}"
invalid_texts: ['{val1}', '{val2}', '{val3}']
- name: {key2}
invalid_texts: ['{val1}', '{val2}', '{val3}']
#####################################################
##
##Declare databases, tables, and keys to use during unification
##
#####################################################
tables:
- database: {db/catalog}
table: {table}
key_columns:
- {column: {col}, key: {key}}
#####################################################
##
##Declare hierarchy for unification. Define keys to use for each level.
##
#####################################################
canonical_ids:
- name: {canonical_id_name}
merge_by_keys: [{key1}, {key2}, ...]
merge_iterations: {number}
#####################################################
##
##Declare Similar Attributes and standardize into a single column
##
#####################################################
master_tables:
- name: {canonical_id_name}_master_table
canonical_id: {canonical_id_name}
attributes:
- name: {attribute}
source_columns:
- {table: {t}, column: {c}, order: last, order_by: time, priority: 1}
NO deviations from this structure - EXACT template compliance guaranteed.
| Step | Duration |
|---|---|
| Platform detection | < 1 min |
| Schema analysis (per table) | 5-10 sec |
| Data analysis (per identifier) | 10-20 sec |
| Expert analysis | 1-2 min |
| YAML generation | < 1 min |
| Total (for 5 tables) | ~3-5 min |
Issue: MCP tools not available for platform Solution:
Issue: No tables have user identifiers Solution:
Issue: Table not accessible Solution:
Issue: Complex data types found Solution:
Generated unify.yml will:
Review the generated unify.yml
Generate SQL for your platform:
/cdp-hybrid-idu:hybrid-generate-snowflake/cdp-hybrid-idu:hybrid-generate-databricksExecute the workflow:
/cdp-hybrid-idu:hybrid-execute-snowflake/cdp-hybrid-idu:hybrid-execute-databricksMonitor convergence and results
Ready to begin?
Please provide:
unified_customer_id)unify.yml)Example:
Platform: Snowflake
Tables:
- customer_db.public.customers
- customer_db.public.orders
- marketing_db.public.campaigns
Canonical ID: unified_id
Output: snowflake_unify.yml
I'll analyze your tables and generate a production-ready unify.yml configuration!