From One-Off Migration to a Reusable Data Integration Platform
What began as a need to move data from Microsoft SQL Server into PostgreSQL evolved into a reusable integration platform capable of supporting multiple programmes, annual data collections, shared reference data, and scheduled synchronisation.
- Duration
- September 2024 – February 2025
- Completed
- Feb 2025
Executive summary
What began as a need to move data from Microsoft SQL Server into PostgreSQL evolved into a reusable integration platform capable of supporting multiple programmes, annual data collections, shared reference data, and scheduled synchronisation.
The central challenge was not simply copying rows between databases. The source and destination systems represented the same business concepts differently. Tables had to be renamed and reshaped; enumerations and identifiers had to be reconciled; nested question options had to become JSON; records had to be loaded in dependency order; and repeated runs had to avoid duplicates while preserving existing relationships.
The resulting Python solution introduced a layered architecture built around repository adapters, table-level migration contracts, reusable transformation strategies, DLT-based loading, and Azure Functions orchestration. At the repository's current point in time, the codebase contains nine programme or shared-data configurations with 123 active, explicitly ordered migration steps. It can be run manually for controlled migrations or invoked on a daily schedule for a selected operational data flow.
The strongest outcome was a change in delivery model: migration logic stopped being a collection of isolated scripts and became an extensible integration framework. New programmes could reuse the same orchestration and data-access foundations while containing their differences in programme-specific models.
This case study is reconstructed from the implementation and its Git history. The repository does not contain production KPIs, user research, commercial outcomes, or a formal post-implementation report. Accordingly, the results below distinguish observed engineering outcomes from business impacts that would still need quantitative validation.
At a glance
- explicitly ordered migration steps across the configured programmes
- 123
- programme and shared-data configurations on one framework
- 9
- table-model modules encoding per-table migration contracts
- 130
- scheduled synchronisation for the operational data flow
- Daily
| Dimension | Evidence from the implementation |
|---|---|
| Delivery period represented | 31 October 2024 to 2 October 2025 |
| Primary integration | Microsoft SQL Server to PostgreSQL |
| Additional integration capability | Separate PostgreSQL source adapter, alongside the PostgreSQL destination adapter |
| Programme scope | Shared/general tables plus NACEL 2024, NACEL 2025, NACEL Northern Ireland 2024, LDIS 2024, Talking Therapies 2024, Talking Therapies 2025, Talking Therapies Data Explorer, and Psychological Professions 2025 |
| Configured migration breadth | 123 active migration calls across nine programme/shared entry points; 130 top-level table-model modules plus four future-model modules |
| Runtime options | Local/manual execution and a timer-triggered Azure Function |
| Scheduled operational scope | A selected four-table NACEL 2025 flow, scheduled daily at midnight |
| Core technologies | Python, pandas, DLT, SQLAlchemy, PyODBC, Psycopg2, Azure Functions, and Application Insights configuration |
| Repository scale at review | 169 tracked Python files, approximately 15,358 tracked lines of Python, 290 commits, and 9 contributor names recorded by Git shortlog |
The repository figures are measures of implementation scope, not claims about production throughput, data volumes, or commercial impact.
The context
The platform served several related data-collection and benchmarking programmes. Although the programmes shared concepts such as projects, organisations, questionnaires, questions, submissions, charts, filters, peer groups, and output types, they did not all use precisely the same structures or rules.
The source system was primarily SQL Server, while the destination application used PostgreSQL. This created a translation boundary at three levels:
- Technical translation — different drivers, connection mechanisms, data types, identity sequences, and database conventions.
- Structural translation — source tables and columns did not always map directly to the target schema.
- Semantic translation — some data needed to be recomputed, grouped, reformatted, enriched, or expressed as new target-side records rather than copied verbatim.
A one-off export/import process would have addressed only the first level. The work instead had to preserve the meaning and relationships of the data while supporting repeated runs and successive programmes.
The challenge
1. Move relational data without breaking its meaning
The migration crossed a database boundary but remained constrained by a highly relational destination model. Projects depended on programmes; questionnaire sections depended on questionnaires; memberships joined questions to sections; submissions depended on registrations and organisations; charts depended on chart groups, parameters, filters, and collections.
Loading the right rows in the wrong order could still fail. The orchestration therefore had to express dependency order as well as table coverage.
2. Reconcile heterogeneous schemas
Some migrations could use straightforward column selection and renaming. Others needed target-specific computation. Examples in the codebase include:
- mapping source names and identifiers onto target columns;
- dropping obsolete source fields or retaining an explicit allow-list;
- normalising values and data types;
- generating slugs, submission codes, parameters, or static reference records;
- restructuring list-style question options into JSON associated with a question;
- applying temporary schema adjustments before a load and restoring them afterwards.
This meant the design needed a predictable common path without pretending every table was identical.
3. Make repeated execution safe
A migration that might run more than once cannot rely on blind inserts. It needs to distinguish new records, changed records, unchanged records, duplicates, and broken references.
The implementation consequently grew from basic extraction and loading into a synchronisation-oriented model. It inspects target constraints, compares incoming data with destination data, separates insert and update paths, removes duplicates within a batch, normalises types, and attempts to account for foreign-key relationships and self-referential parent/child order.
4. Scale from one programme to many
The code history shows an expansion from an initial standalone migration into a portfolio of programme-specific flows. Reuse was essential, but so was isolation: a change for one year's questionnaire or one programme's chart configuration could not be allowed to destabilise every other migration.
5. Move from developer-run migration to operational integration
Local shell scripts were useful during development and controlled cutovers, but a recurring data flow also needed scheduling, failure propagation, configuration through environment variables, and central logging. That drove a later transition into Azure Functions.
The design response
The solution separated stable integration concerns from programme-specific mapping concerns.
flowchart TD
A["SQL Server source"] --> C["Repository adapter"]
B["Optional PostgreSQL source"] --> C
C --> D["Table migration model"]
D --> E["Rename, select, drop and compute"]
E --> F["Programme-specific transformations"]
F --> G["Pre-migration hook"]
G --> H["DLT resource and merge load"]
H --> I["Constraint-aware insert/update handling"]
I --> J["Post-migration and sequence reset"]
J --> K["PostgreSQL destination"]
L["Manual runner"] --> D
M["Daily Azure Function"] --> D
1. A repository layer isolated database connectivity
The data-access layer defined a common repository contract for fetching tables and executing SQL. Concrete adapters supplied the connection details for SQL Server, the primary PostgreSQL destination, and a separately configured PostgreSQL source.
This boundary delivered two practical benefits:
- table migrations could focus on data meaning rather than repeatedly constructing database connections; and
- source connectivity could evolve independently, illustrated by the later addition of automatic SQL Server ODBC driver detection and a ranked fallback list.
The adapter work reduced environment sensitivity across developer machines and cloud hosts. If no compatible SQL Server driver is available, the application fails with a diagnostic that lists the detected and preferred drivers rather than failing later with an opaque connection error.
2. A table-migration contract created a repeatable lifecycle
Every table migration inherits a common base contract. A model identifies its source and destination tables, target primary key, column mappings, fields to drop or retain, and table-specific transformations. It can also override lifecycle hooks for work before the load, after the load, or after identity values need to be reset.
The standard lifecycle is:
- instantiate the table migration;
- identify the destination table and primary key;
- construct the DLT data source;
- run pre-migration adjustments;
- transfer data using merge semantics;
- reset the destination sequence where appropriate; and
- run post-migration adjustments.
That lifecycle is the framework's most important abstraction. It makes ordinary migrations inexpensive while preserving escape hatches for the tables that genuinely require special handling.
3. Transformations handled semantic, not just syntactic, differences
The shared base class supports common structural operations: rename columns, drop unwanted fields, keep an explicit subset, and then apply a table-specific transformation.
For more specialised behaviour, the solution uses transformation strategy objects. One representative example turns multiple list-item records for a question into a question-level JSON value. This is more than changing a column name; it translates how an old relational representation is consumed by the destination product.
Other models compute target records directly when no useful one-to-one source table exists. This allowed output types, parameters, questionnaires, and related configuration to be expressed in the destination's vocabulary rather than preserving a legacy structure that no longer fitted.
4. Programme entry points made ordering explicit
Each programme has a main module that lists table migrations in execution order. The sequence generally moves through:
- programmes, organisations, projects, and guidance;
- questions, questionnaires, sections, and memberships;
- registrations, submissions, peer groups, and case codes;
- chart groups, charts, parameters, collections, and memberships; and
- data and filter tables.
This is deliberately simple orchestration. The code itself documents the dependency order and makes it possible to run a complete programme or a selected subset. Shared reference data such as organisations, role types, time periods, output types, visualisations, and output mappings has its own general migration path.
5. Merge-oriented loading supported repeatable runs
The DLT pipeline defaults to a merge write disposition and receives the table's primary key. Before returning rows for the DLT load, the table base class performs additional target-aware handling:
- it reads the destination table and its unique constraints;
- identifies insert and update candidates;
- compares non-key values to detect change;
- removes duplicates within the incoming batch;
- safely coerces numeric, nullable integer, date, and string values;
- consults target relationship metadata and applies filtering and ordering logic;
- orders self-referential parent records ahead of children; and
- uses bulk insert and temporary-table update paths.
These controls show an important transition in the team's thinking. The problem was no longer framed as “copy this table once”; it was framed as “reconcile a source dataset with a live target while respecting target invariants.”
6. Azure Functions turned the pipeline into an integration service
The repository retained local runners for development and controlled execution, then added a timer-triggered Azure Function for recurring operation. The configured function:
- runs on a daily midnight cron schedule;
- loads environment-based DLT and PostgreSQL configuration;
- invokes a deliberately narrowed NACEL 2025 flow;
- logs success or failure and rethrows exceptions so the function runtime can register a failed invocation; and
- includes Application Insights sampling configuration through the Azure host settings.
The narrowed cloud flow is significant. It shows that the operational integration was not simply “run every historical migration every night.” Instead, the scheduled entry point selected the submission, peer-group membership, case-code, and data tables that needed recurring synchronisation, while broader programme migrations remained available through their own entry points.
How the solution evolved
The Git history reveals six distinct transitions in the project.
Phase 1 — Standalone foundation: October to November 2024
The earliest work separated the migration application from a wider codebase, created the shared repository layer, documented local setup, and established a runnable migration. This was the point at which migration became a product-shaped component rather than incidental application code.
Representative history:
6594db4— refactored the migration project to be standalone;df6c4d0— consolidated shared SQL Server and PostgreSQL repository logic; and5193271— recorded a working migration.
Phase 2 — Reusable table framework: November 2024 to March 2025
The team progressively added question, questionnaire, membership, chart, collection, submission, filter, user, and output migrations. The base model gained a compute_data path, allowing generated or transformed records as well as direct table copies.
By December, the history records a unified output migration. By March, all migrations could be activated through a common orchestration path. The codebase was becoming a framework with programme-specific implementations.
Representative history:
913da41— extended the base migration with computed data;d027c93— completed a unified output migration; and7b92738— activated the full migration set at that stage.
Phase 3 — From loading to reconciliation: January to May 2025
Repeated operational use exposed the need for safer connection handling, indexing, constraint-aware filtering, and duplicate prevention. Changes in this period addressed connection closure, merge behaviour, duplicate filtering, display ordering, submission codes, and data-type mismatches.
The key conceptual shift was from “ETL script” to “state reconciliation.” The target database was treated as an existing system with constraints and records that could not simply be overwritten.
Representative history:
4e61a15— closed database connections;8b93434— corrected merge behaviour; andf684530— introduced pre-filtering to avoid duplicates.
Phase 4 — Cloud operationalisation: May to July 2025
The project was restructured so the same application code could run under Azure Functions. Configuration moved through environment variables and DLT secrets, a timer schedule was added, and the function path was refined until the history recorded a working setup.
Representative history:
d472960— moved code under the application directory to accommodate the Function App;8a31bc9— added the daily midnight schedule;629e4d1— merged the Function App restructure; anddf430f4/f5f6f53— recorded the working Function App setup.
Phase 5 — Multi-programme and integration expansion: June to September 2025
Once the foundation was reusable, the repository expanded across NACEL Northern Ireland, Talking Therapies, Talking Therapies Data Explorer, and Psychological Professions. The team also added automatic ODBC driver selection and a separately configured PostgreSQL source adapter.
Representative history:
6cd3e53— merged NACEL Northern Ireland;3126e59— merged Talking Therapies;f3be440— added SQL Server ODBC driver detection and fallback;0f3f0f2— added the Talking Therapies Data Explorer work; ande218242— added the Psychological Professions 2025 migration.
Phase 6 — Downstream analytics evolution: September to October 2025
The final visible phase broadened output and visualisation mappings. Pie, radar, scatter, list, mean, and median bar representations were added or updated. This illustrates an often-overlooked integration requirement: successful data migration must also preserve how the destination product interprets and presents data.
Representative history:
31096b9— added pie and radar output mappings;88926cc— added scatter output support; and5d210d8— added list, mean, and median bar visualisation mappings.
Major engineering decisions
Keep stable mechanics in the core and volatile rules at the edge
Connection handling, DLT orchestration, column-processing order, and load lifecycle live in shared components. Programme- and year-specific rules live in dedicated models. This limits the blast radius of domain changes and makes the common path easier to understand.
Model the target as the authority on integrity
The upsert path interrogates PostgreSQL metadata for unique and foreign-key constraints. That is preferable to duplicating every target invariant in configuration, because the database remains the canonical expression of its own relationships.
Make exceptions explicit through lifecycle hooks
Some migrations temporarily alter a target column type or resolve a required target identifier before loading. premigration, postmigration, and resettableindex hooks make those exceptions visible instead of hiding them inside generic orchestration.
Choose explicit ordering over premature orchestration complexity
Programme entry points use ordered function calls rather than a separate workflow engine or dependency graph. For the observed scope, this keeps execution transparent. The trade-off is that dependency validation and programme selection remain manual.
Retain manual and scheduled modes
Historical or structural migrations benefit from controlled execution, while operational tables benefit from regular synchronisation. Maintaining both paths supports those different risk profiles.
Outcomes supported by the evidence
A reusable integration capability replaced isolated migration scripts
The architecture provides one table lifecycle, one repository contract, one DLT pipeline wrapper, and shared transformations that are reused across programme folders. This is the clearest engineering outcome in the repository.
The solution expanded without replacing its core
The project grew to nine shared/programme configurations and 123 active migration steps while retaining the same central migration and orchestration abstractions. That indicates the design was extensible enough to accommodate new years and programmes without a wholesale rewrite.
Repeated runs became a first-class concern
Merge disposition, target comparison, duplicate removal, update detection, type coercion, sequence reset hooks, and relationship-aware ordering all reduce the risk inherent in rerunning a migration against a populated target.
A local migration tool became an operational integration
The Azure Function entry point, daily schedule, exception propagation, and Application Insights configuration moved the solution beyond developer-only execution. The same core could now support a recurring synchronisation flow.
Environment portability improved
Environment-driven connection settings, separate source/destination repositories, and automatic ODBC driver discovery reduced machine-specific assumptions. This matters for a pipeline expected to run both locally and in hosted infrastructure.
Downstream use cases stayed connected to the migration model
The later output and visualisation changes show that the team treated analytics configuration as data, not as an afterthought. Migration work therefore included the metadata needed by the destination experience, not only transactional records.
What cannot be claimed from the report
The codebase does not provide evidence for claims such as:
- a specific number of records migrated;
- a measured reduction in manual effort or operating cost;
- a defined improvement in data freshness;
- zero data loss or a quantified reconciliation rate;
- a production uptime or service-level result;
- user adoption, stakeholder satisfaction, or commercial impact; or
- a security or compliance certification.
Those may have been achieved, but they should not be added to a published case study without operational records or stakeholder evidence.
Constraints, risks, and technical debt
A credible case study should show what the implementation solved and what remained to be matured.
Automated verification is missing
No unit, integration, contract, or end-to-end tests are present in the repository, despite pytest being included as a dependency. The most complex logic—upsert classification, constraint handling, type conversion, and parent/child ordering—therefore lacks an executable safety net.
Observability is useful but incomplete
The Function App records top-level success and failure, and SQLAlchemy logging is enabled. Much of the table-level behaviour, however, is reported through print statements. Several repository and metadata errors are caught, printed, and converted into None or empty results. That can allow a run to continue with weakened validation and makes alerting or per-table diagnostics harder.
Recovery behaviour is shallow
There is no explicit retry policy, backoff, dead-letter path, quarantine table, compensating action, or run-level transaction strategy in the migration path. A failure after a pre-migration schema adjustment or after some tables have completed could require manual assessment.
Programme selection is code-driven
The local runner selects programmes by commenting or uncommenting function calls, while the Azure entry point imports a fixed NACEL subset. This is understandable during rapid delivery, but it creates deployment work for what could become configuration.
The abstraction is only partly source-agnostic
A PostgreSQL source adapter exists, but the base table migration currently constructs a SQL Server source and PostgreSQL destination by default. Fully pluggable source selection would require dependency injection or configuration at the pipeline/programme boundary.
SQL construction needs hardening
Several table names, column names, and bulk values are assembled into SQL strings. Values are escaped in places, but a mature integration service should consistently use parameterised execution, safely quoted identifiers, and bounded batch sizes.
Constraint behaviour needs explicit contract tests
The system contains substantial logic intended to prevent duplicate and foreign-key violations. That logic should be verified against empty targets, populated targets, composite unique keys, nullable foreign keys, self-references, partial failures, and changes to destination constraints.
Recommended next phase
1. Establish a migration assurance suite
Create unit tests for transformations and upsert classification, repository contract tests for each adapter, and containerised SQL Server/PostgreSQL integration tests. Add fixture datasets that cover duplicates, changed rows, nulls, composite keys, missing parents, and self-referential trees.
2. Introduce reconciliation as a formal output
Every table run should produce structured counts for source rows, accepted rows, inserts, updates, unchanged rows, rejected rows, and errors. Reconciliation reports should be stored by run ID and compared with defined tolerances.
3. Replace code toggles with a declarative migration registry
A registry should define programme, table, dependencies, source adapter, schedule eligibility, and enabled status. A validated dependency graph could then calculate order and prevent child tables from running before their parents.
4. Strengthen operational resilience
Add run locking, retry/backoff for transient failures, idempotent checkpoints, failure quarantine, bounded transactions, and a documented restart strategy. Ensure post-migration cleanup runs safely when a load fails.
5. Upgrade observability and alerting
Replace print statements with structured logs containing run ID, programme, table, phase, duration, row counts, and exception category. Publish metrics and alerts for freshness, failure rate, reconciliation variance, duration, and rejected records.
6. Harden security and configuration
Move secrets into a managed secret store, use least-privilege database accounts, prevent sensitive connection details from reaching logs, parameterise SQL values, quote identifiers safely, and document data retention and access controls.
7. Clarify migration versus steady-state integration
Separate historical/bootstrap migrations from recurring synchronisation flows. Historical workflows can be versioned and retired after reconciliation; recurring flows should have explicit ownership, service objectives, monitoring, and change management.
Measures that would complete the business story
The next version of this case study should add real operational measures in five categories:
| Measure | Example definition |
|---|---|
| Scale | Source rows, tables, programmes, and data volume processed per run |
| Quality | Reconciliation percentage, rejected rows, duplicates prevented, and referential-integrity failures |
| Performance | End-to-end duration, rows per second, and slowest table |
| Reliability | Successful-run rate, mean time to recovery, retry rate, and freshness SLA attainment |
| Efficiency | Manual hours removed, time to onboard a new programme, and migration code reused versus written anew |
These measures would connect the demonstrated engineering capability to stakeholder value without relying on unsupported estimates.
Lessons for data and integration teams
- A database migration is a domain translation. The hard work lies in preserving meaning, relationships, and downstream behaviour—not in moving bytes.
- Design for the second run. Duplicate handling, update detection, identity management, and reconciliation should be present before a migration is rerun against live data.
- Put programme variation behind a stable lifecycle. Shared mechanics should be reusable; volatile business rules should remain local and explicit.
- Treat ordering as part of the data model. Relational dependencies turn orchestration order into a correctness requirement.
- Operationalisation changes the standard of quality. Once a script is scheduled, it needs observability, retry behaviour, configuration discipline, and ownership like any other service.
- Do not confuse implementation scale with business impact. Repository statistics show breadth; only operational and stakeholder evidence can prove value.
Conclusion
This work is best understood not as a single data migration, but as the creation of a reusable bridge between a legacy data estate and an evolving PostgreSQL-based platform.
The implementation progressed through a coherent series of transitions: from standalone scripts to a shared framework; from direct loads to target-aware reconciliation; from one programme to a portfolio of migrations; and from manual execution to a scheduled cloud integration. Its architecture made programme-specific complexity manageable while keeping the core lifecycle consistent.
The next challenge is assurance rather than breadth. Automated tests, formal reconciliation, structured observability, safer SQL execution, and resilient recovery would turn a capable migration framework into a mature integration service. Even with those gaps, the repository demonstrates a substantial shift in data engineering practice: data movement became repeatable, extensible, and operationally deployable.