Part 1: The Lifeboat
This was supposed to a routine daily standup call:
Sorry folks — for the last three days I've been buried in urgent spreadsheet fixes and made no progress on the release feature.
Said one of the developers on my team.
My worries about next week's release suddenly felt insignificant compared to a problem that had been quietly compounding for a couple of years.
The next hour organically turned into an unplanned and horrifying discovery session.
Where does the data come from, how is it processed, who checks it's correct?
How large is the system and what is dependant on it?
Why is exactly one person holding all of it?
The company was drowning in constantly multiplying Google Spreadsheets.
Over 200 of them.
All with different logic and all pinned on a single developer's "leftover capacity".
Three years earlier that setup was a correct solution for a small problem at the time. But appetites grew with time and the approach started to collapse under its own weight.
Promising a Perfect Data Warehouse in a situation like this sounds tempting.
But It helps nobody, especially the person currently getting bombarded with small feature requests, column renames and minor formula fixes.
Instead I had to lead with a feasible pitch:
I worry about the data being trustworthy and fresh. Everyone else builds whatever charts they need, in parallel
For the work to begin, I needed a Proof Of Concept. Small enough to ship yesterday and convincing enough to become a leverage point in the adoption conversations that would follow.
A lifeboat 🛟
First table matters
From the diverse range of data targets I picked the hardest of the easy: User Profiles.
It was the core of the business but also carrying PII (Personally Identifiable Information).
Which meant if I manage to make it right with this table, the approach would hold everywhere else. Just one chart that can be filtered and edited and recreated as different representations of live data would be enough.
In theory.
Problem of having an uneatable cake
The reality did not ask to wait long - the production Postgres lived on a VPS, inside an internal and isolated Docker network, with no way to reach it from outside (except via direct ssh tunnel of course). And it was plain hosting, with no network products available to connect 2 VPS machines over secured local network directly.
Every clever CDC idea I had at the time broke against this limitation. And after several consultations with other developers in my field and a few attempts to pull off a "pull" architecture (pun intended), the options looked like this:
A remote replica over the open internet - fragile since my team had a very limited knowledge of pg replication. And a security headache, given there's no private network to put it on. There was an idea to wrap it into an ssh tunnel - but that was essentially scrapped as being too complex for the team to support.
Opening the database port to an outside connection - which would mean strict firewall rules on both servers, and one human error on some future day could leak the entire production database.
Extending the existing job - there was already a giant container stuffed with Python scripts and raw SQL running next to production. Continuing feeding that monster was not in anyone's best interest.
At this point I gave in to a conventional wisdom:
If the door won't open when you pull, you push.
Step 1: simple push
What is the simplest, boring and a versatile tool that holds together the whole internet?
Bash.
So I wrote a bash script to run inside the secure network. It would dump the tables I need straight out of Postgres with COPY ... TO STDOUT, gzip them with a timestamp in the filename, and rsyncs the result to the analytics box.
Two details that matter more than they look:
The timestamp in the filename. users_profile20251203_020000.csv.gz carries exact time of when it was exported in its name. That single fact will become the backbone of freshness checks later - the pipeline should know how old its data is without asking the source anything.
PII never leaves secure server. The script asks information_schema for each table's columns and drops the excluded ones before building the COPY. Yes the team would have to manually keep the list up to date with every released feature. But even this chore-like approach is much better than nothing.
Not an elegant or modern solution by a long shot - but a solidly working one and fast.
Step 2: your troop need an orchestrator (Mage.ai)
CSVs appearing on a server is not a pipeline. I needed an orchestrator that my team could actually operate without me standing behind them. Hence my requirements:
Clear UI They should see what a pipeline does or where it broke
Notebook-style iteration Ability to iterate within the tool is priceless for the result
Templates saving working pieces for reuse is how we get from more work to less
We picked Mage.ai, and honestly it was love at first sight for the team.
The first data-loader block just finds the freshest export for a table and stamps it with the export time. This later became a load-bearing template every table reuses:
Step 3: start thinking in columns
Now came the time to pick a database to hold all of the analytics.
And to not regret this choice after a year.
Postgres was already doing its actual job - serving the production app and reliably accumulating new data. For this reason Postgres is row-oriented.
Analytical loads will almost entirely consist of aggregations - so even for the PoC column-oriented is the way to go.
All cloud warehouses were out of the roster since the whole stack was self-hosted, and the company wasn't ready to handle pay per query conversations.
ClickHouse became a winner pretty quick on all points that mattered here:
Free, self-hosted, open source
Columnar storage - wide and sparse tables should pose any issues
Batch ingestion - should be an easy task, if i can avoid any in place edits later
dbt-native - should make writing and supporting transformations manageable
Export in Mage would have to be idempotent and slim:
With the exception of slightly different SQL dialect(especialy around subqueries) and being RAM hungry - this will make a perfect database engine.
Step 4: visualize
For the final piece of the puzzle I picked Apache Superset. Which can be self-hosted from their official Docker image. Full control, no SaaS price tag.
This choice brought 2 pieces of wisdom with it.
First, some driver plumbing will be required. Without clickhouse-connect being present in the app container "Driver missing" is all that you will see. So either patch a Dockerfile or for a faster way to get it running just for now:
Second wisdom came with a taste of failure. The default config ships with SUPERSET_SECRET_KEY = 'TEST_NON_DEV_SECRET'.
Do not leave that in place.
Trust me and my first superset hosting experience. I got lucky with a decent person notifying me of it, and not having anything of value in the server just yet(and relying on never revealing PII). Otherwise gaining admin access to superset will mean gaining access to the Clickhouse.
Generate a real SUPERSET_SECRET_KEY via openssl rand command and treat it like the credential it is.
2 days later
Within two days of that stand-up, I already had a link to show. It was just a distribution of pupils based on the region. Not a huge deal on paper. But the first time, when the representation could be tweaked by anyone with access.
The Spreadsheet Era wasn't over but the new solution was already afloat.
Most importantly word started to spread and people started to play around with data, creating different looking charts, showing to colleagues and generating demand.
And with demand came the most stubborn question:
Why these numbers look a bit wrong?
And I honestly had no proper answer. Just yet.
Comments