New:Socket for Asana Is Now Available.Learn more →
Get Started

@icedq/mcp-server

Package Overview
Dependencies
Maintainers
2
Versions
9
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

@icedq/mcp-server - npm Package Compare versions

Comparing version
1.0.4
 to
1.0.5
+26
guidance/async_monitoring.md
ASYNC TOOL MAPPING:
| Operation | Tool | Returns | Monitor With | ID Format |
|------------------|------------------|------------------|----------------------------|------------|
| Execute rule/workflow | execute_rules_or_workflows | successList[].instanceId | get_workflow_run_status_or_result | integer |
| Execute schedule | execute_schedule | success | get_scheduler_runs_history | scheduleId |
| Move rules/workflows | move_rules_or_workflows | taskInstanceId | check_task_status | tins-xxx |
EXECUTION MONITORING WORKFLOW:
1. execute_rules_or_workflows > get successList[].instanceId (integer per item)
2. Wait 2-3 seconds
3. get_workflow_run_status_or_result (action=status) with instanceId > get status (Success/Warning/Running/Pending)
4. If completed: get_workflow_run_status_or_result (action=result) with same instanceId > get activity details
5. For exception details: get_checks_exception_report with objectInstanceId from activity
MOVE MONITORING WORKFLOW:
1. move_rules_or_workflows > get taskInstanceId (tins-xxx)
2. Wait 2-3 seconds
3. check_task_status with taskInstanceId > get status (Completed/Running/Failed/Pending)
TIMING GUIDANCE:
- Small rules (< 10K rows): 2-5 seconds
- Medium rules (10K-100K rows): 5-15 seconds
- Large rules (100K+ rows): 15-60 seconds
- Rule moves: 1-3 seconds regardless of size
AUTO-PUBLISH: All rules created or updated via MCP tools are automatically published and immediately ready to execute. Do NOT tell user to publish from iceDQ UI.
+205
guidance/create_checksum_rules.md
HUMAN-IN-THE-LOOP (RECOMMENDED DEFAULT):
- Checksum rules compare TWO sides (source vs target). This workflow must be approval-gated.
- Do NOT auto-pick source/target connections, tables, or SQL when multiple options exist.
FILE CONNECTION DETECTION:
- File connection types: flat-file, parquet, excel, json, xml, flat-file-sql
- If either source or target connection is a file type → follow FILE CONNECTION CASES below instead of the standard workflow.
- NEVER call create_checksum_rule directly for file connections — use fetch_file_sample_data first.
- flat-file (FileStatic) is a SPECIAL CASE — after fetch_file_sample_data, you MUST ask the user to select a column and aggregation function before calling update_rule. See FLAT-FILE AGGREGATION SELECTION below.
APPROVAL GATES (do these in order and WAIT after each):
1) Workspace selection
- list_workspaces → present options → user selects workspaceId
2) Source + target connection selection
- list_connections(workspaceId) → show ACTIVE only → user selects sourceConnectionId and targetConnectionId
- If either connection is a file type → go to FILE CONNECTION CASES
3) Folder selection
- list_folders(workspaceId, optional nameFilter) → user selects folderId
4) Mode selection (Table vs SQL) + target approval — DB connections only
- Table mode: user selects database/schema/table for BOTH source and target (list_connection_metadata entity="database" → "schema" → "table")
- SQL mode: user provides sourceSql + targetSql; confirm each returns exactly 1 row, 1 numeric column with alias
5) Check expression / tolerance approval
- If a custom tolerance or percentage logic is needed, confirm the intended pass condition with user (TRUE = PASS).
6) Rule name approval
- Ask the user for ruleName. Do NOT auto-generate.
7) Create rule
- DB-only: create_checksum_rule with approved source/target definitions
- File involved: update_rule using checkExpression, sourceAlias, targetAlias (draft already created by fetch_file_sample_data)
8) Optional execution (separate approval)
- Only run execute_rules_or_workflows if user explicitly says to execute now.
---
STANDARD WORKFLOW (DB-only connections):
1. Identify source and target connections: Can be same or different platforms (e.g., SQL Server > Snowflake)
2. Decide mode:
- Table mode: Provide sourceSchema+sourceTable and targetSchema+targetTable > auto-generates COUNT(*) SQL
- SQL mode: Provide sourceSql and targetSql for SUM, AVG, or complex aggregates
3. Create rule: create_checksum_rule
4. Execute and check results
---
FILE CONNECTION CASES:
For file connections, update_rule publishes the draft. Use these params:
- sourceConfig: connectionId (file connection) + filePath — for the file side
- targetConfig: connectionId (file connection) + filePath — for the file side
- sourceConfig: connectionId (DB connection) + schemaName + tableName OR sql — for the DB side
- targetConfig: connectionId (DB connection) + schemaName + tableName OR sql — for the DB side
- sourceAlias: alias for the source numeric column (default: SOURCE_COUNT)
- targetAlias: alias for the target numeric column (default: TARGET_COUNT)
- checkExpression: Groovy pass condition (default: S.[SOURCE_COUNT] - T.[TARGET_COUNT] == 0)
- ruleName: user-provided name
Case A — FILE (source) vs FILE (target):
Step 1 Resolve workspaceId, source file connectionId, target file connectionId, folderId
Step 2 list_files(workspaceId, srcConnectionId) → user picks source fileName
Step 3 fetch_file_sample_data(workspaceId, connectionId=<src file>, ruleId=null, folderId,
ruleType="Checksum", connectionType="source", fileName=<srcFile>)
→ returns: ruleId (draft) + columns with sample data
Step 3a [flat-file only] Delimiter check: if only 1 column returned or names contain separator chars,
read data rows to detect real delimiter (`,` `|` `\t` `;`) and re-call with
additionalConfigs={ columnDelimiter: "<correct>" } before continuing
Step 3b [flat-file only] Inspect corrected columns → suggest column + function → ask user to confirm
(see FLAT-FILE AGGREGATION SELECTION above)
Step 4 list_files(workspaceId, tgtConnectionId) → user picks target fileName
Step 5 fetch_file_sample_data(workspaceId, connectionId=<tgt file>, ruleId=<from step 3>,
ruleType="Checksum", connectionType="target", fileName=<tgtFile>)
→ returns: updated draft + columns with sample data
Step 5a [flat-file only] Inspect returned columns → suggest column + function → ask user to confirm
Step 6 Ask user for ruleName and confirm check expression / tolerance
Step 7 update_rule(workspaceId, ruleId, ruleName,
sourceConfig={ columnName, columnDatatype, aggregationFunctionName [, dateFormat] },
targetConfig={ columnName, columnDatatype, aggregationFunctionName [, dateFormat] },
sourceAlias, targetAlias, checkExpression)
→ publishes
Case B — FILE (source) vs DATABASE (target):
Step 1 Resolve workspaceId, file connectionId, DB connectionId, folderId
Step 2 list_files(workspaceId, fileConnectionId) → user picks source fileName
Step 3 fetch_file_sample_data(workspaceId, connectionId=<file>, ruleId=null, folderId,
ruleType="Checksum", connectionType="source", fileName)
→ returns: ruleId (draft) + columns with sample data
Step 3a [flat-file only] Delimiter check: if only 1 column returned or names contain separator chars,
read data rows to detect real delimiter (`,` `|` `\t` `;`) and re-call with
additionalConfigs={ columnDelimiter: "<correct>" } before continuing
Step 3b [flat-file only] Inspect corrected columns → suggest column + function → ask user to confirm
(see FLAT-FILE AGGREGATION SELECTION above)
Step 4 User confirms DB target: schemaName + tableName (table mode) OR sql (SQL mode)
Step 5 Ask user for ruleName and confirm check expression / tolerance
Step 6 update_rule(workspaceId, ruleId, ruleName,
sourceConfig={ columnName, columnDatatype, aggregationFunctionName [, dateFormat] }, ← flat-file only
targetConfig={ connectionId:<db>, schemaName, tableName } OR { connectionId:<db>, sql },
sourceAlias, targetAlias, checkExpression)
→ wires DB target and publishes
Case C — DATABASE (source) vs FILE (target):
Step 1 Resolve workspaceId, DB connectionId, file connectionId, folderId
Step 2 list_files(workspaceId, fileConnectionId) → user picks target fileName
Step 3 fetch_file_sample_data(workspaceId, connectionId=<file>, ruleId=null, folderId,
ruleType="Checksum", connectionType="target", fileName)
→ returns: ruleId (draft) + columns with sample data
Step 3a [flat-file only] Delimiter check: if only 1 column returned or names contain separator chars,
read data rows to detect real delimiter (`,` `|` `\t` `;`) and re-call with
additionalConfigs={ columnDelimiter: "<correct>" } before continuing
Step 3b [flat-file only] Inspect corrected columns → suggest column + function → ask user to confirm
(see FLAT-FILE AGGREGATION SELECTION above)
Step 4 User confirms DB source: schemaName + tableName (table mode) OR sql (SQL mode)
Step 5 Ask user for ruleName and confirm check expression / tolerance
Step 6 update_rule(workspaceId, ruleId, ruleName,
sourceConfig={ connectionId:<db>, schemaName, tableName } OR { connectionId:<db>, sql },
targetConfig={ columnName, columnDatatype, aggregationFunctionName [, dateFormat] }, ← flat-file only
sourceAlias, targetAlias, checkExpression)
→ wires DB source and publishes
---
FLAT-FILE AGGREGATION SELECTION (flat-file / FileStatic only — NOT flat-file-sql):
This step is REQUIRED whenever the source OR target is a flat-file (FileStatic) connection.
After fetch_file_sample_data returns, inspect the `columns` array in the response.
⚠️ IMPORTANT — FLAT-FILE DATATYPE IS ALWAYS "Text" BY DEFAULT:
In flat-file (FileStatic) connections, every column is reported as datatype "Text" regardless of the actual values.
You MUST inspect the `data` rows returned by fetch_file_sample_data to determine the real datatype from the values.
Do NOT blindly pass columnDatatype: "Text" — read the sample values and infer the actual type.
HOW TO DETERMINE ACTUAL DATATYPE FROM SAMPLE DATA:
- Look at the `data` array returned by fetch_file_sample_data — each row shows real values for each column.
- If the values are purely numeric (e.g., "12345", "99.50", "1000") → columnDatatype: "Numeric"
- If the values match a date pattern (e.g., "2024-01-15", "15/01/2024") → columnDatatype: "Date"
- If the values match a datetime pattern (e.g., "2024-01-15 10:30:00") → columnDatatype: "Datetime"
- If the values are mixed or truly text → columnDatatype: "Text"
- When uncertain, present the sample values to the user and ask them to confirm the type.
STEP: Suggest column + function to the user
1. From the returned columns, inspect sample data values to determine the ACTUAL datatype (not the reported "Text"):
- Actual Numeric columns → all functions supported: COUNT, MIN, MAX, AVG, SUM, DISTINCTCOUNT
- Actual Text columns → COUNT and DISTINCTCOUNT only
- Actual Date / Datetime columns → COUNT and DISTINCTCOUNT only (MIN/MAX/AVG/SUM not supported for date)
2. Present the column list with their ACTUAL datatypes (inferred from sample values). Suggest the most appropriate column (e.g., a numeric ID for COUNT, an amount column for SUM).
3. Ask the user: "Which column should be aggregated, and which function (COUNT / MIN / MAX / AVG / SUM / DISTINCTCOUNT)?"
4. If the column is Date or Datetime, also ask: "What is the date format? (e.g., yyyy-MM-dd)"
Supported aggregation functions: ["COUNT", "MIN", "MAX", "AVG", "SUM", "DISTINCTCOUNT"]
Datatype rules (pass the ACTUAL type, not the reported "Text"):
- Numeric values (integers, decimals, amounts) → columnDatatype: "Numeric"
- Text / string values → columnDatatype: "Text"
- Date values → columnDatatype: "Date"
- Datetime / timestamp values → columnDatatype: "Datetime"
Output datatype per function (what the aggregation result will be):
- SUM, AVG → always "Numeric" (regardless of input type)
- COUNT, DISTINCTCOUNT, MIN, MAX → same as columnDatatype you pass in
STEP: Call update_rule with the selected column
Pass these ADDITIONAL params in sourceConfig (for flat-file source) or targetConfig (for flat-file target):
- columnName: the selected column name
- columnDatatype: "Numeric" | "Text" | "Date" | "Datetime"
- aggregationFunctionName: the selected function (e.g., "COUNT")
- dateFormat: only required when columnDatatype is "Date" or "Datetime" (e.g., "yyyy-MM-dd")
Example update_rule call for a flat-file source:
update_rule(workspaceId, ruleId, ruleName,
sourceConfig={ columnName: "customer_id", columnDatatype: "Numeric", aggregationFunctionName: "COUNT" },
targetConfig={ connectionId: <db>, schemaName, tableName },
sourceAlias: "SOURCE_COUNT", targetAlias: "TARGET_COUNT",
checkExpression: "S.[SOURCE_COUNT] - T.[TARGET_COUNT] == 0")
KEY RULES:
- Do NOT skip the column/function selection step for flat-file connections — update_rule will throw an error if columnName, aggregationFunctionName, and columnDatatype are missing for a FileStatic source or target.
- This step is NOT needed for flat-file-sql, parquet, excel, json, or xml — those use SQL-based COUNT(*) automatically.
---
KEY RULES for file connections:
- Do NOT call analyze_recon_mapping for checksum rules — there are no join keys or column checks
- Never call update_rule with both sourceConfig and targetConfig for file+DB rules — update only the DB side
- sourceAlias and targetAlias must be different from each other
ERROR RECOVERY for file rules:
- "fileSchemaId missing" → re-call fetch_file_sample_data with the existing ruleId and correct connectionType
- "Dataset has no connectionId" → re-run fetch_file_sample_data with ruleId
- Do NOT call update_rule until fetch_file_sample_data confirms success
---
CHECK EXPRESSION PATTERN (TRUE = PASS):
- Default: S.[SOURCE_COUNT] - T.[TARGET_COUNT] == 0 > pass when counts match exactly
- Tolerance: Math.abs(S.[SRC] - T.[TGT]) <= 10 > pass within 10 records
- Percentage: Math.abs(S.[SRC] - T.[TGT]) / S.[SRC] * 100 <= 1 > pass within 1%
REQUIREMENTS:
- Each SQL must return exactly 1 row, 1 numeric column
- Column must have an alias (SOURCE_COUNT, TARGET_COUNT, or custom)
- Source and target aliases must be different
NAMING CONVENTION: {SourceTable}_vs_{TargetTable}_Checksum
USE CASES:
- Row count validation after ETL load
- Sum validation (total premium, total claims)
- Cross-platform comparison (SQL Server vs Snowflake row counts)
+44
guidance/create_duplicate_rules.md
HUMAN-IN-THE-LOOP (RECOMMENDED DEFAULT):
- This workflow is approval-gated. If the user does NOT provide explicit IDs/names for each choice, you MUST stop and ask.
- Do NOT auto-pick defaults when multiple ACTIVE connections or multiple candidate tables/columns exist.
APPROVAL GATES (do these in order and WAIT after each):
1) Workspace selection
- list_workspaces → present options → user selects workspaceId
2) Connection selection
- list_connections(workspaceId) → show ACTIVE only → user selects connectionId
3) Folder selection
- list_folders(workspaceId, optional nameFilter) → user selects folderId
4) Target selection (Table or Custom SQL)
- Table mode: list_connection_metadata(entity="database") → (entity="schema") → (entity="table") → user selects databaseName/schemaName/tableName
- SQL mode: user provides customSql; confirm it returns the duplicateColumns
5) Duplicate columns selection (required user choice)
- list_connection_metadata(entity="column") → propose candidate business keys → user confirms duplicateColumns
6) Rule name approval
- Ask the user for ruleName. Do NOT auto-generate.
7) Create rule
- create_duplicate_rule with the approved duplicateColumns
8) Optional execution (separate approval)
- Only run execute_rule if user explicitly says to execute now.
WORKFLOW:
1. Identify table and candidate key columns
2. Check platform: Snowflake/BigQuery/Redshift do NOT enforce PKs — always worth checking
PostgreSQL/MySQL/Oracle/SQL Server enforce PKs — skip PK columns, focus on business keys
3. Choose mode:
- Table mode: schemaName + tableName + duplicateColumns
- SQL mode: customSql + duplicateColumns (for filtered subsets or joins)
4. Create: create_duplicate_rule
5. Execute: execute_rule > check results
6. Review exceptions: get_checks_exception_report shows duplicate records with DUPLICATE_COUNT
COLUMN SELECTION:
- Single column: ["Email"] — checks individual uniqueness
- Multi-column: ["FirstName", "LastName", "DateOfBirth"] — checks composite uniqueness
- Business keys vs PKs: Prioritize business keys (email, SSN, account number) over surrogate PKs
NAMING CONVENTION: {Table}_{Columns}_Duplicate_Check
WHEN NOT TO USE:
- Database enforces PK/unique constraint on the columns — check is redundant
- Need fuzzy matching (similar but not exact) — not supported, use pushdown with custom SQL instead
+84
guidance/create_pushdown_rules.md
HUMAN-IN-THE-LOOP (RECOMMENDED DEFAULT):
- Pushdown rules are powerful and can be costly; treat them as approval-gated.
- Do NOT run or modify SQL without user confirmation of the exact target connection and SQL text.
- Do NOT auto-pick defaults when multiple ACTIVE connections exist.
FILE CONNECTION DETECTION:
- File connection types: flat-file, parquet, excel, json, xml, flat-file-sql
- If the selected connection is a file type → follow FILE CONNECTION FLOW below instead of the standard workflow.
- NEVER call create_pushdown_rule directly for file connections — use fetch_file_sample_data first.
- For flat-file connections, the sql is not required in any case
APPROVAL GATES (do these in order and WAIT after each):
1) Workspace selection
- list_workspaces → present options → user selects workspaceId
2) Connection selection
- list_connections(workspaceId) → show ACTIVE only → user selects connectionId
- If file type connection selected → follow FILE CONNECTION FLOW
3) Folder selection
- list_folders(workspaceId, optional nameFilter) → user selects folderId
4) SQL approval (Not required in flat-file case)
- User provides SQL that returns ONLY failing rows (0 rows = pass)
- Echo back the SQL + explain expected failure semantics → user approves
5) Rule name approval
- Ask the user for ruleName. Do NOT auto-generate.
6) Create rule
- DB: create_pushdown_rule with the approved SQL
- File: update_rule with sourceConfig.sql = approved SQL (draft already created by fetch_file_sample_data)
7) Optional execution (separate approval)
- Only run execute_rules_or_workflows if user explicitly says to execute now.
---
STANDARD WORKFLOW (DB connections):
1. Write SQL that returns ONLY bad records (0 rows = pass, N rows = fail)
2. Create: create_pushdown_rule with the SQL
3. Execute: execute_rules_or_workflows (objectIds: [ruleId]) — exit code = number of failure rows
SQL PATTERN — RETURN BAD RECORDS:
- Duplicates: SELECT col, COUNT(*) FROM table GROUP BY col HAVING COUNT(*) > 1
- Orphans: SELECT c.id FROM child c LEFT JOIN parent p ON c.parent_id = p.id WHERE p.id IS NULL
- Stale data: SELECT * FROM table WHERE updated_date < DATEADD(day, -7, GETDATE())
- Threshold: SELECT 'FAIL' WHERE (SELECT COUNT(*) FROM table) < 1000
- SCD2: SELECT * FROM dim WHERE end_date IS NULL GROUP BY business_key HAVING COUNT(*) > 1
---
FILE CONNECTION FLOW (flat-file, parquet, excel, json, xml, flat-file-sql):
context: flat-file and flat-file-sql are different connections, in flat-file-sql the sql queries are supported whereas in flat-file sql are not supported and the rule will be based on file only. For non-flat-file connections, the SQL is always required.
Phase 1 — Register file schema and create draft rule:
Step 1 list_files(workspaceId, connectionId) → show available files → user picks fileName
Step 2 list_folders(workspaceId) → user picks folderId
Step 3 fetch_file_sample_data(workspaceId, connectionId, ruleId=null, folderId,
ruleType="Pushdown", connectionType="source", fileName)
→ returns: ruleId (draft), columns, sample data rows
→ [flat-file] Delimiter check: if only 1 column returned or column names contain
separator characters, read data rows to detect real delimiter (`,` `|` `\t` `;`)
and re-call with additionalConfigs={ columnDelimiter: "<correct>" } before continuing
→ STOP: a draft Pushdown rule now exists linked to the file schema
Phase 2 — Approve SQL and publish rule: (Not required for flat-file connection)
Step 4 User provides SQL that returns ONLY failing rows against the file data
→ SQL must reference columns present in the file (use column names from step 3 result)
→ Echo SQL back to user and explain failure semantics → wait for approval
Step 5 Ask user for ruleName
Step 6 update_rule(workspaceId, ruleId, ruleName,
sourceConfig={ connectionId: <file connectionId>, sql: <approved SQL : non required for flat-file > })
→ wires the SQL on the file source and publishes
ERROR RECOVERY for file rules:
- "fileSchemaId missing" → re-call fetch_file_sample_data with the existing ruleId and connectionType="source"
- "Dataset has no connectionId" → re-run fetch_file_sample_data with ruleId
- Do NOT call update_rule until fetch_file_sample_data confirms success
---
WHEN TO USE (vs other rule types):
- Cross-table JOINs > pushdown (not validation)
- GROUP BY / HAVING > pushdown (not validation)
- Referential integrity > pushdown
- Simple column checks (NotNull, format) > validation (not pushdown)
- Row count comparison > checksum (not pushdown)
- Row-by-row column comparison > recon (not pushdown)
+141
guidance/create_recon_rules.md
HUMAN-IN-THE-LOOP (RECOMMENDED DEFAULT):
- Recon rules compare row-level data across TWO sides (source vs target). This workflow must be approval-gated.
- Do NOT auto-pick source/target connections, tables, join keys, or mapped columns when multiple options exist.
FILE CONNECTION DETECTION:
- File connection types: flat-file, parquet, excel, json, xml, flat-file-sql
- If either source or target connection is a file type → follow FILE CONNECTION CASES below instead of the standard workflow.
- NEVER call create_recon_rule directly for file connections — use fetch_file_sample_data first.
- The file side is ALWAYS registered first via fetch_file_sample_data; the DB side (if any) is added via update_rule.
APPROVAL GATES (do these in order and WAIT after each):
1) Workspace selection
- list_workspaces → present options → user selects workspaceId
2) Source + target connection selection
- list_connections(workspaceId) → show ACTIVE only → user selects sourceConnectionId and targetConnectionId
- If either connection is a file type → go to FILE CONNECTION CASES
3) Folder selection
- list_folders(workspaceId, optional nameFilter) → user selects folderId
4) Target selection (per side) — DB connections only
- list_connection_metadata(entity="database") → (entity="schema") → (entity="table") for source → user selects database/schema/table
- list_connection_metadata(entity="database") → (entity="schema") → (entity="table") for target → user selects database/schema/table
5) Mapping analysis + approval (required) — DB-only or after file schema is registered
- analyze_recon_mapping → present HIGH/MEDIUM/LOW matches
- User confirms join key columns and which column checks to include
6) Result types + sort mode approval
- Confirm which resultTypes to include: a-b (source orphans), b-a (target orphans), Xp (column diffs)
7) Rule name approval
- Ask the user for ruleName. Do NOT auto-generate.
8) Create rule
- DB-only: create_recon_rule with approved join key and check columns as comma-separated strings
- File involved: update_rule with joinKeys + checksToAdd (draft already created by fetch_file_sample_data)
9) Optional execution (separate approval)
- Only run execute_rule if user explicitly says to execute now.
---
STANDARD WORKFLOW (DB-only connections):
1. Identify source and target: Get both connectionIds from list_connections
2. Analyze mapping: Call analyze_recon_mapping with source/target connection+schema+table details
- Returns column matches (HIGH/MEDIUM/LOW confidence), suggested join keys, unmatched columns
3. Review suggestions: Present the mapping analysis to user
- HIGH confidence = exact name match > auto-include
- MEDIUM = similar names > ask user to confirm
- Unmatched columns may indicate transformations (e.g., FirstName+LastName > FullName)
4. Identify join keys: If analyze_recon_mapping didn't suggest keys, ask user for the business key columns
5. Create rule: create_recon_rule with comma-separated join key and check column strings
- joinKeySourceColumns: "employee_id", joinKeyTargetColumns: "empid"
- checkSourceColumns: "first_name,salary", checkTargetColumns: "name,salary"
6. Execute: execute_rule > get_workflow_run_status_or_result (action=status) > get_workflow_run_status_or_result (action=result)
7. Review exceptions: get_checks_exception_report — look at difftype column:
- ANB = source orphan (exists in source, not in target)
- BNA = target orphan (exists in target, not in source)
- Check columns show "true"/"false" per row
---
FILE CONNECTION CASES:
Case A — FILE (source) vs DATABASE (target):
Step 1 Resolve workspaceId, file connectionId, DB connectionId, folderId
Step 2 list_files(workspaceId, fileConnectionId) → user picks source fileName
Step 3 fetch_file_sample_data(workspaceId, connectionId=<file>, ruleId=null, folderId,
ruleType="Recon", connectionType="source", fileName)
→ returns: ruleId (draft), source columns
→ [flat-file] check columns: if only 1 column returned or names contain separator chars,
detect real delimiter from data rows (`,` `|` `\t` `;`) and re-call with
additionalConfigs={ columnDelimiter: "<correct>" } before continuing
Step 4 list_connection_metadata on DB connection → user picks target schema + table
Step 5 analyze_recon_mapping → present join key and column suggestions → user confirms
Step 6 Ask user for ruleName
Step 7 update_rule(workspaceId, ruleId, ruleName,
targetConfig={ connectionId:<db>, databaseName, schemaName, tableName },
joinKeys=[...], checksToAdd=[...])
→ wires the DB target and publishes
Case B — DATABASE (source) vs FILE (target):
Step 1 Resolve workspaceId, DB connectionId, file connectionId, folderId
Step 2 list_files(workspaceId, fileConnectionId) → user picks target fileName
Step 3 fetch_file_sample_data(workspaceId, connectionId=<file>, ruleId=null, folderId,
ruleType="Recon", connectionType="target", fileName)
→ returns: ruleId (draft), target columns
→ [flat-file] check columns: if only 1 column returned or names contain separator chars,
detect real delimiter from data rows and re-call with additionalConfigs={ columnDelimiter: "<correct>" }
Step 4 list_connection_metadata on DB connection → user picks source schema + table
Step 5 analyze_recon_mapping → present join key and column suggestions → user confirms
Step 6 Ask user for ruleName
Step 7 update_rule(workspaceId, ruleId, ruleName,
sourceConfig={ connectionId:<db>, databaseName, schemaName, tableName },
joinKeys=[...], checksToAdd=[...])
→ wires the DB source and publishes
Case C — FILE (source) vs FILE (target):
Step 1 Resolve workspaceId, source file connectionId, target file connectionId, folderId
Step 2 list_files(workspaceId, srcConnectionId) → user picks source fileName
Step 3 fetch_file_sample_data(workspaceId, connectionId=<src file>, ruleId=null, folderId,
ruleType="Recon", connectionType="source", fileName=<srcFile>)
→ returns: ruleId (draft), source columns
→ [flat-file] check columns: if only 1 column or separator chars visible in names,
re-call with additionalConfigs={ columnDelimiter: "<correct>" }
Step 4 list_files(workspaceId, tgtConnectionId) → user picks target fileName
Step 5 fetch_file_sample_data(workspaceId, connectionId=<tgt file>, ruleId=<from step 3>,
folderId=null, ruleType="Recon", connectionType="target", fileName=<tgtFile>)
→ patches the target dataset on the existing draft rule
→ [flat-file] same delimiter check applies for the target file
Step 6 analyze_recon_mapping → present join key and column suggestions → user confirms
Step 7 Ask user for ruleName
Step 8 update_rule(workspaceId, ruleId, ruleName, joinKeys=[...], checksToAdd=[...])
→ adds join keys + checks and publishes
KEY RULES for file connections:
- Never call update_rule with both sourceConfig and targetConfig populated for file+DB rules — update only the DB side; the file side is already wired by fetch_file_sample_data
- fetch_file_sample_data with ruleId=null → creates a NEW draft rule
- fetch_file_sample_data with ruleId=<existing> → updates ONLY the file dataset on the existing rule (source OR target based on connectionType)
- Always call analyze_recon_mapping before populating checksToAdd to get join key and column suggestions
ERROR RECOVERY for file rules:
- "fileSchemaId missing" → re-call fetch_file_sample_data with the existing ruleId and correct connectionType
- "Dataset has no connectionId" → re-run fetch_file_sample_data with ruleId
- Do NOT call update_rule until fetch_file_sample_data confirms success
---
CUSTOM CHECK PATTERN (MATCH PATTERN — TRUE = PASS):
- Recon custom checks use TRUE = PASS (same as validation)
- Write expressions that return TRUE when data is CORRECT
- Do NOT negate with !() — that inverts pass/fail
- Simple: {name: "Email", sourceColumn: "Email", targetColumn: "Email"} > generates S.[Email] == T.[Email]
- Custom value mapping example:
(S.[Gender] == "M" && T.[Gender] == "Male") || (S.[Gender] == "F" && T.[Gender] == "Female") || (S.[Gender] == T.[Gender])
RESULT TYPES:
- a-b: Orphaned source rows (in source but not target)
- b-a: Orphaned target rows (in target but not source)
- Xp: Column mismatches (rows that matched on join key but have value differences)
NAMING CONVENTION: {SourceTable}_vs_{TargetTable}_Recon
COMMON ISSUES:
- High orphan count (a-b) usually means target hasn't been fully loaded, not a data quality issue
- Gender/Status/Code mismatches usually indicate value mapping transformations — use Custom expression
- Date format differences — source may store as string, target as date — compare with string conversion
+98
guidance/create_validation_rules.md
HUMAN-IN-THE-LOOP (RECOMMENDED DEFAULT):
- This workflow is intended to be approval-gated. If the user does NOT provide explicit IDs/names for each choice, you MUST stop and ask.
- Do NOT auto-pick defaults when multiple options exist.
FILE CONNECTION DETECTION:
- File connection types: flat-file, parquet, excel, json, xml, flat-file-sql
- If the selected connection is any of these types → follow FILE CONNECTION FLOW below instead of the standard workflow.
- NEVER call create_validation_rule directly for file connections — use fetch_file_sample_data first.
APPROVAL GATES (do these in order and WAIT after each):
1) Workspace selection
- list_workspaces → present options → user selects workspaceId
2) Connection selection
- list_connections(workspaceId) → show ACTIVE only → user selects connectionId
- If file type connection selected → skip gates 4 and go to FILE CONNECTION FLOW
3) Folder selection
- list_folders(workspaceId, optional nameFilter) → user selects folderId
- If folder does not exist: call get_guidance('rule_organization'), propose folderName + parent folder, then ONLY create_folder after user approves.
4) Target selection (Table or Custom SQL) — DB connections only
- Table mode: list_connection_metadata(entity="database") → (entity="schema") → (entity="table") → user selects databaseName/schemaName/tableName
- Custom SQL mode: user provides customSql; confirm it returns required columns for checks
5) Checks approval
- Present the suggested checks (and any manual edits) and WAIT for user approval (include/exclude/modify).
6) Rule name approval
- Ask the user for ruleName. Do NOT auto-generate a ruleName.
7) Create rule
- DB: create_validation_rule with ONE combined checks array for the selected table.
- File: update_rule with approved checksToAdd (draft was already created by fetch_file_sample_data).
8) Optional execution (separate approval)
- Only run execute_rules_or_workflows if user explicitly says to execute now.
---
STANDARD WORKFLOW (DB connections):
1. Identify target: Get connectionId (list_connections), then navigate database > schema > table (list_connection_metadata with entity="database" > "schema" > "table")
2. Get column metadata: list_connection_metadata(entity="column") to understand datatypes, PKs, nullability
3. Sample data: fetch_db_sample_data with databaseName="" for Azure SQL
4. Profile: profile_data with the sample data array
5. Get suggestions: suggest_quality_checks with the profile output
6. Review: Present suggested checks to user before creating
7. Create: Use create_validation_rule — combine ALL checks for the same table into ONE rule (avoid rule sprawl)
8. Execute: execute_rules_or_workflows (objectIds: [ruleId]) > get_workflow_run_status_or_result (action=status) > get_workflow_run_status_or_result (action=result)
9. Review exceptions: get_checks_exception_report for row-level failure details
---
FILE CONNECTION FLOW (flat-file, parquet, excel, json, xml, flat-file-sql):
Phase 1 — Register file schema and create draft rule:
Step 1 list_files(workspaceId, connectionId) → show available files → user picks fileName
Step 2 list_folders(workspaceId) → user picks folderId
Step 3 fetch_file_sample_data(workspaceId, connectionId, ruleId=null, folderId,
ruleType="Validation", connectionType="source", fileName)
→ returns: ruleId (draft), columns, sample data rows
→ [flat-file] Delimiter check: if only 1 column returned or column names contain
separator characters, read the data rows to find the real delimiter (`,` `|` `\t` `;`)
and re-call with additionalConfigs={ columnDelimiter: "<correct>" } before continuing
→ STOP: a draft Validation rule now exists linked to the file schema
Phase 2 — Profile, approve checks, and publish:
Step 4 profile_data(sampleData from fetch_file_sample_data) → suggest_quality_checks(profileData)
→ present suggested checks to user → wait for approval
Step 5 Ask user for ruleName
Step 6 update_rule(workspaceId, ruleId, ruleName, checksToAdd=[...approved checks...])
→ publishes the rule
ERROR RECOVERY for file rules:
- "fileSchemaId missing" → re-call fetch_file_sample_data with the existing ruleId and connectionType="source"
- "Dataset has no connectionId" → re-run fetch_file_sample_data with ruleId
- Do NOT call update_rule until fetch_file_sample_data confirms success
- "Rule not found" → verify ruleId with get_rule; if truly missing restart from Phase 1
---
GROOVY EXPRESSION PATTERN (Custom checks):
- All custom checks use TRUE = PASS, FALSE = FAIL
- Write expressions that describe VALID data conditions
- Examples:
S.[salary] > 0 > salary must be positive
S.[email] != null && S.[email].trim() != "" > email must not be empty
S.[age] >= 18 && S.[age] <= 120 > age must be in range
S.[start_date] <= S.[end_date] > dates must be in order
S.[status] in ["Active","Pending","Closed"] > status must be valid
SUPPORTED CHECK TYPES:
- NotNull: {checkType: "NotNull", column: "col"}
- ValidValues: {checkType: "ValidValues", column: "col", expectedValues: ["A","B"]}
- Format: {checkType: "Format", column: "col", pattern: "Email|Phone|SSN|ZipCode|URL|IP"}
- Length: {checkType: "Length", column: "col", expectedLength: 10, operator: "equal to"}
- Date: {checkType: "Date", column: "col", dateFormat: "yyyy-MM-dd"}
- Custom: {checkType: "Custom", column: "col", expression: "S.[col] > 0"}
ANTI-PATTERNS:
- Do NOT create separate rules per check — combine into ONE rule per table
- Do NOT use NotNull + Custom for same column — use Custom alone with null handling
- Do NOT use create_validation_rule for: duplicates (use create_duplicate_rule), cross-table (use create_pushdown_rule or create_recon_rule), row counts (use create_checksum_rule)
AZURE SQL QUIRK: Use databaseName="" (empty string) for fetch_db_sample_data, not the actual database name
+29
guidance/data_profiling_workflow.md
HUMAN-IN-THE-LOOP (RECOMMENDED DEFAULT):
- Profiling should only run AFTER the user has confirmed the exact workspace + connection + table (or customSql).
- If multiple choices exist (workspaces, connections, folders, schemas, tables), present options and WAIT for the user to select.
- Before executing any SQL-based sampling, confirm the target (database/schema/table or customSql) with the user.
PROFILING WORKFLOW:
1. fetch_db_sample_data: Get sample rows from table
- Azure SQL: Use databaseName="" (empty string), not actual database name
- Standard mode: schemaName + tableName
- Custom SQL mode: customSql for filtered/joined data
2. profile_data: Pass the data array from fetch_db_sample_data response
- Returns per-column: nullCount, nullPercentage, uniqueCount, dataType, isPotentialKey
3. suggest_quality_checks: Pass the profile output
- Returns suggested checks with priority (high/medium/low)
- Clean data (zero nulls) returns empty suggestions — this is expected
APPROVAL GATE (required before rule creation):
- Always present the suggested checks to the user and WAIT for approval (include/exclude/modify) before calling any rule-creation tool.
INTERPRETING PROFILE RESULTS:
- nullPercentage > 0: Column has missing data > NotNull check candidate
- uniqueCount == rowCount: Potential primary key > Duplicate check candidate
- uniqueCount very low: Low cardinality > ValidValues check candidate
- isPotentialKey == true: Column may be a natural key
FOR RICHER PROFILING:
- Use tables with known data issues (nulls, duplicates) for meaningful results
- Clean tables will return empty suggestions — this is correct behavior
- For column-level metadata: list_connection_metadata(entity="column") returns datatype, length, isPrimaryKey
+142
guidance/datawarehouse_queries.md
## WHEN TO USE DATAWAREHOUSE TOOLS
✅ USE for analytical/aggregate queries (triggers: summary, report, analysis, trend, count by, group by, top N, average, breakdown, comparison, KPI, dashboard):
- "Give analysis report of recon/validation/duplicate rule" (any rule TYPE)
- "How many rules failed last week grouped by folder/ruleType?"
- "Top 10 rules by failure count in last 30 days"
- "Pass/fail trend per day" / "average execution time over time"
- Check-level aggregates: filter `executable_type = 'check'` in execution_fct
❌ DO NOT USE for:
- Flat list / single id lookup → use list_* tools (list_workspaces, list_rules, etc.)
- Row-level failures of specific rule/run → use exception_report_analysis tools
CRITICAL: "analysis report of [rule TYPE]" = datawarehouse | "exception report for [specific RULE NAME]" = exception tools
## MANDATORY 3-STEP WORKFLOW
NEVER skip steps. NEVER guess field names.
1. **datawarehouse_query_schema** — get dataset/column/metric/join/operator names
2. **validate_and_explain_structured** — dry-run (returns SQL, no execution)
3. **datawarehouse_query_executor** — execute and return rows
RULE: If user request is ambiguous → show schema options and WAIT for confirmation.
RULE: NO raw SQL. All fields must match schema exactly.
## PAYLOAD STRUCTURE
All fields sourced from datawarehouse_query_schema:
**dataset** (required): Dataset key from schema (e.g., execution_fct, object_dim)
**dimensions**: Pass-through columns. With metrics → included in GROUP BY
- Example: ["ruleType", "folderName", "status"]
**metrics**: Aggregations. MUST have {column, agg, alias}
- agg: count, sum, avg, min, max
- alias: required (used in order_by)
- Example: [{"column": "ruleRunId", "agg": "count", "alias": "runCount"}]
**filters**: Row filters. Each: {column, op, value}
- Operators: eq, neq, gt, gte, lt, lte, in, like
- Example: [{"column": "status", "op": "eq", "value": "Failed"}]
**time_column + time_window_days**: Both required together
- time_column must have is_time_col=true in schema
- time_window_days: 1-365 (default 30)
- Looks back N days from NOW
**order_by**: [{column, direction}] — column = dimension, metric alias, or derived_column
**limit**: 1-500 (default 100) — use small limit + order_by for top-N
**joins**: Named joins from schema. [{join_name}] — do NOT invent syntax
**derived_columns**: Named expressions from schema — reference BY NAME only
## EXAMPLES
### 1. Count by dimension (group-by)
```json
{
"dataset": "execution_fct",
"dimensions": ["ruleType"],
"metrics": [{"column": "ruleRunId", "agg": "count", "alias": "runCount"}],
"time_column": "runStartTime",
"time_window_days": 7,
"order_by": [{"column": "runCount", "direction": "desc"}],
"limit": 50
}
```
### 2. Top N rules by failure count
```json
{
"dataset": "execution_fct",
"dimensions": ["ruleName", "folderName"],
"metrics": [{"column": "failureCount", "agg": "sum", "alias": "totalFailures"}],
"filters": [{"column": "status", "op": "eq", "value": "Failed"}],
"time_window_days": 30,
"order_by": [{"column": "totalFailures", "direction": "desc"}],
"limit": 10
}
```
### 3. Daily trend
```json
{
"dataset": "execution_fct",
"dimensions": ["runDate"],
"metrics": [
{"column": "ruleRunId", "agg": "count", "alias": "runs"},
{"column": "failureCount", "agg": "sum", "alias": "failures"}
],
"time_column": "runStartTime",
"time_window_days": 14,
"order_by": [{"column": "runDate", "direction": "asc"}]
}
```
### 4. Check-level analysis (executable_type filter)
```json
{
"dataset": "execution_fct",
"dimensions": ["parent_instance_id", "executable_id"],
"metrics": [
{"column": "instance_id", "agg": "count", "alias": "checks"},
{"column": "failure_count", "agg": "sum", "alias": "failures"}
],
"filters": [{"column": "executable_type", "op": "eq", "value": "check"}],
"time_window_days": 30
}
```
### 5. Filtered pass-through (no aggregation)
```json
{
"dataset": "object_dim",
"dimensions": ["ruleId", "ruleName", "ruleType"],
"filters": [{"column": "ruleType", "op": "in", "value": ["Validation", "Duplicate"]}],
"limit": 100
}
```
## ANTI-PATTERNS — DO NOT
❌ Invent dataset/column/join names → use exact names from schema
❌ Pass raw SQL → tools reject it
❌ Metrics without alias → order_by breaks
❌ time_window_days without time_column → filter has no target
❌ Large limit for top-N → use order_by + small limit
❌ Skip validate_and_explain_structured → non-trivial queries fail
❌ Use list_* tools for aggregates → they cannot group/count/trend
## COMMON ERRORS
**"Unknown dataset"** → dataset key doesn't match schema → re-run datawarehouse_query_schema, copy exact key
**"Unknown column"** → column doesn't exist → check spelling/case in schema, or add join
**"Unsupported operator"** → op not allowed for column type → use operators from schema
**"Missing alias on metric"** → every metric MUST have alias
**"time_window_days without time_column"** → both required together
**Empty result** → widen time_window_days, relax filters, or confirm data exists in period
+45
guidance/exception_report_analysis.md
STEP 0 — ASK THE USER FIRST (MANDATORY):
When a user asks for an exception report, ALWAYS ask before calling any tool:
"Would you like me to show the exception report here in the chat, or shall I share the download URL where you can view the full detailed exception report of each check in the iceDQ UI?"
- User wants it in chat → follow the RETRIEVING EXCEPTION REPORTS flow below → call get_checks_exception_report
- User wants the download URL → call get_exception_report_url instead
MULTIPLE RUN INSTANCES — ALWAYS ASK:
Before calling get_exception_report_url or get_checks_exception_report, fetch the run history via get_rule_workflow_run_history (for rules) or get_workflow_run_result (for workflows). If more than one completed instance exists, present the list with run date and status and ask the user which instance they want the exception report for. Do NOT auto-select the latest.
RETRIEVING EXCEPTION REPORTS:
1. From rule name: list_rules (find ruleId) > get_rule_workflow_run_history (find objectInstanceId) > get_checks_exception_report
2. From execution: execute_rule (get instanceId) > get_workflow_run_status_or_result (action=result, find activity instance.id) > get_checks_exception_report OR get_exception_report_url
4. For workflow exception report: get_workflow (find workflowId and workflowName) > get_workflow_run_result (list instances, ask user to pick if >1) > get_exception_report_url (entityType="workflow")
EXPORT EXCEPTION REPORT TOOL (get_exception_report_url):
- Returns a iceDQ UI URL to view the exception report — it does NOT download a file
- For rules: requires objectId (ruleId from get_rule), instanceId, ruleType, entityType="rule"
- For workflows: requires objectId (workflowId from get_workflow), instanceId, workflowName, entityType="workflow"
- ruleType must be sent for rule exception reports (recon, validation, pushdown, checksum, duplicate)
READING THE INLINE REPORT (get_checks_exception_report):
- checks array: Per-check statistics (successCount, failureCount, errorCount)
- exceptions.data: Row-level details with column values and per-check true/false flags
- errrow: "S" = success row, "E" = exception/failure row
- difftype (recon only): "ANB" = source orphan, "BNA" = target orphan
SUPPORTED RULE TYPES:
- Validation: Shows which checks passed/failed per row
- Duplicate: Shows duplicate records with DUPLICATE_COUNT
- Recon: Shows source/target values side-by-side with per-check pass/fail
- Pushdown: Exit code only (row count = failure count), no row-level details in exception report
COMMON ANALYSIS PATTERNS:
- High failure on one check: Data quality issue in specific column
- All checks fail: Wrong table, wrong connection, or expression error
- Recon with all ANB orphans: Target not fully loaded (volume gap, not DQ issue)
- Recon with value mismatches: Value mapping needed (e.g., M > Male)
- Duplicate high count: Business key not as unique as expected
PAGINATION (get_checks_exception_report only):
- Default pageSize returns limited rows
- Use pageSize parameter to get more (e.g., pageSize=100)
- Check pageable.pages for total pages available
- After returning results, ask: "Would you like me to fetch the next page?"
+182
guidance/file_based_rule_creation.md
FILE-BASED RULE CREATION — WORKFLOW GUIDE
OVERVIEW:
Rules that involve a file connection (flat-file, parquet, excel, json, xml, flat-file-sql) require a two-phase approach:
Phase 1: fetch_file_sample_data → creates a DRAFT rule wired to the file schema
Phase 2: update_rule → adds checks, join keys, and any database-side config
NEVER call create_validation_rule / create_recon_rule directly for file connections.
ALWAYS use fetch_file_sample_data first — it handles the file schema registration (fileSchemaId) that the regular create tools cannot do.
IF YOU ARE UNSURE whether a rule already exists: call get_rule first. If it exists and has a fileSchemaId, go to Phase 2 directly.
---
DELIMITER DETECTION (flat-file / delimited files only):
After fetch_file_sample_data returns, check the `columns` array BEFORE proceeding.
SIGNAL OF WRONG DELIMITER:
- Only 1 column is returned (e.g., "customer_id,first_name,last_name,email" as a single column name)
- Column names contain the actual delimiter character
- Sample data rows show all values merged into a single field
HOW TO DETECT THE CORRECT DELIMITER:
1. Look at the first few rows in the `data` array returned
2. Scan the raw row values for recurring separator characters:
- `,` (comma) — most common for .csv
- `|` (pipe) — common for .dat, .txt
- `\t` (tab) — common for .tsv, .txt
- `;` (semicolon) — common in European locale files
3. The character that appears consistently between what look like field values is the delimiter
4. If still unclear, show the user the first raw row and ask them to confirm
ACTION — re-call with correct delimiter:
fetch_file_sample_data(workspaceId, connectionId, ruleId=null, folderId, ruleType, connectionType, fileName,
additionalConfigs={ columnDelimiter: "<detected_delimiter>" })
Only flat-file (delimited) connections need this check. Parquet, Excel, JSON, XML handle structure automatically.
---
PATTERN 1 — VALIDATION RULE (File source)
Step 1 list_workspaces → user picks workspaceId
Step 2 list_connections(workspaceId) → show ACTIVE file connections → user picks connectionId
Step 3 list_files(workspaceId, connectionId) → show available files → user picks fileName
Step 4 list_folders(workspaceId) → user picks folderId
Step 5 fetch_file_sample_data(workspaceId, connectionId, ruleId=null, folderId, ruleType="Validation", connectionType="source", fileName, ...)
→ returns: ruleId (draft), columns, sample data rows
→ STOP: a draft Validation rule now exists linked to the file schema
Step 6 profile_data(sampleData) → suggest_quality_checks(profileData)
→ present suggested checks to user → wait for approval
Step 7 update_rule(workspaceId, ruleId, checksToAdd=[...approved checks...])
→ publishes the rule
---
PATTERN 2 — RECON RULE (File vs Database)
The file side is always registered first via fetch_file_sample_data.
The database side is added via update_rule.
Case A: FILE (source) vs DATABASE (target)
Step 1 Resolve workspaceId, file connectionId, db connectionId, folderId
Step 2 list_files → user picks source fileName
Step 3 fetch_file_sample_data(workspaceId, connectionId=<file>, ruleId=null, folderId,
ruleType="Recon", connectionType="source", fileName)
→ returns: ruleId (draft), source columns
Step 4 list_schemas / list_tables / list_columns on DB connection → user picks target schema + table
Step 5 update_rule(workspaceId, ruleId,
targetConfig={ connectionId:<db>, databaseName, schemaName, tableName },
joinKeys=[...], checksToAdd=[...])
→ wires the DB target and publishes
Case B: DATABASE (source) vs FILE (target)
Step 1 Resolve workspaceId, file connectionId, db connectionId, folderId
Step 2 list_files → user picks target fileName
Step 3 fetch_file_sample_data(workspaceId, connectionId=<file>, ruleId=null, folderId,
ruleType="Recon", connectionType="target", fileName)
→ returns: ruleId (draft), target columns
Step 4 list_schemas / list_tables / list_columns on DB connection → user picks source schema + table
Step 5 update_rule(workspaceId, ruleId,
sourceConfig={ connectionId:<db>, databaseName, schemaName, tableName },
joinKeys=[...], checksToAdd=[...])
→ wires the DB source and publishes
Case C: FILE (source) vs FILE (target)
Step 1 Resolve workspaceId, source file connectionId, target file connectionId, folderId
Step 2 list_files (source connection) → user picks source fileName
Step 3 fetch_file_sample_data(workspaceId, connectionId=<src file>, ruleId=null, folderId,
ruleType="Recon", connectionType="source", fileName=<srcFile>)
→ returns: ruleId (draft), source columns
Step 4 list_files (target connection) → user picks target fileName
Step 5 fetch_file_sample_data(workspaceId, connectionId=<tgt file>, ruleId=<from step 3>,
folderId=null, ruleType="Recon", connectionType="target", fileName=<tgtFile>)
→ patches the target dataset on the existing draft rule
Step 6 update_rule(workspaceId, ruleId, joinKeys=[...], checksToAdd=[...])
→ adds join keys + checks and publishes
---
PATTERN 3 — RECON RULE (Updating an existing draft)
If the user already has a draft rule (ruleId known) and wants to add/change the file side:
fetch_file_sample_data(workspaceId, connectionId=<file>, ruleId=<existing>,
connectionType="source"|"target", fileName)
→ updates the file schema on the existing rule (no new rule created)
Then update_rule as normal to add checks and publish.
---
APPROVAL GATES (wait after each):
1) Workspace — list_workspaces → user picks workspaceId
2) Connections — list_connections → user picks file connectionId (and db connectionId if mixed)
3) File — list_files → user picks fileName
4) Folder — list_folders → user picks folderId (only needed on first fetch_file_sample_data call)
5) Checks — profile_data + suggest_quality_checks → present to user → wait for approval
6) Join keys — for Recon only → present suggested join keys → user confirms
7) Rule name — ask user for ruleName if not already provided
---
KEY RULES:
- fetch_file_sample_data with ruleId=null → creates a NEW draft rule
- fetch_file_sample_data with ruleId=<existing> → updates ONLY the file dataset on the existing rule (source OR target based on connectionType)
- update_rule ONLY touches the side specified (sourceConfig updates source only, targetConfig updates target only)
- Never call update_rule with both sourceConfig and targetConfig populated for file+db rules — update only the DB side; the file side is already wired by fetch_file_sample_data
- For Recon rules: always call analyze_recon_mapping before populating checksToAdd to get join key and column suggestions
- A draft rule returned by fetch_file_sample_data is NOT yet published — update_rule publishes it
- If ruleId is unknown: call get_rule(workspaceId, ruleName=...) to check before creating a new draft
---
PATTERN 4 — CHECKSUM RULE
Compares aggregate counts between source and target using a fixed expression: S.[SOURCE_COUNT] - T.[TARGET_COUNT] == 0
Each side gets exactly ONE column. Do NOT call analyze_recon_mapping — the check is always the same.
FIXED COLUMN per side (do not add more):
Source: { "index": 1, "name": "SOURCE_COUNT", "datatype": "INT", "icedqDatatype": "NUMERIC" }
Target: { "index": 1, "name": "TARGET_COUNT", "datatype": "INT", "icedqDatatype": "NUMERIC" }
FIXED CHECK (skip if already present on the rule):
{ "id": "RecordCheck", "type": "recordCheck", "name": "checks",
"configuration": { "generateRollUps": false, "checks": [{
"name": "Chk_001", "index": 1, "isActive": true, "isVisible": true,
"id": "chck-7a8bce06-dfce-599a-bb2c-40f013bc2a9d", "type": "Custom",
"expression": { "value": "S.[SOURCE_COUNT] - T.[TARGET_COUNT] == 0", "caseInsensitive": false },
"customFields": [{ "field": "sys_dq_dim", "values": ["Validity"] }]
}] } }
Case A: FILE (source) vs FILE (target)
1. fetch_file_sample_data(connectionId=<src file>, ruleId=null, connectionType="source", fileName=<srcFile>) → ruleId
2. fetch_file_sample_data(connectionId=<tgt file>, ruleId=<above>, connectionType="target", fileName=<tgtFile>)
3. update_rule(ruleId, checksToAdd=[<fixed check>]) → publishes
Case B: FILE (source) vs DB (target)
User provides a file (source) and a DB table name or SQL query (target).
1. fetch_file_sample_data(connectionId=<file>, ruleId=null, connectionType="source", fileName) → ruleId
2. update_rule(ruleId,
targetConfig={ connectionId:<db>, databaseName, schemaName, tableName OR sqlQuery },
checksToAdd=[<fixed check>]) → wires DB target and publishes
Case C: DB (source) vs FILE (target)
User provides a DB table name or SQL query (source) and a file (target).
1. fetch_file_sample_data(connectionId=<file>, ruleId=null, connectionType="target", fileName) → ruleId
2. update_rule(ruleId,
sourceConfig={ connectionId:<db>, databaseName, schemaName, tableName OR sqlQuery },
checksToAdd=[<fixed check>]) → wires DB source and publishes
Note: for DB side, use tableName when the user gives a table, sqlQuery when the user gives a SQL statement.
---
ERROR RECOVERY:
- "fileSchemaId missing" → call fetch_file_sample_data again with the existing ruleId and correct connectionType
- "Dataset has no connectionId" → the draft was created but file schema was not linked; re-run fetch_file_sample_data with ruleId
- "Rule not found" → verify ruleId with get_rule; if truly missing, restart from Phase 1
- For any file schema error: do NOT call update_rule until fetch_file_sample_data confirms success
+31
guidance/groovy_expressions.md
UNIVERSAL PATTERN — ALL RULE TYPES USE TRUE = PASS:
- Validation rules: TRUE = row passes, FALSE = row fails
- Recon rules: TRUE = data matches correctly, FALSE = mismatch
- Checksum rules: TRUE = values match, FALSE = values differ
COLUMN REFERENCE SYNTAX:
- Source column: S.[columnName]
- Target column: T.[columnName] (recon/checksum only)
- Square brackets required around column names
- Case-sensitive — must match exact column name from database
COMMON EXPRESSIONS:
- Positive number: S.[amount] > 0
- Non-empty string: S.[name] != null && S.[name].trim() != ""
- Date ordering: S.[start_date] <= S.[end_date]
- Allowed values: S.[status] in ["Active", "Pending", "Closed"]
- Regex pattern: S.[email] ==~ /^[\w.-]+@[\w.-]+\.[a-zA-Z]{2,}$/
- Length range: S.[code].length() >= 2 && S.[code].length() <= 10
- Conditional: S.[discount] > 0 ? S.[discount_reason] != null : true
- Null-safe comparison: (S.[col] == null && T.[col] == null) || (S.[col] != null && S.[col] == T.[col])
VALUE MAPPING (Recon):
- (S.[Gender] == "M" && T.[Gender] == "Male") || (S.[Gender] == "F" && T.[Gender] == "Female") || (S.[Gender] == T.[Gender])
- Write as positive match conditions — do NOT negate with !()
GROOVY GOTCHAS:
- Use == for equality (not ===)
- String comparison: S.[col] == "value" (not .equals())
- Null check first: S.[col] != null && S.[col].trim() != "" (trim() on null throws NPE)
- in operator works for list membership: S.[col] in ["A", "B", "C"]
- ==~ for regex matching
+31
guidance/rule_organization.md
HUMAN-IN-THE-LOOP (RECOMMENDED DEFAULT):
- Do NOT create new folders, move rules, or update existing rules unless the user explicitly approves.
- Always show the target folder path/name and confirm before applying changes.
- When naming is involved (folderName, ruleName), prefer asking the user; do not silently invent names.
FOLDER STRATEGY:
- By domain: Insurance_Rules, HR_Rules, Finance_Rules
- By environment: DEV_Rules, UAT_Rules, PROD_Rules
- By data layer: Staging_Rules, DW_Rules, Reporting_Rules
- By project: ETL_Migration_Rules, Quarterly_Audit_Rules
FOLDER NAMING RULES:
- Alphanumeric characters and underscores only
- No spaces, hyphens, or special characters
- Use underscores for word separation
RULE NAMING CONVENTIONS:
- Validation: {Table}_{Purpose}_Validation (e.g., Customer_Completeness_Validation)
- Duplicate: {Table}_{Columns}_Duplicate (e.g., Customer_Email_Duplicate)
- Pushdown: {Table}_{Check}_Pushdown (e.g., Orders_Orphan_Pushdown)
- Checksum: {Source}_vs_{Target}_Checksum (e.g., Staging_vs_DW_Customer_Checksum)
- Recon: {Source}_vs_{Target}_Recon (e.g., Staging_vs_DW_Customer_Recon)
ANTI-SPRAWL BEST PRACTICES:
- ONE validation rule per table with ALL checks combined
- Before creating: list_rules with nameFilter to check for existing rules
- Use update_rule to add checks to existing rules instead of creating new ones
- Group related rules into workflows for batch execution
MOVE OPERATIONS:
- move_rules_or_workflows: Async, returns taskInstanceId > check_task_status (use type='rule' for rules, type='workflow' for workflows)
+32
guidance/scheduling_pipelines.md
PIPELINE BUILDING WORKFLOW:
1. Create rules (validation, duplicate, recon, etc.)
2. Organize into folder: create_folder > move_rules_or_workflows (type='rule')
3. Create workflow: create_workflow with rule IDs (Sequential execution only)
4. Create schedule: create_schedule with workflow/rule ID, template, start date, timezone
5. Optionally add more rules/workflows: add_rules_workflows_to_schedule
SCHEDULE TEMPLATES:
- Onetime: Single execution at specified date/time
- Daily: Repeats every day at specified hours/minutes, with reoccur interval
- Weekly: Repeats on specified day(s) of week at specified hours/minutes
SCHEDULE PARAMETERS:
- startDate: Format "MM/DD/YYYY HH:mm:ss UTC" (e.g., "04/10/2026 08:00:00 UTC")
- endDate: Required for Daily and Weekly templates
- timeZone: IANA timezone (e.g., "America/New_York", "UTC", "Asia/Kolkata")
- template: "Onetime", "Daily", "Weekly"
- hourArray: Array of hours as strings (e.g., ["8", "14", "20"])
- minuteArray: Array of minutes as strings (e.g., ["0", "30"])
- daysOfWeek: Array of day numbers as strings (0=Sunday through 6=Saturday)
- reoccur: For Daily template — 1=once per day, 2=every 2 hours, etc.
WORKFLOW MANAGEMENT:
- create_workflow: Creates with initial rules, template must be "Sequential"
- update_workflow_rules (action="add"): Add more rules to existing workflow
- update_workflow_rules (action="remove"): Remove rules from workflow
- move_rules_or_workflows (type='workflow'): Move to different folder (async)
NAMING CONVENTIONS:
- Folders: {Domain}_{Environment}_Rules (e.g., Insurance_Staging_Rules)
- Workflows: {Domain}_{Purpose}_Workflow (e.g., Insurance_Quality_Workflow)
- Schedules: {Domain}_{Frequency}_Schedule (e.g., Insurance_Daily_Schedule)
icedq-mcp-server.js

Sorry, the diff of this file is too big to display

+11
-5
package.json
{
"name": "@icedq/mcp-server",
"version": "1.0.4",
"description": "MCP server for iceDQ Data Quality Platform — 48 tools for validation, reconciliation, duplicate detection, scheduling, and data exploration.",
"main": "dist/icedq-mcp-server.js",
"mcpName": "io.github.icedq-tools/mcp-server",
"version": "1.0.5",
"description": "MCP server for iceDQ Data Reliability Platform — 48 tools for validation, reconciliation, duplicate detection, scheduling, and data exploration.",
"repository": {
"type": "git",
"url": "git+https://github.com/icedq-tools/mcp-server.git"
},
"main": "icedq-mcp-server.js",
"type": "module",
"bin": {
"icedq-mcp-server": "dist/icedq-mcp-server.js"
"icedq-mcp-server": "icedq-mcp-server.js"
},

@@ -48,3 +53,4 @@ "scripts": {

"files": [
"dist",
"icedq-mcp-server.js",
"guidance",
"README.md",

@@ -51,0 +57,0 @@ "LICENSE",

+266
-90
README.md
<p align="center">
<img src="https://cdn-ildhhnd.nitrocdn.com/lLTTsRqXojmKENiGvwrypcTvmrbIWtKJ/assets/images/source/rev-faae778/icedq.com/wp-content/uploads/2025/01/icedq-logo.svg" alt="iceDQ Logo" width="80" />
<img src="https://raw.githubusercontent.com/icedq-tools/mcp-server/master/icon.svg" alt="iceDQ Logo" width="80" />
</p>

@@ -8,3 +8,3 @@

<p align="center">
<strong>Connect Claude Desktop to the iceDQ Data Quality Platform</strong>
<strong>Connect your AI assistant to the iceDQ Data Reliability Platform</strong>
</p>

@@ -20,5 +20,5 @@

<p align="center">
<img src="https://img.shields.io/badge/version-1.0.2-blue.svg" alt="Version" />
<img src="https://img.shields.io/badge/version-1.0.5-blue.svg" alt="Version" />
<img src="https://img.shields.io/badge/license-Apache--2.0-green.svg" alt="License" />
<img src="https://img.shields.io/badge/platform-Windows%20%7C%20macOS%20%7C%20Linux-lightgrey.svg" alt="Platform" />
<img src="https://img.shields.io/badge/platform-Windows%20%7C%20macOS-lightgrey.svg" alt="Platform" />
</p>

@@ -30,113 +30,284 @@

The iceDQ MCP Server lets you manage your entire data quality lifecycle through conversation in Claude Desktop. Ask
Claude to explore your data sources, create validation rules, run reconciliations, monitor executions, and analyze
The iceDQ MCP Server lets you manage your entire data quality lifecycle through conversation with an AI assistant.
Ask it to explore your data sources, create validation rules, run reconciliations, monitor executions, and analyze
results — no UI switching required.
**48 tools** covering the full data quality lifecycle:
**48 tools** covering the full data quality lifecycle, grouped by what they do (see
[`manifest.json`](./manifest.json) for the exact tool names and descriptions the assistant calls):
| Capability | What you can do |
|----------------------------|--------------------------------------------------------------------------------------------------|
| **Data Exploration** | Browse workspaces, connections, databases, schemas, tables, and columns |
| **Data Profiling** | Fetch real sample data and analyze quality metrics (nulls, patterns, types) |
| **AI Suggestions** | Get intelligent check recommendations based on your data profile |
| **Validation Rules** | Create row-level rules with NotNull, Format, ValidValues, Length, Date, and Custom Groovy checks |
| **Duplicate Detection** | Identify duplicates on business keys, composite keys, or conditional criteria |
| **Pushdown Rules** | SQL-driven aggregate validation (GROUP BY, JOINs, referential integrity) |
| **Checksum Rules** | Cross-source comparison (row counts, sums) between two different connections |
| **Reconciliation** | Row-level cross-source matching with AI-powered join key and column mapping |
| **Workflows** | Chain multiple rules into sequential execution workflows |
| **Schedules** | Automate rule execution with one-time, daily, or weekly schedules |
| **Execution & Monitoring** | Run rules on demand, track status, and view exception reports |
| **Organization** | Manage folders, move rules in batch, create reusable parameters |
| Category | Tools | What you can do |
|----------------------------------|:-----:|---------------------------------------------------------------------------------------------------------------|
| **Data Exploration** | 8 | Browse workspaces, connections, databases, schemas, tables, and columns; verify a connection is reachable |
| **Data Profiling & AI Suggestions** | 3 | Pull real sample rows, get null/uniqueness/pattern stats per column, and get AI-suggested checks from that profile |
| **Rule Creation** | 6 | Create any of the five rule types — including AI-suggested join keys and column mappings before building a reconciliation rule |
| **Rule Management** | 3 | Search/filter existing rules, inspect full configuration, and update checks, source/target, or join keys |
| **Workflows** | 4 | Chain rules into a workflow and adjust membership later |
| **Schedules & Automation** | 6 | Set up one-time/daily/weekly schedules, add more jobs later, trigger on demand, review run history |
| **Execution & Monitoring** | 4 | Run a rule or workflow, poll it to completion, and pull per-activity results and history |
| **Results & Exception Reporting**| 2 | Get the specific failing rows and reasons, or a link to view the report in the iceDQ UI |
| **Organization** | 5 | Organize rules/workflows into folders and track the async move operations |
| **Reusable Parameters** | 3 | Define reusable thresholds/date ranges/reference values, including bulk-loading from CSV |
| **Data Warehouse Analytics** | 3 | Ask natural-language questions about DQ history via schema-validated structured queries (no raw SQL) |
*(The 48th tool, `get_guidance`, isn't listed above — the assistant calls it internally before complex multi-step
operations; it's not something you ask for directly.)*
---
## Installation in Claude Desktop
## Compatibility
### Prerequisites
Per the [v1.0.0 release notes](https://docs.icedq.com/guides/mcp-server/releases/v1/v1.0.0):
| Requirement | Details |
|----------------------|--------------------------------------------------------------|
| **Claude Desktop** | Latest version — [download here](https://claude.ai/download) |
| **Operating System** | Windows 10+ or macOS 10.15+ |
| **iceDQ** | v7.5.0+ with a valid user account |
| Client | Support | Setup guide |
|----------------------------------|----------------|-------------------------------------------------------------------------------|
| **Claude Desktop** | ✅ MCP Bundle | [Step-by-step](#claude-desktop) |
| **VS Code + GitHub Copilot Chat** | ✅ MCP client | [Step-by-step](#vs-code--github-copilot-chat) |
| **VS Code + Claude Code** | ✅ MCP client | [Step-by-step](#vs-code--claude-code) |
| **Cursor** | ✅ MCP client | [Step-by-step](#cursor) |
| **Windows** | ✅ Tested | — |
| **macOS** | ✅ Tested | — |
| **Node.js** | 18.x or higher | Only needed if you launch via `npx` — Claude Desktop's `.mcpb` path doesn't |
> **Recommended AI model:** Claude Sonnet 4 or higher, for the most accurate rule creation and workflow understanding.
---
### Step 1 — Download the Extension
## Before You Start: Get Your iceDQ Credentials
Download the latest `icedq-mcp-server.mcpb` file from the [Releases page](https://github.com/icedq-tools/mcp-server/releases).
Every install method below needs the same values from your iceDQ instance. See the
[Credentials Guide](https://docs.icedq.com/guides/mcp-server/credentials) for exactly where to find each one in the
iceDQ UI, and the [Authentication Guide](https://docs.icedq.com/guides/mcp-server/icedq-mcp-authentication) for how
the two auth modes differ.
| Value | Env var | Required for | Example |
|----------------------|------------------------|-----------------------------|----------------------------------------|
| **Base URL** | `ICEDQ_BASE_URL` | Both modes | *No default — always your own instance URL* |
| **Realm** | `ICEDQ_REALM` | Both modes | `icedq` or `iam.icedq` |
| **Client ID** | `ICEDQ_CLIENT_ID` | Both modes | — |
| **Client Secret** | `ICEDQ_CLIENT_SECRET` | `username_password` only | — |
| **Username** | `ICEDQ_USERNAME` | `username_password` only | — |
| **Password** | `ICEDQ_PASSWORD` | `username_password` only | — |
| **Tokens file path** | `TOKENS_PATH` | `access_token` only | — |
| **Organization ID** | `ICEDQ_ORG_ID` | Both modes | `org-icedq` or `org-iam.icedq` |
Optional: `VERIFY_SSL` (default `true`; set `"false"` only for self-signed certs), `REQUEST_TIMEOUT` (default `60`
seconds), `DEBUG` (default `false`).
> **Base URL has no default.** Every organization runs its own iceDQ instance — the `https://app.icedq.net`
> value used throughout this guide's examples is illustrative only, not a shared cloud endpoint. Always replace
> it with your own instance's URL.
> **If you're launching via `npx`** (every client below except Claude Desktop's packaged extension), also set
> `NODE_OPTIONS=--use-system-ca` in the `env` block — this is in every official config example and avoids TLS errors
> on machines with a corporate root CA installed.
---
### Step 2 — Get Your iceDQ Credentials
## Installation
You need the following values from your iceDQ instance before configuring the extension:
Jump to your client:
| Value | Where to find it |
|---------------------|--------------------------------------------------------|
| **Base URL** | Your iceDQ instance URL (e.g. `https://app.icedq.net`) |
| **Realm** | Authentication realm (default: `iam.icedq`) |
| **Client ID** | Administration → Security → Client Credentials |
| **Client Secret** | Administration → Security → Client Credentials |
| **Username** | Your iceDQ login email |
| **Password** | Your iceDQ login password |
| **Organization ID** | Visible in any rule's metadata (e.g. `org-iam.icedq`) |
- [Claude Desktop](#claude-desktop)
- [VS Code + GitHub Copilot Chat](#vs-code--github-copilot-chat)
- [VS Code + Claude Code](#vs-code--claude-code)
- [Cursor](#cursor)
Each section below is a condensed quick-start. For the full walkthrough with screenshots and troubleshooting, follow
the linked guide on docs.icedq.com.
### Claude Desktop
📖 **Full guide:** [Setup in Claude Desktop](https://docs.icedq.com/guides/mcp-server/setup-in-claude-desktop)
Two paths — **Path A (recommended)** installs a packaged `.mcpb` extension with a settings form and stores your
password in your OS keychain; **Path B** hand-edits a config file and needs Node.js 18+.
**Path A:** Download the `.mcpb` from the [Releases page](https://github.com/icedq-tools/mcp-server/releases), then
in Claude Desktop go to **Settings → Extensions → Install Extension** and select the file. Fill in the credentials
form that appears and click **Save**.
**Path B:** Edit `claude_desktop_config.json` (**Windows:** `%APPDATA%\Claude\claude_desktop_config.json`,
**macOS:** `~/Library/Application Support/Claude/claude_desktop_config.json`):
```json
{
"mcpServers": {
"icedq": {
"command": "npx",
"args": ["-y", "@icedq/mcp-server"],
"env": {
"ICEDQ_BASE_URL": "https://app.icedq.net",
"ICEDQ_REALM": "icedq",
"ICEDQ_CLIENT_ID": "your-client-id",
"ICEDQ_CLIENT_SECRET": "your-client-secret",
"AUTH_TYPE": "username_password",
"ICEDQ_USERNAME": "your-username",
"ICEDQ_PASSWORD": "your-password",
"ICEDQ_ORG_ID": "your-org-id"
}
}
}
}
```
Fully quit and reopen Claude Desktop (closing the window isn't enough), then verify with `List my iceDQ workspaces`.
---
### Step 3 — Install the `.mcpb` Extension
### VS Code + GitHub Copilot Chat
#### Windows
📖 **Full guide:** [Setup in VS Code & Cursor](https://docs.icedq.com/guides/mcp-server/setup-in-vs-code-and-cursor#configure-vs-code)
1. Open **Claude Desktop**
2. Click the **menu icon** (top-left) → **Settings**
3. Go to the **Extensions** tab
4. Click **Install Extension**
5. Browse to and select `icedq-mcp-server.mcpb`
6. Click **Open** — Claude Desktop installs the extension
Requires Node.js 18+ and the GitHub Copilot Chat extension, installed and signed in.
#### macOS
1. Command Palette (`Ctrl+Shift+P` / `Cmd+Shift+P`) → **MCP: Open user configuration** → opens `mcp.json`.
2. Add:
1. Open **Claude Desktop**
2. Click **Claude** in the menu bar → **Settings**
3. Go to the **Extensions** tab
4. Click **Install Extension**
5. Browse to and select `icedq-mcp-server.mcpb`
6. Click **Open** — Claude Desktop installs the extension
```json
{
"servers": {
"icedq": {
"command": "npx",
"args": ["-y", "@icedq/mcp-server"],
"env": {
"ICEDQ_BASE_URL": "https://app.icedq.net",
"ICEDQ_REALM": "icedq",
"ICEDQ_CLIENT_ID": "your-client-id",
"ICEDQ_CLIENT_SECRET": "your-client-secret",
"AUTH_TYPE": "username_password",
"ICEDQ_USERNAME": "your-username",
"ICEDQ_PASSWORD": "your-password",
"ICEDQ_ORG_ID": "your-org-id",
"NODE_OPTIONS": "--use-system-ca"
}
}
}
}
```
3. Save (`Ctrl+S`/`Cmd+S`) — VS Code shows a **Start** option next to the `icedq` entry. Click it.
4. Verify in Copilot Chat (`Ctrl+Alt+I`/`Cmd+Ctrl+I`): `List my iceDQ workspaces`.
> ⚠️ This file stores your password in plain text. Don't commit `.vscode/mcp.json` to git if you're using
> workspace-scoped settings.
---
### Step 4 — Configure Your Credentials
### VS Code + Claude Code
After installation, Claude Desktop will prompt you to configure the extension:
📖 **Full guide:** [Setup with Claude Code](https://docs.icedq.com/guides/mcp-server/setup-with-claude-code)
1. In **Settings → Extensions**, find **iceDQ Data Quality Platform** and click **Configure**
2. Fill in the fields:
Use this instead of the Copilot Chat guide if you have a Claude subscription (Pro/Max/Team/Enterprise) or an
Anthropic API key rather than a Copilot subscription. Requires Node.js 18+ and the **Claude Code for VS Code**
extension published by **Anthropic** (`anthropic.claude-code`) — skip third-party wrappers.
| Field | Value |
|---------------------|--------------------------------------------------|
| **iceDQ Base URL** | Your instance URL (e.g. `https://app.icedq.net`) |
| **Realm Name** | `iam.icedq` (or your custom realm) |
| **Client ID** | Your OAuth client ID |
| **Client Secret** | Your OAuth client secret |
| **Username** | Your iceDQ username |
| **Password** | Your iceDQ password |
| **Organization ID** | Your org ID (e.g. `org-iam.icedq`) |
**Path A — edit `.claude.json`** (home directory: **Windows** `%USERPROFILE%\.claude.json`, **macOS/Linux**
`~/.claude.json`):
3. Click **Save** and enable the extension by clicking Enable toggle.
```json
{
"mcpServers": {
"icedq": {
"command": "npx",
"args": ["-y", "@icedq/mcp-server"],
"env": {
"ICEDQ_BASE_URL": "https://app.icedq.net",
"ICEDQ_REALM": "icedq",
"ICEDQ_CLIENT_ID": "your-client-id",
"ICEDQ_CLIENT_SECRET": "your-client-secret",
"AUTH_TYPE": "username_password",
"ICEDQ_USERNAME": "your-username",
"ICEDQ_PASSWORD": "your-password",
"ICEDQ_ORG_ID": "your-org-id",
"NODE_OPTIONS": "--use-system-ca"
}
}
}
}
```
**Path B — CLI** (`npm install -g @anthropic-ai/claude-code` first if you don't have it):
```bash
claude mcp add icedq \
--scope user \
--env ICEDQ_BASE_URL=https://app.icedq.net \
--env ICEDQ_REALM=icedq \
--env ICEDQ_CLIENT_ID=<your-client-id> \
--env ICEDQ_CLIENT_SECRET=<your-client-secret> \
--env AUTH_TYPE=username_password \
--env ICEDQ_USERNAME=<your-username> \
--env ICEDQ_PASSWORD=<your-password> \
--env ICEDQ_ORG_ID=<your-org-id> \
--env NODE_OPTIONS=--use-system-ca \
npx --yes @icedq/mcp-server
```
(Windows PowerShell: use `` ` `` for line continuation instead of `\`, or put it all on one line.)
Verify either path with `claude mcp list` (should show `icedq`), reload VS Code
(**Developer: Reload Window**), then ask in the Claude Code panel: `List my iceDQ workspaces`.
---
### Step 5 — Verify the Installation
### Cursor
1. Start a new conversation in Claude Desktop
2. Type: `List my iceDQ workspaces`
3. Claude should respond with your workspace names and IDs
📖 **Full guide:** [Setup in VS Code & Cursor](https://docs.icedq.com/guides/mcp-server/setup-in-vs-code-and-cursor#configure-cursor)
If it works — you're ready to go.
Cursor has built-in MCP support — no extra extension needed. Requires Node.js 18+.
1. Settings (gear icon, or `Cmd+,`/`Ctrl+,`) → search **Tools & MCP** → **Add Custom MCP** → opens `mcp.json`
(**macOS:** `~/.cursor/mcp.json`, **Windows:** `%USERPROFILE%\.cursor\mcp.json`).
2. Add:
```json
{
"mcpServers": {
"icedq": {
"command": "npx",
"args": ["-y", "@icedq/mcp-server"],
"env": {
"ICEDQ_BASE_URL": "https://app.icedq.net",
"ICEDQ_REALM": "icedq",
"ICEDQ_CLIENT_ID": "your-client-id",
"ICEDQ_CLIENT_SECRET": "your-client-secret",
"AUTH_TYPE": "username_password",
"ICEDQ_USERNAME": "your-username",
"ICEDQ_PASSWORD": "your-password",
"ICEDQ_ORG_ID": "your-org-id",
"NODE_OPTIONS": "--use-system-ca"
}
}
}
}
```
3. Save, go back to **Settings → Tools & MCP**, and enable the toggle next to **icedq** — status should show
**Active**.
4. Verify in Cursor chat (`Cmd+L`/`Ctrl+L`): `List my iceDQ workspaces`.
---
### Access token mode (all clients)
If you'd rather not store a password in a config file, every client above also supports **access token** mode —
swap the `username_password` fields for:
```json
"env": {
"ICEDQ_BASE_URL": "https://app.icedq.net",
"ICEDQ_REALM": "icedq",
"ICEDQ_CLIENT_ID": "your-oauth-client-id",
"AUTH_TYPE": "access_token",
"TOKENS_PATH": "/full/path/to/icedq-tokens.json",
"ICEDQ_ORG_ID": "your-org-id"
}
```
Generate the token file from iceDQ's **Profile → Token Generation**. See the
[Authentication Guide](https://docs.icedq.com/guides/mcp-server/icedq-mcp-authentication#access-token-mode) for the
full walkthrough — the connector refreshes the token automatically and writes the new pair back to the same file.
---
## Usage Examples

@@ -162,2 +333,5 @@

**Analytics:**
> "Show me the top 5 rules that failed most often last week"
---

@@ -167,16 +341,15 @@

| Issue | Solution |
|-----------------------------|------------------------------------------------------------|
| **Extension not appearing** | Restart Claude Desktop after installation |
| **Authentication failed** | Verify Client ID, Client Secret, username, and password |
| **No workspaces returned** | Check Base URL and ensure your user has workspace access |
| **SSL certificate error** | Set **Verify SSL** to `false` (for self-signed certs only) |
| **Tools not responding** | Enable **Debug Mode** in extension settings and check logs |
| Issue | Solution |
|-----------------------------|-------------------------------------------------------------------------------------------------------|
| **Server/extension not appearing** | Claude Desktop: fully quit and reopen. VS Code/Cursor: reload the window. Claude Code: `claude mcp list` |
| **Authentication failed** | Verify Client ID, Client Secret, username, and password by logging into iceDQ in your browser with the same values |
| **No workspaces returned** | Check `ICEDQ_ORG_ID` and confirm your user has workspace access |
| **SSL certificate error** | Set `VERIFY_SSL` to `false` (self-signed certs only — not for production) |
| **Invalid JSON** | One missing comma/quote breaks the config — validate at [jsonlint.com](https://jsonlint.com/) |
| **npm download blocked by corporate proxy** | `npm install -g @icedq/mcp-server`, then set `"command": "icedq-mcp-server"` with empty `args` |
### Debug Logs
Each client's full guide (linked above) has an exhaustive troubleshooting section, including exact Debug Mode steps
and log locations for that client.
Enable **Debug Mode** in Settings → Extensions → iceDQ → Configure.
Log file locations:
Claude Desktop log file locations:
- **Windows:** `%APPDATA%\Claude\Logs\extensions\`

@@ -189,7 +362,10 @@ - **macOS:** `~/Library/Logs/Claude/extensions/`

- Credentials are stored in your OS keychain — never on external servers
- Claude Desktop's packaged extension (Path A) stores your password in your OS keychain (Windows Credential
Manager / macOS Keychain). Every other setup path — Claude Desktop Path B, VS Code, Claude Code, and Cursor —
stores credentials in plain text in that client's config file. Don't commit those files to version control.
- All communication uses HTTPS with OAuth 2.0
- Data flows directly between Claude Desktop and your iceDQ instance — no third parties
- Data flows directly between your AI client and your iceDQ instance — no third parties, no vendor-hosted relay
- No telemetry or tracking of any kind
- No data persistence beyond the active session
- Use a separate OAuth client per user, and prefer `access_token` mode on shared machines

@@ -211,5 +387,5 @@ Full details: [https://icedq.com/privacy-policy](https://icedq.com/privacy-policy)

<p align="center">
<strong>iceDQ Data Quality Platform</strong><br/>
<strong>iceDQ Data Reliability Platform</strong><br/>
<em>End-to-end data reliability, powered by AI</em><br/><br/>
<a href="https://icedq.com">icedq.com</a>
</p>
-27
dist/guidance/async_monitoring.md
ASYNC TOOL MAPPING:
| Operation | Tool | Returns | Monitor With | ID Format |
|------------------|------------------|------------------|----------------------------|------------|
| Execute rule | execute_rule | instanceId | check_workflow_run_status | integer |
| Execute schedule | execute_schedule | success | get_scheduler_runs_history | scheduleId |
| Move rules | move_rules | taskInstanceId | check_task_status | tins-xxx |
| Move workflows | move_workflows | taskInstanceId | check_task_status | tins-xxx |
EXECUTION MONITORING WORKFLOW:
1. execute_rule > get instanceId (integer)
2. Wait 2-3 seconds
3. check_workflow_run_status with instanceId > get status (Success/Warning/Running/Pending)
4. If completed: get_workflow_run_result with same instanceId > get activity details
5. For exception details: get_checks_exception_report with objectInstanceId from activity
MOVE MONITORING WORKFLOW:
1. move_rules > get taskInstanceId (tins-xxx)
2. Wait 2-3 seconds
3. check_task_status with taskInstanceId > get status (Completed/Running/Failed/Pending)
TIMING GUIDANCE:
- Small rules (< 10K rows): 2-5 seconds
- Medium rules (10K-100K rows): 5-15 seconds
- Large rules (100K+ rows): 15-60 seconds
- Rule moves: 1-3 seconds regardless of size
AUTO-PUBLISH: All rules created or updated via MCP tools are automatically published and immediately ready to execute. Do NOT tell user to publish from iceDQ UI.
-47
dist/guidance/create_checksum_rules.md
HUMAN-IN-THE-LOOP (RECOMMENDED DEFAULT):
- Checksum rules compare TWO sides (source vs target). This workflow must be approval-gated.
- Do NOT auto-pick source/target connections, tables, or SQL when multiple options exist.
APPROVAL GATES (do these in order and WAIT after each):
1) Workspace selection
- list_workspaces → present options → user selects workspaceId
2) Source + target connection selection
- list_connections(workspaceId) → show ACTIVE only → user selects sourceConnectionId and targetConnectionId
3) Folder selection
- list_folders(workspaceId, optional nameFilter) → user selects folderId
4) Mode selection (Table vs SQL) + target approval
- Table mode: user selects database/schema/table for BOTH source and target (list_databases → list_schemas → list_tables)
- SQL mode: user provides sourceSql + targetSql; confirm each returns exactly 1 row, 1 numeric column with alias
5) Check expression / tolerance approval
- If a custom tolerance or percentage logic is needed, confirm the intended pass condition with user (TRUE = PASS).
6) Rule name approval
- Ask the user for ruleName. Do NOT auto-generate.
7) Create rule
- create_checksum_rule with approved source/target definitions
8) Optional execution (separate approval)
- Only run execute_rule if user explicitly says to execute now.
WORKFLOW:
1. Identify source and target connections: Can be same or different platforms (e.g., SQL Server > Snowflake)
2. Decide mode:
- Table mode: Provide sourceSchema+sourceTable and targetSchema+targetTable > auto-generates COUNT(*) SQL
- SQL mode: Provide sourceSql and targetSql for SUM, AVG, or complex aggregates
3. Create rule: create_checksum_rule
4. Execute and check results
CHECK EXPRESSION PATTERN (TRUE = PASS):
- Default: S.[SOURCE_COUNT] - T.[TARGET_COUNT] == 0 > pass when counts match
- Tolerance: Math.abs(S.[SRC] - T.[TGT]) <= 10 > pass within 10 records
- Percentage: Math.abs(S.[SRC] - T.[TGT]) / S.[SRC] * 100 <= 1 > pass within 1%
REQUIREMENTS:
- Each SQL must return exactly 1 row, 1 numeric column
- Column must have an alias (SOURCE_COUNT, TARGET_COUNT, or custom)
- Source and target aliases must be different
NAMING CONVENTION: {SourceTable}_vs_{TargetTable}_Checksum
USE CASES:
- Row count validation after ETL load
- Sum validation (total premium, total claims)
- Cross-platform comparison (SQL Server vs Snowflake row counts)
-44
dist/guidance/create_duplicate_rules.md
HUMAN-IN-THE-LOOP (RECOMMENDED DEFAULT):
- This workflow is approval-gated. If the user does NOT provide explicit IDs/names for each choice, you MUST stop and ask.
- Do NOT auto-pick defaults when multiple ACTIVE connections or multiple candidate tables/columns exist.
APPROVAL GATES (do these in order and WAIT after each):
1) Workspace selection
- list_workspaces → present options → user selects workspaceId
2) Connection selection
- list_connections(workspaceId) → show ACTIVE only → user selects connectionId
3) Folder selection
- list_folders(workspaceId, optional nameFilter) → user selects folderId
4) Target selection (Table or Custom SQL)
- Table mode: list_databases → list_schemas → list_tables → user selects databaseName/schemaName/tableName
- SQL mode: user provides customSql; confirm it returns the duplicateColumns
5) Duplicate columns selection (required user choice)
- list_columns → propose candidate business keys → user confirms duplicateColumns
6) Rule name approval
- Ask the user for ruleName. Do NOT auto-generate.
7) Create rule
- create_duplicate_rule with the approved duplicateColumns
8) Optional execution (separate approval)
- Only run execute_rule if user explicitly says to execute now.
WORKFLOW:
1. Identify table and candidate key columns
2. Check platform: Snowflake/BigQuery/Redshift do NOT enforce PKs — always worth checking
PostgreSQL/MySQL/Oracle/SQL Server enforce PKs — skip PK columns, focus on business keys
3. Choose mode:
- Table mode: schemaName + tableName + duplicateColumns
- SQL mode: customSql + duplicateColumns (for filtered subsets or joins)
4. Create: create_duplicate_rule
5. Execute: execute_rule > check results
6. Review exceptions: get_checks_exception_report shows duplicate records with DUPLICATE_COUNT
COLUMN SELECTION:
- Single column: ["Email"] — checks individual uniqueness
- Multi-column: ["FirstName", "LastName", "DateOfBirth"] — checks composite uniqueness
- Business keys vs PKs: Prioritize business keys (email, SSN, account number) over surrogate PKs
NAMING CONVENTION: {Table}_{Columns}_Duplicate_Check
WHEN NOT TO USE:
- Database enforces PK/unique constraint on the columns — check is redundant
- Need fuzzy matching (similar but not exact) — not supported, use pushdown with custom SQL instead
-41
dist/guidance/create_pushdown_rules.md
HUMAN-IN-THE-LOOP (RECOMMENDED DEFAULT):
- Pushdown rules are powerful and can be costly; treat them as approval-gated.
- Do NOT run or modify SQL without user confirmation of the exact target connection and SQL text.
- Do NOT auto-pick defaults when multiple ACTIVE connections exist.
APPROVAL GATES (do these in order and WAIT after each):
1) Workspace selection
- list_workspaces → present options → user selects workspaceId
2) Connection selection
- list_connections(workspaceId) → show ACTIVE only → user selects connectionId
3) Folder selection
- list_folders(workspaceId, optional nameFilter) → user selects folderId
4) SQL approval (required)
- User provides SQL that returns ONLY failing rows (0 rows = pass)
- Echo back the SQL + explain expected failure semantics → user approves
5) Rule name approval
- Ask the user for ruleName. Do NOT auto-generate.
6) Create rule
- create_pushdown_rule with the approved SQL
7) Optional execution (separate approval)
- Only run execute_rule if user explicitly says to execute now.
WORKFLOW:
1. Write SQL that returns ONLY bad records (0 rows = pass, N rows = fail)
2. Create: create_pushdown_rule with the SQL
3. Execute: execute_rule — exit code = number of failure rows
SQL PATTERN — RETURN BAD RECORDS:
- Duplicates: SELECT col, COUNT(*) FROM table GROUP BY col HAVING COUNT(*) > 1
- Orphans: SELECT c.id FROM child c LEFT JOIN parent p ON c.parent_id = p.id WHERE p.id IS NULL
- Stale data: SELECT * FROM table WHERE updated_date < DATEADD(day, -7, GETDATE())
- Threshold: SELECT 'FAIL' WHERE (SELECT COUNT(*) FROM table) < 1000
- SCD2: SELECT * FROM dim WHERE end_date IS NULL GROUP BY business_key HAVING COUNT(*) > 1
WHEN TO USE (vs other rule types):
- Cross-table JOINs > pushdown (not validation)
- GROUP BY / HAVING > pushdown (not validation)
- Referential integrity > pushdown
- Simple column checks (NotNull, format) > validation (not pushdown)
- Row count comparison > checksum (not pushdown)
- Row-by-row column comparison > recon (not pushdown)
-63
dist/guidance/create_recon_rules.md
HUMAN-IN-THE-LOOP (RECOMMENDED DEFAULT):
- Recon rules compare row-level data across TWO sides (source vs target). This workflow must be approval-gated.
- Do NOT auto-pick source/target connections, tables, join keys, or mapped columns when multiple options exist.
APPROVAL GATES (do these in order and WAIT after each):
1) Workspace selection
- list_workspaces → present options → user selects workspaceId
2) Source + target connection selection
- list_connections(workspaceId) → show ACTIVE only → user selects sourceConnectionId and targetConnectionId
3) Folder selection
- list_folders(workspaceId, optional nameFilter) → user selects folderId
4) Target selection (per side)
- list_databases → list_schemas → list_tables for source → user selects database/schema/table
- list_databases → list_schemas → list_tables for target → user selects database/schema/table
5) Mapping analysis + approval (required)
- analyze_recon_mapping → present HIGH/MEDIUM/LOW matches
- User confirms join key columns and which column checks to include
6) Result types + sort mode approval
- Confirm which resultTypes to include: a-b (source orphans), b-a (target orphans), Xp (column diffs)
7) Rule name approval
- Ask the user for ruleName. Do NOT auto-generate.
8) Create rule
- create_recon_rule with approved join key and check columns as comma-separated strings
9) Optional execution (separate approval)
- Only run execute_rule if user explicitly says to execute now.
WORKFLOW:
1. Identify source and target: Get both connectionIds from list_connections
2. Analyze mapping: Call analyze_recon_mapping with source/target connection+schema+table details
- Returns column matches (HIGH/MEDIUM/LOW confidence), suggested join keys, unmatched columns
3. Review suggestions: Present the mapping analysis to user
- HIGH confidence = exact name match > auto-include
- MEDIUM = similar names > ask user to confirm
- Unmatched columns may indicate transformations (e.g., FirstName+LastName > FullName)
4. Identify join keys: If analyze_recon_mapping didn't suggest keys, ask user for the business key columns
5. Create rule: create_recon_rule with comma-separated join key and check column strings
- joinKeySourceColumns: "employee_id", joinKeyTargetColumns: "empid"
- checkSourceColumns: "first_name,salary", checkTargetColumns: "name,salary"
6. Execute: execute_rule > check_workflow_run_status > get_workflow_run_result
7. Review exceptions: get_checks_exception_report — look at difftype column:
- ANB = source orphan (exists in source, not in target)
- BNA = target orphan (exists in target, not in source)
- Check columns show "true"/"false" per row
CUSTOM CHECK PATTERN (MATCH PATTERN — TRUE = PASS):
- Recon custom checks use TRUE = PASS (same as validation)
- Write expressions that return TRUE when data is CORRECT
- Do NOT negate with !() — that inverts pass/fail
- Simple: {name: "Email", sourceColumn: "Email", targetColumn: "Email"} > generates S.[Email] == T.[Email]
- Custom value mapping example:
(S.[Gender] == "M" && T.[Gender] == "Male") || (S.[Gender] == "F" && T.[Gender] == "Female") || (S.[Gender] == T.[Gender])
RESULT TYPES:
- a-b: Orphaned source rows (in source but not target)
- b-a: Orphaned target rows (in target but not source)
- Xp: Column mismatches (rows that matched on join key but have value differences)
NAMING CONVENTION: {SourceTable}_vs_{TargetTable}_Recon
COMMON ISSUES:
- High orphan count (a-b) usually means target hasn't been fully loaded, not a data quality issue
- Gender/Status/Code mismatches usually indicate value mapping transformations — use Custom expression
- Date format differences — source may store as string, target as date — compare with string conversion
-60
dist/guidance/create_validation_rules.md
HUMAN-IN-THE-LOOP (RECOMMENDED DEFAULT):
- This workflow is intended to be approval-gated. If the user does NOT provide explicit IDs/names for each choice, you MUST stop and ask.
- Do NOT auto-pick defaults when multiple options exist.
APPROVAL GATES (do these in order and WAIT after each):
1) Workspace selection
- list_workspaces → present options → user selects workspaceId
2) Connection selection
- list_connections(workspaceId) → show ACTIVE only → user selects connectionId
3) Folder selection
- list_folders(workspaceId, optional nameFilter) → user selects folderId
- If folder does not exist: call get_guidance('rule_organization'), propose folderName + parent folder, then ONLY create_folder after user approves.
4) Target selection (Table or Custom SQL)
- Table mode: list_databases → list_schemas → list_tables → user selects databaseName/schemaName/tableName
- Custom SQL mode: user provides customSql; confirm it returns required columns for checks
5) Checks approval
- fetch_sample_data → profile_data → suggest_quality_checks
- Present the suggested checks (and any manual edits) and WAIT for user approval (include/exclude/modify).
6) Rule name approval
- Ask the user for ruleName. Do NOT auto-generate a ruleName.
7) Create rule
- create_validation_rule with ONE combined checks array for the selected table.
8) Optional execution (separate approval)
- Only run execute_rule if user explicitly says to execute now.
WORKFLOW:
1. Identify target: Get connectionId (list_connections), then navigate database > schema > table (list_databases > list_schemas > list_tables)
2. Get column metadata: list_columns to understand datatypes, PKs, nullability
3. Sample data: fetch_sample_data with databaseName="" for Azure SQL
4. Profile: profile_data with the sample data array
5. Get suggestions: suggest_quality_checks with the profile output
6. Review: Present suggested checks to user before creating
7. Create: Use create_validation_rule — combine ALL checks for the same table into ONE rule (avoid rule sprawl)
8. Execute: execute_rule > check_workflow_run_status > get_workflow_run_result
9. Review exceptions: get_checks_exception_report for row-level failure details
GROOVY EXPRESSION PATTERN (Custom checks):
- All custom checks use TRUE = PASS, FALSE = FAIL
- Write expressions that describe VALID data conditions
- Examples:
S.[salary] > 0 > salary must be positive
S.[email] != null && S.[email].trim() != "" > email must not be empty
S.[age] >= 18 && S.[age] <= 120 > age must be in range
S.[start_date] <= S.[end_date] > dates must be in order
S.[status] in ["Active","Pending","Closed"] > status must be valid
SUPPORTED CHECK TYPES:
- NotNull: {checkType: "NotNull", column: "col"}
- ValidValues: {checkType: "ValidValues", column: "col", expectedValues: ["A","B"]}
- Format: {checkType: "Format", column: "col", pattern: "Email|Phone|SSN|ZipCode|URL|IP"}
- Length: {checkType: "Length", column: "col", expectedLength: 10, operator: "equal to"}
- Date: {checkType: "Date", column: "col", dateFormat: "yyyy-MM-dd"}
- Custom: {checkType: "Custom", column: "col", expression: "S.[col] > 0"}
ANTI-PATTERNS:
- Do NOT create separate rules per check — combine into ONE rule per table
- Do NOT use NotNull + Custom for same column — use Custom alone with null handling
- Do NOT use create_validation_rule for: duplicates (use create_duplicate_rule), cross-table (use create_pushdown_rule or create_recon_rule), row counts (use create_checksum_rule)
AZURE SQL QUIRK: Use databaseName="" (empty string) for fetch_sample_data, not the actual database name
-29
dist/guidance/data_profiling_workflow.md
HUMAN-IN-THE-LOOP (RECOMMENDED DEFAULT):
- Profiling should only run AFTER the user has confirmed the exact workspace + connection + table (or customSql).
- If multiple choices exist (workspaces, connections, folders, schemas, tables), present options and WAIT for the user to select.
- Before executing any SQL-based sampling, confirm the target (database/schema/table or customSql) with the user.
PROFILING WORKFLOW:
1. fetch_sample_data: Get sample rows from table
- Azure SQL: Use databaseName="" (empty string), not actual database name
- Standard mode: schemaName + tableName
- Custom SQL mode: customSql for filtered/joined data
2. profile_data: Pass the data array from fetch_sample_data response
- Returns per-column: nullCount, nullPercentage, uniqueCount, dataType, isPotentialKey
3. suggest_quality_checks: Pass the profile output
- Returns suggested checks with priority (high/medium/low)
- Clean data (zero nulls) returns empty suggestions — this is expected
APPROVAL GATE (required before rule creation):
- Always present the suggested checks to the user and WAIT for approval (include/exclude/modify) before calling any rule-creation tool.
INTERPRETING PROFILE RESULTS:
- nullPercentage > 0: Column has missing data > NotNull check candidate
- uniqueCount == rowCount: Potential primary key > Duplicate check candidate
- uniqueCount very low: Low cardinality > ValidValues check candidate
- isPotentialKey == true: Column may be a natural key
FOR RICHER PROFILING:
- Use tables with known data issues (nulls, duplicates) for meaningful results
- Clean tables will return empty suggestions — this is correct behavior
- For column-level metadata: list_columns returns datatype, length, isPrimaryKey
-142
dist/guidance/datawarehouse_queries.md
## WHEN TO USE DATAWAREHOUSE TOOLS
✅ USE for analytical/aggregate queries (triggers: summary, report, analysis, trend, count by, group by, top N, average, breakdown, comparison, KPI, dashboard):
- "Give analysis report of recon/validation/duplicate rule" (any rule TYPE)
- "How many rules failed last week grouped by folder/ruleType?"
- "Top 10 rules by failure count in last 30 days"
- "Pass/fail trend per day" / "average execution time over time"
- Check-level aggregates: filter `executable_type = 'check'` in execution_fct
❌ DO NOT USE for:
- Flat list / single id lookup → use list_* tools (list_workspaces, list_rules, etc.)
- Row-level failures of specific rule/run → use exception_report_analysis tools
CRITICAL: "analysis report of [rule TYPE]" = datawarehouse | "exception report for [specific RULE NAME]" = exception tools
## MANDATORY 3-STEP WORKFLOW
NEVER skip steps. NEVER guess field names.
1. **datawarehouse_query_schema** — get dataset/column/metric/join/operator names
2. **validate_and_explain_structured** — dry-run (returns SQL, no execution)
3. **datawarehouse_query_executor** — execute and return rows
RULE: If user request is ambiguous → show schema options and WAIT for confirmation.
RULE: NO raw SQL. All fields must match schema exactly.
## PAYLOAD STRUCTURE
All fields sourced from datawarehouse_query_schema:
**dataset** (required): Dataset key from schema (e.g., execution_fct, object_dim)
**dimensions**: Pass-through columns. With metrics → included in GROUP BY
- Example: ["ruleType", "folderName", "status"]
**metrics**: Aggregations. MUST have {column, agg, alias}
- agg: count, sum, avg, min, max
- alias: required (used in order_by)
- Example: [{"column": "ruleRunId", "agg": "count", "alias": "runCount"}]
**filters**: Row filters. Each: {column, op, value}
- Operators: eq, neq, gt, gte, lt, lte, in, like
- Example: [{"column": "status", "op": "eq", "value": "Failed"}]
**time_column + time_window_days**: Both required together
- time_column must have is_time_col=true in schema
- time_window_days: 1-365 (default 30)
- Looks back N days from NOW
**order_by**: [{column, direction}] — column = dimension, metric alias, or derived_column
**limit**: 1-500 (default 100) — use small limit + order_by for top-N
**joins**: Named joins from schema. [{join_name}] — do NOT invent syntax
**derived_columns**: Named expressions from schema — reference BY NAME only
## EXAMPLES
### 1. Count by dimension (group-by)
```json
{
"dataset": "execution_fct",
"dimensions": ["ruleType"],
"metrics": [{"column": "ruleRunId", "agg": "count", "alias": "runCount"}],
"time_column": "runStartTime",
"time_window_days": 7,
"order_by": [{"column": "runCount", "direction": "desc"}],
"limit": 50
}
```
### 2. Top N rules by failure count
```json
{
"dataset": "execution_fct",
"dimensions": ["ruleName", "folderName"],
"metrics": [{"column": "failureCount", "agg": "sum", "alias": "totalFailures"}],
"filters": [{"column": "status", "op": "eq", "value": "Failed"}],
"time_window_days": 30,
"order_by": [{"column": "totalFailures", "direction": "desc"}],
"limit": 10
}
```
### 3. Daily trend
```json
{
"dataset": "execution_fct",
"dimensions": ["runDate"],
"metrics": [
{"column": "ruleRunId", "agg": "count", "alias": "runs"},
{"column": "failureCount", "agg": "sum", "alias": "failures"}
],
"time_column": "runStartTime",
"time_window_days": 14,
"order_by": [{"column": "runDate", "direction": "asc"}]
}
```
### 4. Check-level analysis (executable_type filter)
```json
{
"dataset": "execution_fct",
"dimensions": ["parent_instance_id", "executable_id"],
"metrics": [
{"column": "instance_id", "agg": "count", "alias": "checks"},
{"column": "failure_count", "agg": "sum", "alias": "failures"}
],
"filters": [{"column": "executable_type", "op": "eq", "value": "check"}],
"time_window_days": 30
}
```
### 5. Filtered pass-through (no aggregation)
```json
{
"dataset": "object_dim",
"dimensions": ["ruleId", "ruleName", "ruleType"],
"filters": [{"column": "ruleType", "op": "in", "value": ["Validation", "Duplicate"]}],
"limit": 100
}
```
## ANTI-PATTERNS — DO NOT
❌ Invent dataset/column/join names → use exact names from schema
❌ Pass raw SQL → tools reject it
❌ Metrics without alias → order_by breaks
❌ time_window_days without time_column → filter has no target
❌ Large limit for top-N → use order_by + small limit
❌ Skip validate_and_explain_structured → non-trivial queries fail
❌ Use list_* tools for aggregates → they cannot group/count/trend
## COMMON ERRORS
**"Unknown dataset"** → dataset key doesn't match schema → re-run datawarehouse_query_schema, copy exact key
**"Unknown column"** → column doesn't exist → check spelling/case in schema, or add join
**"Unsupported operator"** → op not allowed for column type → use operators from schema
**"Missing alias on metric"** → every metric MUST have alias
**"time_window_days without time_column"** → both required together
**Empty result** → widen time_window_days, relax filters, or confirm data exists in period
-44
dist/guidance/exception_report_analysis.md
STEP 0 — ASK THE USER FIRST (MANDATORY):
When a user asks for an exception report, ALWAYS ask before calling any tool:
"Would you like me to show the exception report here in the chat, or shall I share the download URL where you can view the full detailed exception report of each check in the iceDQ UI?"
- User wants it in chat → follow the RETRIEVING EXCEPTION REPORTS flow below → call get_checks_exception_report
- User wants the download URL → call get_exception_report_url instead
MULTIPLE RUN INSTANCES — ALWAYS ASK:
Before calling get_exception_report_url or get_checks_exception_report, fetch the run history via get_rule_workflow_run_history (for rules) or get_workflow_run_result (for workflows). If more than one completed instance exists, present the list with run date and status and ask the user which instance they want the exception report for. Do NOT auto-select the latest.
RETRIEVING EXCEPTION REPORTS:
1. From rule name: list_rules (find ruleId) > get_rule (find ruleType and objectId) > get_rule_workflow_run_history (list instances, ask user to pick if >1) > get_checks_exception_report OR get_exception_report_url
2. From execution: execute_rule (get instanceId) > get_workflow_run_result (find activity instance.id) > ask user (chat vs URL) > get_checks_exception_report OR get_exception_report_url
3. For workflow exception report: get_workflow (find workflowId and workflowName) > get_workflow_run_result (list instances, ask user to pick if >1) > get_exception_report_url (entityType="workflow")
EXPORT EXCEPTION REPORT TOOL (get_exception_report_url):
- Returns a iceDQ UI URL to view the exception report — it does NOT download a file
- For rules: requires objectId (ruleId from get_rule), instanceId, ruleType, entityType="rule"
- For workflows: requires objectId (workflowId from get_workflow), instanceId, workflowName, entityType="workflow"
- ruleType must be sent for rule exception reports (recon, validation, pushdown, checksum, duplicate)
READING THE INLINE REPORT (get_checks_exception_report):
- checks array: Per-check statistics (successCount, failureCount, errorCount)
- exceptions.data: Row-level details with column values and per-check true/false flags
- errrow: "S" = success row, "E" = exception/failure row
- difftype (recon only): "ANB" = source orphan, "BNA" = target orphan
SUPPORTED RULE TYPES:
- Validation: Shows which checks passed/failed per row
- Duplicate: Shows duplicate records with DUPLICATE_COUNT
- Recon: Shows source/target values side-by-side with per-check pass/fail
- Pushdown: Exit code only (row count = failure count), no row-level details in exception report
COMMON ANALYSIS PATTERNS:
- High failure on one check: Data quality issue in specific column
- All checks fail: Wrong table, wrong connection, or expression error
- Recon with all ANB orphans: Target not fully loaded (volume gap, not DQ issue)
- Recon with value mismatches: Value mapping needed (e.g., M > Male)
- Duplicate high count: Business key not as unique as expected
PAGINATION (get_checks_exception_report only):
- Default pageSize returns limited rows
- Use pageSize parameter to get more (e.g., pageSize=100)
- Check pageable.pages for total pages available
- After returning results, ask: "Would you like me to fetch the next page?"
-182
dist/guidance/file_based_rule_creation.md
FILE-BASED RULE CREATION — WORKFLOW GUIDE
OVERVIEW:
Rules that involve a file connection (flat-file, parquet, excel, json, xml, flat-file-sql) require a two-phase approach:
Phase 1: fetch_file_sample_data → creates a DRAFT rule wired to the file schema
Phase 2: update_rule → adds checks, join keys, and any database-side config
NEVER call create_validation_rule / create_recon_rule directly for file connections.
ALWAYS use fetch_file_sample_data first — it handles the file schema registration (fileSchemaId) that the regular create tools cannot do.
IF YOU ARE UNSURE whether a rule already exists: call get_rule first. If it exists and has a fileSchemaId, go to Phase 2 directly.
---
DELIMITER DETECTION (flat-file / delimited files only):
After fetch_file_sample_data returns, check the `columns` array BEFORE proceeding.
SIGNAL OF WRONG DELIMITER:
- Only 1 column is returned (e.g., "customer_id,first_name,last_name,email" as a single column name)
- Column names contain the actual delimiter character
- Sample data rows show all values merged into a single field
HOW TO DETECT THE CORRECT DELIMITER:
1. Look at the first few rows in the `data` array returned
2. Scan the raw row values for recurring separator characters:
- `,` (comma) — most common for .csv
- `|` (pipe) — common for .dat, .txt
- `\t` (tab) — common for .tsv, .txt
- `;` (semicolon) — common in European locale files
3. The character that appears consistently between what look like field values is the delimiter
4. If still unclear, show the user the first raw row and ask them to confirm
ACTION — re-call with correct delimiter:
fetch_file_sample_data(workspaceId, connectionId, ruleId=null, folderId, ruleType, connectionType, fileName,
additionalConfigs={ columnDelimiter: "<detected_delimiter>" })
Only flat-file (delimited) connections need this check. Parquet, Excel, JSON, XML handle structure automatically.
---
PATTERN 1 — VALIDATION RULE (File source)
Step 1 list_workspaces → user picks workspaceId
Step 2 list_connections(workspaceId) → show ACTIVE file connections → user picks connectionId
Step 3 list_files(workspaceId, connectionId) → show available files → user picks fileName
Step 4 list_folders(workspaceId) → user picks folderId
Step 5 fetch_file_sample_data(workspaceId, connectionId, ruleId=null, folderId, ruleType="Validation", connectionType="source", fileName, ...)
→ returns: ruleId (draft), columns, sample data rows
→ STOP: a draft Validation rule now exists linked to the file schema
Step 6 profile_data(sampleData) → suggest_quality_checks(profileData)
→ present suggested checks to user → wait for approval
Step 7 update_rule(workspaceId, ruleId, checksToAdd=[...approved checks...])
→ publishes the rule
---
PATTERN 2 — RECON RULE (File vs Database)
The file side is always registered first via fetch_file_sample_data.
The database side is added via update_rule.
Case A: FILE (source) vs DATABASE (target)
Step 1 Resolve workspaceId, file connectionId, db connectionId, folderId
Step 2 list_files → user picks source fileName
Step 3 fetch_file_sample_data(workspaceId, connectionId=<file>, ruleId=null, folderId,
ruleType="Recon", connectionType="source", fileName)
→ returns: ruleId (draft), source columns
Step 4 list_schemas / list_tables / list_columns on DB connection → user picks target schema + table
Step 5 update_rule(workspaceId, ruleId,
targetConfig={ connectionId:<db>, databaseName, schemaName, tableName },
joinKeys=[...], checksToAdd=[...])
→ wires the DB target and publishes
Case B: DATABASE (source) vs FILE (target)
Step 1 Resolve workspaceId, file connectionId, db connectionId, folderId
Step 2 list_files → user picks target fileName
Step 3 fetch_file_sample_data(workspaceId, connectionId=<file>, ruleId=null, folderId,
ruleType="Recon", connectionType="target", fileName)
→ returns: ruleId (draft), target columns
Step 4 list_schemas / list_tables / list_columns on DB connection → user picks source schema + table
Step 5 update_rule(workspaceId, ruleId,
sourceConfig={ connectionId:<db>, databaseName, schemaName, tableName },
joinKeys=[...], checksToAdd=[...])
→ wires the DB source and publishes
Case C: FILE (source) vs FILE (target)
Step 1 Resolve workspaceId, source file connectionId, target file connectionId, folderId
Step 2 list_files (source connection) → user picks source fileName
Step 3 fetch_file_sample_data(workspaceId, connectionId=<src file>, ruleId=null, folderId,
ruleType="Recon", connectionType="source", fileName=<srcFile>)
→ returns: ruleId (draft), source columns
Step 4 list_files (target connection) → user picks target fileName
Step 5 fetch_file_sample_data(workspaceId, connectionId=<tgt file>, ruleId=<from step 3>,
folderId=null, ruleType="Recon", connectionType="target", fileName=<tgtFile>)
→ patches the target dataset on the existing draft rule
Step 6 update_rule(workspaceId, ruleId, joinKeys=[...], checksToAdd=[...])
→ adds join keys + checks and publishes
---
PATTERN 3 — RECON RULE (Updating an existing draft)
If the user already has a draft rule (ruleId known) and wants to add/change the file side:
fetch_file_sample_data(workspaceId, connectionId=<file>, ruleId=<existing>,
connectionType="source"|"target", fileName)
→ updates the file schema on the existing rule (no new rule created)
Then update_rule as normal to add checks and publish.
---
APPROVAL GATES (wait after each):
1) Workspace — list_workspaces → user picks workspaceId
2) Connections — list_connections → user picks file connectionId (and db connectionId if mixed)
3) File — list_files → user picks fileName
4) Folder — list_folders → user picks folderId (only needed on first fetch_file_sample_data call)
5) Checks — profile_data + suggest_quality_checks → present to user → wait for approval
6) Join keys — for Recon only → present suggested join keys → user confirms
7) Rule name — ask user for ruleName if not already provided
---
KEY RULES:
- fetch_file_sample_data with ruleId=null → creates a NEW draft rule
- fetch_file_sample_data with ruleId=<existing> → updates ONLY the file dataset on the existing rule (source OR target based on connectionType)
- update_rule ONLY touches the side specified (sourceConfig updates source only, targetConfig updates target only)
- Never call update_rule with both sourceConfig and targetConfig populated for file+db rules — update only the DB side; the file side is already wired by fetch_file_sample_data
- For Recon rules: always call analyze_recon_mapping before populating checksToAdd to get join key and column suggestions
- A draft rule returned by fetch_file_sample_data is NOT yet published — update_rule publishes it
- If ruleId is unknown: call get_rule(workspaceId, ruleName=...) to check before creating a new draft
---
PATTERN 4 — CHECKSUM RULE
Compares aggregate counts between source and target using a fixed expression: S.[SOURCE_COUNT] - T.[TARGET_COUNT] == 0
Each side gets exactly ONE column. Do NOT call analyze_recon_mapping — the check is always the same.
FIXED COLUMN per side (do not add more):
Source: { "index": 1, "name": "SOURCE_COUNT", "datatype": "INT", "icedqDatatype": "NUMERIC" }
Target: { "index": 1, "name": "TARGET_COUNT", "datatype": "INT", "icedqDatatype": "NUMERIC" }
FIXED CHECK (skip if already present on the rule):
{ "id": "RecordCheck", "type": "recordCheck", "name": "checks",
"configuration": { "generateRollUps": false, "checks": [{
"name": "Chk_001", "index": 1, "isActive": true, "isVisible": true,
"id": "chck-7a8bce06-dfce-599a-bb2c-40f013bc2a9d", "type": "Custom",
"expression": { "value": "S.[SOURCE_COUNT] - T.[TARGET_COUNT] == 0", "caseInsensitive": false },
"customFields": [{ "field": "sys_dq_dim", "values": ["Validity"] }]
}] } }
Case A: FILE (source) vs FILE (target)
1. fetch_file_sample_data(connectionId=<src file>, ruleId=null, connectionType="source", fileName=<srcFile>) → ruleId
2. fetch_file_sample_data(connectionId=<tgt file>, ruleId=<above>, connectionType="target", fileName=<tgtFile>)
3. update_rule(ruleId, checksToAdd=[<fixed check>]) → publishes
Case B: FILE (source) vs DB (target)
User provides a file (source) and a DB table name or SQL query (target).
1. fetch_file_sample_data(connectionId=<file>, ruleId=null, connectionType="source", fileName) → ruleId
2. update_rule(ruleId,
targetConfig={ connectionId:<db>, databaseName, schemaName, tableName OR sqlQuery },
checksToAdd=[<fixed check>]) → wires DB target and publishes
Case C: DB (source) vs FILE (target)
User provides a DB table name or SQL query (source) and a file (target).
1. fetch_file_sample_data(connectionId=<file>, ruleId=null, connectionType="target", fileName) → ruleId
2. update_rule(ruleId,
sourceConfig={ connectionId:<db>, databaseName, schemaName, tableName OR sqlQuery },
checksToAdd=[<fixed check>]) → wires DB source and publishes
Note: for DB side, use tableName when the user gives a table, sqlQuery when the user gives a SQL statement.
---
ERROR RECOVERY:
- "fileSchemaId missing" → call fetch_file_sample_data again with the existing ruleId and correct connectionType
- "Dataset has no connectionId" → the draft was created but file schema was not linked; re-run fetch_file_sample_data with ruleId
- "Rule not found" → verify ruleId with get_rule; if truly missing, restart from Phase 1
- For any file schema error: do NOT call update_rule until fetch_file_sample_data confirms success
-31
dist/guidance/groovy_expressions.md
UNIVERSAL PATTERN — ALL RULE TYPES USE TRUE = PASS:
- Validation rules: TRUE = row passes, FALSE = row fails
- Recon rules: TRUE = data matches correctly, FALSE = mismatch
- Checksum rules: TRUE = values match, FALSE = values differ
COLUMN REFERENCE SYNTAX:
- Source column: S.[columnName]
- Target column: T.[columnName] (recon/checksum only)
- Square brackets required around column names
- Case-sensitive — must match exact column name from database
COMMON EXPRESSIONS:
- Positive number: S.[amount] > 0
- Non-empty string: S.[name] != null && S.[name].trim() != ""
- Date ordering: S.[start_date] <= S.[end_date]
- Allowed values: S.[status] in ["Active", "Pending", "Closed"]
- Regex pattern: S.[email] ==~ /^[\w.-]+@[\w.-]+\.[a-zA-Z]{2,}$/
- Length range: S.[code].length() >= 2 && S.[code].length() <= 10
- Conditional: S.[discount] > 0 ? S.[discount_reason] != null : true
- Null-safe comparison: (S.[col] == null && T.[col] == null) || (S.[col] != null && S.[col] == T.[col])
VALUE MAPPING (Recon):
- (S.[Gender] == "M" && T.[Gender] == "Male") || (S.[Gender] == "F" && T.[Gender] == "Female") || (S.[Gender] == T.[Gender])
- Write as positive match conditions — do NOT negate with !()
GROOVY GOTCHAS:
- Use == for equality (not ===)
- String comparison: S.[col] == "value" (not .equals())
- Null check first: S.[col] != null && S.[col].trim() != "" (trim() on null throws NPE)
- in operator works for list membership: S.[col] in ["A", "B", "C"]
- ==~ for regex matching
-32
dist/guidance/rule_organization.md
HUMAN-IN-THE-LOOP (RECOMMENDED DEFAULT):
- Do NOT create new folders, move rules, or update existing rules unless the user explicitly approves.
- Always show the target folder path/name and confirm before applying changes.
- When naming is involved (folderName, ruleName), prefer asking the user; do not silently invent names.
FOLDER STRATEGY:
- By domain: Insurance_Rules, HR_Rules, Finance_Rules
- By environment: DEV_Rules, UAT_Rules, PROD_Rules
- By data layer: Staging_Rules, DW_Rules, Reporting_Rules
- By project: ETL_Migration_Rules, Quarterly_Audit_Rules
FOLDER NAMING RULES:
- Alphanumeric characters and underscores only
- No spaces, hyphens, or special characters
- Use underscores for word separation
RULE NAMING CONVENTIONS:
- Validation: {Table}_{Purpose}_Validation (e.g., Customer_Completeness_Validation)
- Duplicate: {Table}_{Columns}_Duplicate (e.g., Customer_Email_Duplicate)
- Pushdown: {Table}_{Check}_Pushdown (e.g., Orders_Orphan_Pushdown)
- Checksum: {Source}_vs_{Target}_Checksum (e.g., Staging_vs_DW_Customer_Checksum)
- Recon: {Source}_vs_{Target}_Recon (e.g., Staging_vs_DW_Customer_Recon)
ANTI-SPRAWL BEST PRACTICES:
- ONE validation rule per table with ALL checks combined
- Before creating: list_rules with nameFilter to check for existing rules
- Use update_rule to add checks to existing rules instead of creating new ones
- Group related rules into workflows for batch execution
MOVE OPERATIONS:
- move_rules: Async, returns taskInstanceId > check_task_status
- move_workflows: Async > check_task_status
-32
dist/guidance/scheduling_pipelines.md
PIPELINE BUILDING WORKFLOW:
1. Create rules (validation, duplicate, recon, etc.)
2. Organize into folder: create_folder > move_rules
3. Create workflow: create_workflow with rule IDs (Sequential execution only)
4. Create schedule: create_schedule with workflow/rule ID, template, start date, timezone
5. Optionally add more rules/workflows: add_rules_workflows_to_schedule
SCHEDULE TEMPLATES:
- Onetime: Single execution at specified date/time
- Daily: Repeats every day at specified hours/minutes, with reoccur interval
- Weekly: Repeats on specified day(s) of week at specified hours/minutes
SCHEDULE PARAMETERS:
- startDate: Format "MM/DD/YYYY HH:mm:ss UTC" (e.g., "04/10/2026 08:00:00 UTC")
- endDate: Required for Daily and Weekly templates
- timeZone: IANA timezone (e.g., "America/New_York", "UTC", "Asia/Kolkata")
- template: "Onetime", "Daily", "Weekly"
- hourArray: Array of hours as strings (e.g., ["8", "14", "20"])
- minuteArray: Array of minutes as strings (e.g., ["0", "30"])
- daysOfWeek: Array of day numbers as strings (0=Sunday through 6=Saturday)
- reoccur: For Daily template — 1=once per day, 2=every 2 hours, etc.
WORKFLOW MANAGEMENT:
- create_workflow: Creates with initial rules, template must be "Sequential"
- add_rules_to_workflow: Add more rules to existing workflow
- remove_rules_from_workflow: Remove rules from workflow
- move_workflows: Move to different folder (async)
NAMING CONVENTIONS:
- Folders: {Domain}_{Environment}_Rules (e.g., Insurance_Staging_Rules)
- Workflows: {Domain}_{Purpose}_Workflow (e.g., Insurance_Quality_Workflow)
- Schedules: {Domain}_{Frequency}_Schedule (e.g., Insurance_Daily_Schedule)
dist/icedq-mcp-server.js

Sorry, the diff of this file is too big to display