Prepare and test your skills
Prepare and test your skills
BigQuery Data Transfer Service (opens in a new tab) is a managed service that automates scheduled data ingestion into BigQuery (opens in a new tab) without writing code. Data flows from the source system to a destination BigQuery dataset after a transfer configuration is created. The service only moves data into BigQuery and cannot transfer data out of BigQuery.
Supported sources (opens in a new tab) for BigQuery Data Transfer Service are external systems and Google services that load directly into a BigQuery dataset. The transfer configuration connects the source to the destination dataset, and the service handles authentication, scheduling, and loading. Third-party and external-cloud sources are a core use case for automated ingestion.
For Amazon Redshift and Teradata, the service can load schema and data directly from the existing warehouse into BigQuery, while Cloud Storage is still used to stage data during the migration. For other warehouses, files are extracted, uploaded to a Cloud Storage bucket, and then loaded into BigQuery by the transfer.
Scheduled ingestion means the service runs transfer jobs automatically after the configuration is saved. Source files for Cloud Storage transfers are picked up immediately with no minimum file age, the minimum interval between recurring transfers is 15 minutes, and the default interval is every 24 hours. An event-driven transfer that uses a Pub/Sub subscription can trigger runs at lower intervals when new events arrive.
The transfer configuration is always set to the same location as the destination BigQuery dataset, and the service processes and stages data in that same location. A transfer can move data from any source region to any destination dataset location. The Data Delivery SLO applies only to automatically scheduled transfers from sources inside Google Cloud and does not apply to transfers from third-party or non-Google Cloud sources, because outages in those external sources can affect delivery.
Configuring a transfer creates a reusable connection from a source to a destination dataset and table in BigQuery. The transfer can be created and managed with the Google Cloud console, the bq command-line tool, or the BigQuery Data Transfer Service API. The same service is also used for dataset copies and scheduled queries in BigQuery.
Before creating a Cloud Storage transfer, the destination must already exist in BigQuery. The practitioner enables the BigQuery Data Transfer Service and retrieves the Cloud Storage URI, creates the destination dataset, creates the destination table with its schema in advance including for CSV and JSON files, and confirms encryption and key access when using a customer-managed encryption key (CMEK), including the Cloud KMS key resource ID and service account permission to encrypt and decrypt.
A console transfer is created in an ordered workflow where each choice controls the next run:
Managing a transfer covers manual runs, backfills, access control, and recurring-load limits for BigQuery datasets. In addition to the automatic schedule, an operator can run a one-time transfer or schedule a backfill to load a specific date or date range and recover from outages or gaps. Permissions must allow both BigQuery job creation and access to the source bucket that holds the data.
To create a transfer, the identity needs BigQuery Admin (roles/bigquery.admin) or equivalent permissions such as bigquery.transfers.update, bigquery.transfers.get, bigquery.datasets.get, bigquery.datasets.update, bigquery.datasets.setIamPolicy, and bigquery.jobs.create. For Cloud Storage sources, the identity also needs storage.objects.get on the bucket, plus storage.objects.list when using wildcards and storage.objects.delete when source files should be deleted after a successful transfer. Recurring transfers enforce stricter rules than one-time loads because the destination table already exists. All files selected by a wildcard or runtime parameters must share the same schema as the destination table, and schema changes between runs cause the transfer to fail. Only live Cloud Storage objects can be transferred, archived versioned objects are not supported, and including a generation number in the URI causes the load to fail. Write preference defaults to APPEND, so an unmodified file loads only once and reloads only after its last modification time changes.
Exam tip: A recurring Cloud Storage transfer requires the destination table and schema to exist before setup, while a one-time load can create the table during the load.
Storage Transfer Service (opens in a new tab) is a fully managed Google Cloud service that moves large amounts of object and file data into Cloud Storage (opens in a new tab) without custom scripts or managed infrastructure. Data flows from a configured source to a destination Cloud Storage bucket, and the service handles scale, speed, and reliability automatically. The service is optimized for transfers larger than 1 TiB and can move petabytes of data quickly.
Storage Transfer Service moves data between cloud and on-premises storage systems into Cloud Storage for migration, backup, analytics, and archival. Common patterns include moving data generated on other clouds or at the edge into Google Cloud for analysis with BigQuery or Managed Service for Apache Spark, replicating a Cloud Storage bucket to another region for backup, and moving cold data off costly on-premises systems to reduce cost. The service does not provide a service level agreement (SLA) for transfer performance or latency, so some performance fluctuation may occur.
Supported source and destination combinations include:
Transfers can use Google-managed infrastructure for simplicity or self-hosted transfer agents for granular control over network routing and bandwidth consumption. A transfer over a Google-managed private network (opens in a new tab) avoids routing through the public internet. For on-premises and POSIX transfers, agents run in an agent pool that connects the source file system (opens in a new tab) to the destination bucket. Jobs can be created and managed through the Google Cloud console, the gcloud command-line tool, REST APIs, and Java and Python client libraries.
A transfer job defines the source, the destination, the schedule, and the behavior for overwrites, deletes, and logging. The job moves data from the source location to the sink location, and a single job can run once, on a repeating schedule, or continuously when configured as event-driven. Do not include sensitive information such as personally identifiable information (PII) in the transfer job name or description, because resource names can propagate to other Google Cloud resources.
A typical job setup follows this order:
For programmatic creation, the transfer specification names both sides of the flow, such as an S3 data source with a bucket name and a Google Cloud Storage data sink with a bucket name. When jobs are configured or edited through REST APIs or client libraries, schedule times must be provided in Universal Time Coordinated (UTC).
Scheduling controls when data moves from the source to Cloud Storage and how often the source is rechecked. Batch transfers execute on a one-time or scheduled repeating basis, while event-driven transfers continuously monitor the source and transfer data when objects are added or modified. The console displays schedules in local timezone but stores them in UTC, so a schedule can appear to shift when Daylight Saving Time starts or ends.
Filters narrow a transfer with prefix and time-based rules that include or exclude objects by path, last modified time, or storage class. For Amazon S3, time filters include minimum and maximum time elapsed since last modification and last-modified-since or last-modified-before timestamps. Because AWS defines last modified time as when an upload begins, a large object can match a filter while it is still uploading and then be skipped, so a recurring job should use a look-back window longer than its schedule interval, such as a two-hour window for a job that runs every hour.
Destination behavior is controlled by overwrite, delete, storage class, and metadata settings. The overwrite setting decides whether the service copies a source file when a file with the same name already exists in the destination bucket:
| Overwrite option | What happens to the destination file |
|---|---|
| Never | Storage Transfer Service skips the source file when the same name exists in the destination |
| If different | Storage Transfer Service overwrites the destination file only when ETags or checksums differ |
| Always | Storage Transfer Service always overwrites the destination file when the source name matches, even if identical |
Delete settings control consistency between source and destination:
The Cloud Storage storage class can be specified for transferred objects, but that setting is ignored when the destination bucket has Autoclass enabled, in which case objects initially receive Standard storage. For file system transfers, the service can preserve POSIX attributes as custom metadata and convert that metadata back to POSIX attributes if files are later written back to a file system.
Exam tip: A custom storage class setting is ignored when the destination bucket has Autoclass enabled; transferred objects then start as Standard storage.
Data integrity protection is how Storage Transfer Service keeps files safe and correct during the move from source to destination. All HTTPS communication supports Transport Layer Security (TLS) 1.3 for end-to-end encryption in transit, and data integrity validation checks that files are not corrupted during transfer. A highly parallelized architecture increases speed, while automatic retries and load balancing keep large transfers reliable when individual operations fail.
Operational controls help prevent network and consistency problems around the transfer. By default the service uses as much bandwidth as is available, but a bandwidth limit can be set at the agent pool level to protect other traffic, which requires the Storage Transfer Admin (roles/storagetransfer.admin) role. Transfers can be observed in Cloud Logging for Storage Transfer Service, Cloud Monitoring, agent-based transfer logs, and Pub/Sub notifications, so failed files, skipped objects, and job status can be reviewed and rerun without losing track of what reached Cloud Storage.
The Google Cloud CLI (gcloud) (opens in a new tab) and the bq command-line tool (opens in a new tab) are command-line interfaces for working with Google Cloud storage systems from a terminal or script. The gcloud tool handles project-level work such as authentication and access tokens, while the bq tool runs BigQuery jobs such as queries and loads. Together they let practitioners load data into Cloud Storage and BigQuery efficiently based on source, format, and latency requirements.
The gcloud and bq tools provide a scriptable alternative to the Google Cloud console and REST API for data preparation and ingestion. A practitioner can compose and run a query in the console, run the same work from the terminal, call the BigQuery REST API programmatically, or use BigQuery client libraries. The CLI approach fits repeatable tasks because commands can be saved in scripts, rerun the same way, and combined with other shell tools.
BigQuery jobs run through the same backend no matter which interface starts them. When BigQuery runs a query, it generates an execution tree that breaks the query into parallel stages, moves intermediate data between stages through a fast distributed shuffle tier, and then generates a query plan that can be viewed for troubleshooting and optimization. Additional workers monitor progress and may dynamically adjust the plan, and when the query is complete BigQuery writes the results to persistent storage and returns them to the user.
The bq command-line tool runs BigQuery jobs directly from the terminal, including interactive and batch work. The documentation identifies four supported ways to run a query job:
bq query command in the bq command-line tool.jobs.query or jobs.insert method in the BigQuery REST API.The bq tool must specify the correct processing location when reading location-bound data. Each public dataset is stored in a specific location like US or EU, and the BigQuery sample tables are stored in the US multi-region location. Supply the --location=US flag on the command line, choose US as the processing location in the console, or specify the location property in the jobReference section when using the API.
Exam tip: Because sample tables are stored in the US, you cannot write sample table query results to a table in another region, and you cannot join sample tables with tables in another region.
The Google Cloud CLI provides identity and project context that data-loading scripts depend on. A common pattern is to obtain an access token with gcloud auth print-access-token and pass it as a Bearer token in the Authorization header of a curl POST request to a Google Cloud REST method. The destination service then validates the token before accepting the import, load, or event-upload request.
This pattern connects CLI authentication to bulk REST imports. Data flows from the script on the practitioner's machine to the target API endpoint, and the API endpoint depends on the token issued by gcloud for authorization. The same approach is used to trigger imports that point at Cloud Storage URIs such as gs://<bucket>/<path-to-file>/ or at BigQuery tables, so one scripted identity can drive console, CLI, and REST workflows consistently.
Access for CLI-based loading is controlled by Identity and Access Management (IAM) roles granted on the project, and the job fails if the calling identity lacks the required role. The documentation names several roles that control project creation, API enablement, and data access:
roles/resourcemanager.projectCreator), which contains resourcemanager.projects.create for creating projects.roles/serviceusage.serviceUsageAdmin), which provides serviceusage.services.enable for enabling APIs.roles/bigquery.admin) for full BigQuery administration in tutorials.roles/dataform.admin) for Dataform resources.roles/bigquery.jobUser) for running BigQuery jobs.roles/storage.objectViewer) for reading objects in Cloud Storage.Troubleshooting CLI loads starts with project, billing, and API dependencies. First, verify that billing is enabled for the Google Cloud project, because load and query jobs depend on an active billing account. Then confirm that the BigQuery API is enabled in preexisting projects, since BigQuery is automatically enabled only in new projects. If an import reports permission errors, check the IAM grants above, and if an import reports data errors, expand the error in the activity log and select View full error logs to inspect the failure in Logs Explorer.
APPEND by default, and transfers only live objects.--location=US when reading sample tables stored in the US multi-region, while gcloud provides the access token that authorizes scripted REST requests.BigQuery Data Transfer Service automates scheduled ingestion into a BigQuery dataset and cannot transfer data out of BigQuery, while Storage Transfer Service moves object and file data into a Cloud Storage bucket. BigQuery Data Transfer Service loads from SaaS platforms, external databases, external storage and Google services, while Storage Transfer Service moves data from sources including Amazon Simple Storage Service (Amazon S3), Azure Blob Storage, Cloud Storage, POSIX file systems, Hadoop Distributed File System (HDFS) and HTTP URLs. Storage Transfer Service is optimized for transfers larger than 1 TiB and can move petabytes of data quickly.
The destination dataset and the destination table with its schema must already exist in BigQuery before setup, including for CSV and JSON files. All files selected by a wildcard or runtime parameters must share the same schema as the destination table and schema changes between runs cause the transfer to fail. Write preference defaults to APPEND so an unmodified file loads only once and only live Cloud Storage objects can be transferred while archived versioned objects are not supported.
Scheduled ingestion means the service runs transfer jobs automatically after the configuration is saved, with a minimum interval between recurring transfers of 15 minutes and a default interval of every 24 hours, and Cloud Storage source files are picked up immediately with no minimum file age. An event-driven transfer that uses a Pub/Sub subscription can trigger runs at lower intervals when new events arrive. The transfer configuration is set to the same location as the destination BigQuery dataset and processes and stages data in that same location, and a transfer can move data from any source region to any destination dataset location.
Storage Transfer Service offers Never which skips the source file when the same name exists in the destination, If different which overwrites the destination file only when ETags or checksums differ, and Always which always overwrites the destination file when the source name matches even if identical. A custom storage class setting is ignored when the destination bucket has Autoclass enabled, in which case transferred objects initially receive Standard storage.
An analytics team needs to collect daily marketing metrics and customer transaction records stored in an external cloud provider's object storage (such as Amazon S3 or Azure Blob Storage). The team wants to load this data automatically into a BigQuery dataset on a recurring 24-hour schedule without writing custom extraction scripts or managing virtual servers.
Which Google Cloud service should the team configure to meet these requirements?
Last updated