Data Ingestion and Transformation practice questions

AWS DMS change data capture (CDC) reads a relational database's transaction logs to stream ongoing changes to a target such as S3, Kinesis, or Redshift — the standard pattern for continuously replicating an operational database into a data lake.

A DMS full-load-and-CDC task first copies existing data, then continuously applies inserts, updates, and deletes by reading redo logs, which keeps the load on the live Oracle system minimal. Nightly Glue reloads are high-impact batch jobs that miss changes between runs, AppFlow targets SaaS APIs rather than on-premises databases, and DynamoDB Streams only exists for DynamoDB.

1. Classify the requirement: continuous replication of row-level changes (CDC), not periodic snapshots.
2. Choose AWS DMS, which supports Oracle as a source via transaction-log-based CDC.
3. Configure a full-load-and-CDC task so historical data is copied once and ongoing changes flow afterward.
4. Set Amazon S3 as the DMS target to land change records in the data lake.

Exam tip: For continuous, low-impact replication of a relational database's changes into AWS, use DMS with CDC.

DynamoDB Streams provides a time-ordered change log of item-level modifications, and Lambda event source mappings consume it automatically — the native change-driven ingestion pattern for DynamoDB.

Enabling the stream requires only a table setting, and the Lambda event source mapping handles polling, batching, and retries, so processing reacts to every create and update with zero application changes. Dual-writes require code changes and can drift out of sync, scheduled scans are costly and slow, and S3 notifications do not apply to DynamoDB.

1. Enable DynamoDB Streams on the table and pick a view type such as NEW_AND_OLD_IMAGES so processors see the changed data.
2. Create a Lambda function containing the processing logic.
3. Add an event source mapping from the stream to the function; Lambda polls shards and invokes the function with batches of change records.
4. Configure error handling on the mapping (retries, on-failure destination) so bad records do not block the shard.

Exam tip: To react to DynamoDB item changes without touching application code, pair DynamoDB Streams with a Lambda event source mapping.

Amazon Redshift COPY achieves its speed through massively parallel loading: each slice in the cluster can ingest a file simultaneously, so file layout in S3 directly determines load parallelism.

One large file forces effectively serial ingestion, while many similarly sized files loaded by a single COPY command spread the work across all slices. Concurrent INSERTs bypass the optimized bulk path, sequential COPY commands serialize the work, and COPY natively supports gzip so decompression is unnecessary.

1. Split the source data into multiple files of roughly equal size, ideally a multiple of the total slice count of the cluster.
2. Keep the files compressed (gzip is supported) to reduce network transfer from S3.
3. Issue a single COPY command with a common S3 prefix or a manifest file so Redshift loads all files in parallel.
4. Avoid single-row INSERT patterns for bulk data — reserve them for small trickle loads.

Exam tip: For fast Redshift ingestion, split input into many equal files and load them with one parallel COPY command.

Amazon AppFlow is the managed, no-code ingestion service for SaaS sources (Salesforce, ServiceNow, Slack, and others), moving data to AWS targets like S3 and Redshift on a schedule or in response to events.

AppFlow's built-in Salesforce connector plus scheduled flows delivers the nightly ingestion with pure configuration, satisfying both the no-custom-code and no-infrastructure constraints. Lambda and EMR both require custom integration code (and EMR adds cluster management), while DMS does not support SaaS APIs as sources.

1. Recognize the source as a SaaS application (Salesforce), which points to AppFlow rather than database-replication or generic ETL tools.
2. Create an AppFlow connection to Salesforce with the appropriate credentials and scopes.
3. Define a flow mapping Salesforce objects to an S3 destination, optionally with field mapping and filtering.
4. Set the flow trigger to a nightly schedule.

Exam tip: For scheduled, no-code ingestion from SaaS applications like Salesforce, reach for Amazon AppFlow.

Time-based job scheduling on AWS is a spectrum: EventBridge cron schedules for simple time triggers, Glue workflows or Step Functions for dependency chains, and MWAA (Airflow) for complex, code-defined orchestration.

With one job, no dependencies, and a fixed time, an EventBridge cron schedule is the minimal, serverless answer. MWAA carries continuous environment cost and administration for capability this task does not need, an EC2 cron host reintroduces server management, and a looping Step Functions machine misuses a workflow engine as a clock.

1. Assess workflow complexity: a single independent job needs a scheduler, not an orchestrator.
2. Create an EventBridge schedule with the cron expression cron(0 2 * * ? *).
3. Set the Glue StartJobRun action (or the Glue job target) as the schedule's target with an appropriate IAM role.
4. Reserve MWAA or Step Functions for pipelines with branching, dependencies, or cross-service coordination.

Exam tip: For a simple time-based trigger of one job, use an EventBridge cron schedule — save Airflow and Step Functions for real orchestration needs.

MWAA is managed open-source Airflow: it exists specifically so teams with an Airflow investment can keep their DAGs while offloading environment operations to AWS.

The two constraints — reuse existing Airflow DAGs and avoid managing Airflow infrastructure — intersect only at MWAA. EventBridge and Step Functions would both force a rewrite into different orchestration models, and self-managed Airflow on EC2 keeps the DAGs but also keeps all the operational burden.

1. Identify the migration constraint: hundreds of existing Airflow DAGs must run without rewriting.
2. Identify the operational constraint: no Airflow servers to manage.
3. Choose MWAA, which runs Apache Airflow compatibly as a managed environment.
4. Deploy DAGs by uploading them to the environment's S3 bucket, adjusting connections and operators for AWS services where needed.

Exam tip: When a team already lives in Airflow, MWAA delivers managed orchestration without a DAG rewrite.