Design and Build a Data Warehouse

Build a layered data warehouse with Medallion Architecture and star schema.

Introduction

30 Second Summary

Every company collects data from dozens of different systems. Customer records live in one tool, product catalogs in another, and sales transactions in a third. The challenge is turning that scattered, messy data into a single source of truth that business leaders can query in seconds.

In this project, you will build a complete enterprise data warehouse for a fictional retail company. You will take raw data from two source systems, cleanse it, and model it into an analytics-ready star schema that supports fast business queries.

What You'll Build

A layered PostgreSQL data warehouse that ingests raw data into a Bronze layer, cleanses it in a Silver layer, and transforms it into a Kimball dimensional model in a Gold layer.

By the end of this project, you'll have:

  • A PostgreSQL database with three schemas representing the Medallion Architecture layers (Bronze, Silver, Gold).
  • SQL scripts for the full ETL pipeline covering ingestion, cleansing, and transformation into dimensional models.
  • A visual ERD in dbdiagram.io showing your star schema with a central fact table and supporting dimension tables.
  • Secret Mission: Add SCD Type 2 historical tracking to dim_customers so you can answer "what segment was this customer in last quarter?"

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, INSERT, CREATE TABLE, JOIN). No prior data warehousing or dimensional modeling experience is needed.

Install PostgreSQL and Create the Warehouse Database

Every data warehouse needs a database engine to store and query data across its layers. In this project, you will use PostgreSQL as the engine powering your retail data warehouse.

Before you can build any tables or write any SQL, you need PostgreSQL installed on your machine, a database created, and the layered schema structure in place. This step gets all of that ready.

In this step, get ready to:
  • Install PostgreSQL 18 and pgAdmin on your machine.
  • Create a new database called retail_dwh.
  • Set up three schemas representing the Medallion Architecture layers.
Download and install PostgreSQL 18

PostgreSQL is the open-source relational database that will store every layer of your warehouse. The EDB interactive installer bundles PostgreSQL with pgAdmin, so you get both tools in a single download.

  • Open your browser and navigate to the PostgreSQL downloads page:
  • https://www.postgresql.org/download/windows/
  • Click the link to download the EDB interactive installer for PostgreSQL 18.
  • Run the installer once the download completes.

What will the installer ask me?

The installer walks you through several prompts. Here is what to expect:

  • Installation directory: keep the default.
  • Components: leave all selected (PostgreSQL Server, pgAdmin 4, StackBuilder, Command Line Tools).
  • Data directory: keep the default.
  • Password: set a password for the postgres superuser. Remember this password.
  • Port: keep the default 5432.
  • Locale: keep the default.
  • Click **Next** through each screen, entering your password when prompted, then click **Finish** when the installation completes.
  • When StackBuilder launches at the end, close it. You do not need any additional packages.

Check that PostgreSQL installed correctly by running the version check command in your terminal.

  • Open a terminal by pressing Cmd+Space (macOS) or the Windows key (Windows) and typing Terminal or cmd, then pressing Enter.
  • Check your PostgreSQL version by running this command:
psql --version

✔️ I see version 18 or higher

You should see output like psql (PostgreSQL) 18.x. PostgreSQL is ready to go.

ⓧ I see an older version

You have a previous PostgreSQL version installed. You can install version 18 alongside it using the EDB installer.

  • Download and run the EDB installer for PostgreSQL 18 from the link above.
  • During installation, keep the default port 5432 if prompted. If your existing install already uses 5432, the installer will suggest a different port. Accept the suggestion and note the port number for later use.
  • After installation, close and reopen your terminal, then run psql --version again to confirm version 18 is now active.

ⓧ Command not found

PostgreSQL is not installed yet or is not in your system PATH.

  • Download and run the EDB installer for PostgreSQL 18 from the link above.
  • Follow the installer prompts, setting a password for the postgres superuser and keeping the default port 5432.
  • Close and reopen your terminal after installation, then run psql --version again.

Still seeing command not found after installing?

On Windows, the EDB installer adds PostgreSQL to your PATH automatically, but only for new terminal sessions. Close all terminal windows and open a fresh one.

On macOS, you may need to add the PostgreSQL bin directory to your PATH manually. The default location is /Library/PostgreSQL/18/bin.

Help me fix my PostgreSQL PATH

Open pgAdmin and create the warehouse database

pgAdmin is the graphical interface for managing PostgreSQL. It was installed alongside PostgreSQL by the EDB installer.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type pgAdmin and press Enter to open it.
  • When pgAdmin opens, it may ask you to set a master password for the application. Choose a password and click OK.
  • In the left sidebar (the Object Explorer), expand Servers.
  • Click on PostgreSQL 18. Enter the postgres superuser password you set during installation when prompted.

You are now connected to your local PostgreSQL server. Next, create the warehouse database.

  • Right-click on Databases in the Object Explorer.
  • Select Create > Database....
  • In the Database field on the General tab, type retail_dwh.
  • Leave all other fields at their defaults.
  • Click Save.

You should see retail_dwh appear under the Databases node in the Object Explorer.

Don't see the new database?

Right-click on Databases and select Refresh. The retail_dwh database should now appear in the list.

Help me troubleshoot creating a database in pgAdmin

Create the Medallion Architecture schemas

The Medallion Architecture organizes data into three layers: Bronze (raw), Silver (cleansed), and Gold (business-ready). Each layer lives in its own schema inside the database.

  • In the Object Explorer, click on retail_dwh to select it.
  • Open the Query Tool by clicking Tools in the top menu bar, then selecting Query Tool.
  • Create the three schemas by pasting and running this SQL:
-- Create the Medallion Architecture layers
CREATE SCHEMA bronze;
CREATE SCHEMA silver;
CREATE SCHEMA gold;
  • Click the Execute button (the play icon) in the Query Tool toolbar, or press F5.

What does this code do?

Each CREATE SCHEMA statement creates a named namespace inside your database. Schemas let you organize tables into logical groups without creating separate databases.

The bronze schema will hold raw data exactly as it arrives from source systems. The silver schema will hold cleansed, typed data. The gold schema will hold the final star schema tables ready for analytics.

  • Verify the schemas were created by running this query:
SELECT schema_name FROM information_schema.schemata WHERE schema_name IN ('bronze', 'silver', 'gold');

You should see three rows listed: bronze, silver, and gold.

Seeing an error about schema already exists?

If you ran the CREATE SCHEMA statements more than once, PostgreSQL will raise an error because the schemas already exist. This is safe to ignore. Run the verification query to confirm all three schemas are present.

Help me fix my schema creation error

Why three layers instead of one?

Separating data into layers means each stage of your pipeline has a clear purpose. Bronze preserves the original data for auditing. Silver fixes quality issues so downstream consumers get clean data. Gold shapes data into a model optimized for fast queries.

This separation is the standard pattern used in modern data platforms. It makes debugging easier because you can always trace a problem back to the layer where it was introduced.

✔️ Awesome, I've got everything!

Great. Make sure the Query Tool is still open. You will use it throughout the rest of this project.

ⓧ I'd like to double check the full code

Here is the complete SQL you should have run in this step:

-- Create the Medallion Architecture layers
CREATE SCHEMA bronze;
CREATE SCHEMA silver;
CREATE SCHEMA gold;

Your database foundation is ready. Next up, you will design the star schema visually in dbdiagram.io before building it in SQL.

Design the Star Schema in dbdiagram.io

Your database has three empty layers waiting to be filled. Before jumping into SQL, you need a visual blueprint of the Gold layer's structure.

In this step, you will design a star schema using dbdiagram.io. Star schemas place a central fact table (recording business events) surrounded by dimension tables (describing the who, what, and when). Designing visually first means you can see how everything connects before writing a single line of SQL.

In this step, get ready to:
  • Open dbdiagram.io and set up a blank canvas for your schema design.
  • Write DBML code to define one fact table and three dimension tables with foreign key relationships.
  • Export the auto-generated ERD diagram as a PNG for your portfolio.
Open dbdiagram.io in your browser

dbdiagram.io is a free, browser-based tool that lets you define database diagrams using a code-like syntax called DBML (Database Markup Language). No account is needed for the free tier.

  • Open a new browser tab and navigate to https://dbdiagram.io.
  • You will see a code editor on the left pane and a diagram visualizer on the right.
  • Select all the default example code in the left editor and delete it so you have a blank canvas.

What is DBML?

DBML (Database Markup Language) is a simple language designed specifically for defining database structures. You type table definitions and relationships in the code editor, and dbdiagram.io renders an entity-relationship diagram (ERD) automatically on the right side as you type.

Write the star schema in DBML

Your star schema follows the Kimball dimensional modeling approach. The central fact table records each sales transaction, while the dimension tables describe the customer, product, and date context around each sale.

  • Paste the following DBML into the empty code editor on the left:
Table gold.fact_sales {
  sale_id integer [pk, increment]
  customer_key integer [not null, ref: > gold.dim_customers.customer_key]
  product_key integer [not null, ref: > gold.dim_products.product_key]
  date_key integer [not null, ref: > gold.dim_dates.date_key]
  quantity integer [not null]
  unit_price decimal(10,2) [not null]
  total_amount decimal(10,2) [not null]
  discount decimal(5,2)
}

Table gold.dim_customers {
  customer_key integer [pk, increment]
  customer_id varchar(20) [not null, unique]
  first_name varchar(50)
  last_name varchar(50)
  email varchar(100)
  phone varchar(30)
  city varchar(50)
  country varchar(50)
  segment varchar(30)
}

Table gold.dim_products {
  product_key integer [pk, increment]
  product_id varchar(20) [not null, unique]
  product_name varchar(100) [not null]
  category varchar(50)
  subcategory varchar(50)
  brand varchar(50)
  unit_cost decimal(10,2)
}

Table gold.dim_dates {
  date_key integer [pk]
  full_date date [not null, unique]
  day_of_week integer
  day_name varchar(10)
  month integer
  month_name varchar(10)
  quarter integer
  year integer
  is_weekend boolean
}

What does this DBML define?

This code defines four tables in the Gold layer:

  • gold.fact_sales is the fact table. It records each sale with surrogate keys (customer_key, product_key, date_key) that link to dimension tables, plus the measurable values (quantity, price, discount).
  • gold.dim_customers describes who made the purchase.
  • gold.dim_products describes what was purchased.
  • gold.dim_dates describes when the purchase happened, with pre-calculated fields like quarter and day name for easy analytics.

The ref: > syntax creates a many-to-one relationship from the fact table to each dimension. This is what draws the connecting lines in the diagram.

Look at the right side of the screen. The ERD should render automatically, showing gold.fact_sales in the center with relationship lines connecting to each dimension table.

Don't see a diagram on the right?

  • Check for red underlines in the code editor. A typo in a table name or a missing closing brace will prevent the diagram from rendering.
  • Make sure you deleted all the default example code before pasting. Leftover code can conflict with your table definitions.
  • Try refreshing the page and pasting the DBML again.

Help me fix my dbdiagram.io DBML code that is not rendering a diagram.

✔️ Awesome, I've got everything!

Great. Make sure your diagram shows four tables with connecting relationship lines before moving on.

ⓧ I'd like to double check the full code

Here is the complete DBML you should have in the editor:

Table gold.fact_sales {
  sale_id integer [pk, increment]
  customer_key integer [not null, ref: > gold.dim_customers.customer_key]
  product_key integer [not null, ref: > gold.dim_products.product_key]
  date_key integer [not null, ref: > gold.dim_dates.date_key]
  quantity integer [not null]
  unit_price decimal(10,2) [not null]
  total_amount decimal(10,2) [not null]
  discount decimal(5,2)
}

Table gold.dim_customers {
  customer_key integer [pk, increment]
  customer_id varchar(20) [not null, unique]
  first_name varchar(50)
  last_name varchar(50)
  email varchar(100)
  phone varchar(30)
  city varchar(50)
  country varchar(50)
  segment varchar(30)
}

Table gold.dim_products {
  product_key integer [pk, increment]
  product_id varchar(20) [not null, unique]
  product_name varchar(100) [not null]
  category varchar(50)
  subcategory varchar(50)
  brand varchar(50)
  unit_cost decimal(10,2)
}

Table gold.dim_dates {
  date_key integer [pk]
  full_date date [not null, unique]
  day_of_week integer
  day_name varchar(10)
  month integer
  month_name varchar(10)
  quarter integer
  year integer
  is_weekend boolean
}
Review the ERD and export as PNG

The diagram visualizer on the right now shows your complete star schema. The fact_sales table sits at the center with relationship lines pointing outward to each dimension. This visual is your architecture blueprint for the SQL you will write in the next steps.

  • Confirm that gold.fact_sales shows three outgoing relationship lines connecting to gold.dim_customers, gold.dim_products, and gold.dim_dates.
  • Hover over the download button in the top-right corner of the diagram view.
  • Select PNG from the dropdown options.
  • Save the downloaded PNG file somewhere you can find it later.

Why design before building?

Designing the star schema visually first lets you validate relationships and spot missing columns before committing any SQL to the database. In real enterprise teams, ERD reviews happen before any code is written. This diagram also serves as documentation you can present in interviews or share with stakeholders.

Export as PDF for higher quality

If your PNG looks fuzzy, try exporting as PDF instead. PDFs remain sharp at any zoom level, which is better for portfolio presentations.

Your architecture blueprint is complete. Next up, you will build the Bronze layer by creating tables and loading raw data from two source systems into your retail_dwh database.

Build the Bronze Layer

With the Medallion Architecture schemas in place and a star schema blueprint exported from dbdiagram.io, you have the destination planned. Now you need to bring in the raw material.

The Bronze layer is where raw data lands from source systems without any transformation. In this step, you'll create tables that mirror two source systems (a CRM and an ERP) and load them with sample data that includes real-world quality issues like inconsistent casing, mixed date formats, and duplicates.

In this step, get ready to:
  • Create Bronze layer tables that mirror the raw source system format.
  • Load sample data with intentional quality issues from two source systems.
  • Verify the ingestion with row counts across all three tables.
Create the Bronze layer tables

Bronze tables mirror the source system format exactly. Every column is stored as VARCHAR regardless of what the data actually represents. This preserves the raw data without any assumptions about types or formatting.

  • In the Query Tool in pgAdmin (connected to retail_dwh), create the Bronze layer tables by running the following SQL:
-- CRM source: customer data
CREATE TABLE bronze.crm_customers (
    customer_id VARCHAR(20),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(30),
    city VARCHAR(50),
    country VARCHAR(50),
    segment VARCHAR(30),
    create_date VARCHAR(20)
);

-- ERP source: product and sales data
CREATE TABLE bronze.erp_products (
    product_id VARCHAR(20),
    product_name VARCHAR(100),
    category VARCHAR(50),
    subcategory VARCHAR(50),
    brand VARCHAR(50),
    unit_cost VARCHAR(20)
);

CREATE TABLE bronze.erp_sales (
    sale_id VARCHAR(20),
    customer_id VARCHAR(20),
    product_id VARCHAR(20),
    sale_date VARCHAR(20),
    quantity VARCHAR(10),
    unit_price VARCHAR(20),
    discount VARCHAR(10)
);

What does this code do?

You're creating three tables across two fictional source systems:

  • bronze.crm_customers holds customer data from the CRM system (names, contact info, segment).
  • bronze.erp_products holds product catalog data from the ERP system (names, categories, cost).
  • bronze.erp_sales holds transactional sales data from the ERP system (who bought what, when, for how much).

Every column is VARCHAR because the Bronze layer stores data exactly as it arrives. Type casting happens later in the Silver layer.

Why store everything as VARCHAR?

Real source systems often send data in unexpected formats. A date column might contain 2024-01-15 in one row and 15/01/2024 in the next.

If you enforce a DATE type at ingestion, those rows would fail to load and you'd lose data. The Bronze layer accepts everything, and the Silver layer handles the cleanup.

  • Click the Execute/Refresh button (play icon) in the toolbar or press F5 to run the query.
  • You should see a success message in the Messages tab at the bottom of the Query Tool.

Seeing a 'relation already exists' error?

This means you've already run the script once. You can drop the tables first and try again by adding DROP TABLE IF EXISTS bronze.crm_customers, bronze.erp_products, bronze.erp_sales; before the CREATE statements, then re-running.

Help me fix this relation already exists error.

Load raw data with intentional quality issues

Real source data is messy. Customer names arrive in mixed casing, dates use different formats depending on the system's locale, and duplicates slip through when integrations re-send records.

  • Clear the Query Tool editor, then insert the CRM customer data by running this SQL:
-- CRM customers with intentional quality issues
INSERT INTO bronze.crm_customers VALUES
('C001', 'John', 'Smith', 'john.smith@email.com', '555-0101', 'New York', 'US', 'Enterprise', '2023-01-15'),
('C002', 'jane', 'DOE', 'JANE.DOE@EMAIL.COM', '555-0102', 'london', 'uk', 'SMB', '2023-02-20'),
('C003', 'Bob', 'Johnson', NULL, '555-0103', 'Sydney', 'AU', 'Enterprise', '2023-03-10'),
('C004', 'Alice', 'Williams', 'alice.w@email.com', '', 'Toronto', 'CA', 'Mid-Market', '15/04/2023'),
('C005', 'Charlie', 'Brown', 'charlie.b@email.com', '555-0105', 'new york', 'US', 'enterprise', '2023-05-01'),
('C001', 'John', 'Smith', 'john.smith@email.com', '555-0101', 'New York', 'US', 'Enterprise', '2023-01-15');

What quality issues are hiding in this data?

  • C002 has lowercase first name (jane), uppercase last name (DOE), and uppercase email.
  • C003 has a NULL email. C004 has an empty string for phone.
  • C004 uses a different date format (15/04/2023 instead of 2023-04-15).
  • C005 has inconsistent casing in city and segment. The last row is a duplicate of C001.

You'll clean all of these in the Silver layer. For now, the Bronze layer accepts everything as-is.

  • Run the query. You should see a success message confirming 6 rows were inserted.
  • Clear the Query Tool editor, then insert the ERP product and sales data by running this SQL:
-- ERP products
INSERT INTO bronze.erp_products VALUES
('P001', 'Wireless Mouse', 'Electronics', 'Peripherals', 'TechBrand', '15.00'),
('P002', 'Mechanical Keyboard', 'Electronics', 'Peripherals', 'KeyMaster', '45.50'),
('P003', 'USB-C Hub', 'Electronics', 'Accessories', 'TechBrand', '22.00'),
('P004', 'Standing Desk', 'Furniture', 'Desks', 'ErgoWorks', '299.99'),
('P005', 'Monitor Arm', 'Furniture', 'Accessories', 'ErgoWorks', '89.00');

-- ERP sales with mixed date formats
INSERT INTO bronze.erp_sales VALUES
('S001', 'C001', 'P001', '2024-01-15', '2', '25.99', '0.00'),
('S002', 'C002', 'P002', '2024-01-20', '1', '79.99', '0.10'),
('S003', 'C003', 'P004', '15/02/2024', '1', '499.99', '0.05'),
('S004', 'C001', 'P003', '2024-03-01', '3', '39.99', '0.00'),
('S005', 'C004', 'P005', '2024-03-15', '2', '129.99', '0.15'),
('S006', 'C005', 'P001', '2024-04-01', '5', '25.99', '0.00'),
('S007', 'C002', 'P003', '20/04/2024', '1', '39.99', '0.10'),
('S008', 'C003', 'P002', '2024-05-10', '2', '79.99', '0.00');

What does this data represent?

  • erp_products contains 5 products across Electronics and Furniture categories. Note that unit_cost is stored as a string (e.g. '15.00') because it arrived that way from the ERP system.
  • erp_sales contains 8 transactions. Rows S003 and S007 use DD/MM/YYYY date format while the rest use YYYY-MM-DD. Quantities and prices are also stored as strings.
  • Run the query. You should see success messages confirming 5 rows inserted into products and 8 rows inserted into sales.

Seeing a 'relation does not exist' error?

Make sure you ran the CREATE TABLE statements from the previous substep first. Also confirm that the Query Tool is connected to the retail_dwh database, not the default postgres database. Check the database name in the Query Tool tab header.

Help me fix this relation does not exist error in pgAdmin.

Verify the ingestion
  • Clear the Query Tool editor, then confirm row counts across all three Bronze tables by running this query:
SELECT COUNT(*) AS row_count, 'crm_customers' AS source FROM bronze.crm_customers
UNION ALL
SELECT COUNT(*), 'erp_products' FROM bronze.erp_products
UNION ALL
SELECT COUNT(*), 'erp_sales' FROM bronze.erp_sales;

What does this query do?

UNION ALL stacks the results of three separate COUNT(*) queries into a single result set. Each row shows the table name and how many records it contains. This is a quick way to confirm all data loaded successfully.

  • Run the query. You should see a three-row result:
  • crm_customers: 6 rows (including 1 duplicate).
  • erp_products: 5 rows.
  • erp_sales: 8 rows.

Row counts don't match?

If you see 0 rows in any table, the INSERT statements may not have run successfully. Re-run the INSERT statements from the previous substep. If you see more than expected, you may have run the INSERT twice. Drop and recreate the tables, then run both the CREATE and INSERT statements again.

Help me fix my row counts in the Bronze layer tables.

✔️ Awesome, I've got everything!

Great. Your Bronze layer is fully loaded with raw data from both source systems.

ⓧ I'd like to double check the full code

Here is the full SQL for creating the Bronze tables:

-- CRM source: customer data
CREATE TABLE bronze.crm_customers (
    customer_id VARCHAR(20),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(30),
    city VARCHAR(50),
    country VARCHAR(50),
    segment VARCHAR(30),
    create_date VARCHAR(20)
);

-- ERP source: product and sales data
CREATE TABLE bronze.erp_products (
    product_id VARCHAR(20),
    product_name VARCHAR(100),
    category VARCHAR(50),
    subcategory VARCHAR(50),
    brand VARCHAR(50),
    unit_cost VARCHAR(20)
);

CREATE TABLE bronze.erp_sales (
    sale_id VARCHAR(20),
    customer_id VARCHAR(20),
    product_id VARCHAR(20),
    sale_date VARCHAR(20),
    quantity VARCHAR(10),
    unit_price VARCHAR(20),
    discount VARCHAR(10)
);

And here is the full SQL for loading the raw data:

-- CRM customers with intentional quality issues
INSERT INTO bronze.crm_customers VALUES
('C001', 'John', 'Smith', 'john.smith@email.com', '555-0101', 'New York', 'US', 'Enterprise', '2023-01-15'),
('C002', 'jane', 'DOE', 'JANE.DOE@EMAIL.COM', '555-0102', 'london', 'uk', 'SMB', '2023-02-20'),
('C003', 'Bob', 'Johnson', NULL, '555-0103', 'Sydney', 'AU', 'Enterprise', '2023-03-10'),
('C004', 'Alice', 'Williams', 'alice.w@email.com', '', 'Toronto', 'CA', 'Mid-Market', '15/04/2023'),
('C005', 'Charlie', 'Brown', 'charlie.b@email.com', '555-0105', 'new york', 'US', 'enterprise', '2023-05-01'),
('C001', 'John', 'Smith', 'john.smith@email.com', '555-0101', 'New York', 'US', 'Enterprise', '2023-01-15');

-- ERP products
INSERT INTO bronze.erp_products VALUES
('P001', 'Wireless Mouse', 'Electronics', 'Peripherals', 'TechBrand', '15.00'),
('P002', 'Mechanical Keyboard', 'Electronics', 'Peripherals', 'KeyMaster', '45.50'),
('P003', 'USB-C Hub', 'Electronics', 'Accessories', 'TechBrand', '22.00'),
('P004', 'Standing Desk', 'Furniture', 'Desks', 'ErgoWorks', '299.99'),
('P005', 'Monitor Arm', 'Furniture', 'Accessories', 'ErgoWorks', '89.00');

-- ERP sales with mixed date formats
INSERT INTO bronze.erp_sales VALUES
('S001', 'C001', 'P001', '2024-01-15', '2', '25.99', '0.00'),
('S002', 'C002', 'P002', '2024-01-20', '1', '79.99', '0.10'),
('S003', 'C003', 'P004', '15/02/2024', '1', '499.99', '0.05'),
('S004', 'C001', 'P003', '2024-03-01', '3', '39.99', '0.00'),
('S005', 'C004', 'P005', '2024-03-15', '2', '129.99', '0.15'),
('S006', 'C005', 'P001', '2024-04-01', '5', '25.99', '0.00'),
('S007', 'C002', 'P003', '20/04/2024', '1', '39.99', '0.10'),
('S008', 'C003', 'P002', '2024-05-10', '2', '79.99', '0.00');

Your Bronze layer is loaded with raw, messy data from two source systems. Next up, you'll build the Silver layer where the real cleanup happens. Duplicates, inconsistent casing, and mixed date formats will all get resolved.

Build the Silver Layer

Your Bronze layer now holds raw data exactly as it arrived from the CRM and ERP systems. The problem? That data is full of inconsistencies.

Names have mixed casing, dates use different formats, empty strings pretend to be phone numbers, and one customer appears twice. The Silver layer fixes all of this by cleansing, standardizing, and deduplicating the raw data into a form you can trust.

In this step, get ready to:
  • Deduplicate and standardize customer data from the CRM source.
  • Cleanse product and sales data with proper data types.
  • Validate that the cleansing transformations worked correctly.
Create the cleansed customer table

The CRM customer data has the worst quality issues: duplicate rows, inconsistent casing (lowercase names, uppercase emails), empty strings where NULLs should be, and mixed date formats. A single CREATE TABLE AS SELECT statement can fix all of these in one pass.

  • In the Query Tool in pgAdmin (connected to retail_dwh), clear any previous query and paste the following SQL:
CREATE TABLE silver.customers AS
SELECT DISTINCT ON (customer_id)
    customer_id,
    INITCAP(TRIM(first_name)) AS first_name,
    INITCAP(TRIM(last_name)) AS last_name,
    LOWER(TRIM(email)) AS email,
    CASE WHEN TRIM(phone) = '' THEN NULL ELSE phone END AS phone,
    INITCAP(TRIM(city)) AS city,
    UPPER(TRIM(country)) AS country,
    INITCAP(TRIM(segment)) AS segment,
    CASE
        WHEN create_date ~ '^\d{4}-\d{2}-\d{2}$' THEN create_date::DATE
        WHEN create_date ~ '^\d{2}/\d{2}/\d{4}$' THEN TO_DATE(create_date, 'DD/MM/YYYY')
        ELSE NULL
    END AS create_date
FROM bronze.crm_customers
ORDER BY customer_id, create_date;

What does this SQL do?

  • DISTINCT ON (customer_id) keeps only the first row per customer ID, eliminating the duplicate C001 record.
  • INITCAP() converts text to proper case (first letter uppercase, rest lowercase). This fixes names like 'jane' and 'DOE'.
  • LOWER() standardizes email addresses to lowercase. UPPER() standardizes country codes to uppercase.
  • The CASE on phone converts empty strings to proper NULLs.
  • The date CASE uses the ~ regex operator to detect the format, then casts to a proper DATE type using either ::DATE or TO_DATE().
  • Click the **Execute** button (or press **F5**) to run the query.
  • You should see SELECT 5 in the Messages tab, confirming 5 rows were inserted (the duplicate was removed).

Seeing an error about the regex operator?

Make sure you are connected to the retail_dwh database, not the default postgres database. The bronze schema only exists in retail_dwh.

Also check that the backslashes in the regex patterns (\d) were pasted correctly. Some text editors convert them during copy-paste.

help me debug a regex error in my Silver layer SQL

Create cleansed products and sales tables

The product and sales data has fewer issues than customers. The main problem is that all values are stored as VARCHAR in the Bronze layer. The Silver layer casts them to their proper types: decimals for prices, integers for quantities, and dates for timestamps.

  • Clear the Query Tool and paste the following SQL to create the cleansed products table:
CREATE TABLE silver.products AS
SELECT
    product_id,
    TRIM(product_name) AS product_name,
    TRIM(category) AS category,
    TRIM(subcategory) AS subcategory,
    TRIM(brand) AS brand,
    unit_cost::DECIMAL(10,2) AS unit_cost
FROM bronze.erp_products;

What does this query do?

This query trims whitespace from all text columns and casts unit_cost from VARCHAR to DECIMAL(10,2). The :: syntax is PostgreSQL's type-cast operator. Now the database can perform math on costs instead of treating them as strings.

  • Click **Execute** (or press **F5**) to run. You should see SELECT 5 in the Messages tab.
  • Now clear the Query Tool and paste the following SQL to create the cleansed sales table:
CREATE TABLE silver.sales AS
SELECT
    sale_id,
    customer_id,
    product_id,
    CASE
        WHEN sale_date ~ '^\d{4}-\d{2}-\d{2}$' THEN sale_date::DATE
        WHEN sale_date ~ '^\d{2}/\d{2}/\d{4}$' THEN TO_DATE(sale_date, 'DD/MM/YYYY')
        ELSE NULL
    END AS sale_date,
    quantity::INTEGER AS quantity,
    unit_price::DECIMAL(10,2) AS unit_price,
    discount::DECIMAL(5,2) AS discount
FROM bronze.erp_sales;

What does this query do?

The sales table uses the same date-parsing logic as the customer table. It detects whether the date string is in ISO format (YYYY-MM-DD) or European format (DD/MM/YYYY) and converts both to a proper DATE type.

Quantity, unit_price, and discount are cast from VARCHAR to their correct numeric types. This means the Gold layer can perform calculations (like quantity * unit_price) without manual conversion.

  • Click **Execute** (or press **F5**) to run. You should see SELECT 8 in the Messages tab, confirming all 8 sales rows were inserted.

Seeing a 'relation already exists' error?

If you ran a query twice, the table already exists from the first run. Drop it first by running DROP TABLE silver.products; or DROP TABLE silver.sales; and then re-run the CREATE TABLE statement.

help me fix a 'relation already exists' error in PostgreSQL

✔️ Awesome, I've got everything!

Great. Make sure both queries ran successfully before moving on to validation.

ⓧ I'd like to double check the full code

Here is the complete SQL for the customer cleansing (04_silver_customers.sql):

CREATE TABLE silver.customers AS
SELECT DISTINCT ON (customer_id)
    customer_id,
    INITCAP(TRIM(first_name)) AS first_name,
    INITCAP(TRIM(last_name)) AS last_name,
    LOWER(TRIM(email)) AS email,
    CASE WHEN TRIM(phone) = '' THEN NULL ELSE phone END AS phone,
    INITCAP(TRIM(city)) AS city,
    UPPER(TRIM(country)) AS country,
    INITCAP(TRIM(segment)) AS segment,
    CASE
        WHEN create_date ~ '^\d{4}-\d{2}-\d{2}$' THEN create_date::DATE
        WHEN create_date ~ '^\d{2}/\d{2}/\d{4}$' THEN TO_DATE(create_date, 'DD/MM/YYYY')
        ELSE NULL
    END AS create_date
FROM bronze.crm_customers
ORDER BY customer_id, create_date;

Here is the complete SQL for products and sales cleansing (05_silver_products_sales.sql):

CREATE TABLE silver.products AS
SELECT
    product_id,
    TRIM(product_name) AS product_name,
    TRIM(category) AS category,
    TRIM(subcategory) AS subcategory,
    TRIM(brand) AS brand,
    unit_cost::DECIMAL(10,2) AS unit_cost
FROM bronze.erp_products;

CREATE TABLE silver.sales AS
SELECT
    sale_id,
    customer_id,
    product_id,
    CASE
        WHEN sale_date ~ '^\d{4}-\d{2}-\d{2}$' THEN sale_date::DATE
        WHEN sale_date ~ '^\d{2}/\d{2}/\d{4}$' THEN TO_DATE(sale_date, 'DD/MM/YYYY')
        ELSE NULL
    END AS sale_date,
    quantity::INTEGER AS quantity,
    unit_price::DECIMAL(10,2) AS unit_price,
    discount::DECIMAL(5,2) AS discount
FROM bronze.erp_sales;
Validate the cleansing transformations

You need to confirm that deduplication removed the extra row and that standardization applied correctly. Let's run two quick checks.

  • Clear the Query Tool and run the following validation queries:
-- Check deduplication removed the duplicate C001
SELECT COUNT(*) FROM silver.customers;

-- Check standardization
SELECT customer_id, first_name, last_name, city, country, segment
FROM silver.customers
ORDER BY customer_id;

What should I see?

The first query should return 5 (down from 6 in bronze, because the duplicate C001 was removed).

The second query should show standardized values: names in proper case (Jane not jane), last names in proper case (Doe not DOE), cities capitalized (London not london), country codes in uppercase (UK not uk), and segments in proper case (Enterprise not enterprise).

  • Click **Execute** (or press **F5**) to run both queries.
  • Confirm you see 5 rows in the count result, and all values appear standardized in the second result set.

Still seeing 6 rows?

If the count shows 6, the deduplication did not apply. Drop and recreate the table. Make sure the ORDER BY customer_id, create_date clause is present at the end. DISTINCT ON requires ORDER BY to start with the same column specified in DISTINCT ON.

help me fix DISTINCT ON deduplication in PostgreSQL

Why does the Silver layer matter?

Without this cleansing step, the Gold layer would inherit all the quality issues. Imagine running a revenue report that counts 'enterprise' and 'Enterprise' as two different customer segments.

The Silver layer guarantees that downstream analytics are based on a single, consistent version of each record. This is the core principle of the Medallion Architecture: each layer adds trust.

Your Silver layer is solid. Every record is deduplicated, properly typed, and consistently formatted. Next up, you will transform this clean data into the star schema you designed earlier.

Build the Gold Layer

Your Silver layer now holds clean, typed data ready for the final transformation. The next goal is to build the Gold layer, where data is structured specifically for fast, readable analytics.

The Gold layer follows the Kimball star schema pattern you designed earlier in dbdiagram.io. You will create dimension tables that describe your business entities and a central fact table that records measurable sales events.

In this step, get ready to:
  • Create a date dimension table using PostgreSQL's GENERATE_SERIES function.
  • Build customer and product dimension tables with surrogate keys.
  • Create a fact_sales table that joins Silver layer data with dimension surrogate keys.
Create the date dimension table

Date dimensions are a standard pattern in dimensional modeling. Instead of storing raw dates in the fact table, you generate a dedicated table with pre-calculated attributes like day of week, quarter, and weekend flags.

This makes analytical queries faster and simpler because you can filter and group by any date attribute without recalculating it every time.

  • In the pgAdmin Query Tool for your retail_dwh database, run the following SQL to generate a complete date dimension for 2024:
-- Generate a date dimension with one row per day in 2024
CREATE TABLE gold.dim_dates AS
SELECT
    TO_CHAR(d, 'YYYYMMDD')::INTEGER AS date_key,
    d AS full_date,
    EXTRACT(DOW FROM d)::INTEGER AS day_of_week,
    TO_CHAR(d, 'Day') AS day_name,
    EXTRACT(MONTH FROM d)::INTEGER AS month,
    TO_CHAR(d, 'Month') AS month_name,
    EXTRACT(QUARTER FROM d)::INTEGER AS quarter,
    EXTRACT(YEAR FROM d)::INTEGER AS year,
    CASE WHEN EXTRACT(DOW FROM d) IN (0, 6) THEN TRUE ELSE FALSE END AS is_weekend
FROM GENERATE_SERIES('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day'::INTERVAL) AS d;

-- Set the primary key after table creation
ALTER TABLE gold.dim_dates ADD PRIMARY KEY (date_key);

What does this code do?

GENERATE_SERIES creates one row for every day between January 1 and December 31, 2024. Each row gets a set of pre-calculated attributes derived from that date.

  • date_key is an integer like 20240115 that the fact table uses as a foreign key.
  • EXTRACT pulls numeric values (month, quarter, year) while TO_CHAR produces human-readable names.
  • is_weekend flags Saturdays (6) and Sundays (0) so analysts can filter business days instantly.
  • Verify the date dimension was created by running:
SELECT COUNT(*) FROM gold.dim_dates;

You should see 366 rows (2024 is a leap year).

Seeing an error about the table already existing?

If you ran the query twice, the table already exists from the first run. Drop it first with DROP TABLE gold.dim_dates; then re-run the CREATE statement.

If you see a syntax error, make sure you included the ::DATE cast on both date strings in GENERATE_SERIES.

Still stuck? Help me debug my dim_dates table creation in PostgreSQL.

Create customer and product dimensions with surrogate keys

Surrogate keys are auto-generated integers that replace the business keys from source systems. They protect the warehouse from upstream changes to source identifiers.

PostgreSQL's SERIAL type auto-creates a sequence that increments with each new row. This gives every customer and product a stable warehouse-internal key.

  • Create the dim_customers dimension table and populate it from the Silver layer by running:
-- Customer dimension with surrogate key
CREATE TABLE gold.dim_customers (
    customer_key SERIAL PRIMARY KEY,
    customer_id VARCHAR(20) NOT NULL UNIQUE,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(30),
    city VARCHAR(50),
    country VARCHAR(50),
    segment VARCHAR(30)
);

-- Load cleansed customers from Silver layer
INSERT INTO gold.dim_customers (customer_id, first_name, last_name, email, phone, city, country, segment)
SELECT customer_id, first_name, last_name, email, phone, city, country, segment
FROM silver.customers;

What does this code do?

  • customer_key SERIAL PRIMARY KEY generates a new integer (1, 2, 3...) for each row. This is the surrogate key that fact_sales will reference.
  • customer_id is marked UNIQUE so the original business key is preserved for traceability back to the CRM source system.
  • The INSERT pulls only the columns needed for analytics from silver.customers. No raw Bronze data touches this layer.
  • Verify the customer dimension by running:
SELECT * FROM gold.dim_customers;

You should see 5 rows, each with a customer_key value from 1 to 5.

Seeing 0 rows in dim_customers?

The INSERT depends on silver.customers existing. Confirm it has data by running SELECT COUNT(*) FROM silver.customers; which should return 5.

If silver.customers is empty, go back and re-run the Silver layer creation query from the previous step.

Need help? Help me debug why my dim_customers table is empty.

  • Now create the dim_products dimension table and populate it by running:
-- Product dimension with surrogate key
CREATE TABLE gold.dim_products (
    product_key SERIAL PRIMARY KEY,
    product_id VARCHAR(20) NOT NULL UNIQUE,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    subcategory VARCHAR(50),
    brand VARCHAR(50),
    unit_cost DECIMAL(10,2)
);

-- Load cleansed products from Silver layer
INSERT INTO gold.dim_products (product_id, product_name, category, subcategory, brand, unit_cost)
SELECT product_id, product_name, category, subcategory, brand, unit_cost
FROM silver.products;

What does this code do?

  • product_key SERIAL PRIMARY KEY creates the surrogate key for products, following the same pattern as dim_customers.
  • product_name is marked NOT NULL because every product must have a name for reporting.
  • unit_cost stays as DECIMAL(10,2) to preserve the precision established in the Silver layer.
  • Verify the product dimension by running:
SELECT * FROM gold.dim_products;

You should see 5 rows with product_key values from 1 to 5.

Table already exists error?

If you re-ran the query, drop the table first with DROP TABLE gold.dim_products; then re-run the CREATE and INSERT statements.

Make sure you are running queries against the retail_dwh database. Check the database name in the Query Tool tab header.

Still stuck? Help me fix my dim_products table creation.

Create the fact table and validate the star schema

The fact table is the center of your star schema. It stores measurable events (sales transactions) and connects to each dimension through foreign key references to the surrogate keys you just created.

The total_amount column is a derived metric, calculated at load time so every analyst gets the same number without re-deriving it.

  • Create the fact_sales table and populate it by joining Silver layer sales with your dimension tables:
-- Central fact table with foreign keys to all dimensions
CREATE TABLE gold.fact_sales (
    sale_id SERIAL PRIMARY KEY,
    customer_key INTEGER NOT NULL REFERENCES gold.dim_customers(customer_key),
    product_key INTEGER NOT NULL REFERENCES gold.dim_products(product_key),
    date_key INTEGER NOT NULL REFERENCES gold.dim_dates(date_key),
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    discount DECIMAL(5,2)
);

-- Load facts by joining Silver sales to dimension surrogate keys
INSERT INTO gold.fact_sales (customer_key, product_key, date_key, quantity, unit_price, total_amount, discount)
SELECT
    dc.customer_key,
    dp.product_key,
    TO_CHAR(s.sale_date, 'YYYYMMDD')::INTEGER AS date_key,
    s.quantity,
    s.unit_price,
    ROUND(s.quantity * s.unit_price * (1 - s.discount), 2) AS total_amount,
    s.discount
FROM silver.sales s
JOIN gold.dim_customers dc ON s.customer_id = dc.customer_id
JOIN gold.dim_products dp ON s.product_id = dp.product_id;

What does this code do?

  • The REFERENCES constraints enforce referential integrity. PostgreSQL will reject any insert where a key does not exist in the referenced dimension.
  • The INSERT joins silver.sales to both dimension tables on the business key (customer_id, product_id) and pulls back the surrogate key (customer_key, product_key).
  • TO_CHAR(s.sale_date, 'YYYYMMDD')::INTEGER converts each sale date into the matching date_key format from dim_dates.
  • ROUND(s.quantity * s.unit_price * (1 - s.discount), 2) calculates the final revenue per line item, applying the discount.
  • Verify the fact table loaded correctly by running:
SELECT COUNT(*) FROM gold.fact_sales;

You should see 8 rows, one for each sale from the Silver layer.

Seeing fewer than 8 rows or a foreign key error?

A foreign key violation means a sale references a customer or product that does not exist in the dimension tables. Confirm dim_customers has 5 rows and dim_products has 5 rows.

If you see 0 rows, check that silver.sales has data. Run SELECT COUNT(*) FROM silver.sales; which should return 8.

Need help? Help me debug my fact_sales INSERT that is failing with a foreign key error.

Now for the real payoff. Run an analytical query that proves the star schema supports fast, readable business intelligence:

  • Run the following analytics query to see revenue broken down by customer segment, product category, and quarter:
-- Analytical query demonstrating star schema power
SELECT
    dc.segment AS customer_segment,
    dp.category AS product_category,
    dd.quarter,
    SUM(fs.total_amount) AS revenue,
    COUNT(*) AS num_sales
FROM gold.fact_sales fs
JOIN gold.dim_customers dc ON fs.customer_key = dc.customer_key
JOIN gold.dim_products dp ON fs.product_key = dp.product_key
JOIN gold.dim_dates dd ON fs.date_key = dd.date_key
GROUP BY dc.segment, dp.category, dd.quarter
ORDER BY revenue DESC;

What does this query do?

This query joins the fact table to all three dimensions and aggregates revenue. Notice how readable it is. You reference dc.segment and dp.category directly instead of writing subqueries or complex CASE statements.

This is the power of dimensional modeling. The star schema structure makes analytical queries intuitive to write and fast to execute.

You should see a results table showing revenue grouped by customer segment (Enterprise, Mid-Market, Smb), product category (Electronics, Furniture), and quarter.

✔️ Awesome, I've got everything!

Great. Make sure you have seen results from the analytics query before moving on.

ⓧ I'd like to double check the full code

Here is the full SQL for the Gold layer, split across the four scripts you ran.

-- 06_gold_dim_dates.sql
CREATE TABLE gold.dim_dates AS
SELECT
    TO_CHAR(d, 'YYYYMMDD')::INTEGER AS date_key,
    d AS full_date,
    EXTRACT(DOW FROM d)::INTEGER AS day_of_week,
    TO_CHAR(d, 'Day') AS day_name,
    EXTRACT(MONTH FROM d)::INTEGER AS month,
    TO_CHAR(d, 'Month') AS month_name,
    EXTRACT(QUARTER FROM d)::INTEGER AS quarter,
    EXTRACT(YEAR FROM d)::INTEGER AS year,
    CASE WHEN EXTRACT(DOW FROM d) IN (0, 6) THEN TRUE ELSE FALSE END AS is_weekend
FROM GENERATE_SERIES('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day'::INTERVAL) AS d;

ALTER TABLE gold.dim_dates ADD PRIMARY KEY (date_key);
-- 07_gold_dim_customers_products.sql
CREATE TABLE gold.dim_customers (
    customer_key SERIAL PRIMARY KEY,
    customer_id VARCHAR(20) NOT NULL UNIQUE,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(30),
    city VARCHAR(50),
    country VARCHAR(50),
    segment VARCHAR(30)
);

INSERT INTO gold.dim_customers (customer_id, first_name, last_name, email, phone, city, country, segment)
SELECT customer_id, first_name, last_name, email, phone, city, country, segment
FROM silver.customers;

CREATE TABLE gold.dim_products (
    product_key SERIAL PRIMARY KEY,
    product_id VARCHAR(20) NOT NULL UNIQUE,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    subcategory VARCHAR(50),
    brand VARCHAR(50),
    unit_cost DECIMAL(10,2)
);

INSERT INTO gold.dim_products (product_id, product_name, category, subcategory, brand, unit_cost)
SELECT product_id, product_name, category, subcategory, brand, unit_cost
FROM silver.products;
-- 08_gold_fact_sales.sql
CREATE TABLE gold.fact_sales (
    sale_id SERIAL PRIMARY KEY,
    customer_key INTEGER NOT NULL REFERENCES gold.dim_customers(customer_key),
    product_key INTEGER NOT NULL REFERENCES gold.dim_products(product_key),
    date_key INTEGER NOT NULL REFERENCES gold.dim_dates(date_key),
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    discount DECIMAL(5,2)
);

INSERT INTO gold.fact_sales (customer_key, product_key, date_key, quantity, unit_price, total_amount, discount)
SELECT
    dc.customer_key,
    dp.product_key,
    TO_CHAR(s.sale_date, 'YYYYMMDD')::INTEGER AS date_key,
    s.quantity,
    s.unit_price,
    ROUND(s.quantity * s.unit_price * (1 - s.discount), 2) AS total_amount,
    s.discount
FROM silver.sales s
JOIN gold.dim_customers dc ON s.customer_id = dc.customer_id
JOIN gold.dim_products dp ON s.product_id = dp.product_id;
-- 09_analytics_query.sql
SELECT
    dc.segment AS customer_segment,
    dp.category AS product_category,
    dd.quarter,
    SUM(fs.total_amount) AS revenue,
    COUNT(*) AS num_sales
FROM gold.fact_sales fs
JOIN gold.dim_customers dc ON fs.customer_key = dc.customer_key
JOIN gold.dim_products dp ON fs.product_key = dp.product_key
JOIN gold.dim_dates dd ON fs.date_key = dd.date_key
GROUP BY dc.segment, dp.category, dd.quarter
ORDER BY revenue DESC;

Your star schema is complete. You have a working enterprise data warehouse with Bronze, Silver, and Gold layers. Next up, you will tackle a Secret Mission that adds historical tracking to your dimension tables.

Secret mission

Add SCD Type 2 to dim_customers

Your star schema works great for today's data. But what happens when a customer changes segment? Right now, you'd overwrite the old value and lose history forever. In this secret mission, you'll implement Slowly Changing Dimensions Type 2 to track historical changes over time.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally on your machine, so there are no ongoing costs. Decide whether to keep your resources for further experimentation, pause PostgreSQL to free system memory, or delete everything entirely.

Resources you used:

  • retail_dwh PostgreSQL database (bronze, silver, and gold schemas with all tables and data).
  • PostgreSQL 18 installation (includes pgAdmin).
  • Star schema ERD PNG file (exported from dbdiagram.io).

Keep everything running

No action needed. Choose this if you are still actively building or want to keep experimenting with queries on your data warehouse.

  • Your retail_dwh database persists on your local PostgreSQL server.
  • PostgreSQL continues running as a background service and starts automatically when your machine boots.
  • pgAdmin remains available for querying and managing your database.
  • Your exported ERD PNG file stays wherever you saved it on your machine.

Pause - I'll come back to this later

Stop the PostgreSQL service to free up system memory. Your database files remain intact on disk so you can restart the service later.

  • On Windows: press the Windows key, type Services, and press Enter.
  • Find the PostgreSQL service in the list (it starts with postgresql), right-click it, and select Stop.
  • On macOS: open Terminal and run sudo launchctl unload /Library/LaunchDaemons/com.edb.launchd.postgresql-18.plist to stop the service.
  • To resume later, repeat the steps above and select Start (Windows) or replace unload with load (macOS).

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow these steps in order.

  • Open pgAdmin and connect to your local PostgreSQL server.
  • In the browser tree on the left, right-click the retail_dwh database and select Delete/Drop.
  • Confirm the deletion when prompted.
  • On Windows: open Settings, select Apps, then Installed apps. Find PostgreSQL 18 in the list, click the three-dot menu, and select Uninstall. Follow the prompts to remove the entire application including pgAdmin.
  • On macOS: navigate to /Library/PostgreSQL/18/ in Finder and run the uninstall-postgresql.app application included with the EDB installer.
  • Delete the star schema ERD PNG file from wherever you saved it (likely your Downloads folder).

You used dbdiagram.io without creating an account. Your diagrams only exist in your browser session and the exported PNG file. There is nothing to delete on the dbdiagram.io side.

Nice Work!

Nice Work!

Nice work! You just designed and built a complete enterprise data warehouse from scratch using PostgreSQL and the Medallion Architecture.

You've learned how to:

  • Build a layered data warehouse following the Medallion Architecture pattern (Bronze for raw ingestion, Silver for cleansing, Gold for business-ready models).
  • Design a Kimball star schema with a visual ERD in dbdiagram.io, then implement it in PostgreSQL with surrogate keys and foreign key relationships between fact and dimension tables.
  • Write ETL SQL that ingests raw data with real-world quality issues, cleanses and standardizes it, and transforms it into analytics-ready dimensional models.
  • Secret Mission: Implemented SCD Type 2 to track historical changes in dimension attributes so you can answer time-based analytical questions.

Ready to quiz yourself?