Realtime Integration Data SAP – Satelite Application

Overview

A high-performance, event-driven integration built with Python and AWS Lambda to synchronize data between SAP and satellite applications (APC, Recap Invoice, FMS). The pipeline replaces slow, periodic batch jobs with a real-time, serverless flow that pushes SAP data into the application database within milliseconds — without overloading the database.

The Problem

• Data transfer from SAP to the applications could not be sequential or run on a periodic schedule.
• Target throughput was around 2,000 files per day.
• The database could not become a bottleneck — no RAM/CPU spikes were acceptable.

The previous approach relied on periodic jobs (e.g. Airflow exporting SAP data to XML pools on a schedule). Under heavy load, large volumes of data could not be processed in time, causing delays and backlogs.

The Approach

The solution is a fully serverless, event-driven pipeline:

SAP → S3 → SQS → Lambda → PostgreSQL

When SAP produces a file, it lands in an S3 bucket. S3 emits an event notification into an SQS queue, which acts as a buffer of change events. Lambda consumes those events in batches and writes directly to PostgreSQL.

Why direct Lambda → PostgreSQL (instead of Lambda → API → DB)

Two options were evaluated:

1. Lambda calls an API (Golang/Express/Laravel) that inserts into the DB. More flexible source code and easier custom logging, but it depends on an always-on EC2 instance, risks bottlenecks when 2,000 files arrive at once, and adds more moving parts that can fail.
2. Lambda inserts directly into PostgreSQL (chosen). Highly scalable and parallel, low cost (pay per execution duration), no server maintenance, simple flow, and full monitoring via CloudWatch.

The main trade-offs of the direct approach — limited DB connection pool under high parallelism, the 15-minute / 10 GB Lambda limits, stateless logic requirements, and retry/duplicate-insert risks — were all manageable. The connection-pool concern in particular is solved by SQS batching.

How SQS protects the database

SQS sits between S3 and Lambda as the event manager. Instead of an end-to-end process, S3 events are queued and consumed by Lambda in controlled batches:

• SQS is configured as the event trigger for Lambda.
• The Lambda batch size controls how many messages one invocation processes at once (e.g. a batch of 5 means one Lambda run handles 5 messages).
• Concurrency controls how many Lambdas run in parallel (e.g. 50 concurrent instances polling the queue).

With this tuning, roughly 250 messages can be processed every ~0.5 seconds, so 2,000 files complete in about 4 seconds — stable, and without overwhelming PostgreSQL.

Implementation (Setup Summary)

The infrastructure team provisions the S3 bucket and links it to the EC2 directory. From there the pipeline is assembled in AWS:

1. Lambda function — created on Python 3.11+, attached to the VPC, subnets, and security group so it can reach internal services and the database.
2. SQS queue — a Standard queue with a visibility timeout (~60s) to hold S3 change events.
3. Lambda trigger — the SQS queue is added as the Lambda event source; the batch size is set here.
4. S3 event notification — the bucket is configured to send object-creation events (PUT/POST) to the SQS queue.
5. Permissions — a queue policy allows S3 to send messages to SQS, and the Lambda execution role is granted ReceiveMessage, DeleteMessage, and GetQueueAttributes on the queue.
6. Verification — uploading a test file to S3, confirming the message appears in SQS, and checking CloudWatch logs to confirm Lambda ran the business function.

Results

Testing with 2,000 files (Lambda batch size of 5):

• File uploaded to S3 → S3 detects the new file and notifies SQS → SQS holds the message → Lambda triggers on batch → business logic runs → record saved to PostgreSQL.
• End-to-end latency was roughly 190 ms per file (varies with source-code complexity).
• Total for 2,000 files ≈ 285 seconds (~4 minutes).
• Because the queue is FIFO in effect (first in, first out), there is no periodic wait and no forced sequential ordering — when SAP creates a file, the data reaches the application database in ~190–300 ms.

Database performance (during 2,000-file run)

• PostgreSQL CPU started at 6.21%.
• Peak CPU spike reached 10.23%.
• Average CPU usage stayed around 1–4%.

The database was never stressed, confirming the no-bottleneck requirement was met.

Cost Estimation

Assuming 20,000 files/month, ~1 KB each (≈0.02 GB), one SQS message per file, 190 ms Lambda execution on 128 MB memory:

• S3 storage: ≈ $0.0005
• S3 PUT requests (20,000): ≈ $0.10
• SQS (within 1M free tier): Free
• Lambda (3,800,000 ms total): ≈ $0.0065
• Total: ≈ $0.11 / month

Lessons Learned

• Always return at the end of the handler. If the handler only raises or prints on error without returning, SQS keeps the message and re-executes it indefinitely. Returning lets the message be deleted after successful processing.
• Use a Dead Letter Queue (DLQ) for controlled retries. Because returning removes the message, a plain return blocks legitimate retries. A DLQ solves this: if Lambda fails to process a message a set number of times (e.g. 5), the message is moved to the DLQ instead of being lost. From there it can be re-driven back into the main queue and reprocessed with the same parameters — so no file gets stuck unprocessed.