Integrating Insurance Sales Data Across Multiple Partners
How we implemented a reusable AWS data platform to consolidate inconsistent partner data, automate validation and support reliable business reporting.
~20 wksto production go-live and handoverTeam-reported
~163,000rows per month, 5 source layoutsDocumented design
2 partnerson one reusable architectureDelivered
Programme
~20 weeks
Team
~5 FTE
Pattern
Medallion (Bronze / Silver / Gold)
→ or swipe to begin
✦ The challenge
Where it started
An insurance services group needed to consolidate sales information from external business partners into a consistent reporting environment. The problem was not a lack of data. It was that each partner supplied information differently.
Some used REST APIs, others transferred files through SFTP. Formats included CSV, Excel, XML and PDF, often with different field names, structures and business rules.
The objective was to integrate these sources without developing and maintaining a separate end-to-end reporting pipeline for each partner, and to accommodate more partners without redesigning the architecture.
Initial project scopeDocumented design
2Initial business partners
5Different source layouts
~163,000Estimated rows per month
~20Weeks of delivery
✦ Our approach
How we tackled it
A client-defined target architecture on AWS, implemented and refined, combining multi-channel ingestion, AI-assisted data interpretation and a three-layer Medallion data architecture. How information arrives is separated from how the business consumes it.
01
Bronze: preserve the source
Amazon S3
Data from APIs or SFTP is captured as-is with its processing history. Files are tracked and de-duplicated, so failures can be investigated and rerun without asking the partner again.
02
Silver: standardise and validate
Bedrock · Textract
AI helps interpret layouts and map fields into a shared model. Business rules, duplicate detection and automated quality checks decide what is fit for reporting.
03
Gold: business-ready data
Redshift · Athena · APIs
Consolidated datasets and pre-calculated indicators. Qlik Sense reads recurring reports through Redshift, Athena serves ad-hoc analysis, REST endpoints add an integration interface.
The result is a common processing architecture that isolates source-specific complexity from reporting applications.
✦ Architecture
Conceptual architecture
Loading diagram…
A conceptual architecture, not an infrastructure deployment diagram.
Drag to pan · Ctrl/⌘ + scroll or pinch to zoom · click a block to trace its connections
✦ Technology
The stack, and why
Amazon MWAA (Airflow)Orchestrates dependencies, retries and recovery.
Amazon MSK (Kafka)Buffers incoming data and supports controlled reprocessing.
ATAWS Transfer FamilyManaged SFTP ingestion from partners.
Amazon BedrockAI-assisted field mapping across layouts.
ATAWS TextractReads PDFs and scanned partner documents.
Amazon S3Bronze layer: raw data and history preserved.
AWS GlueCatalogues datasets.
AIApache IcebergVersioned table format for reprocessing.
GEGreat ExpectationsQuality gate before publication.
Amazon RedshiftRecurring business reporting.
Amazon AthenaAd-hoc analysis.
Qlik SenseBusiness dashboards.
TerraformReproducible infrastructure.
Amazon CloudWatchMonitoring and alerting.
Hover or tap a technology to see why it is there.
All key engineering decisions
Technology / pattern
Why it matters
Amazon MWAA / Airflow
Coordinate processing dependencies, retries, recovery and workflow execution
Amazon MSK / Kafka
Buffer incoming data and support controlled reprocessing when failures occur
Amazon Bedrock + Textract
Interpret heterogeneous files and assist with field mapping
Amazon S3 + AWS Glue + Apache Iceberg
Preserve source data, catalogue datasets and support versioned processing
Great Expectations
Enforce data-quality rules before information is published
Amazon Redshift + Athena
Separate recurring business reporting from ad-hoc analytical queries
Terraform + CloudWatch
Provision reproducible infrastructure and monitor execution
✦ Technical highlight
AI-assisted mapping with explicit quality controls
The design uses AI to handle differences between partner data structures without letting AI-generated mappings bypass validation.
AI reduces repetitive interpretation work. It does not eliminate the need to verify business-critical information. Great Expectations acts as the quality gate; file-level statuses, alerts and reprocessing provide traceability when something fails.
Known layoutsExisting mappings and rules process them automatically.
VariationsRenamed or reordered fields are interpreted with AI; uncertain results need review.
New layoutsStill need configuration, schema mapping and validation first.
AlwaysDeterministic quality checks before anything is published.
The objective is not unrestricted automation. It is controlled automation that remains understandable and maintainable.
✦ Evidence · 1/2
The numbers
Delivery timelineTeam-reported
About 20 weeks including initial production go-live and handover.
What was deliveredDelivered
Per the engineering team's implementation record.
From file arrival to Gold: latency budgetModelled scenario
Illustrative midpoints of per-stage budgets (the overall budget is roughly 15–45 minutes per file). Not a production measurement.
✦ Evidence · 2/2
More numbers
Monthly cloud cost compositionModelled scenario
Midpoints of estimated ranges using list prices (about US$ 950–1,600 per month in total); they need confirmation. A fixed cost floor that is shared as more partners join.
✦ Results & impact
What we can say, and what we can't yet
The platform established reusable ingestion, transformation, validation and reporting components. Additional partners follow the same architecture with configuration and testing for their specific layouts.
Metric
Result
Evidence
Partner integrations
2 partners, 5 layouts
Delivered
Raw-data structure
6 Bronze tables
Delivered
Analytical data model
7 dimensions, 4 fact tables
Delivered
Workflow orchestration
3 Airflow DAGs
Delivered
Reporting access
Redshift, Athena and REST APIs
Delivered
Estimated onboarding of a new partner
1–2 weeks
Documented design
The 1–2 weeks per additional partner is a documented estimate, not a measured average. Quantified improvements in processing time, operating cost, manual effort and AI mapping accuracy have not yet been verified against production measurements.
✦ What this project demonstrates
Principles that travel
An approach that applies well beyond insurance: distributors, suppliers, franchise networks, financial partners and third-party platforms all send business-critical data in incompatible formats.
The architecture was driven by integration complexity and traceability, not volume. At about 163,000 rows a month a smaller serverless setup could have served a single fixed-format source; the structure pays off with multiple partners and auditable transformations.
Connect heterogeneous systems through one shared architecture
Use AI where it removes repetitive work, keep deterministic quality gates
Design for recoverability, traceability and maintenance
Separate reporting needs from source-specific complexity
How to read the badgesDeliveredTeam-reportedDocumented designMeasured · acceptance testModelled scenarioIn delivery
✦ Your turn
Every partner sends data their own way.
Send us your most stubborn spreadsheet. We will tell you what it takes to make it boring, reliable and ready for reporting, and how the next ten partners can follow it.
Amazon BedrockAI-assisted field mapping across layouts.
AWS GlueCatalogues datasets.
Amazon AthenaAd-hoc analysis.