Start with the big picture, in plain words.

The simple definition

A data warehouse is a system built to store large amounts of data from many places and to answer questions about it quickly. BigQuery is Google Cloud’s data warehouse. It stores data in tables, and you ask questions using a language called SQL.

The BigQuery Data Transfer Service, often shortened to BigQuery DTS or just DTS, is a fully managed Google Cloud service that automates the movement of data into BigQuery on a schedule. Managed means Google runs and repairs the machinery. You do not rent servers, install software, or write the code that copies the data. You choose a source, a destination, and a schedule, and the service does the rest.

What it moves

The service can bring data from several families of sources. Google’s own marketing and advertising products, such as Google Ads and YouTube reports, are supported. Files that sit in cloud storage, including Google Cloud Storage, Amazon S3, and Azure Blob Storage, are supported. Other data warehouses, such as Teradata and Amazon Redshift, can be migrated. A growing list of third-party applications is also available through connectors. Google updates this list over time, so always confirm the current sources in the official documentation.

What else it does

Two other everyday BigQuery features run on the same machinery. Scheduled queries let you run a SQL statement on a timetable and save the result into a table. Dataset copies let you copy a whole dataset to another location. We will look at both in later chapters.

Why it exists

Without such a service, every team writes its own copy scripts. Those scripts break when a password changes, when a file arrives late, when an advertising company updates its report format, or when the one engineer who understood them leaves. A managed service puts the common work in one well-tested place, so teams can spend their effort on analysis, not plumbing.

To value a delivery service, first see the mess without one.

Scattered data

A modern company keeps its information in many separate places. Advertising numbers live inside ad platforms. Sales files sit in cloud storage buckets. An older reporting system lives in a data center. A partner sends a daily file. Each place has its own format, its own login, and its own schedule. Questions that matter, such as “Which advertisement brought the most paying customers?”, need data from several of these places at once.

Pipelines, ETL, and ELT

A data pipeline is an automated path that moves data from where it is created to where it is analyzed. ETL stands for Extract, Transform, Load. You extract data from a source, transform it into a clean shape, and load it into a warehouse. ELT flips the last two steps: you load the raw data first and transform it inside the warehouse afterward, which works nicely with BigQuery because it is powerful at transforming large tables. The Data Transfer Service handles the extract and load parts, and your SQL, scheduled queries, or other tools handle the transform part.

Batch versus streaming

Batch processing handles data in groups at set times, like a bus that leaves the station every hour. Streaming handles each piece the moment it appears, like a river. Batch is simpler and cheaper, and is perfectly good for reports that update daily. Streaming is needed when seconds matter, such as fraud detection.

What goes wrong with hand-made scripts

The vocabulary of the service, explained one word at a time.

Project and dataset

A project is the basic container of Google Cloud resources, carrying billing, permissions, and the list of switched-on services. Inside a project, BigQuery organizes tables into folders called datasets. A transfer always loads into one destination dataset. The dataset has a location, which is the geographic place where its data physically lives, and this location matters for transfers.

Data source

A data source is the kind of thing you are transferring from, such as Cloud Storage, Amazon S3, Google Ads, or Teradata. Each data source has its own settings, such as which account, which files, or which report types.

Transfer configuration

A transfer configuration is the saved set of instructions: the data source, the destination dataset, the schedule, the source settings, and the notification choices. It is like a standing order at a supplier. You create it once, and it triggers deliveries again and again.

Transfer run

A transfer run is one execution of a transfer configuration. If a configuration is set to run daily, you will see one run per day. Each run has a state, a start time, an end time, a log of messages, and a summary of what it loaded.

Schedule

The schedule says how often a configuration runs, such as every day at a given time, or every few hours. Different data sources support different schedule options. Some sources run on a fixed schedule defined by the service, so you cannot choose arbitrary times.

Backfill

A backfill is a request to load data for past dates. Suppose you set up a transfer today but want the last ninety days too. A backfill asks the service to run the transfer for those earlier dates. Not every data source supports backfills, and some limit how far back you can go.

Credentials

The service must prove its right to read the source and write to the destination. It can use user credentials, meaning the permissions of the person who created the transfer, or a service account, which is an identity for software rather than a person. Service accounts are strongly preferred for production, because they do not depend on one employee’s login or job.

Notifications

The service can send email notices about run failures and can publish run status messages to a Pub/Sub topic. Pub/Sub is Google’s messaging service. Publishing status to a topic lets your own automation react to a success or a failure.

Follow a delivery from the source to a BigQuery table.

The service sits between your sources and BigQuery. A scheduler decides when a run should start. A worker connects to the source using the credentials you provided, reads the data, and hands it to BigQuery, which loads it into the destination table. Status and logs are recorded for each step.

flowchart LR
  A["Transfer configuration with schedule"] --> B["Transfer scheduler"]
  B --> C["Transfer run created"]
  C --> D["Worker connects to the source"]
  S1["Cloud Storage"] --> D
  S2["Amazon S3 or Azure Blob"] --> D
  S3["Google Ads and other apps"] --> D
  S4["Teradata or Redshift"] --> D
  D --> E["Data staged and validated"]
  E --> F["BigQuery load into destination dataset"]
  F --> G["Run status and logs recorded"]
  G --> H["Email or Pub/Sub notification"]
    

Reading the diagram

Transfer configuration with schedule. This is your standing order. It holds the source, the destination, the settings, and the timetable.

Transfer scheduler. A scheduling component inside Google’s service watches the clock. When the time comes for a configuration, it creates a run.

Transfer run created. A new run record is made, with a pending state and a number for the date or time window it covers.

Worker connects to the source. Google’s managed workers use the stored credentials to open a connection. You do not manage these workers, and they scale as needed.

Data staged and validated. The data is read and checked. For files, the service finds the matching files and reads their formats. For ad platforms, it requests the day’s reports through the platform’s interface.

BigQuery load. The data is loaded into the destination table. For files, this uses the same kind of load job you could run yourself. A load job is a BigQuery operation that ingests a batch of data into a table, and it is efficient and, in many cases, free of the per-row cost associated with streaming inserts.

Run status and logs recorded. The final state, row counts, and any messages are saved so you can review them later.

Notification. If you set one, an email goes out on failure, or a message is published to a Pub/Sub topic.

A tour of the doors through which data can enter.

The list of supported sources grows over time, and some are in earlier release stages, called preview, where features may change. Treat this chapter as a map of the main families, and confirm the exact current list and release status on Google’s pages before you plan.

Google marketing and advertising platforms

Google offers connectors for several of its own products, such as Google Ads, Campaign Manager, Display and Video 360, Search Ads 360, Google Ad Manager, Google Play, and YouTube channel and content owner reports. These bring ready-made report tables into BigQuery. The result is a set of tables with a stable shape that analysts can combine with sales data.

Cloud storage files

The service can load files from Google Cloud Storage, Amazon S3, and Azure Blob Storage. This is the most flexible family, because many systems can export files. You describe which files to pick up, in which format, and how to treat repeats. Common formats include CSV, JSON, Avro, Parquet, and ORC, which are standard ways to lay out table data in files.

Data warehouse migrations

Teams moving from older warehouses can use connectors for systems such as Teradata and Amazon Redshift. These are intended for migration, meaning a one-time or short-term move of existing data, together with help from companion tools for translating old SQL into BigQuery’s dialect.

Third-party applications and databases

Through partners and newer connectors, the service can bring in data from business applications such as customer relationship and advertising tools, and some operational databases. Availability depends on the connector and its release stage, so check carefully.

Internal features built on the same service

Scheduled queries and dataset copy jobs run on the same engine and show up in the same list of transfers.

flowchart TD
  A["Where is my data?"] --> B{"Google marketing product?"}
  B -- "Yes" --> C["Use the matching Google connector"]
  B -- "No" --> D{"Can the system export files?"}
  D -- "Yes" --> E["Land files in Cloud Storage, S3, or Azure Blob"]
  E --> F["Use a file transfer"]
  D -- "No" --> G{"Is it a supported application or warehouse?"}
  G -- "Yes" --> H["Use that connector"]
  G -- "No" --> I["Consider other tools such as Datastream or Dataflow"]
    

Lift the lid and watch the gears turn.

Scheduling and the run window

The scheduler holds your timetable. For a daily transfer, each run covers a run time and often a run date, which tells the connector which slice of data to fetch. For example, a run at two in the morning may be asked to fetch yesterday’s report. This is why the same configuration can later be re-run for an older date: the date is a parameter of the run.

Authorization at run time

When a run starts, the service uses the stored credentials. If you used a service account, the service impersonates it, which means it obtains a short-lived token as that account. If you used user credentials, the service relies on a stored authorization from that person. If that authorization has expired or the person left the organization, the run fails with an authorization error. This is one of the most common reasons for failed runs.

Reading from the source

For file sources, the worker lists files in the bucket that match your path pattern and that have not been loaded before, using file modification times to decide. It then groups them and starts a BigQuery load job. For advertising sources, the worker calls the platform’s interface for the reports of the requested date, converts them to rows, and loads them. For warehouse migrations, an agent running near the old system extracts data and writes it into cloud storage first, and then a load into BigQuery follows.

Load jobs

The heart of many transfers is a BigQuery load job. BigQuery reads the staged data and writes it into the table in a single atomic step. Atomic means that it either fully succeeds or fully fails, with no half-finished table. This is a kind of safety property. If a run fails during the load, the table is left in its previous state.

Idempotence and duplicates

A step is idempotent when running it twice gives the same final result as running it once. Many connectors are designed to be idempotent for a given run date, for example by replacing the data for that date rather than adding it again. For file transfers, you choose the behavior using a write preference: append new rows to the table, or mirror the source by replacing the table contents. Understanding which one you chose is essential to avoid duplicate data.

Retries

If a run hits a temporary problem, such as a brief network error, the service may retry. Permanent problems, such as a missing permission or a missing dataset, fail the run immediately with a clear message in the log.

State tracking

The service remembers which files it has already loaded and which dates it has already run, so that normal schedules do not repeat the same work. This memory belongs to each configuration. If you delete a configuration and create a new one, the new one has no memory and may load old files again.

sequenceDiagram
  participant Sch as "Scheduler"
  participant Run as "Transfer run"
  participant Auth as "Credentials"
  participant Src as "Source system"
  participant BQ as "BigQuery"
  Sch->>Run: "Time reached, create run for date"
  Run->>Auth: "Obtain token for service account or user"
  Auth-->>Run: "Token"
  Run->>Src: "Request files or reports for the run date"
  Src-->>Run: "Data"
  Run->>BQ: "Start load job into destination table"
  BQ-->>Run: "Load succeeded or failed"
  Run->>Run: "Record state, row counts, and logs"
  Run-->>Sch: "Run complete"
    

A calm, ordered path that avoids the usual surprises.

Step one: prepare the project

Choose the project that will hold the destination dataset and enable the BigQuery Data Transfer API and the BigQuery API. Without the Data Transfer API turned on, creating a transfer fails with a message that says so.

Step two: create the destination dataset

Create a dataset to receive the data. Choose its location carefully. For many sources, the dataset location must match or be compatible with the source location, and the location cannot be changed later without recreating the dataset. For example, a dataset in one region cannot simply receive a bucket’s data from a different location in some transfer types.

Step three: decide on the identity

Create a dedicated service account for transfers, and grant it exactly what it needs: permission to read the source, permission to write to the dataset, and permission to run load jobs. The person creating the transfer needs permission to act as that service account. Keeping a dedicated account separates transfer duties from any human’s login.

Step four: prepare the source

For file sources, make sure the files are in a stable, predictable path, with consistent names and formats. For application sources, authorize the account that holds the data. For cloud-to-cloud transfers, create the access credentials in the other cloud with read-only permission.

Step five: create the transfer configuration

In the Console, open BigQuery, choose Data transfers, and create a transfer. Pick the data source, give the configuration a clear name, set the schedule, choose the destination dataset, and fill in the source-specific settings. You can do the same with the command-line tool or the API, which is better for repeatable setups.

Step six: run it once and inspect

Trigger a first run or a backfill for one day. Open the run details. Check the state, the log messages, and the row counts. Look at the table in BigQuery and compare a few numbers with the source. Only after you are satisfied should you rely on the schedule.

Step seven: add notifications

Turn on email notifications for failures, and where useful, a Pub/Sub topic that your automation can read. A transfer that fails quietly is worse than one that never existed.

The most flexible family deserves its own chapter.

The big idea

File transfers watch a location, find matching files, and load them into a BigQuery table. Because nearly every system can export files, this family is the Swiss army knife of the service. Whatever your source, if you can get it to write files, this route works.

Choosing files with a path pattern

You describe the files with a URI pattern, which is a path that can include wildcards. A wildcard is a special character that stands for “anything here”. For example, one pattern can mean “all files in the sales folder whose names begin with orders”. Using a clear folder structure with dates makes patterns simple and keeps runs fast.

File formats

Self-describing binary formats such as Avro and Parquet carry their own schema, which reduces mistakes about column types. CSV is common but fragile, because a stray comma or a different date style can confuse a load.

Write preference: append or mirror

With append, each run adds newly found files’ rows to the table, leaving earlier rows alone. This suits event-style data that grows over time. With mirror, each run replaces the table contents with the data found in the matching files, so the table reflects the current set of files. This suits reference data, such as a product list that is re-exported in full each day.

Schema handling

The schema is the list of columns and their types. You can let BigQuery detect it, which is quick but can guess wrong, or you can specify it, which is safer for production. If the incoming file gains an extra column, the load may fail, unless you allow schema updates. Decide this deliberately.

Handling bad rows

You can set how many bad rows a load tolerates before it fails. A strict setting catches problems early. A tolerant setting keeps data flowing but may hide quality problems, so pair it with checks.

Runtime parameters and date-based tables

The destination table name and the path can include runtime parameters, which are placeholders filled in at run time, such as the run date. This lets one configuration load a different daily file into a different daily table, or into daily partitions of one table.

Partitioned tables

A partitioned table splits a large table into slices, usually by date, so that queries read only the slices they need. Loading into partitions keeps costs lower and queries faster.

Cleaning up source files

There is an option to delete files from the source after a successful load. This is tidy but dangerous. If a mistake happens in the pipeline, the original files are gone. A safer pattern is to move processed files to an archive folder with a rule that deletes them after a long time.

Cross-cloud transfers

For Amazon S3 and Azure Blob Storage, the service needs credentials in those clouds. Create read-only credentials limited to the specific bucket or container. Remember that moving data between clouds may incur data egress charges from the source cloud. Check those prices before you schedule large daily transfers.

flowchart TD
  A["Source system exports file"] --> B["File lands in bucket folder by date"]
  B --> C["Transfer run starts on schedule"]
  C --> D["Find files matching the path pattern"]
  D --> E{"Write preference"}
  E -- "Append" --> F["Add rows from new files to table or partition"]
  E -- "Mirror" --> G["Replace table contents with matching files"]
  F --> H["Run succeeds and state is remembered"]
  G --> H
  H --> I["Optionally archive processed files"]
    

Two everyday features that run on the same engine.

Scheduled queries

A query is a question you ask BigQuery in SQL. A scheduled query is a question that BigQuery asks itself on a timetable, saving the answer into a table. For example, every night at one o’clock, it can total the previous day’s sales by region and write the result into a summary table. Dashboards then read the small summary table instead of scanning the huge raw table each time, which is faster and cheaper.

Scheduled queries appear in the list of data transfers because the same scheduling and run-tracking machinery powers them. Each execution is a run with a state, a log, and an optional notification. You can pass the run date into the query so that it knows which day to process, and you can choose to append to or overwrite the destination table.

Chaining transfers and queries

A typical flow is two steps: a transfer brings in raw data at one in the morning, and a scheduled query transforms it at two. The weakness of this design is that the second step runs by the clock, not by the success of the first. If the transfer is late, the query may run on missing data. Teams fix this by leaving a safety gap, by using notifications to trigger the next step, or by using a workflow tool that links steps by dependency. We will return to this in the patterns chapter.

Dataset copy

A dataset copy transfer copies all tables in one dataset to another dataset, possibly in a different project or a different region. It is useful for disaster recovery, for sharing data with another team’s project, for moving data to another location, and for keeping a read-only copy for analysts. It can run once or on a schedule, and it can be incremental, which means it copies only what has changed since the last run.

flowchart LR
  A["Raw files in bucket"] --> B["Transfer at 1 AM loads raw table"]
  B --> C["Scheduled query at 2 AM builds clean table"]
  C --> D["Scheduled query at 3 AM builds summary table"]
  D --> E["Dashboard reads summary"]
  C --> F["Dataset copy to reporting project"]
    

Understand when runs happen, what states they pass through, and how to catch up.

Run states

Every run moves through a small set of states. Pending means it is waiting to start. Running means work is in progress. Succeeded means the load completed. Failed means an error stopped it, and the log says why. Cancelled means someone or something stopped it on purpose. Some runs may also show a state that means it was skipped because nothing new was found.

stateDiagram-v2
  [*] --> Pending: "Run created"
  Pending --> Running: "Worker starts"
  Running --> Succeeded: "Load completes"
  Running --> Failed: "Error occurs"
  Pending --> Cancelled: "Stopped by user"
  Running --> Cancelled: "Stopped by user"
  Failed --> Pending: "Manual re-run or backfill"
  Succeeded --> [*]
  Cancelled --> [*]
    

Choosing a schedule

Schedules can be daily, weekly, monthly, or at custom intervals, depending on the source. Some sources only provide data once per day, so running them hourly achieves nothing. Match the schedule to how often the source really changes, and to how fresh the business needs the data. Fresher is not always better, because every run uses resources and may cost money.

Time zones

Schedules are expressed in a time standard, usually Coordinated Universal Time, shortened to UTC. A transfer set for two in the morning UTC may run in the evening for a team on another continent. Always write the time zone in the configuration name or description to avoid confusion, and remember that the run date inside the data may follow the source’s time zone, not yours.

Manual runs

You can start a run on demand for testing or for a one-off catch-up. A manual run does not replace the schedule.

Backfills

When you need earlier dates, you request a backfill for a range of days. The service creates one run per day and works through them. Because backfills can create many load jobs and consume quotas, avoid launching enormous backfills during business hours, and prefer small batches. Not every connector supports backfills, and some limit the maximum span, so check before promising a history to your analysts.

Re-running a failed day

When one day fails, fix the cause first, whether that is a permission, a missing file, or a changed column, and then re-run only that day. For connectors that replace data per date, a re-run is safe. For append-style file transfers, check whether the failed attempt loaded anything before running again. Since loads are atomic, a failed load normally leaves no partial data, but confirm with a row count.

Pausing and disabling

You can disable a configuration to pause its schedule without deleting it. This keeps its memory of already-loaded files, which is why disabling is safer than deleting when you only want a break.

Follow the story from first idea to retirement.

The three layers of data

Many teams organize loaded data into layers. The raw layer holds data exactly as transferred, untouched, so you can always return to the original. The clean layer holds data with fixed types, removed duplicates, and standard names. The reporting layer holds summaries ready for dashboards. Transfers fill the raw layer, and scheduled queries or other tools build the others. Keeping raw data safe means a mistake in cleaning can always be repaired by re-running from the raw layer.

flowchart LR
  A["Sources"] --> B["Data Transfer Service"]
  B --> C["Raw dataset - untouched copies"]
  C --> D["Clean dataset - typed and de-duplicated"]
  D --> E["Reporting dataset - summaries"]
  E --> F["Dashboards and analysts"]
  C --> G["Quality checks and alerts"]
    

An honest look at when this service is the right tool, and when it is not.

Alternatives and companions

The pairing most beginners meet is a Data Transfer Service load followed by scheduled queries. As needs grow, teams add an orchestrator to control dependencies and a transformation tool to manage SQL. The service remains the dependable first mile that brings data to the warehouse door.

Moving data is also moving risk, so build the road with guardrails.

Roles and permissions

IAM, Identity and Access Management, is the rulebook of who may do what. Creating and managing transfers needs specific permissions, which are included in certain BigQuery roles, and the transfer must be able to write to the destination dataset. A good design separates three jobs: the people who manage transfers, the service account that runs them, and the analysts who read the results. Each gets only what its job needs.

Service accounts over personal credentials

User credentials tie a transfer to one person’s account. If that account is disabled, loses permission, or has its authorization expire, the transfer fails. A service account is independent of people and is easier to audit. Where a connector only supports user authorization, use a dedicated, managed account and document who owns it.

Protecting source credentials

For cloud-to-cloud transfers, the credentials for the other cloud must be stored in the transfer configuration. Use read-only, narrowly scoped keys, restrict them to the specific bucket or container, and rotate them on a schedule. Never reuse an administrator key.

Encryption

Data is encrypted in transit and at rest by default. If your organization requires control over the encryption key, you can apply a customer-managed encryption key, created in Cloud KMS, to the destination dataset. Check the documentation for how customer-managed keys interact with each connector.

Data location and residency

The dataset location decides where the loaded data physically lives. If a law or contract limits where data may be stored, choose the dataset location first and make sure every transfer respects it. Organization policies can block creating datasets in disallowed locations.

Network boundaries

VPC Service Controls create a protective perimeter around Google Cloud services. Support for the Data Transfer Service within perimeters has specific requirements, so test early if your organization uses them, and read the current documentation.

Sensitive data

Advertising and sales files may include personal information such as names or email addresses. Decide in advance which columns should be loaded, masked, or dropped. BigQuery offers column-level access controls and data masking that can protect sensitive fields after loading. Landing raw data in a restricted dataset and exposing only a cleaned version to analysts is a common approach.

Audit logging

Changes to transfer configurations and the runs themselves are visible in Cloud Logging and audit logs. Review who created or modified transfers and who changed permissions on datasets. This record answers the question “who changed this transfer last week?” without guesswork.

When data goes missing, ask the right questions in the right order.

What to watch

There are two different things to watch. The first is the health of the transfer: did the run succeed, how long did it take, and how many rows did it load? The second is the freshness and quality of the data: is the newest row recent, do the totals match the source, and are there suddenly many empty values? A transfer can succeed technically and still deliver wrong or stale data, so watch both.

Where to look

• The Data transfers page in the Console: shows each configuration, its recent runs, their states, and error messages.
• Run details and logs: shows the messages written during each run.
• Cloud Logging: holds log entries for transfer runs, where you can search and create log-based alerts.
• Pub/Sub notifications: let automation react to each run’s final state.
• Email notifications: a simple first alarm for failures.
• BigQuery information schema views: let you query metadata, including load jobs, to see what ran and how much it processed.

Freshness checks

A simple and powerful habit is a small scheduled query that looks at the newest date in each loaded table and compares it with today. If the gap is larger than expected, it writes a row to a monitoring table or triggers an alert. This catches the case where the transfer silently stops creating runs, which a failure email would never report.

A troubleshooting checklist

Practical limits and how to live comfortably within them.

What affects speed

The time a run takes depends on how much data there is, how many files, the format, and the source’s own speed. Many small files are slower than a few large ones, because each file adds overhead. Binary formats such as Avro and Parquet load faster than text formats such as CSV, because BigQuery reads them more efficiently.

Make files friendly

When you control the export, aim for fewer, larger, compressed files rather than thousands of tiny ones. Use consistent names and date-based folders. This reduces runs that take too long and avoids hitting limits on the number of files per load.

Quotas and limits

Google sets quotas, which are limits on how many load jobs you can run per table per day, how large a load can be, and how many files a single load may include. Transfer features may also have their own limits, such as the minimum interval between runs for certain sources and the maximum number of transfer configurations. These numbers can change, so read the current quota and limits pages, especially before large backfills.

How cost works

Pricing differs by connector and can change, so use Google’s pricing page as the authority. In general, think about four cost buckets. First, the transfer itself: some connectors, especially those for Google marketing products, have no separate transfer charge, while some others, including certain third-party and migration connectors, may charge. Second, BigQuery storage for the data you load. Third, BigQuery queries, including scheduled ones, which are billed by data processed or by reserved capacity. Fourth, data egress from other clouds, which you pay to the source cloud when moving data out of it.

Practical ways to save

• Match the schedule to need. Daily is often enough. Hourly doubles complexity for little gain if nobody looks hourly.
• Partition and cluster tables. Queries read only the slices they need.
• Set table expiration for raw staging data that you only need for a few weeks.
• Avoid transforming the same data many times. Build shared clean tables instead of many private copies.
• Choose compact, compressed formats to shrink both storage and egress.
• Review old transfers. Disable those whose reports nobody uses anymore.

flowchart TD
  A["Transfer cost review"] --> B{"Is anyone using this data?"}
  B -- "No" --> C["Disable transfer and set expiry on tables"]
  B -- "Yes" --> D{"Is the schedule more frequent than needed?"}
  D -- "Yes" --> E["Reduce frequency"]
  D -- "No" --> F{"Are queries scanning full history?"}
  F -- "Yes" --> G["Partition tables and filter by date"]
  F -- "No" --> H{"Moving data across clouds?"}
  H -- "Yes" --> I["Compress files and estimate egress charges"]
  H -- "No" --> J["Cost is healthy - review again next quarter"]
    

Habits that help, and habits that hurt.

Helpful patterns

Anti-patterns to avoid

A short, practical rulebook you can pin to the wall.

Best practices

• Choose the dataset location first. It cannot be changed later and affects every connector.
• Use service accounts for production. Personal credentials are fine for experiments only.
• Test one day before backfilling a year. Compare numbers with the source before trusting them.
• Land raw data unchanged. Transform afterward in layers.
• Choose the write preference on purpose. Append for growing events, mirror for full snapshots.
• Partition by date. It makes re-runs, queries, and expiry clean and cheap.
• Prefer self-describing formats such as Avro or Parquet when you control the export.
• Turn on notifications and a freshness monitor. Silent failure is the real enemy.
• Archive files rather than deleting them. Keep a replay path for a reasonable period.
• Document ownership. Each transfer should name a team, a purpose, and a contact.
• Keep configurations in code. Review changes like any other change.
• Review quarterly. Retire unused transfers and tighten permissions.

Common mistakes beginners make

How different teams put the delivery robot to work.

flowchart TD
  A["Ad platforms"] --> T["Data Transfer Service"]
  B["Store files in buckets"] --> T
  C["Other cloud storage"] --> T
  D["Old warehouse"] --> T
  T --> R["Raw datasets"]
  R --> Q["Scheduled queries build clean tables"]
  Q --> P["Reporting dataset"]
  P --> X["Dashboards and analysts"]
  R --> M["Freshness and quality checks"]
  M --> N["Alerts to the data team"]
    

Notice what these stories share. The service is not the whole data platform. It is the dependable first mile that brings data to the warehouse door on time. The value comes from the habits around it: raw landing, clear layers, monitoring, and tidy ownership.

Quick answers to the questions beginners ask most.

It is a managed Google Cloud service that automatically loads data into BigQuery on a schedule from sources such as advertising platforms, cloud storage files, other clouds, and other data warehouses.

Not for the common cases. You choose a source, a destination dataset, and a schedule in the Console, command line, or API. You may still write SQL to transform the data after loading.

A configuration is the saved set of instructions. A run is one execution of it. A daily configuration produces one run per day.

No. It is designed for batch loading on a schedule. For continuous streams, look at tools such as the BigQuery Storage Write API, Pub/Sub, Datastream, or Dataflow.

It is a request to run a transfer for past dates, so that you can load history after setting up a new configuration. Support and limits depend on the connector.

Many sources require that the destination dataset be in a compatible location, and a dataset’s location cannot be changed after creation. Choose the location before creating the transfer.

Append adds rows from new files to the existing table. Mirror replaces the table contents with the rows from the currently matching files. Use append for growing event data and mirror for full snapshots.

Common reasons are expired or revoked authorization, a changed file format or column, a deleted dataset, lost permissions, or a moved source location. The run’s error message normally names the cause.

Use a dedicated service account for production wherever the connector allows it. Personal credentials depend on one person and can expire or disappear.

Choose the right write preference, load into date partitions, avoid deleting and recreating configurations, and check run history before re-running an append-style transfer.

Scheduled queries use the same scheduling and run-tracking machinery, so they appear in the same list. They run SQL on a timetable and save results into tables.

It depends on the connector and can change over time. Some Google connectors have no separate transfer fee, while others may charge. You also pay for BigQuery storage, queries, and any cross-cloud egress. Check Google’s pricing page for current terms.

Turn on email notifications and a Pub/Sub topic, and add a freshness monitor that checks the newest date in the destination table. This catches both failed runs and runs that never started.

Mostly no. The service focuses on extracting and loading. Land the raw data first, and transform it afterward with SQL, scheduled queries, or a transformation tool.

Test one day and compare it with the source, confirm permissions and location, set notifications, set up a freshness check, document ownership, and write down the plan for re-running and backfills.

Everything important, in one place.