Fetching column data from a column database

Source: Dev.to

Problem

Fetching BigQuery data row‑by‑row in C# and manually building arrays leads to:

• Slow performance for large datasets
• High CPU usage
• High memory consumption
• Inefficiency for high‑volume tables

You ideally want a structure like:

export interface DealData {
  Season: number[];
  countryOfProduction: string[];
  productionGroup: string[];
  supplierName: string[];
  dealTypes: string[];
}

Best Practice: Let BigQuery Build Arrays (Using ARRAY_AGG)

Instead of retrieving many rows and aggregating in C#, let BigQuery return a single row with arrays for each column using ARRAY_AGG.

Optimized BigQuery SQL

SELECT
  ARRAY_AGG(Season) AS Season,
  ARRAY_AGG(countryOfProduction) AS countryOfProduction,
  ARRAY_AGG(productionGroup) AS productionGroup,
  ARRAY_AGG(supplierName) AS supplierName,
  ARRAY_AGG(dealTypes) AS dealTypes
FROM your_dataset.your_table;

Benefits

• Only one row is returned.
• Aggregation is performed in parallel by BigQuery.
• Faster and more memory‑efficient.
• Minimal processing required in C#.

C# Example: Reading Aggregated Arrays from BigQuery

using Google.Cloud.BigQuery.V2;
using Newtonsoft.Json;

public class DealData
{
    public List Season { get; set; } = new();
    public List countryOfProduction { get; set; } = new();
    public List productionGroup { get; set; } = new();
    public List supplierName { get; set; } = new();
    public List dealTypes { get; set; } = new();
}

public async Task<string> GetDealDataAsync()
{
    BigQueryClient client = BigQueryClient.Create("your-project-id");

    string query = @"
        SELECT
          ARRAY_AGG(Season) AS Season,
          ARRAY_AGG(countryOfProduction) AS countryOfProduction,
          ARRAY_AGG(productionGroup) AS productionGroup,
          ARRAY_AGG(supplierName) AS supplierName,
          ARRAY_AGG(dealTypes) AS dealTypes
        FROM your_dataset.your_table
    ";

    var result = client.ExecuteQuery(query, null);
    var row = result.First();

    DealData dealData = new DealData
    {
        Season = row["Season"].ToList(),
        countryOfProduction = row["countryOfProduction"].ToList(),
        productionGroup = row["productionGroup"].ToList(),
        supplierName = row["supplierName"].ToList(),
        dealTypes = row["dealTypes"].ToList()
    };

    return JsonConvert.SerializeObject(dealData);
}

Summary

• BigQuery stores data columnar and is optimized for analytical workloads.
• Avoid looping through rows in C#.
• Use ARRAY_AGG to let BigQuery perform column‑wise aggregation.
• C# then reads a single row containing arrays → fast, clean, and efficient.