Context

One day, your friend comes to you and offers you a business opportunity. He recently built an analytics system that reads bank transactions and provide customized reports to his millionaire users 🤑. These millionaire users are banking with a secret bank, Alpaca Bank (1). He asks you to build a bank feeds system, that will:

• Connect to the bank SFTP (2) server

• Download encrypted files on the SFTP server

• Extract bank transactions and then store them to a database.

His system will then consume data from that database. The bank will provide SFTP connection information and a public key to decrypt their files. All you need to do is building a PoC (3) for him by showing a system that does exactly as described. This is a great opportunity. You are very excited and accept the challenge as well as his offer 😎

Brainstorming

At this stage, Alpaca Bank doesn’t provide SFTP server connection yet. You will need to build an own SFTP server for this PoC purpose. Fortunately, you know AWS has AWS Transfer Family, that can help you create an SFTP server with just some clicks.

As you are familiar with AWS, you decide to build everything on AWS cloud:

• You will create an SFTP server using AWS Transfer Family. This will require to create an S3 bucket.
• You will simulate the way Alpaca Bank drop files into their SFTP server by creating a Lambda function that is scheduled to run every 30 minutes. You call this lambda function FileGenerator.
• FileGenerator lambda function will create an encrypted transaction file and put it into the SFTP server. Then you will use AWS EventBridge to create a scheduler to trigger this lambda function.
• Next, you will build another Lambda function to connect to the SFTP server and get the file also decrypt it. The decrypted file will be stored to a folder on an AWS S3 bucket. You call this lambda function Transporter. Transporter lambda function will be triggered to call every 30 minutes. Similarly, you will use AWS EventBridge to schedule this event.
• AWS S3 folder is configured to generate notifications. Once the file is dropped into the S3 bucket, it triggers another lambda function. You call this lambda function Extractor. Extractor lambda function will extract data from the file in S3 bucket and store bank transactions into a database. It also moves the processed file into an archive folder on S3 bucket.
• Your friend’s system uses Microsoft SQL server, so you will create a Microsoft SQL server on AWS for this PoC purpose.

Long story short, these main technologies and services will be used:

• C# .NET Core
• Microsoft SQL Server
• AWS Transfer Family
• AWS S3 bucket
• AWS EventBridge
• AWS Lambda function

In the next parts, you will create all components above to complete the PoC, includes:

• SFTP server using AWS Transfer Family. It will need to create an S3 bucket.
• FileGenerator, Transporter, and Extractor lambda functions using C# .NET, AWS lambda function.
• EventBridge rules to trigger FileGenerator and Transporter lamda functions.
• Another S3 bucket to store files, and will need to configure bucket notifications to trigger Extractor lambda function.
• A Microsoft SQL Server database on AWS.

To be continued…
Originally published on medium.

(1): Alpaca Bank is not a real bank. It’s a random name used in this article.

(2): SFTP: Secure File Transfer Protocol

(3): PoC: Proof of Concept