2.4 Application, API & Managed File Ingestion

2.4 Application, API & Managed File Ingestion

Not every source is a stream or an S3 bucket. DEA-C01 scenarios include SaaS applications, partner file protocols, database drivers, and HTTP APIs. The design goal is to land a complete, auditable raw copy without overwhelming the source or hiding partial failures.

Amazon AppFlow

Amazon AppFlow moves data between supported SaaS applications and AWS services through managed connectors. A flow identifies a source, destination, trigger, field mappings, filters, and optional validations or transformations. Depending on the connector, a flow can run on demand, on a schedule, or in response to an application event.

AppFlow is a strong choice when the exact source and destination are supported and the requirement is mostly mapping and transfer. It is not a universal replacement for Glue or Lambda: custom protocols, complex joins, and arbitrary libraries still require code. Protect connector credentials, choose an incremental field when supported, and send rejected records or execution details to an observable location.

Consuming an external API

An API extractor is a state machine even if each request is stateless. It must remember a cursor, page token, or high-water mark and advance that state only after durable output is committed.

1. Retrieve the credential from Secrets Manager or use a temporary role-based mechanism.
2. Request one page with a bounded timeout.
3. Validate the HTTP status and response schema.
4. Write the unmodified response plus request time, source, and cursor metadata to a raw S3 prefix.
5. Persist the next cursor with a conditional update.
6. Continue until the page is empty or the provider signals completion.

Use exponential backoff with jitter for transient 429 and 5xx responses, and honor Retry-After when the provider supplies it. Do not retry authentication or schema errors indefinitely. Reserve concurrency or place requests behind SQS so a Lambda scale-out does not create a retry storm against the provider.

For an API you expose, Amazon API Gateway can authenticate, validate, throttle, and route calls to Lambda or another backend. A synchronous API should quickly accept and durably enqueue long work rather than hold a client connection while a multi-minute ETL job runs. Return a request ID and expose status separately.

SDK calls and data APIs

AWS SDKs implement signed requests, pagination helpers, retries, and service-specific APIs. Use paginators rather than assuming one response is complete. Services such as the Redshift Data API allow SQL submission without a persistent client connection; execution is asynchronous, so store the statement ID, poll or react to completion, and retrieve results in pages.

An IAM permission to call the API does not grant every downstream data permission. For example, a Redshift Data API caller also needs an appropriate database identity and database privileges.

JDBC and ODBC sources

Java Database Connectivity (JDBC) and Open Database Connectivity (ODBC) provide driver-based access to relational and other tabular systems. Connectivity requires more than a connection string:

• Route tables, security groups, network ACLs, DNS, and possibly Direct Connect or VPN must allow the path.
• A source firewall or database may require an IP allowlist. Prefer stable private network ranges or controlled egress rather than brittle worker IP lists.
• Store credentials outside job arguments and rotate them safely.
• Partition parallel reads on a selective numeric or date column, but cap concurrency so extraction does not exhaust source connections or I/O.
• Use predicate pushdown and incremental watermarks to reduce transfer volume.

Glue connections can centralize JDBC properties and network settings. A job that accesses a VPC source needs available subnet IP addresses and suitable security-group rules for the Elastic Network Interfaces it creates.

AWS Transfer Family

Transfer Family provides managed endpoints for partner protocols such as SFTP, FTPS, FTP, and Applicability Statement 2, backed by Amazon S3 or Amazon EFS. It is useful when a partner cannot change its file-transfer client. Identity can come from a supported managed or custom provider, and each user can be scoped to a logical directory.

Landing a file is only the ingestion boundary. Use S3 events or EventBridge to start validation after upload completion, quarantine files with bad checksums or schemas, and record the partner, logical file date, and immutable object version. For protocols without an atomic rename convention, agree on a completion marker so processing does not read a partial upload.

Source protection and auditability

Throttle at both worker and account levels. CloudWatch should show request rate, 429 count, latency, rows received, cursor age, and rejected records. Store a manifest that connects each source page or partner file to its S3 object and downstream run. The best ingestion design is restartable: after a failure, it can resume from the last committed cursor without omitting or duplicating data.