Q2 - MongoDB Legacy Data Cleanup (~2 Parts)

You've just joined a team migrating data into MongoDB. A colleague drops a text file on you, a dump exported from some ancient CRM that's being decommissioned, and says:

"Can you clean this up so we can import it? It's kind of a mess."

That's the entire spec. No schema. No format document. No examples of the desired output. Just a messy file and a vague ask. Welcome to the interview.

When you peek inside, the file looks something like this, clearly meant to be a list of records, but exported by something that didn't care much about consistency:

# CRM export - legacy system v3
# generated 2026-01-02

Name:  John Doe
Email: JOHN@EXAMPLE.COM
Phone: 555-1234

NAME: Jane Smith
email :  jane@sample.org

Name: Bob Jones
Phone: 555 222 3333
this line is junk with no field
Email: bob@test.io

Inconsistent key casing, random extra spaces, comment lines, blank lines between records, a junk line with no field at all. Your job, loosely stated, is to turn this into something structured and clean that could be imported as documents.

For the rest of this walkthrough, we'll commit to a reasonable set of assumptions, state these to your interviewer and adjust if they push back:

• The input is the raw file content as a single string.
• Records are separated by blank lines; each non-empty, non-comment line is a key: value pair split on the first :.
• Keys are normalized: trimmed and lowercased. Values are trimmed.
• Comment lines start with # and are ignored. Malformed lines (no :) are skipped.
• Output is a list of records, each a key→value map.

Part 1: Normalizing One Messy File (~20 minutes)

Write a function parse_records(content) that takes the raw text and returns the cleaned list of records.

Input (raw file text):
"""
# CRM export - legacy system v3

Name:  John Doe
Email: JOHN@EXAMPLE.COM
Phone: 555-1234

NAME: Jane Smith
email :  jane@sample.org

Name: Bob Jones
Phone: 555 222 3333
this line is junk with no field
Email: bob@test.io
"""

Output (list of records):
[
  {"name": "John Doe", "email": "JOHN@EXAMPLE.COM", "phone": "555-1234"},
  {"name": "Jane Smith", "email": "jane@sample.org"},
  {"name": "Bob Jones", "phone": "555 222 3333", "email": "bob@test.io"},
]

Solution - Part 1

The approach is a single linear pass, a tiny state machine with exactly one piece of state: the record we're currently building. We walk the lines and react to each one:

• Blank line → the current record is done; if it has any fields, push it and start a fresh one.
• Comment (#…) → ignore.
• key: value → split on the first : (values may legitimately contain colons, e.g. a URL), normalize the key, trim the value, store it.
• Anything else (no :) → malformed; skip it (we could just as easily collect these into a "rejects" list, a nice thing to mention).

Here's the idiomatic Python version first, since this is exactly the kind of problem where Python's str.partition and str.strip shine:

def parse_records(content: str) -> list[dict]:
    records = []
    current = {}

    for raw_line in content.splitlines():
        line = raw_line.strip()

        if line == "":                      # blank line -> record boundary
            if current:
                records.append(current)
                current = {}
            continue
        if line.startswith("#"):            # comment -> ignore
            continue
        if ":" not in line:                 # malformed -> skip (or collect)
            continue

        key, _, value = line.partition(":") # split on the FIRST colon only
        key = key.strip().lower()
        value = value.strip()
        if key:
            current[key] = value            # duplicate keys: last one wins

    if current:                             # don't forget the final record
        records.append(current)
    return records

Complexity Analysis

• Time: O(n), where n is the total length of the input, each line is processed once, with O(line length) splitting/trimming.
• Space: O(n) for the parsed records we return.

Part 2: Merging Many Files (~25 minutes)

Just as you finish, the interviewer smiles: "Turns out that CRM exported the data in several files, one per sync run, and the same customer can appear in more than one. Newer files have corrections. Can you merge them into one clean view, one record per customer?"

This is the realistic complication, and it turns a parsing problem into a data-reconciliation problem, which is squarely MongoDB's world (this is essentially an upsert / mongoimport --mode merge). Before coding, more questions surface, and again, state your assumptions:

• What makes two records "the same" customer? We'll assume a stable id field. Records without an id are skipped (or you could keep them as standalone, your call).
• When two files disagree on a field, who wins? Files can arrive out of order, so "last file wins" is fragile. We'll assume each record carries an integer version, and higher version wins on a conflict (ties broken by file order). A field that only one file has is always kept (we union fields).
• Are id and version part of the output document? We'll treat them as control metadata: keep id, drop version from the merged fields.

So merge_files(file_contents) takes a list of raw file strings (in order) and returns one merged record per id.

Input (two files, in order):

file_1 = """
id: 1
name: John
email: john@old.com
version: 1

id: 2
name: Jane
version: 1
"""

file_2 = """
id: 1
email: john@new.com
phone: 555-1234
version: 2

id: 3
name: Bob
version: 1
"""

merge_files([file_1, file_2])

Output (one record per id):
[
  {"id": "1", "name": "John", "email": "john@new.com", "phone": "555-1234"},
  {"id": "2", "name": "Jane"},
  {"id": "3", "name": "Bob"},
]

Solution - Part 2

We reuse parse_records from Part 1 to turn each file into records, then fold them together. For every id we keep two things: the merged document so far, and the highest version we've applied to it. For each incoming field:

• If the merged doc doesn't have that field yet → add it (fill the gap, regardless of version).
• If it already has it → overwrite only if the incoming record's version is ≥ the best version we've applied (newer wins; equal wins by file order, since we process in order).

That single rule, fill gaps always, overwrite only when newer-or-equal, gives us "union of fields, newest value wins."

def merge_files(file_contents: list[str]) -> list[dict]:
    merged = {}        # id -> merged record
    best_version = {}  # id -> highest version applied so far

    for content in file_contents:
        for record in parse_records(content):   # reuse Part 1
            if "id" not in record:
                continue                         # no identity -> skip
            rid = record["id"]
            version = int(record.get("version", "0"))

            if rid not in merged:
                merged[rid] = {"id": rid}
                best_version[rid] = -1           # so the first record always applies

            target = merged[rid]
            best = best_version[rid]
            for key, value in record.items():
                if key in ("id", "version"):     # control metadata, not content
                    continue
                if key not in target or version >= best:
                    target[key] = value          # fill gap, or overwrite if newer/equal
            best_version[rid] = max(best, version)

    return list(merged.values())

Complexity Analysis

• Time: O(N), where N is the total size of all files combined. We parse every file once and touch each field once.
• Space: O(U), where U is the size of the merged output (one document per unique id, holding the union of its fields).

Final Notes

Here's how to actually win this kind of problem:

1. The first five minutes are clarifying questions, not code. We cannot stress this enough. The vagueness is the test. A candidate who immediately starts typing has failed the most important signal. A candidate who says "before I write anything, is the input a path or a string? Is this secretly a CSV? How should I treat junk lines?" has already separated themselves. MongoDB interviewers have repeatedly been described as happy to let you choose your own approach, as long as you justify it. Use that freedom out loud.

2. Reach for the standard library, and say why. If it turns out to be a real CSV, don't hand-roll comma splitting, use Python's csv module (or your language's equivalent). It handles quoted fields, embedded commas, and escaped newlines that will absolutely appear in real data and absolutely break a naive split(","). Knowing when not to write the parser yourself is senior-level signal. This is the core reason we recommend Python for these: str.strip, str.partition, csv, and json let you move fast and correctly.

3. Handle the ugly inputs before they're asked. Make your code run (remember, MongoDB uses a live compiler) and walk these out loud: an empty file, a file that's only comments, a file with no trailing newline (the classic "lost last record" bug), duplicate keys, a value that itself contains a :, and, for Part 2, the same id in three files with versions arriving out of order. Naming these unprompted is exactly the robustness MongoDB looks for.

4. Keep parsing and reconciling separate. Notice Part 2 simply reused parse_records. Resisting the urge to cram everything into one mega-function, keeping the "turn text into records" stage cleanly separate from the "merge records" stage, is the kind of decomposition that reads like production code. Interviewers notice clean seams.

The bottom line: this problem calls for the opposite instincts of a LeetCode sprint. Slow down, ask questions, pick a sane approach and defend it, lean on your standard library, and write clean, well-separated, runnable code. Do that, and you'll be writing the calm, pragmatic code MongoDB hires for.