ETL Schema Mapper

Schema mapping defines how fields from a source system translate to a target system. It specifies: which source column maps to which target column, any data type conversions needed (e.g. VARCHAR → INTEGER), and transformation rules (uppercase, trim whitespace, cast to date). Good schema mapping documentation is essential for debugging data discrepancies and onboarding new engineers to a pipeline.

as-is: copy the value unchanged. uppercase/lowercase: standardize string casing. trim: remove leading/trailing whitespace. cast: convert data type (e.g. string '2024-01-15' to DATE). custom: apply a formula or expression (e.g. CONCAT(first_name, ' ', last_name)). null-replace: substitute NULL with a default value. format-date: reformat date strings (e.g. MM/DD/YYYY to YYYY-MM-DD).

Type mismatches are common between systems. Strategies: 1) CAST in SQL when loading into the warehouse. 2) Validate and reject at source with error logging. 3) Store everything as strings and cast at query time (flexible but slow). 4) Use explicit type converters in your ETL tool (dbt casts, Spark schema enforcement). Always test edge cases: empty strings cast to INT fail, trailing spaces in dates break CAST.

SQL SELECT (via dbt, Spark SQL, or direct DB): better for large datasets, runs in-warehouse/distributed, SQL is readable by analysts. Python pandas: better for complex logic that's hard in SQL (ML transformations, API enrichment, custom string parsing), works well for small-medium datasets. Many modern stacks use SQL for heavy lifting (dbt) and Python for edge cases and data quality checks.

For each field mapping document: source table/column, source data type, target table/column, target data type, transformation rule, example source value, example target value, nullable/not-null constraint, business logic notes (e.g. 'revenue is in cents, divide by 100'). This becomes your data dictionary and is critical for compliance, auditing, and troubleshooting data quality issues.