AWSTemplateFormatVersion: 2010-09-09 Description: "CloudFormation Template to deploy an ETL orchestration pipeline by using Redshift Data Api, Step Function, AWS Lambda and EventBridge" Parameters: RedshiftClusterEndpoint: Description: Redshift cluster endpoint including port number and database name Type: String Default: redshift-cluster.xxxxxx.region.redshift.amazonaws.com:5439/dev DbUsername: Description: Redshift database user name which has access to run SQL Script. Type: String AllowedPattern: "([a-z])([a-z]|[0-9])*" Default: 'awsuser' Metadata: AWS::CloudFormation::Interface: ParameterGroups: - Label: default: Input Parameters Parameters: - RedshiftClusterEndpoint - DbUsername Resources: StateMachineExecutionRole: Type: 'AWS::IAM::Role' Properties: Description : IAM Role for the state machine in step function to run AssumeRolePolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Principal: Service: - !Sub 'states.${AWS::Region}.amazonaws.com' Action: 'sts:AssumeRole' Path: / Policies: - PolicyName: RedshiftBatchDataApiPolicy PolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Action: - 'redshift-data:BatchExecuteStatement' - 'redshift-data:ListStatements' - 'redshift-data:GetStatementResult' - 'redshift-data:DescribeStatement' - 'redshift-data:ExecuteStatement' Resource: '*' - Effect: Allow Action: - 'redshift:GetClusterCredentials' Resource: - !Sub - arn:aws:redshift:${AWS::Region}:${AWS::AccountId}:cluster:${SourceRedshiftClusterIdentifier} - {SourceRedshiftClusterIdentifier: !Select [0, !Split [".", !Ref RedshiftClusterEndpoint]]} - !Sub - arn:aws:redshift:${AWS::Region}:${AWS::AccountId}:dbname:${SourceRedshiftClusterIdentifier}/${RedshiftDatabaseName} - {SourceRedshiftClusterIdentifier: !Select [0, !Split [".", !Ref RedshiftClusterEndpoint]],RedshiftDatabaseName: !Select [1, !Split ["/", !Ref RedshiftClusterEndpoint]]} - !Sub - arn:aws:redshift:${AWS::Region}:${AWS::AccountId}:dbuser:${SourceRedshiftClusterIdentifier}/${DbUsername} - {SourceRedshiftClusterIdentifier: !Select [0, !Split [".", !Ref RedshiftClusterEndpoint]]} RedshiftETLStepFunction: Type: 'AWS::StepFunctions::StateMachine' Properties: StateMachineName: Redshift-load-order-to-cash DefinitionString: !Sub - |- { "Comment": "ETL workflow for loading order to cash tables", "StartAt": "run_master_data_tables", "States": { "run_master_data_tables": { "Comment": "Load 2 dimension tables", "Type": "Parallel", "ResultPath": "$.sql_output", "Branches": [ { "StartAt": "Load_Customer_Master", "States": { "Load_Customer_Master": { "Comment": "Load Item Table", "Type": "Task", "Resource": "arn:aws:states:::aws-sdk:redshiftdata:executeStatement", "ResultPath": "$.sql_output", "Parameters": { "ClusterIdentifier": "${RedshiftClusterIdentifier}", "Database": "${RedshiftDbName}", "Sql": "call public.incremental_sync_customer_attr()", "DbUser":"${RedshiftDbUser}" }, "Next": "wait_on_customer_master_table", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "IntervalSeconds": 60, "MaxAttempts": 10, "BackoffRate": 2 } ] }, "wait_on_customer_master_table": { "Comment": "Wait before status check", "Type": "Wait", "Seconds": 15, "Next": "load_customer_master_table_status_check" }, "load_customer_master_table_status_check": { "Comment": "Check Task Status", "Type": "Task", "Resource": "arn:aws:states:::aws-sdk:redshiftdata:describeStatement", "ResultPath": "$.sql_output", "Parameters": { "Id.$": "$.sql_output.Id" }, "Next": "is_load_customer_master_table_complete", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 3, "MaxAttempts": 2 } ] }, "is_load_customer_master_table_complete": { "Comment": "check if load_item_table_complete step is complete", "Type": "Choice", "Choices": [ { "Variable": "$.sql_output.Status", "StringEquals": "FAILED", "Next": "load_customer_master_table_failed" }, { "Variable": "$.sql_output.Status", "StringEquals": "FINISHED", "Next": "load_customer_master_table_succeeded" } ], "Default": "wait_on_customer_master_table" }, "load_customer_master_table_succeeded": { "Type": "Pass", "Result": "load_fact_sales_pipeline_success", "End": true }, "load_customer_master_table_failed": { "Type": "Fail", "Cause": "customer_master table load failed", "Error": "Error" } } }, { "StartAt": "Load_Material_Master_Table", "States": { "Load_Material_Master_Table": { "Comment": "Load Customer Address Table", "Type": "Task", "Resource": "arn:aws:states:::aws-sdk:redshiftdata:executeStatement", "ResultPath": "$.sql_output", "Parameters": { "ClusterIdentifier": "${RedshiftClusterIdentifier}", "Database": "${RedshiftDbName}", "Sql": "call public.incremental_sync_material_attr()", "DbUser":"${RedshiftDbUser}" }, "Next": "wait_on_load_matr_master_table", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "IntervalSeconds": 60, "MaxAttempts": 10, "BackoffRate": 2 } ] }, "wait_on_load_matr_master_table": { "Comment": "Wait before status check", "Type": "Wait", "Seconds": 15, "Next": "load_matr_master_table_status_check" }, "load_matr_master_table_status_check": { "Comment": "Check Task Status", "Type": "Task", "Resource": "arn:aws:states:::aws-sdk:redshiftdata:describeStatement", "ResultPath": "$.sql_output", "Parameters": { "Id.$": "$.sql_output.Id" }, "Next": "is_load_matr_master_table_complete", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 3, "MaxAttempts": 2 } ] }, "is_load_matr_master_table_complete": { "Comment": "check if load_customer_address_table step is complete", "Type": "Choice", "Choices": [ { "Variable": "$.sql_output.Status", "StringEquals": "FAILED", "Next": "load_matr_master_table_failed" }, { "Variable": "$.sql_output.Status", "StringEquals": "FINISHED", "Next": "load_matr_master_table_success" } ], "Default": "wait_on_load_matr_master_table" }, "load_matr_master_table_success": { "Type": "Pass", "Result": "load_customer_address_table_success", "End": true }, "load_matr_master_table_failed": { "Type": "Fail", "Cause": "Failure on load customer_address table", "Error": "Error" } } } ], "Next": "load_sales_document_header" }, "load_sales_document_header": { "Type": "Task", "Parameters": { "ClusterIdentifier": "${RedshiftClusterIdentifier}", "Database": "${RedshiftDbName}", "Sql": "call public.incremental_sync_sales_document_header()", "DbUser":"${RedshiftDbUser}" }, "Resource": "arn:aws:states:::aws-sdk:redshiftdata:executeStatement", "Next": "wait_on_sales_document_header", "ResultPath": "$.sql_output", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 60, "MaxAttempts": 5 } ] }, "wait_on_sales_document_header": { "Comment": "Wait before status check", "Type": "Wait", "Seconds": 15, "Next": "load_sales_document_header_check" }, "load_sales_document_header_check": { "Comment": "Check Task Status", "Type": "Task", "Resource": "arn:aws:states:::aws-sdk:redshiftdata:describeStatement", "ResultPath": "$.sql_output", "Parameters": { "Id.$": "$.sql_output.Id" }, "Next": "is_load_sales_document_header_complete", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 3, "MaxAttempts": 2 } ] }, "is_load_sales_document_header_complete": { "Type": "Choice", "Choices": [ { "Variable": "$.sql_output.Status", "StringEquals": "FAILED", "Next": "load_sales_document_header_failed" }, { "Variable": "$.sql_output.Status", "StringEquals": "FINISHED", "Next": "load_sales_document_header_succeeded" } ], "Default": "wait_on_sales_document_header" }, "load_sales_document_header_succeeded": { "Type": "Pass", "Next": "load_sales_document_item_data" }, "load_sales_document_item_data": { "Type": "Task", "Parameters": { "ClusterIdentifier": "${RedshiftClusterIdentifier}", "Database": "${RedshiftDbName}", "Sql": "call public.incremental_sync_sales_document_item()", "DbUser":"${RedshiftDbUser}" }, "Resource": "arn:aws:states:::aws-sdk:redshiftdata:executeStatement", "Next": "wait_on_sales_document_item", "ResultPath": "$.sql_output", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 60, "MaxAttempts": 5 } ] }, "wait_on_sales_document_item": { "Comment": "Wait before status check", "Type": "Wait", "Seconds": 15, "Next": "load_sales_document_item_check" }, "load_sales_document_item_check": { "Comment": "Check Task Status", "Type": "Task", "Resource": "arn:aws:states:::aws-sdk:redshiftdata:describeStatement", "ResultPath": "$.sql_output", "Parameters": { "Id.$": "$.sql_output.Id" }, "Next": "is_load_sales_document_item_complete", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 3, "MaxAttempts": 2 } ] }, "is_load_sales_document_item_complete": { "Type": "Choice", "Choices": [ { "Variable": "$.sql_output.Status", "StringEquals": "FAILED", "Next": "load_sales_document_item_failed" }, { "Variable": "$.sql_output.Status", "StringEquals": "FINISHED", "Next": "load_sales_document_item_succeeded" } ], "Default": "wait_on_sales_document_item" }, "load_sales_document_item_failed": { "Type": "Fail" }, "load_sales_document_header_failed": { "Type": "Fail" }, "load_sales_document_item_succeeded": { "Type": "Pass", "Next": "load_sales_delivery_header" }, "load_sales_delivery_header": { "Type": "Task", "Parameters": { "ClusterIdentifier": "${RedshiftClusterIdentifier}", "Database": "${RedshiftDbName}", "Sql": "call public.incremental_sync_sales_delivery_header()", "DbUser":"${RedshiftDbUser}" }, "Resource": "arn:aws:states:::aws-sdk:redshiftdata:executeStatement", "ResultPath": "$.sql_output", "Next": "wait_on_sales_delivery_header", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 60, "MaxAttempts": 5 } ] }, "wait_on_sales_delivery_header": { "Comment": "Wait before status check", "Type": "Wait", "Seconds": 15, "Next": "load_sales_delivery_header_check" }, "load_sales_delivery_header_check": { "Comment": "Check Task Status", "Type": "Task", "Resource": "arn:aws:states:::aws-sdk:redshiftdata:describeStatement", "ResultPath": "$.sql_output", "Parameters": { "Id.$": "$.sql_output.Id" }, "Next": "is_sales_delivery_header_complete", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 3, "MaxAttempts": 2 } ] }, "is_sales_delivery_header_complete": { "Type": "Choice", "Choices": [ { "Variable": "$.sql_output.Status", "StringEquals": "FAILED", "Next": "load_sales_delivery_header_failed" }, { "Variable": "$.sql_output.Status", "StringEquals": "FINISHED", "Next": "load_sales_delivery_header_succeeded" } ], "Default": "wait_on_sales_delivery_header" }, "load_sales_delivery_header_succeeded": { "Type": "Pass", "Next": "load_delivery_item" }, "load_delivery_item": { "Type": "Task", "Parameters": { "ClusterIdentifier": "${RedshiftClusterIdentifier}", "Database": "${RedshiftDbName}", "Sql": "call public.incremental_sync_delivery_item()", "DbUser":"${RedshiftDbUser}" }, "Resource": "arn:aws:states:::aws-sdk:redshiftdata:executeStatement", "ResultPath": "$.sql_output", "Next": "wait_on_delivery_item", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 60, "MaxAttempts": 5 } ] }, "wait_on_delivery_item": { "Comment": "Wait before status check", "Type": "Wait", "Seconds": 15, "Next": "load_delivery_item_check" }, "load_delivery_item_check": { "Comment": "Check Task Status", "Type": "Task", "Resource": "arn:aws:states:::aws-sdk:redshiftdata:describeStatement", "ResultPath": "$.sql_output", "Parameters": { "Id.$": "$.sql_output.Id" }, "Next": "is_delivery_item_complete", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 3, "MaxAttempts": 2 } ] }, "is_delivery_item_complete": { "Type": "Choice", "Choices": [ { "Variable": "$.sql_output.Status", "StringEquals": "FINISHED", "Next": "load_delivery_item_succeeded" }, { "Variable": "$.sql_output.Status", "StringEquals": "FAILED", "Next": "load_delivery_item_failed" } ], "Default": "wait_on_delivery_item" }, "load_delivery_item_succeeded": { "Type": "Pass", "Next": "load_billing_document_header" }, "load_billing_document_header": { "Type": "Task", "Parameters": { "ClusterIdentifier": "${RedshiftClusterIdentifier}", "Database": "${RedshiftDbName}", "Sql": "call public.incremental_sync_billing_document_header()", "DbUser":"${RedshiftDbUser}" }, "Resource": "arn:aws:states:::aws-sdk:redshiftdata:executeStatement", "ResultPath": "$.sql_output", "Next": "wait_on_billing_document_header", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 60, "MaxAttempts": 5 } ] }, "wait_on_billing_document_header": { "Comment": "Wait before status check", "Type": "Wait", "Seconds": 15, "Next": "load_billing_document_header_check" }, "load_billing_document_header_check": { "Comment": "Check Task Status", "Type": "Task", "Resource": "arn:aws:states:::aws-sdk:redshiftdata:describeStatement", "ResultPath": "$.sql_output", "Parameters": { "Id.$": "$.sql_output.Id" }, "Next": "is_billing_document_header_complete", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 3, "MaxAttempts": 2 } ] }, "is_billing_document_header_complete": { "Type": "Choice", "Choices": [ { "Variable": "$.sql_output.Status", "StringEquals": "FINISHED", "Next": "load_billing_document_header_succeeded" }, { "Variable": "$.sql_output.Status", "StringEquals": "FAILED", "Next": "load_billing_document_header_failed" } ], "Default": "wait_on_billing_document_header" }, "load_billing_document_header_succeeded": { "Type": "Pass", "Next": "load_billing_document_item" }, "load_billing_document_item": { "Type": "Task", "Parameters": { "ClusterIdentifier": "${RedshiftClusterIdentifier}", "Database": "${RedshiftDbName}", "Sql": "call public.incremental_sync_billing_document_item()", "DbUser":"${RedshiftDbUser}" }, "Resource": "arn:aws:states:::aws-sdk:redshiftdata:executeStatement", "ResultPath": "$.sql_output", "Next": "wait_on_billing_document_item", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 60, "MaxAttempts": 5 } ] }, "wait_on_billing_document_item": { "Comment": "Wait before status check", "Type": "Wait", "Seconds": 15, "Next": "load_billing_document_item_check" }, "load_billing_document_item_check": { "Comment": "Check Task Status", "Type": "Task", "Resource": "arn:aws:states:::aws-sdk:redshiftdata:describeStatement", "ResultPath": "$.sql_output", "Parameters": { "Id.$": "$.sql_output.Id" }, "Next": "is_billing_document_item_complete", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 3, "MaxAttempts": 2 } ] }, "is_billing_document_item_complete": { "Type": "Choice", "Choices": [ { "Variable": "$.sql_output.Status", "StringEquals": "FINISHED", "Next": "billing_document_item_succeeded" }, { "Variable": "$.sql_output.Status", "StringEquals": "FAILED", "Next": "billing_document_item_failed" } ], "Default": "wait_on_billing_document_item" }, "billing_document_item_succeeded": { "Type": "Pass", "Next": "Parallel" }, "Parallel": { "Type": "Parallel", "Next": "Success", "Branches": [ { "StartAt": "refresh_salesorders", "States": { "refresh_salesorders": { "Type": "Task", "Parameters": { "ClusterIdentifier": "${RedshiftClusterIdentifier}", "Database": "${RedshiftDbName}", "Sql": "refresh materialized view archdm.salesorders", "DbUser":"${RedshiftDbUser}" }, "Resource": "arn:aws:states:::aws-sdk:redshiftdata:executeStatement", "ResultPath": "$.sql_output", "Next": "wait_on_refresh_salesorders", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 60, "MaxAttempts": 5 } ] }, "wait_on_refresh_salesorders": { "Type": "Wait", "Seconds": 15, "Next": "refresh_salesorders_check" }, "refresh_salesorders_check": { "Comment": "Check Task Status", "Type": "Task", "Resource": "arn:aws:states:::aws-sdk:redshiftdata:describeStatement", "ResultPath": "$.sql_output", "Parameters": { "Id.$": "$.sql_output.Id" }, "Next": "is_refresh_salesorders_complete", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 3, "MaxAttempts": 2 } ] }, "is_refresh_salesorders_complete": { "Type": "Choice", "Choices": [ { "Variable": "$.sql_output.Status", "StringEquals": "FINISHED", "Next": "refresh_salesorders_succeeded" }, { "Variable": "$.sql_output.Status", "StringEquals": "FAILED", "Next": "rfresh_salesorders_failed" } ], "Default": "wait_on_refresh_salesorders" }, "refresh_salesorders_succeeded": { "Type": "Pass", "End": true, "ResultPath": null }, "rfresh_salesorders_failed": { "Type": "Fail" } } }, { "StartAt": "refresh_invoicedocs", "States": { "refresh_invoicedocs": { "Type": "Task", "Parameters": { "ClusterIdentifier": "${RedshiftClusterIdentifier}", "Database": "${RedshiftDbName}", "Sql": "refresh materialized view archdm.invoicedocs", "DbUser":"${RedshiftDbUser}" }, "Resource": "arn:aws:states:::aws-sdk:redshiftdata:executeStatement", "ResultPath": "$.sql_output", "Next": "wait_on_refresh_invoicedocs", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 60, "MaxAttempts": 5 } ] }, "wait_on_refresh_invoicedocs": { "Type": "Wait", "Seconds": 15, "Next": "refresh_invoicedocs_check" }, "refresh_invoicedocs_check": { "Comment": "Check Task Status", "Type": "Task", "Resource": "arn:aws:states:::aws-sdk:redshiftdata:describeStatement", "ResultPath": "$.sql_output", "Parameters": { "Id.$": "$.sql_output.Id" }, "Next": "is_refresh_invoicedocs_complete", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 2, "MaxAttempts": 2 } ] }, "is_refresh_invoicedocs_complete": { "Type": "Choice", "Choices": [ { "Variable": "$.sql_output.Status", "StringEquals": "FINISHED", "Next": "refresh_invoicedocs_succeeded" }, { "Variable": "$.sql_output.Status", "StringEquals": "FAILED", "Next": "rfresh_invoicedocs_failed" } ], "Default": "wait_on_refresh_invoicedocs" }, "refresh_invoicedocs_succeeded": { "Type": "Pass", "End": true, "ResultPath": null }, "rfresh_invoicedocs_failed": { "Type": "Fail" } } }, { "StartAt": "refresh_billingdocs", "States": { "refresh_billingdocs": { "Type": "Task", "Parameters": { "ClusterIdentifier": "${RedshiftClusterIdentifier}", "Database": "${RedshiftDbName}", "Sql": "call public.incremental_sync_billing_document_item()", "DbUser":"${RedshiftDbUser}" }, "Resource": "arn:aws:states:::aws-sdk:redshiftdata:executeStatement", "ResultPath": "$.sql_output", "Next": "wait_on_refresh_billingdocs", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 60, "MaxAttempts": 5 } ] }, "wait_on_refresh_billingdocs": { "Type": "Wait", "Seconds": 15, "Next": "refresh_billingdocs_check" }, "refresh_billingdocs_check": { "Comment": "Check Task Status", "Type": "Task", "Resource": "arn:aws:states:::aws-sdk:redshiftdata:describeStatement", "ResultPath": "$.sql_output", "Parameters": { "Id.$": "$.sql_output.Id" }, "Next": "is_refresh_billingdocs_complete", "Retry": [ { "ErrorEquals": [ "States.ALL" ], "BackoffRate": 2, "IntervalSeconds": 3, "MaxAttempts": 2 } ] }, "is_refresh_billingdocs_complete": { "Type": "Choice", "Choices": [ { "Variable": "$.sql_output.Status", "StringEquals": "FINISHED", "Next": "refresh_billingorders_succeeded" }, { "Variable": "$.sql_output.Status ", "StringEquals": "FAILED", "Next": "refresh_bilingorders_failed" } ], "Default": "wait_on_refresh_billingdocs" }, "refresh_billingorders_succeeded": { "Type": "Pass", "End": true, "ResultPath": null }, "refresh_bilingorders_failed": { "Type": "Fail" } } } ], "ResultPath": null }, "Success": { "Type": "Succeed" }, "billing_document_item_failed": { "Type": "Fail" }, "load_billing_document_header_failed": { "Type": "Fail" }, "load_delivery_item_failed": { "Type": "Fail" }, "load_sales_delivery_header_failed": { "Type": "Fail" } } } - RedshiftClusterIdentifier: !Select [0, !Split [".", !Ref RedshiftClusterEndpoint]] RedshiftDbName: !Select [1, !Split ["/", !Ref RedshiftClusterEndpoint]] RedshiftDbUser: !Ref DbUsername RoleArn: !GetAtt StateMachineExecutionRole.Arn LambdaInvokeStepFunctionRole: Type: AWS::IAM::Role Properties: RoleName: !Sub "LambdaInvokeStepFunctionRole-${AWS::AccountId}" Description: IAM Role for lambda to execute the Step Function AssumeRolePolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Principal: Service: - lambda.amazonaws.com - events.amazonaws.com Action: - sts:AssumeRole Path: / Policies: - PolicyName: LambdaInvokePolicy PolicyDocument : Version: 2012-10-17 Statement: - Effect: "Allow" Action: - states:StartExecution Resource: !Ref RedshiftETLStepFunction - PolicyName: LambdaCloudFormationPolicy PolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Action: - s3:* Resource: - !Sub "arn:aws:s3:::cloudformation-custom-resource-response-${AWS::Region}" - !Sub "arn:aws:s3:::cloudformation-waitcondition-${AWS::Region}" - !Sub "arn:aws:s3:::cloudformation-custom-resource-response-${AWS::Region}/*" - !Sub "arn:aws:s3:::cloudformation-waitcondition-${AWS::Region}/*" LambdaInvokeStepFunction: Type: AWS::Lambda::Function Properties: FunctionName: !Sub "LambdaInvokeRedshift-load-order-to-cash-${AWS::AccountId}" Description: Lambda to execute the step function Handler: index.handler Runtime: python3.8 Role: !GetAtt 'LambdaInvokeStepFunctionRole.Arn' Timeout: 60 Code: ZipFile: | import boto3 import traceback import json import cfnresponse def handler(event, context): print(event) step_function_client = boto3.client('stepfunctions') res = {} if event['RequestType'] != 'Delete': try: step_function_input = {"comment": "Execute ETL Workflow for Redshift"} response = step_function_client.start_execution(stateMachineArn=event['ResourceProperties'].get('StepFunctionArn'), input=json.dumps(step_function_input) ) print(response) except: print(traceback.format_exc()) cfnresponse.send(event, context, cfnresponse.FAILED, input) raise cfnresponse.send(event, context, cfnresponse.SUCCESS, res) StartStepFunction: Type: Custom::LambdaStartStepFunction Properties: ServiceToken: !GetAtt [LambdaInvokeStepFunction, Arn] StepFunctionArn: !Ref RedshiftETLStepFunction EventRule0: Type: AWS::Events::Rule Properties: EventBusName: default EventPattern: source: - aws.states detail-type: - Step Functions Execution Status Change detail: status: - SUCCEEDED stateMachineArn: - >- arn:aws:states:us-east-1:086684561838:stateMachine:RedshiftETLStepFunction-EeLMsZIAos5S Name: Trigger_SAP_Analytics_Step_Function2 State: ENABLED Targets: - Id: Ida6ce276f-bfb5-486a-a534-f1456cb24585 Arn: !Ref RedshiftETLStepFunction RoleArn: !GetAtt 'LambdaInvokeStepFunctionRole.Arn' Outputs: RedshiftETLStepFunctionArn: Description: "The ARN of the step function used for ETL orchestration" Value: Ref: RedshiftETLStepFunction