Scripts & Serendipity

Understanding NULL in SQL: Common Pitfalls and How to Handle Them NULL is one of the first things every SQL learner meets and one of the last things fully understood. It doesn't mean zero, it doesn't mean empty string, and it doesn't behave like a normal value in comparisons — which is exactly where the bugs come from. 📘 What NULL Actually Means NULL represents the absence of a value — "unknown," not "empty." A NULL phone number isn't the same as an empty string phone number; one means "we don't have this data," the other means "we know it's blank." 🧪 Why NULL = NULL Doesn't Work SELECT * FROM customers WHERE phone = NULL; -- ❌ Always returns zero rows SELECT * FROM customers WHERE phone IS NULL; -- ✅ Correct NULL isn't equal to anything — not even another NULL. In three-valued SQL logic, any comparison involving NULL evaluates to "unknown," not true or false, so the row is excluded either wa...

How to Write a SQL UPSERT (Compared Across MySQL, PostgreSQL, and BigQuery) An "UPSERT" — update if a row exists, insert if it doesn't — is one of those operations every database supports, but with completely different syntax. If you've moved between databases, you've probably had to relearn this every time. Here's all three side by side. 📘 Why UPSERT Matters Without UPSERT, handling "insert or update" logic means checking for existence first, then branching — two round trips and a race condition risk under concurrent writes. UPSERT does it atomically, in one statement. 🧪 MySQL: INSERT ... ON DUPLICATE KEY UPDATE INSERT INTO inventory (product_id, quantity) VALUES (101, 50) ON DUPLICATE KEY UPDATE quantity = quantity + 50; Requires a UNIQUE or PRIMARY KEY constraint on product_id for MySQL to know what counts as a "duplicate." 🧪 PostgreSQL: INSERT ... ON CONFLICT DO UPDATE INSERT INTO inventory (product_id, quantity) VALUES ...

SQL Window Functions Explained: ROW_NUMBER, RANK, LAG, LEAD Window functions let you calculate something "across" a set of related rows without collapsing them into one — they're one of the most powerful tools in SQL once they click, and one of the most confusing before they do. 📘 The Shared Pattern Every window function follows the same basic shape: function_name() OVER ( PARTITION BY column -- optional: split into groups ORDER BY column -- optional: define row order within each group ) 🧪 ROW_NUMBER — a unique sequential number SELECT employee_name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept FROM employees; Every row gets a unique number, 1, 2, 3... within its department, ordered by salary descending. No ties — even if two people have identical salaries, one gets 1 and the other gets 2, arbitrarily. 🧪 RANK vs DENSE_RANK — handling ties SELECT employee_name, salary, RANK()...

Common Table Expressions (CTEs) in SQL — A Beginner's Guide A Common Table Expression (CTE) is a named, temporary result set you can reference within a single query. Think of it as giving a subquery a name and using that name like a regular table — it makes complex queries dramatically more readable. 📘 Basic Syntax WITH high_earners AS ( SELECT employee_id, name, salary FROM employees WHERE salary > 80000 ) SELECT * FROM high_earners WHERE name LIKE 'A%'; The WITH ... AS (...) block defines the CTE, and it can then be queried just like a table in the main SELECT below it. 🎯 Why Use a CTE Instead of a Subquery? Compare the same logic without a CTE: -- Without CTE — nested and harder to read SELECT * FROM ( SELECT employee_id, name, salary FROM employees WHERE salary > 80000 ) high_earners WHERE name LIKE 'A%'; Functionally identical, but for anything more complex than this toy example, nested subqueries get hard to follow fast. CTEs le...

SQL WHERE vs HAVING: When to Use Each WHERE and HAVING both filter rows, which is exactly why they're so easy to mix up. The difference comes down to one thing: when in the query's execution each one runs. 📘 The Core Rule WHERE filters rows before grouping happens. HAVING filters groups after GROUP BY has already collapsed the rows. This is why HAVING can filter on aggregate functions (like COUNT or SUM) and WHERE cannot. 🧪 Why WHERE Can't Filter on Aggregates -- ❌ This throws an error in most databases SELECT department, COUNT(*) AS employee_count FROM employees WHERE COUNT(*) > 5 GROUP BY department; This fails because WHERE runs before COUNT(*) has even been calculated — at the point WHERE executes, there's no "count" yet to filter on. 🧪 The Correct Version, Using HAVING SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) > 5; This works because HAVING runs after grouping and aggregatio...

GROUP BY vs PARTITION BY in SQL — What's the Difference? Both GROUP BY and PARTITION BY organize rows into groups — but they do fundamentally different things with those groups. Mixing them up is one of the most common points of confusion once you move past basic SQL. 📘 The Core Difference GROUP BY collapses rows: multiple input rows become one output row per group. You lose the individual row-level detail. PARTITION BY (used with window functions) keeps every row, but lets you calculate something "within" each group without collapsing anything. 🧪 GROUP BY in Action SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department; Output: one row per department. If you had 50 employees across 5 departments, you get back exactly 5 rows. 🧪 PARTITION BY in Action SELECT employee_name, department, salary, AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary FROM employees; Output: all 50 employee rows, unchanged — but each ro...

SQL JOIN Types Explained (INNER, LEFT, RIGHT, FULL) with Examples JOINs are the single most-used SQL concept and also the most commonly half-understood one. Once you can picture what each JOIN type actually keeps and discards, the syntax stops being something to memorize and starts being something you can reason through. 📘 The Setup Imagine two tables: -- customers customer_id | name 1 | Alice 2 | Bob 3 | Carol -- orders order_id | customer_id | amount 101 | 1 | 50 102 | 1 | 20 103 | 4 | 75 -- note: customer_id 4 doesn't exist in customers Carol (id 3) has no orders. Order 103 belongs to customer_id 4, who doesn't exist in the customers table. This mismatch is exactly what makes JOIN behavior visible. 🧪 INNER JOIN — only matching rows SELECT c.name, o.order_id, o.amount FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id; Result: Alice's two orders only. Carol is exc...

How I Replaced Legacy SSIS with Real-Time Data Pipelines (and Saved Costs!) A few years into working with SQL Server Integration Services (SSIS), I hit the same wall a lot of data teams eventually hit: nightly batch jobs that took longer every month, a server that needed constant babysitting, and a growing list of "just run it again manually" incidents. This is the story of moving that pipeline to a GCP-native setup — what worked, what I underestimated, and what it actually saved. 📘 The Starting Point The original setup was a fairly typical on-prem pattern: Source SQL Server → SSIS nightly ETL job → Data Warehouse (on-prem) It worked — until data volume grew. The nightly job window kept creeping later, sometimes bleeding into business hours, and every schema change at the source meant manually re-mapping columns in the SSIS designer. 🔧 The Migration Path Rather than a big-bang rewrite, I moved piece by piece: Google Cloud Datastream replaced the extraction step...

Using Airflow to Orchestrate Daily GCP Pipeline Failover Tests A pipeline that's never had its failure path tested is a pipeline you don't actually understand yet. Apache Airflow is the standard tool for orchestrating multi-step data pipelines — and one of its most underused features is scheduling regular failover tests , not just the happy-path job. 📘 Why Test Failover on a Schedule Most teams only discover their retry logic, alerting, and fallback paths don't actually work the moment a real production failure happens — which is the worst possible time to find out. Running a scheduled, low-stakes failover drill (e.g., forcing one task to fail and confirming recovery) catches gaps before they matter. 🧪 A Simple Airflow DAG Structure from airflow import DAG from airflow.operators.python import PythonOperator from datetime import datetime, timedelta default_args = { "retries": 3, "retry_delay": timedelta(minutes=5), } with DAG( ...

Managing Schema Evolution Without Losing Data Source systems change: a new column gets added, a field gets renamed, a data type gets widened. If your pipeline isn't built to handle that gracefully, a schema change upstream can silently break a dashboard downstream — or worse, silently drop data without throwing any error at all. 📘 What "Schema Evolution" Means Schema evolution is the practice of letting your tables and pipelines adapt to structural changes in source data over time, without requiring a full rebuild or causing data loss. The three changes you'll deal with most: adding a column , renaming a column , and changing a data type . 🧪 Handling New Columns in BigQuery BigQuery supports schema auto-detection and relaxation for many load jobs, but for controlled pipelines it's safer to be explicit: ALTER TABLE analytics.orders ADD COLUMN IF NOT EXISTS discount_code STRING; Adding a column is the safe, additive case — existing queries keep working b...

Change Data Capture (CDC): Real-Life Use Cases and Pitfalls Batch pipelines that run "every night at 2am" are simple — but they mean your data is always up to a day stale. Change Data Capture (CDC) solves this by streaming just the changes (inserts, updates, deletes) from a source database the moment they happen, instead of re-extracting everything on a schedule. 📘 What CDC Actually Captures Most CDC tools read a database's transaction/replication log (e.g., MySQL's binlog, PostgreSQL's WAL) rather than querying tables directly. This means CDC sees every change as an event, without adding query load to your production database. 🧪 A Simplified CDC Event A CDC tool like Debezium or Google Cloud's Datastream converts a database change into a structured event, roughly like this: { "op": "UPDATE", "table": "orders", "before": { "order_id": "1001", "status": "pendi...

ETL vs ELT: Choosing the Right Strategy for Your Workflow Every data pipeline has to answer the same question: where does the transformation happen — before loading into the warehouse, or after? That's the entire ETL vs ELT debate, and the right answer depends on your tools and scale, not on which one is "modern." 📘 The Core Difference ETL (Extract, Transform, Load): data is transformed in a separate processing step (traditionally an ETL server like SSIS or Talend) before it lands in the destination database. ELT (Extract, Load, Transform): raw data is loaded into the destination first, and transformation happens inside the warehouse itself using its own compute (SQL, dbt, stored procedures). 🧪 What This Looks Like in Practice ETL pipeline: Source DB → Transformation Server (clean, join, aggregate) → Data Warehouse ELT pipeline: Source DB → Data Warehouse (raw tables) → SQL/dbt transforms → Analytics tables A real ELT transform step in BigQuery might loo...

Optimizing BigQuery Costs with Partitioning & Clustering BigQuery bills by the amount of data your query scans — not by how long it runs. This means the single biggest lever for controlling your GCP bill isn't compute tuning, it's reducing bytes scanned . Partitioning and clustering are the two main tools for that. 📘 Partitioning vs Clustering — The Core Idea Partitioning physically splits a table into segments (most commonly by date), so a query can skip entire segments it doesn't need. Clustering sorts data within each partition by one or more columns, so BigQuery can skip irrelevant blocks even within a partition. 🧪 Creating a Partitioned & Clustered Table CREATE TABLE dataset.orders ( order_id STRING, customer_id STRING, region STRING, order_date DATE, amount NUMERIC ) PARTITION BY order_date CLUSTER BY customer_id, region; Here, every query that filters on order_date only scans the relevant day(s)/month(s), and if it also filters on cust...

Avoiding Deadlocks in MySQL: A Practical Guide for Real-Time Systems If you've ever seen ERROR 1213 (40001): Deadlock found when trying to get lock in a production log at 2am, this post is for you. Deadlocks are one of those MySQL problems that seem rare until your system has real concurrent traffic — then they show up weekly. 📘 What Is a Deadlock? A deadlock happens when two (or more) transactions each hold a lock the other one needs, and neither can proceed. MySQL's InnoDB engine detects this automatically and kills one of the transactions (the "victim") to break the cycle — but that still means one of your transactions failed, and your application needs to handle that gracefully. 🧪 A Simple Example -- Transaction A START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- Transaction B (running at the same time) START TRANSACTION; UPDATE accounts SET balance = balanc...