ETL orchestration utilizing the Amazon Redshift Knowledge API and AWS Step Features with AWS SDK integration

0/5 No votes

Report this app



Extract, rework, and cargo (ETL) serverless orchestration structure functions have gotten widespread with many purchasers. These functions provides higher extensibility and ease, making it simpler to take care of and simplify ETL pipelines. A main good thing about this structure is that we simplify an current ETL pipeline with AWS Step Features and straight name the Amazon Redshift Knowledge API from the state machine. Consequently, the complexity for the ETL pipeline is diminished.

As an information engineer or an utility developer, you might need to work together with Amazon Redshift to load or question knowledge with a easy API endpoint with out having to handle persistent connections. The Amazon Redshift Knowledge API means that you can work together with Amazon Redshift with out having to configure JDBC or ODBC connections. This function means that you can orchestrate serverless knowledge processing workflows, design event-driven internet functions, and run an ETL pipeline asynchronously to ingest and course of knowledge in Amazon Redshift, with the usage of Step Features to orchestrate the complete ETL or ELT workflow.

This submit explains tips on how to use Step Features and the Amazon Redshift Knowledge API to orchestrate the completely different steps in your ETL or ELT workflow and course of knowledge into an Amazon Redshift knowledge warehouse.

AWS Lambda is often used with Step Features resulting from its versatile and scalable compute advantages. An ETL workflow has a number of steps, and the complexity might differ inside every step. Nonetheless, there’s another strategy with AWS SDK service integrations, a function of Step Features. These integrations can help you name over 200 AWS providers’ API actions straight out of your state machine. This strategy is perfect for steps with comparatively low complexity in comparison with utilizing Lambda since you not have to take care of and take a look at operate code. Lambda capabilities have a most timeout of quarter-hour; if that you must await longer-running processes, Step Features commonplace workflows permits a most runtime of 1 yr.

You’ll be able to exchange steps that embrace a single course of with a direct integration between Step Features and AWS SDK service integrations with out utilizing Lambda. For instance, if a step is simply used to name a Lambda operate that runs a SQL assertion in Amazon Redshift, you might take away the Lambda operate with a direct integration to the Amazon Redshift Knowledge API’s SDK API motion. You too can decouple Lambda capabilities with a number of actions into a number of steps. An implementation of that is obtainable later on this submit.

We created an instance use case within the GitHub repo ETL Orchestration utilizing Amazon Redshift Knowledge API and AWS Step Features that gives an AWS CloudFormation template for setup, SQL scripts, and a state machine definition. The state machine straight reads SQL scripts saved in your Amazon Easy Storage Service (Amazon S3) bucket, runs them in your Amazon Redshift cluster, and performs an ETL workflow. We don’t use Lambda on this use case.

Answer overview

On this situation, we simplify an current ETL pipeline that makes use of Lambda to name the Knowledge API. AWS SDK service integrations with Step Features can help you straight name the Knowledge API from the state machine, decreasing the complexity in operating the ETL pipeline.

The whole workflow performs the next steps:

  1. Arrange the required database objects and generate a set of pattern knowledge to be processed.
  2. Run two dimension jobs that carry out SCD1 and SCD2 dimension load, respectively.
  3. When each jobs have run efficiently, the load job for the actual fact desk runs.
  4. The state machine performs a validation to make sure the gross sales knowledge was loaded efficiently.

The next structure diagram highlights the end-to-end answer:

We run the state machine through the Step Features console, however you possibly can run this answer in a number of methods:

You’ll be able to deploy the answer with the offered CloudFormation template, which creates the next sources:

  • Database objects within the Amazon Redshift cluster:
    • 4 saved procedures:
      • sp_setup_sales_data_pipeline() – Creates the tables and populates them with pattern knowledge
      • sp_load_dim_customer_address() – Runs the SCD1 course of on customer_address data
      • sp_load_dim_item() – Runs the SCD2 course of on merchandise data
      • sp_load_fact_sales (p_run_date date) – Processes gross sales from all shops for a given day
    • 5 Amazon Redshift tables:
      • buyer
      • customer_address
      • date_dim
      • merchandise
      • store_sales
  • The AWS Identification and Entry Administration (IAM) position StateMachineExecutionRole for Step Features to permit the next permissions:
    • Federate to the Amazon Redshift cluster via getClusterCredentials permission avoiding password credentials
    • Run queries within the Amazon Redshift cluster via Knowledge API calls
    • Record and retrieve objects from Amazon S3
  • The Step Features state machine RedshiftETLStepFunction, which accommodates the steps used to run the ETL workflow of the pattern gross sales knowledge pipeline


As a prerequisite for deploying the answer, that you must arrange an Amazon Redshift cluster and affiliate it with an IAM position. For extra data, see Authorizing Amazon Redshift to entry different AWS providers in your behalf. Should you don’t have a cluster provisioned in your AWS account, check with Getting began with Amazon Redshift for directions to set it up.

When the Amazon Redshift cluster is obtainable, carry out the next steps:

  1. Obtain and save the CloudFormation template to an area folder in your laptop.
  2. Obtain and save the next SQL scripts to an area folder in your laptop:
    1. sp_statements.sql – Accommodates the saved procedures together with DDL and DML operations.
    2. validate_sql_statement.sql – Accommodates two validation queries you possibly can run.
  3. Add the SQL scripts to your S3 bucket. The bucket title is the designated S3 bucket specified within the ETLScriptS3Path enter parameter.
  4. On the AWS CloudFormation console, select Create stack with new sources and add the template file you downloaded within the earlier step (etl-orchestration-with-stepfunctions-and-redshift-data-api.yaml).
  5. Enter the required parameters and select Subsequent.
  6. Select Subsequent till you get to the Evaluate web page and choose the acknowledgement verify field.
  7. Select Create stack.
  8. Wait till the stack deploys efficiently.

When the stack is full, you possibly can view the outputs, as proven within the following screenshot:

Run the ETL orchestration

After you deploy the CloudFormation template, navigate to the stack element web page. On the Assets tab, select the hyperlink for RedshiftETLStepFunction to be redirected to the Step Features console.

The RedshiftETLStepFunction state machine runs robotically, as outlined within the following workflow:

  1. read_sp_statement and run_sp_deploy_redshift – Performs the next actions:
    1. Retrieves the sp_statements.sql from Amazon S3 to get the saved process.
    2. Passes the saved process to the batch-execute-statement API to run within the Amazon Redshift cluster.
    3. Sends again the identifier of the SQL assertion to the state machine.
  2. wait_on_sp_deploy_redshift – Waits for a minimum of 5 seconds.
  3. run_sp_deploy_redshift_status_check – Invokes the Knowledge API’s describeStatement to get the standing of the API name.
  4. is_run_sp_deploy_complete – Routes the following step of the ETL workflow relying on its standing:
    1. FINISHED – Saved procedures are created in your Amazon Redshift cluster.
    2. FAILED – Go to the sales_data_pipeline_failure step and fail the ETL workflow.
    3. All different standing – Return to the wait_on_sp_deploy_redshift step to attend for the SQL statements to complete.
  5. setup_sales_data_pipeline – Performs the next steps:
    1. Initiates the setup saved process that was beforehand created within the Amazon Redshift cluster.
    2. Sends again the identifier of the SQL assertion to the state machine.
  6. wait_on_setup_sales_data_pipeline – Waits for a minimum of 5 seconds.
  7. setup_sales_data_pipeline_status_check – Invokes the Knowledge API’s describeStatement to get the standing of the API name.
  8. is_setup_sales_data_pipeline_complete – Routes the following step of the ETL workflow relying on its standing:
    1. FINISHED – Created two dimension tables (customer_address and merchandise) and one truth desk (gross sales).
    2. FAILED – Go to the sales_data_pipeline_failure step and fail the ETL workflow.
    3. All different standing – Return to the wait_on_setup_sales_data_pipeline step to attend for the SQL statements to complete.
  9. run_sales_data_pipeline LoadItemTable and LoadCustomerAddressTable are two parallel workflows that Step Features runs on the identical time. The workflows run the saved procedures that have been beforehand created. The saved process masses the information into the merchandise and customer_address tables. All different steps within the parallel classes observe the identical idea as described beforehand. When each parallel workflows are full, run_load_fact_sales runs.
  10. run_load_fact_sales – Inserts knowledge into the store_sales desk that was created within the preliminary saved process.
  11. Validation – When all of the ETL steps are full, the state machine reads a second SQL file from Amazon S3 (validate_sql_statement.sql) and runs the 2 SQL statements utilizing the batch_execute_statement technique.

The implementation of the ETL workflow is idempotent. If it fails, you possibly can retry the job with none cleanup. For instance, it recreates the stg_store_sales desk every time, then deletes the goal desk store_sales with the information for the actual refresh date every time.

The next diagram illustrates the state machine workflow:

On this instance, we use the duty state useful resource arn:aws:states:::aws-sdk:redshiftdata:[apiAction] to name the corresponding Knowledge API motion. The next desk summarizes the Knowledge API actions and their corresponding AWS SDK integration API actions.

To make use of AWS SDK integrations, you specify the service title and API name, and, optionally, a service integration sample. The AWS SDK motion is all the time camel case, and parameter names are Pascal case. For instance, you should utilize the Step Features motion batchExecuteStatement to run a number of SQL statements in a batch as part of a single transaction on the Knowledge API. The SQL statements may be SELECT, DML, DDL, COPY, and UNLOAD.

Validate the ETL orchestration

The whole ETL workflow takes roughly 1 minute to run. The next screenshot reveals that the ETL workflow accomplished efficiently.

When the complete gross sales knowledge pipeline is full, you might undergo the complete execution occasion historical past, as proven within the following screenshot.

Schedule the ETL orchestration

After you validate the gross sales knowledge pipeline, you might choose to run the information pipeline on a each day schedule. You’ll be able to accomplish this with Amazon EventBridge.

  1. On the EventBridge console, create a rule to run the RedshiftETLStepFunction state machine each day.
  2. To invoke the RedshiftETLStepFunction state machine on a schedule, select Schedule and outline the suitable frequency wanted to run the gross sales knowledge pipeline.
  3. Specify the goal state machine as RedshiftETLStepFunction and select Create.

You’ll be able to affirm the schedule on the rule particulars web page.

Clear up

Clear up the sources created by the CloudFormation template to keep away from pointless value to your AWS account. You’ll be able to delete the CloudFormation stack by deciding on the stack on the AWS CloudFormation console and selecting Delete. This motion deletes all of the sources it provisioned. Should you manually up to date a template-provisioned useful resource, you may even see some points throughout cleanup; that you must clear these up independently.


The Knowledge API and Step Features AWS SDK integration provides a sturdy mechanism to construct extremely distributed ETL functions inside minimal developer overhead. Contemplate the next limitations when utilizing the Knowledge API and Step Features:


On this submit, we demonstrated tips on how to construct an ETL orchestration utilizing the Amazon Redshift Knowledge API and Step Features with AWS SDK integration.

To study extra in regards to the Knowledge API, see Utilizing the Amazon Redshift Knowledge API to work together with Amazon Redshift clusters and Utilizing the Amazon Redshift Knowledge API.

In regards to the Authors

Jason Pedreza is an Analytics Specialist Options Architect at AWS with over 13 years of information warehousing expertise. Previous to AWS, he constructed knowledge warehouse options at He makes a speciality of Amazon Redshift and helps prospects construct scalable analytic options.

Bipin Pandey is a Knowledge Architect at AWS. He likes to construct knowledge lake and analytics platforms for his prospects. He’s obsessed with automating and simplifying buyer issues with the usage of cloud options.

David Zhang is an AWS Options Architect who helps prospects design strong, scalable, and data-driven options throughout a number of industries. With a background in software program improvement, David is an energetic chief and contributor to AWS open-source initiatives. He’s obsessed with fixing real-world enterprise issues and constantly strives to work from the shopper’s perspective. Be happy to attach with him on LinkedIn.


Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.