Pushing CDC data from pubsub to BQ

[sgk]

Hi All,

I would like to understand what are the ways by which I can handle the CDC data present in pubsub & push to BigQuery. I don't want to push the raw cdc data but apply those changes to the respective BQ tables.

Any pointers regarding the same would be helpful.

Thanks,
Gopi

[ms4446 · Accepted solution]

Hi @sgk ,

Its definitely a good idea to look beyond merely pushing raw data and are aiming to enhance data integrity and efficiency. Here are some strategies you might consider, depending on your specific needs:

1. Datastream to BigQuery:

   • Ideal for: Cases where you are using a source compatible with Google Datastream (e.g., MySQL, PostgreSQL).
   • Benefits: Offers native integration and a managed service, simplifying setup and maintenance.
   • Limitations: Limited to sources and configurations that Datastream supports.

2. Dataflow Templates:

   • Ideal for: Basic transformations or filtering of CDC data.
   • Benefits: Utilizes pre-built templates to streamline processes; allows for some level of customization.
   • Considerations: Involves setting up and managing Dataflow pipelines, which can add to overhead.

3. Custom Dataflow Pipelines:

   • Ideal for: Advanced transformation needs, intricate error handling, or specific CDC logic requirements.
   • Benefits: Provides complete control over the pipeline's design and implementation.
   • Considerations: Requires deep expertise in Dataflow and Apache Beam, making it suitable for more complex requirements.

Choosing the Right Approach:

• Data Source: If your data source is supported by Datastream, this option is generally the most straightforward.
• Transformation Needs: For basic transformations, Dataflow templates are adequate, whereas custom pipelines offer greater flexibility for complex scenarios.
• Management Overhead: Consider the balance between ease of integration and the need for ongoing management of pipelines.

Additional Points to Consider:

• Data Volume and Latency: Make sure your chosen method can accommodate the expected data volume and latency.
• Error Handling: It’s essential to have robust error handling processes in place to safeguard against data inconsistencies.
• Cost Optimization: Take into account the costs associated with compute resources, operational overhead, and any potential data transfer fees.

Hopefully, this information helps you as you decide on the best approach for integrating CDC data into BigQuery.