Scripts & Serendipity

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