Haven't used Airflow before but use Azure Data Factory in my org to load the raw the data into the data warehouse and then transform into data models using SQL.
I invented ELT.
No, duh, of course not... But: I and my then-colleagues, other DW consultants, started noticing some time in the early-to-mid-00s that what we were doing didn't actually follow the order of the "ETL" acronym, and occasionally commented to each other along the lines of "shouldn't this be called 'ELT' to be more descriptive?". So I don't quite get the "starting to notice this at ELT becomes more popula[r]" bit -- this feels quite old to me. Before most of the meteoric rise of Python.
Retrieve credentials from X
Rewrite SQL (maybe QA is repointed to PROD)
Run, test for performance, then promote/notify/whatever
That being said, my last org was an early adopter of Airflow, and we deployed it before there were "best practices" - and it was an unpleasant experience. In my current role, we are investigating schedulers and Airflow is in the "I really hope I don't have to do this" list of tools. ADF is in the "this will work until we find the real solution" list