Pigment + Google BigQuery
Connect Pigment and Google BigQuery for Smarter Business Planning
Sync your enterprise planning data with your cloud data warehouse for faster, more accurate forecasts and decisions.
Why integrate Pigment and Google BigQuery?
Pigment is a modern business planning platform built for finance, sales, and operations teams that need real-time visibility and agile forecasting. Google BigQuery is a fully managed, serverless data warehouse built for large-scale analytics. Together, they give you a solid foundation for connecting raw business data with the planning models that drive company decisions.
Automate & integrate Pigment & Google BigQuery
Use case
Automated Actuals Ingestion into Pigment
Automatically pull financial and operational actuals — revenue, costs, headcount, pipeline data — from BigQuery into Pigment on a scheduled or event-driven basis. Your planning models stay continuously updated without manual intervention, so variance analysis against budget always reflects the latest numbers.
Use case
Writeback of Approved Plans to BigQuery
Once plans, budgets, or forecasts are finalized and approved in Pigment, automatically write them back to Google BigQuery so downstream BI tools, dashboards, and reporting layers can consume the latest approved numbers. One source of truth across your entire data stack.
Use case
Real-Time Sales Forecast Synchronization
Stream CRM and pipeline data from BigQuery into Pigment's sales planning models in near real-time, so sales forecasts reflect the latest deal stages, ARR changes, and win/loss events. Sales ops and revenue teams get a continuously updated forecast without rebuilding models each cycle.
Use case
Headcount and Workforce Planning Data Sync
Pull headcount actuals, new hire data, attrition, and compensation details stored in BigQuery into Pigment's workforce planning models. HR and finance teams can model future headcount scenarios knowing the baseline data is current and sourced directly from the warehouse.
Use case
Marketing Spend and Performance Data Integration
Aggregate marketing spend, campaign performance, and channel attribution data from BigQuery and push it into Pigment so marketing teams can plan budgets against actual ROI. It closes the loop between performance analytics and the planning process, making budget allocation more data-driven.
Use case
Automated Variance Reporting Pipelines
Trigger automated workflows that compare plan data from Pigment against actuals in BigQuery, compute variances, and push summary results back to BigQuery or notify stakeholders via collaboration tools. Business performance gets monitored continuously against plan, with no manual analysis required.
Use case
Scenario Planning Output Distribution
When planning teams publish new scenarios or updated forecasts in Pigment, automatically distribute those outputs to Google BigQuery so data engineering and analytics teams can incorporate them into dashboards, models, and downstream pipelines without waiting for manual handoffs.
Get started with Pigment & Google BigQuery integration today
Pigment & Google BigQuery Challenges
What challenges are there when working with Pigment & Google BigQuery and how will using Tray.ai help?
Challenge
Schema Drift Between BigQuery Tables and Pigment Models
BigQuery schemas evolve as data engineering teams add columns, rename fields, or restructure tables. When that happens without coordination, pipelines pushing into Pigment break or silently load incorrect data, and planning models end up reflecting inaccurate actuals — often without anyone noticing until review time.
How Tray.ai Can Help:
tray.ai's data transformation layer lets teams define explicit field mappings with fallback logic, so when upstream BigQuery schemas change, workflows can be updated centrally without rebuilding entire integrations. Tray also supports alerting on unexpected null or missing fields, giving teams early warning of schema drift before it corrupts planning data.
Challenge
Managing Large Data Volumes from BigQuery Without Timeouts
BigQuery datasets used for planning can contain millions of rows of transactional data. Trying to sync large result sets in a single API call regularly leads to timeouts, memory issues, or Pigment API rate limit errors.
How Tray.ai Can Help:
tray.ai supports pagination, batching, and chunked processing natively, so workflows can break large BigQuery result sets into manageable page sizes before loading them into Pigment. Built-in retry logic and configurable concurrency controls keep large sync jobs running reliably without manual babysitting.
Challenge
Keeping Data Consistent Across Bidirectional Sync
When data flows both from BigQuery into Pigment and from Pigment back into BigQuery, you can end up with circular updates, duplicate records, or conflicting versions of the truth if the sync logic isn't designed with idempotency in mind.
How Tray.ai Can Help:
tray.ai workflows can track state using connector metadata or external lookup tables to record last-sync timestamps and checksums, preventing circular writes and ensuring each record is only processed once per sync cycle. Conditional logic in tray.ai lets teams enforce clear directionality rules for each data type.
Challenge
Authenticating and Governing Access to Sensitive Planning Data
Pigment holds sensitive financial plans, headcount data, and strategic forecasts. BigQuery holds raw business data across the organization. Controlling which data flows between them — and who can configure those flows — is a real compliance requirement, not just a nice-to-have.
How Tray.ai Can Help:
tray.ai has a secure credential management store for OAuth and service account credentials, so BigQuery service account keys and Pigment API tokens are stored encrypted and never exposed in workflow logic. Role-based access controls mean only authorized team members can view or modify integration workflows that touch sensitive planning data.
Challenge
Aligning Sync Timing with Planning Cycles and Data Availability
Pigment users often need actuals data from BigQuery at specific points in the planning cycle — month-end close, quarterly review — but the BigQuery pipelines feeding that data can finish at unpredictable times. Simple time-based schedules don't account for that, and running a sync before upstream data is ready causes real problems.
How Tray.ai Can Help:
tray.ai supports event-driven triggers alongside scheduled ones, so Pigment sync workflows can fire only after upstream BigQuery pipelines have completed and data is confirmed ready. Workflow chaining and conditional trigger logic let teams build dependency-aware pipelines that respect data availability rather than running on rigid clock schedules.
Start using our pre-built Pigment & Google BigQuery templates today
Start from scratch or use one of our pre-built Pigment & Google BigQuery templates to quickly solve your most common use cases.
Pigment & Google BigQuery Templates
Find pre-built Pigment & Google BigQuery solutions for common use cases
Template
Daily BigQuery Actuals Sync to Pigment
Runs on a daily schedule to query the latest financial or operational actuals from a specified BigQuery dataset and load them into the corresponding Pigment model, keeping plan-vs-actual comparisons continuously updated.
Steps:
- Trigger on a daily scheduled interval via tray.ai workflow scheduler
- Execute a parameterized BigQuery SQL query to fetch new or updated actuals records since last sync
- Transform and map the query results to match Pigment's data model schema
- Use the Pigment connector to upsert records into the target planning module
- Log sync results and send a Slack or email notification on completion or error
Connectors Used: Google BigQuery, Pigment
Template
Pigment Approved Plan Writeback to BigQuery
Automates the export of finalized plans, budgets, or forecasts from Pigment into a dedicated BigQuery dataset, making approved planning outputs immediately available to BI tools and downstream data consumers.
Steps:
- Trigger when a plan or forecast version is marked as approved in Pigment via webhook or scheduled poll
- Fetch the approved plan data from Pigment using the Pigment API connector
- Transform and flatten nested planning data into a BigQuery-compatible tabular format
- Stream or batch insert the transformed records into the target BigQuery table
- Update a metadata log table in BigQuery with sync timestamp and row counts
Connectors Used: Pigment, Google BigQuery
Template
Real-Time Pipeline Data Push from BigQuery to Pigment
Monitors a BigQuery table for new or updated CRM pipeline records and pushes changes into Pigment's sales planning model, so forecast models always reflect the current state of the sales pipeline.
Steps:
- Trigger on a near-real-time schedule or via BigQuery pub/sub event notification
- Query BigQuery for pipeline records updated since the last successful sync run
- Apply field mappings and data transformations to match Pigment's sales model structure
- Upsert updated pipeline records into the relevant Pigment sales planning dimension
- Record sync metrics to a BigQuery audit table for monitoring and reconciliation
Connectors Used: Google BigQuery, Pigment
Template
Variance Alert Workflow: Pigment vs. BigQuery Actuals
Compares plan data from Pigment against actuals stored in BigQuery on a scheduled basis, automatically alerting finance stakeholders when variances exceed configurable thresholds.
Steps:
- Trigger on a weekly or monthly schedule aligned to reporting cadences
- Fetch current period plan figures from Pigment via API
- Query BigQuery for the corresponding actuals for the same period and dimensions
- Calculate variance percentages and identify records that breach defined thresholds
- Send formatted variance summary alerts to finance stakeholders via email or Slack
Connectors Used: Pigment, Google BigQuery
Template
Headcount Actuals Sync from BigQuery to Pigment Workforce Model
Pulls the latest employee headcount, compensation, and attrition data from BigQuery — aggregated from HRIS systems — and syncs it into Pigment's workforce planning module on a regular cadence.
Steps:
- Trigger on a bi-weekly or monthly schedule to align with payroll cycles
- Run a BigQuery query to retrieve current headcount and compensation actuals by department and role
- Normalize and map employee data fields to Pigment's workforce planning model schema
- Load headcount actuals into Pigment, updating existing records and inserting new hires
- Flag discrepancies or unmatched records for manual review in a reconciliation report
Connectors Used: Google BigQuery, Pigment
Template
Marketing Spend Actuals Pipeline: BigQuery to Pigment
Aggregates marketing channel spend and performance data from BigQuery and loads it into Pigment's marketing planning models, so teams can run budget vs. actual comparisons and reforecast based on real numbers.
Steps:
- Trigger on a weekly schedule or after marketing data pipelines complete in BigQuery
- Query BigQuery for aggregated spend and performance metrics by channel, campaign, and time period
- Apply currency normalization and field mapping to match Pigment's marketing model structure
- Upsert aggregated spend data into the Pigment marketing planning module
- Send a summary report to marketing and finance stakeholders confirming successful data refresh
Connectors Used: Google BigQuery, Pigment