A complete AWS data pipeline typically follows this architecture:

1. Ingestion: Use Kinesis Data Streams for real-time data or S3 Transfer Family for batch file transfers. For databases, AWS DMS handles CDC.

2. Storage (Raw Zone): All incoming data lands in S3 organized as s3://bucket/raw/{source}/{year}/{month}/{day}/

3. Cataloging: Glue Crawlers run on a schedule to discover schemas and populate the Glue Data Catalog.

4. Transformation: Glue ETL jobs (or EMR for large-scale Spark) read from raw zones, apply business logic, and write to processed zones in Parquet format.

5. Serving:

   • Athena for ad-hoc serverless queries
   • Redshift for complex analytical workloads and joins
   • OpenSearch for full-text search and log analytics

6. Orchestration: Step Functions coordinate the pipeline with error handling, retry logic, and notification on failure.

7. Visualization: QuickSight connects to Athena and Redshift for dashboards.

8. Monitoring: CloudWatch tracks job metrics; CloudTrail provides audit logs; Lake Formation enforces access policies.

Architecture for high-throughput real-time analytics:

1. Ingestion Layer: Kinesis Data Streams with multiple shards (each shard handles 1 MB/s in, 2 MB/s out). Use enhanced fan-out for multiple consumers.

2. Processing: Kinesis Data Analytics with Apache Flink for windowed aggregations (tumbling windows of 1 minute). Alternatively, use Lambda with Kinesis trigger for simpler transformations.

3. Real-time Storage: DynamoDB with DAX (caching) for sub-millisecond reads of aggregated metrics. Use global tables for multi-region availability.

4. Batch Rollup: Kinesis Firehose buffers data to S3 every 60 seconds. Glue jobs aggregate hourly/daily summaries into Redshift.

5. Visualization: QuickSight with SPICE for dashboard rendering. Use refresh schedules aligned with data availability.

6. Alerting: CloudWatch Alarms on Kinesis iterator age and Lambda error rates. SNS for PagerDuty integration.

Scaling considerations: Auto-shard Kinesis based on IncomingBytes and IncomingRecords metrics. Use Lambda reserved concurrency to prevent throttling.

Kinesis Data Streams (KDS):

• Real-time streaming with 200ms latency
• Custom consumer applications (KCL, Lambda, SDK)
• Manual shard management and scaling
• Data retention up to 365 days
• Exactly-once processing with enhanced fan-out
• Use when: You need real-time processing, custom logic, or exactly-once semantics

Kinesis Data Firehose (KDF):

• Near-real-time with 60-900 second buffer
• Managed delivery to S3, Redshift, OpenSearch, HTTP endpoints
• Automatic scaling (no shard management)
• Data conversion (JSON to Parquet/ORC)
• Built-in compression and encryption
• Use when: You need simple, managed delivery without custom processing

Decision Matrix:

Schema evolution strategies for S3-based data lakes:

1. Partitioned Writes: Use Hive-style partitioning with dynamic partitions. New partitions automatically get the new schema.

2. Iceberg/Delta Lake: Use table formats that support schema evolution natively:

   • Add columns without rewriting existing data
   • Rename columns (metadata-only operation)
   • Change column types with compatibility rules

3. Glue Schema Registry: Register Avro/JSON schemas. Consumer applications detect schema changes and handle them gracefully.

4. Column-Level Lineage: Use AWS Lake Formation to track which schema version each dataset uses.

5. Best Practices:

   • Never delete columns (add new, deprecate old)
   • Add new columns at the end
   • Use nullable types for backward compatibility
   • Version datasets with suffixes: orders_v2/

# Glue ETL with schema evolution
spark.conf.set("spark.sql.schemaEvolution.enabled", "true")
df.write.mode("append").partitionBy("year", "month").parquet("s3://lake/cleaned/orders/")

Lambda Architecture (two layers):

• Batch Layer: Processes complete historical dataset (Glue + Redshift)
• Speed Layer: Processes real-time data (Kinesis + Lambda)
• Serving Layer: Merges both views for queries

Pros: Handles both historical and real-time, fault-tolerant Cons: Two codebases to maintain, complexity, storage overhead

Kappa Architecture (single layer):

• Single stream processing layer (Kafka/Flink)
• Reprocess from beginning of log when logic changes
• All data treated as a stream

Pros: Simpler operations, one codebase, easier debugging Cons: Replay can be expensive, stream-only processing

Recommendation:

• Use Lambda when: you have complex batch transformations, need batch corrections, or have distinct batch and real-time use cases
• Use Kappa when: your processing logic is relatively simple, you want operational simplicity, or you are building greenfield

On AWS: Lambda architecture maps naturally to Glue+Redshift (batch) + Kinesis+Lambda (speed). Kappa maps to Managed Flink + Kinesis.

AWS-native data quality implementation:

1. Glue DataBrew: Visual data profiling with built-in quality rules. Detect anomalies, duplicates, and missing values before ETL.

2. Custom Glue Jobs with Great Expectations:

import great_expectations as ge
df = ge.from_pandas(spark_df)
df.expect_column_values_to_not_be_null("order_id")
df.expect_column_values_to_be_between("amount", min_value=0, max_value=100000)
results = df.validate()

1. Step Functions Validation Step: Add a Lambda validation step before downstream processing. If validation fails, write to quarantine S3 bucket and send SNS alert.

2. CloudWatch Metrics: Emit custom metrics for record counts, null rates, and schema drift. Set alarms on thresholds.

3. Athena for Ad-hoc Quality: Run SQL queries against raw data to spot-check completeness:

SELECT date, COUNT(*) as cnt, COUNT(DISTINCT order_id) as unique_orders
FROM raw_orders
GROUP BY date
HAVING cnt != unique_orders OR cnt < 1000;

1. Automated Alerts: Use EventBridge to trigger Lambda functions when quality metrics fall below thresholds.

Phase 1: Assessment

• Profile source data volumes, query patterns, and SLAs
• Map schema types (Oracle/SQL Server to Redshift)
• Identify dependencies and downstream consumers

Phase 2: Infrastructure Setup

• Provision Redshift cluster (RA3 nodes for managed storage)
• Set up VPC, security groups, and IAM roles
• Configure S3 buckets for staging (raw, processed, archive)

Phase 3: Schema Migration

• Use AWS Schema Conversion Tool (SCT) for automated conversion
• Handle stored procedures, views, and functions manually
• Create distribution keys and sort keys for optimal performance

Phase 4: Data Migration

• Initial load: Use DMS for bulk data transfer or S3 Copy Command
• Set up ongoing CDC with DMS for zero-downtime migration
• Validate row counts and checksums post-migration

Phase 5: Query Migration

• Convert source SQL to Redshift-optimized syntax
• Replace proprietary functions with Redshift equivalents
• Implement materialized views for frequently-run queries

Phase 6: Cutover

• Dual-run period (2-4 weeks) comparing results
• Update connection strings in applications
• Decommission legacy system after validation

Key Redshift Tips: Use COPY command for bulk loads, leverage STL query tables for performance tuning, enable concurrency scaling for peak loads.

Storage Optimization:

• S3 Intelligent-Tiering for unpredictable access patterns
• S3 Lifecycle policies to move infrequent data to Glacier
• Redshift Managed Storage with auto-scaling
• Compress data (Parquet + Snappy reduces storage 4-10x)

Compute Optimization:

• Lambda: Right-size memory (CPU scales proportionally)
• EMR: Use Spot Instances (60-70% savings) for non-critical jobs
• Glue: Use auto-scaling workers and schedule crawlers off-peak
• Redshift: Pause during off-hours, resume for batch runs

Data Architecture Optimization:

• Partition data by date to minimize scanned data
• Use columnar formats (Parquet) to skip unnecessary columns
• Implement data retention policies (archive old data)
• Deduplicate early in the pipeline to reduce downstream processing

Monitoring & Alerts:

• Cost Explorer with budget alerts
• CloudWatch custom metrics for per-pipeline cost tracking
• Trusted Advisor recommendations for idle resources

AWS CDC Architecture:

1. Source Database: Enable logical replication (PostgreSQL) or use Oracle LogMiner / SQL Server CDC feature.

2. AWS DMS: Create a replication instance with CDC task:

   • Full load + CDC mode for initial sync + ongoing changes
   • Table mapping rules to filter specific tables/schemas
   • LOB handling configuration for large objects

3. Target Options:

   • S3: DMS directly writes to S3 in Parquet format
   • Kinesis: DMS streams changes to Kinesis for real-time processing
   • Redshift: DMS with bulk insert for near-real-time warehousing
   • Aurora: DMS for cross-region replication

4. Processing: Lambda or Kinesis Data Analytics processes the CDC stream for:

   • Deduplication (last-write-wins or merge logic)
   • Schema transformation
   • Enrichment with reference data

5. Downstream: Write processed CDC events to:

   • DynamoDB for operational queries
   • S3 for historical archive
   • OpenSearch for search indexing

DMS Task Configuration Example:

• CDCStartTime: Beginning of capture window
• LOBSupport: Full for LOB columns
• ParallelLoadThreads: 4-8 for performance
• ParallelLoadBufferSize: 500 for batch size

Multi-Region Data Architecture Patterns:

1. Active-Active: Both regions process data simultaneously

   • DynamoDB Global Tables for real-time sync
   • Route 53 with health checks for failover
   • S3 Cross-Region Replication for data backup

2. Active-Passive: Primary region handles all traffic; secondary is standby

   • DMS for continuous replication to standby
   • CloudFront for static content from both regions
   • Redshift cross-region snapshot copy

3. Data Sovereignty: Data stays in specific regions per regulation

   • Region-locked S3 buckets
   • KMS keys per region
   • VPC endpoints to prevent data egress

Key Services:

Design Principles:

• Design for failure in each region independently
• Use eventual consistency for cross-region sync
• Implement conflict resolution (last-write-wins or application-level)
• Monitor cross-region latency and replication lag