AWS Trade Data Pipeline โ€” Project Guide

Project scope: this project supported BCA (Bank Central Asia), Indonesia.
The data-engineering pipeline starts from structured CINV (Commercial Invoice) CSV batches provided through the client data-transfer workflow.
Upstream systems may extract business documents or export structured files.
From that point, the pipeline standardizes heterogeneous schemas, validates data quality, removes duplicates, reviews unit-price anomalies, creates traceable outputs, and prepares trusted data for AWS analytics.
INQ and PO are related trade-document types, while CINV is the main analytical example.

Four-part learning path: AWS Core, Local ETL, Data Ingestion, and Data Exploitation โ€” with project visuals and interview reinforcement embedded in each part.

โ˜๏ธ AWS Core โ€” Data Engineer Knowledge Map

Learn the platform first. Four exam-weighted AWS data domains plus a practical IaC track. Open only the module you need; P5 moves from beginner concepts to production Terraform / CloudFormation.

AWS core architecture showing Route 53, Application Load Balancer, multi-AZ EC2, RDS, VPC, IAM and CloudWatch.
Architecture visual: VPC, load balancing, multi-AZ compute and database, plus core security and monitoring services.

P1. Data Ingestion & Transformation ยท 34%

34%
๐ŸŒAWS Data Pipeline Mental ModelBatch ยท Streaming ยท ETL/ELT ยท Source โ†’ Ingestion โ†’ Storage โ†’ Processing โ†’ Servingโ€บ01
๐Ÿงฉ Components in this module
๐ŸššDMS๐Ÿ”Š
What it isManaged database migration and replication service.
When to useFull load and CDC from operational databases.
Key pointUse when the source is a database and changes must be captured.
โšกKinesis Data Streams๐Ÿ”Š
What it isDurable AWS-native real-time event stream.
When to useLow-latency producers with multiple consumers and replay.
Key pointPartition key maps records to shards; design for throughput and ordering.
๐ŸชฃS3๐Ÿ”Š
What it isDurable object storage used as the main data-lake layer.
When to useLand raw, clean and curated files.
Key pointStorage is separate from compute; layout and file format matter.
๐Ÿง Glue/PySpark๐Ÿ”Š
What it isServerless managed Spark data integration.
When to useBatch ETL and lake transformations.
Key pointBest when you want managed Spark ETL.
๐Ÿ”ŽAthena๐Ÿ”Š
What it isServerless interactive SQL query service.
When to useAd-hoc SQL directly over S3.
Key pointCost/performance depend heavily on bytes scanned.
๐ŸขRedshift๐Ÿ”Š
What it isManaged MPP cloud data warehouse.
When to useRepeated analytical SQL and BI workloads.
Key pointColumnar parallel SQL; distribution/sort design matters.
Sourcesโ†’DMS / Kinesis / MSKโ†’S3โ†’Glue / EMR / Flinkโ†’Athena / Redshiftโ†’BI
Interview: choose a service by workload, latency, operational control, and costโ€”not by memorizing names.
๐Ÿ“ฆBatch IngestionS3 ยท Transfer Family ยท DataSync ยท DMS ยท AppFlow ยท Full Load / CDCโ€บ02
๐Ÿงฉ Components in this module
๐ŸชฃS3๐Ÿ”Š
What it isDurable object storage used as the main data-lake layer.
When to useLand raw, clean and curated files.
Key pointStorage is separate from compute; layout and file format matter.
๐Ÿ“Transfer Family๐Ÿ”Š
What it isManaged SFTP, FTPS and FTP endpoints backed by AWS storage.
When to usePartner or legacy file transfer.
Key pointProtocol compatibility without operating an SFTP server.
๐Ÿš›DataSync๐Ÿ”Š
What it isManaged high-speed transfer between on-premises/storage systems and AWS.
When to useLarge or recurring file and storage migration.
Key pointFile/storage transfer, not database CDC.
๐ŸššDMS๐Ÿ”Š
What it isManaged database migration and replication service.
When to useFull load and CDC from operational databases.
Key pointUse when the source is a database and changes must be captured.
๐Ÿ”—AppFlow๐Ÿ”Š
What it isManaged integration between supported SaaS apps and AWS services.
When to useSupported SaaS ingestion with little code.
Key pointBest for SaaS-to-AWS data movement.
FilesS3 upload, Transfer Family, or DataSync.
DatabasesDMS for full load and/or CDC.
SaaSAppFlow for supported SaaS applications.
RuleSeparate ingestion from downstream transformation.
โšกStreaming IngestionKinesis Data Streams ยท Firehose ยท MSK ยท Shard ยท Partition ยท Producer / Consumerโ€บ03
๐Ÿงฉ Components in this module
โšกKinesis Data Streams๐Ÿ”Š
What it isDurable AWS-native real-time event stream.
When to useLow-latency producers with multiple consumers and replay.
Key pointPartition key maps records to shards; design for throughput and ordering.
๐Ÿ”ฅFirehose๐Ÿ”Š
What it isFully managed streaming delivery service.
When to useDeliver streaming data to S3, Redshift and supported destinations.
Key pointBuffers and delivers; less consumer control than Data Streams.
๐ŸŸ MSK๐Ÿ”Š
What it isAWS-managed Apache Kafka.
When to useKafka-compatible streaming workloads.
Key pointThink topics, partitions, brokers and consumer groups.

Kinesis Data Streams / MSK retain an event stream for consumers. Amazon Data Firehose is managed delivery to destinations such as S3 and Redshift.

ServiceBest fitRemember
Kinesis Data StreamsAWS-native event streamingShard capacity, consumers, ordering
Amazon Data FirehoseManaged deliveryLow operations; buffering before delivery
Amazon MSKManaged Apache KafkaKafka compatibility and ecosystem
๐Ÿง AWS Glue Deep DiveCrawler ยท Data Catalog ยท ETL Job ยท Studio ยท Workflow ยท Job Bookmark ยท Data Qualityโ€บ04
๐Ÿงฉ Components in this module
๐Ÿง Crawler๐Ÿ”Š
What it isGlue component that scans sources and infers schema/partitions.
When to useDiscover datasets and update catalog metadata.
Key pointCrawler discovers metadata; it does not store the data.
๐Ÿ“šData Catalog๐Ÿ”Š
What it isCentral metadata repository for databases, tables and partitions.
When to useShare metadata across Glue, Athena, EMR and lake tools.
Key pointMetadata is not the underlying data.
โš™๏ธETL Job๐Ÿ”Š
What it isManaged Spark-based Glue transformation job.
When to usePySpark/Scala ETL without operating a Spark cluster.
Key pointTransforms data; choose workers, retries and bookmarks appropriately.
๐Ÿ–ฅ๏ธStudio๐Ÿ”Š
What it isVisual authoring and monitoring experience for Glue jobs.
When to useLow-code ETL design and operational visibility.
Key pointStudio is the UI; Glue Job performs execution.
๐Ÿ”Workflow๐Ÿ”Š
What it isGlue-native coordination of jobs, crawlers and triggers.
When to useMulti-step Glue-centric pipelines.
Key pointFor broader cross-service workflows, Step Functions or MWAA may fit better.
๐Ÿ”–Job Bookmark๐Ÿ”Š
What it isTracks already processed input for supported Glue jobs.
When to useIncremental ETL that should avoid reprocessing old input.
Key pointStateful progress feature; validate behavior for the source.
โœ…Data Quality๐Ÿ”Š
What it isRules and evaluations for measurable dataset quality.
When to useCheck completeness, validity, uniqueness and freshness.
Key pointFailed rules need an explicit stop, quarantine or alert action.
ComponentWhat it does
Glue Data CatalogCentral metadata repository: table definitions, schemas, partitions, locations.
Glue CrawlerScans sources, infers schema/partitions, and updates the Data Catalog.
Glue ETL JobRuns Spark-based ETL; commonly PySpark or Scala.
Glue StudioVisual interface to create, run, and monitor ETL jobs.
Glue Workflow / TriggerCoordinates Glue jobs, crawlers, and triggers.
Job BookmarkTracks previously processed data to support incremental ETL.
AWS Glue capabilities reference

Glue processing model

S3 / JDBC sourceโ†’Crawlerโ†’Data Catalogโ†’Glue Job (Spark)โ†’Curated S3

Stateful vs Stateless

Other Glue topics

ฮปProcessing โ€” Lambda vs Glue vs EMREvent logic ยท Managed Spark ETL ยท Maximum cluster controlโ€บ05
๐Ÿงฉ Components in this module
ฮปLambda๐Ÿ”Š
What it isServerless event-driven function compute.
When to useShort lightweight event logic and transformations.
Key pointNo server management; bounded runtime and resources.
๐Ÿง Glue/PySpark๐Ÿ”Š
What it isServerless managed Spark data integration.
When to useBatch ETL and lake transformations.
Key pointBest when you want managed Spark ETL.
๐Ÿ˜EMR/Spark๐Ÿ”Š
What it isManaged big-data platform with Spark/Hadoop ecosystem control.
When to useLarge workloads needing runtime or cluster control.
Key pointMore control usually means more operational responsibility.
ServiceChoose it forTypical use
LambdaShort event-driven functionsSeconds/minutes; lightweight transformations
GlueServerless managed Spark ETLData integration and batch ETL
EMRManaged big-data cluster/serverless optionsMaximum Spark/Hadoop ecosystem control
๐ŸŒŠStreaming ProcessingManaged Service for Apache Flink ยท Event Time ยท Window ยท State ยท Checkpointโ€บ06
๐Ÿงฉ Components in this module
๐ŸŒŠManaged Service for Apache Flink๐Ÿ”Š
What it isManaged stateful stream-processing runtime for Apache Flink.
When to useEvent time, windows, state and real-time transformations.
Key pointCheckpoint state and reason about event time/watermarks.
โšกKinesis Data Streams๐Ÿ”Š
What it isDurable AWS-native real-time event stream.
When to useLow-latency producers with multiple consumers and replay.
Key pointPartition key maps records to shards; design for throughput and ordering.
๐ŸŸ MSK๐Ÿ”Š
What it isAWS-managed Apache Kafka.
When to useKafka-compatible streaming workloads.
Key pointThink topics, partitions, brokers and consumer groups.
Kinesis / MSKโ†’Flinkโ†’Window + Stateโ†’Checkpointโ†’Sink
๐ŸŽ›๏ธOrchestration & MessagingStep Functions ยท MWAA/Airflow ยท EventBridge ยท SQS ยท SNS ยท Retry ยท DLQโ€บ07

Step Functions / MWAA orchestrate workflow sequence. EventBridge routes events. SQS buffers work and decouples producers/consumers. SNS fans out notifications.

๐Ÿ“จ
SQS โ€” decouple a data pipelineKeep the useful flow from the reference: producer โ†’ queue โ†’ consumer, plus retry and DLQ.
โš™๏ธProducerIngestion job / Lambda / application
โ†’
๐Ÿ“จSQS QueueBuffers messages; consumers pull independently
โ†’
๐ŸงฐConsumerGlue trigger / worker / processing service
โ†’
โ˜ ๏ธDLQIsolates messages that exceed retry policy
Data Engineer mental model: SQS is not your data lake. It buffers work and decouples producers from consumers. Design around visibility timeout, retry count, idempotency, and a dead-letter queue.
Real data-pipeline use case HIGH

A producer lands orders_2026-10-02.csv in S3 and sends a small message containing bucket, key and run ID. Workers pull messages and start validation independently.

Simulate: 10,000 files arrive at 09:00. SQS absorbs the spike; 20 workers drain the queue at their own rate. Failed files retry and finally move to a DLQ.
Parameters that actually matter

Visibility TimeoutRetentionLong PollingDLQRedrive Policy

Interview trap: standard queues are at-least-once, so consumers must be idempotent. FIFO is only needed when ordering/deduplication requirements justify it.

๐ŸŽคP1 Architecture & InterviewExplain service boundaries and why each service existsโ€บ08
๐Ÿงฉ Components in this module
๐ŸงญArchitecture Mental Model๐Ÿ”Š
What it is30-second answer:
When to useUse this to explain the end-to-end service boundaries before discussing individual AWS services.
Key point
๐ŸŒBatch vs streaming ingestion๐Ÿ”Š
What it isBatch processes bounded data on a schedule; streaming continuously processes arriving events with lower latency.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointBatch processes bounded data on a schedule; streaming continuously processes arriving events with lower latency.
๐ŸššDMS vs DataSync๐Ÿ”Š
What it isDMS is for database migration/replication and CDC; DataSync is for bulk or recurring file/storage transfer.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointDMS is for database migration/replication and CDC; DataSync is for bulk or recurring file/storage transfer.
๐ŸŒŠKinesis Data Streams vs Firehose๐Ÿ”Š
What it isData Streams gives durable streams, consumers and replay; Firehose focuses on managed buffering and delivery.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointData Streams gives durable streams, consumers and replay; Firehose focuses on managed buffering and delivery.
๐Ÿง What does a Glue Crawler do๐Ÿ”Š
What it isIt scans supported sources, infers schema, and creates or updates metadata in the Glue Data Catalog.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointIt scans supported sources, infers schema, and creates or updates metadata in the Glue Data Catalog.
๐Ÿ“šCrawler vs Data Catalog๐Ÿ”Š
What it isCrawler discovers metadata; Data Catalog stores databases, tables, schemas, partitions and locations.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointCrawler discovers metadata; Data Catalog stores databases, tables, schemas, partitions and locations.
๐Ÿ”–What is a Glue Job Bookmark๐Ÿ”Š
What it isIt tracks previously processed input so incremental jobs can avoid reprocessing the same data.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointIt tracks previously processed input so incremental jobs can avoid reprocessing the same data.
โš™๏ธLambda vs Glue vs EMR๐Ÿ”Š
What it isShort event logic โ†’ Lambda; managed Spark ETL โ†’ Glue; deeper Spark/Hadoop/runtime control โ†’ EMR.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointShort event logic โ†’ Lambda; managed Spark ETL โ†’ Glue; deeper Spark/Hadoop/runtime control โ†’ EMR.
๐ŸŽ›๏ธStep Functions vs MWAA๐Ÿ”Š
What it isStep Functions is state-machine orchestration; MWAA is managed Airflow for DAG-based workflows.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointStep Functions is state-machine orchestration; MWAA is managed Airflow for DAG-based workflows.
๐Ÿ“จSQS vs SNS๐Ÿ”Š
What it isSQS queues/buffers work; SNS publishes and fans out messages to multiple subscribers.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointSQS queues/buffers work; SNS publishes and fans out messages to multiple subscribers.
Container delivery workflow โ€” code โ†’ ECR โ†’ ECS FargateDE HIGHExtracted from the ECR + ECS workflow examples; this is the container path that matters for a Data Engineer.
๐ŸPython CodeDownloader / data utility
โ†’
๐ŸณDocker BuildCreate image
โ†’
๐Ÿท๏ธTag ImageVersion the artifact
โ†’
๐Ÿ“ฆAmazon ECRPush image
โ†’
๐Ÿ“‹Task DefinitionReference image + role
โ†’
โ˜๏ธECS FargatePull and run container
ECRManaged registry for storing and versioning container images.
ECSOrchestrates tasks/services; Fargate provides serverless container compute.
DE scopeKnow build โ†’ registry โ†’ task โ†’ logs/metrics. Deep Kubernetes control-plane knowledge is not required for this project.
Real Data Engineer simulation: Package the SFTP downloader with its Python dependencies into a Docker image, push a versioned tag to ECR, reference it from an ECS task definition, and let Step Functions start a Fargate task. The task reads Secrets Manager, downloads the CSV batch, writes S3, emits logs, and exits.
Lower-priority reference extraction: ALB/NLB and EKS are useful architecture literacy, but they are secondary for this Data Engineer learning path. Know ALB as Layer 7 HTTP/HTTPS routing, NLB as Layer 4 TCP/UDP/TLS high-performance routing, and EKS as managed Kubernetes. Go deeper only when the target role explicitly owns platform or Kubernetes infrastructure.
๐Ÿ“ฆ
ECS Fargate โ€” batch container lifecycleUseful for this project because the SFTP downloader is short-lived and containerized.
๐ŸPython CodeSFTP downloader
โ†’
๐ŸณDocker ImagePackage runtime + dependencies
โ†’
๐Ÿ“ฆAmazon ECRStore versioned image
โ†’
๐ŸงพTask DefinitionCPU, memory, role, image, network
โ†’
โ˜๏ธECS Fargate TaskRuns, downloads CSV, uploads to S3, exits
Why not EKS here? This workload does not need Kubernetes orchestration. Fargate gives the project a simpler serverless container execution path with less platform overhead.
ECR โ†’ ECS/Fargate production path HIGH

ECR stores immutable/versioned container images; ECS orchestrates tasks; Fargate supplies serverless compute. Keep these responsibilities separate.

docker build -t sftp-downloader:1.0 . docker tag sftp-downloader:1.0 ACCOUNT.dkr.ecr.REGION.amazonaws.com/sftp-downloader:1.0 docker push ACCOUNT.dkr.ecr.REGION.amazonaws.com/sftp-downloader:1.0
Simulated ingestion run

EventBridge โ†’ Step Functions โ†’ ECS RunTask. The task assumes an IAM role, reads SFTP credentials from Secrets Manager, downloads files, writes raw objects to S3, emits logs to CloudWatch, then exits.

Failure: container exits code 1. Keep logs/run ID, retry only transient failures, and never bake passwords into the image.
๐Ÿงฉ Architecture Lab โ€” Design Flow, Components & ECS/FargateFormer AWS Components knowledge ยท end-to-end design flow ยท service boundaries ยท compute mental model โ€บ09
๐Ÿงญ ARCHITECTURE MENTAL MODEL

End-to-End AWS Design Flow

Learn the responsibility of each layer first, then map each responsibility to the AWS service that implements it.

โฐ Scheduleโ†’๐ŸŽ›๏ธ Orchestrateโ†’๐Ÿ“ฅ Ingestโ†’ โš™๏ธ Transformโ†’๐Ÿ—„๏ธ Store & Catalogโ†’๐Ÿ”Ž Queryโ†’๐Ÿ“Š Present
โฐ01
SCHEDULE
EventBridge Scheduler

Starts the workflow at a defined time or interval.

๐ŸŽ›๏ธ02
ORCHESTRATE
AWS Step Functions

Controls workflow order, dependencies, retries, and failure paths.

๐Ÿ“ฆ03
INGEST
ECS + AWS Fargate

Runs the downloader container and writes source data into S3 Raw.

๐Ÿง 04
TRANSFORM
AWS Glue

Reads raw data, applies ETL logic, and prepares processed datasets.

๐Ÿ—„๏ธ05
STORE & CATALOG
S3 + Glue Data Catalog

S3 stores processed Parquet data; Glue Data Catalog stores schema and partition metadata.

๐Ÿ”Ž06
QUERY
Amazon Athena

Uses catalog metadata to query the required processed data in S3.

๐Ÿ“Š07
PRESENT
Tableau

Sends SQL through Athena and presents returned results as dashboards and visualisations.

๐ŸŽฏ PROJECT FLOW
EventBridgeโ†’Step Functionsโ†’Fargateโ†’ S3 Rawโ†’Glueโ†’S3 Processed + Catalogโ†’ Athenaโ†’Tableau
๐Ÿ“ฆ ECS + Fargate โ€” Compute Mental Model
๐Ÿ“ฆ Amazon ECS
What it isContainer orchestration service.
Used hereDefines and orchestrates the downloader task.
Key pointECS decides what workload should run.
โšก AWS Fargate
What it isServerless compute engine for ECS tasks.
Used hereRuns the downloader container without EC2 worker-node management.
Key pointFargate provides the compute where the task runs.
Remember: ECS = orchestrator โ†’ Fargate = compute โ†’ Task = running container workload.

P2. Data Store Management ยท 26%

26%
๐Ÿ—„๏ธStorage Selection & ProtectionS3 ยท RDS/Aurora ยท DynamoDB ยท Redshift ยท EBS ยท EFS ยท AWS Backupโ€บ01
๐Ÿงฉ Components in this module
๐ŸชฃS3๐Ÿ”Š
What it isDurable object storage used as the main data-lake layer.
When to useLand raw, clean and curated files.
Key pointStorage is separate from compute; layout and file format matter.
๐Ÿ›ข๏ธRDS/Aurora๐Ÿ”Š
What it isManaged relational OLTP database options.
When to useTransactions, constraints and relational application workloads.
Key pointMulti-AZ is HA; read replicas scale reads.
โšกDynamoDB๐Ÿ”Š
What it isServerless key-value/document database.
When to usePredictable low-latency access at scale.
Key pointModel access patterns and partition keys first.
๐ŸขRedshift๐Ÿ”Š
What it isManaged MPP cloud data warehouse.
When to useRepeated analytical SQL and BI workloads.
Key pointColumnar parallel SQL; distribution/sort design matters.
StoreWorkloadWhy
S3Data lake / object storageCheap durable storage, open formats
RDS / AuroraOLTP relationalTransactions and relational access
DynamoDBKey-value / documentLow-latency access at scale
RedshiftOLAP warehouseRepeated analytical SQL / BI
Other Storage & Protection
๐Ÿงฉ Components in this module
๐ŸชฃS3๐Ÿ”Š
What it isDurable object storage used as the main data-lake layer.
When to useLand raw, clean and curated files.
Key pointStorage is separate from compute; layout and file format matter.
๐Ÿ›ข๏ธRDS/Aurora๐Ÿ”Š
What it isManaged relational OLTP database options.
When to useTransactions, constraints and relational application workloads.
Key pointMulti-AZ is HA; read replicas scale reads.
โšกDynamoDB๐Ÿ”Š
What it isServerless key-value/document database.
When to usePredictable low-latency access at scale.
Key pointModel access patterns and partition keys first.
๐ŸขRedshift๐Ÿ”Š
What it isManaged MPP cloud data warehouse.
When to useRepeated analytical SQL and BI workloads.
Key pointColumnar parallel SQL; distribution/sort design matters.

EBS = block storage for compute. EFS = managed shared file system. AWS Backup centralizes backup policies across supported AWS resources.

Lower-priority reference extraction: EBS is persistent block storage attached to EC2; for Data Engineering, remember gp3 for general workloads, snapshots for point-in-time backup, KMS encryption, and CloudWatch monitoring. It matters mainly when you operate EC2-based tools such as Tableau, not as your data-lake storage.
โšก
DynamoDB โ€” partition-first data modelThe valuable part is not โ€œNoSQL is fastโ€; it is how the key design determines distribution and access patterns.
๐Ÿ“ฑApplicationGetItem / Query / PutItem
โ†’
โšกDynamoDB TablePartition key + optional sort key
โ†’
๐ŸงฉDistributed PartitionsHash of partition key spreads items
PK โ€” UserIdSK โ€” EventTimeEvent
U1232026-10-02T10:01view
U1232026-10-02T10:03add_to_cart
U4562026-10-02T10:05pay
Interview focus: start from access patterns. A poor partition key creates hot partitions; a sort key lets one partition support ordered/range queries. Use a GSI when you need a different query key.
Scope note for Data Engineering: Know EBS as persistent block storage attached to EC2 and understand snapshots, but do not spend the same study time on EBS volume tuning as on S3, RDS, DynamoDB, Athena, Redshift, and Glue.
๐ŸชฃAmazon S3 Deep DivePartitioning ยท Storage Classes ยท Lifecycle ยท Versioning ยท Encryption ยท Events ยท Icebergโ€บ02
๐Ÿงฉ Components in this module
๐ŸชฃS3๐Ÿ”Š
What it isDurable object storage used as the main data-lake layer.
When to useLand raw, clean and curated files.
Key pointStorage is separate from compute; layout and file format matter.

Key areas: bucket/object/prefix, raw-clean-curated zones, partition design, Parquet/ORC, compression, small-file control, lifecycle/storage classes, versioning, bucket policies, SSE-S3/SSE-KMS, event notifications, Access Points, and Iceberg tables.

๐Ÿ“šGlue Data CatalogDatabase ยท Table ยท Schema ยท Partition ยท Location ยท Metadata โ‰  Dataโ€บ03
๐Ÿงฉ Components in this module
๐Ÿ“šData Catalog๐Ÿ”Š
What it isCentral metadata repository for databases, tables and partitions.
When to useShare metadata across Glue, Athena, EMR and lake tools.
Key pointMetadata is not the underlying data.
๐Ÿง Crawler๐Ÿ”Š
What it isGlue component that scans sources and infers schema/partitions.
When to useDiscover datasets and update catalog metadata.
Key pointCrawler discovers metadata; it does not store the data.
Mental model: the Catalog stores metadata; the actual lake data remains in S3.
S3 objectsโ†’Crawler / DDLโ†’Glue Data Catalogโ†’Athena / Glue / Redshift Spectrum
๐Ÿ”ŽAmazon AthenaServerless SQL ยท Partition Pruning ยท CTAS ยท Projection ยท Federated Query ยท Workgroupsโ€บ04
๐Ÿงฉ Components in this module
๐Ÿ”ŽAthena๐Ÿ”Š
What it isServerless interactive SQL query service.
When to useAd-hoc SQL directly over S3.
Key pointCost/performance depend heavily on bytes scanned.
๐ŸชฃS3๐Ÿ”Š
What it isDurable object storage used as the main data-lake layer.
When to useLand raw, clean and curated files.
Key pointStorage is separate from compute; layout and file format matter.
๐Ÿ“šData Catalog๐Ÿ”Š
What it isCentral metadata repository for databases, tables and partitions.
When to useShare metadata across Glue, Athena, EMR and lake tools.
Key pointMetadata is not the underlying data.
TopicKey point
Querying dataSQL directly over S3; partition pruning; Parquet/ORC
Federated QueryQuery supported external sources through data source connectors
Performance & costReduce bytes scanned with partitions, columnar formats, compression
Partition ProjectionGenerate partition values from rules instead of cataloging every partition
WorkgroupsSeparate teams/workloads; control settings, metrics, and cost
S3 Glue Catalog Athena analytics reference

Architecture reference: S3 โ†’ Glue Data Catalog โ†’ Athena โ†’ BI. Athena query results can also be written to S3.

๐ŸขAmazon Redshift Deep DiveMPP ยท RA3 ยท Distribution ยท Sort ยท WLM ยท Spectrum ยท Serverless ยท Data Sharingโ€บ05
๐Ÿงฉ Components in this module
๐ŸขRedshift๐Ÿ”Š
What it isManaged MPP cloud data warehouse.
When to useRepeated analytical SQL and BI workloads.
Key pointColumnar parallel SQL; distribution/sort design matters.
๐ŸชฃS3๐Ÿ”Š
What it isDurable object storage used as the main data-lake layer.
When to useLand raw, clean and curated files.
Key pointStorage is separate from compute; layout and file format matter.

Amazon Redshift is a fully managed cloud data warehouse optimized for analytical SQL at scale.

Amazon Redshift architecture reference
TopicRemember
Cluster / ServerlessProvisioned cluster or serverless warehouse model
RA3Separates managed storage from compute capacity
DistributionAUTO / EVEN / KEY / ALL; controls row placement
Sort keysImprove data skipping for common filters/ranges
WLMControls workload queues and concurrency
Materialized ViewsPersist query results for repeated analytical patterns
SpectrumQuery external data in S3
Federated QueryQuery supported operational databases
Data APIHTTPS API to run SQL without persistent DB connections
Data SharingShare live Redshift data across consumers
SnapshotsBackup / restore capability
SecurityIAM, database privileges, encryption, VPC controls

Redshift vs Hive vs Spark SQL

TechnologyTypeStorageTypical workload
RedshiftManaged MPP data warehouseManaged storage / S3 integrationInteractive OLAP & BI
HiveSQL layer in Hadoop ecosystemHDFS / object storageBatch SQL, historically higher latency
Spark SQLDistributed SQL engineHDFS / S3 / otherETL + general distributed processing
Architecture: SQL is optimized and distributed across parallel compute. With RA3, managed storage is decoupled from compute capacity; Spectrum extends queries to S3.
๐Ÿ›ข๏ธRDS & AuroraOLTP ยท Multi-AZ ยท Read Replica ยท Backup ยท Failoverโ€บ06

Multi-AZ primarily improves availability/failover. Read Replicas primarily scale reads. Aurora provides a cloud-native distributed storage architecture with MySQL/PostgreSQL compatibility.

๐Ÿ›ข๏ธ
RDS โ€” OLTP, Multi-AZ and Read ReplicaSeparate availability from read scaling; this is the part most useful to a Data Engineer.
๐Ÿ–ฅ๏ธApplicationSQL connections
โ†’
๐Ÿ›ข๏ธRDS PrimaryTransactional reads + writes
โ‡ข
๐Ÿ›ก๏ธMulti-AZ StandbySynchronous standby for failover
๐Ÿ“– Read Replica

Asynchronous copy used mainly to scale read traffic. It is not the same thing as Multi-AZ failover.

๐Ÿ’พ Backups

Automated backups and point-in-time recovery protect the database; design retention around RPO/RTO.

โšกDynamoDBPartition Key ยท Sort Key ยท GSI / LSI ยท Capacity ยท Streams ยท Hot Partitionโ€บ07
๐Ÿงฉ Components in this module

Start with access patterns. Design partition keys to distribute traffic, use sort keys for range/group access, and secondary indexes for alternate query patterns.

DynamoDB request and partition workflowDE HIGHExtracted from the DynamoDB high-level workflow and data-model example; keep the focus on keys, partitions and access patterns.
๐Ÿ’ปApplicationGetItem / PutItem / Query
โ†’
โšกDynamoDB TableServerless NoSQL table
โ†’
๐ŸงฉPartition KeyDistributes items
โ†’
โ†•๏ธSort KeyOrders/groups items in a partition
โ†’
๐ŸŒReplicated across AZsManaged availability
Partition keyChoose a high-cardinality key that spreads traffic; poor key choice can create hot partitions.
Sort keyUse it to model one-to-many or time-ordered access inside one partition key.
Capacity modeOn-demand fits unpredictable traffic; provisioned capacity fits predictable workloads when you want explicit capacity control.
Real Data Engineer simulation: For user event state, use PK = user_id and SK = event_timestamp when the access pattern is โ€œread one userโ€™s recent events.โ€ For large analytical history, export or stream data to S3 instead of scanning DynamoDB as a warehouse.
๐ŸŽคP2 Architecture & InterviewChoose storage by workload and explain trade-offsโ€บ08
๐Ÿงฉ Components in this module
๐ŸงญStorage Decision Model๐Ÿ”Š
What it isChoose storage by access pattern: S3 for lake storage, Athena for ad-hoc SQL, Redshift for repeated BI, RDS/Aurora for relational OLTP, and DynamoDB for key-value access.
When to useUse this when an interviewer asks why you chose S3, Athena, Redshift, RDS/Aurora or DynamoDB.
Key pointChoose storage by access pattern: S3 for lake storage, Athena for ad-hoc SQL, Redshift for repeated BI, RDS/Aurora for relational OLTP, and DynamoDB for key-value access.
๐ŸชฃWhy is S3 suitable for a data lake๐Ÿ”Š
What it isDurable, scalable object storage integrated with Glue, Athena, EMR and Redshift Spectrum.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointDurable, scalable object storage integrated with Glue, Athena, EMR and Redshift Spectrum.
๐Ÿ—‚๏ธPrefix vs partition๐Ÿ”Š
What it isPrefix is part of an S3 object key; a partition is a logical data division used by query metadata and pruning.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointPrefix is part of an S3 object key; a partition is a logical data division used by query metadata and pruning.
๐Ÿ“ฆWhy do Parquet/ORC reduce Athena cost๐Ÿ”Š
What it isColumnar layout lets Athena read fewer bytes; compression reduces scanned data further.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointColumnar layout lets Athena read fewer bytes; compression reduces scanned data further.
๐Ÿ”ŽWhat is partition pruning๐Ÿ”Š
What it isThe engine skips partitions that cannot satisfy the filter, reducing I/O and scan cost.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointThe engine skips partitions that cannot satisfy the filter, reducing I/O and scan cost.
๐ŸขAthena vs Redshift๐Ÿ”Š
What it isAthena is serverless SQL over S3; Redshift is an MPP analytical warehouse for repeated BI/DW workloads.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointAthena is serverless SQL over S3; Redshift is an MPP analytical warehouse for repeated BI/DW workloads.
๐ŸงฑDistribution Key vs Sort Key๐Ÿ”Š
What it isDistribution controls row placement across Redshift compute; sort strategy improves scan efficiency.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointDistribution controls row placement across Redshift compute; sort strategy improves scan efficiency.
๐Ÿ›ข๏ธMulti-AZ vs Read Replica๐Ÿ”Š
What it isMulti-AZ targets availability/failover; Read Replica primarily scales reads.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointMulti-AZ targets availability/failover; Read Replica primarily scales reads.
โšกDynamoDB Partition Key vs Sort Key๐Ÿ”Š
What it isPartition key drives distribution; sort key orders/groups items inside the same partition key.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointPartition key drives distribution; sort key orders/groups items inside the same partition key.
๐Ÿ”ฅWhat causes a hot partition๐Ÿ”Š
What it isUneven traffic concentrated on a small set of partition-key values.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointUneven traffic concentrated on a small set of partition-key values.
๐Ÿงฉ Architecture Lab โ€” Athena Query PathTableau โ†’ Athena โ†’ Glue Data Catalog + S3 ยท query path and analytics service boundaries โ€บ09
๐Ÿ“Š

Tableau + Athena โ€” Query Path

One sentence to remember: Tableau presents; Athena queries.

TABLEAU
Tableau EC2 is the BI layer. It sends SQL through the Athena JDBC driver.
PRIVATE API
Athena Interface VPC Endpoint keeps the API call private inside the VPC.
QUERY
Athena reads metadata from Glue Data Catalog and scans S3 Processed.
RESULT
Athena writes output to S3 Query Results, then Tableau visualises the result.
๐Ÿ‘ค
Internal User
private access / VPN
โ†’
๐Ÿ“Š
Tableau EC2
dashboard + JDBC
โ†’
๐Ÿ”’
Athena Endpoint
PrivateLink / private API
โ†’
โšก
Athena
serverless SQL engine
โ†’
๐Ÿชฃ
S3 Query Results
returned output file
๐Ÿ—‚๏ธ Glue Data Catalog
table + partition metadata
metadata โ†’ Athena
๐Ÿชฃ S3 Processed
partitioned Parquet data
data scan โ†’ Athena
Line: Tableau presents the result; Athena performs the query.

P3. Data Operations & Support ยท 22%

22%
๐Ÿ“ˆObservability โ€” CloudWatch & CloudTrailMetrics ยท Logs ยท Alarms ยท Dashboards ยท API Auditโ€บ01
CloudWatch provides operational telemetry: metrics, logs, dashboards, and alarms.
CloudWatch vs CloudTrail
CloudWatch focuses on operations and observability. CloudTrail records AWS API activity for audit and governance.
๐Ÿ“ˆ
CloudWatch vs CloudTrail โ€” do not mix them upThe two reference diagrams are useful together because Data Engineers are often asked to distinguish operations from audit.
โš™๏ธAWS WorkloadsGlue ยท ECS ยท Lambda ยท RDS
โ†’
๐Ÿ“ˆCloudWatchMetrics ยท Logs ยท Alarms
โ†’
๐ŸšจActionDashboard ยท SNS ยท Auto Scaling

Question answered: Is the pipeline healthy and what is happening now?

๐Ÿ‘คUser / Role / ServiceMakes AWS API call
โ†’
๐ŸงพCloudTrailRecords API activity
โ†’
๐ŸชฃS3 / LakeAudit history for investigation

Question answered: Who did what, when, from where, and to which AWS resource?

Fast memory: CloudWatch = operational observability. CloudTrail = AWS API audit trail. In an incident you often use both.
Incident simulation HIGH

Symptom: Glue job runtime jumps from 12 to 45 minutes. Check CloudWatch job metrics/logs first. If a configuration or IAM change is suspected, use CloudTrail to identify the API call, principal and timestamp.

Sequence: Alarm โ†’ logs/metrics โ†’ isolate bottleneck โ†’ CloudTrail change history โ†’ fix โ†’ rerun โ†’ validate SLA.
What to monitor as a Data Engineer

Job failuresRuntimeQueue depthFreshnessCPU / memoryError count

CloudTrail is not your application log stream; it is primarily the audit trail for AWS API activity.

โœ…Data QualityCompleteness ยท Accuracy ยท Validity ยท Uniqueness ยท Freshness ยท Consistencyโ€บ02
๐Ÿงฉ Components in this module
โœ…Data Quality๐Ÿ”Š
What it isRules and evaluations for measurable dataset quality.
When to useCheck completeness, validity, uniqueness and freshness.
Key pointFailed rules need an explicit stop, quarantine or alert action.
๐Ÿ“ˆCloudWatch๐Ÿ”Š
What it isOperational observability for metrics, logs, alarms and dashboards.
When to useMonitor pipeline and service health.
Key pointAnswers what is happening operationally.
Put measurable quality checks at ingestion, transformation, and serving boundaries.
๐Ÿ›ก๏ธReliable PipelinesRetry ยท Idempotency ยท Checkpoint ยท DLQ ยท Reprocessing ยท Backfill ยท Reconciliationโ€บ03
๐Ÿงฉ Components in this module
๐Ÿ“ฅSQS๐Ÿ”Š
What it isDurable message queue.
When to useBuffer and decouple producers from consumers.
Key pointQueue semantics help absorb spikes and support retries.
๐Ÿ“ˆCloudWatch๐Ÿ”Š
What it isOperational observability for metrics, logs, alarms and dashboards.
When to useMonitor pipeline and service health.
Key pointAnswers what is happening operationally.
Recovery must not silently duplicate or lose data. Design retries, idempotent writes, replay/backfill, and reconciliation.
๐ŸงฐPerformance & Cost OptimizationGlue/Spark ยท Athena ยท Kinesis ยท Redshift ยท S3 ยท Cost controlsโ€บ04
๐Ÿงฉ Components in this module
๐Ÿง Glue/PySpark๐Ÿ”Š
What it isServerless managed Spark data integration.
When to useBatch ETL and lake transformations.
Key pointBest when you want managed Spark ETL.
๐Ÿ”ŽAthena๐Ÿ”Š
What it isServerless interactive SQL query service.
When to useAd-hoc SQL directly over S3.
Key pointCost/performance depend heavily on bytes scanned.
โšกKinesis Data Streams๐Ÿ”Š
What it isDurable AWS-native real-time event stream.
When to useLow-latency producers with multiple consumers and replay.
Key pointPartition key maps records to shards; design for throughput and ordering.
๐ŸขRedshift๐Ÿ”Š
What it isManaged MPP cloud data warehouse.
When to useRepeated analytical SQL and BI workloads.
Key pointColumnar parallel SQL; distribution/sort design matters.
Observe metrics โ†’ isolate the bottleneck โ†’ change one layer โ†’ validate the improvement.
Cost Optimization
๐Ÿงฉ Components in this module
๐Ÿ’ฐCost Explorer๐Ÿ”Š
What it isAWS cost and usage analysis tool.
When to useFind spend drivers before optimizing.
Key pointUse measured cost data, not guesses.
๐ŸšจBudgets๐Ÿ”Š
What it isBudget thresholds and notifications.
When to useGuardrail account/project spend.
Key pointAlerts complementโ€”not replaceโ€”architecture optimization.
๐ŸชฃS3๐Ÿ”Š
What it isDurable object storage used as the main data-lake layer.
When to useLand raw, clean and curated files.
Key pointStorage is separate from compute; layout and file format matter.
๐Ÿ”ŽAthena๐Ÿ”Š
What it isServerless interactive SQL query service.
When to useAd-hoc SQL directly over S3.
Key pointCost/performance depend heavily on bytes scanned.
๐ŸขRedshift๐Ÿ”Š
What it isManaged MPP cloud data warehouse.
When to useRepeated analytical SQL and BI workloads.
Key pointColumnar parallel SQL; distribution/sort design matters.
Optimize storage tier, bytes scanned, compute duration, worker/cluster sizing, and idle capacity.
๐Ÿ—๏ธHigh Availability & Disaster RecoveryRegion ยท AZ ยท Multi-AZ ยท Auto Scaling ยท Backup ยท RTO / RPOโ€บ05
๐Ÿงฉ Components in this module
๐Ÿ›ข๏ธRDS/Aurora๐Ÿ”Š
What it isManaged relational OLTP database options.
When to useTransactions, constraints and relational application workloads.
Key pointMulti-AZ is HA; read replicas scale reads.
๐Ÿ“ˆCloudWatch๐Ÿ”Š
What it isOperational observability for metrics, logs, alarms and dashboards.
When to useMonitor pipeline and service health.
Key pointAnswers what is happening operationally.
Define RTO/RPO first, then choose Multi-AZ, backups, replicas, and cross-Region strategies.
๐ŸŽคP3 Troubleshooting & InterviewSymptom โ†’ Metric โ†’ Root Cause โ†’ Recovery โ†’ Preventionโ€บ06
๐Ÿงฉ Components in this module
๐ŸงญTroubleshooting Mental Model๐Ÿ”Š
What it isTroubleshoot in order: Symptom โ†’ Metric โ†’ Root Cause โ†’ Recovery โ†’ Prevention.
When to useUse this when an interviewer gives you a production incident or performance symptom.
Key pointTroubleshoot in order: Symptom โ†’ Metric โ†’ Root Cause โ†’ Recovery โ†’ Prevention.
๐Ÿ“ˆCloudWatch vs CloudTrail๐Ÿ”Š
What it isCloudWatch covers operational metrics/logs/alarms; CloudTrail records AWS API activity for audit.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointCloudWatch covers operational metrics/logs/alarms; CloudTrail records AWS API activity for audit.
๐Ÿ›ก๏ธWhat is idempotency๐Ÿ”Š
What it isA retry or rerun does not create duplicate business effects.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointA retry or rerun does not create duplicate business effects.
๐Ÿ“ฎRetry vs DLQ๐Ÿ”Š
What it isRetry handles transient failures; DLQ isolates records that continue to fail.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointRetry handles transient failures; DLQ isolates records that continue to fail.
โš™๏ธHow do you troubleshoot a slow Glue/Spark job๐Ÿ”Š
What it isUse metrics/logs, then inspect skew, shuffle, partition count, file size, pushdown and worker sizing.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointUse metrics/logs, then inspect skew, shuffle, partition count, file size, pushdown and worker sizing.
๐Ÿ”ŽWhy is Athena scanning too much๐Ÿ”Š
What it isWeak partition filtering, non-columnar formats, unnecessary columns, poor compression or inefficient layout.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointWeak partition filtering, non-columnar formats, unnecessary columns, poor compression or inefficient layout.
๐Ÿ“ฆHow do you handle S3 small files๐Ÿ”Š
What it isCompact them into appropriately sized columnar files and avoid excessive tiny output partitions.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointCompact them into appropriately sized columnar files and avoid excessive tiny output partitions.
โฑ๏ธRTO vs RPO๐Ÿ”Š
What it isRTO is acceptable recovery time; RPO is acceptable data-loss window.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointRTO is acceptable recovery time; RPO is acceptable data-loss window.
๐Ÿ—๏ธMulti-AZ vs Multi-Region๐Ÿ”Š
What it isMulti-AZ improves availability inside one Region; Multi-Region addresses regional resilience.
When to useUse this distinction when comparing AWS services, architecture choices, or troubleshooting trade-offs in an interview.
Key pointMulti-AZ improves availability inside one Region; Multi-Region addresses regional resilience.

P4. Data Security & Governance ยท 18%

18%
๐Ÿ”IAMUser ยท Role ยท Policy ยท Resource Policy ยท Least Privilege ยท AssumeRoleโ€บ01
๐Ÿงฉ Components in this module
๐ŸŽญIAM๐Ÿ”Š
What it isAWS identity and authorization system.
When to useGrant least-privilege access to people and workloads.
Key pointPrefer roles and temporary credentials for workloads.
Prefer roles and temporary credentials; grant only required actions on required resources.
๐Ÿ”‘Encryption & SecretsAt Rest ยท In Transit ยท KMS ยท SSE-S3 ยท SSE-KMS ยท TLS ยท Secrets Managerโ€บ02
Protect stored data, secrets, and network traffic as separate security concerns.
๐Ÿ”
Secrets Manager + KMS โ€” secure a Data Engineer workloadCredentials and encryption keys solve different problems. The workload needs both IAM permission and the correct key/resource permissions.
โš™๏ธGlue / ECS RoleTemporary workload identity
โ†’
๐Ÿ”Secrets ManagerSFTP / DB credential secret
โ†’
๐Ÿ”‘AWS KMSProtects encryption keys
โ†’
๐ŸชฃS3 / RDSEncrypted data at rest
Secrets Manager HIGH

Store passwords, API keys, SSH private keys and database credentials outside code. Retrieve them at runtime with the workload IAM role. Rotation is useful for supported secrets and should be tested with consumers.

Your SFTP example: ECS Fargate starts โ†’ assumes task role โ†’ GetSecretValue โ†’ connects to SFTP โ†’ downloads CSV โ†’ secret never appears in source code or Docker image.
KMS for a data lake HIGH

KMS manages cryptographic keys; services such as S3, RDS and Secrets Manager integrate with it. With SSE-KMS, access can require both permission to the S3 object and permission to use the KMS key.

Failure simulation: Athena can see the table but query returns AccessDenied on encrypted objects. Check S3/Lake Formation access and KMS key permissions.
Envelope-encryption mental model

Application/service requests encryption โ†’ KMS protects the data key โ†’ ciphertext is stored with the data โ†’ authorized decrypt operations recover plaintext. KMS does not become your bulk data store.

SSE-S3SSE-KMSKey policyIAM policyRotationAudit

Security checklist

Use workload roles, least privilege, encryption at rest and TLS in transit. Keep secrets out of Git, images and logs. Monitor secret/key API activity with CloudTrail and operational failures with CloudWatch.

๐ŸŒVPC & Private NetworkingVPC ยท Subnets ยท Routes ยท IGW ยท NAT ยท SG ยท NACL ยท VPC Endpoints ยท PrivateLinkโ€บ03
๐Ÿงฉ Components in this module
๐ŸŒVPC๐Ÿ”Š
What it isIsolated virtual network boundary in AWS.
When to usePlace private application and data resources.
Key pointSubnets, routes and security controls define connectivity.
โžก๏ธNAT Gateway๐Ÿ”Š
What it isManaged outbound internet path for private subnets.
When to usePrivate resources that require internet egress.
Key pointNot needed for S3 traffic when a suitable VPC endpoint is used.
A public subnet has a route to an Internet Gateway. Private workloads can use NAT for outbound internet access when needed.
Security Group vs NACL
๐Ÿงฉ Components in this module
๐ŸงฑSecurity Group๐Ÿ”Š
What it isStateful resource/ENI-level virtual firewall.
When to useAllow only required inbound/outbound resource traffic.
Key pointReturn traffic for established flows is automatically allowed.
๐ŸšงNACL๐Ÿ”Š
What it isStateless subnet-level network ACL.
When to useCoarse subnet traffic guardrails.
Key pointReturn traffic requires explicit matching rules.
Security Groups are stateful and attached to resources/ENIs; NACLs are stateless subnet-level controls.
VPC Endpoints & PrivateLink
๐Ÿงฉ Components in this module
๐Ÿ”ŒVPC Endpoint๐Ÿ”Š
What it isPrivate access path to supported AWS services.
When to useKeep service traffic off the public internet.
Key pointGateway and interface endpoints serve different services.
๐Ÿ”—PrivateLink๐Ÿ”Š
What it isAWS technology for private service connectivity.
When to useExpose or consume services privately across VPCs/accounts.
Key pointInterface endpoints are a common consumer-side pattern.
โžก๏ธNAT Gateway๐Ÿ”Š
What it isManaged outbound internet path for private subnets.
When to usePrivate resources that require internet egress.
Key pointNot needed for S3 traffic when a suitable VPC endpoint is used.
๐ŸชฃS3๐Ÿ”Š
What it isDurable object storage used as the main data-lake layer.
When to useLand raw, clean and curated files.
Key pointStorage is separate from compute; layout and file format matter.
For S3, a Gateway VPC Endpoint provides private routing from a VPC to S3 without a NAT Gateway.
๐Ÿ›๏ธLake Governance & AuditGlue Catalog ยท Lake Formation ยท CloudTrail ยท Macie ยท Retention ยท Lineageโ€บ04
๐Ÿงฉ Components in this module
๐Ÿ›๏ธLake Formation๐Ÿ”Š
What it isCentral governance and fine-grained permissions for data lakes.
When to useControl database/table/column access to cataloged lake data.
Key pointAdds data-level governance beyond raw S3 IAM.
๐Ÿ“šData Catalog๐Ÿ”Š
What it isCentral metadata repository for databases, tables and partitions.
When to useShare metadata across Glue, Athena, EMR and lake tools.
Key pointMetadata is not the underlying data.
Lake Formation centralizes fine-grained permissions for cataloged data lake resources.
Governance & Audit
๐Ÿงฉ Components in this module
๐Ÿ“šData Catalog๐Ÿ”Š
What it isCentral metadata repository for databases, tables and partitions.
When to useShare metadata across Glue, Athena, EMR and lake tools.
Key pointMetadata is not the underlying data.
๐Ÿ›๏ธLake Formation๐Ÿ”Š
What it isCentral governance and fine-grained permissions for data lakes.
When to useControl database/table/column access to cataloged lake data.
Key pointAdds data-level governance beyond raw S3 IAM.
๐Ÿ‘ฃCloudTrail๐Ÿ”Š
What it isAWS API activity and audit history.
When to useAudit who called what API and when.
Key pointAnswers who did what in AWS.
๐Ÿ•ต๏ธMacie๐Ÿ”Š
What it isManaged sensitive-data discovery for S3.
When to useFind and classify sensitive lake data.
Key pointClassification supports governance and remediation.
Catalog metadata, govern permissions, audit API activity, classify sensitive data, and define retention/lineage controls.
๐ŸŽคP4 Security Architecture & ComponentsIdentity ยท Network ยท Encryption ยท Governance ยท Interview recallโ€บ05
๐Ÿงฉ Components in this module
๐Ÿ‘คIAM User vs Role๐Ÿ”Š
What it isUser is a long-lived IAM identity; Role is assumed and provides temporary credentials, preferred for workloads.
When to useUse this distinction when explaining security architecture and service boundaries.
Key pointUser is a long-lived IAM identity; Role is assumed and provides temporary credentials, preferred for workloads.
๐Ÿ“œIdentity policy vs Resource policy๐Ÿ”Š
What it isIdentity policies attach to identities; resource policies attach directly to supported resources.
When to useUse this distinction when explaining security architecture and service boundaries.
Key pointIdentity policies attach to identities; resource policies attach directly to supported resources.
๐ŸงฑSecurity Group vs NACL๐Ÿ”Š
What it isSecurity Group is stateful and resource/ENI-level; NACL is stateless and subnet-level.
When to useUse this distinction when explaining security architecture and service boundaries.
Key pointSecurity Group is stateful and resource/ENI-level; NACL is stateless and subnet-level.
๐ŸŒNAT Gateway vs VPC Endpoint๐Ÿ”Š
What it isNAT gives private subnets outbound internet; VPC Endpoint gives private access to supported services.
When to useUse this distinction when explaining security architecture and service boundaries.
Key pointNAT gives private subnets outbound internet; VPC Endpoint gives private access to supported services.
๐Ÿ”ŒGateway Endpoint vs Interface Endpoint๐Ÿ”Š
What it isGateway endpoints support services such as S3/DynamoDB; interface endpoints use ENIs/PrivateLink.
When to useUse this distinction when explaining security architecture and service boundaries.
Key pointGateway endpoints support services such as S3/DynamoDB; interface endpoints use ENIs/PrivateLink.
๐Ÿ”‘SSE-S3 vs SSE-KMS๐Ÿ”Š
What it isBoth encrypt at rest; SSE-KMS adds KMS key permissions and auditing/control.
When to useUse this distinction when explaining security architecture and service boundaries.
Key pointBoth encrypt at rest; SSE-KMS adds KMS key permissions and auditing/control.
๐Ÿ›๏ธIAM vs Lake Formation๐Ÿ”Š
What it isIAM controls AWS API/resource access; Lake Formation adds fine-grained governed lake permissions.
When to useUse this distinction when explaining security architecture and service boundaries.
Key pointIAM controls AWS API/resource access; Lake Formation adds fine-grained governed lake permissions.
๐ŸงญCloudTrail vs Macie๐Ÿ”Š
What it isCloudTrail records API activity; Macie discovers/classifies sensitive data in S3.
When to useUse this distinction when explaining security architecture and service boundaries.
Key pointCloudTrail records API activity; Macie discovers/classifies sensitive data in S3.
๐ŸชฃHow should private compute access S3๐Ÿ”Š
What it isUse a private subnet plus S3 Gateway VPC Endpoint, least-privilege IAM and encryption.
When to useUse this distinction when explaining security architecture and service boundaries.
Key pointUse a private subnet plus S3 Gateway VPC Endpoint, least-privilege IAM and encryption.
๐Ÿงฉ Architecture Lab โ€” Private NetworkingNAT Gateway vs VPC Endpoint ยท private subnet traffic paths ยท secure AWS service access โ€บ06
๐ŸŒ

Private Networking โ€” NAT Gateway vs VPC Endpoint

Use these two diagrams to explain where traffic goes from a private Fargate task.

Line : Download via NAT; then upload to S3 through the VPC Endpoint.

1. Secure external SFTP path

The private Fargate task initiates HTTPS through NAT Gateway and Internet Gateway. The response returns through the same path and lands back on Fargate.

Application VPC Private Subnet โ–ฃ ECS Fargate Downloader No public IP Public Subnet โ‡„ NAT Gateway โˆฉ Internet Gateway Client SFTP Endpoint SFTP/SSH source ๐ŸŒ 1. Outbound HTTPS request 2. Response returns to Fargate

2. Private upload to S3 Raw

After Fargate receives the file, it streams or multipart-uploads it to S3 Raw through the S3 Gateway VPC Endpoint. This path does not use NAT or the external network.

Application VPC Private Subnet โ–ฃ ECS Fargate Downloader File already received S3 Gateway VPC Endpoint Amazon S3 Raw Original CSV file ๐Ÿชฃ 1. Stream / multipart upload 2. Private AWS service path No NAT Gateway. No Internet Gateway. S3 traffic stays on the AWS private path.
DownloadFargate โ†’ NAT Gateway โ†’ Internet Gateway โ†’ Client SFTP Endpoint โ†’ back to Fargate
UploadFargate โ†’ S3 Gateway VPC Endpoint โ†’ S3 Raw
LineNAT = internet; VPC Endpoint = private AWS service access

P5. Infrastructure as Code โ€” Terraform & CloudFormation

PRACTICAL
๐ŸงฑIaC Fundamentals โ€” Beginner Mental ModelDesired state ยท Declarative configuration ยท Idempotency ยท Plan / Apply ยท Driftโ€บ01
๐Ÿงช Hands-on Lab 01 โ€” Manual AWS vs Infrastructure as Code
AWS Console โ€” manual path
Amazon S3 ย โ€บย  Buckets ย โ€บย  Create bucket
Bucket nametrade-dev-raw
AWS Regionap-southeast-1
Create bucket
IaC โ€” repeatable path
resource "aws_s3_bucket" "raw" {
  bucket = "trade-dev-raw"
}
Same desired resource, but now the definition can be reviewed in Git and reproduced.
1. Defineโ†’2. Previewโ†’3. Applyโ†’4. Compare desired vs real state
๐Ÿงฉ Components in this module

What is Infrastructure as Code?

Infrastructure as Code means defining cloud infrastructure in version-controlled files instead of creating resources manually in the AWS Console.

Key point: Code describes the desired infrastructure; the IaC engine creates or changes AWS resources to match it.

Why use IaC?

IaC makes infrastructure repeatable, reviewable, auditable and easier to reproduce across development, test and production environments.

Interview: The main benefit is not automation alone; it is consistent and controlled infrastructure change.

๐Ÿ‘จโ€๐Ÿ’ป Engineer
โ†’
๐Ÿ“ Git / IaC Code
โ†’
๐Ÿ” Plan / Change Set
โ†’
โ˜๏ธ AWS
ConceptBeginner explanation
DeclarativeDescribe what the final infrastructure should look like, rather than scripting every console click.
Desired stateThe configuration represents the infrastructure you want.
IdempotencyRunning the same configuration again should not create duplicate resources when nothing changed.
DriftThe real AWS environment no longer matches the infrastructure definition because of manual or external changes.
๐ŸŸฃTerraform FundamentalsProvider ยท Resource ยท Variable ยท Output ยท init ยท validate ยท plan ยท apply ยท destroyโ€บ02
๐Ÿงช Hands-on Lab 02 โ€” Create your first S3 bucket with Terraform
๐Ÿ“ terraform-lab/
โ””โ”€โ”€ ๐Ÿ“„ main.tf
Terminal
$ terraform init
Terraform has been successfully initialized!

$ terraform validate
Success! The configuration is valid.

$ terraform plan
Plan: 1 to add, 0 to change, 0 to destroy.

$ terraform apply
Apply complete! Resources: 1 added.
AWS Console โ€” verification
Amazon S3 โ€บ Buckets
trade-dev-rawAsia Pacific (Singapore)โœ“ Created
Beginner habit: always read the plan before apply, then verify the resource in AWS.

First Terraform example

terraform {
  required_providers {
    aws = { source = "hashicorp/aws" }
  }
}

provider "aws" {
  region = "ap-southeast-1"
}

resource "aws_s3_bucket" "raw" {
  bucket = "trade-data-raw"
}

How to read it

Provider tells Terraform which platform or API to use.

Resource declares an infrastructure object to manage.

aws_s3_bucket is the resource type, while raw is Terraform's local resource name.

terraform init
โ†’
terraform validate
โ†’
terraform plan
โ†’
terraform apply
โ†’
AWS

Variables

Variables make configuration reusable instead of hard-coding values such as region, environment or bucket names.

Outputs

Outputs expose useful values such as a VPC ID, bucket ARN or endpoint DNS name after deployment.

๐Ÿ—ƒ๏ธTerraform State, Backend & Lifecycletfstate ยท remote backend ยท locking ยท refresh ยท import ยท drift ยท sensitive dataโ€บ03
๐Ÿงช Hands-on Lab 03 โ€” See why Terraform state matters
Terminal โ€” inspect state
$ terraform state list
aws_s3_bucket.raw

$ terraform state show aws_s3_bucket.raw
# aws_s3_bucket.raw:
id     = "trade-dev-raw"
region = "ap-southeast-1"
Drift simulation
Console changeTag Environment: dev โ†’ test
Next planTerraform detects a difference and proposes reconciliation.! Drift
Think of state as Terraform's map: configuration address โ†” real AWS resource identity.
Terraform Code
โ†”
๐Ÿ“˜ State
โ†”
โ˜๏ธ Real AWS Resources

State is Terraform's mapping between configuration and real infrastructure. It records resource identities and attributes so Terraform can calculate what must change.

Local vs remote state

Local state is acceptable for learning. Teams normally use a remote backend so state is shared, protected and controlled.

Locking and concurrency

Production workflows should prevent two engineers or pipelines from modifying the same state at the same time.

Import

Import brings an existing resource under Terraform management; configuration still needs to describe the resource correctly.

Sensitive state

State can contain sensitive values. Restrict access, encrypt the backend and avoid treating the state file as ordinary source code.

Production rule: Do not commit terraform.tfstate to Git.

๐ŸงฉTerraform Modules & EnvironmentsModule ยท input / output ยท for_each ยท count ยท dev / staging / prod ยท reusable platform patternsโ€บ04
๐Ÿงช Hands-on Lab 04 โ€” Reuse one module for dev and prod
Project structure
infra/
โ”œโ”€โ”€ modules/
โ”‚   โ””โ”€โ”€ s3-data-lake/
โ”‚       โ”œโ”€โ”€ main.tf
โ”‚       โ”œโ”€โ”€ variables.tf
โ”‚       โ””โ”€โ”€ outputs.tf
โ””โ”€โ”€ environments/
    โ”œโ”€โ”€ dev/
    โ””โ”€โ”€ prod/
Root module usage
module "data_lake" {
  source      = "../../modules/s3-data-lake"
  environment = "dev"
  raw_bucket  = "trade-dev-raw"
}
The module contains the reusable design; each environment supplies values.

A module is a reusable package of Terraform resources. Instead of repeating VPC, S3, IAM and ECS definitions for every environment, create a tested module and pass environment-specific inputs.

Root configurationdev / staging / prod
โ†’
VPC modulesubnets ยท routes ยท endpoints
+
Data platform moduleS3 ยท Glue ยท IAM
+
Compute moduleECR ยท ECS Fargate

for_each vs count

Use for_each when instances have stable semantic keys; count is convenient for simple indexed repetition.

Environment strategy

Keep environment values separate while reusing the same modules. Production should not be a manually edited copy of development.

๐ŸŸ AWS CloudFormation FundamentalsTemplate ยท Resources ยท Parameters ยท Outputs ยท Stack ยท Change Set ยท rollback ยท driftโ€บ05
๐Ÿงช Hands-on Lab 05 โ€” Deploy the same idea with CloudFormation
AWS CLI
$ aws cloudformation deploy \
  --template-file template.yaml \
  --stack-name trade-dev \
  --parameter-overrides Environment=dev

Waiting for changeset to be created...
Successfully created/updated stack - trade-dev
CloudFormation Console โ€” stack view
CloudFormation โ€บ Stacks โ€บ trade-dev
Stack statusCREATE_COMPLETE
ResourcesRawBucket ยท AWS::S3::Bucket
OutputsRawBucketName = trade-dev-raw

First CloudFormation example

AWSTemplateFormatVersion: '2010-09-09'
Parameters:
  Environment:
    Type: String
Resources:
  RawBucket:
    Type: AWS::S3::Bucket
    Properties:
      BucketName: !Sub trade-${Environment}-raw
Outputs:
  RawBucketName:
    Value: !Ref RawBucket

Mental model

Template is the infrastructure definition.

Stack is the deployed collection of AWS resources.

Change Set previews proposed stack changes before execution.

Rollback helps return a failed stack update toward the previous stable state.

YAML / JSON Template
โ†’
Change Set
โ†’
CloudFormation Stack
โ†’
AWS Resources
โš–๏ธTerraform vs CloudFormationState ยท ecosystem ยท AWS-native integration ยท reuse ยท preview ยท rollback ยท drift ยท selectionโ€บ06
๐Ÿงช Hands-on Lab 06 โ€” Same change, two tools
Terraformterraform planPreview resource diffterraform applyApply through provider APIs
VS
CloudFormationcreate-change-setPreview stack changesexecute-change-setAWS manages stack lifecycle
Beginner memory: Terraform thinks in configuration + provider + state. CloudFormation thinks in template + change set + stack.

Quick decision: Terraform is strong when you want a consistent IaC workflow, reusable modules and infrastructure beyond AWS. CloudFormation is strong when the environment is AWS-only and the team prefers an AWS-native stack lifecycle.

DimensionTerraformCloudFormation
LanguageHCLYAML or JSON
ScopeMulti-provider and multi-cloudAWS-native
State modelExplicit Terraform state and backendAWS manages stack state
Previewterraform planChange Sets
ReuseModulesNested stacks, modules and reusable templates
RollbackHandled through workflow and corrective configurationBuilt-in stack rollback behavior
Typical strengthPlatform standardization and reusable cross-provider IaCDeep AWS-native lifecycle integration

Interview: Why Terraform?

I would choose Terraform when the organization wants a consistent infrastructure workflow, reusable modules and the flexibility to manage infrastructure beyond AWS.

Interview: Why CloudFormation?

I would choose CloudFormation when the environment is AWS-only and the team values a fully AWS-native stack lifecycle and service integration.

๐Ÿš€Production IaC โ€” CI/CD, Project Example & InterviewGit ยท review ยท plan ยท approval ยท apply ยท least privilege ยท drift ยท recovery ยท Trade Data Pipelineโ€บ07
๐Ÿงช Hands-on Lab 07 โ€” Simulate a production pull-request deployment
โ‘  Git PR
main.tf changed
โ†’
โ‘ก fmt + validate
syntax / style
โ†’
โ‘ข plan
1 add ยท 1 change
โ†’
โ‘ฃ approval
human review
โ†’
โ‘ค apply
production
Example plan review
Terraform will perform the following actions:
  + aws_s3_bucket.processed
  ~ aws_ecs_task_definition.downloader

Plan: 1 to add, 1 to change, 0 to destroy.
Production rule: do not treat apply as the first time you discover the change. The plan is the review artifact.

Production delivery flow

Git Pull Request
โ†’
Validate / Lint
โ†’
Plan
โ†’
Review + Approval
โ†’
Apply
โ†’
Drift / Monitoring

Apply IaC to this Trade Data Pipeline

NetworkVPC ยท private subnets ยท NAT ยท VPC endpoints
โ†“
SecurityIAM roles ยท KMS ยท Secrets Manager ยท security groups
โ†“
Data & ComputeS3 Raw / Processed ยท ECR ยท ECS Fargate ยท Glue
โ†“
Workflow & OperationsEventBridge ยท Step Functions ยท CloudWatch ยท SNS
๐Ÿงฉ Components in this module

Plan before apply

Review the proposed infrastructure diff before changing production. Treat infrastructure changes like application code changes.

Least-privilege pipeline identity

The CI/CD runner should have only the permissions required to deploy the approved infrastructure.

Drift control

Avoid manual console changes. Detect and reconcile differences between code and real infrastructure.

Failure recovery

Understand partial failures, state consistency, stack rollback and how to safely retry or correct the configuration.

30-second interview answer: I use Infrastructure as Code so AWS environments are reproducible and reviewable. For Terraform, I separate reusable modules from environment configuration, keep state in a protected remote backend, review terraform plan in CI/CD, require approval before production apply, and avoid manual drift. CloudFormation provides a similar declarative model with AWS-managed stacks and Change Sets, so the choice depends on the team's ecosystem and AWS-only versus broader infrastructure requirements.

2. Trade Document CSV ETL

Trade Document CSV ETL architecture from source CSV files to S3 Raw, AWS Glue ETL, S3 Processed, data quality reports and audit outputs.
Project visual: source CSV โ†’ S3 Raw โ†’ Glue ETL โ†’ S3 Processed โ†’ audit and reconciliation.

Trade Document Types

CINV is the main analytical document type in this project. INQ and PO are related trade-document types that can follow the same downstream standardization pattern.

TypeMeaningBusiness purpose
CINVCommercial InvoiceRecords an actual sale of goods or services, including seller, buyer, invoice number, date, quantity, unit price, tax, and total amount.
INQInquiry / RFQ-type documentSent before purchase to ask a supplier for price or availability; item and quantity may exist while price is still blank or zero.
POPurchase OrderA formal buyer order after the purchase decision, normally containing supplier, items, quantities, agreed prices, and delivery terms.
 
INQ / RFQ  โ†’  Quotation  โ†’  PO  โ†’  Delivery  โ†’  CINV  โ†’  Payment

One-line story

I built a reproducible and auditable Python ETL pipeline that converts multiple trade invoice CSV files into validated, deduplicated, transformed and traceable output datasets.

AI is my assistant. I use AI to speed up my work, but AI often generates solutions that are more complex than necessary. I review, validate, and simplify the output so it meets the actual requirements without over-engineering or unnecessary code.

Remember:CSV Sources โ†’ Quality โ†’ Business Rules โ†’ Trade Identifier โ†’ Outputs โ†’ Reconciliation
๐Ÿ—บ๏ธEnd-to-End MapPART 1 OF 301

End-to-End ETL Map โ€” explain the project from left to right

Failed = invalid or lower-amount duplicateReview = unusual but still validCleaned = accepted business records
๐ŸŽค
Presentation โ€” 60-second walkthroughUse this after showing the ETL map.
โ€บ
Start with the three-stage structure, then explain the exception paths and outputs.
01
Let me walk through the pipeline from left to right. It has three stages: ingest and prepare, clean and validate, and create outputs.
02
First, I read the ZIP programmatically, discover the CSV files, preserve the original CSV files, combine schema differences, filter the required period, and profile the data.
03
Second, I validate and standardise the business fields, and invalid records are written to Failed with an explicit reason.
04
I then recalculate the remaining invoice using the 99-year rule and a configurable as-of date so the result is reproducible.
05
For duplicates, I use the business key, keep the higher invoice amount, and send the lower-amount duplicate to Failed.
06
For price anomalies, I use IQR on unit price within the year, buyer, and product-category peer group. These records remain valid in Cleaned and are also copied to Review.
07
Finally, I create the prescribed trade identifier and its SHA-256 hash, producing Cleaned, Transformed, and Hashed outputs with clear traceability.
Line : Ingest & Prepare โ†’ Clean & Validate โ†’ Create Outputs.
โญSTAR StoryPART 2 OF 302
S

Situation โ€” Why was the pipeline needed?

Start with the business background, not the code.

Business context

The data platform needed to turn structured CINV outputs from upstream document extraction or client CSV exports into trusted datasets for downstream credit, risk and analytics users.

The required period was January 2020 to December 2022.

Data challenge

The input was a batch of multiple structured CSV files, and different client/export versions could omit optional columns such as unit_price.

The solution had to preserve raw client files, normalize schema differences, and avoid manual file-by-file processing.

Situation memory: Multiple CSV files + schema variation + strict business rules + auditable outputs.
T

Task โ€” What did I need to deliver?

Turn the assignment into four engineering objectives.

01Preserve & Combine

Keep CSV files unchanged and union schemas into one staging dataset.

02Validate & Clean

Standardise fields, recompute invoice, deduplicate and identify review cases.

03Transform & Protect

Create the prescribed identifier and generate its SHA-256 representation.

04Explain & Reconcile

Produce profiles, failure reasons, row counts, tests and a run manifest.

Senior-level framing

The task was not only to clean a CSV. It was to create a repeatable data product with clear modules, deterministic rules, separate exception paths, and reconciliation evidence.

A

Action โ€” How did I implement it?

This is the longest part of the answer. Follow the six numbered steps.

1

Ingest, preserve and profile

The pipeline opens the ZIP programmatically, discovers contributing CSV files and filters the required date range.

It performs a schema union: columns missing from older files are added as null so all records share one master schema.

Profiling records null counts, null percentages, data types, distinct counts and key categorical distributions.

2

Validate and standardise

Rules cover month, buyer_name, product category, item description, storey range, reference_no, street, floor area, invoice year and invoice amount.

Invalid records are removed from the accepted flow and written to Failed with an explicit failure reason.

3

Recompute unit price

I use a derived unit-price rule, the invoice commencement year and a configurable assessment_end_date.

The configurable date makes results reproducible instead of changing every time the pipeline runs.

invoice_end = invoice_year + amount รท quantityremaining = floor(invoice_end โˆ’ assessment_end_date) โ†’ years + months
4

Resolve duplicate invoice lines deterministically

The composite key uses all original business columns except amount.

For the same key, I retain the highest price and send lower-amount records to Failed as DUPLICATE_LOWER_PRICE.

Same composite key?โ†’Sort price descendingโ†’Rank 1: keepRank >1: Failed
5

Flag unusual unit prices with IQR

First, I calculate analysis_unit_price = amount / quantity so flats of different sizes are comparable.

I compare similar flats within the same year, buyer_name, and product category.

Q125th percentile
IQRQ3 โˆ’ Q1
LowerQ1 โˆ’ 1.5 ร— IQR
UpperQ3 + 1.5 ร— IQR

IQR measures the spread of the middle 50 percent of the analysis_unit_price values.

Values outside the 1.5-times-IQR bounds are flagged for Review, but not automatically rejected.

A flagged record remains in Cleaned and is also copied to Review, because unusual does not automatically mean invalid.

6

Create the identifier and hash

After quality rules, the pipeline builds the required identifier: T + reference suffix + peer amount code + month + buyer initial.

SBBBAAMMT
S fixed prefix
BBB first three reference_no digits, left-padded
AA first two digits of grouped average price
MM transaction month
T first buyer_name character

The clear identifier is written to Transformed, while Hashed contains its irreversible SHA-256 value encoded from UTF-8.

Because the prescribed identifier uses shortened grouped attributes, it is not guaranteed to be a transaction-level unique key.

๐ŸงฉCore Rules, Code & OutputsPART 3 OF 303

Core rules โ€” the five points most likely to be questioned

RuleImplementationWhy
Schema differencesUnion all source columns; missing historical fields become null.Preserves every source attribute without manual edits.
Unit price recomputederived unit-price rule + configurable assessment_end_date; round down to years and months.Reproducible and aligned with the requirement.
DuplicatesComposite business key excludes amount; highest amount survives.Deterministic and auditable duplicate resolution.
Unit-price anomalyUnit price, grouped by year + buyer + product category, using 1.5ร—IQR bounds.Creates comparable invoice-item peer groups while keeping review cases in the cleaned dataset.
IdentifierT + reference suffix + peer amount code + month + buyer initial, then SHA-256.Follows the prescribed transformation and irreversible hashing requirement.

Code structure โ€” how the design stays maintainable

I organised the implementation as a small Python package rather than placing every rule in one notebook or script.

src/trade_pipeline/
โ”œโ”€โ”€ main.py             # Command-line entry point
โ”œโ”€โ”€ config.py           # Pipeline configuration
โ”œโ”€โ”€ ingestion.py        # Source discovery, extraction and schema union
โ”œโ”€โ”€ data_quality.py     # Profiling, validation, invoice, deduplication and anomaly detection
โ”œโ”€โ”€ transformation.py   # Trade Identifier creation and SHA-256 hashing
โ”œโ”€โ”€ output.py           # Output datasets and run manifest
โ””โ”€โ”€ pipeline.py         # End-to-end ETL orchestration
main.pyParse CLI arguments
โ†’
pipeline.pyControl the complete ETL sequence
โ†“
config.pyPaths, as-of date and parameters
ingestion.pyZIP โ†’ CSV discovery โ†’ schema union
data_quality.pyProfile โ†’ validate โ†’ invoice โ†’ deduplicate โ†’ IQR
transformation.pyIdentifier โ†’ SHA-256
โ†“
output.pyWrite datasets, manifest and reconciliation evidence
How to explain the diagram: pipeline.py is deliberately thin. It coordinates the sequence, while each specialist module owns one category of responsibility. This reduces coupling and lets validation, transformation or output logic be tested and changed independently.
Single responsibilityEach module owns one clear concern.
Configuration-drivenNo business date is hidden inside rule logic.
TestableRules can be unit-tested without running the full pipeline.
AuditableOutputs, failure reasons and manifest explain every run.
R

Result โ€” What did the solution produce?

Explain outputs as evidence, not only as folders.

RawOriginal contributing CSV files
StagingCombined records in the required period
ProfilingQuality statistics and distributions
CleanedValid and deduplicated records
FailedInvalid and lower-amount duplicates
ReviewUnusual prices; also retained in Cleaned
TransformedCleaned data + clear identifier
HashedCleaned data + SHA-256 identifier
ManifestExecution metadata, counts and reconciliation
QualityExplicit rules and failure reasons
MaintainabilityResponsibility-based Python modules
ReliabilityTests and deterministic logic
TraceabilityRaw preservation and run reconciliation

Presentation-ready STAR answer

Situation: for the BCA Indonesia client workflow, the AWS Trade Data Pipeline needed a programmatic and reproducible way to prepare multiple structured CINV CSV batches for downstream business-activity and risk analytics.

Task: I had to preserve raw data, enforce data quality and business rules, create the prescribed identifier and produce auditable outputs.

Action: I separated the pipeline into ingestion, data quality, transformation, output and orchestration modules. I unioned changing schemas, profiled and validated data, recomputed unit price as amount divided by quantity, kept the higher amount for duplicate business keys, and applied peer-group IQR to unit price for review cases. Finally, I generated the trade identifier and SHA-256 hash.

Result: The pipeline produces Raw, Staging, Profiling, Cleaned, Failed, Review, Transformed and Hashed outputs, together with a run manifest, reconciliation counts and automated tests.

30-second memory:Preserve โ†’ Profile โ†’ Validate โ†’ Invoice โ†’ Deduplicate โ†’ Review โ†’ Identify โ†’ Hash โ†’ Reconcile.

3. AWS Data Ingestion

AWS data ingestion architecture using S3, optional Kinesis, Glue Data Catalog and Athena.
Ingestion visual: sources โ†’ catalog/metadata โ†’ S3 data lake โ†’ Athena query layer.
๐ŸššHow to explain this ingestion architectureClick to open the key flow; each sentence has audio.
01
Structured trade CSV files enter the pipeline as batch source data and are landed in Amazon S3 as the durable raw layer.
02
AWS Glue uses the registered schema and metadata to transform the raw files into standardized, analytics-ready datasets.
03
The processed data remains in Amazon S3, while the AWS Glue Data Catalog stores table, schema, and partition metadata rather than the data itself.
04
Amazon Athena provides the serverless SQL query layer over the processed S3 datasets, so downstream users do not query raw files directly.
05
This separation keeps ingestion, storage, metadata, transformation, and query responsibilities clear and independently manageable.
Main route
โฑ๏ธEventBridgeTrigger
๐ŸงฉStep FunctionsOrchestrate
๐Ÿ“ฅFargateDownload
๐ŸชฃS3 RawPreserve
โš™๏ธGlueTransform
๐Ÿ“ฆS3 ProcessedPublish
External client endpointFargate โ†’ NAT Gateway โ†’ Client SFTP Endpoint
๐Ÿ”’ Private AWS pathFargate โ†’ S3 Gateway Endpoint โ†’ S3 Raw
๐Ÿ“ฆ Large CSV batchStreaming + multipart upload + SHA-256 metadata
๐Ÿ—บ๏ธArchitecture DiagramPART 1 OF 301
๐Ÿ—บ๏ธ

Architecture Diagram

PART 1 OF 3

Use the diagram and the five-step presentation to explain the end-to-end ingestion path.

AWS trade CSV ingestion architecture

AWS trade CSV ingestion architecture
๐ŸŽค Presentation
Let me start with a quick overview of the architecture, focusing on the data flow, private network paths, and large-file handling.
01
EventBridge Scheduler starts the Step Functions workflow.
02
Step Functions runs the downloader, a Python application packaged as a Docker container, on ECS Fargate.
03
The downloader accesses Client SFTP Endpoint through the NAT Gateway and Internet Gateway.
04
It streams each CSV file file to the S3 Raw Zone through the S3 Gateway VPC Endpoint, using multipart upload for large CSV batchs.
05
After the download and S3 upload succeed, Step Functions starts the AWS Glue ETL job.
06
AWS Glue reimplements the core Part 1 ETL logic and writes the processed datasets to the S3 Processed Zone, while updating the Glue Data Catalog.
07
CloudWatch collects logs and metrics, while Amazon SNS sends failure notifications.
๐Ÿ”Network, Large Files & SecurityPART 2 OF 302
๐Ÿ”

Network, Large Files & Security

PART 2 OF 3

These three design considerations explain how the ingestion path stays private where possible, handles large CSV batchs, and remains production-oriented.

๐ŸŒ

Network

SFTP credentials / SSH key are stored in AWS Secrets Manager and retrieved by the Fargate task at runtime; they are not hard-coded in the container image.

Fargate stays in a private subnet with no public IP. SFTP/SSH access to Client SFTP Endpoint goes through the NAT Gateway.

S3 traffic uses the S3 Gateway VPC Endpoint, so AWS storage access stays on a private path.

๐Ÿ“ฆ

Large CSV batches

The downloader streams the source and uses multipart upload instead of loading the whole file into memory.

SHA-256 metadata provides an integrity fingerprint for traceability and duplicate checks.

๐Ÿ›ก๏ธ

Security & operations

Step Functions starts Glue only after the download and S3 upload succeed; failures can be retried cleanly.

CloudWatch provides logs and operational visibility, while IAM and encryption protect access to the data.

4. AWS Data Exploitation

Governed analytics architecture using Amazon S3, AWS Glue Data Catalog, Amazon Athena, Tableau Server on EC2 and business users.
Analytics visual: S3 stores โ†’ Glue describes โ†’ Athena queries โ†’ Tableau visualizes โ†’ users consume.
๐Ÿ“ŠHow to explain this analytics architectureClick to open the interview-ready flow; each sentence has audio.
01
Business users access Tableau, while Tableau Server runs on Amazon EC2 inside the analytics environment.
02
Tableau submits SQL queries to Amazon Athena instead of reading Amazon S3 directly.
03
Athena uses metadata from the AWS Glue Data Catalog to understand the table schema, S3 location, and partitions.
04
Athena then queries the processed Parquet data in Amazon S3 and writes query results to the configured S3 result location.
05
IAM controls AWS resource access, while Tableau groups control human access to dashboards and data sources.
06
Private VPC endpoints can keep Athena API and S3 traffic on the AWS network without requiring public service access.
๐Ÿ—บ๏ธArchitecture DiagramPART 1 OF 301
๐Ÿ—บ๏ธ

Architecture Diagram

A concise visual walkthrough of how Tableau reaches Athena privately and queries governed data in S3.

Tableau on EC2Athena private APIGlue metadataS3 processed data

AWS trade data exploitation architecture

AWS trade data exploitation architecture
๐ŸŽค Presentation
The workflow is:
Line : Analytics / Risk Users โ†’ Tableau โ†’ Athena โ†’ Glue Catalog + S3 โ†’ Query Results โ†’ Governance
01
Internal users access Tableau through a private connection or VPN.
02
Tableau runs on Amazon EC2 in a private subnet without a public IP address.
03
Tableau submits SQL queries to Amazon Athena through the Athena JDBC driver.
04
The Athena Interface VPC Endpoint provides private access to the Athena API.
05
Athena reads table and partition metadata from the AWS Glue Data Catalog.
06
Athena queries the partitioned Parquet datasets in the S3 Processed Zone.
07
Query results are written to the S3 Athena Query Results bucket.
08
The Athena Workgroup controls the result location, encryption and query limits.
09
IAM provides access control, while CloudWatch provides logging, metrics and monitoring.
๐Ÿ”Private Access & VPC DesignPART 2 OF 302
๐Ÿ”

Private Access & VPC Design

Three practical controls keep the analytics path private, governed, and easy to explain.

Private Tableau accessAthena Interface EndpointS3 Gateway EndpointClear VPC boundary

Private Access & VPC Design

๐Ÿ‘ค

Private entry

Internal users reach Tableau on EC2 through the approved corporate network or VPN. The EC2 instance stays in a private analytics subnet with no public IP.

๐Ÿ”’

Private Athena API path

Tableau uses the Athena JDBC or ODBC driver. The Athena Interface VPC Endpoint with Private DNS keeps the API call private.

๐Ÿ—‚๏ธ

Data, metadata & VPC boundary

Athena uses Glue Data Catalog metadata to query processed Parquet in S3, and query-result S3 access can use the S3 Gateway VPC Endpoint.

Separate application and analytics VPCs are a design choice for clearer isolation; a shared VPC with separate subnets is also valid for a smaller environment.