Case study

Claims Data, End to End: Webhook to Regulator

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

Architecture · webhook to 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 · A model does not merge until its tests pass, so a broken definition is caught before the regulator sees it. 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 · Power BI is where the decisions get made, so the marts are shaped for the questions being asked rather than for the source systems behind them.

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.