In an ELT stack, when should transformation run on an external engine rather than warehouse SQL?

## Start from where the data already is In an ELT stack the raw and intermediate data is in the warehouse. Any proposal to transform it elsewhere carries an unavoidable tax: read the data out, serialise it into a transport format, move it across a network, deserialise it into the external engine's memory, do the work, then serialise and write the result back. On a large table that round trip frequently costs more than the transformation itself, and it is pure overhead — no user ever benefits from a byte being copied. So the question is never "which engine is better". It is "is this work bad enough at SQL to be worth the round trip". ## What warehouse SQL is genuinely good at Modern analytical engines are built for set-based relational work over columnar data: filtering, joining, grouping, windowing, deduplicating, pivoting, incremental merges. For these, the engine reads only the columns referenced, prunes blocks that cannot match, and splits the work across its own workers without anyone tuning partition counts or executor memory. Beyond raw speed, staying in SQL buys operational properties that are easy to undervalue: - **One system to operate.** No second cluster to size, patch, secure or debug at 3am. - **One dialect and one skill set.** Analysts can read and change the logic. - **Coherent lineage and permissions.** Table-to-table dependencies are visible, and the warehouse's access controls apply throughout instead of being re-implemented. - **No data leaves.** Fewer copies means a smaller compliance surface. Size alone is a weak reason to leave. A join over billions of rows is exactly the workload these engines exist for. ## When leaving is the right call **The work is not set-based.** Scoring each row with a trained model, running an iterative algorithm that revisits the same data many times, graph traversal, numerical simulation, custom similarity logic. Expressing these in SQL ranges from awkward to impossible, and forcing them in usually produces something slow and unreadable. **The work needs libraries the warehouse cannot host.** Parsing a proprietary binary format, decoding industry-specific message standards, image or audio handling, or a vendor SDK. Some warehouses do host code in-database, which shrinks this category but does not eliminate it — check what your platform actually supports rather than assuming. **The work is per-row and external.** Calling an API, enriching against a third-party service, writing to an operational system. Warehouses are poor at orchestrating millions of outbound calls with retries and rate limiting; a general-purpose engine or a stream processor handles it naturally. **The data is not in the warehouse and mostly should not be.** Petabytes of logs or files on object storage of which you want a filtered, aggregated slice. Reducing outside and loading the result moves far less data than loading everything to filter it — this is transform-before-load surviving inside an ELT-first architecture, and it is a perfectly respectable answer. **Cost, when measured.** Heavy per-row user-defined function work often prices badly against warehouse compute. If you have a measurement showing an external engine is substantially cheaper for a specific job, that is a legitimate exception. A hunch is not. **Open outputs for non-warehouse readers.** If the result must be consumed by systems that read files directly rather than querying the warehouse, producing it outside can avoid an export step entirely. ## The hybrid nearly everyone lands on Most mature platforms push the overwhelming majority of transformations down as SQL and keep a small, deliberate set of jobs outside — feature generation, unstructured parsing, enrichment — each with a written reason. The orchestrator is what makes the hybrid coherent: it triggers both kinds of work, holds the dependency edges between them, and records the runs, so the fact that one step ran elsewhere does not fragment the lineage. ## The failure modes on both sides Pushing everything down produces SQL nobody can read: thousand-line statements simulating procedural logic, recursive constructs used as loops, string manipulation approximating a parser. If a transformation reads like code fighting its language, that is the signal to move it. Moving too eagerly produces the opposite failure: a second engine, a second permission model, a second deployment path, a second on-call rotation, and a lineage graph with a hole in the middle — all to run three jobs that SQL would have handled. The maintenance cost of an engine is paid every week; the speed of one job is measured once. When you do move a step out, keep the boundary clean: read a defined input, write a defined output back, and let the orchestrator own the dependency between them. A job that reaches into the warehouse mid-pipeline and mutates tables other models are reading is the worst of both designs.