---
title: How to Setup a Simple ETL Pipeline with AWS Lambda for Data Science
description: How to setup a simple ETL pipeline with AWS Lambda that can be triggered via API Endpoint or Schedule and write the results to an S3 Bucket for ingestion.
image: https://dataknowsall.com/hubfs/lambdaetl_deploy.png
---

[Skip to content](https://dataknowsall.com/blog/lambdaetl.html#main-content)

[![Lambda with Text - Wide](https://dataknowsall.com/hs-fs/hubfs/Lambda%20with%20Text%20-%20Wide.png?width=3068&height=301&name=Lambda%20with%20Text%20-%20Wide.png)](https://dataknowsall.com)

- [Home](https://dataknowsall.com)
- [Blog](https://dataknowsall.com/blog/)
- [Services](https://dataknowsall.com/services)
- [Technologies](https://dataknowsall.com/technologies)

Open main navigation

Close main navigation

- [Home](https://dataknowsall.com)
- [Blog](https://dataknowsall.com/blog/)
- [Services](https://dataknowsall.com/services)
- [Technologies](https://dataknowsall.com/technologies)
- [Contact us](https://dataknowsall.com/contact)

[Contact us](https://dataknowsall.com/contact)

[All posts](https://dataknowsall.com/blog/all)

 March 5, 2024

# How to Setup a Simple ETL Pipeline with AWS Lambda for Data Science

![Picture of Brian Roepke](https://app.hubspot.com/settings/avatar/67adab425dfba5d6fa7e242d82fbe2c4) By  [Brian Roepke](https://dataknowsall.com/blog/author/brian-roepke)  ·   8 minute read

## Introduction to ETL with AWS Lambda

When it comes time to build an ETL pipeline, many options exist. You can use a tool like [Astronomer](https://dataknowsall.com/blog/astrointro.html) or [Prefect](https://dataknowsall.com/blog/prefectintro.html) for Orchestration, but you will also need somewhere to run the compute. With this, you have a few options:

- Virtual Machine (VM) like AWS EC2
- Container services like AWS ECS or AWS Fargate
- Apache Spark like AWS EMR (Elastic Map Reduce)
- Serverless Computing like AWS Lambda

Each of these has its advantages. If you're looking for simplicity in setup, maintenance, and cost, you can run *simple* jobs with **AWS Lambdas** or Serverless Computing.

Notice I said **simple**. AWS Lambdas are not meant for compute-intensive or long-running jobs. They're suitable for executing small amounts of code that take minutes versus hours.

## What are AWS Lambda and Serverless Computing?

A Lambda function in AWS is a piece of code that is executed in response to an event. The event can be a request to an API endpoint, a file being uploaded to an S3 bucket, or a scheduled event. The code is executed, and the results are returned. Here is a great description of how it works from [AWS](https://docs.aws.amazon.com/lambda/latest/dg/welcome.html):

> Lambda runs your function only when needed and scales automatically, from a few daily requests to thousands per second. You pay only for the computing time you consume—there is no charge when your code is not running. For more information, see AWS Lambda Pricing.

A Lambda function is a wonderful way to think about ETL for smaller jobs that need to run frequently. Such as on a trigger, like an API call, or nightly on a schedule. It also allows you to orchestrate multiple Lambda functions to create a more complex ETL pipeline.

Let's dive into creating our first Lambda function.

## Creating Your Lamba Function

From the AWS Console, navigate to the Lambda service. Press the **Create Function**button to get started. You will be prompted to select a blueprint. For this example, we will select **Author from scratch**. Give your name an appropriate name and select **Python 3.9**as the runtime. Select the **architecture** you prefer or typically develop locally; this makes it easier to upload new libraries compatible with your Lambda function.

![lambdaetl\_create\_function](https://dataknowsall.com/hs-fs/hubfs/lambdaetl_create_function.png?width=800&height=591&name=lambdaetl_create_function.png)

You can create a **new role** or choose an **existing** one. We'll cover that in the next section.

### Lamba Role

A critical part of creating a Lambda function is the **role**. The **role** allows the function to access other AWS services. For this example, we will need to give the function access to the **Lambda** and **S3**. I also gave access to **VPC**, but that's not necessary for this setup.

It's a good practice to create a new role to help you isolate only the permissions needed for this function. Or, if you will create multiple lambda functions for ETL use cases, consider a more generic naming like **Lamba-ETL-Role**.

![lambdaetl\_role](https://dataknowsall.com/hs-fs/hubfs/lambdaetl_role.png?width=800&height=504&name=lambdaetl_role.png)

### Set Your Function's Timeout

Next is configuring the function's timeout. Depending on how long the function will take to execute, you can increase it to 15 minutes. For this example, we will set it to **1 minute**. You could see in your CloudWatch logs if the timeout was reached.

Click on the **Configuration** tab, and under **General Configuration**, set the timeout to 1 minute.

![lambdaetl\_timeout](https://dataknowsall.com/hs-fs/hubfs/lambdaetl_timeout.png?width=800&height=381&name=lambdaetl_timeout.png)

## Using the Parameters and Secrets Extension

Next - this isn't 100% necessary, but it's a great practice when you want to ensure that you're handling sensitive data safely and not exposing it in your code. In the past, I've written about how to use [Environment Variables](https://dataknowsall.com/blog/envvar.html) to do this locally; however, in AWS, we'll use the **Parameters and Secrets Extension**.

The **Parameters and Secrets Extension** allows you to store sensitive data in the AWS Secrets Manager and access it in your Lambda function, a great way to store API keys, database credentials, etc. You can also use it to store non-sensitive data like configuration settings. You can read up more on this functionality here: [Configure the Secrets Manager](https://docs.aws.amazon.com/secretsmanager/latest/userguide/retrieving-secrets_lambda.html)

We'll start by adding a Layer to our Lambda function, allowing us to access the extension. From the **Code** tab, scroll to the bottom and click on **Add a Layer**. Select **AWS Layers**and then choose **AWS-Parameters-and-Secrets-Lambda-Extension-Arm64** and the latest version of that layer.

![lambdaetl\_secrets](https://dataknowsall.com/hs-fs/hubfs/lambdaetl_secrets.png?width=800&height=418&name=lambdaetl_secrets.png)

Next, we need to add code to help us access the secrets. I've added this to a small function that will look up my **API Key** for [The Movie Database](https://www.themoviedb.org/) (TMDB) and return it. You can see the full code below. TMDB is a great API for getting movie and TV show information, and it's free to use for non-commercial purposes.

As shown below, we'll create a `headers` variable with the JSON object. We'll then pass that into our API call to the **secrets\_extension\_endpoint** as shown. The response will be a JSON object with the secret string. We'll then parse that string and return the API key.

Where my code says **\<\< your secrets ARN \>\>**, you'll need to replace that with the **ARN** of your secret. You can find that in the **AWS Secrets Manager console**.

**Note:** *You will need to go back and modify your role to allow access to the secret you created. You can find instructions on how to do that in the [AWS Documentation](https://docs.aws.amazon.com/secretsmanager/latest/userguide/auth-and-access_examples.html). Follow the example in the section "Example Read one secret (attach to an identity)".*

```
import requestsimport jsondef get_tmdb_api_key(): headers = {"X-Aws-Parameters-Secrets-Token": os.environ.get("AWS_SESSION_TOKEN")} secrets_extension_endpoint = ( "http://localhost:" + "2773" + "/secretsmanager/get?secretId=" + "<< your secrets arn >>" ) r = requests.get(secrets_extension_endpoint, headers=headers) secret = json.loads(r.text)["SecretString"] secret = json.loads(secret) TMDB_API_KEY = secret["TMDB_API"] return TMDB_API_KEY
```

## Add Support for Pandas

Next, our example and many other ETL use cases will require using Pandas. We'll need to add a layer to our Lambda function to support this. From the **Code** tab, scroll to the bottom and click on **Add a Layer**. Select **Custom Layers** and then choose **AWSSDKPandas-Python39-Arm64** and the latest layer version.

After enabling this layer, you can `import pandas` in your code *without* having to upload the package to your lambda function.

![lambdaetl\_pandas](https://dataknowsall.com/hs-fs/hubfs/lambdaetl_pandas.png?width=800&height=416&name=lambdaetl_pandas.png)

## Writing Files to an S3 Bucket

One very powerful workflow with modern Data Warehouses / Platforms is the ability to work directly with JSON data. We'll take advantage of this by outputting data to **JSON files**in an **S3 bucket**.

We'll create a function that will take a **DataFrame** and write it to a **JSON file** in an S3 bucket. We'll use the `boto3` library to do this. We'll create a JSON string using Pandas `to_json` method, encode it as `utf-8`, and then write it to the S3 bucket.

```
def write_to_s3(df, type, imdb_id):  # Get JSON for the DataFrame output = json.loads(df.to_json(orient='records')) string = str(output) encoded_string = string.encode("utf-8") bucket_name = "lambda-tmdb" file_name = "out.json" s3_path = "output/" + type + "/" + imdb_id + "-" + type + "-" + file_name s3 = boto3.resource('s3') object = s3.Object(bucket_name, s3_path) object.put(Body=encoded_string) return "Success"
```

We call this function when we're ready to output our DataFrame to S3 with a few parameters such as the **DataFrame** itself, the **type** of data we're writing, and the **IMDB ID** of the movie or TV show. The type is here for convenience so we can write different data types with a single function while creating a unique file name for each movie lookup.

```
write_to_s3(df_crew, "crew", imdb_id)
```

## Deploying Your Lambda Function

There are multiple ways to deploy your function, and the easiest is to use the AWS Console and upload a zip file. However, adding a little bit of automation here will simplify your life when making changes to your code.

The first step you'll need to do is set up the AWS CLI. You can find instructions on how to do that in the AWS Documentation [Getting started with the AWS CLI](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-started.html). You'll want to **create a new IAM user** with the appropriate permissions to write to the Lambda service.

Next, I wrote a simple `bash` script that I can call from my local terminal, which does all the work needed. One very important step is to zip up your python libraries at the correct level and package them with the zip file. The script shows you the process of changing to the `sites-packages` directory and zipping the contents of the libraries you want to keep. Explicitly name the libraries you want here so you don't end up uploading unnecessary files.

**Note:** *This also assumes that I've created a local virtual environment and have my function code local. I prefer to build my Lambdas this way so that I can version control everything in GitHub*

```
# delete the old ziprm tmdb-deployment-package.zip# change the directory to the site-packages directory and zip the contentscd venv/lib/python3.9/site-packages# Explicitly add the tmdbsimple package folders needed for the lambda functionzip -r ../../../../tmdb-deployment-package.zip tmdbsimple tmdbsimple-2.9.1.dist-info# change back to the root directory and add the needed python filescd ../../../../zip -g tmdb-deployment-package.zip lambda_function.py# deploy to AWS Lambdaaws lambda update-function-code \     --function-name lambda-tmdb \     --zip-file fileb://tmdb-deployment-package.zip
```

After your function successfully deploys, you can head back to the console and see your code and the supplied packages. Notice the structure. The packages are in folders at the root level, allowing them to be imported like any other package as if you were developing locally.

![lambdaetl\_deploy](https://dataknowsall.com/hs-fs/hubfs/lambdaetl_deploy.png?width=800&height=359&name=lambdaetl_deploy.png)

## Trigger the Lambda Function with an API Endpoint

As the final step to our function, we will trigger it with an API Endpoint using the AWS API Gateway and pass the function parameters as a query string, an ultra-powerful way to trigger specific events to fire for the exact data you want to process.

Navigate to the **Configuration** tab and click the **Add Trigger** button. Scroll to API Gateway and select the option for **Create New API**. Additionally, select the option for **HTTP API** and set the security to **Open**.

![lambdaetl\_create\_api](https://dataknowsall.com/hs-fs/hubfs/lambdaetl_create_api.png?width=800&height=557&name=lambdaetl_create_api.png)

After the endpoint is created, you will have a URL with which you can trigger your Lambda. We'll pass a list of IDs as a query string parameter allowing us to pass multiple IDs to the function and process them all at once. Triggering your API with query string parameters is a very powerful way to process data in bulk.

We construct the URL as follows:

```
API Gateway URL + ?ids=tt0162346&ids=tt0326900
```

And now, in our function, we can access the list of query string parameters via the event object that is part of all Lambda functions. We'll access it through the `multiValueQueryStringParameters` key. And then, using the name of the query string parameter, we want to access, in this case, `ids`.

```
params = event["multiValueQueryStringParameters"]id_list = params['ids']
```

## Monitoring the Lambda Function with CloudWatch When Using an API Gateway

By default, you will get CloudWatch monitoring for your function. I've run into a few errors where I needed extended logging. You must add the following to your **Log Format** for the API Gateway Stage.

```
$context.integrationErrorMessage
```

Read the full instructions: [Troubleshooting issues with HTTP API Lambda integrations](https://docs.aws.amazon.com/apigateway/latest/developerguide/http-api-troubleshooting-lambda.html).

![lambdaetl\_api\_logging](https://dataknowsall.com/hs-fs/hubfs/lambdaetl_api_logging.png?width=800&height=219&name=lambdaetl_api_logging.png)

## Function Code

We will put this all together for the finale into our `lambda_handler` function. We retrieve the IDs from the Query string, then call our function, `get_tmdb_api_key,` to get the API key from the Secrets Store. Finally, we loop through our IDs and build a data frame, writing it to S3 with the function `write_to_s3`.

```
def lambda_handler(event, context):    # Get the IDs from the Query String    params = event["multiValueQueryStringParameters"]    id_list = params['ids']    # Get credentials from the Secrets Manager    KEY = get_tmdb_api_key()    # Loop through the IDs passed and do something with them    for i in range(len(id_list)):        imdb_id = id_list[i]        # Get the movie details        # CODE NOT SHOWN        # Save the movie details as a dataframe        # write it to S3        write_to_s3(df_crew, "crew", imdb_id)    # return a success message in the proper format for a Lambda function    return {        "statusCode": 200,        "headers": {"Content-Type": "application/json"},        "body": "Success",    }
```

## Orchestrating and Processing the Data in your Warehouse

The final step is to trigger the API Endpoint in a workflow and then process the data in your warehouse. An example of how JSON works with snowflake can be found in my article [Getting Started with Snowflake and the Rise of ELT Workflows in the Cloud](https://dataknowsall.com/blog/snowflakestart.html).

For orchestration, check out [Getting Started with Prefect: Powerful Orchestration for Your Data](https://dataknowsall.com/blog/prefectintro.html) for the full details on how to orchestrate this particular workflow.

You can find the full code for this project on [GitHub](https://github.com/broepke/LambdaTMDB)

## Conclusion

When building ETL pipelines, you have many options to choose from when it comes to computing. For simple jobs, an AWS Lambda function can be a wonderful way to enrich your data or process quickly and efficiently. We started by showing you how to create a Lambda, including setting up the role to run it. Then we covered several recommendations, such as leveraging the Parameters and Secrets Extension to safely store information such as API keys. Then we look at how you can automate deployment using the AWS CLI. Finally, we triggered the function with an API Gateway and monitored it with CloudWatch. I hope this article shows how wonderful Lambdas can be and how you can add Lambda to your ETL toolbelt.

Share: [facebook-f icon](http://www.facebook.com/share.php?u=https://dataknowsall.com/blog/lambdaetl.html) [linkedin-in icon](http://www.linkedin.com/shareArticle?mini=true&url=https://dataknowsall.com/blog/lambdaetl.html) [twitter icon](https://twitter.com/intent/tweet?url=https://dataknowsall.com/blog/lambdaetl.html) [pinterest-p icon](http://pinterest.com/pin/create/link/?url=https://dataknowsall.com/blog/lambdaetl.html) [envelope icon](mailto:?body=https://dataknowsall.com/blog/lambdaetl.html)

## Related posts

[![Prefect Flow](https://dataknowsall.com/hubfs/prefect_flowrun.png)](https://dataknowsall.com/blog/prefectintro.html)

[Data Engineering](https://dataknowsall.com/blog/tag/data-engineering)

### [Getting Started with Prefect: Powerful Orchestration for Your Data](https://dataknowsall.com/blog/prefectintro.html)

[![](https://dataknowsall.com/hubfs/DynamoDB%20API.png)](https://dataknowsall.com/blog/dynamodbapi.html)

[Data Engineering](https://dataknowsall.com/blog/tag/data-engineering)

### [Build Your First API with Python and AWS!](https://dataknowsall.com/blog/dynamodbapi.html)

[![Fivetran Connectors](https://dataknowsall.com/hubfs/normalize_fivetran.png)](https://dataknowsall.com/blog/normalizemongo.html)

[Data Engineering](https://dataknowsall.com/blog/tag/data-engineering)

### [How to Normalize MongoDB Data in Snowflake for Data Science Workflows](https://dataknowsall.com/blog/normalizemongo.html)

[![Lambda Google Workspace](https://dataknowsall.com/hs-fs/hubfs/Lambda%20Google%20Workspace.png?width=320&height=132&name=Lambda%20Google%20Workspace.png "Lambda Google Workspace")](https://www.dataknowsall.com)

Specialists in Data Engineering, Machine Learning, and Analytics Consultation

[linkedin-in icon](https://www.linkedin.com/in/broepke/) [twitter icon](https://twitter.com/broepke) [Follow us on Facebook](https://github.com/broepke)

**Pages**

- [Home](https://dataknowsall.com)
- [Blog](https://dataknowsall.com/blog/)
- [Services](https://dataknowsall.com/services)
- [Technologies](https://dataknowsall.com/technologies)

### Get Updates In Your Inbox

Copyright © 2026, Data Knows All

```json
{
  "@context" : "https://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Brian Roepke",
    "url" : "https://dataknowsall.com/blog/author/brian-roepke"
  },
  "dateModified" : "2024-03-05T13:23:13.845Z",
  "datePublished" : "2023-02-07T11:09:00.000Z",
  "headline" : "How to Setup a Simple ETL Pipeline with AWS Lambda for Data Science",
  "image" : [ "https://dataknowsall.com/hubfs/lambdaetl_deploy.png" ],
  "mainEntityOfPage" : {
    "@id" : "https://dataknowsall.com/blog/lambdaetl.html",
    "@type" : "WebPage"
  },
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "url" : "https://dataknowsall.com/hubfs/Lambda%20Google%20Workspace.png"
    },
    "name" : "Data Knows All, LLC"
  }
}
```

```json
{
  "@context" : "http://schema.org",
  "@type" : "Article",
  "author" : {
    "@type" : "Person",
    "name" : [ "Brian Roepke" ],
    "url" : "https://dataknowsall.com/blog/author/brian-roepke"
  },
  "datePublished" : "2023-02-07T11:09:00+0000",
  "description" : "How to setup a simple ETL pipeline with AWS Lambda that can be triggered via API Endpoint or Schedule and write the results to an S3 Bucket for ingestion.",
  "headline" : "How to Setup a Simple ETL Pipeline with AWS Lambda for Data Science",
  "image" : "https://44115987.fs1.hubspotusercontent-na1.net/hubfs/44115987/lambdaetl_deploy.png",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "url" : "https://44115987.fs1.hubspotusercontent-na1.net/hubfs/44115987/Lambda%20Google%20Workspace.png"
    },
    "name" : "Data Knows All"
  },
  "url" : "https://dataknowsall.com/blog/lambdaetl.html"
}
```