BigQuery is a REST API, the errors it uses follow standard HTTP error conventions.

In python, an HttpError has a resp.status field that returns the HTTP status code. As you show above, 409 is 'conflict', 404 is 'not found'.

For example:

from googleapiclient.errors import HttpError
try:
   ...
except HttpError as err:
  # If the error is a rate limit or connection error,
  # wait and try again.
  if err.resp.status in [403, 500, 503]:
    time.sleep(5)
  else: raise

The response is also a json object, an even better way is to parse the json and read the error reason field:

if err.resp.get('content-type', '').startswith('application/json'):
    reason = json.loads(err.content).get('error').get('errors')[0].get('reason')

This can be: notFound, duplicate, accessDenied, invalidQuery, backendError, resourcesExceeded, invalid, quotaExceeded, rateLimitExceeded, timeout, etc.