Data ingestion and transformation

Ingestion patterns

Start by classifying the workload.

• Frequency and history: a one-time historical load (full load) is usually separate from the ongoing feed (incremental or change data capture).
• Replayability: design so that raw data is kept and a failed run can be repeated. Kinesis keeps records 24 hours by default (up to 365 days with extended retention); Kafka keeps them as long as the topic retention allows; S3 raw zones keep them forever. Make transforms idempotent (writing the same input twice gives the same output) so a re-run doesn’t duplicate rows.
• Stateless vs stateful: a stateless step looks at one record (parse, filter, convert). A stateful step needs memory across records (windowed aggregates, joins, deduplication, sessionization). Stateful stream processing means Flink or Spark Structured Streaming with checkpoints.

Amazon Kinesis Data Streams

A managed, ordered, replayable log. You pick a capacity mode.

Key facts:

• The partition key is hashed to choose a shard. Ordering is guaranteed per shard, not across the stream. A skewed key (one popular customer) makes a hot shard: throttling with ProvisionedThroughputExceededException on that shard while others sit idle. Fix with a higher-cardinality key (add a random suffix), then split the hot shard, or switch to on-demand.
• Producers: the AWS SDK PutRecord/PutRecords, the Kinesis Producer Library (KPL) (batches and aggregates records, retries; adds latency from batching), the Kinesis agent, and CloudWatch Logs subscription filters. The SDK is simplest, the KPL gets the best throughput.
• Consumers: the Kinesis Client Library (KCL) (a worker per shard, leases and checkpoints in a DynamoDB table), Lambda, Firehose, Managed Service for Apache Flink, Glue streaming jobs.
• Fan-out: classic consumers share 2 MB/s per shard (and GetRecords allows 5 calls per second per shard). If five consumers each need the whole stream, use enhanced fan-out: each registered consumer gets its own 2 MB/s per shard pushed over HTTP/2 (SubscribeToShard) with about 70 ms latency. Fan-in is the opposite: many producers writing into one stream, which is just many producers sharing the shard capacity.
• Retention: 24 hours default, up to 365 days. Extended retention is what makes replays possible.
• Resharding: split (more capacity) or merge (less), for provisioned streams.
• Record data blob is up to 1 MB.

Amazon Data Firehose

A delivery service, not a log. It buffers records and writes them to a destination with no consumer code.

• Sources: direct PUT, a Kinesis data stream, MSK, CloudWatch Logs, WAF, IoT and others.
• Destinations: S3, Redshift (via S3 and COPY), OpenSearch, Splunk, HTTP endpoints, Apache Iceberg tables and some SaaS partners.
• Buffering: delivers when either the size or the time threshold is hit (for S3, 1 to 128 MB and 0 to 900 seconds; zero buffering gives the lowest latency). It is near real time, not real time.
• Transformation: an optional Lambda function per batch. Format conversion to Parquet or ORC uses a schema from the Glue Data Catalog. Dynamic partitioning writes year=/month=/ style prefixes by field values.
• Failed records go to an S3 error prefix. Firehose is not replayable: it doesn’t retain data. If you need replay, put a Kinesis stream in front.

Amazon MSK

Managed Apache Kafka. Pick it when the team already uses Kafka APIs, needs Kafka Connect or very high throughput with long retention, or wants to migrate existing Kafka without code changes.

• MSK Provisioned (you choose brokers) and MSK Serverless (capacity managed for you).
• MSK Connect runs Kafka Connect connectors (for example S3 sink).
• Authentication: IAM access control, SASL/SCRAM with Secrets Manager, or mutual TLS.
• Consumers: Lambda event source mapping, Glue streaming ETL, Flink, Firehose.
• Compare with Kinesis: Kinesis is simpler and AWS-native; MSK gives Kafka ecosystem compatibility and bigger messages and retention control.

Other streaming and change sources

• DynamoDB Streams captures item changes (24-hour retention, ordered per item). Consume with Lambda or the KCL. For longer retention or multiple consumers, use Kinesis Data Streams for DynamoDB instead.
• AWS DMS moves data between databases and to S3, Kinesis or Kafka. Modes: full load, CDC (change data capture, reads the transaction log), or full load plus CDC. It needs a replication instance (or DMS Serverless). Target S3 writes CSV or Parquet, with an Op column marking inserts, updates, deletes in CDC. DMS Schema Conversion and AWS SCT convert schemas and code between engines.
• Zero-ETL integrations replicate from Aurora, RDS, DynamoDB and some SaaS sources straight into Redshift or SageMaker lakehouse without a pipeline to maintain.
• Redshift streaming ingestion reads Kinesis or MSK directly into a materialized view.
• Amazon AppFlow moves data between SaaS apps (Salesforce, SAP, Slack, ServiceNow, Google Analytics) and S3, Redshift and others. Run on demand, on a schedule or on an event, with mapping, filtering and optional PrivateLink.
• Consuming data APIs: call REST APIs from Lambda or Glue Python shell with pagination, back-off and secrets from Secrets Manager.

Batch ingestion

• S3 as the landing zone: upload through the console, SDK, DataSync (online transfer from on-premises NFS/SMB/HDFS or other clouds, scheduled, with verification), Transfer Family (managed SFTP/FTPS/FTP into S3 or EFS), Snow Family for very large offline moves.
• Event-driven: S3 Event Notifications (to Lambda, SQS, SNS) or EventBridge (richer filtering, many targets, archive/replay) start a pipeline when an object lands. Notifications are at-least-once, so handle duplicates.
• Schedules: EventBridge Scheduler and rules, Glue triggers and crawler schedules, MWAA schedules (Airflow cron).
• Batch configuration: pick file size (aim for 128 MB to 1 GB objects; many tiny files slow everything), partition layout, compression, and for databases a bookmark or watermark so only new data is read.
• IP allowlists: sources that only accept known addresses need a NAT gateway with an Elastic IP (or a fixed outbound path) so your Glue, Lambda or EMR traffic arrives from an address you can give them. VPC-attached Lambda and Glue connections go through the VPC.

Throttling and rate limits

SQS between a producer and a consumer is the standard buffer: it smooths bursts, and a Lambda reserved concurrency limit stops the consumer overrunning the downstream system.

Lambda as a data processor

• Triggers: Kinesis, DynamoDB Streams, SQS, MSK, S3, EventBridge. For stream and queue sources an event source mapping polls and invokes with a batch.
• Tuning: batch size and batch window, parallelization factor (up to 10 concurrent batches per shard), ReportBatchItemFailures so one bad record doesn’t retry the whole batch, bisect on error, a failure destination (SQS or SNS) and a maximum retry age. A poison record blocks a shard until it expires if you don’t configure these.
• Concurrency: reserved concurrency caps a function and guarantees it capacity; provisioned concurrency removes cold starts. Memory also sets CPU.
• Limits: 15 minutes, 10 GB memory, 10 GB /tmp ephemeral storage. For larger or shared files, mount EFS (the function must be in a VPC) or read S3. Lambda is for small, short transforms. Heavier work belongs in Glue or EMR.

AWS Glue ETL

Serverless Apache Spark (and Python shell, Ray) with a catalog and scheduler.

• Jobs: Spark ETL, streaming ETL (reads Kinesis or Kafka), Python shell (small tasks), Ray. Pay per DPU-hour (billed per second). Flex execution is a cheaper class for non-urgent jobs. Auto scaling (Glue 3.0+) adds workers as needed. Worker types: G.1X, G.2X, G.4X, G.8X, and memory-optimized R types.
• DynamicFrame vs DataFrame: a DynamicFrame tolerates inconsistent schemas (a field with mixed types becomes a choice type, resolved with ResolveChoice) and carries Glue transforms such as ApplyMapping, Relationalize, DropNullFields. Convert to a Spark DataFrame for general Spark code, and back.
• Job bookmarks track what a job has processed so reruns pick up only new data (works for S3 and JDBC sources with an ordered key). Disable or reset to reprocess.
• Pushdown predicates filter partitions at read time (push_down_predicate), so only the wanted partitions are listed and read. Partition indexes speed catalog lookups for heavily partitioned tables.
• Connections hold JDBC details and credentials (use Secrets Manager) for databases in a VPC. The job needs network access to the source and an S3 gateway endpoint or NAT to reach S3.
• Glue Studio is the visual editor. Glue interactive sessions and notebooks develop code. Glue Data Quality adds DQDL rules (covered in the operations notes).
• Streaming jobs use micro-batches, checkpointing to S3 and a window size.
• Glue 5.0 runs Spark 3.5. Supports the open table formats Iceberg, Delta Lake and Hudi for upserts and time travel.
• Glue Data Catalog and crawlers are in the data store notes.

Amazon EMR

Managed Hadoop/Spark/Hive/Presto/Flink/HBase when you need control or an existing framework.

• Cost: run core nodes on On-Demand and task nodes on Spot; use transient clusters that terminate when steps finish; store data in S3 (EMRFS) not HDFS so the cluster is disposable.
• Glue vs EMR: choose Glue for serverless, low-ops ETL. Choose EMR for custom libraries, other engines (Presto, HBase), fine-tuned Spark settings, or a lift of existing on-premises Hadoop jobs.
• Containers: for custom ETL code in containers, ECS or EKS with Fargate gives control over CPU/memory per task; right-size task definitions, use Spot capacity for fault-tolerant jobs, and store images in ECR. AWS Batch runs queued batch jobs on managed compute.

Transformation choices

File formats

Converting CSV to Parquet (with Glue, Athena CTAS or Firehose conversion) is the single most common cost and speed optimisation. Pair it with partitioning and Snappy or ZSTD compression.

Troubleshooting transformation failures

• Out of memory / container killed: skewed partitions or too-large shuffles; add workers or a bigger worker type, repartition, avoid collect(), salting for skewed joins.
• Slow Glue job: too many small files (use grouping options groupFiles/groupSize, or compact), no pushdown predicate, a single JDBC read thread (set hashfield/hashpartitions for parallel reads), a hot key.
• Schema errors: mixed types (resolve choice), new columns (update catalog or enable schema evolution).
• Cannot connect to JDBC source: security group self-referencing rule, route to NAT or VPC endpoint, subnet has no path to S3.
• Duplicate rows after rerun: bookmarks disabled or non-idempotent writes.

Making data available through APIs

Expose results with API Gateway + Lambda (querying DynamoDB or Athena), the Redshift Data API (HTTPS SQL calls with no drivers or connection pools) or the Athena API. The Redshift Data API is asynchronous and works well from Lambda.

Connecting with JDBC and ODBC

JDBC (Java) and ODBC are the drivers that BI tools and Spark use to reach Redshift, RDS and others. Glue connections and Redshift drivers use them. Lambda functions opening many connections to RDS should use RDS Proxy.

Orchestration

• Standard workflows run up to a year with exactly-once execution and full history; Express workflows run up to 5 minutes, at high rates and are at-least-once. For long ETL use Standard.
• Step Functions retry (with backoff) and catch blocks give resilience; the .sync integration pattern means the state waits for the Glue job to finish.
• Resilience: retry transient failures, dead-letter queues for poison messages, idempotent steps, multi-AZ managed services, and alerts through SNS (fan-out to people and systems) or SQS (buffer work for later consumers).
• Event-driven vs schedule-driven: prefer events when data arrival is irregular. Prefer schedules when downstream needs a fixed time.
• MWAA environments live in a VPC, have a size class (small, medium, large), and scale workers automatically. Plugins and requirements.txt sit in S3.

Programming concepts

• SQL: joins, aggregations, window functions (ROW_NUMBER, LAG, rolling AVG ... OVER), CTEs. In Redshift, stored procedures (PL/pgSQL) run transformations inside the warehouse. Optimise by filtering early, selecting only needed columns, avoiding functions on join keys, and using sort/distribution keys.
• Distributed computing: data splits into partitions processed in parallel; shuffles are expensive; avoid skew.
• Data structures: graph structures (Neptune, lineage) and tree structures (JSON, hierarchical data) show up conceptually.
• IaC: CloudFormation (declarative templates) and CDK (code that synthesises CloudFormation) give repeatable pipelines across environments.
• SAM: a CloudFormation extension (Transform: AWS::Serverless-2016-10-31) with short resources for functions, state machines and tables; sam build, sam deploy, sam local invoke.
• CI/CD: CodeCommit or a Git provider through CodeConnections, CodeBuild for tests (unit tests on transform logic, SQL tests), CodePipeline to deploy. Promote the same artifact dev to test to prod.
• Git: clone, branch, checkout -b, add, commit, push, pull, merge, pull requests.
• Optimising runtime: batch API calls, use columnar formats, reuse connections across Lambda invocations (initialise outside the handler), avoid row-by-row loops.