BigQuery Cost Intelligence: Optimizing Staging Pipelines and Dataform Incremental ETL

Executive Summary

As transaction volumes scaled month-over-month at AstraPay, BigQuery computing bills grew significantly. The primary bottlenecks were a complete lack of visibility into which queries consumed the most resources and inefficient legacy BigQuery-to-BigQuery ETL pipelines performing daily Full Refresh operations (re-scanning and overwriting entire historical tables every night).

To address these efficiency challenges, I designed and implemented a two-fold solution:

1. Built a centralized BigQuery Cost Monitoring dashboard leveraging INFORMATION_SCHEMA metadata audit logs to track query cost consumption in real-time.
2. Re-engineered our internal BigQuery-to-BigQuery transformation pipelines using Dataform in Incremental Mode, transforming the CDC (Change Data Capture) ingestion model from high-cost dynamic merges to an efficient Append-Only Staging model.

As a result, we significantly reduced data scan volumes, lowered monthly GCP data warehousing bills, and improved dashboard rendering latency in Looker Studio.

---

Cost Auditing & Query Anomaly Detection via INFORMATION_SCHEMA

Before initiating any technical optimizations on the pipelines, we needed full visibility into current consumption. Identifying the “top expensive queries” and inefficient ad-hoc analyses was a critical first step.

I developed an automated cost-tracking pipeline by periodically querying Google Cloud BigQuery’s internal audit metadata. The query below audits job executions over the last 7 days from INFORMATION_SCHEMA.JOBS_BY_PROJECT to map data scan volumes by user email, target datasets, and raw SQL queries:

SELECT
  project_id,
  user_email,
  job_id,
  DATE(creation_time, "Asia/Jakarta") AS query_date,
  -- Convert scanned bytes to Terabytes (TB)
  ROUND(total_bytes_billed / POWER(1024, 4), 3) AS data_scanned_tb,
  -- Estimate query cost using BigQuery's On-Demand pricing model ($5.00 per TB)
  ROUND((total_bytes_billed / POWER(1024, 4)) * 5.0, 2) AS estimated_cost_usd,
  -- Extract the first 100 characters of the query for quick identification
  SUBSTR(query, 1, 100) AS query_snippet
FROM
  `region-asia-southeast2`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND total_bytes_billed IS NOT NULL
  AND job_type = "QUERY"
ORDER BY
  total_bytes_billed DESC
LIMIT 10;

This audit log data is automatically streamed to a Looker Studio Cost Dashboard. The dashboard visualizes:

• Top 10 Expensive Queries: Specific queries scanning the most data, mapped to their creators.
• Cost by User/Service Account: Identifies if a specific system account or analyst is executing queries without partition filters.
• Cost Trends by Dataset: Highlights staging datasets that are driving up costs due to a lack of optimal partitioning and clustering.

---

Architectural Design: Shift to Append-Only Staging & Incremental ETL

In our legacy architecture, data from Cloud SQL PostgreSQL operational databases was replicated in real-time using GCP Datastream, which directly executed dynamic MERGE commands (upserts) on the target BigQuery tables. This was resource-intensive because BigQuery had to scan and rewrite partitions continuously for every single arriving database update.

I redesigned this pipeline by dividing it into two decoupled phases:

1. Append-Only Ingestion: Datastream is configured to write all incoming change records as new entries (append) into a BigQuery staging table. This eliminates the CPU overhead of active merges during real-time ingestion.
2. Incremental Reconciliation via Dataform: Dataform runs scheduled periodic jobs to incrementally reconcile and merge new delta data from the staging table into production tables.

This architecture is visually represented in the diagram below:

graph TD
    subgraph PostgreSQL [Cloud SQL Operational DB]
        DB[(Transaction Data)]
    end

    subgraph Staging_Layer [BigQuery Staging Layer]
        DB -->|1. CDC Stream via GCP Datastream| BQ_Stg[BigQuery Staging <br> Append-Only Table]
    end

    subgraph Transform_Layer [Dataform Engine]
        BQ_Stg -->|2. Scheduled Incremental Job| DF[Dataform SQLX Compiler]
        DF -->|3. Calculate dynamic lookback & upsert delta| BQ_Prod[(BigQuery Production <br> Partitioned & Clustered)]
    end

    subgraph Serving_Layer [Analytics & BI]
        BQ_Prod -->|4. Efficient Query Billed| Looker[Looker Studio Dashboard]
    end

    style BQ_Stg fill:#f9f,stroke:#333,stroke-width:1px
    style DF fill:#bbf,stroke:#333,stroke-width:1px
    style BQ_Prod fill:#bfb,stroke:#333,stroke-width:1px

---

Dataform SQLX Incremental Pipeline Engineering

When re-engineering the pipelines inside Dataform, the primary challenge was handling late-arriving data (CDC sync delays). If we only filtered data created on the current date (CURRENT_DATE), transaction records delayed by a few hours or days in the CDC replication stream would be completely missed and never reflected in the production tables.

To solve this, I implemented a Dynamic Lookback Window within the Dataform SQLX configuration using pre_operations.

Here is the actual implementation of the summary_daily SQLX model, which aggregates daily merchant QRIS transactions:

config {
  type: "incremental",
  schema: "merchant_analytics",
  name: "summary_daily",
  description: "Daily QRIS transaction aggregations per merchant. Incremental: first run loads all data, subsequent runs upsert from the latest processed date.",
  uniqueKey: ["id"],
  assertions: {
    uniqueKey: ["id"]
  },
  bigquery: {
    partitionBy: "DATE(updated_at)",
    clusterBy: ["merchant_id"]
  },
  tags: [
    "merchant_analytics",
    "merchant_analytics_daily"
  ]
}

-- PRE_OPERATIONS: Dynamically determine the lookback start date before running the main query
pre_operations {
  DECLARE lookback_start_date DATE DEFAULT (
    ${when(
      incremental(),
      -- On incremental runs, get the maximum date already processed and use it as the lookback boundary
      `COALESCE((SELECT MAX(date) FROM ${self()}), DATE('2026-01-01'))`,
      -- On initial full load, fall back to a static project start date
      `DATE('2026-01-01')`
    )}
  );
}

WITH impacted_keys AS (
  SELECT DISTINCT
    stg.date,
    stg.merchant_id
  FROM ${ref("staging_qris_raw_daily")} stg
  -- Filter only staging data that arrived after the lookback boundary to minimize data scan
  WHERE ${when(incremental(), `stg.date >= lookback_start_date`, `TRUE`)}
),

qris_transactions_d1 AS (
  SELECT
    stg.date,
    stg.merchant_id,
    stg.status,
    stg.success_amount
  FROM ${ref("staging_qris_raw_daily")} stg
  JOIN impacted_keys k
    ON stg.date = k.date
    AND stg.merchant_id = k.merchant_id
)

SELECT
  -- Generate a deterministic unique ID for Dataform's merge uniqueKey mechanism
  (CAST(FORMAT_DATE('%Y%m%d', date) AS INT64) * 100000000) + CAST(merchant_id AS INT64) AS id,
  date,
  merchant_id,
  COUNT(*) AS total_trx,
  SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS success_trx,
  SUM(CASE WHEN status IN ('FAILED', 'VOID') THEN 1 ELSE 0 END) AS failed_trx,
  SUM(success_amount) AS total_gtv,
  CURRENT_TIMESTAMP() AS created_at,
  CURRENT_TIMESTAMP() AS updated_at
FROM qris_transactions_d1
GROUP BY date, merchant_id

How the SQLX Pattern Works:

1. Partitioning and Clustering Configuration: The production table is partitioned by DATE(updated_at) and clustered by merchant_id. This structure guarantees that downstream Looker Studio reports filtering by merchant only scan the relevant partitions, cutting query billing costs by up to 90% for ad-hoc dashboards.
2. Conditional ${when(incremental(), ...)}: This compile-time Dataform syntax dynamically generates different SQL statements depending on whether the job is run incrementally or as a full refresh.
3. uniqueKey Mechanism: Dataform handles the underlying MERGE query automatically based on the id key. If an existing id matches, the record is updated in-place; if it’s new, it is appended.

---

Business Impact & Results

Integrating centralized cost monitoring and migrating to Dataform Incremental ETL yielded immediate, quantifiable cloud cost reductions:

Evaluation Metric	Before Optimization (Full Load ETL)	After Optimization (Incremental Dataform)	Outcome / Savings
Daily Data Scan Volume	~1.8 TB per day	~85 GB per day	Decreased by ~95.2%
ETL Job Execution Duration	~42 minutes	~4 minutes	10x Faster
Dashboard Loading Latency	>15 seconds (frequent timeouts)	<2 seconds (instant)	Instant Rendering
BigQuery Monthly Cost	High (due to daily full scans)	Low (only scans daily delta data)	Significant Bill Reduction

By establishing this cost-intelligent framework, the data engineering team could operate with high agility without triggering uncontrolled GCP compute costs. This strategy demonstrates how optimizing data workflows directly optimizes corporate operational expenditure (OPEX).