Hey Andrew,
I donāt have a whole lot of airflow experience, but my understanding is that the agent in prefect is some what more closely related to the scheduler in airflow. That said, the agent is a bit more of a hybrid/dual purpose entity. It feels like in prefect you have slightly less flexibility in terms of what your worker/operator/task compute environment looks like. In airflow for example you can use KubernetesPodOperator and have a fully custom container instance per task. In prefect, each flow execution is a single āinstanceā, with task scaling/parallel processing accomplished via dask. The upside to the decreased flexibility, is a much more ergonomic approach to handling task input/output. Tasks can return stuff and other tasks can use the results, without a system like Xcom, etc.
On the topic of big/query and cloud functions. It should be possible using a task per query/cloud function. I have been doing something similar with AWS Batch jobs. Let me look a bit more.