Approach: design a classic star schema with a central fact_orders table (surrogate integer keys, foreign keys to dimension surrogate keys) and dimension tables customers, products, promotions, time. For Amazon Redshift I'll choose DISTSTYLE/KEY for co-location on common joins and SORTKEYs for common filters (order_ts/time_key). For Snowflake I'll note clustering keys instead of distkeys.sql
-- REDSHIFT DDLs
CREATE TABLE dim_customers (
customer_sk INT IDENTITY(1,1) PRIMARY KEY,
customer_id VARCHAR(50), -- business natural id
first_name VARCHAR(100),
last_name VARCHAR(100),
email VARCHAR(256),
phone VARCHAR(50),
signup_ts TIMESTAMP,
country VARCHAR(50)
)
DISTSTYLE ALL; -- smallish dimension: replicate for fast joins
CREATE TABLE dim_products (
product_sk INT IDENTITY(1,1) PRIMARY KEY,
product_id VARCHAR(50),
name VARCHAR(256),
category VARCHAR(100),
price NUMERIC(10,2)
)
DISTSTYLE ALL;
CREATE TABLE dim_promotions (
promotion_sk INT IDENTITY(1,1) PRIMARY KEY,
promotion_id VARCHAR(50),
name VARCHAR(200),
discount_pct NUMERIC(5,2),
start_dt DATE,
end_dt DATE
)
DISTSTYLE ALL;
CREATE TABLE dim_time (
time_sk INT IDENTITY(1,1) PRIMARY KEY,
date DATE,
year INT,
quarter INT,
month INT,
day INT,
weekday INT
)
DISTSTYLE ALL
SORTKEY (date);
CREATE TABLE fact_orders (
order_sk BIGINT IDENTITY(1,1) PRIMARY KEY,
order_id VARCHAR(50),
customer_sk INT,
product_sk INT,
promotion_sk INT, -- nullable
order_ts TIMESTAMP,
order_date DATE,
quantity INT,
unit_price NUMERIC(10,2),
total_amount NUMERIC(12,2)
)
DISTKEY (customer_sk) -- distribute by customer for customer-centric queries
SORTKEY (order_date, order_ts);
-- Foreign keys not enforced in Redshift for performance; document relationships.
