Skip to content
All projects

Case study · Data Engineering · AI Engineering · Architecture · Forward Deployed

AI-Powered Data Lake: Claude + MCP Server

Medallion-architecture data lake that ingests CRM, ERP and API data, uses Claude to advise on data quality, generate SQL and business insights, and exposes the lake to agents through an MCP server

The problem

Customer data lives in a CRM, an ERP and messaging APIs, each with its own format and its own quality problems. Nobody trusts a number until someone cleans it by hand.

Constraints

  • Sources arrive as JSON, CSV and API responses, with duplicates and missing fields
  • Everything on AWS, created from code so it can be rebuilt in one step
  • AI has to help without becoming a black box: every AI step can run in mock mode and is covered by tests

Architecture and decisions

Click a box to see why it was chosen.

AI-Powered Data LakeSources
CRMHubSpot, Salesforce
ERPSAP exports
APIs & eventsWhatsApp, webBronze → Silver
S3 Bronzeraw, as received
Glue + PySparkclean, dedupe, UTC
Quality checksschema + rulesGold
dbt on Athenadaily metrics
KPIs & reportsrevenue, growth
CloudFormationS3, Glue, AthenaAI layer
Claude Sonnetprofiling, NL → SQL
Claude Opusbusiness insights
MCP serveragents query lake

Why · S3 Bronze

Raw data is kept exactly as received, so any bad transform can be replayed from the original.

Results

automated tests
92
automated tests
layers: Bronze, Silver, Gold
3
layers: Bronze, Silver, Gold
source types: CRM, ERP, API, CSV
4
source types: CRM, ERP, API, CSV
CloudFormation stack for all AWS resources
1
CloudFormation stack for all AWS resources

What I would do next

  1. 01Add evals for natural language to SQL: compare results of generated queries against hand-written ones
  2. 02Schedule the Bronze to Silver job with alerts on failed quality checks
  3. 03Write a data contract per source so upstream changes fail fast