BigQuery - Where can I find the error stream?

You could also just do.

try:
    load_job.result()  # Waits for the job to complete.
except ClientError as e:
    print(load_job.errors)
    raise e

This will print the errors to screen or you could log them etc.

You should be able to click on Job History in the BigQuery UI, then click the failed load job. I tried loading an invalid CSV file just now, and the errors that I see are:

Errors:
Error while reading data, error message: CSV table encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the error stream for more details. (error code: invalid)
Error while reading data, error message: CSV table references column position 1, but line starting at position:0 contains only 1 columns. (error code: invalid)

The first one is just a generic message indicating the failure, but the second error (from the "error stream") is the one that provides more context for the failure, namely CSV table references column position 1, but line starting at position:0 contains only 1 columns.

Edit: given a job ID, you can also use the BigQuery CLI to see complete information about the failure. You would use:

bq --format=prettyjson show -j <job ID>

I'm finally managed to see the error stream by running the following command in the terminal:

bq --format=prettyjson show -j <JobID>

It returns a JSON with more details. In my case it was:

"message": "Error while reading data, error message: Could not parse '16.66666666666667' as int for field Course_Percentage (position 46) starting at location 1717164"

Using python client it's

from google.api_core.exceptions import BadRequest

job = client.load_table_from_file(*args, **kwargs)
try:
    result = job.result()
except BadRequest as ex:

    for err in ex.errors:
        print(err)
    raise
    # or alternatively
    # job.errors