It automates the movement of data into BigQuery on a regular, managed schedule.

The problem of getting data in

BigQuery is Google Cloud’s data warehouse, a place where huge amounts of data can be stored and analyzed with SQL. But a warehouse is only useful when it is full of data, and that data lives somewhere else. Advertising numbers sit inside advertising platforms. Customer records sit inside a sales application. Log files sit in cloud storage on another provider. Orders sit in a database. Someone has to carry all of this into BigQuery, and keep carrying it every day as new data appears.

The old way: custom pipelines

Traditionally, an engineer wrote a program for each source. The program called the source’s interface, handled logins, dealt with limits, converted formats, loaded the result and retried when something failed. Each program had to be scheduled, monitored and repaired whenever the source changed its interface. A company with twenty sources ended up with twenty fragile programs and a team busy keeping them alive.

What the service does

BigQuery Data Transfer Service, often shortened to DTS, replaces those programs with configuration. You pick a source, give it permission to read, choose a destination dataset in BigQuery and set a schedule. From then on the service loads data automatically on that schedule. It can also backfill, meaning re-load a past period to recover from outages or gaps. It is a one-way street: it loads data into BigQuery and cannot be used to move data out.

Twelve words you will meet in every DTS conversation.

Hold on to the picture: a transfer configuration is the standing order, each run is one delivery, and the dataset is the shelf where deliveries are put.

The first stop of a data journey, before cleaning and analysis.

A modern data platform has stages. First data is ingested, meaning brought into the warehouse. Then it is transformed, which means cleaned, joined and reshaped. Finally it is analyzed and shared through reports and models. DTS lives in the first stage. It lands raw data into BigQuery in a reliable, repeatable way, so that later stages can start from a trustworthy base.

flowchart LR
SRC["Sources: ads, SaaS, clouds, databases, files"] --> DTS["BigQuery Data Transfer Service"]
DTS --> RAW["Raw datasets in BigQuery"]
RAW --> TRF["Transform with SQL or Dataform"]
TRF --> MART["Clean tables and views"]
MART --> BI["Dashboards, reports and machine learning"]
        

ELT, not ETL

Older systems extracted data, transformed it on a separate machine and then loaded the result, which is called ETL. With a powerful warehouse such as BigQuery, it is often better to load first and transform inside the warehouse with SQL, called ELT. DTS follows the ELT idea. It loads data in a form close to the source, and you shape it afterwards with queries.

The few objects you create, and the work Google does behind them.

From your side, DTS has a small number of objects. You enable the service in a project, create a destination dataset, and create a transfer configuration for each source. Behind the scenes, the service reaches the source using the credentials you gave, retrieves the data, stages it, loads it into BigQuery tables and records the result. You never see or manage the machines that do this.

flowchart TD
CFG["Transfer configuration: source, schedule, identity"] --> RUN1["Run: Monday window"]
CFG --> RUN2["Run: Tuesday window"]
CFG --> RUN3["Run: Backfill of earlier dates"]
RUN1 --> DS["Destination dataset in BigQuery"]
RUN2 --> DS
RUN3 --> DS
RUN1 --> LOG["Run history and logs"]
RUN2 --> LOG
RUN3 --> LOG
RUN1 --> NOTE["Notifications by email or Pub/Sub"]
        

A growing list of connectors, grouped by what kind of system they read from.

DTS offers connectors for many systems. Some are generally available and covered by full support, while others are in preview, meaning they are still maturing and may change. The list grows over time, so always check the current documentation for the exact sources and their status.

The service does more than load files, and the other jobs are easy to overlook.

Scheduled queries in more detail

Many teams meet DTS first through scheduled queries without realizing it. You write a SQL query, choose how often it should run, and choose a destination table. Each run executes the query and either replaces or adds to the table. This is a tidy way to build a daily summary, a weekly snapshot or a cleaned copy of raw data, with no external scheduler.

Dataset copies in more detail

A dataset copy transfer duplicates tables from a source dataset to a destination dataset. It can be repeated on a schedule to keep the copy fresh. Teams use it to give another team its own copy, to move data closer to users in another region, or to keep a backup in a separate project.

What happens between the schedule firing and the data appearing in your table.

sequenceDiagram
participant S as Scheduler
participant D as DTS Service
participant X as Source System
participant B as BigQuery
participant N as Notifications
S->>D: Time reached, start a run
D->>D: Check identity and permissions
D->>X: Connect and read data for the window
X-->>D: Return data
D->>D: Stage and shape into table format
D->>B: Load into destination tables
B-->>D: Load result
D->>N: Send success or failure message
        

Choosing when data arrives, and repairing the past when it does not.

Time based schedules

Most transfers run on a repeating timetable, such as every day at a chosen hour, or every hour for sources that support it. The right frequency depends on how fresh the data must be and how often the source updates. Loading hourly something that changes once a day wastes effort and cost.

Event-driven transfers

For files, waiting for a clock is not always ideal. Some transfers can instead be triggered by an event, such as a new file arriving in a Cloud Storage bucket, using a Pub/Sub notification. This brings the data into BigQuery soon after it appears, without polling on a timer.

Backfills

Pipelines break. A source is down, a password expires, a quota is hit, and a few days of data are missing. A backfill lets you ask DTS to run the transfer again for a range of past dates. Not every source supports backfill in the same way, and some can only return a limited history, so check what your connector allows.

The most common use: files in buckets, turned into queryable tables.

Many data sources export files, such as daily log files, partner data drops or exports from other systems. DTS can pick these up from Cloud Storage, Amazon S3 and Azure Blob Storage, and load them into BigQuery. You give a location and a file pattern, and the service finds matching files, loads them and tracks which it has already handled.

flowchart LR
S3["Amazon S3 bucket"] --> DTS["DTS transfer"]
AZ["Azure Blob container"] --> DTS
GCS["Cloud Storage bucket"] --> DTS
DTS --> BQ["BigQuery table, partitioned by date"]
        

Cross-cloud loads

Moving data from another cloud involves network transfer between providers, and the source provider may charge for data leaving its network. Hourly loads from Amazon S3 are available for fresher data. Credentials for the other cloud are stored in the transfer configuration, so treat them with the same care as any secret, and use limited, read-only keys.

Turning campaign and website data into tables that analysts can join with the rest of the business.

Marketing teams live in advertising and analytics products, each with its own reports and interface. Their data becomes much more valuable when it sits next to sales, product and customer data in one warehouse. DTS connectors for Google Ads, Campaign Manager, Display and Video 360, Search Ads 360 and Google Analytics 4 deliver this data into a standard set of BigQuery tables, so it can be joined and analyzed with SQL.

Bringing tables from operational systems and other warehouses into BigQuery.

Database and warehouse connectors

For databases such as MySQL, PostgreSQL, Oracle and SQL Server, and for warehouses such as Teradata, Amazon Redshift and Snowflake, DTS can copy tables into BigQuery. This is widely used for migration, where an organization moves its analytics away from an older warehouse. It also supports ongoing batch replication, so reporting can run in BigQuery without burdening the operational database. For some warehouse sources, DTS can also help with schema conversion and moving data and history.

SaaS application connectors

For applications such as Salesforce, ServiceNow, HubSpot and Shopify, connectors read the application’s objects, such as accounts, tickets or orders, and load them as tables. Some of these connectors are offered through Google Cloud Marketplace as third party transfers. They are enrolled and configured in the console, and support comes from the provider of the connector.

Where your data is processed and stored matters for law, speed and cost.

BigQuery datasets live in a location, which can be a multi-region such as a large geographic area, or a single region. DTS follows the destination dataset. The transfer configuration is set to the same location as the destination dataset, and the service processes and stages data in that same location. The source can be anywhere, since DTS supports transfers from the source’s region to any destination location, but the destination location decides where processing happens.

flowchart TD
SRC["Source in any region or cloud"] --> DTS["DTS processes and stages in destination location"]
DEST["Destination dataset: chosen location"] --> DTS
DTS --> OUT["Tables stored in the destination location"]
        

A transfer holds the keys to both the source and the warehouse, so protect it carefully.

Whose identity does the transfer use?

A transfer can run with the authorization of a person, or, for many sources, with a service account. A transfer tied to a person breaks when that person leaves the company, changes their password or loses access. A service account is a stable identity for the transfer itself, which is the better choice for production. Service account support has been added for many sources over time, so check which your connector supports.

Permissions you need

The person creating a transfer needs permission to manage transfers in the project, and to edit the destination dataset. The identity running the transfer needs access to the source data and permission to write to the destination. For third party sources, you also need whatever permissions the external system requires. Grant roles on specific datasets rather than on the whole project where possible.

A pipeline that fails quietly is worse than one that fails loudly.

Every run records its status and logs, which you can inspect in the console. You can also configure run notifications. Pub/Sub notifications send a message to a topic when a run finishes as succeeded, failed or cancelled, which suits automation. Email notifications tell people when a run fails. If a transfer uses a service account, the email may not reach a real person, so Pub/Sub notifications are recommended in that case.

flowchart LR
RUN["Transfer run finishes"] --> ST{"Status"}
ST -->|"Succeeded"| OK["Pub/Sub message: downstream jobs can start"]
ST -->|"Failed"| FAIL["Pub/Sub and email: alert the team"]
ST -->|"Cancelled"| CAN["Pub/Sub message: investigate"]
OK --> NEXT["Start transformation workflow"]
FAIL --> TKT["Create ticket or page on-call"]
        

What Google promises, and what depends on other people’s systems.

Google describes a data delivery objective for scheduled transfers from sources within Google Cloud. For transfers from third party or non-Google sources, the objective does not apply, because outages and limits at those sources can affect the transfer. In practice, treat transfers from outside sources as dependent on those sources, and design reports to tolerate delay.

Slots and processing

BigQuery does its work using units of compute capacity called slots. Jobs started by DTS use the slots of a reservation only if the project, folder or organization is assigned to a reservation for the relevant job types. Otherwise they use the default on-demand arrangements. Teams with reservations should check how transfer jobs are assigned, so that loading data does not compete with business queries, or the other way around.

The transfer fee is only one part of what loading data costs.

DTS pricing has been evolving. Google announced a consumption-based model for third party SaaS and database connectors that takes effect when each connector becomes generally available, with connectors free while in preview. More recently, the orchestration fee has been removed for many Google sources, object storage and migration connectors. Because terms change, use the official pricing page for the current picture of which transfers are free and which are charged.

Whatever the transfer fee is, other costs remain. Once data has landed, normal BigQuery storage and query pricing applies. Cross-cloud transfers can involve network charges from the source provider. Pub/Sub notifications incur Pub/Sub charges. Source systems may also bill for exports or API usage.

Where scheduled loading quietly powers decisions.

These are common patterns rather than named customer stories, because specific designs and contracts change over time.

Convenience and reliability, in return for less flexibility than custom code.

Habits that keep data arriving, and habits that let it silently disappear.

Patterns that work

Strong teams separate raw and cleaned datasets. They run production transfers with service accounts, and they send run notifications to a topic that triggers downstream work and alerts. They partition tables by date and set expiry on raw data. They name transfers clearly, record the owner of each, and keep the configuration under infrastructure as code, so setups can be reviewed and rebuilt. They test backfills before they need them, and they monitor freshness.

A checklist to keep your data deliveries dependable, secure and affordable.

Mistakes beginners make most often

The first is using a personal authorization for production transfers. The second is ignoring notifications. The third is forgetting dataset location rules. The fourth is treating preview connectors as fully supported. The fifth is starting downstream jobs by the clock instead of by completion. The sixth is backfilling without understanding write behavior. The seventh is leaving raw data unpartitioned and unexpired.

Google Cloud has several ways to get data into BigQuery, each with its own sweet spot.

flowchart TD
Q1{"Is a supported connector available for your source?"} -->|Yes| Q2{"Is scheduled batch loading enough?"}
Q2 -->|Yes| DTS["Use BigQuery Data Transfer Service"]
Q2 -->|"No, need changes in seconds"| DS["Consider Datastream or streaming tools"]
Q1 -->|No| Q3{"Need custom logic or streaming events?"}
Q3 -->|Yes| DF["Consider Dataflow or Pub/Sub"]
Q3 -->|No| LJ["Consider load jobs or a partner tool"]
        

These tools are often combined. A company may use DTS for marketing and file loads, Datastream for key databases, and Dataflow for event streams, all landing in the same BigQuery warehouse.

It is a managed Google Cloud service that automatically loads data into BigQuery on a schedule from supported sources such as advertising platforms, SaaS applications, databases and cloud storage, with no pipeline code to write.

No. DTS loads data into BigQuery. To export data, use other tools such as export jobs or other services.

No. It is built for scheduled batch loads, with some sources supporting hourly runs and some file sources supporting event-driven triggers. For changes within seconds, use change data capture or streaming tools.

A request to run a transfer again for past dates, so gaps caused by outages or errors can be filled. How far back you can go depends on the source.

For production, a service account where the connector supports it, so the transfer does not depend on a person’s account. Check each connector’s documentation.

Configure run notifications. Pub/Sub notifications report succeeded, failed and cancelled runs and suit automation, while email notifications report failures to people.

In the location of the destination dataset. The transfer configuration and staging happen there, while the source can be in any region or cloud.

It also runs scheduled queries and copies datasets between locations or projects, on a schedule.

It depends on the connector and its status. Preview connectors have been free, many Google sources have had fees removed, and some third party connectors use consumption-based pricing once generally available. Storage and queries in BigQuery are billed separately. Check the official pricing page.

Only lightly, through connector options. For real transformation, load the data first and use SQL, scheduled queries or Dataform inside BigQuery.

Look for a partner connector, export files to cloud storage and load them with DTS, or build a pipeline with Dataflow or custom load jobs.

Understand whether the connector replaces date partitions or appends rows. Prefer replace-style writes, load into partitioned tables, and verify counts after a backfill.

BigQuery Data Transfer Service is Google Cloud’s managed way to bring data into BigQuery on a schedule. You create a transfer configuration for a source, a destination dataset and a timetable, and the service runs, retries, logs and notifies, while also offering backfills, scheduled queries and dataset copies. It covers advertising and analytics products, storage in several clouds, databases, warehouses and SaaS applications. Success comes from stable identities, correct dataset locations, sensible schedules, notifications tied to downstream work, partitioned tables and attention to changing connector status and pricing.