-- Creating a Dimension Table for Products CREATE TABLE dim_product ( product_key INT PRIMARY KEY, product_name VARCHAR(100), category VARCHAR(50) ); -- Creating the Fact Table CREATE TABLE fact_sales ( sale_id INT PRIMARY KEY, product_key INT, customer_key INT, sale_amount DECIMAL(10, 2), sale_date DATE, FOREIGN KEY (product_key) REFERENCES dim_product(product_key) ); Use code with caution. Copied to clipboard 3. Moving the Earth (ETL Process)
Once loaded, you can query the "Gold" layer to answer business questions. Building a Data Warehouse with Examples in SQL ...
To build a data warehouse, you first need to identify your business objectives, such as revenue forecasting or customer segmentation, to guide your design. A common approach is the , which organizes data into three layers: Bronze (raw), Silver (cleaned), and Gold (analytical/star schema). The Story: Building the "North Star" Sales Warehouse 1. Designing the Blueprint (Data Modeling) -- Creating a Dimension Table for Products CREATE
: Cleaning data in the Silver Layer , such as standardizing "Yes/No" strings to booleans. Load : Inserting into the final Gold Layer tables. To build a data warehouse, you first need
moves data from raw sources (like CSVs or ERP systems) into your warehouse. Extract : Pulling raw data into the Bronze Layer .
-- Transforming and Loading: Standardizing product names to uppercase INSERT INTO dim_product (product_key, product_name, category) SELECT product_id, UPPER(p_name), category FROM raw_staging_products; Use code with caution. Copied to clipboard 4. The Final View (Analytical Querying)
: Stores metrics like price, quantity, and foreign keys.