How does ml-connector stay in sync with Odoo as new records are created and changed?

ml-connector polls Odoo on a schedule you define (typically daily or more frequent) using the write_date field to detect changed records since the last run. Each invoice, PO, payment, or GL entry modified in Odoo is read, mapped to your BigQuery schema, and streamed into the appropriate table. Unchanged records are skipped, so large syncs are fast.

What happens if a BigQuery write fails partway through a load?

ml-connector logs the error with the failed record batch and timestamp in its audit trail. The job stays in the queue and retries on the next scheduled run or manually. BigQuery deduplication via insertId ensures that retried rows do not create duplicate table entries, so recovery is safe and idempotent.

Does the service account need special permissions in Google Cloud?

Yes. The service account used for BigQuery access must have the bigquery.dataEditor role to write rows and the bigquery.jobUser role to run queries. ml-connector refreshes the OAuth2 bearer token automatically every hour before expiry, so you do not need to rotate credentials manually. Store the service account JSON encrypted in ml-connector.

OdooGoogle BigQuery

Odoo and Google BigQuery integration

Odoo runs your accounting, purchasing, HR, and CRM operations. Google BigQuery holds your historical data for analytics, reporting, and machine learning. Connecting the two means every finance transaction, purchase order, payment, and headcount change in Odoo automatically populates your BigQuery data warehouse without manual export or re-keying. Analytics teams can query live ERP data without touching production, and month-end reporting runs against BigQuery tables that stay synchronized with Odoo.

How Odoo works

Odoo exposes invoices, purchase orders, payments, GL accounts, cost centers, products, and employee records through XML-RPC (all versions) and JSON-2 (Odoo 19+) over HTTP POST to instance-specific URLs. Authentication uses an API key paired with a username, and ml-connector obtains a session token before each request. Odoo supports polling with write_date filters for incremental syncs, making it ideal for scheduled warehouse loads. Outgoing webhooks exist in Enterprise editions with Studio but are not production-grade, so polling is recommended for reliable ERP integrations. External API access requires the Custom pricing plan.

How Google BigQuery works

Google BigQuery is accessed via REST JSON over HTTPS at the Google Cloud API endpoint. Authentication uses OAuth2 with a service account private key that signs a JWT and exchanges it for a bearer token valid for one hour. ml-connector creates or appends to existing BigQuery tables using the tabledata.insertAll endpoint, which supports streaming deduplication with user-provided insertIds to prevent accidental duplicate rows. BigQuery has no native webhook subscriptions, so ml-connector polls on a schedule you set via BullMQ cron. The service account requires bigquery.dataEditor and bigquery.jobUser roles for read and write operations. Access tokens refresh automatically before expiry to ensure uninterrupted syncs.

What moves between them

Records flow from Odoo into BigQuery on a schedule you control. ml-connector polls Odoo using a high-water-mark timestamp (write_date), reads changed invoices, purchase orders, GL entries, payments, analytic allocations, and employee records, applies any custom mappings, and streams them into your designated BigQuery tables. Reads are the primary direction. Odoo record IDs and timestamps are preserved so you can join against other warehouse tables and track when each change arrived. If a BigQuery write fails, the record stays in the ml-connector queue and replays when connectivity is restored.

How ml-connector handles it

ml-connector stores your Odoo API key and BigQuery service account JSON encrypted in its database. On each scheduled run, it authenticates to Odoo (XML-RPC or JSON-2 depending on your instance version), fetches records modified since the last sync using write_date filters, and applies any field mappings you have defined. For BigQuery, it exchanges the service account private key for an OAuth2 bearer token (cached for up to one hour), then streams rows using insertAll with insertIds derived from Odoo record IDs and timestamps to prevent duplicates if a retry occurs. BigQuery deduplicates based on insertId within the same table, so retried rows do not create duplicates. If a BigQuery table does not exist, ml-connector can create it on first load with a schema you specify. Every load is logged in the audit trail with timestamp, record count, and any errors. If a Odoo API call fails, ml-connector backs off and retries before surfacing the error. Custom field mappings allow you to rename Odoo columns, filter rows, or join related records before insertion.

A real-world example

A mid-sized e-commerce company runs Odoo for accounting, purchasing, and inventory. The finance team builds monthly reports in Google Sheets and Looker from exported Odoo CSVs, spending hours downloading files and re-uploading them to BigQuery. With Odoo and Google BigQuery connected, every customer invoice, PO, payment, and GL entry syncs automatically to BigQuery on a daily schedule. The business intelligence team writes SQL queries directly against live Odoo data without touching production, builds dashboards in Looker that refresh overnight, and the month-end close process includes reconciliation reports that query the warehouse instead of CSV files. The manual export step is eliminated, and analytics teams always have current ERP data.

What you can do

Sync Odoo invoices, purchase orders, GL entries, and payments to BigQuery on a schedule you control.
Maintain high-water-mark incremental loads using Odoo write_date to avoid re-syncing unchanged records.
Authenticate Odoo with API key credentials and handle XML-RPC or JSON-2 depending on your instance version.
Exchange Odoo record IDs and timestamps to BigQuery for audit tracking and downstream joins.
Prevent duplicate rows in BigQuery using deduplication keys on retry, with full failure replay on connection recovery.

Questions

How does ml-connector stay in sync with Odoo as new records are created and changed?: ml-connector polls Odoo on a schedule you define (typically daily or more frequent) using the write_date field to detect changed records since the last run. Each invoice, PO, payment, or GL entry modified in Odoo is read, mapped to your BigQuery schema, and streamed into the appropriate table. Unchanged records are skipped, so large syncs are fast.
What happens if a BigQuery write fails partway through a load?: ml-connector logs the error with the failed record batch and timestamp in its audit trail. The job stays in the queue and retries on the next scheduled run or manually. BigQuery deduplication via insertId ensures that retried rows do not create duplicate table entries, so recovery is safe and idempotent.
Does the service account need special permissions in Google Cloud?: Yes. The service account used for BigQuery access must have the bigquery.dataEditor role to write rows and the bigquery.jobUser role to run queries. ml-connector refreshes the OAuth2 bearer token automatically every hour before expiry, so you do not need to rotate credentials manually. Store the service account JSON encrypted in ml-connector.

Connect Odoo and Google BigQuery

Free to use. Add your credentials, ping your real systems, and see if we fit.

Get started