Home Big Data Implement information warehousing resolution utilizing dbt on Amazon Redshift

Implement information warehousing resolution utilizing dbt on Amazon Redshift

Implement information warehousing resolution utilizing dbt on Amazon Redshift


Amazon Redshift is a cloud information warehousing service that gives high-performance analytical processing based mostly on a massively parallel processing (MPP) structure. Constructing and sustaining information pipelines is a standard problem for all enterprises. Managing the SQL recordsdata, integrating cross-team work, incorporating all software program engineering rules, and importing exterior utilities could be a time-consuming activity that requires advanced design and many preparation.

dbt (DataBuildTool) presents this mechanism by introducing a well-structured framework for information evaluation, transformation and orchestration. It additionally applies common software program engineering rules like integrating with git repositories, organising DRYer code, including practical check instances, and together with exterior libraries. This mechanism permits builders to deal with making ready the SQL recordsdata per the enterprise logic, and the remainder is taken care of by dbt.

On this put up, we glance into an optimum and cost-effective method of incorporating dbt inside Amazon Redshift. We use Amazon Elastic Container Registry (Amazon ECR) to retailer our dbt Docker photos and AWS Fargate as an Amazon Elastic Container Service (Amazon ECS) activity to run the job.

How does the dbt framework work with Amazon Redshift?

dbt has an Amazon Redshift adapter module named dbt-redshift that allows it to attach and work with Amazon Redshift. All of the connection profiles are configured inside the dbt profiles.yml file. In an optimum surroundings, we retailer the credentials in AWS Secrets and techniques Supervisor and retrieve them.

The next code exhibits the contents of profile.yml:


goal: dev
kind: redshift
host: "{{ env_var('DBT_HOST') }}"
consumer: "{{ env_var('DBT_USER') }}"
password: "{{ env_var('DBT_PASSWORD') }}"
port: 5439
dbname: "{{ env_var('DBT_DB_NAME') }}"
schema: dev
threads: 4
keepalives_idle: 240 # default 240 seconds
connect_timeout: 10 # default 10 seconds
sslmode: require
ra3_node: true

The next diagram illustrates the important thing elements of the dbt framework:

The first elements are as follows:

  • Fashions – These are written as a SELECT assertion and saved as a .sql file. All of the transformation queries could be written right here which could be materialized as a desk or view. The desk refresh could be full or incremental based mostly on the configuration. For extra data, refer SQL fashions.
  • Snapshots – These implements type-2 slowly altering dimensions (SCDs) over mutable supply tables. These SCDs determine how a row in a desk modifications over time.
  • Seeds – These are CSV recordsdata in your dbt mission (usually in your seeds listing), which dbt can load into your information warehouse utilizing the dbt seed command.
  • Assessments – These are assertions you make about your fashions and different sources in your dbt mission (akin to sources, seeds, and snapshots). Whenever you run dbt check, dbt will let you know if every check in your mission passes or fails.
  • Macros – These are items of code that may be reused a number of occasions. They’re analogous to “features” in different programming languages, and are extraordinarily helpful if you end up repeating code throughout a number of fashions.

These elements are saved as .sql recordsdata and are run by dbt CLI instructions. Throughout the run, dbt creates a Directed Acyclic Graph (DAG) based mostly on the inner reference between the dbt elements. It makes use of the DAG to orchestrate the run sequence accordingly.

A number of profiles could be created inside the profiles.yml file, which dbt can use to focus on completely different Redshift environments whereas working. For extra data, check with Redshift arrange.

Resolution overview

The next diagram illustrates our resolution structure.

The workflow incorporates the next steps:

  1. The open supply dbt-redshift connector is used to create our dbt mission together with all the required fashions, snapshots, assessments, macros and profiles.
  2. A Docker picture is created and pushed to the ECR repository.
  3. The Docker picture is run by Fargate as an ECS activity triggered by way of AWS Step Features. All of the Amazon Redshift credentials are saved in Secrets and techniques Supervisor, which is then utilized by the ECS activity to attach with Amazon Redshift.
  4. Throughout the run, dbt converts all of the fashions, snapshots, assessments and macros to Amazon Redshift compliant SQL statements and it orchestrates the run based mostly on the inner information lineage graph maintained. These SQL instructions are run immediately on the Redshift cluster and subsequently the workload is pushed to Amazon Redshift immediately.
  5. When the run is full, dbt will create a set of HTML and JSON recordsdata to host the dbt documentation, which describes the info catalog, compiled SQL statements, information lineage graph, and extra.


You must have the next stipulations:

  • A great understanding of the dbt rules and implementation steps.
  • An AWS account with consumer function permission to entry the AWS companies used on this resolution.
  • Safety teams for Fargate to entry the Redshift cluster and Secrets and techniques Supervisor from Amazon ECS.
  • A Redshift cluster. For creation directions, check with Create a cluster.
  • An ECR repository: For directions, check with Creating a non-public repository
  • A Secrets and techniques Supervisor secret containing all of the credentials for connecting to Amazon Redshift. This contains the host, port, database identify, consumer identify, and password. For extra data, check with Create an AWS Secrets and techniques Supervisor database secret.
  • An Amazon Easy Storage (Amazon S3) bucket to host documentation recordsdata.

Create a dbt mission

We’re utilizing dbt CLI so all instructions are run within the command line. Due to this fact, set up pip if not already put in. Seek advice from set up for extra data.

To create a dbt mission, full the next steps:

  1. Set up dependent dbt packages:
    pip set up dbt-redshift
  2. Initialize a dbt mission utilizing the dbt init <project_name> command, which creates all of the template folders robotically.
  3. Add all of the required DBT artifacts.
    Seek advice from the dbt-redshift-etlpattern repo which features a reference dbt mission. For extra details about constructing tasks, check with About dbt tasks.

Within the reference mission, we have now carried out the next options:

  • SCD kind 1 utilizing incremental fashions
  • SCD kind 2 utilizing snapshots
  • Seed look-up recordsdata
  • Macros for including reusable code within the mission
  • Assessments for analyzing inbound information

The Python script is ready to fetch the credentials required from Secrets and techniques Supervisor for accessing Amazon Redshift. Seek advice from the export_redshift_connection.py file.

  1. Put together the run_dbt.sh script to run the dbt pipeline sequentially. This script is positioned within the root folder of the dbt mission as proven in pattern repo.
-- Import the dependent exterior libraries
dbt deps --profiles-dir . --project-dir .

-- Create tables based mostly on the seed recordsdata
dbt seed --profiles-dir . --project-dir .

-- Run all of the mannequin recordsdata
dbt run --profiles-dir . --project-dir .

-- Run all of the snapshot recordsdata
dbt snapshot --profiles-dir . --project-dir .

-- Run all inbuilt and customized check instances ready
dbt check --profiles-dir . --project-dir .

-- Generate dbt documentation recordsdata
dbt docs generate --profiles-dir . --project-dir .

--Copying dbt outputs to s3 bucket - for internet hosting
aws s3 cp --recursive --exclude="*" --include="*.json" --include="*.html" dbt/goal/ s3://<bucketName>/REDSHIFT_POC/

  1. Create a Docker file within the mum or dad listing of the dbt mission folder. This step builds the picture of the dbt mission to be pushed to the ECR repository.
FROM python:3

ADD dbt_src /dbt_src

RUN pip set up -U pip

# Set up DBT libraries
RUN pip set up --no-cache-dir dbt-core

RUN pip set up --no-cache-dir dbt-redshift

RUN pip set up --no-cache-dir boto3

RUN pip set up --no-cache-dir awscli

WORKDIR /dbt_src

RUN chmod -R 755 .

ENTRYPOINT [ "/bin/sh", "-c" ]

CMD ["./run_dbt.sh"]

Add the picture to Amazon ECR and run it as an ECS activity

To push the picture to the ECR repository, full the next steps:

  1. Retrieve an authentication token and authenticate your Docker shopper to your registry:
    aws ecr get-login-password --region <region_name> | docker login --username AWS --password-stdin <repository_name>

  2. Construct your Docker picture utilizing the next command:
docker construct -t <picture tag> .

  1. After the construct is full, tag your picture so you possibly can push it to the repository:
docker tag <picture tag>:newest <repository_name>:newest

  1. Run the next command to push the picture to your newly created AWS repository:
docker push <repository_name>/<picture tag>:newest

  1. On the Amazon ECS console, create a cluster with Fargate as an infrastructure possibility.
  2. Present your VPC and subnets as required.
  3. After you create the cluster, create an ECS activity and assign the created dbt picture as the duty definition household.
  4. Within the networking part, select your VPC, subnets, and safety group to attach with Amazon Redshift, Amazon S3 and Secrets and techniques Supervisor.

This activity will set off the run_dbt.sh pipeline script and run all of the dbt instructions sequentially. When the script is full, we will see the leads to Amazon Redshift and the documentation recordsdata pushed to Amazon S3.

  1. You may host the documentation by way of Amazon S3 static web site internet hosting. For extra data, check with Internet hosting a static web site utilizing Amazon S3.
  2. Lastly, you possibly can run this activity in Step Features as an ECS activity to schedule the roles as required. For extra data, check with Handle Amazon ECS or Fargate Duties with Step Features.

The dbt-redshift-etlpattern repo now has all of the code samples required.

Price for executing dbt jobs in AWS Fargate as an Amazon ECS activity with minimal operational necessities would take round $1.5 (cost_link) per thirty days.

Clear up

Full the next steps to scrub up your sources:

  1. Delete the ECS Cluster you created.
  2. Delete the ECR repository you created for storing the picture recordsdata.
  3. Delete the Redshift Cluster you created.
  4. Delete the Redshift Secrets and techniques saved in Secrets and techniques Supervisor.


This put up lined the fundamental implementation of utilizing dbt with Amazon Redshift in a cost-efficient method by utilizing Fargate in Amazon ECS. We described the important thing infrastructure and configuration set-up with a pattern mission. This structure may help you reap the benefits of the advantages of getting a dbt framework to handle your information warehouse platform in Amazon Redshift.

For extra details about dbt macros and fashions for Amazon Redshift inside operation and upkeep, check with the next GitHub repo. In subsequent put up, we’ll discover the normal extract, rework, and cargo (ETL) patterns which you could implement utilizing the dbt framework in Amazon Redshift. Check this resolution in your account and supply suggestions or recommendations within the feedback.

In regards to the Authors

Seshadri Senthamaraikannan is an information architect with AWS skilled companies staff based mostly in London, UK. He’s effectively skilled and specialised in Information Analytics and works with prospects specializing in constructing revolutionary and scalable options in AWS Cloud to satisfy their enterprise targets. In his spare time, he enjoys spending time together with his household and play sports activities.

Mohamed Hamdy is a Senior Large Information Architect with AWS Skilled Companies based mostly in London, UK. He has over 15 years of expertise architecting, main, and constructing information warehouses and massive information platforms. He helps prospects develop huge information and analytics options to speed up their enterprise outcomes by means of their cloud adoption journey. Exterior of labor, Mohamed likes travelling, working, swimming and enjoying squash.


Supply hyperlink


Please enter your comment!
Please enter your name here