Skip to content
Devansh Soni
All posts

Building a Data Lake using AWS

TODO: I Built a Data Lake on AWS From a Pile of Excel Sheets. Here's Everything That Broke.

I Built a Data Lake on AWS From a Pile of Excel Sheets. Here's Everything That Broke.

Most "data lake" blog posts start at the architecture diagram. Mine started at a single .xlsx file with 14 tabs, three financial years of mixed formats, merged header cells, and a "Grand Total" row sitting at the bottom of half the sheets like it belonged there.

That's the honest version of the problem. Someone in finance had been maintaining reports in Excel for years — facilitator reports split by state, invoice registers, a detailed P&L, purchase registers, sales reports customer-wise, aged receivables — one tab per report, one tab per financial year. Everything worked, as long as the only question you ever asked was "open the file and look."

The moment you want to ask across the file — how much did we spend by region compared to what we billed, year over year — you're stuck. There is no query layer. There's a human with a mouse and a VLOOKUP.

So the brief was simple to state and annoying to execute: turn this into something you can run SQL against.

The idea, and why medallion

I didn't want to just dump CSVs into a bucket and call it a lake. Dumping files into S3 is how you get a data swamp — nobody knows which file is authoritative, nobody knows what's been cleaned, and six months later there are four versions of the same report with slightly different row counts.

The medallion architecture solves this by being boring on purpose. You split your storage into layers, and each layer has exactly one job:

Bronze — raw, as-it-landed, untouched. If the source system lied to you, bronze faithfully records the lie. You never delete from bronze. It's your ability to reprocess everything from scratch when you inevitably discover your transformation logic was wrong.

Silver — cleaned, typed, deduplicated, conformed. One row per real-world thing. Column names that a human would guess. This is where the actual work lives.

Gold — aggregated, business-facing. The tables an analyst or a dashboard queries directly. Pre-joined, pre-summed, denormalised for speed.

The reason this matters isn't purity. It's that when a number looks wrong on a dashboard, you can walk it backwards — gold to silver to bronze — and find exactly which layer introduced the error. Without layers, every bug investigation is a full re-read of one giant script.

Now, a confession about my own implementation: I split bronze into two sub-stages. I called them pseudo-bronze and bronze.

Pseudo-bronze is the raw .xlsx sitting in S3, exactly as the finance team sent it. Bronze is the same data after it's been flattened into one clean CSV per report — headers stripped, subtotal rows removed, columns normalised — but with zero business logic applied. No joins, no derived metrics, no re-aggregation.

Purists will say that flattened layer is really silver. They're not wrong. But an Excel sheet isn't a table — it's a picture of a table with decorative junk around it. Getting from "picture of a table" to "actual table" isn't cleaning, it's parsing. Calling it bronze felt more honest, because I still hadn't made a single decision about what the data means.

The flow

Excel workbook (14 sheets)
        │
        ▼
  S3 — pseudo-bronze / raw
        │
        ▼
  AWS Glue job (Python Shell)  ── openpyxl + pandas
        │
        ▼
  S3 — bronze / processed
   └── one subfolder per table, one CSV inside
        │
        ▼
  AWS Glue Crawler  ── infers schema, writes to Data Catalog
        │
        ▼
  Amazon Athena  ── SQL over S3, 14 queryable tables

The services, and why each one

S3 is the lake. Not a metaphor — the storage layer genuinely is just object storage with a folder convention. This is the whole trick of a lakehouse: your data is cheap flat files, and the "database" is a layer of metadata pointing at them.

AWS Glue (Python Shell) runs the ETL. Glue gives you two flavours: Spark jobs and Python Shell jobs. Spark is the default reach, and it was completely wrong here. My dataset is thousands of rows, not billions. Spinning up a distributed cluster to process a spreadsheet is like chartering a freight train to move a sofa — slower, more expensive, and now you have a train to maintain.

I ran the Python Shell job at 0.0625 DPU — the smallest allocation available. Plain pandas and openpyxl in a managed runtime. Cost per run rounds to nothing.

Glue Crawler + Data Catalog is the piece that turns files into tables. The crawler walks your S3 prefixes, infers column names and types, and registers a table definition in the catalog. That catalog is what makes the files queryable.

Athena is the query engine. Serverless Presto — you point it at the catalog and write SQL. No cluster, no warehouse, pay per byte scanned.

The thing worth internalising: the data never moves into a database. Athena reads the CSVs in S3 directly at query time. The "database" is purely metadata. That's the lakehouse model, and once it clicks, a lot of architecture stops feeling magical.

Now the part nobody blogs about: everything that broke

1. My Athena tables returned zero rows

I wrote all 14 CSVs flat into one prefix: bronze/processed/sales_report.csv, bronze/processed/purchase_register.csv, and so on. Ran the crawler. It created tables. I ran a SELECT *. Empty.

Here's the thing about Athena that the docs mention once and never emphasise: a table's location is a folder, not a file. Athena reads everything under that prefix. Point 14 tables at the same folder and each one tries to parse all 14 files as if they shared its schema. You get empty results, or worse, silent garbage.

Fix: one subfolder per table.

bronze/processed/sales_report_fy26/sales_report_fy26.csv
bronze/processed/purchase_register_fy26/purchase_register_fy26.csv

Genuinely one of the hardest bugs in this project, purely because nothing errors. No exception, no warning. Just a well-formed empty result set. I lost most of a day to it.

2. My Glue job parameters silently didn't exist

I'd written the script to take --input and --output. Glue's getResolvedOptions throws if a requested key isn't present in the job config — and my try/except fallback quietly swallowed it and used the hardcoded default paths.

So the job "succeeded." It just wrote to a bucket path I wasn't looking at.

Two lessons. Name Glue parameters to match your job config exactly (--S3_INPUT_PATH, --S3_OUTPUT_PATH). And never wrap config resolution in a bare except — a fallback that hides a misconfiguration is worse than a crash, because a crash tells you the truth immediately.

3. A sheet name had two spaces in it

"CRM_Detailed P&L report FY26". Two spaces between "Detailed" and "P&L". Invisible in Excel's tab bar. Instant KeyError in openpyxl.

Now I normalise every sheet name before lookup — collapse whitespace, strip, match case-insensitively. Never trust a string a human typed into a UI.

4. Hyphens in the Athena database name

I named the database with hyphens. Then SHOW TABLES IN my-bronze-db failed with a parse error, because Presto reads the hyphen as minus.

Fix is trivial — double-quote it, "my-bronze-db" — but it taints every single query you write forever. If I were starting again: underscores only, everywhere, no exceptions.

5. I over-engineered v1 and had to throw it away

My first ETL script was proud of itself. Every output row carried _source_fy, _source_state, _etl_timestamp, and a family of flag_* columns marking rows where a date failed to parse or an amount looked suspicious. Full lineage. Very responsible.

Then I got the reference output — what the clean tables were actually supposed to look like — and none of that was in it. Fourteen tables of pristine business columns.

I'd solved a problem nobody had. Lineage metadata is genuinely valuable, but it belongs in a sidecar — a separate audit table keyed back to the row — not smeared across every business table where it pollutes every SELECT * and confuses every downstream consumer.

I rewrote it clean. The v1 script still sits in the repo as a reminder.

6. Dates were inconsistent, and I chose not to fix it

Two sheets used ISO YYYY-MM-DD. Every other sheet used DD-MM-YYYY. My instinct was to normalise everything to ISO — obviously correct, right?

Wrong, at this layer. Bronze's job is fidelity, not opinion. If I silently reformat dates in bronze and someone later reconciles my output against the original workbook, the mismatch looks like a data bug rather than a deliberate transformation. Date standardisation is a silver decision. Bronze preserves what it was handed.

Same call on a column that exists in the FY26 sheet but not FY25, and on a Quantity column that's 100% empty in both years but only dropped in one. Preserved as-is. Fidelity beats tidiness at this layer.

7. Three fields crammed into one cell

Several sheets had a single column holding "12 March 2025 / Melbourne / Northside High School". Date, location, entity — one string, slash-delimited.

So I wrote a parser. Then reality happened:

  • rows with no slash at all
  • two-digit years (25 — is that 2025 or 1925?)
  • invoice numbers glued to the front with no separator (INV004212 March 2025)
  • dates with an ordinal suffix (12th March)
  • rows where the date was just missing

The parser I ended up with tries the clean split first, then falls back to a regex date extraction, then falls back to keeping the whole string intact and leaving the date null. A parser without a graceful degradation path isn't a parser, it's a crash waiting for the right input.

I also carry a "last seen year" through the sheet so 2-digit years can inherit context from the rows around them.

8. Excel decoration masquerading as data

Report title rows. Blank spacer rows. Subtotal rows in the middle of the data. A "Grand Total" row at the bottom that a naive SUM() would double-count. Category labels sitting in the same column as values. Count suffixes appended to names like Northside High School (5).

None of that is data. All of it parses cleanly into your dataframe and quietly corrupts every aggregate you build on top.

9. The reference file had its own bug

While validating my output cell-by-cell against the reference, 12 of 14 tables matched exactly. Two didn't — and only on one column, for 5 rows out of 26. In those rows the reference had leaked an account code and a contact name into the entity-name field.

The reference was wrong. Mine was right.

That's an uncomfortable place to be, and the right move is not to silently "fix" it in either direction. I documented the discrepancy, kept the correct value in my output, and flagged it. Being bug-compatible with an upstream artefact is a decision someone else needs to make, not one you make quietly in a script.

10. The thing that actually saved me: a local twin

Early on, every iteration meant uploading a script to S3, triggering the Glue job, waiting for a cold start, reading CloudWatch logs, finding a typo. Five-plus minutes per attempt.

So I built a second copy of the script with identical transformation logic that reads and writes the local filesystem — argparse instead of getResolvedOptions, no boto3. Same functions, same parsing, different I/O boundary.

Iteration loop went from five minutes to two seconds. I'd get the logic completely right locally, verify row counts matched, then push to Glue as the last step.

The generalisable lesson: isolate your I/O at the edges so the core logic can run anywhere. If your transformation functions take a dataframe and return a dataframe, they're testable in a REPL. If they take an S3 URI, you're debugging in the cloud forever.

11. Assorted small ones

  • Scientific notation. Large integer reference numbers written to CSV get re-read by Excel as 4.21E+09. Fix: csv.QUOTE_ALL so every field is quoted and read as text.
  • Formula cells. openpyxl returns the formula string, not the value, unless you pass data_only=True.
  • Crawler staleness. Change your output schema and forget to re-run the crawler, and Athena queries an old table definition against new files. Confusing failure mode. Re-run the crawler as part of the deploy, not as an afterthought.

Where it stands

Fourteen clean, query-ready bronze tables in Athena. One SQL query now answers questions that used to mean opening a workbook and scrolling. I wrote a data-quality check suite alongside it — row counts per table, boolean/None strings leaking into text columns, empty date fields, non-numeric values in amount columns, unexpected values in categorical fields.

That last part matters more than it sounds. A pipeline without automated quality checks is a pipeline you don't actually trust, you just haven't been burned by it yet.

What's next, honestly:

  • Silver layer. Date standardisation, consistent column naming across FY25/FY26, proper typing.
  • Parquet instead of CSV. Columnar, compressed, and Athena charges by bytes scanned — this is a direct cost and speed win.
  • Partitioning by financial year so queries prune instead of full-scanning.
  • Orchestration. Right now the raw file lands manually. A Glue Workflow or an EventBridge trigger on S3 upload turns this from a script into a pipeline.
  • A natural-language query layer on top, so people who need the answers don't have to write the SQL.

What I'd tell myself at the start

Underscores in every name. Databases, tables, columns, S3 prefixes. One hyphen taints every query downstream.

Build the local twin first. The hours spent making your logic runnable outside the cloud pay back within the first afternoon of debugging.

Bronze means bronze. Every time I felt clever and "improved" the data in the raw layer, I made reconciliation harder later. Fidelity first, opinions later.

Silent success is the most expensive failure mode. The empty Athena tables, the swallowed config exception, the stale crawler schema — none of these threw. They all returned plausible-looking nothing. Design your pipeline so wrong states are loud.

The architecture is the easy part. S3 → Glue → Athena is a diagram you can draw in two minutes. The other 95% of the work is the two spaces in a sheet name, the slash-delimited cell, and the Grand Total row that quietly doubled your revenue.