Skip to content

Skill 1.1.6: Merge data from multiple sources (for example, by using programming techniques, AWS Glue, Apache Spark)

Question 1 - AWS Glue Job Strategy for RDS + S3 Merge

A large e-commerce company stores customer transaction records in Amazon RDS for PostgreSQL and user behavior clickstream data in an Amazon S3 data lake (partitioned by date, stored as Apache Parquet). The ML team needs to build a unified dataset for training a product recommendation model.

The merged dataset must:

  • Join transactions with clickstream events on user_id
  • Be refreshed automatically every night
  • Minimize infrastructure management overhead

Which solution should the ML engineer implement?

A) Write a Python script using pandas that runs on a manually provisioned Amazon EC2 instance with a cron job to execute the merge nightly and write results back to S3.

B) Create an AWS Glue ETL job using Apache Spark that reads from the RDS JDBC connection and S3 data lake, performs the join, and writes the output to S3 in Parquet format. Schedule the job using AWS Glue Triggers to run nightly.

C) Use Amazon EMR with a long-running Apache Spark cluster that polls the RDS database and S3 bucket continuously for new data and performs the merge in real time.

D) Export all RDS tables to S3 using the AWS Database Migration Service (DMS) continuous replication task, then use Amazon Athena federated queries to join the two sources directly at query time without pre-processing.

Click to reveal Answer & Explanation

Correct Answer: B

Correct Option Analysis

  • B is Correct: AWS Glue ETL jobs run on serverless Apache Spark, so there is no cluster to provision or manage. The RDS JDBC connection and S3 as a native source are both supported. AWS Glue Triggers automate the nightly schedule without external orchestration. Writing output in Parquet optimizes the resulting dataset for downstream analytic reads (column pruning) and is the least operational overhead option that satisfies all constraints.

Distractor Analysis

  • A is Incorrect: Using pandas on a manually provisioned EC2 instance with a cron job introduces high operational overhead (patching, scaling, job monitoring). pandas is also memory-bound and fragile when joining large volumes of transaction and clickstream data, plus schemas are not cataloged automatically.
  • C is Incorrect: A long-running Amazon EMR cluster for a nightly batch job is cost-inefficient and contradicts the requirement to minimize infrastructure management. Standing up an EMR cluster is unnecessarily heavy for a scheduled join. Spark Structured Streaming on EMR would also not be the simplest fit for a batch refresh.
  • D is Incorrect: DMS continuous replication adds unnecessary moving parts to duplicate the RDS tables into S3, and Athena federated queries would still need to combine two different sources with non-trivial latency, not pre-produce a training-ready dataset. This design also shifts the heavy join cost to every read, rather than once per night.

Key Takeaway: For scheduled batch merging of relational and object storage sources with minimal operations, AWS Glue ETL (serverless Spark) + Glue Triggers is the default best practice.

Question 2 - Authoritative Source Conflict Resolution in Glue Merge Job

A financial services company is consolidating customer profile data from two independent systems:

  1. An Amazon RDS for MySQL customer master table (sales CRM).
  2. An Amazon S3 folder containing compliance KYC exports (CSV, refreshed weekly).

The teams anticipate that phone_number and address fields may differ between the two systems for the same customer. The merged output will be consumed by a fraud detection model where consistency and auditability are critical.

What is the MOST appropriate approach before writing the final merged dataset? (Select ONE)

A) Use an AWS Glue Studio visual ETL job to perform an inner join on customer ID, and select whichever value appears first in the join order.

B) Define explicit conflict resolution rules that designate one source as authoritative per field, implement them as Apache Spark transformations in the AWS Glue job, and log every conflict to Amazon CloudWatch for auditability.

C) Use the AWS Glue DataBrew capability to visually profile the data and let it automatically drop conflicting rows based on freshness.

D) Load both sources into separate Amazon DynamoDB tables, then use DynamoDB transactions to perform conditional updates that automatically resolve conflicts in favor of the most recently written record.

Click to reveal Answer & Explanation

Correct Answer: B

Correct Option Analysis

  • B is Correct: When merging overlapping customer records from different sources, field-level conflict resolution rules must be explicitly defined. Implementing these as Spark transformations in AWS Glue makes the logic code-defined, reproducible, and auditable. Logging conflicts to CloudWatch maintains a compliance trail. This directly addresses the "which source wins" question required for a fraud detection model where consistency matters.

Distractor Analysis

  • A is Incorrect: Relying on join order (which row appears first) is non-deterministic and violates the auditability and consistency requirements. There is no explicit rule about why one phone number or address was chosen over another.
  • C is Incorrect: AWS Glue DataBrew is a visual data preparation service for profiling, cleaning, and normalizing data. It does not provide automated conflict resolution based on "freshness." Dropping conflicting rows would reduce the training data unnecessarily and could introduce bias.
  • D is Incorrect: DynamoDB transactions only enforce atomicity and serializability among items in DynamoDB tables. They do not resolve conflicts based on a business-defined authoritative source. Also, having to manage two extra DynamoDB tables adds operational overhead without solving the true data governance problem.

Key Takeaway: When merging data across systems, always define and codify which source is authoritative per field; rely on deliberate Spark transformations — never on row order or implicit "last write wins" logic.

Question 3 - File Format Selection for Merged Dataset Output

A data engineering team has deployed an AWS Glue job that merges clickstream logs (JSON, raw) with product metadata (CSV, raw) from S3 into a curated dataset. The downstream workload is a recurrent training job that reads only:

  • user_id (string)
  • timestamp (timestamp)
  • product_category (category string)

However, the merged schema contains 80 additional columns that are rarely used for training.

Which format should the team write the merged output to in order to minimize future read cost and latency for the training job? (Select ONE)

A) Uncompressed JSON files in Amazon S3 with one document per record.

B) Apache Parquet files partitioned by date and stored in Amazon S3.

C) CSV files grouped by user_id and sorted by timestamp.

D) A single large ZIP archive containing all merged data.

Click to reveal Answer & Explanation

Correct Answer: B

Correct Option Analysis

  • B is Correct: Parquet is a columnar format that enables column pruning. A query engine like Amazon Athena or Apache Spark can read only the storage blocks for user_id, timestamp, and product_category, skipping the other 80 columns entirely. Partitioning by date also enables partition pruning. This dramatically reduces the amount of data scanned from S3, minimizing cost and latency. Parquet also typically uses compression, further reducing storage and networking overhead.

Distractor Analysis

  • A is Incorrect: Uncompressed JSON is row-oriented and stored as text. Query engines must read and parse the entire document for every record, including all 80 unused columns. This inflates I/O and parsing time.
  • C is Incorrect: CSV is also row-oriented text, so the query engine cannot skip the unused columns. Sorting and grouping by user_id may help with specific point lookups, but does not avoid full-row scans for the training workload.
  • D is Incorrect: A single ZIP archive is not queryable without first downloading and decompressing the whole file. It completely prevents column pruning and parallelism, drastically increasing read latency.

Key Takeaway: For analytical/training workloads that read a subset of columns across many rows, columnar formats like Apache Parquet are optimal due to column pruning and efficient compression.

Question 4 - Glue Crawler and Catalog Reconcile Before Merge

A media company ingests raw JSON files and Parquet files from two different S3 buckets. Both buckets contain event data, but the schemas have diverged: the JSON source has a field named user_id of type string, while the Parquet source has a field named user_id of type long. There are also extra nested fields present in the JSON source only.

The ML engineer needs to merge these into a single dataset for model training, and wants to ensure that the schema is correctly discovered and available to downstream AWS services (Athena, Glue, SageMaker). Which steps should the engineer take? (Select TWO)

A) Run an AWS Glue crawler on both S3 buckets to register table definitions in the AWS Glue Data Catalog.

B) Manually edit the JSON files in place to add a user_id long field before running the merge job.

C) Configure an AWS Glue ETL job that converts user_id to string in the Parquet source and flattens or drops consistent fields as necessary, then writes a single dataset with a consistent schema.

D) Use Amazon S3 batch operations to merge the files physically at the object level before any crawler runs.

E) Set up a long-running Amazon EMR cluster with Apache HBase to store the merged tables in real time.

Click to reveal Answer & Explanation

Correct Answers: A and C

Correct Options Analysis

  • A is Correct: AWS Glue crawlers automatically discover schemas from diverse data sources (JSON, Parquet) and populate the AWS Glue Data Catalog with consistent table definitions. This makes the sources queryable and mergeable by downstream services.
  • C is Correct: An AWS Glue ETL job is the correct way to resolve schema conflicts (string vs. long) by casting user_id to a consistent type and aligning the field set. The ETL job writes a single dataset with a unified schema, which is then registered in the Data Catalog.

Distractor Analysis

  • B is Incorrect: Manually altering source JSON files at the object level is dangerous, error-prone, and does not scale. It also defeats the purpose of the merge pipeline, which should resolve schema conflicts programmatically.
  • D is Incorrect: S3 batch operations can copy, tag, or delete objects, but cannot intelligently merge schemas at the object level. This is an invalid approach to schema unification.
  • E is Incorrect: Running a long-lived Amazon EMR HBase cluster is unnecessary for a batch merge. It adds high operational cost and complexity compared to a serverless AWS Glue job.

Key Takeaway: Use AWS Glue crawlers to catalog schema definitions automatically, and AWS Glue ETL jobs to cast or drop fields, resolving conflicts to produce a consistent merged schema.

Question 5 - Selecting a Merge Framework

A data team currently runs an on-premises Apache Spark cluster that merges several TB of data from Oracle databases and HDFS into a unified dataset for an ML pipeline. They are migrating this workload to AWS and want the fastest possible time-to-value while keeping Apache Spark semantics, but with zero cluster management overhead. The amount of data is expected to double by the end of the year.

Which AWS service is the MOST suitable replacement for the on-premises Apache Spark merge jobs? (Select ONE)

A) Amazon EMR with an auto-scaling group of EC2 instances running Apache Spark.

B) AWS Glue ETL jobs with Apache Spark.

C) A single large EC2 instance running Apache Spark locally.

D) Amazon Kinesis Data Analytics for Apache Flink.

Click to reveal Answer & Explanation

Correct Answer: B

Correct Option Analysis

  • B is Correct: AWS Glue ETL is built on Apache Spark and is serverless. It scales automatically with data volume and does not require manual cluster provisioning, patching, or decommissioning. It handles both relational sources (via JDBC) and object storage (S3). This provides the management-free, Spark-compatible migration path the team requires.

Distractor Analysis

  • A is Incorrect: Amazon EMR running Apache Spark on EC2 instances is a valid Spark solution on AWS, but it requires managing clusters, choosing instance types, and configuring auto-scaling. This violates the "zero cluster management overhead" requirement.
  • C is Incorrect: A single large EC2 instance running Spark locally does not scale well for TB-scale merges, and it requires managing the OS, Spark installation, and the single instance itself. It is not a robust migration target.
  • D is Incorrect: Amazon Kinesis Data Analytics for Apache Flink is designed for streaming, stateful event processing, not batch ETL batch merges of relational and object data. Using Flink for a scheduled batch job is an anti-pattern.

Key Takeaway: For migrating on-prem Spark ETL to AWS with zero cluster management, AWS Glue (serverless Spark) is the default service.

Question 6 - Merging with Streaming and Batch Layers

A ride-sharing platform needs to build a single training dataset for a driver churn prediction model. The dataset must combine:

  1. Historical trip data stored in S3 as gzipped JSON (batch, updated daily).
  2. Real-time driver event data (e.g., driver_logged_in, trip_offer_received) from Amazon Kinesis Data Streams.

The ML team needs to merge these two sources into a feature set with low latency for retraining every hour. Which architecture is the most appropriate? (Select ONE)

A) Use an AWS Glue ETL job scheduled every hour that reads from S3 and directly queries Kinesis to produce the feature set.

B) Use AWS Glue ETL to create a batch feature layer in S3 from historical data, then use an Amazon Managed Service for Apache Flink application to consume Kinesis Data Streams and join against the batch layer to produce a continuous feature set.

C) Store both sources in Amazon RDS for PostgreSQL with the pgvector extension and use SQL joins.

D) Use Amazon Athena federated queries over Kinesis Data Streams to join the data at query time.

Click to reveal Answer & Explanation

Correct Answer: B

Correct Option Analysis

  • B is Correct: This architecture cleanly separates batch and streaming. AWS Glue builds the historical trip feature layer into S3 (or a cache). An Amazon Managed Service for Apache Flink application consumes real-time Kinesis events and performs the join against the batch feature layer (e.g., using broadcast state or a side input) to emit a continuous, up-to-date feature set. This is the standard pattern for combining large historical data with a live stream and gives the hourly refresh latency required.

Distractor Analysis

  • A is Incorrect: AWS Glue ETL jobs are designed for batch processing. They cannot natively consume Kinesis Data Streams as a source. This option incorrectly mixes batch and streaming paradigms.
  • C is Incorrect: Moving streaming data into RDS and joining in SQL is not scalable for high-volume event streams. The pgvector extension is irrelevant for this use case. Latency and throughput requirements would be severely constrained by the relational database.
  • D is Incorrect: Amazon Athena cannot query Kinesis Data Streams directly via federated queries. Kinesis is a transient stream, not a queryable data source for Athena. This is a non-existent feature combination.

Key Takeaway: For merging batch data with streaming events, pair AWS Glue batch ETL with a Flink streaming application on Amazon Managed Service for Apache Flink — one layer is not a substitute for the other.

Question 7 - File Format of Two Sources for Merge Efficiency

A healthcare analytics startup stores patient vitals in Amazon Redshift and wearable device data in Amazon S3 as Avro files. The ML team needs to build a training dataset by joining the two on patient_id and timestamp.

The job is written in Apache Spark (AWS Glue). The S3 Avro files are highly nested and contain many fields. The team wants to optimize the Glue ETL job to read only needed columns and rows from the S3 source during the merge to reduce execution time and cost.

Which combination of actions should the team take? (Select TWO)

A) Convert the Avro files to Parquet with Snappy compression in a staging area first using a Glue ETL job.

B) Use a pushdown predicate on the S3 files to read only the required patient_id and timestamp columns.

C) Store the Avro files in a single, uncompressed raw format to simplify reading.

D) Use the AWS Glue DynamicFrame applyMapping to drop unused fields and use Spark partition pruning by adding a date partition to the staging S3 path before the main merge job.

E) Read the Avro files directly into a pandas DataFrame inside the Glue job for maximum flexibility.

Click to reveal Answer & Explanation

Correct Answers: A and D

Correct Options Analysis

  • A is Correct: Avro is row-oriented and does not support efficient column pruning (it has schema projections but still reads complete blocks). Converting Avro to Parquet (columnar) in a staging area first allows the subsequent merge job to read only needed columns, greatly reducing I/O. Snappy compression further reduces data scanned.
  • D is Correct: applyMapping in AWS Glue allows the ETL job to explicitly select and cast only the needed fields, reducing the in-memory footprint. Adding a date partition to the staging S3 location enables partition pruning, so the merge job reads only relevant date folders rather than scanning the whole data lake.

Distractor Analysis

  • B is Incorrect: While pushdown predicates are generally good, the phrase "pushdown predicate" usually refers to row-level filtering (e.g., WHERE clauses). It does not by itself enable efficient column pruning on Avro files (Avro is row-oriented). Column pruning works best with columnar formats like Parquet.
  • C is Incorrect: Storing Avro files uncompressed and raw actually worsens I/O cost and read time. It does not help with either column or row skipping.
  • E is Incorrect: pandas DataFrames are not distributed and will fail or be extremely slow on large, nested Avro datasets. Using Spark (DynamicFrame or DataFrame API) is the correct approach in AWS Glue.

Key Takeaway: For efficient joins in Spark on data lakes, land a staging layer in a columnar format (Parquet) with meaningful partitions, and use DynamicFrame mappings to trim unused fields in the ETL job.

Question 8 - Choice of Storage Service for Intermediate Merge Results

A machine learning team is running a distributed training job on Amazon SageMaker across 10 instances. The job requires a shared, intermediate feature cache that was produced by a merge job earlier in the pipeline. All 10 instances must concurrently read the cache files from the same file path namespace with low, consistent latency.

Which storage service is the most appropriate for this merged intermediate result? (Select ONE)

A) Amazon Elastic Block Store (Amazon EBS) with io2 Block Express volumes.

B) Amazon S3 bucket with Intelligent-Tiering enabled.

C) Amazon Elastic File System (Amazon EFS).

D) Amazon DynamoDB with on-demand capacity.

Click to reveal Answer & Explanation

Correct Answer: C

Correct Option Analysis

  • C is Correct: Amazon EFS is a POSIX-compliant shared file system that can be mounted by many EC2 instances (including SageMaker training nodes) simultaneously. It provides a single shared file namespace and stable, low-latency file access. This exactly matches the requirement for concurrent reads across 10 training instances.

Distractor Analysis

  • A is Incorrect: EBS volumes are block storage that can only be attached to a single instance at a time (except for limited multi-attach features on certain volume types). They do not provide a shared namespace across 10 instances. Managing 10 separate EBS copies would be infeasible.
  • B is Incorrect: S3 is object storage, not a POSIX file system. While SageMaker can read directly from S3, it does not present a mounted file path with file-level operations that a POSIX training job might expect. It also has higher GET latency compared to EFS for small repeated lookups.
  • D is Incorrect: DynamoDB is a key-value NoSQL store, not a file system. It cannot store arbitrary file caches and is not optimized for byte-level or file-level reads from a training loader.

Key Takeaway: For distributed training jobs needing a shared file namespace with POSIX semantics across multiple nodes, Amazon EFS is the recommended storage layer.

Question 9 - Data Catalog Role in Glue Merge

A large retail chain has multiple source systems that get loaded into an Amazon S3 data lake. The data engineering team runs multiple AWS Glue ETL jobs to merge and curate data. They notice that their merge jobs often fail or produce inconsistent outputs because one job expects a table schema that another job changed.

Which AWS service feature should be used to provide a centralized, versioned, and discoverable schema for all source and merged tables? (Select ONE)

A) AWS Glue Data Catalog together with AWS Lake Formation for governed access.

B) Manually maintained schema files in a dedicated S3 bucket that every job reads before running.

C) AWS Identity and Access Management (IAM) policies that enforce schema versions on each Glue job.

D) Amazon DynamoDB with a table called "schemas" storing JSON schema definitions.

Click to reveal Answer & Explanation

Correct Answer: A

Correct Option Analysis

  • A is Correct: The AWS Glue Data Catalog is the central metadata repository for S3 data lake tables. It stores table definitions and schemas discovered by crawlers or created by ETL jobs. AWS Lake Formation can sit on top to provide centralized, fine-grained access control and schema governance, ensuring that all downstream jobs discover and use the correct table definitions.

Distractor Analysis

  • B is Incorrect: Manually maintained schema files in S3 do not provide automatic discovery, versioning, or management consistency. They are error-prone and require ad-hoc code to read and enforce.
  • C is Incorrect: IAM controls access to AWS resources, not schema validation or version enforcement. You cannot specify a schema version in an IAM policy.
  • D is Incorrect: A DynamoDB "schemas" table is a custom, home-grown metadata store. It lacks integration with query engines, crawlers, and Lake Formation, and would require substantial custom code to keep in sync.

Key Takeaway: Use the AWS Glue Data Catalog (with Lake Formation for governance) as the single source of truth for table and schema definitions across all Glue jobs in a data lake.

Question 10 - Merge Strategy Based on Data Access Patterns

A genomics research institute stores raw sequencing data in Amazon S3 as FASTQ files and clinical records in Amazon RDS for Oracle. The ML team needs to frequently join a small subset of columns (patient ID, age, gene variant) from both sources for each training run, but the clinical database is constantly being updated.

The team wants to minimize data movement and ensure that training jobs always use the most current versions of clinical records. Which solution is the most operationally efficient? (Select ONE)

A) Use AWS Glue ETL to nightly extract all Oracle tables to Parquet in S3, then enable S3 Object Lambda to merge with FASTQ data at query time.

B) Configure an AWS Glue JDBC connection to Oracle with a read-only user and use an AWS Glue ETL job that runs at the beginning of each training pipeline to pull only the required columns and join with S3 data.

C) Set up Amazon MSK to capture change data capture (CDC) from Oracle and stream all records into S3, then use a custom Lambda function to perform the join.

D) Use Amazon SageMaker Feature Store to ingest all FASTQ and Oracle data into an online store, then query it directly from the training code.

Click to reveal Answer & Explanation

Correct Answer: B

Correct Option Analysis

  • B is Correct: This solution directly addresses both constraints. AWS Glue JDBC connection can read live data from the Oracle RDS instance on-demand. The Glue ETL job can project (SELECT) only the needed columns from Oracle (reducing data movement), and when the job runs at each training pipeline start, it inherently pulls the most up-to-date clinical records. This avoids nightly duplication and uses serverless Spark for efficiency.

Distractor Analysis

  • A is Incorrect: Extracting all Oracle tables nightly to Parquet moves unnecessarily large volumes of data. S3 Object Lambda is for transforming objects on retrieval, not for performing arbitrary joins between RDS and FASTQ. Also, nightly extraction means the data is up to 24 hours stale.
  • C is Incorrect: Setting up Amazon MSK with CDC for Oracle is a heavy streaming infrastructure (connectors, topics, consumers) that is overkill when the training workflow is batch. The Lambda function merging FASTQ and CDC streams would be extremely complex and not cost-effective.
  • D is Incorrect: SageMaker Feature Store is designed for structured feature data, not raw FASTQ files (which are large genomic sequences). Ingesting all raw FASTQ data into an online Feature Store would be volume- and cost-prohibitive and does not align with its intended use.

Key Takeaway: For merging an on-prem-like relational source (RDS Oracle) that updates frequently with S3 data for batch training, a scheduled AWS Glue JDBC read with column projection is the simplest and most operationally efficient solution.