Scripts & Serendipity

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( ...