*From the “Data but Make it Personal” series – Part 5
A SQL Project Survival Story
When you’re in training, you don’t choose your projects – they choose you. Every week brings a new client, a new challenge, and a new opportunity to grow (or panic). This week, the “client” was one of our very own coaches. He threw us some curved balls that made us scratch our heads and re-think our life choices. But, hey! This is not a tragic story, we actually ended up learning a lot and growing as data analysts.
This blog is a little different from my usual ones. If you’ve followed this series, you know I like to connect data concepts with real-life stories. This time, though, the story was the data – and it almost broke us. The purpose of writing this story is not just to complain and blow off some steam (which, spoiler, I do), but also to help me retrace my steps and let everything I learned sink in.
It was supposed to be a SQL project. Just some light querying, maybe some JOINs, some GROUP BYs here and there, right? Right?! Instead, we were thrown head-first into the world of audit logs, alert systems, triggers and event pipelines – systems-level stuff that none of us were really prepared for. SQL? Sure. But this was SQL + system architecture + chaos.
Let’s talk about what went down.
A Dataset That Lied to Us
We were given five tables – customers, products, orders, returns, and something else I’ve probably mentally repressed. Being extraordinary analysts, it didn’t take us long to realize that the data was…weird.
One client with multiples addresses, many purchases with just one product id, order totals that didn’t match the price of the products in them. We spent hours trying to figure out what it all meant. What were we supposed to do with this?
Eventually, we came to a collective, slightly broken epiphany: The sample data was not the point.
It wasn’t meant to guide us – it was just there to trigger (pun intended) the idea. Our real task was to build a system that could enforce the business rules that made sense for this fictional business, track user activity, generate audit logs, fire off alerts, and function like a mini analytics pipeline – from scratch.
Let me take you on a journey of what we created.
What is an audit log, anyway?
Good question, past me.
Audit logs are system-level records that track everything important that happens in your database – logins, updates, deletions, new records, shady moves, you name it.
They’re not something you usually build from scratch in your first SQL project. But hey, here we are.
So, we had to define:
- What events are worth tracking?
- How do we structure an audit log table?
- When should alerts be fired?
- How do we write triggers that actually capture this?
This is the point where we stopped querying and started architecting.
What is a Trigger, anyway?
In case you ever find yourself in our trigger-ignorant shoes, let me give you some insight.
A trigger in SQL is a predefined action that fires when a specific event (INSERT, UPDATE, DELETE) occurs on a table.
I now think of them as rules I set for my son: If you take out all the toys, you need to put them back after play time.
In the database world: If the user deletes their account, log the event in the audit log.
Triggers require two parts:
- A trigger function (what happens).
- A trigger (when it happens).
Here’s what a trigger for our delete account example looks like in code (PostgreSQL – style):
-- Step 1: Create the trigger function CREATE OR REPLACE FUNCTION log_user_deletion() RETURNS TRIGGER AS $$ BEGIN INSERT INTO audit_log(user_id, event_type, event_time) VALUES (OLD.user_id, 'DELETE', now()); RETURN OLD; END; -- Step 2: Attach the function to a trigger CREATE TRIGGER audit_user_delete AFTER DELETE ON users FOR EACH ROW EXECUTE FUNCTION log_user_deletion();
And just like that, every time someone deletes a user record, it’s automatically logged.
The Alert Log: When the System Says “Help”
Alert logs are audit logs, but for red flags. They’re designed to tell you, ‘Hey, something’s weird’. For example:
- Too many failed logins.
- A price change that makes the new unit price lower than the cost.
- An order without a total price in it.
Here’s how you might write a trigger function for an alert:
CREATE OR REPLACE FUNCTION raise_price_alert()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.unit_price < NEW.unit_cost THEN
INSERT INTO alert_log(product_id, alert_type, message, created_at)
VALUES (NEW.product_id, 'Price Error', 'Unit price is below cost', now());
END IF;
RETURN NEW;
END;
CREATE TRIGGER alert_on_price_change
AFTER UPDATE ON products
FOR EACH ROW
WHEN (NEW.unit_price < NEW.unit_cost)
EXECUTE FUNCTION raise_price_alert();
Did we know how to do any of this at the start? No.
Did we eventually figure out by crying into our keyboards and trial-and-erroring our way through it? You bet – although for the crying part, I speak only for myself.
When AI Is Your Panic Button
We leaned on AI to help us write the actual SQL code. It saved our ‘%utts’, no doubt. But it couldn’t help us understand the logic of how triggers work, or why certain events were being captured the way they were.
Using AI in this context was like having a chat with my grandma’s parrot: it repeated things beautifully but couldn’t tell us why they mattered. We had to figure that part out ourselves.
What We Actually Learned
- Sometimes, the data isn’t there to help you. It’s there to force you to think.
- Audit pipelines are more than just logging. They’re about designing accountability.
- Triggers are powerful but messy if mishandled (hello, infinite loops).
- Creating event-driven systems requires more than SQL skills. It needs architecture thinking.
Closing Thoughts
This project was frustrating, confusing, and deeply humbling. But by the end, I knew what audit logs were. I understood triggers, alert logic, and how to stitch it all together with code. I even appreciated the messy data for what it was: a distraction that led us to the real task.
So no, this wasn’t the SQL project I expected. It was better — because it showed me what I didn’t know I needed to learn.
And to the person who designed this project: I see you. I might need a vacation, but I see what you did. Well played.