Build a three-party policy reconciliation pipeline (SQL/dbt/Python) to unblock insurance invoicing

An insurance company sells device protection plans for a hardware manufacturer’s devices through a telco. The telco sells the plans at point of sale, the hardware manufacturer validates and enrols the device, and the insurance company puts the policy on risk and invoices the telco. The three parties’ daily and weekly policy counts don’t agree, so the insurance company cannot invoice the telco with confidence. We expect the gaps to come from a mix of: different definitions (sold vs enrolled vs effective); cut-off and time-zone differences; failed or retried enrolments; cancellations, refunds and device swaps; IMEI/serial key problems; plan/SKU mapping; billing status; VAT/commission treatment. Part of the job is to prove which causes apply and how much each contributes. We are looking for a data/analytics engineer with hands-on partner-reconciliation experience to: Profile the three parties’ data and quantify and explain the gaps. Agree definitions, keys and a data contract with all three parties. Build an automated, auditable matching pipeline with an exception workflow. Produce a monthly invoice that the insurance company and the telco both accept, based on matched policies plus a true-up for open items. An AI agent layer (LLM-assisted investigation of mismatches) is in scope as a later phase. Matching decisions and invoice amounts must stay rules-based and auditable. Expected volume is tens of thousands of policy records per month (to be confirmed). Data arrives as files (CSV/Excel via SFTP or email) and/or API extracts. Phases and deliverables Discovery and data audit (fixed price, 1–2 weeks) Deliverables: data inventory, profiling, a count comparison over at least one month, root-cause hypotheses, a data-protection plan. Done when every count difference is quantified and attributed, with a written report and walkthrough. Definitions, mapping and data contract (1–2 weeks) Deliverables: definition sheet, single cut-off, policy model, plan/SKU mapping, file layout with control totals and sequence numbers, matching tolerances. Done when the parties have signed off. Matching engine (2–4 weeks) Deliverables: SQL/dbt + Python pipeline, IMEI normalisation, rule-based matching, probabilistic matching (e.g. Splink) on the rest, and a break table with cause codes. Done when two historical months reproduce known totals. Exceptions, dashboard, optional AI agent (1–2 weeks) Deliverables: counts per party, match rate, ageing and owners. Optionally, a read-only LLM agent that drafts explanations for human approval, fully logged. Done when every open break has a cause code, owner and age. Invoice and true-up (1–2 weeks) Deliverables: a monthly reconciled statement (gross, VAT, net, commission) from approved matches only, plus a true-up and dispute log. Done when one real month’s invoice is accepted by both parties. Handover (~1 week) Deliverables: runbook, data dictionary, alerts, training. Done when client staff run one cycle without you. Required skills: SQL (advanced) · Python (pandas) · data reconciliation / exception management · dbt or equivalent · Airflow, Prefect or similar Required experience 3+ years in data or analytics engineering. At least one automated reconciliation between two or more external parties that drove invoicing, settlement or payments; ideally insurance bordereaux or telco partner settlement. Matching records without a shared unique key. Audit-ready pipelines: versioned inputs, reproducible reruns, documented rules. Getting written sign-off on definitions from finance stakeholders. Handling personal data under PDPA, GDPR or similar. Nice to have: device or embedded insurance; IMEI/device lifecycle; Splink or other record-linkage tools; LLM agents with human-in-the-loop controls; TM Forum revenue assurance; a Southeast Asian language. Engagement and budget Phase 1: fixed-price milestone. Please quote; we expect roughly USD 800–1,200. Phases 2–6: fixed price per phase, or hourly with a weekly cap, agreed after phase 1. Expected rate: around USD 20/hour. If you see a smarter, faster or cheaper way to solve this than the phases above, please propose it. Please include a short paid test task in your proposal. An NDA and data processing agreement are signed before any data is shared. Data stays in a client-approved environment and is pseudonymised wherever possible. Screening questions Describe a reconciliation you automated between two or more external parties: the volumes, the match rate before and after, and how you handled records without a shared key. Three parties report different daily policy counts for the same programme. Which root causes would you check first, and why? How do you define break categories and materiality, and assign an owner to each exception? How do you make a monthly invoice figure reproducible and auditable months later? IMEI or policy ID is missing on part of the records. Which matching approach would you use, and how do you stop false matches reaching an invoice? Where would you use an LLM or AI agent in this project, and where would you refuse to? How would you handle personal data under data-protection laws such as PDPA or GDPR when parties or tools sit outside the country where the data is collected? What exactly would you deliver in a two-week paid discovery, and what do you need from us on day one?