Schema Conversion and Evolution with AWS SCT and DMS
Schema Conversion and Evolution with AWS SCT and DMS
Why Is This Important?
In modern data engineering, organizations frequently need to migrate databases between different engines (e.g., Oracle to Amazon Aurora, SQL Server to PostgreSQL, or on-premises to the cloud). One of the biggest challenges in any migration is converting the source database schema—including tables, views, stored procedures, functions, triggers, and data types—into a format compatible with the target database engine. Schema evolution, on the other hand, refers to the ongoing process of managing schema changes over time as applications and business requirements evolve. For the AWS Data Engineer Associate exam, understanding how AWS Schema Conversion Tool (SCT) and AWS Database Migration Service (DMS) handle schema conversion and evolution is critical, as these are core services tested in migration and data store management scenarios.
What Is Schema Conversion?
Schema conversion is the process of translating the database schema (DDL statements, data types, indexes, constraints, stored procedures, functions, triggers, etc.) from one database engine's format to another. For example, converting an Oracle PL/SQL stored procedure into a PostgreSQL PL/pgSQL equivalent. This process can be complex because different database engines have proprietary extensions, unique data types, and different SQL dialects.
What Is Schema Evolution?
Schema evolution refers to the ability to modify and update a database schema over time without breaking existing applications or losing data. In the context of migrations, schema evolution matters because the source database may continue to change during the migration process. AWS DMS supports ongoing replication (Change Data Capture / CDC), but schema changes at the source during migration require careful handling to ensure the target stays synchronized.
What Is AWS Schema Conversion Tool (SCT)?
AWS SCT is a free desktop application that automates the conversion of database schemas from one engine to another. It supports heterogeneous migrations (different source and target engines) and provides:
• Assessment Reports: SCT analyzes the source schema and generates a detailed migration assessment report showing what percentage of the schema can be automatically converted, what requires manual intervention, and the estimated level of effort.
• Automatic Schema Conversion: SCT automatically converts the majority of database objects, including tables, views, indexes, sequences, and many stored procedures/functions.
• Action Items: For objects that cannot be automatically converted (e.g., complex stored procedures using proprietary features), SCT provides specific action items and suggestions for manual conversion.
• Data Type Mapping: SCT maps source data types to equivalent target data types. You can customize these mappings if the default behavior does not suit your needs.
• Application SQL Conversion: SCT can also scan application source code (Java, C#, C++, etc.) to find embedded SQL statements and convert them to the target dialect.
• Support for Data Warehouses: SCT supports converting schemas from data warehouse platforms like Teradata, Oracle, Netezza, Greenplum, and Microsoft SQL Server to Amazon Redshift.
What Is AWS Database Migration Service (DMS)?
AWS DMS is a managed service that migrates data from a source database to a target database. While SCT handles schema conversion, DMS handles the actual data movement. Key features include:
• Full Load Migration: DMS copies all existing data from source to target.
• Change Data Capture (CDC): DMS continuously replicates ongoing changes from the source to the target, enabling near-zero-downtime migrations.
• Schema Handling in DMS: DMS can create basic target schemas (tables with primary keys and basic data types) on the fly during migration. However, DMS does not convert stored procedures, functions, triggers, secondary indexes, or non-primary key constraints. This is why SCT is needed for heterogeneous migrations.
• Homogeneous Migrations: For same-engine migrations (e.g., MySQL to MySQL), DMS can handle basic schema migration without SCT, since the schema syntax is the same.
• Heterogeneous Migrations: For different-engine migrations (e.g., Oracle to PostgreSQL), you must use SCT first to convert the schema, then use DMS to migrate the data.
How SCT and DMS Work Together
The typical workflow for a heterogeneous database migration is:
1. Run SCT Assessment: Connect SCT to the source database and generate an assessment report. Review the complexity and identify items requiring manual conversion.
2. Convert Schema with SCT: Use SCT to automatically convert as much of the schema as possible. Manually address any action items for objects that could not be automatically converted.
3. Apply Schema to Target: SCT generates DDL scripts for the target engine. Apply these scripts to create the target schema on the destination database.
4. Migrate Data with DMS: Create a DMS replication instance, configure source and target endpoints, and create a migration task. DMS performs a full load of the data and optionally enables CDC for ongoing replication.
5. Validate and Cut Over: Validate the migrated data using DMS data validation features, then perform the application cutover to the target database.
Schema Evolution During Migration
Handling schema changes during an active migration is a challenge. Key considerations include:
• DMS and DDL Changes: DMS has limited support for DDL changes during CDC. It can handle some DDL operations (like adding a column or dropping a table) depending on the source and target engines, but complex DDL changes may require restarting the migration task or manual intervention.
• Task Settings for DDL Handling: DMS task settings allow you to configure how DDL statements are handled during CDC. Options include: HandleSourceTableDropped, HandleSourceTableTruncated, and HandleSourceTableAltered. These settings control whether DMS propagates DROP, TRUNCATE, or ALTER TABLE statements to the target.
• Schema Evolution in Data Lakes: When migrating to data lakes (e.g., S3 with AWS Glue), schema evolution is managed through tools like AWS Glue Schema Registry, which supports Avro, JSON Schema, and Protobuf formats with compatibility modes (BACKWARD, FORWARD, FULL, NONE) to control how schemas can evolve over time.
• SCT Data Extraction Agents: For large-scale data warehouse migrations (e.g., to Amazon Redshift), SCT provides data extraction agents that work alongside DMS to move large volumes of data in parallel, handling schema conversion at the same time.
Key Differences Between SCT and DMS
• SCT converts the schema (structure) — DDL, stored procedures, views, functions, triggers, and application SQL.
• DMS migrates the data (content) — rows, ongoing changes via CDC.
• SCT is a client-side desktop tool; DMS is a managed cloud service with replication instances.
• For homogeneous migrations, SCT is generally not needed. For heterogeneous migrations, SCT is essential.
SCT Mapping Rules and Conversion Settings
SCT allows you to define custom mapping rules to control how objects are converted:
• Change schema names, table names, or column names during conversion
• Override default data type mappings
• Apply transformation rules for specific objects
• Filter which objects to include or exclude from conversion
Exam Tips: Answering Questions on Schema Conversion and Evolution with AWS SCT and DMS
1. Heterogeneous vs. Homogeneous: If a question involves migrating between different database engines, SCT is almost always required for schema conversion. If migrating between the same engine, DMS alone is typically sufficient. This is one of the most commonly tested distinctions.
2. SCT Assessment Reports: Know that SCT generates assessment reports that show the percentage of automatically convertible objects and the estimated effort. Questions may ask which tool provides migration complexity analysis — the answer is SCT.
3. DMS Does NOT Convert Stored Procedures: Remember that DMS only creates basic table structures. It does not convert stored procedures, functions, triggers, or secondary indexes. SCT handles these. Expect questions that test whether you know the boundary between what SCT and DMS handle.
4. CDC and Schema Changes: Understand that DMS has limited DDL handling during CDC. If a question asks about handling schema changes during ongoing replication, know the task settings (HandleSourceTableAltered, etc.) and that complex DDL changes may require task restart.
5. Data Warehouse Migrations: For questions involving migration from Teradata, Netezza, or Oracle Data Warehouse to Amazon Redshift, remember that SCT supports these conversions and can use data extraction agents for large-scale data movement.
6. Application SQL Conversion: SCT can scan and convert embedded SQL in application code. If a question mentions converting application-level SQL queries to a new database dialect, SCT is the answer.
7. Schema Registry for Evolution: If questions reference schema evolution in streaming or data lake scenarios (Kafka, Kinesis, Glue), the AWS Glue Schema Registry is the relevant service, not SCT. Distinguish between migration-time schema conversion (SCT) and runtime schema evolution (Schema Registry).
8. Order of Operations: The correct order is always: SCT first (convert and apply schema), then DMS (migrate data). If a question presents options about the sequence of migration steps, ensure SCT schema conversion precedes DMS data migration.
9. SCT and S3/Redshift: SCT can convert schemas for migrations targeting Amazon Redshift and can also be used to convert schemas when migrating data to Amazon S3 as a data lake target. Know that DMS can write to S3 in CSV or Parquet format.
10. Elimination Strategy: If an answer option suggests using DMS alone for a heterogeneous migration involving stored procedures, eliminate it. If an option suggests using SCT for data movement, eliminate it. SCT = schema, DMS = data. This simple rule resolves many exam questions quickly.
11. Replication Instance Sizing: DMS uses replication instances to perform migrations. For questions about performance issues during migration, consider replication instance sizing, multi-AZ deployment, and parallel load settings. SCT runs on the client side and does not require cloud compute resources for schema conversion itself (except for data extraction agents).
12. Watch for Distractors: Exam questions may include AWS Glue ETL, Amazon EMR, or other services as answer choices. For database-to-database schema conversion, SCT is the primary tool. Glue ETL is for data transformation in ETL pipelines, not for database schema conversion.
