Every data engineer knows this pain. Someone drops a CSV in a shared folder on Friday evening. Dates are in three different formats, amount column has Rs written inside it, one customer is repeated twice, and the status column says Dlivered. Then you spend two hours cleaning it with the same pandas code you wrote last month, slightly modified.
So I asked myself, why not build an agent which watches the raw folder, checks every new file, fixes what can be fixed, throws what cannot be fixed into a quarantine folder, and finally pushes clean data to silver and business-ready data to gold? Claude is good at reading a data quality report and deciding what to do about it, so I used the Claude API as the brain.
In this blog I will show you the complete build, step by step. We will use only Python, pandas, watchdog and the Anthropic SDK. No Spark, no Airflow, nothing heavy. You can run it on your laptop today and later move the same logic to your cloud setup.
What we are building
The flow is very simple. The moment a new file lands in the raw folder, this whole thing runs on its own:
data/raw/orders.csv (new file arrives)
|
v
[1] CHECK -> schema, nulls, duplicates, types, dates, allowed values, ranges, email format
|
issues found?
| |
no yes
| |
| v
| [2] CLAUDE reads the issue report and returns a FIX PLAN
| |
| v
| [3] PYTHON applies the plan (only whitelisted operations)
| |
| v
| [4] CHECK again (max 3 rounds)
| |
| still broken? -> data/quarantine + report (human needed)
| |
v v
data/silver/orders_clean.csv (clean and validated)
|
v
[5] CHECK AGAIN from disk (second gate)
|
v
data/gold/orders_daily_sales.csv (business-ready)
The golden rule of this design is: AI proposes, code verifies. Claude never touches your files directly. It only suggests fixes. Our own Python code applies them and then re-checks everything. If Claude makes a mistake, the checks will catch it.
Raw, Silver and Gold in simple words
This is called the medallion architecture and most data teams use some version of it.
- Raw: the file exactly as it came from the source. We never edit it. If something goes wrong, we can always come back here.
- Silver: cleaned and validated data. Correct types, no duplicates, standard formats, every rule passed.
- Gold: business-ready data. Aggregated or modelled for dashboards and reports, for example daily sales per city.
We also keep two extra folders: quarantine for files or rows that could not be fixed, and logs where the agent writes a small report for every file it processes.
Why Claude should not run code directly
A lot of tutorials let the model write pandas code and then exec() it. Please do not do this with real data. One bad line and your data is gone, or worse, silently wrong.
Instead we give Claude a fixed menu of operations, like strip_whitespace, parse_dates, drop_duplicates and so on. Claude returns a plan in structured JSON using tool use, and our code runs only those operations. It is safer, easier to audit, and you can add a new operation whenever you need.
Also, we never send the full file to the API. We send only a profile: row count, null counts, a few example values per column and the list of issues. It is cheaper and much better for privacy.
Prerequisites
- Python 3.9 or above
- Anthropic API key from console.anthropic.com
- Basic pandas knowledge
Step 1: Create the project and folders
mkdir data-agent
cd data-agent
python -m venv venv
source venv/bin/activate
pip install anthropic pandas watchdog
export ANTHROPIC_API_KEY='your-key-here'
The folders get created automatically by the config file in the next step. Final project structure will look like this:
data-agent/
config.py
rules.py
checks.py
fixer.py
agent.py
gold.py
pipeline.py
watcher.py
data/
raw/ silver/ gold/ quarantine/ archive/ logs/
Step 2: config.py
from pathlib import Path
BASE = Path('data')
RAW = BASE / 'raw'
SILVER = BASE / 'silver'
GOLD = BASE / 'gold'
QUARANTINE = BASE / 'quarantine'
ARCHIVE = BASE / 'archive'
LOGS = BASE / 'logs'
for folder in (RAW, SILVER, GOLD, QUARANTINE, ARCHIVE, LOGS):
folder.mkdir(parents=True, exist_ok=True)
MODEL = 'claude-sonnet-5-5'
MAX_FIX_ROUNDS = 3
Step 3: rules.py (tell the agent what good data looks like)
This is the most important file. The agent can only check what you define here. I am using an orders file as example. Change the columns and rules for your own dataset.
RULES = {
'required_columns': ['order_id', 'customer_name', 'email', 'order_date', 'amount', 'status', 'city'],
'unique': ['order_id'],
'not_null': ['order_id', 'order_date', 'amount'],
'dtypes': {'amount': 'float', 'order_date': 'date'},
'allowed_values': {'status': ['placed', 'shipped', 'delivered', 'cancelled', 'returned']},
'ranges': {'amount': [1, 500000]},
'regex': {'email': r'^[^@ ]+@[^@ ]+[.][^@ ]+$'},
}
Step 4: checks.py (the checker)
This is plain pandas, no AI. It returns a list of issues. Each issue has the check name, the column, how many rows are affected and a few sample values, so Claude can understand the problem properly.
import pandas as pd
def run_checks(df, rules):
issues = []
def add(check, column, mask, detail):
mask = mask.fillna(False).astype(bool)
count = int(mask.sum())
if count:
issues.append({
'check': check,
'column': column,
'count': count,
'detail': detail,
'sample_values': df.loc[mask, column].astype(str).head(5).tolist(),
'row_numbers': df.index[mask].tolist()[:5],
})
missing = [c for c in rules['required_columns'] if c not in df.columns]
if missing:
issues.append({'check': 'missing_columns', 'column': None, 'count': len(missing),
'detail': f'Missing columns: {missing}', 'sample_values': [], 'row_numbers': []})
return issues
if df.empty:
issues.append({'check': 'empty_file', 'column': None, 'count': 1,
'detail': 'File has no rows', 'sample_values': [], 'row_numbers': []})
return issues
# 1. extra spaces in any column
for col in df.columns:
s = df[col].astype('string')
add('extra_whitespace', col, s.notna() & (s != s.str.strip()), 'Leading or trailing spaces')
# 2. nulls in required columns
for col in rules['not_null']:
blank = df[col].isna() | (df[col].astype('string').str.strip() == '')
add('null_values', col, blank, f'{col} is empty but required')
# 3. duplicates
for col in rules['unique']:
add('duplicate_keys', col, df[col].notna() & df.duplicated(subset=[col], keep='first'),
f'Repeated values in {col}')
add('duplicate_rows', df.columns[0], df.duplicated(keep='first'), 'Fully identical rows')
# 4. data types
for col, kind in rules['dtypes'].items():
if kind == 'float':
converted = pd.to_numeric(df[col], errors='coerce')
add('bad_number', col, df[col].notna() & converted.isna(), f'{col} must be a plain number')
if kind == 'date':
converted = pd.to_datetime(df[col], errors='coerce', format='%Y-%m-%d')
add('bad_date', col, df[col].notna() & converted.isna(), f'{col} must be YYYY-MM-DD')
# 5. allowed values
for col, allowed in rules['allowed_values'].items():
add('invalid_category', col, df[col].notna() & ~df[col].isin(allowed),
f'{col} must be one of {allowed}')
# 6. numeric ranges
for col, (low, high) in rules['ranges'].items():
num = pd.to_numeric(df[col], errors='coerce')
add('out_of_range', col, num.notna() & ((num < low) | (num > high)),
f'{col} must be between {low} and {high}')
# 7. pattern checks like email
for col, pattern in rules['regex'].items():
s = df[col].astype('string')
ok = s.str.match(pattern)
add('invalid_format', col, s.notna() & ~ok.fillna(True), f'{col} does not match the expected pattern')
return issues
If you want more checks, like freshness of data, row count compared to yesterday, or foreign key checks, just add another block here.
Step 5: fixer.py (the safe hands)
This file has the only operations that can change data. Notice three guard rails: we never fill key columns, regex and ranges come from our rules and not from the model, and every row we drop is saved so nothing disappears silently.
import pandas as pd
def _cols(p):
return p.get('columns') or ([p['column']] if p.get('column') else [])
def apply_plan(df, operations, rules):
log, rejected = [], []
for op in operations:
name = op.get('op')
p = op.get('params', {})
before = len(df)
try:
if name == 'strip_whitespace':
for c in _cols(p):
if c in df.columns:
df[c] = df[c].astype('string').str.strip().replace('', pd.NA)
elif name == 'standardize_case':
c, how = p['column'], p.get('case', 'lower')
s = df[c].astype('string').str.strip()
df[c] = {'lower': s.str.lower(), 'upper': s.str.upper(), 'title': s.str.title()}[how]
elif name == 'parse_dates':
for c in _cols(p):
d = pd.to_datetime(df[c], errors='coerce', format='mixed', dayfirst=True)
df[c] = d.dt.strftime('%Y-%m-%d')
elif name == 'cast_numeric':
for c in _cols(p):
cleaned = df[c].astype('string').str.replace(r'[^0-9.-]', '', regex=True)
df[c] = pd.to_numeric(cleaned, errors='coerce')
elif name == 'map_values':
c = p['column']
mapping = {str(k).lower(): v for k, v in p['mapping'].items()}
s = df[c].astype('string').str.strip().str.lower()
df[c] = s.replace(mapping)
elif name == 'nullify_invalid_regex':
c = p['column']
s = df[c].astype('string')
ok = s.str.match(rules['regex'][c]).fillna(True)
df[c] = s.where(ok, pd.NA)
elif name == 'fill_null':
c = p['column']
if c in rules['unique'] or c in rules['not_null']:
raise ValueError(f'Guard rail: not allowed to fill required column {c}')
how = p.get('strategy', 'constant')
if how == 'median':
value = pd.to_numeric(df[c], errors='coerce').median()
elif how == 'mode':
value = df[c].mode().iloc[0]
else:
value = p['value']
df[c] = df[c].fillna(value)
elif name == 'drop_rows_null':
mask = df[_cols(p)].isna().any(axis=1)
rejected.append((name, df[mask]))
df = df[~mask]
elif name == 'drop_out_of_range':
c = p['column']
low, high = rules['ranges'][c]
num = pd.to_numeric(df[c], errors='coerce')
mask = num.notna() & ((num < low) | (num > high))
rejected.append((name, df[mask]))
df = df[~mask]
elif name == 'drop_duplicates':
mask = df.duplicated(subset=p.get('subset') or None, keep='first')
rejected.append((name, df[mask]))
df = df[~mask]
else:
log.append({'op': name, 'status': 'skipped: unknown operation'})
continue
log.append({'op': name, 'params': p, 'rows_before': before, 'rows_after': len(df), 'status': 'ok'})
except Exception as e:
log.append({'op': name, 'params': p, 'status': f'failed: {e}'})
return df.reset_index(drop=True), log, rejected
One note: dayfirst=True means 05-11-2026 is read as 5 November, which is the normal Indian format. If your source uses American dates, change it.
Step 6: agent.py (Claude as the decision maker)
Here we send the issue report to Claude and force it to answer through a tool called submit_fix_plan. Forcing the tool means we always get clean JSON back, no parsing headaches.
import json
import anthropic
from config import MODEL
client = anthropic.Anthropic()
ALLOWED_OPS = {
'strip_whitespace': 'params: columns (list)',
'standardize_case': 'params: column, case (lower, upper or title)',
'parse_dates': 'params: columns (list). Any date format to YYYY-MM-DD, unparseable becomes null',
'cast_numeric': 'params: columns (list). Removes currency text and commas, converts to number',
'map_values': 'params: column, mapping (dict of wrong value to correct value)',
'nullify_invalid_regex': 'params: column. Sets values failing the rule pattern to null',
'fill_null': 'params: column, strategy (median, mode or constant), value. Not allowed on required columns',
'drop_rows_null': 'params: columns (list)',
'drop_out_of_range': 'params: column. Drops rows outside the allowed range in the rules',
'drop_duplicates': 'params: subset (list, optional)',
}
FIX_TOOL = {
'name': 'submit_fix_plan',
'description': 'Submit the ordered list of cleaning operations for this file.',
'input_schema': {
'type': 'object',
'properties': {
'summary': {'type': 'string', 'description': 'Two lines on what is wrong and how you plan to fix it'},
'operations': {
'type': 'array',
'items': {
'type': 'object',
'properties': {
'op': {'type': 'string', 'enum': list(ALLOWED_OPS.keys())},
'params': {'type': 'object'},
},
'required': ['op', 'params'],
},
},
'needs_human': {'type': 'array', 'items': {'type': 'string'},
'description': 'Problems that cannot be fixed safely'},
},
'required': ['summary', 'operations'],
},
}
SYSTEM = (
'You are a senior data engineer. You get a data quality report for a CSV file: the rules, '
'a column profile and the issues found. Reply only by calling submit_fix_plan. '
'Use only the allowed operations. Order them sensibly: strip whitespace first, then type '
'conversion, then fix case, then map misspelled values, then nullify invalid formats, then drop rows. '
'Never invent data and never fill required columns. For a required column with bad values, '
'prefer dropping that row. If something cannot be fixed safely, put it in needs_human.'
)
def build_profile(df):
columns = {}
for c in df.columns:
columns[c] = {
'nulls': int(df[c].isna().sum()),
'unique': int(df[c].nunique()),
'examples': df[c].dropna().astype(str).unique()[:6].tolist(),
}
return {'rows': len(df), 'columns': columns}
def get_fix_plan(df, issues, rules):
payload = {
'rules': rules,
'profile': build_profile(df),
'issues': issues,
'allowed_operations': ALLOWED_OPS,
}
response = client.messages.create(
model=MODEL,
max_tokens=2000,
system=SYSTEM,
tools=[FIX_TOOL],
tool_choice={'type': 'tool', 'name': 'submit_fix_plan'},
messages=[{'role': 'user', 'content': json.dumps(payload, default=str)}],
)
for block in response.content:
if block.type == 'tool_use':
return block.input
return {'summary': 'No plan returned', 'operations': [], 'needs_human': ['Model returned no plan']}
Step 7: gold.py (business-ready output)
Gold depends on what your business needs. For orders, I made a daily sales table per city, ignoring cancelled orders. Replace this with your own model.
import pandas as pd
def build_gold(df):
df = df.copy()
df['amount'] = pd.to_numeric(df['amount'])
df['order_day'] = pd.to_datetime(df['order_date']).dt.date
valid = df[df['status'] != 'cancelled']
daily = (
valid.groupby(['order_day', 'city'])
.agg(orders=('order_id', 'count'), revenue=('amount', 'sum'))
.reset_index()
.sort_values(['order_day', 'city'])
)
return daily
Step 8: pipeline.py (the full flow in one place)
This is where everything connects. Read it slowly, because this is the heart of the project.
import shutil
from datetime import datetime
from pathlib import Path
import pandas as pd
from config import SILVER, GOLD, QUARANTINE, ARCHIVE, LOGS, MAX_FIX_ROUNDS
from rules import RULES
from checks import run_checks
from agent import get_fix_plan
from fixer import apply_plan
from gold import build_gold
def write_report(name, status, first_issues, applied, left_issues, needs_human, rejected_count):
lines = [
f'# Data agent report: {name}',
f'Status: {status}',
f'Time: {datetime.now():%Y-%m-%d %H:%M:%S}',
'',
'## Issues found in raw file',
]
lines += [f'- {i["check"]} on {i["column"]}: {i["count"]} rows' for i in first_issues] or ['- none']
lines += ['', '## Fixes applied']
lines += [f'- {a["op"]}: {a["status"]}' for a in applied] or ['- none']
lines += ['', f'## Rows rejected: {rejected_count}']
if left_issues:
lines += ['', '## Issues still open']
lines += [f'- {i["check"]} on {i["column"]}: {i["count"]} rows' for i in left_issues]
if needs_human:
lines += ['', '## Needs a human']
lines += [f'- {n}' for n in needs_human]
(LOGS / f'{name}_report.md').write_text('\n'.join(lines), encoding='utf-8')
def quarantine(path, name, first_issues, applied, left, needs_human, rejected_count):
shutil.copy(path, QUARANTINE / path.name)
write_report(name, 'QUARANTINED', first_issues, applied, left, needs_human, rejected_count)
print(f'[QUARANTINE] {path.name} could not be fixed. See data/logs/{name}_report.md')
def process_file(path: Path):
name = path.stem
print(f'[NEW] {path.name} found in raw')
df = pd.read_csv(path, dtype=str)
first_issues = run_checks(df, RULES)
issues = first_issues
applied, rejected, needs_human = [], [], []
# Check -> ask Claude -> fix -> check again
rounds = 0
while issues and rounds < MAX_FIX_ROUNDS:
if any(i['check'] in ('missing_columns', 'empty_file') for i in issues):
break
rounds += 1
print(f'[AGENT] round {rounds}: {len(issues)} issue types, asking Claude for a fix plan')
plan = get_fix_plan(df, issues, RULES)
needs_human += plan.get('needs_human', [])
df, log, dropped = apply_plan(df, plan.get('operations', []), RULES)
applied += log
rejected += dropped
issues = run_checks(df, RULES)
rejected_count = sum(len(d) for _, d in rejected)
if issues:
quarantine(path, name, first_issues, applied, issues, needs_human, rejected_count)
return
# Silver
silver_path = SILVER / f'{name}_clean.csv'
df.to_csv(silver_path, index=False)
print(f'[SILVER] saved {silver_path.name} with {len(df)} rows')
# Second gate: read back from disk and check again
silver_df = pd.read_csv(silver_path, dtype=str)
final_issues = run_checks(silver_df, RULES)
if final_issues:
quarantine(path, name, first_issues, applied, final_issues, needs_human, rejected_count)
return
# Gold
gold_path = GOLD / f'{name}_daily_sales.csv'
build_gold(silver_df).to_csv(gold_path, index=False)
print(f'[GOLD] saved {gold_path.name}')
# Save every dropped row so nothing disappears silently
if rejected_count:
rows = pd.concat([d.assign(rejected_by=op) for op, d in rejected if len(d)])
rows.to_csv(QUARANTINE / f'{name}_rejected_rows.csv', index=False)
write_report(name, 'SUCCESS', first_issues, applied, [], needs_human, rejected_count)
shutil.move(str(path), ARCHIVE / path.name)
print(f'[DONE] {path.name} moved from raw to archive')
See how the raw file is never edited? We read it, work on a copy in memory and finally archive it. That gives you a full audit trail.
Step 9: watcher.py (trigger the moment a file lands)
This is the part which makes it feel like a real agent. As soon as a file appears in the raw folder, the pipeline starts. We also wait until the file size stops changing, otherwise we might read a half-copied file.
import time
from pathlib import Path
from watchdog.observers import Observer
from watchdog.events import FileSystemEventHandler
from config import RAW
from pipeline import process_file
def wait_until_stable(path, gap=2):
last = -1
while True:
size = path.stat().st_size
if size == last:
return
last = size
time.sleep(gap)
class RawHandler(FileSystemEventHandler):
def handle(self, location):
if not location.endswith('.csv'):
return
path = Path(location)
wait_until_stable(path)
try:
process_file(path)
except Exception as e:
print(f'[ERROR] {path.name}: {e}')
def on_created(self, event):
if not event.is_directory:
self.handle(event.src_path)
def on_moved(self, event):
if not event.is_directory:
self.handle(event.dest_path)
if __name__ == '__main__':
for existing in RAW.glob('*.csv'):
process_file(existing)
observer = Observer()
observer.schedule(RawHandler(), str(RAW), recursive=False)
observer.start()
print(f'Watching {RAW} ... press Ctrl+C to stop')
try:
while True:
time.sleep(1)
except KeyboardInterrupt:
observer.stop()
observer.join()
Step 10: Test it with a dirty file
Start the watcher in one terminal:
python watcher.py
Now in another terminal, create a deliberately dirty file in data/raw. I put almost every problem in it: extra spaces, mixed date formats, Rs inside amount, duplicate row, broken email, empty amount, invalid date, spelling mistake in status, a crazy amount and wrong casing.
order_id,customer_name,email,order_date,amount,status,city
1001, Rahul Sharma ,rahul@gmail.com,05-11-2026,1250.50,Delivered,kota
1002,PRIYA VERMA,priya@gmail,2026-11-04,Rs 999,shipped,Jaipur
1002,PRIYA VERMA,priya@gmail,2026-11-04,Rs 999,shipped,Jaipur
1003,Amit Singh,amit@gmail.com,2026/11/03,,placed,Delhi
1004,Neha Gupta,neha@gmail.com,not available,450,Dlivered,Mumbai
1005,Karan Mehta,karan@gmail.com,02-11-2026,9999999,cancelled,pune
1006,Sana Khan,sana@gmail.com,2026-11-01,1800,DELIVERED,Kota
1007,Vikram Rao,vikram@gmail.com,31-10-2026,320.75,Returned,Hyderabad
Save it as orders.csv inside data/raw. You should see something like this in the watcher terminal:
[NEW] orders.csv found in raw
[AGENT] round 1: 9 issue types, asking Claude for a fix plan
[AGENT] round 2: 2 issue types, asking Claude for a fix plan
[SILVER] saved orders_clean.csv with 4 rows
[GOLD] saved orders_daily_sales.csv
[DONE] orders.csv moved from raw to archive
The exact counts can differ a little, because the model may group its fixes differently, and that is fine. What matters is that the final result passes all the checks. In my run, the outcome was:
- Silver had 4 clean rows (1001, 1002, 1006, 1007), with trimmed names, ISO dates, numeric amounts, lowercase status and properly cased cities. The broken email priya@gmail became empty instead of staying wrong.
- Quarantine had a file with 4 rejected rows: the duplicate of 1002, the row with empty amount, the row with invalid date and the row with the 9999999 amount.
- Gold had daily revenue and order count per city.
- Logs had a markdown report listing what was found and what was fixed.
If the file has a problem the agent cannot fix, for example a missing column, it goes to quarantine with a report saying why, and the silver and gold folders stay untouched. That is exactly how it should behave.
How to use it for your own data
- Copy rules.py and write the rules for your dataset: columns, keys, allowed values, ranges.
- Change gold.py to produce the table your business actually needs.
- If you have many datasets, keep one rules file per dataset and pick it using the file name, for example orders_*.csv uses the orders rules.
- Need a new type of fix, like splitting a column or converting currency? Add one more operation in fixer.py and one line in ALLOWED_OPS. Claude will start using it automatically.
Things to keep in mind before going to production
- Privacy. We send only the profile and a few sample values, but even samples can contain names or phone numbers. Mask sensitive columns in build_profile before sending. If your data is highly confidential, check your company policy first.
- Never let rows vanish. Every dropped row goes to the rejected file. Someone should look at that folder regularly, because a repeated rejection pattern usually means a problem at the source system.
- Do not trust, verify. The checks run after every fix and again on the silver file from disk. Keep this double gate always.
- Keep the round limit. MAX_FIX_ROUNDS stops the agent from looping forever and wasting API cost.
- Cost. One file costs only one to three small API calls because we send a profile, not the data. Even for big files the cost stays almost the same.
- Better storage. Use Parquet instead of CSV for silver and gold once your data grows. It is smaller, faster and keeps data types.
- Bigger scale. For huge files, run the same logic on Spark or DuckDB, and trigger it with S3 events, Airflow or a simple cron job instead of watchdog. The agent part stays the same.
- Alerts. Send a Slack or email message when a file goes to quarantine. A silent quarantine folder is a ticking bomb.
- Idempotency. Use unique file names or a processed-files table so the same file is never processed twice.
Mistakes I made while building this
First, I let the model write pandas code. It worked ten times and on the eleventh time it dropped a column. That is why we moved to a fixed operation menu. Second, I forgot dayfirst and a whole month of dates got swapped silently, so test with real dates from your source. Third, I sent 50 sample rows in the first version, and the bill and the privacy risk both went up for no benefit. A small profile is enough.
Final thoughts
This agent will not replace a data engineer. Somebody still needs to define the rules, understand the business and decide what is gold. But the boring 80 per cent, the trimming, the date fixing, the duplicate hunting and the report writing, can now happen by itself the moment a file lands. Your time goes to the real problems.
Start with one dataset, run it for a week, look at the quarantine folder every day, and keep improving the rules. You will be surprised how quickly it becomes a trusted part of your pipeline.
If you get stuck anywhere in the setup, comment below and I will try to help. Happy cleaning!






Leave a Reply