layout	title	subtitle	categories	tags
post	Data Warehouse	Replicate, transform, and analyze data about book sales and orders	Data-Circle	data-circle

1. Introduction

• What is the project content about? You will replicate, transform, and analyze data about book sales and orders.
• What is the problem we are solving with the project? Data warehouses are the backbone of company analytics. This project will familiarize you with the design and architecture of data warehouses. If you are interested in a data analyst, analytics engineer, or data engineer role, this project will give you an introduction to how these roles interact with data in production.

2. Project Brief

• How is the student going to work on the project? You will collaborate with others delivering different parts of the same project.
• What is the major challenge of the project? You will implement a data pipeline and a star schema data model, then use it for dashboards.

3. Learning Objective

List the components / frameworks which the student is going to use / learn. Example below:

• concepts: data warehouses, Extract-Transform-Load, data modeling
• BigQuery - massively parallel processing (MPP) data warehouse
• airbyte - ETL tool
• dbt - transformation tool
• Metabase - BI tool

4. Extra Challenges for further developments

Add a few extra challenges for the more ambitious students.

• Create additional fact table: orders that summarizes all order line items and adds shipping costs. Users can analyze sales and shipping.
• Create additional dimensional tables: publishers and languages. Users can analyze sales per publisher and languages.
• Create additional fact tables: order_fulfillment. Users can analyze orders by status.

5. Technical Setup

How can the student get started with the project? Example:

1. Fork boilerplate repo
2. Set up the project with version control (e.g. Git)
3. Read the README.md file and have a look around the project
4. Get colors, fonts etc from the style-guide.md file
5. Set up your project/file architecture however you want
6. Start coding!

6. Resources / Hints & Tips

• (free book) The Analytics Setup Guidebook from Holistics.io: https://www.holistics.io/books/setup-analytics/start-here-introduction/
• (free course) dbt Fundamentals Course from dbt Labs: https://courses.getdbt.com/courses/fundamentals
• (blog series) Three-Part Data Modeling Series from airbyte: https://airbyte.com/blog/data-modeling-unsung-hero-data-engineering-introduction https://airbyte.com/blog/data-modeling-unsung-hero-data-engineering-approaches-and-techniques https://airbyte.com/blog/data-modeling-unsung-hero-data-engineering-architecture-pattern-tools

7. Glossary

• Airbyte: An open-source data integration platform that extracts and loads data from various sources.

  Learning Resources

  • Airbyte Documentation

  • Set up a modern data stack with Docker and Airbyte

  • Getting Started with Airbyte Guide

  • Airbyte YouTube Channel

  • Airbyte Community

  • Airbyte on GitHub

  • Data Modelling in Airbyte

• Airflow: A platform to programmatically author, schedule, and monitor workflows.

  Learning Resources

  • Airflow Documentation

  • Principles of Airflow on GitHub

  • Airflow YouTube Videos

  • Airflow Community and Mailing lists

• BI (Business Intelligence): A technology-driven process for analyzing data and presenting actionable information to help executives, managers and other corporate end users make informed business decisions.

  Learning Resources

  • Business Intelligence on Wikipedia

  • Foundations of Business Intelligence on Coursera

  • IBM introduction on Business Intelligence

• BigQuery: A web service from Google that is used for handling and analyzing big data.

  Learning Resources

  • BigQuery Official Documentation

  • BigQuery Quickstart Guides

  • BigQuery Guided Project on Coursera

• Data Modelling: The process of creating a data model for an information system by applying certain formal techniques.

  Learning Resources

  • Data Modelling on Airbyte Blog, part 1 - Introduction

  • Data Modelling on Airbyte Blog, part 2 - Approaches and Techniques

  • Data Modelling on Airbyte Blog, part 3 - Architecture Pattern, Tools

• Data Stack: A collection of software tools, platforms, and languages to systematically manage and perform tasks with large sets of data.

  Learning Resources

  • A bit more on Modern Data Stack

• Data Warehouse: A technology that aggregates structured data from one or more sources so that it can be compared and analyzed for greater business intelligence.

  Learning Resources

  • A book on Data Warehouse concepts and Analytics Setup in general

  • IBM introduction to Data Warehouse Concept

  • Data Warehouse on Wikipedia

• Database Normalization: The process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity.

  Learning Resources

  • Database Normalization on Wikipedia

  • Database Normalization on GeeksforGeeks

• DBT (Data Build Tool): A tool that allows data analysts and engineers to transform data in their warehouses by simply writing select statements.

  Learning Resources

  • DBT Documentation

  • DBT Tutorials

  • The tutorial on dbt fundamentals

• Docker: An open-source platform that automates the deployment, scaling, and management of applications.

  Learning Resources

  • Docker Official Documentation

  • Docker Get Started Guide

  • Docker Tutorials

• Docker-compose: A tool for defining and managing multi-container Docker applications.

  Learning Resources

  • Getting Started guide

• ETL (Extract, Transform, Load): The process of extracting data from outside sources, transforming it to fit operational needs, then loading it into the end target.

  Learning Resources

  • ETL explaind by AWS

  • More details at Wikipedia

• GCP (Google Cloud Platform): A suite of cloud computing services that runs on the same infrastructure that Google uses internally for its end-user products.

  Learning Resources

  • BigQuery and GCP tutorial

• Metabase: An open-source business intelligence tool that lets you ask questions about your data, and displays answers in formats that make sense, either as a chart, dashboard, or detailed data table.

  Learning Resources

  • Metabase Documentation

  • Metabase Tutorials

• pgAdmin: An open-source, full-featured and web-based PostgreSQL management tool.

  Learning Resources

  • pgAdmin Documentation

• Postgres: An open-source, object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

  Learning Resources

  • Postgres Official Documentation

  • Getting Started with PostgreSQL Tutorial

• Star Schema: A relational database schema for representing multidimensional data.

  Learning Resources

  • Star Schema on Wikipedia

  • Star Schema on GeeksforGeeks

• Superset: An open-source data exploration and visualization platform designed to be intuitive and interactive.

  Learning Resources

  • Superset Documentation

  • A Superset YouTube Tutorial