Spike in Billing on GCP(BigQuery)

[Varshini_123]

Hi Team,

Currently, we are working on Qlik Replicate. Our source is Oracle, and the target is GCP BigQuery. Since we were not provided ASM privileges, we are using a backup folder to replicate data. we have currently 3 CDC tasks(with and without primary key) that are running in transactional apply mode with the provided SCN.

We are encountering issues while replicating data from Oracle to GCP BigQuery, which is causing a spike in the billing of GCP. Below is the query running frequently in the backend of GCP that is contributing to the spike:

MERGE INTO `CBSPMSV3_EOY`.`CPM_LOAN_ADVANCE` USING ( SELECT CAST( `seg1` as NUMERIC) AS `seg1` FROM `CBSPMSV3_EOY`.`attrep_changesCC33E06820A548FA` WHERE `attrep_changesCC33E06820A548FA`.`seq` >= ? and `attrep_changesCC33E06820A548FA`.`seq` <= ? ) C ON ( `CPM_LOAN_ADVANCE`.`LOAN_ADVANCEID`=`C`.`seg1` ) WHEN MATCHED THEN DELETE

We are seeking your expertise and guidance to provide a solution.

[sureshkumar]

Hello @Varshini_123 

Kindly collect the information in Trace i.e Target_Apply set to Trace and check how frequently Replicate sending this query at the target.

Regards,
Suresh

[SachinB]

Hello @Varshini_123 ,

Could you please check do you have mass delete are happening for table "CPM_LOAN_ADVANCE"?
Kindly check the target table has defined the primary key or are indexed to avoid full table scans. This can lead to high read and compute costs.

Monitor the query performance and resource usage using GCP’s monitoring tools.

If there are concurrent operations on the same tables, it can lead to contention and increased resource usage.

As suggested in previous comment, kindly provide more details to check further and guide you accordingly 

Regards,

Sachin B

[john_wang]

Hello @Varshini_123 ,

I'm not very sure why the Batch Apply Mode is not used, please take note that

• The Transactional apply Change Processing Mode is not supported in GBQ target

The transactional apply mode should be disabled in your task, it will make huge cost in your cloud services.

Feel free to let us know if you facing any difficulties to work with Batch Apply Mode.

Regards,

John.

[Jenil_Sangoi]

We have moved to slot based pricing and use the service account from project that has slot based pricing. You can explore that option as well if you think the data is geniune.