SQL Server ETL Engineer (T-SQL / SSIS) – Intermediate

Optimum Patient Care has been supporting chronic disease management and research in primary care for over 15 years. We are a not-for-profit social enterprise which provides a range of services to over 1000 GP surgeries across England, Scotland, Wales and Northern Ireland. Our services include clinical audit and review services, IG‑compliant data extraction services, and research support services. Our services are provided at no cost to participating practices and freely available to all GP surgeries across the UK. Our anonymised research database, OPC Research Database (OPCRD), is one of the largest electronic health record databases in the world for research use, with over 17 million anonymised patients.

Job Description

We are seeking a hands‑on SQL Server ETL Engineer to work on large‑scale T‑SQL and SSIS‑based data pipelines supporting national GP clinical research datasets. The role focuses on maintaining and improving a mature on‑premises SQL Server 2019 environment responsible for ingesting and processing high‑volume clinical data used in real‑world research. This role requires strong hands‑on SQL Server and SSIS experience. It is not a cloud‑platform or Spark/Snowflake data engineering role. The position is script‑heavy and database‑centric, involving significant T‑SQL development, ETL scripting, SSIS package maintenance, and large‑scale data processing across very large databases.

Key Responsibilities

• SQL Server ETL Development
  • Develop and maintain T‑SQL based ETL pipelines running on SQL Server 2019.
• Data Processing & Data Quality
  • Investigate and resolve data integrity and quality issues across multiple research databases.
  • Write historic correction scripts to fix data anomalies across large datasets.
  • Implement validation and consistency checks during data ingestion and transformation.
• Performance & Database Engineering
  • Optimise ETL queries and batch processes running against very large tables (billions of rows).
  • Analyse and tune query execution plans, indexing strategies, and partitioned tables.
  • Improve performance of data loads and batch ETL processing.
• Operational Database Work
  • Support operational SQL Server tasks including:
    • Integrity checks
    • Troubleshooting failed imports
  • Manage file‑based data ingestion, including large CSV imports and server directory management.
  • Maintain and improve SSIS packages and SQL‑driven import scripts.
  • Manage ~700 automated data imports per month from multiple GP clinical systems.
  • Maintain and extend 150+ ETL scripts responsible for data ingestion, transformation, and validation.
  • Adapt ETL pipelines as data formats and source system specifications evolve.

Key Requirements

• Strong commercial experience working with Microsoft SQL Server (2016–2019+)
  • Stored procedures
  • Complex joins and transformations
  • Large batch processing scripts
  • Error handling and logging
• Hands‑on experience building or maintaining ETL pipelines using SSIS.
• Experience working with large datasets (hundreds of millions to billions of rows).
• Experience with query optimisation and performance tuning, including:
  • Execution plan analysis
  • Partitioned tables
• Experience working in script‑driven ETL environments.
• Comfortable working directly with database servers and file‑based data imports.
• Typical candidates will have approximately 3–6 years experience in SQL Server ETL, BI development, or database engineering roles.
• Following experience would be beneficial but is not required:
  • Experience working with clinical, healthcare, or research datasets.
  • Familiarity with SNOMED CT or OMOP CDM.
  • Experience with PostgreSQL.
  • Experience building ETL utilities in Java or C#.

Future Projects

• Migration of one major research database to a PostgreSQL clustered platform, expanding our data infrastructure rather than replacing SQL Server.
• Development of OMOP‑compatible data pipelines to strengthen interoperability and support advanced research workflows.
• National GP clinical datasets used in medical and population health research.
• Extensive use of T‑SQL, SSIS, and batch ETL scripting.
• Very large datasets containing billions of clinical records.
• Collaborative technical team of engineers, DBAs, and research specialists.

Why Join Us

You will help maintain and develop the data infrastructure supporting some of the UK’s most significant real‑world clinical research programmes. This role offers the opportunity to work deeply with large‑scale SQL Server ETL systems and complex healthcare datasets in a collaborative research‑focused organisation.