| name | lab-column-mapper |
| description | Use this skill when a Power Automate flow triggers the agent with a payload describing an unknown source column from a lab results file that broke an Azure Data Factory ingestion pipeline. The skill semantically matches the source column against the canonical clinical schema indexed in Azure AI Search, scoped first by the sending lab and then by clinical domain, and writes a suggested destination-column mapping to the Dataverse review queue for a clinical informatics steward to approve. Also activates on manual invocations like "map this lab column", "the ADF pipeline failed on column X, what should it map to", or "suggest a canonical column for this lab feed field". |
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": [
{ < to >
< to >
Confidence bands from @search.rerankerScore:
>= 3.0 → high, confidence_score = min(1.0, rerankerScore / 4.0)2.5 – 2.99 → medium2.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
