Case study

Claims Data, End to End: Webhook to Regulator

A UK specialty insurer needed to own its claims data end to end — from a claim event landing in the cloud to the tested numbers the business and the regulator run on. I built and own the full pipeline, plus an independent reconciliation path that proves it against a vendor feed.

Architecture · webhook → regulator

1. 01

   Ingestion

   WebhooksEventBridgeSQS + DLQLambda (Python)S3DynamoDB

   Claim events arrive event-driven and buffered; a Python Lambda lands them in time-partitioned S3.

   Decision — Bucket by true event-time, not publish-time — prod-verified across ~410k events.

2. 02

   Load

   Prefect 3dltSnowflake

   Prefect and dlt load the new data into Snowflake on a 15-minute cycle.

   Decision — Typed loads, so a changed field surfaces as a reviewed schema diff instead of silent drift.

3. 03

   Transform

   dbtSnowflake

   dbt turns the raw events into a tested dimensional model — policy lifecycle, claims, regulatory reporting.

   Decision — Every model tested and gated in CI — critical report failures went from weekly to zero.

4. 04

   Reconciliation

   ECSdltdbt

   A vendor feed is streamed in and diffed against the internal models, column for column, in CI.

   Decision — Trust needs an independent path — it catches bugs code review misses.

5. 05

   Observability

   SnowflakeSlack

   Every run logs to Snowflake, with Slack alerts on silent no-data runs.

   Decision — A green run that quietly loaded nothing is the real failure mode — so it's alerted explicitly.

6. 06

   Serve

   Power BI

   The marts feed Power BI dashboards and the regulatory reports.

   Decision — The numbers someone actually decides on — everything upstream exists to make them boring.

What shipped

• Event-driven ingestion into time-partitioned S3 on AWS
• A 15-minute Prefect + dlt load into Snowflake — typed and idempotent
• A tested, version-controlled dbt platform: staging → curated → dimensional marts, with CI/CD
• An independent reconciliation layer diffing a vendor feed against the internal models in CI
• Run logging and Slack alerting for silent-failure detection, feeding Power BI at the end

Stack

None of it is exotic. All of it is owned, tested, and reconciled against an independent source — which turns out to be the rare part.