All posts by Ashutosh Dubey

Use a reusable ETL framework in your AWS lake house architecture

Post Syndicated from Ashutosh Dubey original

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 house architecture using AWS

Figure 1 shows a typical lake house implementation in an Amazon Web Services (AWS) environment.

Typical lake house implementation in AWS

Figure 1. Typical lake house implementation in AWS

In this diagram we have five layers. The number of layers and names can vary per environmental requirements, so check recommended data layers for more details.

  1. Landing layer. This is where all source files are dropped in their original format.
  2. Raw layer. This is where all source files are converted and stored in a common parquet format.
  3. Stage layer. This is where we maintain a history of dimensional tables as Slowly Changing Dimension Type 2 (SCD2). Apache Hudi is used for SCD2 in the Amazon Simple Storage Service (Amazon S3) bucket, and an AWS Glue job 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, see using the Hudi framework in AWS Glue.
  4. Presentation layer. This is where data is being cleansed, validated, and transformed, using an AWS Glue job, in accordance with business requirements.
  5. Data warehouse layer. Amazon Redshift is 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, push and pull. In the pull-based ingestion, services like AWS Glue or AWS Lambda are used to pull data from sources like databases, APIs, or flat files into the data lake. In the push-based pattern, 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 EventBridge is used to schedule the Airflow directed acyclic graph (DAG) data pipelines. Amazon RDS for PostgreSQL is 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 challenges

Maintaining 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 framework

Having 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 framework

In 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:

Reusable ETL framework architecture

Figure 2. Reusable ETL framework architecture

In 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 a template_id in Amazon Relational Database Service (Amazon RDS). Each data pipeline (Airflow DAG) is represented as a flow_id in the main job config table. Each flow_id can have one or more tasks, and each task refers to a template_id. This framework can support both the type of ingestions—pull-based (scheduled pipelines) and push-based (initiated pipelines). The following steps show the detailed flow of the pipeline in Figure 2.

  1. To schedule a pipeline, the “Scheduled DAG Invoker Lambda” is scheduled in EventBridge, with flow_id of the pipeline as the parameter.
  2. The source drops files in a landing bucket.
  3. 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.
  4. A Lambda function queries an RDS metadata table with the parameter to get the DAG name.
  5. Both of the Lambda functions call the Airflow API to start the DAG.
  6. The Airflow webserver locates the DAG from the S3 location and passes it to the executor.
  7. The DAG is initiated.
  8. The DAG calls the functions in the common util python script with all required parameters.
  9. For any pipeline, the util script gets all the task details from the metadata table, along with the AWS Glue template name and location.
  10. For any database or API connectivity, the util function gets the secret credentials from AWS Secrets Manager based on the secret_id.
  11. 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.
  12. If the pipeline contains any Lambda calls, the util script calls the Lambda function as per the configuration parameter.
  13. If the AWS Glue job fails due to any error in Step #11, the script captures the error message and sends an Amazon 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.


The 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:

Email delta cost usage report in a multi-account organization using AWS Lambda

Post Syndicated from Ashutosh Dubey original

Overview of solution

AWS Organizations gives customers the ability to consolidate their billing across accounts. This reduces billing complexity and centralizes cost reporting to a single account. These reports and cost information are available only to users with billing access to the primary AWS account.

In many cases, there are members of senior leadership or finance decision makers who don’t have access to AWS accounts, and therefore depend on individuals or additional custom processes to share billing information. This task becomes specifically complicated when there is a complex account organization structure in place.

In such cases, you can email cost reports periodically and automatically to these groups or individuals using AWS Lambda. In this blog post, you’ll learn how to send automated emails for AWS billing usage and consumption drifts from previous days.

Solution architecture

Account structure and architecture diagram

Figure 1. Account structure and architecture diagram

AWS provides the Cost Explorer API to enable you to programmatically query data for cost and usage of AWS services. This solution uses a Lambda function to query aggregated data from the API, format that data and send it to a defined list of recipients.

  1. Amazon EventBridge (Amazon CloudWatch Events) is configured to cue the Lambda function at a specific time.
  2. The function uses the AWS Cost Explorer API to fetch the cost details for each account.
  3. The Lambda function calculates the change in cost over time and formats the information to be sent in an email.
  4. The formatted information is passed to Amazon Simple Email Service (Amazon SES).
  5. The report is emailed to the recipients configured in the environment variables of the function.


For this walkthrough, you should have the following prerequisites:


  • Download the AWS CloudFormation template from this link: AWS CloudFormation template
  • Once downloaded, open the template in your favorite text editor
  • Update account-specific variables in the template. You need to update the tuple, dictionary, display list, and display list monthly sections of the script for all the accounts which you want to appear in the daily report email. Refer to Figure 2 for an example of some dummy account IDs and email IDs.
A screenshot showing account IDs in AWS Lambda

Figure 2. Account IDs in AWS Lambda

  • Optionally, locate “def send_report_email” in the template. The subject variable controls the subject line of the email. This can be modified to something meaningful to the recipients.

After these changes are made according to your requirements, you can deploy the CloudFormation template:

  1. Log in to the Cloud Formation console.
  2. Choose Create Stack. From the dropdown, choose With new resources (standard).
  3. On the next screen under Specify Template, choose Upload a template file.
  4. Click Choose file. Choose the local template you modified earlier, then choose Next.
  5. Fill out the parameter fields with valid email address. For SchduleExpression, use a valid Cron expression for when you would like the report sent. Choose Next.
    Here is an example for a cron schedule:  18 11 * * ? *
    (This example cron expression sets the schedule to send every day at 11:18 UTC time.)
    This creates the Lambda function and needed AWS Identity and Access Management (AWS IAM) roles.

You will now need to make a few modifications to the created resources.

  1. Log in to the IAM console.
  2. Choose Roles.
  3. Locate the role created by the CloudFormation template called “daily-services-usage-lambdarole
  4. Under the Permissions tab, choose Add Permissions. From the dropdown., choose Attach Policy.
  5. In the search bar, search for “Billing”.
  6. Select the check box next to the AWS Managed Billing Policy and then choose Attach Policy.
  7. Log in to the AWS Lambda console.
  8. Choose the DailyServicesUsage function.
  9. Choose the Configuration tab.
  10. In the options that appear, choose General Configuration.
  11. Choose the Edit button.
  12. Change the timeout option to 10 seconds, because the default of three seconds may not be enough time to run the function to retrieve the cost details from multiple accounts.
  13. Choose Save.
  14. Still under the General Configuration tab, choose the Permissions option and validate the execution role.
    The edited IAM execution role should display the Resource details for which the access has been gained. Figure 3 shows that the allow actions to aws-portal for Billing, Usage, PaymentMethods, and ViewBilling are enabled. If the Resource summary does not show these permissions, the IAM role is likely not correct. Go back to the IAM console and confirm that you updated the correct role with billing access.
A screenshot of the AWS Lambda console showing Lambda role permissions

Figure 3. Lambda role permissions

  • Optionally, in the left navigation pane, choose Environment variables. Here you will see the email recipients you configured in the Cloud Formation template. If changes are needed to the list in the future, you can add or remove recipients by editing the environment variables. You can skip this step if you’re satisfied with the parameters you specified earlier.

Next, you will create a few Amazon SES identities for the email addresses that were provided as environment variables for the sender and recipients:

  1. Log in to the SES console.
  2. Under Configuration, choose Verified Identities.
  3. Choose Create Identity.
  4. Choose the identity type Email Address, fill out the Email address field with the sender email, and choose Create Identify.
  5. Repeat this step for all receiver emails.

The email IDs included will receive an email for the confirmation. Once confirmed, the status shows as verified in the Verified Identities tab of the SES console. The verified email IDs will start receiving the email with the cost reports.

Amazon EventBridge (CloudWatch) event configuration

To configure events:

    1. Go to the Amazon EventBridge console.
    2. Choose Create rule.
    3. Fill out the rule details with meaningful descriptions.
    4. Under Rule Type, choose Schedule.
    5. Schedule the cron pattern from when you would like the report to run.

Figure 4 shows that the highlighted rule is configured to run the Lambda function every 24 hours.

A screenshot of the Amazon EventBridge console showing an EventBridge rule

Figure 4. EventBridge rule

An example AWS Daily Cost Report email

From[email protected] (the email ID mentioned as “sender”)
Sent: Tuesday, April 12, 2022 1:43 PM
To[email protected] (the email ID mentioned as “receiver”)
Subject: AWS Daily Cost Report for Selected Accounts (the subject of email as set in the Lambda function)

Figure 5 shows the first part of the cost report. It provides the cost summary and delta of the cost variance percentage compare to the previous day. You can also see the trend based on the last seven days from the same table. This helps in understanding a pattern around cost and usage.

This summary is broken down per account, and then totaled, in order to help you understand the accounts contributing to the cost changes. The daily change percentages are also color coded to highlight significant variations.

AWS Daily Cost Report email body part 1

Figure 5. AWS Daily Cost Report email body part 1

The second part of the report in the email provides the service-related cost breakup for each account configured in the Account dictionary section of the function. This is a further drilldown report; you will get these for all configured accounts.

AWS Daily Cost Report email body part 2

Figure 6. AWS Daily Cost Report email body part 2


  • Delete the Amazon CloudFormation stack.
  • Delete the identities on Amazon SES.
  • Delete the Amazon EventBridge (CloudWatch) event rule.


The blog demonstrates how you can automatically and seamlessly share your AWS accounts’ billing and change information with your leadership and finance teams daily (or on any schedule you choose). While the solution was designed for accounts that are part of an organization in the service AWS organizations, it could also be deployed in a standalone account without making any changes. This allows information sharing without the need to provide account access to the recipients, and avoids any dependency on other manual processes. As a next step, you can also store these reports in Amazon Simple Storage Service (Amazon S3), generate a historical trend summary for consumption, and continue making informed decisions.

Additional reading