AWS - Data Pipeline Suggestions
Brainstorming on possible solutions for pipeline we're working on.
We've been given access to a client's S3 buckets (7 buckets with ~8 folders each) which receive new data every few mins or so. Files are small (~10kB)
Goal is to transform the data with an existing Airflow pattern and load it into a Redshift cluster
Client S3 --> ??? --> Landing S3 --> Airflow (dbt/Athena) --> Transformed S3 --> ??? --> Redshift
We're trying to figure out what should go in the ??? at each end.
For Client S3 --> Landing S3, would something like this be appropriate:
Client S3 --> SQS --> EventBridge --> Lambda --> Firehose --> Landing S3
For Transformed S3 --> Redshift, what about:
Transformed S3 --> SNS --> Redshift Spectrum --> Redshift
Are we over complicating things?
Does anything seem superfluous? (excluding the Airflow component as that's required)
4 comments · 81% upvoted · other discussions
I think you've left out some useful bits of information like whether it is a requirement that the data be processed in real-time. Also, how many files are there in each batch?
I'm inclined to suggest triggering a lambda function which publishes the update to a message queue for each batch of S3 files (similar to the what you've outlined).
elus [OP] 1 points · Mar 2023 · Temp
7 buckets x 8 folders x say 5 mins per file ~ 11 files/minute
Data doesn't have to be real time but not opposed to such a pattern.
Allow for the system to scale by 2 orders of magnitude in file size and number of objects.
knowledgebass 2 points · Mar 2023
I'm probably a bit of a smooth brain and would just start by coding everything in an Airflow DAG with a series of operators and then breaking that apart into AWS services as needed. I don't see your small amount of data requiring such an extensive pipeline design, at least to start.
Something like:
Airflow DAG triggers every ~5 minutes (based on your requirements) and within that...
Find new client files and copy them to landing bucket
Transform and write new S3 files
Use Redshift Spectrum or other interface to read that data using SQL and then write to your Redshift table
I would do all the steps using boto3 with PythonOperator. Then once you get that working and tested, start adding in message queues and what not if you feel like it is needed. Debugging a pipeline of AWS services is such a pain in the ass that I'd basically "tracer bullet" the procedure first. The final form may not look all that similar but at least you start with something relatively simple and understandable, encapsulated in a single DAG.
I also feel like tools such as Firehose and SQS are overkill for such a small amount of data. Airflow should give you good enough logging, monitoring, etc. I'm not sure making it a lot more complicated gets you much.
elus [OP] 2 points · Mar 2023 · Temp
That's in line with our initial analysis.
The only thing we're wrestling with is whether or not the landing bucket is necessary at all.
We don't have full access to the schemas we're ingesting at the moment but will map out the data lineage and see once we get set up.
Thanks for notes!