System Design - Data modeling for Datawarehouse

person icon Akshat Verma

4.3 ★★★★ ★

Buy Now

System Design - Data modeling for Datawarehouse

Name: System Design - Data modeling for Datawarehouse
Rating: 4.3 (242 reviews)
Author: Akshat Verma

Data modeling in a data warehouse like RedShift/BigQuery/Snowflake

updated on icon Updated on Jun, 2024

language icon Language - English

person icon Akshat Verma

English [CC]

category icon Database Design,Database Design & Development,Big Data,Data & Analytics,Development

Lectures -13

Duration -2 hours

4.3 ★★★★ ★

Add to Cart Buy Now

30-days Money-Back Guarantee

Training 5 or more people ?

Get your team access to 10000+ top Tutorials Point courses anytime, anywhere.

Course Description

Designing a data model for a data warehouse is fundamentally different from designing the schema for your primary database. A data warehouse is meant for supporting aggregation queries that touch a huge number of records and are very expensive. The scale in terms of number of queries is at least an order of magnitude less than the number of queries seen by a consumer-facing primary database. This distinction leads to a fundamental different data layout and indexing model in the database used in a data warehouse. Such databases are called OLAP databases.

This course will take you into the internal architecture of an OLAP database with specific focus on areas that help you how a query runs on an OLAP database. We will walk though the levers that an OLAP database provides to optimize your queries. We will then cover various scenarios that will help you design the data model for an OLAP database used in a data warehouse.

Throughout this course, we will use AWS Redshift as the OLAP database. However, the principles covered in this course are general and can be applied in any data warehouse including Snowflake or Hive. Some of these principles are also applicable in real-time column stores like HBase.

Goals

Understand the internal architecture of an OLAP store
Designing the data model to support rich analytical queries
Understand distribution and sort keys and how to leverage them
Designing your aggregation queries in a column-oriented OLAP store
Optimize Redshift Query Performance

Prerequisites

You understand the basics of SQL and now want to extend your skills to designing the data model for data warehouses

System Design - Data modeling for Datawarehouse

Curriculum

Check out the detailed breakdown of what’s inside the course

Introduction to OLAP databases
3 Lectures

Course overview 04:02 04:02
OLTP versus OLAP - Part I 12:03 12:03
OLTP Versus OLAP - Part II 04:10 04:10

OLAP Architecture
3 Lectures

Data modeling
5 Lectures

Beyond Data Modeling
2 Lectures

Instructor Details

Akshat Verma

I can broadly categorize my career into two phases. I spent the first 14 years of my career at IBM Research solving important research problems on the intersection of distributed systems, cloud and analytics and applying them to IBM products and services. My work led to numerous papers and patents, led to major features in multiple IBM products, and created new service engagements. I received IBM’s Outstanding Innovation Award and ACM Middleware’s 10 year best paper award for my work on Power management and virtual machine consolidation.

I spent the next 7.5 years in industry applying the same computer science principles to solve real world problems. I was responsible for all engineering related to hotels for Makemytrip and its subsidiaries, and built the core pricing and supply platforms at Makemytrip. I received Makemytrip’s highest honor for this work. I built the digital engineering team at Airtel from 30 to 170+ talented engineers and engineering leaders. I led some of the most significant and highest impact projects at Airtel during this time and received Airtel’s highest recognition, the Chairman Award, for all three years at Airtel.

I enjoy setting engineering practices for large teams to drive efficiency as well as working in a hands-on fashion with small (<20) member teams building systems from scratch.

Course Certificate

Use your certificate to make a career change or to advance in your current career.