Increase Airflow logging coverage, review existing logs and add missing essential logs

[omkar-foss]

Description

As per users' feedback in the Airflow Debugging Survey 2024, about 42% of respondents mentioned that logs are missing or non-existent in some cases.

Use case/motivation

Goal for this issue is to review the existing Airflow logs coverage and enhance it by adding logs wherever appropriately required.

Related issues

Parent Issue: #40975

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[Dev-iL]

A good example of an issue with logging is when (fatal) errors from workers are not being forwarded to the scheduler, and it's very hard to trace what's actually wrong. In cases when a worker is exhibiting an early enough error (being unable to load the full Airflow machinery that records logs locally and forwards them to the scheduler/webserver), displaying a stack trace captured on the worker would help immensely.

Ref: #42136