We use SQL in our pipelines frequently! For standard lightweight transforms you can pick your tool flavor: DuckDB, Polars, or like, and use SQL as normal. For Spark-based transforms you can use Spark SQL.
In any case, we heavily use lightweight transforms orientated around SQL and recommend trying this approach.