DevOps NetworkDevOpsNetwork
Join Free
Practical infra coding

Join a host inventory CSV with a team owners CSV

mediumCSV, JSON and YAML Must-do

Problem statement

You have a host inventory and a list of team owners, exported from two different systems. Produce one CSV that adds each host's owner and on-call channel. This is a left join: every host appears in the output, and hosts whose team is unknown get UNKNOWN.

The exports are not clean:

  • A team name contains a comma, so it is quoted: "payments, eu".
  • One host lists its team as Platform with a capital letter and a trailing space.

Match teams case-insensitively, ignoring surrounding spaces. Write the joined CSV to stdout. On stderr, list hosts with no matching team and teams with no hosts, because both usually mean the inventory is out of date.

hosts.csv

APACHE
hostname,ip,team
web-01,10.0.1.11,platform
web-02,10.0.1.12,platform
db-01,10.0.2.21,"payments, eu"
cache-01,10.0.3.31,Platform
batch-01,10.0.4.41,data

teams.csv

AUTOIT
team,owner,channel
platform,alice@example.com,#platform-oncall
"payments, eu",bob@example.com,#payments-eu
security,carol@example.com,#sec

Examples

Example 1

Input: python solution.py hosts.csv teams.csv

Output: hostname,ip,team,owner,channel web-01,10.0.1.11,platform,alice@example.com,#platform-oncall web-02,10.0.1.12,platform,alice@example.com,#platform-oncall db-01,10.0.2.21,"payments, eu",bob@example.com,#payments-eu cache-01,10.0.3.31,Platform,alice@example.com,#platform-oncall batch-01,10.0.4.41,data,UNKNOWN,

Explanation: stderr shows hosts with no matching team: batch-01 and teams with no hosts: security. The csv module writes the comma-containing team with quotes again.

Hints

Approach

A hash join: build an index on one side and probe it with the other.

  1. read_rows opens a file with newline="" (the csv module requires it for quoted newlines) and utf-8-sig, which removes the BOM that spreadsheet exports often add. It exits with a clear message if a required column is missing.
  2. Index teams.csv in a dict keyed by strip().lower(). Warn about duplicate teams and keep the first, so the result does not depend on silent overwrites.
  3. Stream hosts.csv. Each lookup is O(1); record which teams were used and which hosts did not match. A short row gives None for missing fields, which is reported and treated as unknown.
  4. csv.writer quotes fields that need it. After the loop, report unmatched hosts and unused teams on stderr.

Memory holds only the smaller file, so the host file can be arbitrarily large.

ComplexityTime O(n + m)Space O(m)
Python
import csv
import sys
def norm(key):
return key.strip().lower()
def read_rows(path, required):
f = open(path, newline="", encoding="utf-8-sig") # utf-8-sig drops an Excel BOM
reader = csv.DictReader(f)
missing = set(required) - set(reader.fieldnames or [])
if missing:
f.close()
sys.exit(f"{path}: missing column(s): {', '.join(sorted(missing))}")
return f, reader
def join(hosts_path, teams_path, out=sys.stdout):
# 1. index the small side (teams) in a dict: O(m) memory
f, reader = read_rows(teams_path, ["team", "owner", "channel"])
with f:
teams = {}
for row in reader:
key = norm(row["team"])
if key in teams:
print(f"{teams_path}:{reader.line_num}: duplicate team {row['team']!r}, "
"keeping the first", file=sys.stderr)
continue
teams[key] = row
# 2. stream the big side (hosts) and look each row up in O(1)
f, reader = read_rows(hosts_path, ["hostname", "ip", "team"])
writer = csv.writer(out, lineterminator="\n")
writer.writerow(["hostname", "ip", "team", "owner", "channel"])
used, unmatched = set(), []
with f:
for row in reader:
if row["team"] is None: # DictReader fills missing trailing fields with None
print(f"{hosts_path}:{reader.line_num}: short row", file=sys.stderr)
key = norm(row["team"] or "")
team = teams.get(key)
if team is None:
unmatched.append(row["hostname"])
writer.writerow([row["hostname"], row["ip"], (row["team"] or "").strip(), "UNKNOWN", ""])
continue
used.add(key)
writer.writerow([row["hostname"], row["ip"], row["team"].strip(),
team["owner"], team["channel"]])
if unmatched:
print("hosts with no matching team:", ", ".join(unmatched), file=sys.stderr)
idle = [t["team"] for k, t in teams.items() if k not in used]
if idle:
print("teams with no hosts:", ", ".join(idle), file=sys.stderr)
if __name__ == "__main__":
args = sys.argv[1:] or ["hosts.csv", "teams.csv"]
join(args[0], args[1])

Follow-up questions

  • A team may have several owners in several rows. How does the output change? (One output row per host and owner, or join the owners into one cell.)
  • Make the join key and the columns to copy command-line arguments.
  • The owners live in an API, not a CSV. How do you avoid one request per host?

Frequently asked questions

pandas.merge would do it in one line, and in day-to-day work that is fine. In an interview that says standard library only, the point is to show you understand the join: which side to index, how to normalise keys, what to do with unmatched rows. Knowing this also helps you read what pandas does when it is slow.

Joining exports from a CMDB, a cloud account and an on-call tool is routine infra work: who owns this host, which servers have no owner, which team has nothing in production. Messy keys and quoted fields are normal in those exports, which is why the interviewer includes them.

Sort both files by the join key (for example with sort on disk) and walk them together in a merge join, advancing whichever side has the smaller key. Memory stays constant. Databases use this when a hash join does not fit.