Designing Data Warehouse From Scratch: End to End Guide

Name: Designing Data Warehouse From Scratch: End to End Guide
Rating: 3.8 (16 reviews)

Master the art of building a data warehouse from the ground up with this comprehensive end to end guide

Created byGaniyu Yusuf

Last updated 4/2024

English

English [Auto],

What you'll learn

Begin with the basics of data warehousing, understanding its significance, and exploring data modeling concepts to design the logical view of your DW
Get hands-on with Amazon Redshift, learning how to set up, manage, and optimize your data warehouse cluster for performance and cost-efficiency.
Translate your logical model into a robust physical model in Redshift, mastering the art of schema design, table creation, and query optimization.
Explore various data ingestion methods and learn how to transform raw data into insightful, analytical information using ETL and ELT processes.
Implement the cutting-edge medallion architecture in your data warehouse to streamline data processing and enhance analytical capabilities.

Course content

10 sections • 22 lectures • 5h 12m total length

Prerequisites7:18
Highlight AWS prerequisites, including an AWS account and Redshift free tier, and cover essential services like S3, IAM, and VPC. Plan data sources, schema design, ETL, security, cost, and monitoring.
Steps Involved in Creating a Data Warehouse11:32
Identify business use cases and define grain, dimensions, and facts to design the data warehouse. Apply ETL or ELT, ensure data quality, and plan documentation and training.
Discussing the business usecase1:32
Explore the business use case of a bank, mapping customers, transactions, loans, and investments into a robust data warehouse pipeline with multi-channel data ingestion, ensuring accuracy for stakeholders.

Creating Security Groups on AWS1:27
Configure VPC security groups in AWS by editing inbound and outbound rules, adding a redshift rule for your IP, and applying changes to the cluster initialization.
Connecting and Managing your Redshift Cluster5:02
Connect to the Redshift cluster using DBeaver or the query editor and test the JDBC connection. Then automate the physical architecture with an AWS Glue crawl to create tables.
Creating Glue crawler to create table from S38:30
Configure an AWS Glue crawler to create tables from S3 data, using a pipe separator in the CSV classifier, with on-demand runs for a YouTube Redshift data warehouse.
Uploading data to s3 and crawling them with Glue crawler8:57
Upload data to S3 buckets, organize folders like accounts, channels, currencies, customers, dates, investments, loans, and transactions, then rerun the Glue crawler to populate fact and dimension tables.
Verifying the data loaded into Data Warehouse9:46
Verify the crawler populated the Glue Data Catalog with tables, then connect Redshift to the catalog and confirm data loads by checking accounts and channels.
Creating VPC, Subnets, Internet Gateways on AWS2:26
Create a dedicated VPC for Redshift, add public and private subnets, attach an internet gateway, and configure route tables to prepare for launching a Redshift cluster.
Creating Subnet Groups on Redshift for VPC0:53
Create a cluster subnet group in Redshift for a VPC, include all subnets, and verify the cluster subnet group appears.

Creating a Redshift Cluster on AWS3:31
Create an AWS Redshift cluster with a two-node large setup, configure admin user and SQL password, and set up a default IAM role, VPC, and public access.
Finalising the creation Redshift Cluster on AWS13:33
Finalizes an AWS Redshift cluster, connects from DBeaver using the JDBC URL, and validates access; then automates table creation via AWS Glue crawlers from S3 data with a pipe-delimiter classifier.

Creating DBT Project and Implementing Medallion Architecture1:07:05
Learn to set up a dbt project with redshift, configure profiles.yaml, and implement a medallion architecture with bronze, silver, and gold layers, including incremental and table or view materializations.
Finishing up on the Gold layer Facts29:30
Finish the gold layer by materializing daily balances, investments, and transactions in the silver schema, linking investment type and currency with incremental transformations and tests.

Writing Test Cases for Dimension Layers13:00
Write test cases for the gold and other dimension layers using yaml models (dim accounts, dim channel, dim currency, dim date), define keys and relationships, and validate dbt tests.
Writing Test Cases for Gold Layer17:43
Develop and document tests for the gold layer facts, including customer interactions and daily balances, using YAML models, config, and dbt test validations to ensure correct dimensional links.
Writing Data Quality Test Cases6:50
Write and run custom DBT tests to enforce data quality rules across dimensions and facts, including negative balances, account type validity, and duplicate records.

Requirements

Basic Understanding of AWS Services
Basic SQL Technical Skills
Basic Understanding of Datawarehouse and Data Modelling

Description

This is the ultimate course for aspiring and experienced data engineers alike. This comprehensive course is meticulously designed to take you through the entire process of creating a data warehouse, from conceptualization to implementation, using Amazon Redshift and the medallion architecture.

This course is designed for data professionals, analysts, and anyone interested in mastering data warehousing, this course will equip you with the skills and knowledge needed to create robust and scalable data warehouses from the ground up.

Through hands-on lessons and real-world examples, you'll learn how to:

Establish the logical architecture and design of a data warehouse, ensuring a solid foundation for your project.
Create and configure a data warehouse in the cloud using Amazon Redshift, leveraging the power and scalability of cloud computing.
Implement various data loading strategies to efficiently populate your data warehouse with relevant data from multiple sources.
Convert the logical design into a physical implementation, setting up the necessary infrastructure and configurations on AWS.
Implement the Medallion Architecture in your Redshift data warehouse, following industry best practices for data modeling and organization.
Write comprehensive test cases and implement data quality measures to ensure the integrity and reliability of your data warehouse.
Utilize DBT (Data Build Tool) for seamless data transformation, analysis, and documentation, streamlining the entire data warehousing process.

This course is suitable for all regardless of your job function; a data analyst, business intelligence professional, or simply someone passionate about data, this course will provide you with the skills and knowledge required to design and build a robust data warehouse from scratch, enabling you to unlock valuable insights and drive data-driven decision-making within your organisation.

Enroll now and embark on a journey to become a data warehousing expert with "Designing Data Warehouse From Scratch: End to End Guide".

Who this course is for:

Suitable to everyone interested in Big Data and Data Warehouse Modelling

Designing Data Warehouse From Scratch: End to End Guide

What you'll learn

Explore related topics

Course content

Introduction3 lectures • 20min

Creating Logical View of Datawarehouse1 lecture • 34min

Converting Logical View to Physical View7 lectures • 37min

Creating a Datawarehouse in the Cloud2 lectures • 17min

Data Loading into Datawarehouse1 lecture • 50min

Implementing Medallion Architecture in Redshift Datawarehouse2 lectures • 1hr 37min

Data Quality and Testing3 lectures • 38min

DBT Analyses1 lecture • 15min

DBT Documentation1 lecture • 6min

Course Resources1 lecture • 1min

Requirements

Description

Who this course is for: