Teradata to Hadoop migration and handling data with SCD Type 2?

Teradata to Hadoop migration with SCD Type 2 requires careful planning. Key steps: (1) Schema mapping—map Teradata tables to Hive/Spark equivalents; handle Teradata-specific functions. (2) SCD Type 2 logic—maintain current and historical rows using effective_date, effective_end_date, and is_current flag. In Spark: use MERGE (Delta) or upsert logic with window functions to detect changes. (3) Migration strategy—batch historical load first, then CDC for ongoing changes. Use staging tables to validate row counts and checksums before cutover. (4) Performance—partition SCD tables by effective_date; use bucketing for join keys. Example: For customer dimension, compare source hash with target; insert new versions and update is_current on previous row. Validate with reconciliation queries post-migration. Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.