Apache Airflow子DAG执行失败求助：期望SUCCESS实际为failed

我在Mac M1上运行Apache Airflow，尝试将S3生成的临时staging表数据插入AWS Redshift时任务失败，无法定位问题根源，恳请协助调试。报错日志如下：

[2022-07-30 16:16:58,226] {subdag.py:186} INFO - Execution finished. State is failed
[2022-07-30 16:16:58,252] {taskinstance.py:1909} ERROR - Task failed with exception
Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py", line 1471, in _run_raw_task
    self._execute_task_with_callbacks(context, test_mode)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py", line 1621, in _execute_task_with_callbacks
    self.task.post_execute(context=context, result=result)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/operators/subdag.py", line 189, in post_execute
    raise AirflowException(f"Expected state: SUCCESS. Actual state: {dag_run.state}")
airflow.exceptions.AirflowException: Expected state: SUCCESS. Actual state: failed
[2022-07-30 16:16:58,268] {taskinstance.py:1415} INFO - Marking task as FAILED. dag_id=sparkify_dag, task_id=load_artist_dim_table, execution_date=20220729T174100, start_date=20220730T161557, end_date=20220730T161658
[2022-07-30 16:16:58,298] {standard_task_runner.py:92} ERROR - Failed to execute job 399 for task load_artist_dim_table (Expected state: SUCCESS. Actual state: failed; 55013)
[2022-07-30 16:16:58,356] {local_task_job.py:156} INFO - Task exited with return code 1
[2022-07-30 16:16:58,426] {local_task_job.py:273} INFO - 0 downstream tasks scheduled from follow-on schedule check

• 查看subdag内部子任务日志：当前报错仅提示subdag整体状态失败，未展示具体原因。需进入load_artist_dim_table这个subdag，查看其中每个子任务的详细日志，定位真正的失败点（比如Redshift连接错误、SQL语法问题、数据类型不匹配、S3权限不足等）
• 验证Redshift连接配置：检查Airflow中Redshift连接的端点、端口、数据库名、用户名/密码是否正确，同时确认Mac M1所在IP已加入Redshift安全组的允许访问列表
• 检查S3与Redshift的权限：确认Redshift使用的IAM角色拥有读取目标S3桶的权限，或者Airflow所使用的凭证同时具备S3读取和Redshift操作权限
• 核对数据结构与插入SQL：对比staging表和目标artist_dim_table的字段数量、类型是否匹配，检查插入SQL语句是否存在语法错误（如字段名拼写错误、聚合函数使用不当）
• 单独测试SQL语句：将subdag中执行的插入SQL提取出来，用Redshift客户端（如psql）直接执行，观察是否报错，获取更具体的错误信息
• 排查M1架构兼容性：Airflow部分依赖在M1的arm64架构下可能存在兼容性问题，确认所使用的Airflow版本、apache-airflow-providers-amazon等依赖是否支持arm64，或尝试通过Rosetta 2运行Airflow解决兼容问题

内容的提问来源于stack exchange，提问作者Guolun Li