Fivetran Ingestion doesn't work for BigQuery Destination due to failure in parsing BigQuery sql syntax

[yingying-chen-cko]

Describe the bug
A clear and concise description of what the bug is.

I specified the Fivetran log dataset with the GCP project id and the ingestion failed due to the sqlglot.parse_one has defaulted the dialect to snowflake. See: https://github.com/datahub-project/datahub/blob/master/metadata-ingestion/src/datahub/ingestion/source/fivetran/fivetran_log_api.py#L88

To Reproduce
Steps to reproduce the behavior:

Specify the fivetran_log_config.bigquery_destination_config.dataset with the gcp project id that the dataset is in and trigger the ingestion:

source:
    type: fivetran
    config:
        history_sync_lookback_period: 1
        fivetran_log_config:
            destination_platform: bigquery
            bigquery_destination_config:
              dataset: bq-project-id.fivetran_logs
              credential:
                project_id: '${BQ_PROJECT_ID}'
                private_key_id: '${BQ_DATAHUB_SA_PRIVATE_KEY_ID}'
                private_key: '${BQ_DATAHUB_SA_PRIVATE_KEY}'
                client_email: '${BQ_DATAHUB_SA_CLIENT_EMAIL}'
                client_id: '${BQ_DATAHUB_SA_CLIENT_ID}'
        stateful_ingestion:
          enabled: true

pipeline_name: 'fivetran-default-ingestion'

Expected behavior
A clear and concise description of what you expected to happen.
The ingestion will fail with this error:

<class 'sqlglot.errors.ParseError'>: Invalid expression / Unexpected token. Line 9, Col: 10.
  er_id,
  connector_type_id,
  connector_name,
  paused,
  sync_frequency,
  destination_id
FROM �[4m-�[0mdxxxata-xxx-prod-xxx-.fivetran_logs.connector
WHERE
  _fivetran_deleted = FALS

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

[yoonhyejin]

Thanks for raising the issue, our team will take a look and keep you updated.

[sgomezvillamor]

ingestion failed due to the sqlglot.parse_one has defaulted the dialect to snowflake. See: master/metadata-ingestion/src/datahub/ingestion/source/fivetran/fivetran_log_api.py#L88

The transpilation exists because of a design decision for code maintainability.

• Instead of maintaining separate query templates for each database (Snowflake, BigQuery, etc.), developers only write queries once in Snowflake SQL and they are automatically transpiled to the target dialect
• Easier to maintain one set of complex queries than multiple dialect-specific versions

datahub/metadata-ingestion/src/datahub/ingestion/source/fivetran/fivetran_query.py

Lines 9 to 11 in 5ba8b7d

	class FivetranLogQuery:
	# Note: All queries are written in Snowflake SQL.
	# They will be transpiled to the target database's SQL dialect at runtime.

The problem is likely that BigQuery dataset name contains project ID with hyphens which are not being properly quoted when generating SQL queries, causing SQLGlot to fail when parsing them as Snowflake syntax before transpilation to BigQuery.

If that's the issue, the solution would be to do so quoting in the set_schema

datahub/metadata-ingestion/src/datahub/ingestion/source/fivetran/fivetran_query.py

Lines 20 to 21 in 5ba8b7d

	def set_schema(self, schema_name: str) -> None:
	self.schema_clause = f"{schema_name}."

Something like this may solve the issue:

  def set_schema(self, schema_name: str) -> None:
      # For BigQuery, properly quote identifiers with backticks
      if '.' in schema_name:  # Likely contains project.dataset
          parts = schema_name.split('.')
          quoted_parts = [f'`{part}`' for part in parts]
          self.schema_clause = f"{'.'.join(quoted_parts)}."
      else:
          self.schema_clause = f"`{schema_name}`."

Could you please test this (or similar) fix with your ingestion setup to confirm it fixes the issue?

[hsheth2]

@askumar27 has a PR up for this #14523

[askumar27]

This issue is addressed in the PR: #14523