Batch Ingestion Patterns: DMS, AppFlow, DataSync, and Transfer Family

Lesson 04 put a transactional table format on top of Amazon S3. This lesson is everything that happens before that: the byte-moving layer. A lakehouse with a perfect Iceberg silver layer and a badly chosen ingress service is a lakehouse with a two-week batch window, a duplicated bronze folder and a partner contract you cannot renegotiate.

The uncomfortable truth about batch ingestion is that the choice is almost never about throughput. Every service in this lesson can move the data. The choice is about who controls the source (you, a vendor API, a partner's SFTP client, a mounted share), whether changes must keep flowing, and how the bill is shaped: fixed hours versus gigabytes.

In this lesson you will:

• run the service-selection matrix, source type to service, and defend each row against the two runners-up;
• configure AWS DMS full load + CDC into Amazon S3: DataFormat=parquet, CdcMaxBatchInterval/CdcMinFileSize, IncludeOpForFullLoad, TimestampColumnName, TargetTablePrepMode, parallel load modes and GlueCatalogGeneration;
• name the S3-target limitations that decide whether your bronze layer is a clean landing or a duplicate generator;
• build AppFlow incremental pulls: schedules, events, filters, offsets and the 15 GB Salesforce ceiling;
• place DataSync agents, choose Basic vs Enhanced, schedule TransferMode=CHANGED runs and read the verification story;
• stand up Transfer Family endpoints, workflows and connectors, and price them per protocol-hour;
• use S3 File Gateway for hybrid NFS/SMB writes and know its durability gap;
• state the Snowball Edge wind-down dates correctly (Snowcone 2024, new customers 2025-11-07, EOS 2026-12-31);
• apply the four idempotent landing patterns: deterministic keys, manifests, atomic promote, MERGE-from-staging;
• compare fixed-hour versus per-GB cost shapes before you commit to an architecture;
• walk a worked DMS → bronze → Glue → Iceberg-silver flow end to end;
• study two named AWS customer cases (FINRA, Transport for NSW) and a sourced October 2026 update box;
• practise with 13 exam-style questions plus three interactive checks.

---

1. Service selection: source type to service

1.1 The matrix

This is the whole lesson in one table. Every row is a constraint, not a preference: read the left column first.

1.2 The decision flow

Two rules the flow encodes, both of which are examinable:

1. Copy versus serve. DataSync copies from a location to a location and finishes. File Gateway serves a live share whose writes happen to land in S3. They solve different halves of "the data is on NFS".
2. Protocol versus API. Transfer Family preserves a protocol the partner controls; AppFlow preserves a vendor's API with its rate limits and pagination. Neither substitutes for the other: AppFlow has no SFTP connector, and Transfer Family cannot page through Salesforce.

• 📚 Did you know? DMS names its S3 output mechanically: full-load files carry an incremental hexadecimal counter (LOAD00001.csv, LOAD00002.csv, …) while CDC files are named with timestamps (20141029-1134010000.csv), and every non-empty source table gets its own folder under the target path (AWS DMS S3 target docs, accessed 2026-10). That naming scheme is why an idempotent landing can be reasoned about at all; you can tell a load file from a change file from the key alone.

---

2. AWS DMS: full load + CDC into Amazon S3

2.1 The three task types, restated for the lakehouse

For a lakehouse the target is not another database; it is a prefix on S3, which is where this differs from every migration tutorial you have read.

2.2 S3 target settings that actually matter

AWS documents the S3 target settings you will use in production. Grouped by purpose (AWS DMS S3 target docs and S3Settings API reference, accessed 2026-10):

2.3 The full-load + CDC timeline on S3

Two things about S3 change the picture versus a database target:

• S3 has no primary keys, no updates and no referential integrity (AWS re:Post knowledge center, 2026-02-09). CDC files are therefore event files, not table states. Deduplication and ordering are your job downstream, which is exactly what TimestampColumnName and the op column are for.
• Transaction order is not preserved by default. The docs state that "by default for CDC, AWS DMS stores the row changes for each database table without regard to transaction order." For CSV you may set PreserveTransactions=true with CdcPath; for Parquet you add TimestampColumnName and reconstruct order in the Glue job.

2.4 Parallel full load and target-table prep

That fifth bullet is the single most important sentence in this lesson: bronze is a schema-on-read surface. Old files keep old shapes forever. Only an Iceberg silver layer plus a Glue catalog entry restores schema-on-write semantics (lesson 04); which is precisely the flow in §11.

2.5 Worked example E1: Athena-friendly CDC sizing (as of Oct 2026)

AWS's own SQL Server-to-data-lake step-by-step tells you the two settings and the reason:

"Because we plan to use Amazon Athena to query data from our Amazon S3 bucket, we need to make sure that our target file size is at least 64 MB. Specify the following endpoint settings: CdcMaxBatchInterval=3600 and CdcMinFileSize=64000." (AWS DMS SBS, accessed 2026-10)

The examinable chain: Athena wants ≥ 64 MB files → CdcMinFileSize=64000 KB and CdcMaxBatchInterval=3600 s → Parquet lands smaller than the target because of compression → so a naive "one file per hour" assumption is wrong for Parquet and right for CSV.

---

3. Amazon AppFlow: SaaS to S3 with incremental pulls

3.1 Connectors, triggers, and the incremental contract

AppFlow's source ConnectorType values include Salesforce, Slack, Servicenow, SAPOData (SAP), Zendesk, Marketo, Googleanalytics, Datadog, S3, Redshift, Snowflake, CustomConnector and EventBridge, among others; destinations for Salesforce flows include Amazon S3, Amazon Redshift, Amazon EventBridge, Snowflake and Zendesk (CloudFormation SourceFlowConfig docs and the Salesforce connector guide, accessed 2026-10).

3.2 Worked example E2: what a scheduled Salesforce pull costs (as of Oct 2026)

AWS's pricing page publishes the arithmetic rather than a rate table. From the us-east-1 and Frankfurt examples (accessed 2026-10): $0.001 per successful flow run, $0.02 per GB processed, and, critically, "Flow runs to check for new data will count towards your flow run costs, even if no new data is available."

Two design consequences fall out of the pricing page, not out of the feature list:

1. A poll cadence is a line item. Checking every five minutes costs 8,640 runs/month in a 30-day month, whether or not anything changed. Set the schedule from the freshness the consumer needs.
2. The 30-day first pull is a deduplication event. Your bronze layer receives a month of history on run one and deltas thereafter, so the landing must be idempotent (§9) or day-one bronze will double-write anything that also arrived through another path.

• 📚 Did you know? AppFlow bills the check, not just the change: AWS's own pricing page states that runs "to check for new data will count towards your flow run costs, even if no new data is available." For SAP flows AWS shows a concurrency multiplier instead, "you apply 9 concurrent processes … (1+9) x $0.001 (flow cost)", so parallelism multiplies run charges rather than saving them (AWS AppFlow pricing, accessed 2026-10).

---

4. AWS DataSync: agents, task modes, schedules, verification

4.1 Agent or agentless?

A DataSync task copies between two locations. Whether you need an agent is decided by where those locations live (AWS DataSync docs, accessed 2026-10):

Rules that bite in production:

• A location can hold up to four Basic-mode agents and up to four Enhanced-mode agents, and "All the agents associated with a location must be online before you can start your transfer task. If one of the agents is offline, you can't run your task." Multi-agent gives speed, not failover.
• Enhanced-mode agents and Basic-mode agents are not interchangeable for the same location.
• NFS defaults to version 4.1 (also 4.0 and 3.x) over TCP 2049 (plus 111 for v3); destination exports need no_root_squash, and DataSync does not copy NFSv4 ACLs.

4.2 Basic versus Enhanced task modes

Task options worth memorising: TransferMode=CHANGED, VerifyMode=ONLY_FILES_TRANSFERRED, ObjectTags=PRESERVE, LogLevel=TRANSFER, plus filters/manifests and optional deletion mirroring (delete in the destination what no longer exists in the source). Scheduling is hourly, daily or weekly directly in the console.

4.3 Worked example E3: 20 TB from NFS to S3, Basic vs Enhanced (as of Oct 2026)

Using the rates printed in AWS's own worked examples on the DataSync pricing page (accessed 2026-10):

Read the trap before you copy anything. AWS's Storage Blog warns that objects seeded into a bucket by other tools lack DataSync metadata, so DataSync performs HEAD + GET + an in-place copy, and objects in archive storage classes cannot be GET at all, forcing a full re-transmission over the wire (plus a new version if versioning is on). The blog's advice: "use DataSync to perform the initial transfer to S3 whenever possible" and consider suspending S3 versioning on the bucket. Do the first sync with DataSync into an empty bucket.

• 📚 Did you know? DataSync's "changed since last run" detection depends on DataSync's own metadata. AWS's blog states that DataSync "automatically copies each file as a single S3 object in a 1:1 relationship, and preserves POSIX metadata on the source file as Amazon S3 object metadata", and that objects uploaded by other tools trigger the HEAD+GET+COPY path (AWS Storage Blog, 2021-08-24). A hybrid pipeline where a partner's SFTP feed and DataSync both write one prefix will therefore pay for re-copies forever.

---

5. AWS Transfer Family: partner protocols into the lake

5.1 What it is

Transfer Family is a fully managed endpoint speaking SFTP (v3), FTPS, FTP, AS2 or a browser, backed by Amazon S3 or Amazon EFS, "supports up to 3 Availability Zones and is backed by an auto scaling, redundant fleet."

5.2 Worked example E4: the partner feed bill (as of Oct 2026)

---

6. Amazon S3 File Gateway: hybrid NFS writes that land in S3

An S3 File Gateway appliance (VM, hardware or EC2) exports NFS v3/v4.1 or SMB v2/v3 shares; "Files written to a file share become objects in Amazon S3, with the path as the key. There is a one-to-one mapping between files and objects."

The durability gap is the operational point: writes are durable only after the cache flushes, so AWS instructs you to "make sure that the CachePercentDirty CloudWatch metric is 0" before stopping the host that runs the gateway. And with versioning on, a single file write creates two object versions: one for the data, one for the metadata update.

When to pick File Gateway over DataSync: if an application must keep writing through a POSIX path, it is File Gateway. If you need to copy a tree on a schedule, it is DataSync. Using DataSync to fake a writable share, or File Gateway to fake a migration, both produce the wrong failure mode.

---

7. Offline bulk: the Snowball Edge pattern (and its 2026 status)

Teach the pattern, a shipped disk that lands verified bytes in S3, but teach the product status correctly, because most content on the open web is now stale.

Device and price anchors for anyone maintaining an existing deployment (AWS Snowball pricing, us-east-1, accessed 2026-10): Storage Optimized 210 TB usable; On-Demand Job Fee up to 100 TB (includes 15 days) $1,800; 101 TB: 210 TB $3,200; $250 per day beyond 15 days; $9,885 per month; 1-year commitment upfront $97,268; S3-compatible on-device storage $0.01 per GB-month or $0.12 per GB-year. Compute Optimized reaches 104 vCPUs, 416 GB memory and 28 TB of dedicated NVMe SSD.

• 📚 Did you know? The offline pattern outlives the product. With Snowcone gone and Snowball Edge restricted, AWS's own migration note points at DataSync for online transfers, Data Transfer Terminal for secure physical uploads, and partners: the architecture question ("how do 500 TB reach S3?") still has the same three answers, only the order of preference changed (AWS Snow device updates, 2024-11-12; Snowball Edge availability change docs, accessed 2026-10).

---

8. Amazon S3 Batch Operations: reorganising what already landed

S3 Batch Operations is the repair tool for a landing that went wrong, and the migration tool for a prefix layout you are changing. One job runs one operation over a manifest:

• copy objects, update server-side encryption, set object tags or ACLs, initiate restores from S3 Glacier Flexible Retrieval, invoke an AWS Lambda function, Object Lock and Batch Replication;
• default manifest size 4 billion objects, rising to 20 billion for Copy, Object Tagging, Object Lock, Lambda invoke and Batch Replication;
• manifest format S3BatchOperations_CSV_20180820, commonly generated from S3 Inventory;
• price: $0.25 per job + $1.00 per million objects processed (AWS S3 pricing, accessed 2026-10).

8.1 Worked example E5: repairing a mis-tagged landing (as of Oct 2026)

---

9. Idempotent landing: the four patterns

Everything so far produces files. S3 will happily accept the same file twice, and AWS's own guidance is blunt: "Amazon S3 doesn't support primary keys, updates, or referential integrity." So the landing layer, the part between the mover and the table format, needs a retry-safe contract.

9.1 Ordering the steps

9.2 The MERGE that makes replay safe

From AWS's own CDC-into-Iceberg recipes (Big Data Blog, 2022-06-15 updated May 2023; 2023-02-14): DMS supplies the op attribute for incremental CDC data but not for the initial load unless you set IncludeOpForFullLoad=true; the Glue job deduplicates on the last_update_time column that TimestampColumnName added, then runs

9.3 Worked example E6: what a replay costs you (as of Oct 2026)

• 📚 Did you know? A DMS task restart can manufacture duplicates on its own. AWS's guidance for TargetTablePrepMode=DO_NOTHING is that a task stopping during full load "writes the full load files again when it restarts", so you must either switch to TRUNCATE_BEFORE_LOAD or manually delete the earlier LOAD* files before restarting (AWS re:Post, 2026-02-09). Idempotency is not only about your Glue job; it is about the mover's restart behaviour too.

---

10. Cost shape: fixed-hour versus per-GB

Before you commit to an architecture, work out which of these three shapes the bill takes. They behave completely differently as volume grows.

The worked example in §5.2 is the proof: a 500 GB partner feed on Transfer Family costs $241, of which $216 (89.6%) is protocol-hour. The same 500 GB through DataSync at the Basic rate would be 500 × $0.0125 = $6.25. Transfer Family is still the correct answer, because the partner is the constraint, but you should know you are buying protocol compatibility, not bandwidth.

---

11. Worked end-to-end: DMS → bronze → Glue → Iceberg silver

11.1 The flow

11.2 The outline, step by step (as of Oct 2026)

Every ingredient in that block is drawn from AWS's published patterns: Step Functions + SQS ordering with Glue jobs creating the silver layer (Big Data Blog, 2024-10-30), a Glue job bookmark driving incremental MERGE with last_update_time dedup (2023-02-14), and two DMS tasks splitting full-load and date-partitioned CDC folders for an Athena-native MERGE (2023-04-27).

2026 Updates (as of October 2026)

---

Real-World Case Studies

AWS publishes what these patterns look like in production. Every figure below is customer- or AWS-claimed and unaudited, with the source named so you can check it: the examinable point is the pattern, not the marketing.

Case A, FINRA: validating and cataloguing files at regulator scale

"We extended the data lake pattern with EMR, Hbase, and Amazon S3 to allow interactive random access queries across trillions of records spanning 600+ terabytes of data.": AWS Public Sector Blog, 2017-10-03

Read FINRA as a batch-ingestion case, not just an architecture case: files land, get validated before anything reads them (that is your manifest/verify step), get catalogued (your Glue entry), and only then are queried. The validation-before-read discipline is the same regardless of whether the mover is Transfer Family, DataSync or DMS.

Date honesty: these are 2016 to 2017 figures for a platform that has since changed: treat them as historical milestones, never as FINRA's current state.

Case B, Transport for NSW: 400+ ingestion workflows off licensed ETL

• 📚 Did you know? Transport for NSW's "30+ team members trained" is the operational half of the cost argument: AWS notes the team "was also able to distribute expertise across several existing team members" once the platform was Python-based Glue rather than a specialist tool (AWS case study, accessed 2026-10-11). The 80% cost cut is the headline; the removal of the key-person dependency is usually the reason such migrations stick.

---

Practice Questions