Destination Bigquery Normalization: Name transformation should be consistent between normalization and connector

[edgao]

The connector will ensure that the dataset ID (i.e. stream namespace) starts with an alphanumeric character - https://github.com/airbytehq/airbyte/blob/master/airbyte-integrations/connectors/destination-bigquery/src/main/java/io/airbyte/integrations/destination/bigquery/BigQuerySQLNameTransformer.java#L38

but normalization doesn't do this. So if you configure a custom stream namespace that starts with an underscore (e.g. _foo)... then destination-bigquery will create the raw tables in the n_foo dataset, but normalization will search for them in an _foo dataset.

We probably want to add the n prefix in normalization. (this stops being a problem once normalization runs in java, since we can just pass around a single BigQueryWriteConfig - but that's not an immediate solution)

(dataset names are allowed to start with an underscore, but they won't show up in the UI - https://cloud.google.com/bigquery/docs/datasets#dataset-naming)

[evantahler]

Grooming

• ${namespace}_airbyte -> "hubspot" + _airbyte => hubspot_airbyte
  *happy path
• ${namespace}_airbyte -> "" + _airbyte => _airbyte
  • hidden in BQ
• ${namespace}_airbyte -> "_public" + _airbyte => _public_airbyte
  • hidden in BQ

#3 won't be fixed by a platform change, so we still need to handle this.

• appending "n" is silly to begin with, we should at lesat append "Airbyte" or something meaningful

---

The plan:

1. in metabase confirm that case 3 is rate
2. if checkin catalogs #3 is really rare, this fix belongs in the platform so that "" namespaces are never empty and we append something (name of the source?). We make this change as part of this story.
3. Remove the "n_" hack the destination has now
4. docs to educate BQ users that _ tables are hidden (add to destination docs).
5. Migration plan - what do we tell existing users (use output of step 1)

[edgao]

I was reminded that https://github.com/airbytehq/airbyte-internal-issues/issues/943 also exists. I think that's a nonissue because the UI trims off whitespace (#17113), but it's another thing to fix when we're doing the typing/deduping in java.

[edgao]

closing since we want platform to do this https://github.com/airbytehq/airbyte-internal-issues/issues/1875