Use a reusable ETL framework in your AWS lake house architecture
AWS Architecture BlogUse a reusable ETL framework in your AWS lake house architectureby Ashutosh Dubey and Prantik Gachhayat | on11 AUG 2023| inAmazon EventBridge,Amazon Managed Workflows for Apache Airflow (Amazon MWAA),Amazon Redshift,Architecture,AWS Glue,AWS Lambda|Permalink|Comments|ShareData lakes and lake house architectures have become an integral part of a data platform for any organization. However, you may face multiple challenges while developing a lake house platform and integrating with various source systems. In this blog, we will address these challenges and show how our framework can help mitigate these issues.Lake house architecture using AWSFigure 1 shows a typical lake house implementation in anAmazon Web Services(AWS) environment.Figure 1. Typical lake house implementation in AWSIn this diagram we have five layers. The number of layers and names can vary per environmental requirements, so checkrecommended data layersfor more details.Landing layer.This is where all source files are dropped in their original format.Raw layer.This is where all source files are converted and stored in a common parquet format.Stage layer.This is where we maintain a history of dimensional tables asSlowly Changing DimensionType 2 (SCD2).Apache Hudiis used for SCD2 in theAmazon Simple Storage Service(Amazon S3) bucket, and anAWS Gluejob is used to write to Hudi tables. AWS Glue is used to perform any extract, transform, and load (ETL) job to move, cleanse, validate, or transform files between any two layers. For details, seeusing the Hudi framework in AWS Glue.Presentation layer.This is where data is being cleansed, validated, and transformed, using an AWS Glue job, in accordance with business requirements.Data warehouse layer.Amazon Redshiftis being used as the data warehouse where the curated or cleansed data resides. We can either copy the data using an AWS Glue python shell job, or create a Spectrum table out of the Amazon S3 location.The data lake house architecture shows two types of data ingestion patterns,pushandpull. In thepull-basedingestion, services like AWS Glue orAWS Lambdaare used to pull data from sources like databases, APIs, or flat files into the data lake. In thepush-basedpattern, third-party sources can directly upload files into a landing Amazon S3 bucket in the data lake.Amazon Managed Workflows for Apache Airflow(Amazon MWAA) is used to orchestrate data pipelines that move data from the source systems into a data warehouse.Amazon EventBridgeis used to schedule the Airflow directed acyclic graph (DAG) data pipelines.Amazon RDS for PostgreSQLis used to store metadata for configuration of the data pipelines. A data lake architecture with these capabilities provides a scalable, reliable, and efficient solution for data pipelines.Data pipeline challengesMaintaining data pipelines in a large lake house environment can be quite challenging. There are a number of hurdles one faces regularly. Creating individual AWS Glue jobs for each task in every Airflow DAG can lead to hundreds of AWS Glue jobs to manage. Error handling and job restarting gets increasingly more complex as the number of pipelines grows. Developing a new data pipeline from scratch takes time, due to the boilerplate code involved. The production support team can find it challenging to monitor and support such a large number of data pipelines. Data platform monitoring becomes arduous at that scale. Ensuring overall maintainability, robustness, and governability of data pipelines in a lake house is a constant struggle.The benefits of a data pipeline frameworkHaving a data pipeline framework can significantly reduce the effort required to build data pipelines. This framework should be able to create a lake house environment that is easy to maintain and manage. It should also increase the reusability of code across data pipelines. Effective error handling and recovery mechanisms in the framework should make the data pipelines robust. Support for various data ingestion patterns like batch, micro batch, and streaming should make the framework versatile. A framework with such capabilities will help you build scalable, reliable, and flexible data pipelines, with reduced time and effort.Reusable ETL frameworkIn a metadata-driven reusable framework, we have pre-created templates for different purposes. Metadata tables are used to configure the data pipelines.Figure 2 shows the architecture of this framework:Figure 2. Reusable ETL framework architectureIn this framework, there are pre-created AWS Glue templates for different purposes, like copying files from SFTP to landing bucket, fetching rows from a database, converting file formats in landing to parquet in the raw layer, writing to Hudi tables, copying parquet files to Redshift tables, and more.These templates are stored in a template bucket, and details of all templates are maintained in a template config table with atemplate_idinAmazon Relational Database Service(Amazon RDS). Each data pipeline (Airflow DAG) is represented as aflow_idin the main job config table. Eachflow_idcan have one or more tasks, and each task refers to atemplate_id. This framework can support both the type of ingestions—pull-based(scheduled pipelines) andpush-based(initiated pipelines). The following steps show the detailed flow of the pipeline in Figure 2.To schedule a pipeline, the “Scheduled DAG Invoker Lambda” is scheduled in EventBridge, withflow_idof the pipeline as the parameter.The source drops files in a landing bucket.An event is initiated and calls the “Triggered DAG Invoker” Lambda. This Lambda function gets the file name from the event to call the Airflow API.A Lambda function queries an RDS metadata table with the parameter to get the DAG name.Both of the Lambda functions call the Airflow API to start the DAG.The Airflow webserver locates the DAG from the S3 location and passes it to the executor.The DAG is initiated.The DAG calls the functions in the common util python script with all required parameters.For any pipeline, the util script gets all the task details from the metadata table, along with the AWS Glue template name and location.For any database or API connectivity, the util function gets the secret credentials fromAWS Secrets Managerbased on thesecret_id.The AWS Glue template file from the S3 location starts the AWS Glue job using Boto3 API by passing the required parameters. Once the AWS Glue job completes successfully, it deletes the job.If the pipeline contains any Lambda calls, the util script calls the Lambda function as per the configuration parameter.If the AWS Glue job fails due to any error in Step #11, the script captures the error message and sends anAmazon Simple Notification Service(Amazon SNS) notification.For developing any new pipeline, the developer must identify the number of tasks that need to be created for the DAG. Identify which template can be used for which task, and insert configuration entries to the metadata tables accordingly. If there is no template available, create a new template to reuse later. Finally, create the Airflow DAG script and place it in the DAG location.ConclusionThe proposed framework leverages AWS native services to provide a scalable and cost-effective solution. It allows faster development due to reusable components. You can dynamically generate and delete AWS Glue jobs as needed. This framework enables jobs tracking by configuration tables, supports error handling, and provides email notification. You can create scheduled and event-driven data pipelines to ingest data from various sources in different formats. And you can tune the performance and cost of AWS Glue jobs, by updating configuration parameters without changing any code.A reusable framework is a great practice for any development project, as it improves time to market and standardizes development patterns in a team. This framework can be used in any AWS data lake or lake house environments with any number of data layers. This makes pipeline development faster, and error handing and support easier. You can enhance and customize even further to have more features like data reconciliation, micro-batch pipelines, and more.Further reading:Land data from databases to a data lake at scale using AWS Glue blueprintsCreating a source to Lakehouse data replication pipe using Apache Hudi, AWS Glue, AWS DMS, and Amazon RedshiftTemporal data lake architecture for benchmark and indices analyticsAshutosh DubeyAshutosh is a Global Technical leader and Solutions Architect at Amazon Web Services based out of New Jersey, USA. He has extensive experience specializing in the Data, Analytics, and Machine Learning field, and has helped Fortune 500 companies in their cloud journey to AWS.Prantik GachhayatPrantik is an Enterprise Architect at Infosys with 19+ years of experience in various technology fields and business domains. He has a proven track record helping large enterprises modernize digital platforms and delivering complex transformation programs. Prantik specializes in architecting modern data and analytics platforms in AWS. Prantik loves exploring new tech trends and enjoys cooking.CommentsView Comments
Data lakes and lake house architectures have become an integral part of a data platform for any organization. However, you may face multiple challenges while developing a lake house platform and integrating with various source systems. In this blog, we will address these challenges and show how our framework can help mitigate these issues. Lake […]
Ashutosh Dubey
Amazon EventBridge Amazon Managed Workflows for Apache Airflow (Amazon MWAA) Amazon Redshift Architecture AWS Glue AWS Lambda