Lab Column Mapper

Lab Column Mapper

What this skill does

Given an unknown column from an incoming lab results file, retrieve the most likely canonical destination column from a health system's clinical warehouse, explain why, and write the suggestion to the review queue.

Input payload

The skill is invoked with a JSON payload delivered by Power Automate. Every field is required unless marked optional.

| Field | Type | Description |

|---|---|---|

| source_column_name | string | The unrecognized column header from the incoming file (e.g., PT_MRN, spec_collected_dt). |

| source_column_sample_values | array of strings | Up to 5 sample values, de-identified. Never include raw PHI — the caller must hash or redact patient identifiers before invoking. |

| source_lab_id | string | Stable identifier for the sending lab (e.g., LAB-QUEST-001). |

| source_lab_name | string | Human-readable lab name (e.g., Quest Diagnostics). |

| source_file_name | string | Name of the file that failed ingestion. |

| source_domain | string | One of lab-observations, orders, specimens, results. Used to scope the search. |

| pipeline_run_id | string | ADF pipeline run ID for traceability. |

| source_column_description | string, optional | If the sending lab publishes a data dictionary, the human description of the column. Boosts match confidence significantly when present. |

Retrieval steps

Perform these steps in order. Do not skip to a broader search until the previous scope has been tried.

Step 1 — Same-lab scoped search

Query the Azure AI Search index lab-canonical-schema with:

• Search text: source_column_name + source_column_description (if present) + a synthesized query from the sample values (e.g., if samples look like dates, add "date time timestamp"; if samples look like numeric with units, add "quantity measurement value").
• Filter: source_lab_id eq '{source_lab_id}' or previously_mapped_from_labs/any(l: l eq '{source_lab_id}')
• Top: 5
• Semantic ranking: on. Use the column-descriptions semantic configuration.

If the top result has a @search.rerankerScore ≥ 2.5, accept it as the primary suggestion and go to Step 3.

Step 2 — Same-domain broader search

If Step 1 returns no result at or above the threshold, re-query with:

• Same search text.
• Filter: clinical_domain eq '{source_domain}'
• Top: 8
• Semantic ranking: on.

Accept the top result at @search.rerankerScore ≥ 2.2 as the primary suggestion. Capture the next 2 candidates as alternatives.

If no result clears the threshold, produce a no_confident_match outcome (see Step 3) and let the human decide from scratch.

Step 3 — Format the suggestion

Build a suggestion record with this schema:

{
  "suggestion_id": "<new GUID>",
  "pipeline_run_id": "<from payload>",
  "source_column_name": "<from payload>",
  "source_lab_id": "<from payload>",
  "source_lab_name": "<from payload>",
  "source_file_name": "<from payload>",
  "suggested_destination_column": "<canonical column name, or null if no_confident_match>",
  "suggested_destination_loinc": "<LOINC code if the canonical column has one, else null>",
  "confidence": "high | medium | low | no_confident_match",
  "confidence_score": <0.0 to 1.0, derived from the rerankerScore normalized>,
  "reasoning": "<one to two sentences citing WHICH signals drove the match — description match, sample-value shape, prior mapping history for this lab>",
  "alternatives": [
    { "destination_column": "<name>", "confidence_score": <0.0 to 1.0> },
    { "destination_column": "<name>", "confidence_score": <0.0 to 1.0> }
  ],
  "status": "pending_review",
  "created_at": "<ISO-8601 UTC>"
}

Confidence bands from @search.rerankerScore:

• >= 3.0 → high, confidence_score = min(1.0, rerankerScore / 4.0)
• 2.5 – 2.99 → medium
• 2.2 – 2.49 → low
• < 2.2 → no_confident_match, suggested_destination_column = null

Write to Dataverse

Call the lab_column_mapping_suggestions table's Create action via the Dataverse connector with the suggestion record. Return the new record ID to the caller so Power Automate can notify the clinical informatics steward.

Response to the caller

Return this JSON to Power Automate:

{
  "suggestion_id": "<the ID>",
  "review_url": "<Power App URL for the steward, if configured; else null>",
  "summary": "<one-line human-readable summary — 'PT_MRN → patient_mrn (high confidence)' or 'No confident match found for PT_MRN — manual review required'>"
}

Interaction guardrails

• Never invent a destination column. Only propose columns that appear in the Azure AI Search index. If nothing clears the low threshold, emit no_confident_match and let a human decide.
• Never propose a mapping that would change data type incompatibly (e.g., mapping a text column to a date column). Check the data_type field on the index result before finalizing the suggestion.
• Never include raw sample values from the payload in the reasoning field. Describe the *shape* of the values ("looks like an ISO date"), not the values themselves.
• If the payload is missing required fields, respond to Power Automate with an error object { "error": "missing_field", "field": "<name>" } and do not query the index.
• If Azure AI Search returns an error, retry once with exponential backoff (1s, then 2s). If it still fails, write a stub suggestion with status = "search_unavailable" and surface it for human review.
• Do not auto-approve. This skill only *suggests* — approval belongs to the clinical informatics steward.

References

• Canonical schema documentation and index configuration: see README.md in this skill package.
• LOINC (Logical Observation Identifiers Names and Codes): https://loinc.org/
• Azure AI Search semantic ranking: https://learn.microsoft.com/azure/search/semantic-search-overview
• Dataverse connector for Power Automate: https://learn.microsoft.com/power-automate/dataverse/overview