Data Migration

[jirhiker]

ADR: Migration from Legacy MS Access/SQL Server Database to GCP Postgres/PostGIS

Status: In Progress
Date: 2025-08-26
Deciders: [Team/Project Name]
Context: Migration of legacy geospatial and tabular data

---

Context

The current data management system relies on a combination of Microsoft Access and SQL Server databases. These systems have several limitations:

• Poor schema design: inconsistent relationships, denormalized tables, and unclear constraints.
• Limited scalability: difficult to integrate with modern analytics pipelines and geospatial tools.
• Operational friction: local file-based Access databases and older SQL Server instance create maintainability risks.

The organization is standardizing on Google Cloud Platform (GCP) and requires a modern, cloud-native, scalable, and geospatially enabled database.

Requirements driving the migration:

• Support for geospatial queries (PostGIS extension).
• Improved schema design with clear constraints and normalization.
• Cloud hosting on GCP for availability, backups, and integrations.
• Use of Python + SQLAlchemy for ETL (extract-transform-load) to ensure repeatability and maintainability.
• Capability to handle large time-series and geospatial datasets.

---

Decision

We will migrate the legacy MS Access/SQL Server databases into a Postgres/PostGIS instance hosted on Google Cloud SQL.

The migration approach will be:

1. Schema Redesign

   • Normalize legacy tables where appropriate.
   • Define explicit foreign keys, primary keys, and constraints.
   • Introduce PostGIS geometry/geography types for spatial data.

2. Data Extraction

   • Use Python scripts to extract data from MS Access and SQL Server via ODBC connectors.
   • Apply transformations to clean inconsistent field names, datatypes, and encodings.

3. Data Transformation

   • Leverage SQLAlchemy ORM and core expressions for schema mapping.
   • Implement validation and type conversions (e.g., Access Yes/No → Postgres BOOLEAN, geometry strings → PostGIS geometries).
   • Apply business rules (deduplication, reference table alignment, normalization).

4. Data Loading

   • Load into the target Postgres/PostGIS instance using SQLAlchemy bulk inserts or COPY for efficiency.
   • Enforce constraints and run integrity checks after load.

5. Validation

   • Perform row counts, checksum comparisons, and sample queries.
   • Validate spatial data integrity (geometry validity, CRS consistency).

6. Deployment & Operations

   • Run migrations in repeatable, idempotent Python workflows.
   • Maintain migration scripts in version control.
   • Configure Cloud SQL backups, monitoring, and IAM-based access.

---

Alternatives Considered

• Lift-and-shift with no schema redesign

  • Rejected: would replicate poor design and technical debt in new system.

• ETL via commercial tools (e.g., Fivetran, Talend, Informatica)

  • Rejected: higher cost, less flexibility for domain-specific transformations, limited geospatial handling.

• Using Google BigQuery instead of Postgres/PostGIS

  • Rejected: while BigQuery is strong for analytics, it is less suited for transactional geospatial workloads requiring PostGIS functions.

---

Consequences

• Positive

  • Modern, maintainable database on GCP.
  • Better data integrity, constraints, and geospatial support.
  • Migration scripts are reusable for future refreshes or additional data sources.

• Negative

  • Upfront effort for schema redesign and data cleaning.
  • Additional operational complexity managing Cloud SQL vs. on-prem SQL Server.
  • Learning curve for PostGIS functions and optimizations.

---

Notes

• Migration will be phased:

  • Phase 1: Schema design and pilot dataset migration.
  • Phase 2: Full migration and validation.
  • Phase 3: Decommission legacy databases.

• Documentation and repeatable scripts will ensure smooth onboarding for new team members and support audits.