Forum Discussion
Advocate IIIHarmonizing incident data from Excel, PDF, JSON and scans in Fabric: your approach?
Hi all,
While working on an AI project for a safety risk organization at DataToBiz, we ran into a data harmonization problem that I’d like to compare with how people here would build it in Fabric.
The core issue was that there was no standard format for incident reports. Every company submitted data with its own parameters, in Excel, PDF, plain text, JSON or even scanned documents. To handle it, we identified the formats and fields, separated mandatory from non-mandatory columns, and worked with domain experts to build our own mappings. Text similarity and rule-based engines then matched each company’s parameters to ours and converted the data into the target format. After that, an API filtered the incidents using a rule-based model, a BERT text classifier and a reinforcement learning component, with domain experts reviewing what to keep or exclude. The existing risk model was a heavy statistical calculation that took around 12 to 13 hours on a fresh dataset, so we replaced it with a hybrid of the experts’ rules and statistics plus machine learning.
The original build didn’t use Fabric, so here’s what I’d like to ask about doing it there.
For the schema mapping, would you use Dataflows Gen2, Spark notebooks or a config-driven mapping table? How do you handle fuzzy column matching and keep the mappings maintainable when new companies join and the experts need to review and approve them?
For the PDFs and scanned files, how do you extract structured data in Fabric? Do you call an external document extraction service from notebooks, or is there a better built-in option now?
For the human-in-the-loop filtering, how do you store the experts’ keep or exclude decisions and feed them back into retraining? Is a decisions table in the Lakehouse with notebooks and experiment tracking enough?
And for replacing a slow statistical model with ML, how do you validate that the ML version stays consistent with the original over time, and how do you manage the compute cost of the heavy runs on Spark?
If you’ve dealt with messy multi-source data like this, or tried and dropped an approach, I’d really like to know what worked.
Thanks, and I’d love to hear how you’ve handled it.
2 Replies
- v-csrikanth
Community Support
I would use a mapping table with notebooks, keeping Dataflows Gen2 for simpler, stable transformations.
- Mapping: Let fuzzy matching suggest matches, then have experts approve them. Check field meanings and units not just similar names. Store mapping versions, effective dates, and approval history, and record which version processed each incident. Keep the original files in OneLake for traceability.
- PDFs and scans: Fabric’s multimodal AI Functions, including ai.extract, can process PDFs and images, but this capability is in preview. Check regional availability, supported formats, tenant requirements, preview limits, and capacity costs. For OCR and layout details, compare it with Azure Document Intelligence called from notebooks. Test both on your actual document types.
- Expert feedback: A Lakehouse decisions table, notebooks, and MLflow are a good starting point for batch retraining. Keep the decision, reason, reviewer, timestamp, and model version without overwriting earlier decisions or deleting excluded incidents. You’ll still need a review interface and approval process. Retrain from approved labels rather than automatically treating every response as training data.
- Validation and cost: Run the old and new models side by side, checking against expert-labelled cases too matching the old model does not prove correctness. For filtering, track per-class precision, recall, and F1, especially missed severe incidents; assess numerical risk scores separately. Repeat these checks over time to catch drift.
For the heavy runs, profile the bottleneck first. Spark won’t automatically parallelize serial Python code. Reuse extraction results, process changed inputs where appropriate, and measure capacity consumption alongside runtime before adding more compute.
Regards,
Microsoft Fabric Community Support - Prince0011
Memorable Member
Hi,
Use a config-driven mapping table in the Lakehouse (company, source field, target field, status) and apply it with one Spark notebook. Fuzzy matching should only suggest matches, and experts approve them in the table.
For PDFs and scans, call Azure AI Document Intelligence from a notebook and land the output in Bronze.
Store the keep/exclude decisions in a Delta table and use them as retraining labels, tracked in MLflow.
For validation, rerun the old model on a sample every release and compare outputs.
If this helped, feel free to give it a kudos or mark it as solution, helps others find it too.
