Purpose of the role
To build an automated, well-controlled and scalable process for the syndicate’s Consortium & Facility business, covering:
- Production of bordereaux and additional data feeds for follow markets
- Calculation of fees and profit commissions (PCs), including consortia fees and coverholder commissions
- Reporting, dashboards and auditable controls across the end-to-end process
Responsibilities: Solution design and data engineering
- Architect and build the end-to-end solution, taking ownership from design through to delivery as a hands-on engineer
- Extract data from on-premise SQL Server databases and data warehouses and land it in Microsoft Fabric (Azure, OneLake)
- Develop data pipelines and transformation logic using T-SQL and Python within Fabric
- Design and maintain the mapping tables and reference data required for calculations
Fee and commission calculation
- Enhance and automate the existing fee calculation process so that additional follow markets can be added efficiently
- Enhance and automate the collection and processing of consortia fees and coverholder commissions
- Build accurate, repeatable profit commission calculations
Data distribution and reporting
- Build an efficient and scalable capability to provide data and bordereaux to follow markets, with controls embedded throughout
- Develop a semantic layer over the data to support Power BI reporting and data exports
- Create dashboards and outputs that feed Finance and Operations
Controls, governance and documentation
- Design and implement auditable controls around all automated processes, giving clear visibility of what each process is doing
- Produce clear technical and process documentation to support audit, ongoing support and handover
- Use PowerShell to manage and automate scheduled tasks
Stakeholder Engagement
- Work closely with Operations, Underwriting and Finance to ensure each group receives exactly the data and outputs it needs
- Collaborate with the business analyst and project manager, translating the agreed process map into a working solution
- Communicate progress, design decisions and risks clearly to the Head of Data Management and wider stakeholders
Requirements: Excellent Transact-SQL (T-SQL)
- Strong knowledge of Microsoft SQL Server database systems and ETL tooling, including SSIS
- Hands-on experience with Azure cloud data solutions, ideally Microsoft Fabric and OneLake
- Python for data engineering, including data flows and notebooks within Fabric
- Power BI, including building and maintaining semantic models and online reports
- PowerShell for task automation and scheduling
- A track record of designing and building end-to-end data solutions, from architecture through to delivery
- Experience embedding auditable controls and reconciliations into automated processes
Desirable: Working knowledge of the Lloyd’s insurance market (strongly preferred)
- Exposure to consortium, facility, binder or delegated authority business
- Understanding of bordereaux production, Coverholder commissions and profit commission calculations
- Experience migrating on-premise SQL Server and SSIS workloads to Microsoft Fabric
- Experience delivering data to follow markets or other external counterparties
- Experience working alongside business analysts and project managers on a defined project
Benefits: Initial 12 month contract