Simplifying knowledge processing at Capitec with Amazon Redshift integration for Apache Spark







This put up is co-written with Preshen Goobiah and Johan Olivier from Capitec.

Apache Spark is a widely-used open supply distributed processing system famend for dealing with large-scale knowledge workloads. It finds frequent software amongst Spark builders working with Amazon EMR, Amazon SageMaker, AWS Glue and customized Spark purposes.

Amazon Redshift affords seamless integration with Apache Spark, permitting you to simply entry your Redshift knowledge on each Amazon Redshift provisioned clusters and Amazon Redshift Serverless. This integration expands the probabilities for AWS analytics and machine studying (ML) options, making the information warehouse accessible to a broader vary of purposes.

With the Amazon Redshift integration for Apache Spark, you may shortly get began and effortlessly develop Spark purposes utilizing well-liked languages like Java, Scala, Python, SQL, and R. Your purposes can seamlessly learn from and write to your Amazon Redshift knowledge warehouse whereas sustaining optimum efficiency and transactional consistency. Moreover, you’ll profit from efficiency enhancements by means of pushdown optimizations, additional enhancing the effectivity of your operations.

Capitec, South Africa’s greatest retail financial institution with over 21 million retail banking purchasers, goals to supply easy, reasonably priced and accessible monetary companies as a way to assist South Africans financial institution higher in order that they’ll reside higher. On this put up, we focus on the profitable integration of the open supply Amazon Redshift connector by Capitec’s shared companies Function Platform staff. On account of using the Amazon Redshift integration for Apache Spark, developer productiveness elevated by an element of 10, characteristic era pipelines had been streamlined, and knowledge duplication decreased to zero.

The enterprise alternative

There are 19 predictive fashions in scope for using 93 options constructed with AWS Glue throughout Capitec’s Retail Credit score divisions. Function information are enriched with details and dimensions saved in Amazon Redshift. Apache PySpark was chosen to create options as a result of it affords a quick, decentralized, and scalable mechanism to wrangle knowledge from various sources.

These manufacturing options play a vital function in enabling real-time fixed-term mortgage purposes, bank card purposes, batch month-to-month credit score conduct monitoring, and batch day by day wage identification throughout the enterprise.

The info sourcing downside

To make sure the reliability of PySpark knowledge pipelines, it’s important to have constant record-level knowledge from each dimensional and truth tables saved within the Enterprise Information Warehouse (EDW). These tables are then joined with tables from the Enterprise Information Lake (EDL) at runtime.

Throughout characteristic improvement, knowledge engineers require a seamless interface to the EDW. This interface permits them to entry and combine the mandatory knowledge from the EDW into the information pipelines, enabling environment friendly improvement and testing of options.

Earlier answer course of

Within the earlier answer, product staff knowledge engineers spent half-hour per run to manually expose Redshift knowledge to Spark. The steps included the next:

  1. Assemble a predicated question in Python.
  2. Submit an UNLOAD question by way of the Amazon Redshift Information API.
  3. Catalog knowledge within the AWS Glue Information Catalog by way of the AWS SDK for Pandas utilizing sampling.

This method posed points for big datasets, required recurring upkeep from the platform staff, and was complicated to automate.

Present answer overview

Capitec was capable of resolve these issues with the Amazon Redshift integration for Apache Spark inside characteristic era pipelines. The structure is outlined within the following diagram.

The workflow consists of the next steps:

  1. Inside libraries are put in into the AWS Glue PySpark job by way of AWS CodeArtifact.
  2. An AWS Glue job retrieves Redshift cluster credentials from AWS Secrets and techniques Supervisor and units up the Amazon Redshift connection (injects cluster credentials, unload areas, file codecs) by way of the shared inside library. The Amazon Redshift integration for Apache Spark additionally helps utilizing AWS Identification and Entry Administration (IAM) to retrieve credentials and connect with Amazon Redshift.
  3. The Spark question is translated to an Amazon Redshift optimized question and submitted to the EDW. That is achieved by the Amazon Redshift integration for Apache Spark.
  4. The EDW dataset is unloaded into a short lived prefix in an Amazon Easy Storage Service (Amazon S3) bucket.
  5. The EDW dataset from the S3 bucket is loaded into Spark executors by way of the Amazon Redshift integration for Apache Spark.
  6. The EDL dataset is loaded into Spark executors by way of the AWS Glue Information Catalog.

These elements work collectively to make sure that knowledge engineers and manufacturing knowledge pipelines have the mandatory instruments to implement the Amazon Redshift integration for Apache Spark, run queries, and facilitate the unloading of information from Amazon Redshift to the EDL.

Utilizing the Amazon Redshift integration for Apache Spark in AWS Glue 4.0

On this part, we exhibit the utility of the Amazon Redshift integration for Apache Spark by enriching a mortgage software desk residing within the S3 knowledge lake with shopper data from the Redshift knowledge warehouse in PySpark.

The dimclient desk in Amazon Redshift accommodates the next columns:

  • ClientKey – INT8
  • ClientAltKey – VARCHAR50
  • PartyIdentifierNumber – VARCHAR20
  • ClientCreateDate – DATE
  • IsCancelled – INT2
  • RowIsCurrent – INT2

The loanapplication desk within the AWS Glue Information Catalog accommodates the next columns:

  • RecordID – BIGINT
  • LogDate – TIMESTAMP
  • PartyIdentifierNumber – STRING

The Redshift desk is learn by way of the Amazon Redshift integration for Apache Spark and cached. See the next code:

import pyspark.sql.features as F
from pyspark.sql import SQLContext
sc = # present SparkContext
sql_context = SQLContext(sc)

secretsmanager_client = boto3.shopper('secretsmanager')
secret_manager_response = secretsmanager_client.get_secret_value(
username = # get username from secret_manager_response
password = # get password from secret_manager_response
url = "jdbc:redshift://redshifthost:5439/database?consumer=" + username + "&password=" + password

read_config = {
    "url": url,
    "tempdir": "s3://<capitec-redshift-temp-bucket>/<uuid>/",
    "unload_s3_format": "PARQUET"

d_client = (
    .choice("question", f"choose * from edw_core.dimclient")
    .the place((F.col("RowIsCurrent") == 1) & (F.col("isCancelled") == 0))

Mortgage software information are learn in from the S3 knowledge lake and enriched with the dimclient desk on Amazon Redshift data:

import pyspark.sql.features as F
from awsglue.context import GlueContext
from pyspark import SparkContext

glue_ctx = GlueContext(SparkContext.getOrCreate())

push_down_predicate = (
    f"meta_extract_start_utc_ms between "
    f" 18:00:00.000000' and "
    f"'2023-07-13 06:00:00.000000'"

catalog_id = # Glue Information Catalog

# Deciding on solely the next columns

d_controller = (glue_ctx.create_dynamic_frame.from_catalog(catalog_id=catalog_id,
                .withColumn("LogDate", F.date_format("LogDate", "yyyy-MM-dd").forged("string"))

# Left Be part of on PartyIdentifierNumber and enriching the mortgage software file
d_controller_enriched = a part of(d_client, on=["PartyIdentifierNumber"], how="left").cache()

In consequence, the mortgage software file (from the S3 knowledge lake) is enriched with the ClientCreateDate column (from Amazon Redshift).

How the Amazon Redshift integration for Apache Spark solves the information sourcing downside

The Amazon Redshift integration for Apache Spark successfully addresses the information sourcing downside by means of the next mechanisms:

  • Simply-in-time studying – The Amazon Redshift integration for Apache Spark connector reads Redshift tables in a just-in-time method, guaranteeing the consistency of information and schema. That is notably beneficial for Kind 2 slowly altering dimension (SCD) and timespan accumulating snapshot details. By combining these Redshift tables with the supply system AWS Glue Information Catalog tables from the EDL inside manufacturing PySpark pipelines, the connector permits seamless integration of information from a number of sources whereas sustaining knowledge integrity.
  • Optimized Redshift queries – The Amazon Redshift integration for Apache Spark performs a vital function in changing the Spark question plan into an optimized Redshift question. This conversion course of simplifies the event expertise for the product staff by adhering to the information locality precept. The optimized queries use the capabilities and efficiency optimizations of Amazon Redshift, guaranteeing environment friendly knowledge retrieval and processing from Amazon Redshift for the PySpark pipelines. This helps streamline the event course of whereas enhancing the general efficiency of the information sourcing operations.

Gaining one of the best efficiency

The Amazon Redshift integration for Apache Spark robotically applies predicate and question pushdown to optimize efficiency. You’ll be able to achieve efficiency enhancements through the use of the default Parquet format used for unloading with this integration.

For extra particulars and code samples, check with New – Amazon Redshift Integration with Apache Spark.

Answer Advantages

The adoption of the mixing yielded a number of important advantages for the staff:

  • Enhanced developer productiveness – The PySpark interface offered by the mixing boosted developer productiveness by an element of 10, enabling smoother interplay with Amazon Redshift.
  • Elimination of information duplication – Duplicate and AWS Glue cataloged Redshift tables within the knowledge lake had been eradicated, leading to a extra streamlined knowledge surroundings.
  • Decreased EDW load – The mixing facilitated selective knowledge unloading, minimizing the load on the EDW by extracting solely the mandatory knowledge.

Through the use of the Amazon Redshift integration for Apache Spark, Capitec has paved the best way for improved knowledge processing, elevated productiveness, and a extra environment friendly characteristic engineering ecosystem.


On this put up, we mentioned how the Capitec staff efficiently carried out the Apache Spark Amazon Redshift integration for Apache Spark to simplify their characteristic computation workflows. They emphasised the significance of using decentralized and modular PySpark knowledge pipelines for creating predictive mannequin options.

At present, the Amazon Redshift integration for Apache Spark is utilized by 7 manufacturing knowledge pipelines and 20 improvement pipelines, showcasing its effectiveness inside Capitec’s surroundings.

Transferring ahead, the shared companies Function Platform staff at Capitec plans to increase the adoption of the Amazon Redshift integration for Apache Spark in several enterprise areas, aiming to additional improve knowledge processing capabilities and promote environment friendly characteristic engineering practices.

For extra data on utilizing the Amazon Redshift integration for Apache Spark, check with the next assets:

In regards to the Authors

Preshen Goobiah is the Lead Machine Studying Engineer for the Function Platform at Capitec. He’s centered on designing and constructing Function Retailer elements for enterprise use. In his spare time, he enjoys studying and touring.

Johan Olivier is a Senior Machine Studying Engineer for Capitec’s Mannequin Platform. He’s an entrepreneur and problem-solving fanatic. He enjoys music and socializing in his spare time.

Sudipta Bagchi is a Senior Specialist Options Architect at Amazon Net Companies. He has over 12 years of expertise in knowledge and analytics, and helps clients design and construct scalable and high-performant analytics options. Exterior of labor, he loves operating, touring, and enjoying cricket. Join with him on LinkedIn.

Syed Humair is a Senior Analytics Specialist Options Architect at Amazon Net Companies (AWS). He has over 17 years of expertise in enterprise structure specializing in Information and AI/ML, serving to AWS clients globally to handle their enterprise and technical necessities. You’ll be able to join with him on LinkedIn.

Vuyisa Maswana is a Senior Options Architect at AWS, based mostly in Cape City. Vuyisa has a powerful give attention to serving to clients construct technical options to resolve enterprise issues. He has supported Capitec of their AWS journey since 2019.


Supply hyperlink

Share this


Google Presents 3 Suggestions For Checking Technical web optimization Points

Google printed a video providing three ideas for utilizing search console to establish technical points that may be inflicting indexing or rating issues. Three...

A easy snapshot reveals how computational pictures can shock and alarm us

Whereas Tessa Coates was making an attempt on wedding ceremony clothes final month, she posted a seemingly easy snapshot of herself on Instagram...

Recent articles

More like this


Please enter your comment!
Please enter your name here