From SSIS to Airflow: what changed, what did not

Where I started

At AMARTEK I worked on a bank's data platform with SQL Server, SSIS and a lot of stored procedures. At SG-EDTS I now build pipelines for FMCG clients with Airflow, GCP and BigQuery, and report on them in Looker Studio. The tools look nothing alike, but a surprising amount of the job stayed the same.

What carried over

• Idempotency matters more than speed. A job you cannot safely re-run will eventually double-count something.
• Most incidents are data-quality problems, not code problems. Row counts, null checks and reconciliation against the source catch more than clever logic.
• Understanding the business flow first saves rework. The pipeline is only right if the number matches what the business expects.

What I had to unlearn

• A pipeline is code, not a diagram. In SSIS the flow lives in a visual designer; in Airflow it lives in Python files that can be reviewed, diffed and version-controlled.
• Scheduling and dependencies are explicit. A DAG states what runs when and what depends on what, with retries and alerting as first-class settings.
• The warehouse does the heavy lifting. With BigQuery I push transformations into SQL and let the engine scale, instead of tuning a single server.

A simple habit that helped

Whatever the tool, I design each task so that running it twice leaves the data in the same state. In Airflow that usually means writing to a date partition and replacing it, rather than appending:

-- BigQuery: re-runnable daily load
DELETE FROM mart.daily_sales WHERE sales_date = @run_date;
INSERT INTO mart.daily_sales
SELECT * FROM staging.sales WHERE sales_date = @run_date;

Takeaway

Moving stacks did not reset my experience. The tools changed; the habits of careful reconciliation, safe re-runs and talking to the people who use the data are what I carried across.