Load large flat file based data in warehouse from s3

Niks Jain 0
Viewed 39

This is more of design and architecture question so please bare with me.

Requirement - Lets consider we have 2 type of flat files (csv, xls or txt) for below two db tables.

Doctor
   name
   degree
   ...
Patient
   name
   doctorId
   age
   ...

each file contains data of each tables respectively. (volume 2-3millions each file). we have to load these two files data into Doctor, Patient table of warehouse. after some of the validations like null value, foreign key, duplicates in doctor etc.. if any invalid data identifies, i will need to attach the reasons like null value, duplicate value. so that i can evaluate the invalid data. Note that, expectations is to load 1 million records in ~1-2mins of span.

My Designed workflow (so far)

After several articles and blog reading i find it to go with AWS Glue & Databrew of my ETL for source to target along with custom validations.

Please find below design and architecture. Suggest & guide me on it. Is there any scope of parallel or partition based processing for quick validation and loading the data? Your help is going to really help me and others (who gonna come to this type of case).

Thanks a ton.

enter image description here

etl aws-glue amazon-s3 amazon-web-services python
1 Answers

I'm not sure if you're asking the same thing, but your architecture should follow these guidelines

  1. File land to S3 raw bucket.

  2. Your lambda trigger once file put on S3 bucket.

  3. AWS lambda trigger will invoke Step function which contains following steps

    3.1 ) Data governance (AWS Deeque) check all validations

    3.2 ) perform transformation

  4. Move process data to your process bucket where you have data for reconciliation and other process.

  5. Finally your data move to your production bucket where you have only required process data not all

Note :

  1. Partitioning is help to achieve parallels processing in Glue.
  2. Your lambda and ETL logic should be stateless so rerun will not corrupts your data.
  3. All Synchronous call should use proper retry Exponential Backoff and jitter.
  4. Logs every steps into DynamoDB table so analysis logs and help in reconciliation.
vaquar khan0
Related