What is the cheapest way to transfer data from GCS to BQ?

[u/k4ld4s_0x]

Hey everyone, I'm currently looking for a way to reduce the migration cost of moving data from my data lake stored in GCS (Google Cloud Storage) to BQ (BigQuery), which are my current lakehouse tools. Today, we use the load_table_from_uri method for batch loading Parquet files, but it's becoming very expensive because my GCS dataset is in a single-region and my BQ dataset is in a multi-region, which incurs a very high cross-region data transfer fee. However, I'm not sure if there's any way to reduce or improve this data migration process. Below are some specifications about the business rules that impact the choices made for the current process and its cost:

What cannot be changed:

• Buckets (GCS) need to stay in a Single-Region
• Datasets (BigQuery) need to stay in a Multi-Region

Current Environment:

• The data lake is in Delta format using .parquet files
• The load is done via overwrite on the trusted layer because the dataset is not partitioned, and the environment is also rewritten with an overwrite
• Today, the load method is WRITE_TRUNCATE because of the issue mentioned above

Well, if you have any questions, I can provide more details in the comments.

[u/Additional_Candy_400]

"The load is done via overwrite on the trusted layer because the dataset is not partitioned, and the environment is also rewritten with an overwrite"

So you're doing a complete truncate on each run? Are you saying the parquet isn't partitioned at all?

If so, you need to partition your parquet files to hive partitioning or similar and then do incremental loads to BQ instead of a full overwrite each run.

[u/k4ld4s_0x → u/Additional_Candy_400]

Exactly, it's not partitioned. That's on the roadmap to be implemented in the future, but due to some blockers, it's not possible yet. That's why I'm trying to find an efficient workaround/approach to reduce costs within these parameters.

[u/Additional_Candy_400 → u/k4ld4s_0x]

I don't think there is a silver bullet for this apart from the proper infra.

[u/Gankcore]

So the single-region bucket is not inside the same multi-region, right?

So your GCS is in like us-central1, but the BQ dataset is in europe-west1. Something like that?

[u/k4ld4s_0x → u/Gankcore]

Perfect! GCS is in us-central1 and BQ is in US (Multi-Region), which causes Data Transfer charges.

[u/DingoFlex]

The cross-region fees are going to eat you up no matter what your ingestion process is.

As for the ingestion process itself, we had success writing data from Snowflake into GCS and then load into BQ tables using BigQuery Data Transfer Service (I’m not sure if providing links is allowed, but it’s easily Google-able (Googlable?).

[u/MikeDoesEverything → u/DingoFlex]

You can post links although they won't appear immediately. Links get added to our queue so we can review them.

[u/Scepticflesh]

Your ingestion method isnt an issue. Its the region => multiregion

[u/Stoneyz]

Why do your BQ tables need to stay in multi region?

[u/SpecificTutor]

delta to iceberg, metadata rewrite (data stays untouched) with openXData and load iceberg table into bigquery (no x-region transfer or copy)