Data Types and Compatibility Issues

Section 4: Database Platforms for Migration. Lesson 22.

Data types and compatibility issues are among the most critical challenges in data migration, because different systems store, process and interpret information in different ways. Even small differences in formats, structures or encoding can cause serious operational problems. Understanding them keeps data accurate, accessible and functional after migration.

The lesson covers six areas: cross-platform data matching, schema differences, encoding, compatibility risk assessment, data constraints, and format conflicts.

1. Cross-platform data matching

Cross-platform data matching ensures that data from one system aligns correctly with both the structure and the meaning of data in another. Different platforms may use different types for the same information. One system stores customer IDs as numbers, another as text strings, so a join between them silently fails or mismatches:

Source:  customer_id INT      →  1042
Target:  customer_id VARCHAR  →  '001042'   -- leading zeros: 1042 ≠ '001042'

Without accurate matching you get mismatched records, broken relationships and missing information, which lead to inaccurate reports, duplicate records and operational confusion. Data mapping identifies which fields belong together and how values must be converted, such as padding, trimming, casting or code lookups.

Common type mappings

2. Handling schema differences

A schema defines a database's tables, columns, relationships and constraints (lesson 21). When source and target schemas differ, data cannot simply be copied across. Typical differences:

• One system keeps customer information in a single table; the other splits it into related tables (customers, addresses, contacts).
• Different naming conventions (CUST_NM versus full_name).
• Different field lengths (VARCHAR(50) into VARCHAR(30) truncates).
• Hierarchical or nested structures (XML, JSON) going into flat relational tables, or the reverse.

Handling them means restructuring data to fit the target: splitting fields, merging tables, creating new relationships and transforming structures. Done properly, the migrated database works efficiently and stays logically consistent.

3. Managing encoding problems

Character encoding determines how characters, symbols and languages are represented as bytes. If source and target encodings don't match, text becomes corrupted or unreadable. Accented letters, symbols and non-English scripts turn into random characters, a problem often called mojibake.

Original (UTF-8):          José Müller — 北京
Read as Latin-1 by mistake: José Müller — 北京

To manage encoding:

• Identify the encoding of the source database, tables, columns and export files.
• Choose UTF-8 for the target (utf8mb4 in MySQL, NVARCHAR or a UTF-8 collation in SQL Server).
• Convert explicitly during extract or load; never let tools guess.
• Test with real multilingual samples such as names, addresses and legal text.

This matters most for global organisations with multilingual data, where bad encoding can damage customer records, legal documents and business communications.

# Read a legacy export in its real encoding, write UTF-8
import pandas as pd

df = pd.read_csv("legacy_customers.csv", encoding="cp1252")
df.to_csv("customers_utf8.csv", index=False, encoding="utf-8")

4. Compatibility risk assessment

Before migration, assess whether the target can fully support the source data. Risks include:

• Unsupported file formats
• Incompatible data types, such as a legacy date format the new platform cannot parse
• Software limitations: maximum lengths, precision, row size, missing features
• Performance problems: queries or indexes that behave differently on the new engine

Finding these early means solutions can be planned rather than improvised. Reduce the risk with testing, validation and pilot migrations: migrate a representative subset first, compare the results, then scale up. A thorough assessment minimises downtime, prevents corruption and raises the success rate.

5. Understanding data constraints

Constraints are rules that protect data integrity by rejecting invalid or inconsistent entries.

Analyse the target's constraints and clean, validate and transform data before transfer (Section 3). Load parent tables before child tables, or disable foreign keys during a bulk load and re-enable them with validation afterwards. Handled properly, constraints stop the migration failing and keep the target consistent and reliable.

-- Pre-load checks against staged data
SELECT email, COUNT(*) FROM staging.customers
GROUP BY email HAVING COUNT(*) > 1;                  -- would violate UNIQUE

SELECT COUNT(*) FROM staging.customers WHERE name IS NULL;  -- would violate NOT NULL

SELECT o.order_id FROM staging.orders o
LEFT JOIN staging.customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;                        -- would violate the FOREIGN KEY

6. Resolving format conflicts

Format conflicts are differences in how values are represented across systems:

• Dates: MM/DD/YYYY versus DD/MM/YYYY, so 03/04/2025 is either 4 March or 3 April
• Decimal separators: 1,234.56 in the US versus 1.234,56 in much of Europe
• Currency symbols and codes: $, US$, USD
• Measurements: kg versus lb, cm versus inches
• Phone numbers: local versus international (E.164) formats

Unresolved, these make migrated data inaccurate or unusable. Standardise values before or during migration (lesson 16), usually with data transformation tools that automate the conversions and reduce manual effort. The result keeps information meaningful, consistent and compatible across every business system.

Compatibility checklist

• Map every source column to a target column and type, noting length and precision.
• Check that identifiers used in joins have the same type and format on both sides.
• Document schema restructuring: splits, merges and new relationships.
• Confirm source and target encodings, and convert to UTF-8.
• Run pre-load checks against the target's keys, uniqueness, NOT NULL and CHECK constraints.
• Standardise dates, numbers, currencies, units and phone numbers.
• Run a pilot migration and validate it before the full load.

Try it: Cross-Engine Type Translator

Pick any source and target among Oracle, SQL Server, MySQL and PostgreSQL to see 14 common column types side by side, each rated low, check or high risk with what to verify, such as Oracle DATE carrying a time, MySQL having no offset-preserving timestamp, or identity sequences needing a reset. The encoding lab takes real text, encodes it as UTF-8, and decodes those bytes with the charset you choose, so you can watch mojibake happen.

Key takeaways

• Different platforms store the same information differently, and small differences cause real failures.
• Match fields by meaning as well as name, and map types explicitly.
• Restructure for schema differences, and manage encoding deliberately, with UTF-8 as the target.
• Assess risks early, respect target constraints, resolve format conflicts, and pilot before the full move.

Next: 23. Connecting to Databases.