Lesson 1 of 6 · 47 min

Data wrangling under the gun

The forward-deployed job starts with a 250MB customer Excel, not a clean API. Streaming parse, type coercion, a canonical ontology, dedup/entity-resolution with dedupe, and a 20-sample quality eval — the flow that turns enterprise mess into something a model can stand on, fast.

Why the demo works and production doesn’t

The New Stack number that should reframe this whole track: roughly 95% of enterprise AI pilots produce no measurable P&L impact — and they don’t die in the model, they die at the data and integration seam (undocumented workflows, messy data, legacy systems). As a forward-deployed engineer you almost never start from a clean API. You start from a 250MB Excel attachment, a REST export with three different date formats, and a procurement VP’s spreadsheet whose “Active” flags were last set in 2003. This lesson is the first hour of every engagement: turn that mess into a canonical, deduplicated, eval-backed dataset a model can actually stand on. Interview angle. Palantir and OpenAI FDE loops open the coding round with exactly this — “parse this messy CSV,” “dedupe these customer records” — because it is the real job.

The senior mental model: treat data wrangling as a flow, not a project. There is a fixed ladder you climb every time — (1) ingest with type coercion, (2) normalize to a canonical ontology, (3) deduplicate and resolve entities, (4) stamp provenance, (5) embed/ship — and a 20-sample quality eval that runs at the bottom of it. The output you actually care about is a canonical join key (call it customer_canonical_id) that both the downstream LLM and your metrics depend on. Skip the dedup step and the same supplier shows up twice in your embedding space, and the model confidently merges two companies into one hallucinated answer.

Building out an entity resolution pipeline with Python and dbtdbt Labs (Coalesce)

Ingest: stream it, coerce it, never load the whole thing

Rule one of the field: never pd.read_csv(whole_thing) on a customer export. A 10M-row file will OOM the notebook you’re demoing from, in front of the customer, on day one. Pull data in chunks — Pandas chunksize=10_000, or the stdlib csv reader with explicit dtype="string" and na_filter=False so the parser doesn’t silently turn the string "NA" (a real region code) into a null. The coercion work is unglamorous and it is the job: numeric columns arrive as "1,234" and "$ 1,234", dates as "03/04/25" (is that March or April?), booleans as "Y"/"Yes"/"1"/"true"/"✓". Decide each mapping explicitly and write it down; a guessed coercion is a silent data bug you’ll chase for a week.

python

1import pandas as pd23# Stream a large customer export; coerce instead of guessing.4def load_suppliers(path):5    chunks = pd.read_csv(6        path,7        dtype="string",          # everything in as text first; coerce deliberately8        na_filter=False,         # do NOT auto-null "NA", "N/A", "" -- they may be real9        chunksize=10_000,        # a 10M-row file must not land in RAM at once10    )11    for df in chunks:12        df["amount"] = (13            df["amount"].str.replace(r"[$,\s]", "", regex=True)  # "$ 1,234" -> "1234"14            .pipe(pd.to_numeric, errors="coerce")15        )16        df["is_active"] = df["active_flag"].str.strip().str.lower().isin(17            {"y", "yes", "1", "true"}18        )19        yield df2021# The first pass over a real export answers: how many columns lie? Which dates22# are ambiguous? How many "Active" rows are actually dead? Profile before you model.

The concrete week-one case from the research makes this vivid: a pharmaceutical client with 17,000 supplier records split across SAP, Coupa, and a procurement VP’s Excel; four distinct address columns (“HQ”, “Registered”, “Bill-to”, “Ship-to”); and conflicting Active flags from two decades ago. No model survives that input. The move is to build the canonical ontology first — decide that a Supplier has exactly one canonical_name, one primary_address, one status with defined inclusion rules — and then map every source system onto it. You are not cleaning data; you are defining the object the rest of the system will agree on.

There’s a fixed pillar-to-tool mapping worth internalizing, because it’s the same on every engagement and it tells you what to reach for in the first hour versus the last mile. The first three pillars (parse, normalize, dedup) are the data layer; the last two (embed, eval) are the bridge to the model. Skipping straight to embedding — the thing that feels like “AI work” — is the single most common reason a forward-deployed pilot produces confidently wrong answers.

code

1THE WRANGLING FLOW (what to reach for, and when)23  Pillar          Tool / pattern                       When in the engagement4  -------------   ----------------------------------   -----------------------------5  parse           stdlib csv / pandas chunksize +      first hour, on the raw export6                  openpyxl; deliberate coercion7  normalize       Pydantic schema -> typed records     once freeform fields exist8  dedup/linkage   dedupe (blocking + learned match)    any list of > 1k entities9  embed/similar   embeddings + cosine top-k            categorization, fuzzy lookup10  eval quality    20-sample golden set, model-graded   last mile before shipping1112  Reach for them in order. The canonical_id from "dedup" is the precondition13  for both the embedding step AND the metric-bearing eval.

Dedup & entity resolution: the part that actually collapses the mess

In CRM and supplier data the canonical poisons are always the same four: whitespace (“Acme Inc” vs “Acme Inc ”), casing, legal-suffix variation (“Inc” / “Incorporated” / “LLC”), and concatenated or transliterated names (“van der Berg” vs “vanderberg”). A 60-line fuzzy pass on name + address typically collapses 15–25% duplicate rows before any AI work begins — which is why dedup, not prompting, is often the single biggest quality lever in the first week.

The library of record is dedupeio/dedupe — a battle-tested Python library that uses machine learning to do fuzzy matching, deduplication, and entity resolution on structured records. It doesn’t brute-force every pair (that’s O(N²) and dies at scale); it trains a blocking model that only compares records likely to match, then a pairwise classifier, then clusters. The field playbook: (a) run on a representative ~5k-row sample; (b) hand-label ~50 obvious pairs via its active-learning loop; (c) let it converge; (d) cluster; (e) write the canonical_id. Interview angle. “You have a million customer rows with dupes — how do you dedupe without comparing all pairs?” The strong answer names blocking / canopy clustering to cut the comparison space, then a learned pairwise match — not “nested loop with fuzzywuzzy.”

python

1import dedupe23# Entity resolution that scales: blocking cuts the O(N^2) comparison space,4# then a learned pairwise model + clustering. Label ~50 pairs, not millions.5fields = [6    {"field": "name", "type": "String"},7    {"field": "address", "type": "String"},8    {"field": "email_domain", "type": "ShortString", "has_missing": True},9]10deduper = dedupe.Dedupe(fields)11deduper.prepare_training(records)          # records: {id: {"name":..., "address":...}}12dedupe.console_label(deduper)              # active learning: ~50 yes/no pairs13deduper.train()1415clustered = deduper.partition(records, threshold=0.5)16# clustered -> [( (id1, id2, id3), (conf1, conf2, conf3) ), ...]17# Assign one canonical_id per cluster; that key joins SAP + Coupa + the VP's Excel.

Sometimes the dedup happens in the warehouse, not Python — and FDE SQL screens lean on this exact pattern. When duplicate ingest rows arrive (the same order_id landing three times from a flaky pipeline), the canonical move is a window function that keeps only the latest version per key before you aggregate. Knowing this cold is the difference between a strong SQL round and an “instant no-hire” for not being able to write a clean dedup.

sql

1-- FDE SQL screen: daily GMV + distinct orders, deduping duplicate ingest rows2-- by keeping the latest ingestion per order_id (slowly-changing snapshot pattern).3WITH latest AS (4  SELECT *,5         ROW_NUMBER() OVER (6           PARTITION BY order_id7           ORDER BY ingested_at DESC      -- keep the freshest row per order8         ) AS rn9  FROM orders_raw10  WHERE ingested_at >= CURRENT_DATE - INTERVAL '30 days'11)12SELECT order_date,13       SUM(amount)            AS daily_gmv,14       COUNT(DISTINCT order_id) AS distinct_orders15FROM latest16WHERE rn = 1                            -- the dedup: one row per order_id17GROUP BY order_date18ORDER BY order_date;

Schema drift: the customer is a federation, not a source of truth

Enterprise schemas lie and move. A Salesforce field that started as free-text “Status” gets repurposed three times; a column widens from text to a picklist; an app update renames a field mid-engagement. The data-engineering literature names three responses, and you must pick deliberately: schema-evolve (consumer relaxes types, accepts new fields as optional — resilient but leaks a “big blob” downstream), schema-enforce (source rejects non-conforming payloads — strict, but you’ll lose that fight at a Fortune 500 whose org three prior vendors operated), and schema-adapt (auto-rewrite at the ingestion edge — best of both, but needs real observability + a dead-letter queue).

The FDE default is schema-adapt at the edge, schema-enforce at the contract: write the ad-hoc mapper yourself, but stamp every record with source_system and is_current so drift is traceable, and impose a typed contract downstream of the messy edge (Azure Data Factory’s “flexible at source, typed downstream” split is the canonical pattern). Until the customer assigns canonical ownership of the master record, model them as a multi-source federation, not a single source of truth. Interview angle. “The customer’s ‘Customer’ object has eight nickname variants across systems — what do you do?” List every observed schema, choose schema-adapt, stamp provenance, and push back for canonical ownership with a date and an owner.

code

1THE THREE SCHEMA-DRIFT PHILOSOPHIES (pick deliberately)23  Philosophy       Mechanism                          Pro                Con4  --------------   --------------------------------    ---------------    -------------------------5  schema-evolve    consumer relaxes types,             immediate          "big blob" leaks6                   new fields optional                 resilience         downstream7  schema-enforce   source rejects non-conforming       strict contract    you lose this fight at8                   payloads                                               a F500 you don't control9  schema-adapt     auto-rewrite at ingestion edge      best of both       needs observability + DLQ1011  FDE default: schema-adapt at the edge + typed contract downstream.12  Always stamp source_system + is_current so drift stays traceable.

The 20-sample quality eval (write it before you ship)

The OpenAI cookbook’s Eval-Driven System Design recipe is the most important process idea in the whole track, and it starts in the data layer: build a small labeled set to qualify a minimal viable system, align eval scores with a business KPI, then iterate. The receipt-inspection case study is the proof — it began at “two false negatives and two false positives across 20 samples” and, after better prompts and few-shot examples (no model change), landed at one false negative and one false positive: a 50% error reduction. Your data eval is the same shape: ~20 hand-labeled “these two rows are/aren’t the same company” and “this row’s canonical fields are correct” judgments, run as a CI step. It’s an afternoon of work that saves weeks of “is this better?” Slack threads with the customer.

Once the data is canonical and deduped, the last pillar is embedding for similarity search — the bridge to the model. Categorization, semantic lookup, and fuzzy joins that exact keys can’t express all run on embeddings + cosine top-k (the RAG recipe from lesson 3). But the ordering is load-bearing: embedding a pre-dedup corpus bakes the duplicates into the vector space permanently, so this step strictly follows the canonical-ID step. The same canonical_id that joins SAP and Coupa is the metadata you attach to each vector so retrieval can filter and attribute.

Case studies: how the messy-data step actually plays out

Palantir Deltas operate terabyte-scale pipelines on customer sites and fold field-built transformations back into Foundry — the COVID-19 response flows were “deployed and operational within days” precisely because the canonical ontology work was treated as the product, not a chore. Scale AI FDEs are first-class data engineers who sit inside the customer’s data plane; for some companies the entire FDE deliverable is the production-grade pipeline that feeds the model, not the model itself. OpenAI FDEs use the eval-driven-design loop above as their canonical handbook for shipping data-backed features on a customer’s clock. The common thread: in the field, the data plumbing is 50%+ of the work, not the last 10%.

No AI model survives a 17,000-row supplier export with four address columns and 2003-era status flags. Build the canonical ontology first, then run evals against it. — the through-line of every forward-deployed data engagement.

Interview prep

The FDE coding round is not LeetCode — it is “practical engineering under realistic constraints,” and parsing/dedup of messy data is its most common opening prompt. Lead every answer with the mechanism (blocking, coercion, provenance stamping) and then the production consequence. Narrate continuously; silence on the coding round is one of the most-cited weak signals.

01“Parse this messy CSV with inconsistent quoting/dates.” → stream in chunks, dtype=string + na_filter off, coerce each column explicitly, profile what lies before modeling.
02“Dedupe a million customer rows.” → block to cut O(N²), learned pairwise match (dedupe), cluster transitively; write one canonical_id. Never a nested fuzzywuzzy loop.
03“The same company appears as ‘Acme Inc’ and ‘ACME Incorporated’ — fix it.” → normalize whitespace/case/legal-suffix, fuzzy match name+address, resolve to a canonical record with confidence.
04“Write SQL for daily GMV deduping duplicate ingest rows.” → window function ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY ingested_at DESC) = 1, then aggregate.
05“The customer’s schema keeps changing — how do you survive it?” → schema-adapt at the edge, typed contract downstream, stamp source_system + is_current, push for canonical ownership.
06“How do you know your cleaned data is good?” → a 20-sample labeled golden set in CI, eval scores tied to a business KPI, regressions block the release.
07“They gave you 4 address columns — which is canonical?” → don’t guess; define inclusion rules with the customer, model as a multi-source federation until they assign ownership.
08“How big is the dedup win, realistically?” → 15–25% duplicate collapse on typical CRM data; it’s often the biggest single quality lever week one.

Follow-ups cluster tightly: “what happens if a coercion fails — drop, default, or dead-letter the row?” (dead-letter and review, never silently drop), “how would you validate this in production?” (Great Expectations / schema checks + the golden set), and “how do you split the work between your team and the customer’s?” (you own the mapper and evals; they own canonical-record ownership and access). The interviewer is reading whether you’ve actually done this on a real deployment, so anchor answers to a specific messy dataset and what broke.

repodedupe — ML for fuzzy matching, dedup & entity resolution on structured datadedupeio docsEval Driven System Design — From Prototype to Production (receipt inspection)OpenAI Cookbook docsSchema drift in mapping data flow (flexible at source, typed downstream)Microsoft Azure articleA Day in the Life of a Palantir Forward Deployed Software EngineerPalantir

Checkpoint

You open a customer engagement with a 9GB CSV export. Your demo notebook has 16GB RAM. First move?

Apd.read_csv on the whole file — 9GB fits in 16GB of RAMBStream it in chunks (chunksize) with explicit dtypes, profiling and coercing column-by-column before anything elseCAsk the customer to export it as JSON instead

Checkpoint

A client VP wants a dashboard “tomorrow,” but it depends on a join key that doesn’t exist and two systems with conflicting customer names. What ships something defensible?

ABuild the canonical_id via entity resolution, ship an MVP dashboard with explicit caveats in the UI, and commit to closing the data gaps with dated ownersBTell the VP it’s impossible until the data is cleanCPick whichever system’s names look cleaner and join on a fuzzy string match silently

Checkpoint

You must dedupe 2M customer records. Which approach is both correct and tractable?

ANested loop comparing every pair with a similarity ratioBSort by name and only merge exact string matchesCBlock records into candidate groups, run a learned pairwise classifier inside blocks, then cluster transitively

Checkpoint

Mid-engagement, a Salesforce field you depend on silently changes from free-text to a picklist and your pipeline starts dropping rows. Best durable response?

AHard-code the new picklist values into the parser and move onBDemand the customer freeze their schema for the rest of the engagementCSchema-adapt at the ingestion edge, stamp source_system + is_current, dead-letter non-conforming rows for review, and impose the typed contract downstream

Checkpoint

The customer says “your cleaned data looks wrong” but can’t say which rows. What do you reach for?

AA 20-sample labeled golden set run as a CI check, with dedup/coercion scores tied to a business KPIBRe-run the whole pipeline with different parameters until they’re happyCAsk them to manually review the full dataset and flag every bad row

Could you walk into a customer with a messy export and, within the hour, produce a canonical, deduplicated, eval-backed dataset — and defend each step in an FDE coding round?

New to itGetting thereConfident

Takeaways

95% of AI pilots die at the data/integration seam, not the model — wrangling is 50%+ of the work.
Ingest by streaming + deliberate coercion; never read a customer export whole.
Define the canonical ontology + join key first; map every source system onto it.
Dedup with blocking → learned pairwise match → transitive clustering (dedupe), not O(N²) loops.
Schema-adapt at the edge, typed contract downstream, stamp provenance; the customer is a federation.
Write a 20-sample golden eval first — it’s your objective interlocutor when quality is disputed.

Next: APIs & integration patterns — making every write idempotent, every retry full-jitter, every webhook verified.

Sources

Free to read · better with Enzo

Learn it with Enzo

Save your progress, answer the checkpoints, and let Enzo quiz you on what you just read.

Continue with Enzo Sign up free