Hi @b-schmeling, thanks for opening the issue! I think bigquery requires that column names start with either an underscore or letter, as said in the documentation. A new feature flexible column name is currently in preview, when it's GA'd you would be able to use numerical column names.

I will close the issue now, but feel free to leave a comment if you have any further question!

I think bigquery requires that column names start with either an underscore or letter, as said in the documentation.

You can already use a number as a column name as long as it's quoted. As per the documentation here, "Column names can be quoted identifiers or unquoted identifiers."

This is demonstrated by running the SQL:

CREATE TABLE <dataset_id>.tmp AS (SELECT 'a' as `2`);

Which creates a table with a column name "2" with no problem.

Thank you, indeed the backtick seems to work in this case, but I'm not entirely sure if this was by design or a happy accident. I will clarify with the docs team about what the intended format is here (I still feel like column name wouldn't allow string starting with numbers, as this is exactly the thing flexi-name project is trying to resolve. I wonder, by any chance, is your project in the preview list for flexi-name? Maybe that will explain why (SELECT 'a' as `2`) works.)

Apologies for not following up for so long. We are still having this issue come up.

I wonder, by any chance, is your project in the preview list for flexi-name?

It would make sense if we were at the time. It seems like it's GA now, is that correct?

Also, we were able to find which code is adding the underscore:

See the lines:

# Fields must start with a letter or underscore
if not name[0].isalpha() and name[0] != "_":
    name = "_" + name

It should be relatively simple to also check whether the first character is a number, and to not prepend the underscore in that case. I believe this would work because the identifier is quoted right after?