Overview

You’re given three messy datasets (customers.csv, orders.csv, aliases.csv) along with cryptic notes about canonicalization and duplicates. Your task is to clean, deduplicate, and integrate them properly. If you follow the correct data warehousing rules, a hidden flag is revealed: one character is embedded every 137th row of the final dataset.

Phase 1 — Standardize & Canonicalize the Data

What to do (explanation only)

  1. Normalize Headers
  2. Canonicalize Email Keys
  3. Deduplicate Customers
  4. Deduplicate Orders

Code snippet — header normalization + email canonicalization

import pandas as pd, unicodedata, re

def load_aliases():
    aliases = pd.read_csv("aliases.csv")
    return dict(zip(aliases["alias"], aliases["canonical"]))

def normalize_headers(df, aliases):
    norm_cols = []
    for col in df.columns:
        col = col.lower().strip()
        col = re.sub(r"\s+", " ", col)
        norm_cols.append(aliases.get(col, col))
    df.columns = norm_cols
    return df

def canonicalize_email(email):
    if pd.isna(email): return email
    email = email.lower()
    email = unicodedata.normalize("NFC", email)
    return email.replace(" ", "")

Phase 2 — Deduplicate & Join

What to do (explanation only)

Code snippet — deduplication + join

def process_customers(df):
    df["canonical_key"] = df["email"].apply(canonicalize_email)
    df["created_at"] = pd.to_datetime(df["created_at"])
    df = df.sort_values(["canonical_key", "created_at"])
    return df.groupby("canonical_key").first().reset_index()

def process_orders(df):
    df["canonical_key"] = df["customer_key"].apply(canonicalize_email)
    df["order_date"] = pd.to_datetime(df["order_date"])
    df = df.sort_values(["order_id", "version"], ascending=[True, False])
    return df.groupby("order_id").first().reset_index()

# Join and sort
result = pd.merge(orders_processed, customers_processed,
                  on="canonical_key", how="inner")
result = result.sort_values(["region", "order_date", "order_id"]).reset_index(drop=True)

Phase 3 — Extract the Flag (every 137th row)

What to do (explanation only)