World Cup Data Pipeline

#database #dataengineering #postgres #showdev

How I Built a CSV-to-Postgres Data Pipeline for World Cup Player Stats

The Problem

With excited of watching the World Cup for the past month, I wanted to my own project that would show the stats of all players that have played in the tournament.

One of the major issues that I ran into was that the CSV file I had wasn't really one dataset. It flattened together two different kinds of records. Outfield players have fields like goals, assists, and yellow or red cards. Goalkeepers have saves, clean sheets, and goals conceded instead. Representing both in a single table would mean filling half the columns with meaningless NULL values depending on the player's position.

I wanted something closer to a real data pipeline than a spreadsheet import. The goal was to read the raw CSV, validate it, transform each row into the shape it actually belonged to, store it in a normalized Postgres schema, and expose it through an API instead of treating the CSV as the application's source of truth.

The project deliberately focused on the unglamorous middle of backend engineering: ingesting data, transforming it correctly, storing it efficiently, and serving it reliably.

Schema Design Decisions

The first design decision happened during the transform step. Instead of forcing every record into the same schema, the pipeline checks position === "GK" and routes each row into either a player shape or a goalkeeper shape. Each shape lands in its own normalized Postgres table, which avoids maintaining a table full of columns that only apply to half the records.

The next challenge came from Postgres itself. It has a hard limit of 65,535 bound parameters per query, which means a single giant INSERT eventually stops working as the dataset grows. Rather than inserting everything at once, the pipeline chunks writes into batches: 200 players or 30 goalkeepers per INSERT. Each batch generates its own $1..$N placeholder sequence, keeping every query comfortably below the parameter limit regardless of how large the CSV becomes.

I also wanted the pipeline to tolerate partial failures instead of treating every error as fatal. Player and goalkeeper batches execute through Promise.allSettled, so a failed goalkeeper batch doesn't prevent successful player batches from committing. Within each batch, the pipeline continues attempting later chunks even if an earlier one fails, then reports exactly how many rows were inserted compared to how many were attempted.

That design became even more important once I considered running the pipeline multiple times against refreshed data. After verifying that (name, country) produced zero collisions across all 1,247 rows, I added a unique constraint through a Knex migration and changed inserts to ON CONFLICT (name, country) DO UPDATE. Re-running the pipeline now updates existing rows instead of creating duplicates.

API Architecture

The serving layer is intentionally small. Everything flows through a single Express 5 endpoint: GET /players.

Requests first pass through rate limiting, which allows 100 requests every 15 minutes. After that, the endpoint uses a cache-aside strategy backed by Redis with a one-hour TTL. The first request reads from Postgres and populates Redis. Every subsequent request during that hour reads directly from the cache instead of hitting the database again.

The part that required the most thought wasn't caching itself—it was making sure the cache couldn't become a single point of failure.

try {
  // Redis cache operations
} catch (error) {
  // Fall back to Postgres
}

Redis operations are isolated inside their own try/catch, separate from the Postgres query. If Redis becomes unavailable, the endpoint doesn't return a 500 error. Instead, it skips the cache, serves data directly from Postgres, logs the Redis failure, and continues responding normally. Losing the cache should never mean losing the API.

That same philosophy of graceful degradation shows up elsewhere in the project. The ingestion pipeline is designed to continue processing remaining chunks after individual failures, and the API is designed to continue serving data when an infrastructure component disappears. Both decisions favor availability over an all-or-nothing approach.

Keeping the serving layer intentionally small also helped keep responsibilities clear. The pipeline owns reading, validating, transforming, and storing the data. The API owns exposing that data efficiently, with Redis acting as a performance optimization instead of becoming a dependency the application cannot function without.

The Debugging Rabbit Hole

The bug that taught me the most wasn't dramatic. Nothing crashed. Nothing printed an error.

After refactoring row-by-row inserts into batch inserts, I built the values array, generated the placeholder groups using the correct offset math (base = index * columns.length), and saw success messages in the console. The pipeline reported that player data had been stored successfully, and there were no visible failures.

Except the database hadn't changed.

I compared row counts before and after running the entire pipeline, and they were identical. The SQL was built correctly. The values array was correct. The placeholder math was correct.

The problem was almost embarrassingly simple: after constructing the query, I never actually called pool.query(). The function finished immediately after building the .map(). Nothing threw an exception because nothing that could have thrown was ever executed.

The lesson that stuck is that logs only prove your function returned—they don't prove your data changed.

If you're writing a data pipeline, verify the state of the database itself. Check row counts. Run a SELECT. Trust what the database confirms, not what your console prints.

Outcome

The finished project is deployed on Render, backed by Supabase Postgres and a Redis cache. It includes a test suite of 19 Jest and Supertest tests running against an isolated Postgres test schema so production data is never touched. The suite covers real constraint failures, per-chunk failure behavior, and the ON CONFLICT upsert path.

The pipeline is now operational. Running it against a refreshed CSV updates existing records instead of inserting duplicates, making repeated imports a normal workflow rather than something to avoid.

You can try the live project here:

This became Portfolio Project #2 on my self-taught software engineering roadmap. The goal wasn't to build another CRUD application—it was to build something with a completely different shape: ingest, transform, chunked storage, caching, validation, and graceful degradation working together as a data pipeline. That difference is exactly what satisfies the Week 16 milestone of shipping two backend projects that tell different technical stories.

More than anything else, this project reinforced one idea I'll carry into every future backend system: when software writes data, the database—not the logs—is the ground truth.