In your situation, even if the MongoDB schema remains unchanged, the Dataflow MongoDB CDC to BigQuery template might still have trouble processing the data. This is because the template doesn’t automatically flatten or transform the Pub/Sub messages to fit the schema of your BigQuery table.
The Dataflow template is designed to make the process easier but doesn’t offer much flexibility for complex tasks like flattening nested data structures or adjusting schemas. So, even if you create a BigQuery table with all the required columns, the template might not correctly insert the data because it doesn’t reshape the data from Pub/Sub to match your BigQuery schema.
Since you’re working with multiple collections that need to be flattened and prefer using the template for its ease of use, one solution is to add a preprocessing step. This step could involve using a Cloud Function or a lightweight Dataflow job to listen to the Pub/Sub topic, flatten and format the data to match your BigQuery schema, and then send it to another Pub/Sub topic. The Dataflow template could then process this correctly formatted data.