UN-3176 [FIX] Stop reporting BigQuery value errors as missing columns

Every google.api_core BadRequest was mapped to ColumnMissingException, whose message tells the user to "make sure all the columns exist in your table as per the destination DB configuration". BigQuery also returns BadRequest for VALUE-level failures -- including this ticket's "cannot round-trip through string representation; error in PARSE_JSON expression" -- so users were sent to check a schema that was never wrong. That misdirection is likely why this was filed as a datatype-conversion bug. Adds BigQueryValueException and discriminates before wrapping: prefers the structured errors[] payload, falls back to message signatures for the round-trip / PARSE_JSON / invalid-JSON cases BigQuery does not tag. Anything unrecognised falls through to the existing ColumnMissingException, so this only narrows messages that were already wrong. Verified 7/7 including the ticket's verbatim error text and two genuine missing-column messages that must NOT be reclassified. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_011sXFBEu2GHatXq2CV2ShPF
hari-kuriakose · Aug 28, 2026 · Aug 28, 2026 · Aug 28, 2026 · Aug 28, 2026 · Aug 28, 2026
commit 1f8a4a7cb4b18b5bdd1e40949a3839339c74d205
diff --git a/unstract/connectors/src/unstract/connectors/databases/bigquery/bigquery.py b/unstract/connectors/src/unstract/connectors/databases/bigquery/bigquery.py
@@ -13,6 +13,7 @@
 from unstract.connectors.databases.exceptions import (
     BigQueryForbiddenException,
     BigQueryNotFoundException,
+    BigQueryValueException,
     ColumnMissingException,
 )
 from unstract.connectors.databases.sql_safety import (
@@ -321,6 +322,17 @@
                 detail=e.message, table_name=table_name
             ) from e
         except google.api_core.exceptions.BadRequest as e:
+            # UN-3176: BigQuery returns BadRequest for VALUE-level failures as
+            # well as for schema mismatches. Mapping them all to
+            # ColumnMissingException told users to check their columns when the
+            # columns were fine (e.g. a float that will not round-trip through
+            # PARSE_JSON), which misdirects the investigation. Discriminate
+            # before wrapping.
+            if BigQuery._is_value_error(e):
+                logger.error(f"Value rejected by BigQuery on insert: {str(e)}")
+                raise BigQueryValueException(
+                    detail=e.message, table_name=table_name
+                ) from e
             logger.error(f"Column missing in inserting data: {str(e)}")
             db, schema, table = table_name.split(".")
             raise ColumnMissingException(
@@ -330,6 +342,36 @@
                 table_name=table,
             ) from e
 
+    @staticmethod
+    def _is_value_error(e: Any) -> bool:
+        """True if a BigQuery BadRequest is about the DATA, not the schema.
+
+        UN-3176. Prefers the structured ``errors`` payload (a list of
+        ``{reason, message}``), because ``invalidQuery`` covers the value-level
+        rejections we care about here, and falls back to the message text for
+        the signatures BigQuery does not tag -- notably the PARSE_JSON
+        round-trip failure in this ticket. Unknown shapes fall through to the
+        existing column-missing behaviour, so this only ever narrows a message
+        that was already wrong for these cases.
+        """
+        value_error_markers = (
+            "parse_json",
+            "round-trip through string representation",
+            "invalid json",
+            "cannot round-trip",
+            "failed to parse json",
+        )
+        text = f"{getattr(e, 'message', '') or ''} {str(e)}".lower()
+        if any(marker in text for marker in value_error_markers):
+            return True
+        for error in getattr(e, "errors", None) or []:
+            if not isinstance(error, dict):
+                continue
+            message = str(error.get("message", "")).lower()
+            if any(marker in message for marker in value_error_markers):
+                return True
+        return False
+
     def get_information_schema(self, table_name: str) -> dict[str, str]:
         """Function to generate information schema of the big query table.
 

diff --git a/unstract/connectors/src/unstract/connectors/databases/exceptions.py b/unstract/connectors/src/unstract/connectors/databases/exceptions.py
@@ -90,6 +90,29 @@ def __init__(self, detail: str, table_name: str) -> None:
         super().__init__(detail=final_detail)
 
 
+class BigQueryValueException(UnstractDBConnectorException):
+    """A BigQuery BadRequest caused by the DATA, not the table schema.
+
+    UN-3176: BigQuery returns BadRequest for value-level failures (a float that
+    will not round-trip through PARSE_JSON, a malformed JSON literal) as well as
+    for genuine schema mismatches. Mapping every BadRequest to
+    ColumnMissingException told users to "make sure all the columns exist" when
+    the columns were fine, which sent at least one investigation down the wrong
+    path.
+    """
+
+    def __init__(self, detail: Any, table_name: str) -> None:
+        default_detail = (
+            f"Error writing to '{table_name}'. \n"
+            f"BigQuery rejected a value in the row being inserted -- the table "
+            f"schema is not the problem. This usually means a number could not "
+            f"be represented exactly, or a JSON column received text that is "
+            f"not valid JSON.\n"
+        )
+        final_detail = _format_exception_detail(default_detail, detail)
+        super().__init__(detail=final_detail)
+
+
 class ColumnMissingException(UnstractDBConnectorException):
     def __init__(
         self,