Table Clustering and Partitioning

[dovydasm]

• Migrations
• Scaffolding

CREATE TABLE sales (
    sale_id INT64,
    customer_id INT64,
    product_id INT64,
    store_id INT64,
    sale_date DATE,
    amount NUMERIC
)
PARTITION BY sale_date
CLUSTER BY customer_id, product_id;

 // Time-based partitioning
  modelBuilder.Entity<Order>()
      .PartitionByTime(o => o.OrderDate, BigQueryTimePartitionGranularity.Day)
      .ClusterBy(o => o.CustomerId, o => o.Status);

  // Integer range partitioning
  modelBuilder.Entity<Event>()
      .PartitionByRange(e => e.UserId, start: 0, end: 10000, interval: 100);

  // Ingestion time partitioning
  modelBuilder.Entity<LogEntry>()
      .PartitionByIngestionTime();

modelBuilder.Entity<Order>()
      .PartitionByTime(o => o.OrderDate)
      .HasPartitionExpiration(days: 90)
      .RequirePartitionFilter(true);

https://docs.cloud.google.com/bigquery/docs/clustered-tables

https://hoffa.medium.com/bigquery-optimized-cluster-your-tables-65e2f684594b