Give me your best AI Engineer
Canonicalization, deduplication and stable joins reveal a message buried in messy customer and order data.
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)
- Normalize Headers
- Lowercase all headers, strip whitespace, collapse multiple spaces.
- Apply the alias mapping from
aliases.csv(e.g.,"e-mail","email_address"→"email").
- Canonicalize Email Keys
- Convert email to lowercase.
- Apply Unicode NFC normalization.
- Remove only spaces (keep and
_, as they are meaningful identifiers).
- Deduplicate Customers
- Group by canonical email.
- Keep the record with the earliest
created_attimestamp.
- Deduplicate Orders
- Group by
order_id. - Keep the record with the highest
version(lower versions contain decoy bytes).
- Group by
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)
- Customers: after canonicalization, sort by
created_atand keep the first row per canonical key. - Orders: sort by
order_id+version(descending), then keep the first per order_id. - Join Strategy: Inner join
orders↔customerson the canonical key. - Stable Sorting: Sort the joined dataset by:
region(alphabetical: East, North, South, West)order_date(chronological)order_id(alphanumeric)
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)
- After sorting, read every 137th row (positions 137, 274, 411, …).
- Extract the
byte_codefield. - Convert each
byte_codeinto an ASCII character. - Concatenate characters to form the flag.
Code snippet — flag extraction
flag_bytes = []
for i in range(136, len(result), 137): # 0-indexed → row 137
flag_bytes.append(result.iloc[i]["byte_code"])
flag = "".join(chr(b) for b in flag_bytes)
print("Flag:", flag)
- Final Flag:
CONCTF{Data_2L3An_1N5iGht}