Lab: Model Harbor Lane's warehouse

mkdir harbor-data && cd harbor-data
python3 -m venv .venv && source .venv/bin/activate
pip install duckdb pyyaml
mkdir -p data/raw

'''Harbor Lane's source systems, in miniature. Deterministic: same files every run.'''
import csv, os, random
from datetime import date, timedelta

random.seed(42)
os.makedirs('data/raw', exist_ok=True)

FIRST = ['Ava','Ben','Carla','Dev','Elena','Farid','Grace','Hugo','Imani','Jack',
         'Keiko','Liam','Mona','Nate','Olive','Priya','Quinn','Rosa','Sam','Tara']
LAST = ['Alvarez','Brooks','Chen','Diaz','Egan','Fischer','Grant','Hopper','Ito',
        'Jensen','Khan','Lopez','Mehta','Novak','Ortiz','Park','Reyes','Singh']
ZIPS = ['02114','02139','01801','02446','02155']

def phone():
    return f'617-{random.randint(200,999)}-{random.randint(1000,9999)}'

# 320 real people. The first 140 exist in BOTH systems - that is Module 4's problem.
people = [{'first': random.choice(FIRST), 'last': random.choice(LAST),
           'phone': phone(), 'zip': random.choice(ZIPS), 'n': i} for i in range(320)]
for p in people:
    p['email'] = f"{p['first'].lower()}.{p['last'].lower()}{p['n']}@example.com"

# --- web store: people 0-259, clean-ish -----------------------------------
with open('data/raw/web_customers.csv', 'w', newline='') as f:
    w = csv.writer(f)
    w.writerow(['web_id','email','first_name','last_name','phone','zip','created_at'])
    for p in people[:260]:
        w.writerow([f"W{p['n']:04}", p['email'], p['first'], p['last'], p['phone'],
                    p['zip'], (date(2026,1,1) + timedelta(days=p['n'] % 150)).isoformat()])

# --- point of sale: 140 overlap + 60 store-only, entered by cashiers ------
def mess_phone(ph):
    d = ph.replace('-', '')
    return random.choice([ph, d, f'({d[:3]}) {d[3:6]}-{d[6:]}'])

with open('data/raw/pos_customers.csv', 'w', newline='') as f:
    w = csv.writer(f)
    w.writerow(['pos_id','full_name','email','phone','store_zip','signup_date'])
    for p in people[:140] + people[260:]:
        email = p['email'] if random.random() > 0.3 else ''   # 30% missing email
        name = f"{p['first']} {p['last']}"
        if random.random() < 0.2:
            name = name.upper()                                # SOME ARE SHOUTED
        w.writerow([f"P{p['n']:04}", name, email, mess_phone(p['phone']),
                    p['zip'], (date(2026,2,1) + timedelta(days=p['n'] % 90)).isoformat()])

# --- product catalog -------------------------------------------------------
CATS = [('Coffee', 8, 24), ('Bakery', 3, 9), ('Beans', 12, 28), ('Gear', 15, 60)]
with open('data/raw/products.csv', 'w', newline='') as f:
    w = csv.writer(f)
    w.writerow(['sku','product_name','category','unit_cost'])
    for i in range(40):
        cat, lo, hi = CATS[i % len(CATS)]
        w.writerow([f'SKU-{i:03}', f'{cat} item {i}', cat, round(random.uniform(lo, hi), 2)])

# --- one order export per day for 30 days ----------------------------------
start = date(2026, 5, 1)
oid = 1000
for d in range(30):
    day = start + timedelta(days=d)
    with open(f'data/raw/orders_{day.isoformat()}.csv', 'w', newline='') as f:
        w = csv.writer(f)
        w.writerow(['order_id','channel','customer_ref','sku','qty','unit_price','ordered_at','status'])
        for _ in range(random.randint(80, 120)):
            p = random.choice(people)
            web = p['n'] < 260 and (p['n'] >= 140 or random.random() < 0.5)
            ref = f"W{p['n']:04}" if web else f"P{p['n']:04}"
            oid += 1
            w.writerow([f'O{oid}', 'web' if web else 'pos', ref,
                        f'SKU-{random.randint(0,39):03}', random.randint(1, 4),
                        round(random.uniform(4, 60), 2),
                        f'{day}T{random.randint(8,20):02}:{random.randint(0,59):02}:00',
                        random.choice(['completed'] * 8 + ['cancelled', 'refunded'])])

print('raw exports written to data/raw/')

# (named explore.py, not inspect.py - Python reserves some built-in
#  file names, don't collide with them: a file called inspect.py would
#  shadow Python's stdlib 'inspect' module and break duckdb's import)
import duckdb
con = duckdb.connect()  # in-memory; just looking

print(con.execute('''
    SELECT count(*) AS line_rows, count(DISTINCT order_id) AS order_lines
    FROM read_csv_auto('data/raw/orders_*.csv', union_by_name=true)
''').fetchall())

# The duplicate-person problem, visible in one query:
print(con.execute('''
    SELECT w.email, w.first_name, w.last_name, w.web_id, p.pos_id, p.full_name, p.phone
    FROM read_csv_auto('data/raw/web_customers.csv') w
    JOIN read_csv_auto('data/raw/pos_customers.csv') p ON lower(p.email) = lower(w.email)
    LIMIT 5
''').fetchall())

-- Harbor Lane star schema. Grain declarations are part of the model.

-- grain: one row per SKU (natural key: sku)
CREATE TABLE IF NOT EXISTS dim_product (
  sku          TEXT PRIMARY KEY,
  product_name TEXT,
  category     TEXT,
  unit_cost    DECIMAL(8,2)
);

-- grain: one row per line item on an order. Harbor Lane's data has one
-- line per order, so order_id is unique per row and works as the primary
-- key here; in production you'd key on (order_id, line_number).
-- customer_ref is a SOURCE key (W.../P...). The golden customer_key
-- arrives in Module 4 - the hole is deliberate.
CREATE TABLE IF NOT EXISTS fct_order_lines (
  order_id     TEXT PRIMARY KEY,
  channel      TEXT NOT NULL,
  customer_ref TEXT NOT NULL,
  sku          TEXT NOT NULL,
  qty          INTEGER,
  unit_price   DECIMAL(8,2),
  ordered_at   TIMESTAMP,
  order_date   DATE NOT NULL,
  status       TEXT
);

-- dim_customer (grain: one row per real-world customer) is created in
-- Module 4, by the entity-resolution job that can make that sentence true.