A data warehouse typically uses a , consisting of a central Fact Table (quantitative data like sales) surrounded by Dimension Tables (descriptive data like products or dates).
Once loaded, you can query the "Gold" layer to answer business questions.
moves data from raw sources (like CSVs or ERP systems) into your warehouse. Extract : Pulling raw data into the Bronze Layer . Building a Data Warehouse with Examples in SQL ...
-- 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)
: Cleaning data in the Silver Layer , such as standardizing "Yes/No" strings to booleans. Load : Inserting into the final Gold Layer tables. A data warehouse typically uses a , consisting
-- Finding total sales by product category SELECT p.category, SUM(s.sale_amount) AS total_revenue FROM fact_sales s JOIN dim_product p ON s.product_key = p.product_key GROUP BY p.category; Use code with caution. Copied to clipboard
-- 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) Extract : Pulling raw data into the Bronze Layer
: dim_product , dim_customer , and dim_date provide context. 2. Laying the Foundation (SQL Table Creation) You start by defining these structures in your database.