Prefect with SQLMesh (TLDR set MAX_FORK_WORKERS env to 1 if you work with them)

[IqbalLx]

Just a tips for anyone who try to automate sqlmesh command with prefect, set MAX_FORK_WORKERS into 1 in your prefect deployment. It is due to sqlmesh is defaulted to use parallel loading when in linux env, and somehow that didn't works with prefect task that are executed inside a child process too. Setting the value into 1 will disable this sqlmesh behaviour

reference: https://sqlmesh.readthedocs.io/en/stable/reference/configuration/?h=max+fork+workers#parallel-loading

[IqbalLx]

looking back to this thread, it turns out that prefect spawned a task using a multithreading stdlibrary, ref: https://docs.prefect.io/v3/concepts/task-runners#available-task-runners

The default task runner in Prefect is the ThreadPoolTaskRunner, which runs tasks concurrently in independent threads.

hence setting MAX_FORK_WORKERS=1 disabling default SQLMesh behaviour that also using multithreading to parallelize the job, making it works to run as prefect task.

to keep parallel execution of SQLMesh, use prefect ProcessPoolTaskRunner, ref: https://docs.prefect.io/v3/api-ref/python/prefect-task_runners#processpooltaskrunner