Skill 1.1.6: Merge data from multiple sources (for example, by using programming techniques, AWS Glue, Apache Spark)
Task 1.1: Data Preparation for ML and AI – Collect and store data
Exam focus: Combine disparate sources into one training-ready dataset. Decide by scale, latency, source type, transformation complexity, and governance. The merge must preserve keys, time alignment, schema, and lineage.
Core Merge Concepts
- Business objective: Single trustworthy dataset for ML, avoiding training-serving skew and rework.
- Merge patterns: Joins (inner/outer/left/semi/anti), unions, aggregations, deduplication, entity resolution, incremental upserts.
- Conflict resolution: Declare authoritative source per field; normalize keys, timestamps, units, nulls, and PII.
- Output: Columnar Parquet/ORC, partitioned, compressed, cataloged. Use Iceberg/Hudi/Delta for ACID upserts.
Tool Selection Matrix
| Scenario | Best fit | Why | Watch-outs |
|---|---|---|---|
| Small, event-driven, low-latency | Lambda + Step Functions | Serverless, quick | Not for large joins |
| Serverless batch ETL | AWS Glue | Managed Spark, Data Catalog, bookmarks | DPU cost; not low-latency |
| Large-scale complex Spark | Amazon EMR / EMR Serverless | Full control, custom libs, scale | Cluster management; cost |
| ML-specific preprocessing | SageMaker Processing | ML-native, feature engineering | Not general ETL |
| Entity resolution / dedupe | AWS Entity Resolution | No-code matching across sources | Limited to matching |
| Feature consistency | SageMaker Feature Store | Online + offline stores, one definition | Not a general DB |
| Stream merge | Kinesis + Flink / MSK + Spark | Continuous ingestion | Checkpointing, backpressure |
AWS Glue for Merging
- Serverless ETL: Crawlers → Data Catalog → ETL jobs (Spark/Python shell/Ray) → Workflows/Triggers.
- DynamicFrame: Handles schema inconsistencies and choice types; convert to DataFrame for Spark SQL.
- Job bookmarks: Incremental S3 reads; JDBC needs custom watermark.
- Config: Worker type (G.1X/G.2X/G.4X/G.8X), auto-scaling, Flex, continuous logging, retries, timeout, max concurrency.
- Data quality: Glue Data Quality, DataBrew for visual prep.
- Anti-pattern: Crawler as transformer; full refresh when incremental works; no partition pruning.
Apache Spark / Amazon EMR for Merging
- When: Large joins, complex transformations, custom libraries, existing Spark skills.
- Performance: Partitioning, bucketing, broadcast joins for small tables, AQE, skew handling (salting), caching, minimize shuffles.
- EMR specifics: Instance fleets, Spot, EMRFS, managed scaling, EMR Serverless, EMR on EKS, Lake Formation integration, Kerberos.
- Anti-pattern: Single-node pandas for big data; Cartesian joins; ignoring skew; small files.
Data Quality, Schema, Incremental Merge
- Schema evolution: Glue Schema Registry, Data Catalog, Parquet/Avro/JSON. Handle new/missing columns.
- Dedup: Window-based ranking to pick latest record per key.
- Incremental: Glue Bookmarks, Delta Lake/Iceberg/Hudi merge-on-read vs copy-on-write.
- Data quality: Validate before merge; quarantine bad records; dead-letter queues.
Security & Compliance
- IAM roles for Glue/EMR/Lambda; least privilege.
- Encryption: KMS SSE-KMS at rest; TLS in transit.
- Network: VPC endpoints for S3, Glue, KMS; no public internet.
- Governance: Lake Formation fine-grained access; Macie for PII; Secrets Manager for JDBC creds; CloudTrail audit.
Cost & Performance Tuning
- Glue: DPU-hour; auto-scaling; Flex; bookmarks; partition pruning.
- EMR: Instance-hour; Spot; managed scaling; EMR Serverless.
- S3: Parquet/ORC + compression (Snappy/ZSTD); target 128MB–1GB files; compact small files; lifecycle policies.
- Partitioning: By date/entity; avoid high-cardinality over-partitioning.
Exam Gotchas
Glue crawler ≠ ETL. Crawlers only catalog schema; they do not transform or merge. Glue bookmarks are for S3 incremental reads; JDBC needs custom watermarking. DynamicFrame is for schema inconsistency; DataFrame is for Spark SQL performance. Parquet/ORC are columnar, not for row-level upserts; use Iceberg/Hudi/Delta. Feature Store answers identity/feature values; Vector store answers similarity. Lambda for small/event-driven; Glue for serverless batch; EMR for large-scale Spark control. Training-serving skew is prevented by one feature definition in Feature Store. S3 vector buckets for large, infrequent vector queries; OpenSearch for scalable vector search; pgvector for PostgreSQL-resident embeddings.
Quick Trade-offs
| Need | Choose | Avoid |
|---|---|---|
| Serverless batch ETL | AWS Glue | Lambda for large joins |
| Full Spark control | Amazon EMR | Glue for custom libs |
| Low-latency event transform | Lambda + Step Functions | Glue for per-event |
| Incremental upserts | Iceberg/Hudi/Delta on S3 | Parquet overwrite |
| Entity resolution | AWS Entity Resolution | Manual fuzzy matching at scale |
| Feature consistency | SageMaker Feature Store | Ad-hoc feature code |