Introduction
SQL Server to BigQuery data warehouse load errors can happen when object names generated from source or destination configuration are not valid BigQuery identifiers. In Kleene, this often appears when a table name contains a character that BigQuery interprets as SQL syntax rather than as part of the table name. A common example is a hyphen in the destination table name.
Issue description
A SQL Server to BigQuery data warehouse load failed with the following BigQuery syntax error:
Syntax error: Expected end of input but got "-" at [1:72]This means BigQuery reached a hyphen (-) in the generated SQL and treated it as a minus operator instead of part of an identifier. The SQL was rejected before the load could complete.
Signs
You may be dealing with this issue if a BigQuery load or generated SQL statement fails immediately with a syntax error that points to a hyphen. The error may include a line and column number, such as [1:72], showing where BigQuery stopped parsing the statement.
This is especially likely when the destination table, dataset, project, or another generated identifier contains a hyphen and is not correctly escaped.
Basic troubleshooting steps
Start with the following checks to narrow down the cause of the error:
- Review the full BigQuery error message and note the line and column number.
- Check the generated SQL at the reported position.
- Look for a hyphen (
-) in the table name, dataset name, project name, or alias. - Confirm whether the destination BigQuery table name contains a hyphen.
- Check whether the table reference is escaped with BigQuery backticks.
- Review the Kleene destination configuration for invalid or unsupported identifier characters.
- Decide whether the table name should be renamed or escaped.
Common causes and how to fix them
Hyphen in the destination table name
BigQuery can interpret a hyphen in an unescaped table name as a minus operator. For example, a table name like table-name may be parsed as table - name unless it is escaped correctly.
How to fix it: rename the BigQuery destination table using underscores instead of hyphens. For example, use table_name rather than table-name.
Unescaped BigQuery table reference
BigQuery supports backticks for escaping full table references. If a table name contains special characters, the full reference must be wrapped in backticks.
How to fix it: escape the table reference using BigQuery backticks:
`project.dataset.table-name`Renaming the table is usually the preferred fix because it avoids needing special escaping in generated SQL.
Generated SQL using invalid identifiers
If the SQL is generated from configuration, an invalid table name can be carried into the final statement automatically. The query may not be hand-written, so the issue may sit in the destination setup rather than the SQL text itself.
How to fix it: update the Kleene destination configuration so the generated BigQuery table name uses a valid identifier, preferably with underscores instead of hyphens.
Confusing project IDs with table names
BigQuery project IDs can contain hyphens, but table names and dataset names have stricter naming expectations depending on how they are referenced. This can make it unclear which part of the full reference is causing the parsing error.
How to fix it: inspect the full table reference and confirm exactly where the hyphen appears. If the hyphen is in the table name, rename the table or escape the full reference.
Practical troubleshooting workflow
- Read the BigQuery error and copy the reported line and column number.
- Open the generated SQL or load statement.
- Go to the reported position and check whether the token contains a hyphen.
- Inspect the full destination table reference.
- Confirm whether the destination BigQuery table name contains a hyphen.
- Rename the destination table using underscores where possible.
- If renaming is not possible, ensure the full table reference is wrapped in BigQuery backticks.
- Update the Kleene destination configuration with the corrected table name or reference.
- Re-run the load and confirm that BigQuery no longer rejects the SQL.
Best practices to avoid BigQuery identifier syntax errors
- Use underscores instead of hyphens in BigQuery table names.
- Keep destination table names simple and SQL-safe.
- Avoid special characters in generated object names.
- Validate destination configuration before running a new load.
- When special characters are unavoidable, wrap full BigQuery references in backticks.
- Check the generated SQL when BigQuery reports a syntax error with a specific line and column.
- Document naming conventions for BigQuery destinations used by Kleene loads.
Additional information
For this issue, the root cause was that the destination BigQuery table name contained a hyphen. BigQuery interpreted the hyphen as an operator, which caused the generated SQL to fail with:
Syntax error: Expected end of input but got "-" at [1:72]The preferred resolution is to rename the destination table using underscores. If the table name cannot be changed, escape the full BigQuery table reference with backticks, such as:
`project.dataset.table-name`