Modern ELT & Data Pipelines

What’s the difference between ETL and ELT?

ETL transforms data before it lands in the warehouse. ELT loads the raw data first and transforms it afterwards, inside the warehouse, using the warehouse’s own compute.

The old order existed because warehouse compute was scarce and expensive, so you cleaned data on the way in to avoid paying for it twice. BigQuery changed that arithmetic. Storage is cheap, compute is separate and elastic, and SQL runs over billions of rows without a cluster to size. Once that’s true, transforming after loading is simply better: you keep the raw record, so when someone changes their mind about a business rule in eight months you can rebuild history instead of apologising for it.

We build ELT by default now. ETL still wins in two cases. If you’re legally not allowed to land the raw data, masking has to happen in flight. And if you’re ingesting something enormous where 90% is discarded, filtering early saves real money.

Streaming or batch?

Batch, for most teams. This is the decision people get wrong most often, and it’s expensive in both directions.

Ask when a human or system actually acts on the data. If the answer is “someone looks at the dashboard over coffee”, a nightly Composer run is right and streaming is a waste of money and on-call attention. Streaming pipelines have more failure modes, need windowing and watermark logic, and cost you engineering time forever.

Streaming earns its keep when the latency is load-bearing: fraud scoring before settlement, inventory that a customer is about to rely on, a model feature that’s worthless if it’s an hour stale. For those, Dataflow with Apache Beam is the right tool, and the unified batch and streaming model means you’re not maintaining two codebases.

There’s a middle option people forget. Datastream does change data capture from Oracle, MySQL, PostgreSQL and SQL Server into BigQuery with a few minutes of lag, no pipeline code to maintain. For a lot of “we need fresher data” requests, that’s the whole answer.

Which orchestrator, and when

Cloud Composer, which is managed Apache Airflow, is where we put anything with real dependencies: a DAG that waits on three source systems, backfills a date range, and reruns a failed task without redoing the other forty. It costs money while idle, which is the honest downside, so it’s overkill if you have six tasks.

If you genuinely have six tasks, BigQuery scheduled queries or Cloud Workflows on a Cloud Scheduler trigger will do. We’d rather set you up with the cheap thing and migrate later than sell you an Airflow environment you don’t need yet.

What actually breaks in production

Three things, over and over.

Non-idempotent loads. If rerunning yesterday’s job double-counts yesterday’s revenue, you don’t have a pipeline, you have a hand grenade. Every load we write is safe to run twice, usually via a MERGE on a natural key or partition-level replacement.

Late-arriving data. A source system posts a transaction with yesterday’s timestamp after you closed yesterday’s partition. Handling this needs an explicit lateness window and a reprocessing strategy, decided up front. Discovering it during month-end close is a bad time.

Silent schema drift. An upstream team renames a column and nothing errors, it just nulls out, and the dashboard quietly understates a number for three weeks until someone in finance notices. dbt tests on every model plus Dataplex profiling catch this at the pipeline, which is the only place it’s cheap to catch.

How long does a pipeline migration take?

Depends almost entirely on how many source systems and how much undocumented business logic is buried in the old jobs. Reverse-engineering a decade of SAS or Informatica transformations is usually the long pole, not writing the new pipelines.

For scale, we cut Snowflake warehouse run-rates by at least 20% on migration to BigQuery, and we put $50,000 cash behind that. Pricing is fixed and dated before we start, so the discovery risk sits with us rather than with you.