MySQL and CTE for data cleaning/prep

Hi all,

I’m new to dbt and trying to incorporate my data cleansing and preparation using the CTE workflow.

My problem is that MySQL does not support updating CTE’s (as far as I can tell), so I’m curious if there is a best practice or a work-around to navigate this issue? If not, im headed into some very difficult feature engineering.

I am starting to look towards temp tables as a possible work around, but I think this breaks the dbt workflow. I’m really hoping I dont have to scrap my whole database and move to postgres.

Any tips would be very much appreciated.

Kind regards from a data noob