High-Speed Data Ingestion: Integrating the BigQuery Storage Read API into .NET Applications to Bypass Memory Limits

When extracting hundreds of thousands or millions of rows from Google BigQuery into .NET calculation services (C# or F#), the standard Google.Cloud.BigQuery.V2 client library quickly becomes an architectural bottleneck. Attempting to transfer large datasets through the classic REST API (tabledata.list or jobs.getQueryResults) leads to uncontrolled memory growth, Large Object Heap (LOH) fragmentation, and container terminations in Cloud Run or Kubernetes with an OOMKilled status (OutOfMemoryException).

Switching to the BigQuery Storage Read API changes the underlying data transfer mechanics: instead of sequentially fetching text-based JSON pages over HTTP, the application establishes direct, multiplexed gRPC streams to BigQuery’s distributed storage layer (Colossus) and receives binary batches formatted as Apache Arrow or Apache Avro. This enables processing tens of gigabytes of data inside a container with a strict 512 MiB memory limit while increasing read throughput by 5 to 12 times.

1. Root Cause Analysis: Why the Standard REST API Exhausts Memory in .NET

The default data retrieval mechanism in Google.Cloud.BigQuery.V2 is designed for interactive queries and compact analytical reports. When applied to streaming data pipelines, attribution modeling, graph algorithms, or batch ML inference, four systemic issues emerge:

  1. JSON Text Serialization Overhead. BigQuery serializes every row and cell into a text-based JSON document. On the .NET client side, the parser allocates memory for millions of intermediate System.String objects, character arrays, and dictionaries inside the BigQueryRow wrapper. Reading 5 million rows across 10 columns allocates over 50 million objects in the managed heap, with lifetimes exceeding a Generation 0 (Gen 0) garbage collection cycle.
  2. Large Object Heap (LOH) Fragmentation. Buffers used for reading JSON pages and internal collection arrays during deserialization routinely exceed the 85,000-byte threshold. The .NET runtime places these buffers directly into the LOH. Because the garbage collector does not compact the LOH by default due to the high CPU cost of moving large memory blocks, unallocated gaps accumulate in the process address space. The container’s physical memory footprint (RSS / Working Set) steps up continuously until it hits the container boundary.
  3. Single-Threaded Pagination (pageToken). The classic REST API delivers data strictly sequentially. To request page N + 1, the client must completely download and parse page N, extract the nextPageToken marker from the response, and issue the next HTTP request. Parallelizing the read operation of a single table across the network is impossible.
  4. Mandatory Materialization for Filtering. The standard tabledata.list method reads the entire table along with all its columns. To select only specific fields or filter rows by a predicate, the application must submit a full query job (jobs.insert), wait for the SQL engine to write the results to a temporary table, and only then begin slow, paginated reading.
CharacteristicStandard REST API (BigQuery.V2)Storage Read API + Avro (Storage.V1)Storage Read API + Apache Arrow (Storage.V1)
Transport ProtocolHTTP/1.1 or HTTP/2 (REST, paginated)Streaming gRPC over HTTP/2Streaming gRPC over HTTP/2
Data FormatText-based JSON (row-oriented)Binary Avro (row-oriented, compact)Binary Arrow (columnar, memory-aligned)
Read ParallelismSingle stream (blocked by pageToken)Independent parallel streams (ReadStream)Independent parallel streams (ReadStream)
Column and Row Pruning Without SQLNoYes (SelectedFields and RowRestriction)Yes (SelectedFields and RowRestriction)
.NET Memory ModelHeavy object allocations across Gen 0/1/2 and LOHRow-by-row decoding from a byte streamDirect buffer access (ReadOnlySpan, Zero-Copy)
Throughput per Stream5–15 MB/s (CPU-bound by JSON parsing)60–140 MB/s100–250 MB/s (supports SIMD vectorization)

2. Three Architectural Tiers: From Simple to Advanced

Depending on the application architecture and dataset volume, three distinct approaches are used to optimize BigQuery reads.

Tier 1 (Basic): Server-Side Aggregation and Manual Pagination Control in the REST API

If the raw data volume is large but the business logic permits pushing filtering and grouping down to the database engine, the simplest solution is to execute calculations inside BigQuery (for example, via pre-aggregated Dataform marts) and extract only the final summary rows (up to 50,000 records) into .NET. When reading via BigQueryClient.GetQueryResultsAsync, a fixed PageSize (e.g., 5,000 rows) must be configured, and iteration should occur through an asynchronous enumerator without calling materialization methods like .ToList() or .ToArray(). This suffices for standard reporting but fails when a .NET algorithm requires millions of unaggregated records.

Tier 2 (Intermediate): Single-Stream Lazy Evaluation via Storage Read API and Avro/Arrow

When an application needs to ingest between 100,000 and 5,000,000 rows without redesigning the worker’s threading model, the standard client is replaced with Google.Cloud.BigQuery.Storage.V1 configured with a single session and a single read stream (MaxStreamCount = 1). In F#, this stream is wrapped into a lazy sequence (seq or IAsyncEnumerable). Data arrives in binary batches, is processed on the fly as network packets arrive, and is immediately discarded. Throughput increases by 4 to 6 times by eliminating JSON parsing, and memory consumption stabilizes at the size of a single read buffer.

Tier 3 (Advanced): Multi-Stream Storage Read API + Apache Arrow Pipeline with Zero-Copy

For high-throughput computational kernels processing tens or hundreds of millions of rows inside memory-constrained containers, parallel ReadStream channels are combined with the columnar Apache Arrow format.

BigQuery storage takes a consistent table snapshot and partitions it across multiple independent read streams. The application spawns asynchronous workers matching the number of available vCPU cores. Each worker consumes its assigned ReadStream, maps the binary ArrowRecordBatch payload directly into memory without instantiating objects per row (using ReadOnlySpan structures over contiguous primitive arrays), and folds the data into a compact result accumulator.

3. Infrastructure and Access Control (Terraform)

The standard roles/bigquery.dataViewer role is insufficient for the Storage Read API. While it permits reading table data, any attempt to initialize a read session will fail with a PermissionDenied error unless the service account holds the bigquery.readsessions.create permission. This permission is granted via the roles/bigquery.readSessionUser role at the project level where the session is created.

Below is the Terraform configuration that provisions the service account and deploys a Cloud Run v2 service with a strict 512 MiB memory limit and environment variables that tune the .NET garbage collector for small containers.

Terraform

terraform {
  required_version = ">= 1.6.0"
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = "~> 6.0"
    }
  }
}

variable "project_id" {
  type        = string
  description = "Google Cloud Project ID"
}

variable "region" {
  type        = string
  default     = "europe-central2"
}

variable "dataset_id" {
  type        = string
  description = "Target BigQuery dataset ID"
}

# 1. Dedicated service account for the .NET worker
resource "google_service_account" "bq_storage_worker" {
  account_id   = "sa-bq-storage-worker"
  display_name = "BigQuery Storage Read API .NET Worker"
  project      = var.project_id
}

# 2. Role required to create Storage Read API sessions (assigned at the project level)
resource "google_project_iam_member" "bq_read_session_user" {
  project = var.project_id
  role    = "roles/bigquery.readSessionUser"
  member  = "serviceAccount:${google_service_account.bq_storage_worker.email}"
}

# 3. Role required to read tables within the specific dataset
resource "google_bigquery_dataset_iam_member" "bq_dataset_viewer" {
  project    = var.project_id
  dataset_id = var.dataset_id
  role       = "roles/bigquery.dataViewer"
  member     = "serviceAccount:${google_service_account.bq_storage_worker.email}"
}

# 4. Cloud Run service constrained to 512 MiB of RAM
resource "google_cloud_run_v2_service" "dotnet_arrow_worker" {
  name     = "bq-arrow-stream-worker"
  location = var.region
  project  = var.project_id
  ingress  = "INGRESS_TRAFFIC_INTERNAL_ONLY"

  template {
    service_account = google_service_account.bq_storage_worker.email
    timeout         = "900s"

    scaling {
      min_instance_count = 0
      max_instance_count = 4
    }

    containers {
      image = "${var.region}-docker.pkg.dev/${var.project_id}/containers/bq-arrow-worker:latest"

      resources {
        limits = {
          cpu    = "2"
          memory = "512Mi"
        }
        cpu_idle = false # Keep CPU allocated during background stream processing
      }

      # Switch .NET to Workstation GC to conserve memory in small containers
      env {
        name  = "DOTNET_gcServer"
        value = "0"
      }
      # Cap the managed heap at 75% of the container limit (384 MiB)
      env {
        name  = "DOTNET_GCHeapHardLimitPercent"
        value = "75"
      }
    }
  }
}

4. .NET (F#) Implementation: Parallel Apache Arrow Ingestion Without GC Pressure

Compiling this module requires two official NuGet packages:

  • Google.Cloud.BigQuery.Storage.V1
  • Apache.Arrow

IPC Protocol Specifics When Working with Arrow in BigQuery

The BigQuery Storage Read API separates metadata transmission from payload transmission: the serialized table schema (ArrowSchema.SerializedSchema) is returned once inside the ReadSession object, whereas each ReadRowsResponse packet in the stream contains only an ArrowRecordBatch.SerializedRecordBatch payload without a schema header.

The standard ArrowStreamReader parser in the Apache.Arrow library expects a complete IPC stream that begins with a schema message. To avoid allocating new byte arrays for every incoming packet, the implementation below allocates a single reusable MemoryStream per worker stream. It sequentially writes the schema memory slice (ReadOnlyMemory) followed by the current batch memory slice, allowing ArrowStreamReader to project the columnar arrays directly. Crucially, because RecordBatch allocates underlying buffers via ArrayPool and unmanaged memory, it must be bound with the use keyword so that Dispose() is invoked immediately at the end of each iteration.

F#

namespace TechMacro.BigQuery.StorageEngine

open System
open System.IO
open System.Threading
open System.Threading.Tasks
open Google.Cloud.BigQuery.Storage.V1
open Google.Api.Gax.Grpc
open Apache.Arrow
open Apache.Arrow.Ipc

/// Configuration parameters for initializing a BigQuery Storage Read API session
type StorageSessionRequest = {
    BillingProjectId: string
    DatasetProjectId: string
    DatasetId: string
    TableId: string
    SelectedFields: string list
    RowRestriction: string option
    MaxStreamCount: int
}

/// Summary statistics returned by an individual parallel stream worker
type StreamResult = {
    StreamName: string
    ProcessedRows: int64
    AggregatedSum: decimal
}

module ArrowStreamReaderEngine =

    /// Processes a single RecordBatch directly through columnar buffers (Zero-Copy)
    let private processBatch (batch: RecordBatch) : struct (int64 * decimal) =
        let rowCount = int64 batch.Length

        // Access typed columnar arrays by field name
        let clientIdCol = batch.Column("client_id") :?> StringArray
        let revenueCol = batch.Column("revenue_usd") :?> DoubleArray

        // Obtain a contiguous ReadOnlySpan<double> memory slice without copying the array
        let revenueValues = revenueCol.Values
        let mutable batchSum = 0.0m

        for i = 0 to batch.Length - 1 do
            if not (revenueCol.IsNull(i)) then
                let valFloat = revenueValues.[i]
                batchSum <- batchSum + decimal valFloat

            // Read string data as a UTF-8 byte slice (ReadOnlySpan<byte>)
            // without allocating a System.String instance on the managed .NET heap
            if not (clientIdCol.IsNull(i)) then
                let utf8Bytes = clientIdCol.GetBytes(i)
                ignore utf8Bytes // Pass the byte slice directly to a hashing or parsing routine

        struct (rowCount, batchSum)

    /// Resiliently consumes a single ReadStream with automatic Offset-based reconnection
    let private consumeStreamAsync
        (client: BigQueryReadClient)
        (schemaMemory: ReadOnlyMemory<byte>)
        (streamName: string)
        (ct: CancellationToken) : Task<StreamResult> =
        task {
            let mutable offset = 0L
            let mutable totalRows = 0L
            let mutable totalSum = 0.0m
            let mutable completed = false
            let mutable retries = 0

            // Reusable 4 MiB buffer per worker thread for assembling IPC messages
            use ipcBuffer = new MemoryStream(4 * 1024 * 1024)

            while not completed && not ct.IsCancellationRequested do
                try
                    let readRequest = ReadRowsRequest(ReadStream = streamName, Offset = offset)
                    use responseStream = client.ReadRows(readRequest, CallSettings.FromCancellationToken(ct))
                    let enumerator = responseStream.GetResponseStream().GetAsyncEnumerator(ct)

                    try
                        let! hasFirst = enumerator.MoveNextAsync()
                        let mutable hasMore = hasFirst

                        while hasMore do
                            let currentResponse = enumerator.Current
                            if currentResponse.RowCount > 0L then
                                let batchMemory = currentResponse.ArrowRecordBatch.SerializedRecordBatch.Memory

                                // Assemble a valid IPC stream inside the reusable buffer: Schema + Batch
                                ipcBuffer.SetLength(0L)
                                ipcBuffer.Position <- 0L
                                ipcBuffer.Write(schemaMemory.Span)
                                ipcBuffer.Write(batchMemory.Span)
                                ipcBuffer.Position <- 0L

                                use arrowReader = new ArrowStreamReader(ipcBuffer, leaveOpen = true)
                                // Explicitly release RecordBatch pooled buffers via 'use!'
                                use! recordBatch = arrowReader.ReadNextRecordBatchAsync(ct)

                                if not (isNull recordBatch) then
                                    let struct (rows, sum) = processBatch recordBatch
                                    totalRows <- totalRows + rows
                                    totalSum <- totalSum + sum
                                    offset <- offset + rows

                            let! next = enumerator.MoveNextAsync()
                            hasMore <- next

                        completed <- true
                    finally
                        enumerator.DisposeAsync().AsTask().Wait()

                with
                | :? Grpc.Core.RpcException as rpcEx when
                    (rpcEx.StatusCode = Grpc.Core.StatusCode.Unavailable ||
                     rpcEx.StatusCode = Grpc.Core.StatusCode.Internal ||
                     rpcEx.StatusCode = Grpc.Core.StatusCode.DeadlineExceeded) && retries < 3 ->
                    retries <- retries + 1
                    let backoffSeconds = float (1 <<< retries)
                    do! Task.Delay(TimeSpan.FromSeconds(backoffSeconds), ct)
                    // On the next loop iteration, reading resumes at the exact row offset

            return {
                StreamName = streamName
                ProcessedRows = totalRows
                AggregatedSum = totalSum
            }
        }

    /// Initializes the read session and dispatches parallel stream workers
    let readTableParallelAsync (req: StorageSessionRequest) (ct: CancellationToken) : Task<StreamResult[]> =
        task {
            let! client = BigQueryReadClient.CreateAsync(ct)

            let tableResource =
                $"projects/{req.DatasetProjectId}/datasets/{req.DatasetId}/tables/{req.TableId}"

            let tableOptions = ReadSession.Types.TableReadOptions()
            for field in req.SelectedFields do
                tableOptions.SelectedFields.Add(field)

            match req.RowRestriction with
            | Some predicate when not (String.IsNullOrWhiteSpace(predicate)) ->
                tableOptions.RowRestriction <- predicate
            | _ -> ()

            let sessionDefinition =
                ReadSession(
                    Table = tableResource,
                    DataFormat = DataFormat.Arrow,
                    ReadOptions = tableOptions
                )

            let createSessionReq =
                CreateReadSessionRequest(
                    Parent = $"projects/{req.BillingProjectId}",
                    ReadSession = sessionDefinition,
                    MaxStreamCount = req.MaxStreamCount
                )

            let! session = client.CreateReadSessionAsync(createSessionReq, CallSettings.FromCancellationToken(ct))
            let schemaMemory = session.ArrowSchema.SerializedSchema.Memory

            let workers =
                session.Streams
                |> Seq.map (fun s -> consumeStreamAsync client schemaMemory s.Name ct)
                |> Seq.toArray

            return! Task.WhenAll(workers)
        }

Key Optimization Mechanisms in the Code

  • Storage-Level Pruning (SelectedFields and RowRestriction). Specifying column names in SelectedFields instructs BigQuery storage to read only the relevant columnar files. Passing a filter predicate in RowRestriction (for example, "event_date >= '2026-10-01' AND country = 'UA'") prunes partitions and clusters before data traverses the network, eliminating unnecessary byte transfer.
  • Exact Stream Resumption (Offset). When transferring tens of gigabytes, a gRPC connection may be dropped by an intermediate network load balancer with an Unavailable status code. Because the offset counter increments strictly after each RecordBatch is processed, re-issuing ReadRowsRequest with the current Offset resumes the stream from the exact disconnection point without duplicating records or restarting the session.
  • Zero-Allocation String Handling. Calling clientIdCol.GetBytes(i) returns a ReadOnlySpan of bytes pointing directly into the Arrow buffer. If a user identifier needs to be hashed or matched against a lookup value, the operation executes over the byte slice with zero garbage collection overhead.

5. .NET Memory Management Specifics in Linux Containers

Even with properly structured streaming code, default .NET runtime settings can still trigger container terminations (OOMKilled) due to how the garbage collector behaves inside containerized Linux environments.

  1. The Server GC Issue Under Low RAM Limits. Official Docker images ([mcr.microsoft.com/dotnet/aspnet](https://mcr.microsoft.com/dotnet/aspnet) and runtime) enable Server Garbage Collection (Server GC) by default. Server GC creates an independent managed heap and dedicated GC thread for each logical CPU core, prioritizing throughput by delaying collections and reserving large memory segments. Inside a container configured with 2 vCPUs and 512 MiB of RAM, Server GC frequently exhausts the container memory limit before a full collection is triggered. Switching to Workstation GC (DOTNET_gcServer=0) consolidates the heap and forces the collector to run conservatively, keeping process memory consumption steady between 120 and 180 MiB.
  2. Accounting for Unmanaged Memory (DOTNET_GCHeapHardLimitPercent). A Linux container enforces a hard limit on the total process memory footprint (via cgroups), which encompasses the managed .NET heap, thread stacks, JIT-compiled code, and native gRPC/HTTP2 buffers. Setting DOTNET_GCHeapHardLimitPercent=75 restricts the managed heap to 75% of the container limit, leaving a guaranteed 25% headroom for native network buffers and operating system structures.

6. Design Anti-Patterns and Operational Troubleshooting

3 Core Anti-Patterns When Adopting the Storage Read API

  1. Materializing the Stream into In-Memory Collections. Replacing the transport layer with the Storage Read API while still accumulating all incoming records into a ResizeArray (List in C#) prior to calculation negates all memory savings. Processing must be architected as a streaming fold/aggregation or decoupled via a bounded producer-consumer channel (System.Threading.Channels.Channel.CreateBounded).
  2. Unbounded Stream Parallelism (MaxStreamCount = 0). Leaving MaxStreamCount at 0 allows BigQuery’s scheduler to choose the stream count for maximum throughput, which on a large table can result in 50 to 100 streams. Launching Task.WhenAll across 100 concurrent streams inside a 2-vCPU container allocates 100 simultaneous read buffers and causes an immediate OutOfMemoryException. MaxStreamCount should be explicitly bounded to match the container’s CPU allocation (typically 1 to 2 streams per vCPU).
  3. Attempting to Read Standard Logical VIEWs via the Storage API. The Storage Read API interacts directly with the underlying storage layer and supports only physical tables, temporary query result tables, and materialized views (Materialized Views). Passing a resource path pointing to a standard logical VIEW returns an InvalidArgument exception.

Operational Troubleshooting Reference

Production Error / SymptomRoot CauseEngineering Solution
StatusCode.PermissionDenied on CreateReadSessionThe service account holds roles/bigquery.dataViewer but lacks permission to initialize read sessions.Grant roles/bigquery.readSessionUser on the project specified in the Parent field of the request.
Linear growth of container RSS memory while the managed .NET heap remains flatMemory pool leak inside Apache.Arrow caused by omitting Dispose() calls on processed batches.Ensure deterministic disposal (use in F# or using in C#) for every RecordBatch and ArrowStreamReader instance.
Overflow exception when reading NUMERIC / BIGNUMERIC columnsArrow represents NUMERIC as a Decimal128Array (38 digits of precision), which exceeds the capacity of .NET’s System.Decimal (28–29 digits).Cast NUMERIC values to FLOAT64 or INT64 (e.g., monetary values in cents or micro-units) in the source table, or parse the raw bytes via SqlDecimal.
ArrowStreamReader returns null or throws a framing exceptionOnly the SerializedRecordBatch bytes were written to the stream without prepending SerializedSchema.Prepend session.ArrowSchema.SerializedSchema bytes to the buffer before reading each batch.
StatusCode.FailedPrecondition: Session expiredStream consumption exceeded the maximum session lifetime, or the worker attempted to reuse a stale session ID.Create a fresh ReadSession immediately before starting the extraction job (sessions expire after 6 hours).
session.Streams returns only 1 stream despite MaxStreamCount = 4After applying the RowRestriction filter, the remaining data volume is small (tens of megabytes).Standard BigQuery behavior: the storage engine does not partition small datasets across multiple streams. Workers must iterate over the actual session.Streams.Count.

7. Financial Impact (FinOps): BigQuery and Cloud Run Cost Optimization

Adopting the Storage Read API directly reduces two primary cloud cost drivers: BigQuery data extraction charges and Cloud Run compute billing.

BigQuery Pricing Model

  • On-Demand Pricing (per-TiB scanned): Standard SQL queries (jobs.query) cost $6.25 per TiB scanned (with the first 1 TiB per month free). Data read via the Storage Read API is billed separately at approximately $1.10 per TiB read, and every billing account receives a 300 TiB monthly free tier. Because SelectedFields and RowRestriction prune unneeded columns and partitions at the storage layer, most mid-sized and large analytical workloads operate entirely within the 300 TiB/month free quota.
  • BigQuery Editions (Standard / Enterprise): For organizations using capacity-based commitments (Autoscaling Slots), Storage Read API operations can be assigned to a baseline slot reservation, eliminating per-terabyte extraction charges altogether.

Cloud Run Compute Cost Reduction

In a production workload processing a daily table of 60 million rows (approximately 15 GB across 8 columns), replacing the standard REST client with a 4-stream Storage Read API and Apache Arrow pipeline yields the following measurable improvements:

  • Required container memory drops from 16 GiB to 512 MiB (a 32x reduction) by eliminating JSON string allocations and LOH fragmentation.
  • Job execution time decreases from 45 minutes to 3 minutes (a 15x speedup) due to 4-way parallel streaming and zero-copy binary access.
  • Total Cloud Run compute cost (vCPU-seconds and GiB-seconds) per job drops by more than 95%, while eliminating overnight job failures caused by OutOfMemoryException.

8. Selection Criteria and Migration Checklist

Integrating the BigQuery Storage Read API is not necessary for every query. The standard Google.Cloud.BigQuery.V2 library remains the appropriate tool for executing DDL/DML statements, orchestrating SQL transformation pipelines inside BigQuery, and fetching small lookup tables (under 10,000 to 50,000 rows).

Migrating to Google.Cloud.BigQuery.Storage.V1 and Apache.Arrow is warranted when:

  1. A single extraction into a .NET service exceeds 100,000 rows or 50 MB of raw data.
  2. Container telemetry reveals memory spikes, LOH expansion, or OOMKilled restarts.
  3. Network data transfer and deserialization time exceeds the execution time of the .NET calculation algorithm itself.

Before deploying to production, verify four mandatory parameters: the service account holds the roles/bigquery.readSessionUser IAM role, required columns are explicitly listed in SelectedFields, Dispose() is deterministically called on every RecordBatch inside the read loop, and Server GC is disabled (DOTNET_gcServer=0) for containers provisioned with less than 1.5 GiB of RAM.

Similar Posts