@kapa.ai I built a pipeline which extracts data from BigQuery table (size 65 GB) and loading it into Databricks lakehouse.. The connector is failing because as Airbyte runs SELECT * query.. Bigquery is throwing error:

Response too large to return. Consider specifying a destination table in your job configuration. For more details, see <https://cloud.google.com/bigquery/troubleshooting-errors>

How can this be solved ?

I'm sorry, but the knowledge sources do not contain specific information about this exact error ("Response too large to return") when using the BigQuery source connector with a 65 GB table syncing to Databricks. However, based on related context in the knowledge sources, here are some general directions that may help: Possible Approaches 1. Use Incremental Sync Instead of Full Refresh Rather than running a full

SELECT *

on a 65 GB table every sync, switch to Incremental Sync mode using a cursor field (e.g., a timestamp or auto-incrementing ID). This limits the query to only new/changed records. [BigQuery Source Docs] > The BigQuery source connector implements incremental sync by querying with

WHERE cursor_field > last_cursor_value

, fetching only new records. 2. Partition or Cluster Your BigQuery Table If your table is partitioned by date/timestamp, choosing a cursor field that aligns with the partition column can significantly reduce the bytes scanned per query — and may help avoid the "response too large" error by limiting result set sizes. [BigQuery Source Docs] 3. Split the Data into Smaller Chunks A similar large-response issue in the Airbyte CDK was addressed by recommending users split data into smaller chunks through pagination or partitioning. [Forum: Debugging large data] 4. Specify a Destination Table in BigQuery The error message itself (

Consider specifying a destination table in your job configuration

) is a BigQuery-side suggestion. This is a BigQuery limitation when query results exceed ~10 GB. You may need to raise this with Airbyte's support or GitHub issues, as it would require the BigQuery source connector to route large query results through a temporary destination table before exporting. For the most accurate resolution, I'd recommend: • Opening a GitHub issue with the Airbyte team describing this specific error • Reaching out via Airbyte Community Slack for peer support • If you're on Airbyte Cloud, opening a support ticket 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai Even if I do the parttitioning on table... Airbyteis still going to scan the full table.. right ?

You raise a valid point. Based on the knowledge sources, partitioning alone won't prevent a full table scan — it depends on how the cursor field aligns with the partition column. The BigQuery source connector generates queries like:

SELECT * FROM table WHERE cursor_field > last_cursor_value

The docs note: [BigQuery Sync Modes] > Good: cursor field matches partition column >

WHERE _PARTITIONTIME > @cursor_value

> Less optimal: cursor field differs from partition column >

WHERE updated_at > @cursor_value AND _PARTITIONTIME >= '2023-01-01'

So: • ✅ If your cursor field matches the partition column → BigQuery will use partition pruning and only scan relevant partitions, significantly reducing data scanned. • ❌ If your cursor field does NOT match the partition column → BigQuery will still scan the full table, and the "response too large" error will persist. That said, the knowledge sources do not explicitly address a solution to the "Response too large to return" error itself. This appears to be a BigQuery-side hard limit on query result sizes (~10 GB for interactive queries), and resolving it at the connector level would likely require Airbyte to route results through a temporary BigQuery destination table — which is not a documented feature of the current connector. I'd recommend raising this as a GitHub issue with the Airbyte team for a proper fix. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai Incremental is fine.. I am talking about the firt run.. when historical data will be fetched

You're absolutely right — the core issue is the first/initial sync, where Airbyte performs a full historical data load regardless of sync mode. Unfortunately, the knowledge sources confirm this is a known limitation: > "Currently this isn't possible - the first sync is always a 'full refresh' sync that gets all the data." [Forum: Partial Data Fetching] Workarounds Suggested by Airbyte 1. Create a BigQuery View with a Date Filter Instead of pointing Airbyte at the full 65 GB table, create a view that limits the data:

CREATE VIEW my_dataset.my_table_view AS
SELECT * FROM my_dataset.my_table
WHERE created_at >= '2024-01-01'

Then sync the view instead of the raw table. This way the initial load only fetches the filtered subset of data, avoiding the "response too large" error. After the first sync, switch to incremental mode on top of this view. [Forum: Partial Data Fetching] [Forum: Initial Loading Large Dataset] 2. Use Separate Connections for Historical Batches Set up multiple connections, each pointing to a view covering a specific date range (e.g., month by month), sync them sequentially, then switch to a single incremental connection going forward. [Forum: Partial Resync] The view-based approach is the most commonly recommended workaround in the Airbyte community for avoiding large initial loads. The knowledge sources do not mention any native Airbyte feature to set a start date for the initial sync on the BigQuery source connector. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.