How Airflow runs BigQuery work with the Google provider
A team that schedules BigQuery queries, loads and data checks from Airflow needs one answer first: which part of the Google provider does that work. The integration is the set of BigQuery operators, check operators and sensors in the Google provider, not hand-written client code inside a Python task. The BigQueryInsertJobOperator covers dataset, table and job work, including queries and loads, and the check operators (BigQueryCheckOperator, BigQueryValueCheckOperator, BigQueryIntervalCheckOperator, BigQueryColumnCheckOperator and BigQueryTableCheckOperator) cover data quality. Table, partition and streaming buffer sensors let a DAG wait for data to land before downstream steps start. The provider documentation lists each operator and its parameters, and it is the reference to read before writing the first DAG.
The two execution behaviors that matter most for production are durable mode and deferrable mode. From Airflow 3.3, BigQueryInsertJobOperator runs in durable mode by default, which saves the job ID so that a retried task reconnects to the running BigQuery job instead of submitting a duplicate. Deferrable mode moves the waiting for job completion onto the triggerer, so long BigQuery jobs do not hold a worker slot. Plan capacity for the triggerer as a component of its own. Confirm with your team which Airflow 3.x release you will run, and test the retry path in staging, since the default behavior depends on that version. The provider documentation also notes that the implicit use_legacy_sql default is deprecated, so set it explicitly in every job configuration you write.
All of this runs in your own Airflow environment: the scheduler, the triggerer, the workers and the metadata database are components you deploy and operate in your infrastructure. BigQuery is the external service that executes the SQL and holds the data, reached through a Google Cloud connection that you configure. Decisions to make before build include which service account the connection uses, how that credential is stored and rotated, which datasets and projects the account can reach, and where job configuration is kept under version control. Ask the team that will own the DAGs to confirm which provider package version they will pin, and check the release notes for any change to these operators before upgrading.
What the BigQuery integration needs before the first DAG
Before any BigQuery operator runs, the platform needs a supported Airflow 3 base, a pinned Google provider, a Google Cloud identity with narrow permissions, and network paths to Google APIs. The dataset and table design also needs agreement, because it shapes every DAG that follows.
Airflow 3 base and metadata database
Airflow 3.3.2 lists Python 3.10 to 3.14, PostgreSQL 14 to 18, MySQL 8.0, 8.4 and Innovation releases, and Kubernetes 1.30 to 1.35 as prerequisites. Use PostgreSQL or MySQL for the metadata database, since SQLite is for testing only and MariaDB is not supported. Confirm the base image and Python version against these lists before installing anything.
Google provider package and pinning
BigQuery operators, sensors and check operators ship in the Google provider, not in core Airflow. Install it with a version constraint that matches your Airflow release, then pin the exact provider version in your constraints file so workers and the triggerer run identical code. Confirm provider compatibility with the provider documentation before each upgrade.
Google Cloud project and service account
Create a dedicated project or folder for the pipelines and a service account per environment. Grant only the BigQuery job, dataset and table roles the DAGs need, and decide whether credentials come from a Google Cloud connection backed by a secrets store or from workload identity. Ask your cloud team how keys are issued, rotated and revoked.
Outbound access to Google APIs
Airflow workers run query submission and result handling, and the triggerer polls job status in deferrable mode, so both need outbound HTTPS to Google APIs. Confirm that egress rules, proxies and private connectivity cover both components. In air-gapped or restricted networks, confirm with Google and your network team which endpoints are reachable before the first run.
Dataset and table design
Agree on dataset names, locations, owners and environment separation (development, staging and production) before writing DAGs. Decide how partitioned and clustered tables are named and which team owns schema changes. These choices determine partition sensors, check operators and access grants, and they are costly to rename later.
Query dialect and job defaults
Decide the SQL dialect for every job up front. The provider's implicit use_legacy_sql default is deprecated, so set the dialect explicitly in each job configuration. Agree on default labels, job priority and location so cost reports and incident reviews can trace each job back to its DAG and owner.
Configuring the Google connection and first BigQuery DAG
This sequence takes a team from an installed Google provider to a tested DAG that runs a BigQuery query, waits on upstream data, and gates the run on a data quality check. Each step ends with a visible check so the team can confirm the work before moving to production.
Install the Google provider package
The platform engineer installs the Google provider into the same Airflow environment that runs the scheduler, triggerer and workers, using the constraints file that matches the Airflow 3 release in use. Installing the package only on the scheduler is a common mistake, because tasks fail when a worker cannot import the operators. The step is done when the provider appears in the installed package list on every component image and a DAG file that imports BigQueryInsertJobOperator parses without errors in the DAG processor.
Create the service account connection
The platform engineer creates a dedicated Google Cloud service account for Airflow and grants only the BigQuery roles that the planned jobs need, typically dataset-level read or write on named datasets plus permission to run jobs in the chosen project. The key or workload identity configuration is stored in a connection rather than in DAG code, and the secret location follows the team's secrets design. Confirm the exact role set with the security team and against the Google provider documentation before granting it. The step is done when the connection test succeeds and the service account can list only the datasets it was granted.
Set the default project and location
The platform engineer sets the default project and the dataset location in the connection or in the operator arguments so that every job runs in a known project and region. Leaving the project implicit makes jobs depend on whichever credentials are active, which complicates audit and cost attribution. The step is done when a manual test task reports the expected project ID in its logs and the job appears under that project in the BigQuery job history.
Write the first query with explicit SQL dialect
The data engineer writes a BigQueryInsertJobOperator task that runs a query, with the configuration setting use_legacy_sql set explicitly to false so the query uses standard SQL. The Google provider documents that the implicit default is deprecated, so relying on it leaves the DAG exposed to a behavior change. The query writes to a named destination table or runs as a read-only statement, depending on the design. The step is done when a staging run completes and the job detail in the BigQuery job history shows the standard SQL dialect and the expected destination table.
Enable deferrable waiting on the triggerer
The data engineer switches the same task to deferrable mode so the worker slot is released while BigQuery runs the job and the triggerer polls for completion. The team confirms that a triggerer process is running and healthy in the target environment, because deferred tasks do not progress without it. The step is done when a staging run shows the task in a deferred state in the Airflow UI, the worker slot is freed during the wait, and the task resumes and succeeds when the job finishes.
Add a table or partition sensor
The data engineer adds a sensor task upstream of the query that waits for a table or a specific partition to exist, so the query runs only after the source data has landed. The sensor is configured with the project, dataset, table and, where relevant, the partition value, and the team decides whether it runs in deferrable mode based on how long the upstream load usually takes. The step is done when a test run shows the sensor passing once the source partition is present and failing or waiting when it is absent, which the team can confirm by temporarily pointing it at a missing partition.
Add a data quality check operator
The data engineer adds a check operator after the query, such as a column or table check, that asserts a condition on the output, for example a row count or a non-null rule on a key column. A failed check stops downstream tasks, which keeps bad data from reaching consumers. The team agrees with the data owners which thresholds count as a failure before the check goes live. The step is done when a staging run passes with clean data and, when the team deliberately feeds a failing condition, the check task fails and the downstream tasks are marked as upstream failed.
Confirm the run in job history
The data engineer compares the Airflow task logs with the BigQuery job history for each run, matching job IDs, destination tables and bytes processed to the expected results. This confirms that the operator submitted the jobs it reported and that retries reconnect to existing jobs rather than creating duplicates. The step is done when a complete staging run has one matching job per query task, with no unexpected duplicate jobs, and the team signs off the DAG for promotion through its normal review process.
How durable job reconnects change retry behavior
From Airflow 3.3, the Google provider's BigQueryInsertJobOperator runs in durable mode by default. The operator saves the BigQuery job ID, so when a task retries it reconnects to the job that is already running instead of submitting a new one. This changes what a retry means. Before this behavior, a retry could submit the same statement again. Now the main risk shifts to the first submission and to any job that was accepted but whose result the worker never saw. For loads and DML statements such as INSERT, MERGE or UPDATE, the team should still confirm in writing that a second execution cannot double-write rows, because a reconnect protects only the cases the provider can detect.
Deferrable mode is the other lever. In deferrable mode, polling for job status moves to the triggerer, so the worker slot is released while BigQuery works. The trade-off is that the job's lifecycle now depends on the triggerer process being healthy and sized for the number of waiting tasks. A triggerer outage, a restart or a pod eviction affects in-flight jobs in a different way than a failed worker does, so the team should test what happens when the triggerer is stopped during a long load before relying on it for production data movement. Durable mode and deferrable mode are separate settings, and each should be verified on its own in the pinned provider version.
Confirm the exact reconnect and deferral behavior in the provider version you pin, since defaults and parameters have changed across releases, and read the operator reference in the Google provider BigQuery operators documentation before you set retries. Set retries on each BigQuery task deliberately rather than inheriting a DAG-wide default: idempotent queries and full table rewrites can tolerate retries, while append-only loads and DML need either an idempotency key, a write-to-staging-then-swap pattern, or a check that inspects the target before rerunning. Test a forced failure in staging for each write pattern and record the outcome as change-management evidence.
Choosing between worker waits, deferrable mode and sensors
A synchronous BigQueryInsertJobOperator submits a job and then keeps a task slot occupied on a worker while it polls BigQuery until the job finishes. The worker does useful work only at the start and end of the task, so the slot is held for the full duration of the query or load. This is the simplest model to reason about and debug, because the task log shows the job from submission to completion in one place.
- Slot cost: the task occupies a worker slot for the entire job runtime, including time spent only waiting.
- Triggerer cost: none, since no triggerer work is involved.
- Good fit: short jobs, small DAGs with few concurrent BigQuery tasks, and environments where worker capacity is generous.
- Risk: long loads or many parallel queries can exhaust worker pools and delay unrelated tasks.
- Retries: with durable mode on Airflow 3.3 and later, a retry reconnects to the saved job ID instead of resubmitting it.
Before choosing this model, confirm your worker pool size, your expected number of concurrent BigQuery tasks and the typical job duration with your own data team.
In deferrable mode the operator submits the job, releases its worker slot, and hands status polling to a trigger running on the triggerer. When the job completes, the task resumes on a worker. Waiting then costs triggerer capacity, which is a lightweight async process, rather than a worker slot. This suits long-running queries and loads, where most of the elapsed time is spent waiting for BigQuery.
- Slot cost: the worker slot is released during the wait and only used again when the task resumes.
- Triggerer cost: each deferred task adds a trigger that polls BigQuery, so triggerer capacity must grow with the number of concurrent waits.
- Good fit: long jobs, high fan-out DAGs and shared clusters where worker slots are scarce.
- Risk: the triggerer becomes a single point of attention, so its health and logs need monitoring.
- Scope: the operator's deferrable option applies to the operators that support it, so confirm support for each operator you plan to use in the Google provider documentation.
Ask your platform team how many triggerer replicas run in your environment and how they are monitored before you rely on this model at scale.
Sensors wait for a condition in BigQuery, such as a table existing, a partition being present or the streaming buffer being empty, before downstream tasks run. They are useful when data arrives from another system on its own schedule and the pipeline must not start early. Sensors can hold a worker slot while they poke, so their mode and poke interval determine how much capacity they consume.
- Table sensors: wait for a table to exist before a dependent load or check begins.
- Partition sensors: wait for a specific partition, which fits daily or hourly loads from upstream feeds.
- Streaming buffer sensors: wait until recently streamed rows are no longer in the buffer, so that exports or checks read stable data.
- Slot cost: a sensor in poke mode holds a worker slot between checks. Reschedule or deferrable behavior, where the provider supports it, releases the slot between checks.
- Good fit: pipelines gated by upstream arrival, where the wait time is unpredictable.
Confirm which sensor modes the provider version in your image supports, and whether your team can accept the query cost and load of repeated metadata checks.
The right model depends on how long each wait lasts, how many waits run at once and what shares the cluster. Short, infrequent jobs rarely justify extra triggerer capacity, while long or numerous waits can exhaust worker slots quickly. Many DAGs use a mix: synchronous operators for quick steps, deferrable operators for long jobs and sensors for arrival conditions. The decision should be written into the DAG design review, not left to individual authors.
- Measure first: record typical job durations and peak concurrent waits from your current runs.
- Count slots: compare peak waits against worker pool and pool slot limits.
- Count triggers: estimate deferred tasks at peak against triggerer capacity.
- Mix deliberately: assign each BigQuery task to one model based on its expected duration.
- Re-check on upgrades: operator and sensor behavior can change between provider releases.
Before finalizing the design, ask your Airflow administrators for pool limits, triggerer count and recent peak concurrency figures.
Mapping Airflow connections, projects and datasets to BigQuery
This table maps each piece of BigQuery configuration to the Airflow object that should hold it, where the value should live, and who owns it. Read it as a division of responsibility: platform teams own connection and secret storage, while DAG authors own the job definitions that reference those values. The main takeaway is that project, service account and location should be set once in the connection, while dataset and table names stay in DAG parameters so the same code can run in several environments.
| Mapping | Where the value should live | Owner |
|---|---|---|
| Google Cloud project ID | Google Cloud connection, or a secrets backend entry referenced by the connection | Platform team |
| Service account identity | Connection using a secrets backend or workload identity, never the key file in DAG code | Platform or security team |
| Default location (dataset region) | Connection extra or a documented variable, checked against each dataset's location | Platform team with data owners |
| Dataset and table names | DAG parameters or Airflow variables per environment, passed into the job configuration | DAG authors with data owners |
| Query job configuration | Operator argument in the DAG, with use_legacy_sql set explicitly rather than relying on the default | DAG authors |
| Load job configuration (source URIs, format, write disposition) | DAG parameters for paths, with bucket access granted through the connection's identity | DAG authors and data engineers |
| Copy job configuration | Operator argument referencing source and destination tables from DAG parameters | DAG authors |
| Partition and sensor targets | DAG parameters matching the partition or table each sensor watches | DAG authors |
Using check operators for data quality gates
Each BigQuery check operator asserts something different, and choosing the right one decides what a failure means. BigQueryCheckOperator runs a SQL statement and fails when the first row of the result is falsy, which suits yes or no conditions such as a partition containing rows. BigQueryValueCheckOperator compares a single returned value with an expected value within a tolerance, which fits row counts or totals against a known target. BigQueryIntervalCheckOperator compares today's metric with a earlier reference window, so it catches drift rather than absolute errors. BigQueryColumnCheckOperator applies a set of named checks, such as null counts or uniqueness, to columns declared in the configuration, and BigQueryTableCheckOperator applies table-level expressions across the whole table. Confirm the exact parameter names and check syntax against the Google provider documentation for the provider version you pin, because these operators have changed across releases.
A failed check only protects downstream data if the dependency graph is wired to stop. Place each check between the load task and the publish or transform tasks, so that a failing check marks itself failed and its downstream tasks do not run under the default trigger rule. Avoid trigger rules that let downstream tasks run after a failed upstream, and review any all_done or none_failed settings on the tasks after the gate. Decide with the data owner whether a failed gate should alert on-call, quarantine the load, or hold the run for manual release, and make sure the retry policy does not reload the same bad batch repeatedly.
Thresholds belong to the data owner, not to the engineer writing the DAG. Agree on row count bounds, null tolerances and allowed drift in writing, and record who approves changes. Where practical, store these values outside DAG code, for example in Airflow Variables, in a versioned configuration file reviewed through your change process, or in a secrets backend for sensitive values, so that a threshold change does not require a DAG redeploy and leaves an audit trail. Keep the check SQL in version control alongside the tests for the DAG, and confirm with your data owners which thresholds they must sign off on for regulated datasets.
Securing the service account and BigQuery access
BigQuery access from Airflow is only as safe as the identity behind it and the places that identity can be read or changed. The cards below cover the service account's roles, where its key lives, who can edit connections, how job activity is logged, and what the metadata database reveals if it is compromised.
Scope roles to datasets
Grant the Airflow service account BigQuery roles at the dataset level wherever possible, not across the whole project. A DAG that loads one staging dataset should not be able to read finance tables or create datasets elsewhere. Before go-live, list every role on the account and confirm each one maps to a specific DAG or dataset, so a compromised worker has a bounded blast radius.
Keep project-wide keys out of DAG code
A service account key pasted into a DAG file or a Python module ends up in version control, deployment bundles and task logs. Reference credentials only through an Airflow connection or a secrets backend, and make sure DAG code contains no key material. Ask whether the organization's policy allows long-lived JSON keys at all, or whether workload identity from the Airflow host is an option in your environment.
Store the key in a secrets backend
Put the service account credential in your secrets backend rather than the metadata database, and let Airflow resolve the connection at runtime. Rotation then happens in one place without editing DAGs or redeploying workers. Confirm which secrets backend your environment uses, how Airflow authenticates to it, and whether rotation is scheduled and tested.
Limit who can edit connections
Anyone who can change a BigQuery connection in the Airflow UI can redirect a DAG to a different project or swap in their own credential. Restrict connection create, edit and read permissions to a small operator group through the auth manager you run, and separate that role from DAG authors. Review the mapping whenever team membership changes.
Audit BigQuery job activity
Enable Cloud Audit Logs for BigQuery in the projects Airflow touches, and route them to a store your security team controls. Tie entries to the service account and the job labels your DAGs set, so a query can be traced to a DAG run. Confirm retention and who can read the logs, because the audit trail is useless if the same identity can delete it.
Treat the metadata database as sensitive
The Airflow metadata database stores connection records, variables, DAG run history, and XCom values, which can include query results or identifiers. Even when secrets are kept elsewhere, a database dump can reveal project names, dataset layouts and job patterns. Apply the same access restrictions, encryption and backup controls you use for other databases holding credential-adjacent data, and review Airflow's security settings for masking and encryption options.
Common BigQuery failures from Airflow and how to fix them
This table lists the failures teams most often hit when BigQuery work runs from Airflow, with the likely cause and the fix for each. Read each row as a symptom to match against your task logs, then check the cause in your own project, location and Airflow deployment before changing code.
| Failure | Likely cause | Fix |
|---|---|---|
| Permission denied on datasets | The Google connection's service account lacks the BigQuery role needed for the dataset or job, or the job runs in a project the account cannot bill to. | Confirm which identity the Airflow connection uses, then grant only the dataset and job roles the task needs. Check the project that runs the job, not just the project that owns the table. |
| Wrong location | The job's location does not match the dataset's location, so BigQuery cannot read or write across regions in one query. | Set the location on the operator or job to match the datasets involved. Confirm dataset locations in BigQuery before adding cross-dataset joins to a DAG. |
| Legacy SQL syntax errors | The query uses legacy SQL syntax, or the job falls back to the implicit use_legacy_sql default, which the provider documentation marks as deprecated. | Write queries in standard SQL and set the SQL dialect explicitly in the job configuration. Fix legacy syntax such as bracketed table references before rerunning. |
| Duplicate job submission after retry | Earlier provider versions or configurations resubmitted the job on retry, so a query or load could run twice while the first run was still active. | Use durable mode in BigQueryInsertJobOperator, which saves the job ID so retries reconnect to the running job. Confirm the behavior on your Airflow and Google provider versions, and make loads idempotent where possible. |
| Triggerer not running, so deferred tasks stall | Deferrable tasks hand polling to the triggerer, which must run as its own process. If it is down or not deployed, tasks wait in a deferred state indefinitely. | Start the triggerer alongside the scheduler and API server, then check its health and logs. Confirm your deployment template includes it, since deferred tasks will not finish without it. |
| Sensors timing out on partitions that never arrive | The upstream load did not write the expected partition, the partition key or date format in the sensor does not match the table, or the sensor timeout is shorter than the upstream delay. | Confirm the partition name and format against the table, check the upstream load, and set a timeout and failure path that alerts the owning team. Use reschedule or deferrable waiting so idle sensors do not hold worker slots. |
| Provider version mismatch with Airflow 3 | The installed Google provider package is older than the Airflow 3 release line or pins a version set that does not match the Airflow core, so operators or parameters behave differently or fail at import. | Install provider versions from the constraints file for your exact Airflow release. Confirm operator parameters against the provider documentation for that version before upgrading DAG code. |
Questions teams ask before connecting Airflow to BigQuery
What drives the effort of connecting Airflow to BigQuery?
The effort follows the number of pipelines, the variety of BigQuery work they run, and how much of the platform around them is new. Simple scheduled query jobs against one project are a small build. Effort grows with dataset and table design, load patterns that need partition handling, data quality checks that gate downstream tasks, and the connection and secrets setup for each Google Cloud project. Two other factors add work: whether the Airflow platform itself is already running in your infrastructure, and whether you need deferrable waiting on the triggerer so long BigQuery jobs do not hold worker slots. Ask for the effort to be broken down by these items in any quote.
Can our internal team connect Airflow to BigQuery without a partner?
Yes, if your team already runs self-hosted Airflow 3 and has someone who knows Google Cloud IAM, service accounts and BigQuery. The Google provider documents the operators, sensors and check operators, so the core pattern is learnable. Teams often find the harder parts are the parts around the DAGs: choosing service account scopes, separating dev and production projects, deciding how jobs are named and reconnected after retries, and building a test approach for queries that cost money to run. A partner is most useful when those design decisions need to be made quickly, documented for auditors, or handed over cleanly to your engineers.
What must our team provide before the BigQuery integration starts?
Plan to provide the following before build work begins:
- Google Cloud projects for development, test and production, with billing and BigQuery access confirmed for each
- Service accounts with the narrowest roles your data owners will approve, plus a decision on how Airflow stores their credentials
- Dataset and table design, including naming, partitioning and the owners of each dataset
- Representative test data or a non-production copy of the tables the pipelines read and write
- Named contacts for data quality rules and for approving changes to production schedules
Confirm the exact project and role requirements with your Google Cloud administrators and your data governance owners, since these depend on your organization's policies.
How long does a BigQuery integration take?
No duration figure is available for this integration, so no timeline should be assumed from this page. The time depends on how many pipelines are in scope, how many datasets and projects they touch, whether the Airflow platform is already built, and how many data quality checks and sensors must be designed. Ask any partner, including DeployOpen, for a scoped estimate built from a written list of DAGs, BigQuery objects and environments. A good estimate separates the platform work from the per-pipeline work, and states the assumptions about test data and access approvals that could move the schedule.
Does a managed Airflow service change the BigQuery work?
It changes where the platform work sits, not the BigQuery design. With a managed Airflow service, the provider may handle the scheduler, workers and upgrades, but you still need to confirm which Google provider package versions are available, how connections and secrets are stored, and whether your network can reach BigQuery as required. Your DAG code, dataset design, service account roles and checks remain your responsibility. Ask the managed service provider which Airflow and provider versions it runs, and whether it lets you control the connection configuration needed for the BigQuery operators.
Is the BigQuery integration fixed scope?
Most DeployOpen engagements are fixed scope, with scope, deliverables and timelines agreed in writing before work starts. For a BigQuery integration, the written scope would normally list the pipelines, BigQuery objects, operators and checks, environments, and the handover items. Changes such as a new dataset, an additional project or a different data quality standard should go through a written change to that scope. Ongoing support, upgrades and continuous testing are available as retainers and are priced separately from the initial build.
How does durable mode affect retries on BigQuery jobs?
From Airflow 3.3, BigQueryInsertJobOperator runs in durable mode by default, saving the job ID so a retry reconnects to the running job instead of submitting it again. This prevents duplicate loads and duplicate query costs after a transient failure. Engineers should still confirm the behavior in their Airflow version, test a forced retry in a non-production project, and decide how load jobs will be made idempotent for the data they write. Deferrable mode moves polling to the triggerer, which frees worker slots during long jobs.
How DeployOpen scopes and delivers the BigQuery integration
DeployOpen plans and builds the Airflow to BigQuery integration inside your own infrastructure, starting from a written scope that names the DAGs, datasets, connections and checks involved. Engagements are fixed-scope, so deliverables and timelines are agreed before work begins.
How we scope
- Inventory the BigQuery datasets, projects and jobs each DAG will run, load or check.
- Agree which operators, sensors and check operators each pipeline step will use.
- Record the service account, connection and secrets approach in the written scope before build starts.
How we build
- Configure the Google provider connection and test job submission in a non-production Airflow environment.
- Set deferrable mode and durable job reconnect behavior, then verify retries reattach to running jobs.
- Write data quality checks and partition sensors, and test them against representative tables before promotion.
How we hand over
- Document each DAG, its BigQuery dependencies, service account permissions and failure handling for your team.
- Walk your engineers through rerun, backfill and retry procedures using your own pipeline examples.
- Deliver configuration, tests and runbooks in the form your change process and reviewers require.
How we support
- Ongoing support, upgrades and continuous testing are available as a retainer after handover.
- Confirm Airflow version upgrades and provider package changes with your team before rollout.
- Agree in the written scope which support responsibilities stay with your own platform team.
