Data Warehouse - The Ultimate Guide

Explore data warehousing principles and concepts through practical demonstrations and optional hands-on assignments, reinforcing theory with tool-agnostic examples and quick, accessible setup.

Set up the Airbyte data integration tool to connect sources like Facebook, PostgreSQL, Google Sheets, and MySQL, then transform and load data to build a data warehouse.

Explore the three data warehouse layers—staging, core, and data marts—driven by ETL from sources, transformations, cleansing, and access for reports, data mining, and predictive analytics.

Discover why the staging area in a data warehouse speeds up ETL by quickly extracting data from sources, staging it in tables, and enabling safe transformations.

See how data marts sit atop the core data warehouse as an additional layer, modeling data dimensionally with facts and dimensions for specific use cases.

Relational databases store data in tables and use SQL to query them, using primary and foreign keys and joins. This enables OLAP and data warehouses with star schemas.

In-memory databases deliver high query performance for analytical use cases and data marts by storing data in memory and using columnar storage and parallel query plans.

Explore olap cubes, a multidimensional molap approach that pre-calculates values for fast analytics in data marts, using dimensions, hierarchies, and mdx to drill, slice, and dice data.

Explore operational data storage and distinguish the ODS from a data warehouse, emphasizing current state data, near real-time updates, and operational decision making.

Dimensional modeling aims for fast data retrieval by structuring data into dimension and fact tables, reducing duplication and boosting performance and usability for OLAP reporting.

Explore dimensions in data warehousing, learning how they provide context to facts in a star schema through slicing and dicing, with dimension tables, keys, and snowflake concepts.

Explore the star schema as the core data warehouse model, linking a fact table to dimension tables via primary and foreign keys to enable one-to-many relationships and efficient queries.

Demonstrates building a snowflaked dimension by creating a distinct category table from the products data using SQL, including mapping, importing a CSV, and assigning category IDs.

Learn to distinguish fully additive, semi-additive, and non-additive facts in data warehousing, with examples from sales, balances, and prices, and store underlying values to enable accurate Power BI analytics.

This lecture explains year-to-date calculations in data warehouses and why storing them breaks the defined grain; compute YTD in Power BI and Tableau using daily values.

Explore the three types of fact tables—transactional, periodic snapshot, and accumulating snapshot—and learn what makes each type unique in data warehouses.

Explore periodic snapshot fact tables, aggregating events into a standard period to produce one row of measures per grain, with controlled growth, additive data, and flexible null or zero handling.

Compare the three fact table types—transactional, periodic snapshot, and accumulating snapshot—highlighting grain, end-of-period and transaction date dimensions, and how each measures performance and aggregates data.

Explore factless fact tables, where events are recorded with dimensional attributes but no metrics, and learn how to design, implement, and query them with date, region, and department filters.

Identify the business process and four key decisions for designing fact tables. Define a fine, atomic grain and identify relevant dimensions and facts for flexible analysis.

Understand the difference between natural and surrogate keys and how surrogate keys are created in ETL, then apply them to fact and dimension tables for better storage and joins.

Follow a case study to design fact tables in a data warehouse, analyzing shopping cart checkout, orders, and fulfillment for an e-commerce company to optimize profits.

Define the grain of the fact table using the atomic, transactional level where one row represents a single item order line, preserving dimensionality and analytical value.

Derive dimensions from the defined grain, including customers, products, promotions, dates, times, and websites from three systems; add a running-number primary key and foreign keys, opting for one date-time dimension.

Identify additive facts that comply with the grain, derive absolute discounts and profit, and design the final fact table with website, customer, product, and data dimensions.

Explore dimension tables in data warehousing, using surrogate keys, lookup tables, and left joins to link fact and product dimensions, enabling slicing and grouping by attributes.

Explore how to set up a date dimension in a data warehouse using SQL in PostgreSQL, including creating a table, primary key, index, and populating data for reuse across warehouses.

Replace nulls in dimension foreign keys with descriptive dummy values, such as no promo or 1900-01-01, to preserve referential integrity, support joins, and clear BI visuals.

Conformed dimensions enable drill across sales and costs by sharing attributes like date or region, allowing comparison of measures across multiple facts with different granularities.

Explains degenerate dimensions as dimension keys in the fact table with no associated dimension and no foreign key, enabling analysis and grouping by payment and order ids with dd suffix.

Explore junk dimensions in dimensional modeling, transforming scattered flags and indicators from a fact table into a low-cardinality dimension to improve query performance and usability.

Explore slowly changing dimensions in data warehousing, learn why changes occur, and develop proactive strategies with business users and IT, based on Kimball's SCD types.

Understand type 1 overwrite for slowly changing dimensions, updating dimension attributes to the current state. See how this simple method preserves current values but eliminates history and may affect queries.

Learn how type 2 slowly changing dimensions preserve history by adding rows and using new foreign keys in the fact table, enabling accurate product counting via distinct product IDs.

Explore the ETL process: extract from data sources, transform and load into a data warehouse, using staging, core, and data mart layers and scheduled workflows.

Extract data from sources into the transient staging layer, then apply initial and delta loads to transform and move data into the core layer via SQL tables.

Extract initial data from source systems after coordinating with business users and IT responsibles, then move from staging layer to core layer, undergo ETL testing, and prepare for delta load.

Implement delta load after the initial load by incrementally loading new data using a delta column, filtering by the max delta value and moving data through staging to core layers.

Explore the load workflow in a data warehouse, from staging to core, using delta detection and insert or update operations to append data while preserving history with a current/deleted flag.

Install the Pentaho community edition, download and unzip the package, and run the Windows batch file to launch Spoon, ensuring Java is installed for PostgresQL in the next lecture.

Install PostgreSQL and pgAdmin on Windows using the 64-bit installer from postgres.org, set the superuser password, and use the default port 5432; then open pgAdmin to manage databases.

Learn a quick pentaho data integration (pdi) walkthrough: build a simple etl transformation, read csv data, uppercase the status, and set up a staging workflow for the data warehouse.

Set up a workflow to move product data from the source into the staging schema of a data warehouse by creating a dim product table with identity surrogate key.

Set up a data ingestion workflow in pentaho to read from a source table, configure a PostgreSQL connection, and load data into the staging dim_product table with a surrogate key.

Transform data to create a consolidated view for analysis by integrating data from multiple systems, standardizing data types and column names, reshaping for analytical needs, and loading into core layer.

Perform deduplication to unify the product dimension across stores, filter and clean data, map values, standardize units, and introduce surrogate keys in an etl workflow.

Master advanced data transformations by joining tables to add foreign keys for the product dimension and surrogate keys, then splitting, aggregating, and deriving values like tax amount in ETL workflows.

Implement the ETL plan by extracting from the product table, applying delta logic, and cleaning staging data. Load transformed data into the core schema after adding a surrogate key.

Set up the core schema and staging table in PG admin and configure a Spoon ETL workflow to load dim_product into staging before moving to the core layer.

Demonstrates an update insert upsert strategy for the dim product table, configures upsert logic, runs the ETL, and verifies results in the core layer.

Schedule ETL jobs either in the ETL tool with an enterprise version or via external schedulers, aligning business requirements, ETL duration, and initial vs delta loads on productive systems.

Evaluate your data integration needs to choose the right ETL tool by defining requirements, considering connectors and cost, reviewing tools, and using a weighted scoring matrix before testing.

Load source sales data into public schema. Design a fact table with date and product keys, create junk and degenerate dimensions for incremental load and compute total_cost, total_price, and profit.

Set up the staging sales fact table, the core sales table, and the payment dimension in pgAdmin, with a surrogate-key sequence, and prepare the ETL workflow for the data warehouse.

Set up staging for the sales fact by creating a delta-based transformation, using last load date to perform incremental loads from public.sales, and prepare core-layer integration.

Connect the SetLastLoad and GetLastLoad transformations in the staging job, configure sales variables, and fix data type mismatches to ensure the staging table loads numeric cost data.