How We Built a DataOps Dashboard for BigQuery and Saved Our Budget from Time Travel

Cloud providers love two things: infinite scaling and analysts who don’t know how to use WHERE _PARTITIONTIME. BigQuery works like an all-you-can-eat buffet: you can grab a petabyte of data in a second, but the bill at the end of the month will make your CFO need a strong drink. The cloud does not forgive lazy architecture. Every bad CROSS JOIN or daily DROP TABLE turns into real dollars flying off the company’s credit card.

Punishing the team after the Google bill arrives is a strategy for weak managers. Real Data Engineering builds analytical firewalls—dashboards where a mistake becomes visually obvious before it turns into a financial disaster.

In this article, we will break down the real process of building FinOps and DataOps dashboards in Looker Studio. We will walk through our engineering journey: from hunting down the “greediest” scripts to fighting Looker Studio’s weird UI, and solving the mystery of disappearing Google Analytics data. No marketing nonsense about “smart monitoring”—just SQL, billing math, and the cold, hard truth of system logs.

Block 1. The Compute Inferno: Who is Burning Our Money? (The Foundation)

Before digging into disk space (which is dirt cheap in GCP), we need to close the biggest financial black hole: Compute. With On-Demand pricing, you pay $6.25 for every terabyte scanned. If a junior analyst or a crazy Python script decides to scan three years of logs without limits, your project will go bankrupt faster than you can click Cancel Query.

For basic hygiene, we created two reports using the INFORMATION_SCHEMA.JOBS system view.

1.1. The “Big Spenders” Rating: Cost by User

This dashboard answers a very direct question: “Who exactly is burning the project’s budget right now?”

We show a flat table, sorted by scanned terabytes (tb_billed).

SQL Extraction Algorithm (based on incremental aggregation):

SQL

SELECT
  user_email,
  EXTRACT(DATE FROM creation_time) AS usage_date,
  COUNT(job_id) AS total_queries,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tb_billed,
  ROUND((SUM(total_bytes_billed) / POW(1024, 4)) * 6.25, 2) AS est_cost_usd,
  ROUND(SUM(total_slot_ms) / 1000, 2) AS slot_seconds
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND statement_type != 'SCRIPT'
GROUP BY 1, 2
ORDER BY tb_billed DESC;

Dashboard Parameter Dictionary:

Parameter in DB	Engineering Meaning and Interpretation
user_email	The suspect’s ID. It helps to quickly tell the difference between a real human (testing heavy code) and a service account (a broken robot hitting the database every minute).
tb_billed	The core metric of greed. If an analyst read 50 TB in one day with a low total_queries count, they are writing absolutely “toxic” SQL (they forgot their filters).
est_cost_usd	Translating abstract bytes into the language of business. This is your best weapon when arguing why you need sprint time to rewrite a pipeline.

1.2. The Hot Zones: Cost by Table

If the first report shows who, this one shows where. If a raw, unoptimized raw_events table generates 80% of your reading costs, it is a mathematical sign that you need to build a Rollup table or use partitions. (Note: In INFORMATION_SCHEMA, the referenced_tables array must be unpacked using UNNEST).

Parameter in DB	Engineering Meaning and Interpretation
table_id	The target entity. Highlights the bottlenecks in your Data Lineage.
query_count	Table popularity. A great metric to find out what data the business actually needs, and what is just lying around dead.
tb_billed_impact	The share of the financial pain this table brings to the project.

Block 2. The Reliability Pulse and UI Fights (System Health)

We sorted the money. Now let’s move to stability, because cheap but wrong data is useless. We started by building a “System Health Trend” line chart (X-axis: usage_date, Y-axis: AVG reliability_score).

And this is where we met the classic traps of data visualization.

Trap 1: The Auto-Scale Heart Attack

When we first displayed the chart, it looked like our infrastructure was collapsing. The problem was Looker Studio’s automatic Y-axis scaling. The tool cut the scale from 0.99 to 1.0. As a result, a tiny 0.5% drop in stability (one failed script out of a thousand) looked like a catastrophic cliff across the screen.

Engineering Fix: Hardcode the Y-axis limits from 0 to 1 (or 0%–100%) in the style settings. The visual panic must match the actual problem.

Trap 2: Finding the Missing Base Line

A chart is completely useless without a boundary—an SLA (Service Level Agreement). We needed to draw a hard line at 90%. Finding the “Reference Line” button in the depths of Looker Studio is a quest of its own. We finally set the value to 0.9, color to a red dashed line, and labeled it SLA 90%. Now, any drop below this line acts as a visual siren.

Anatomy of Flaky Errors and Dead Integrations

By filtering the chart by specific service accounts, we were able to diagnose real problems from our logs:

1. The “Race Condition” Pattern: The account id-fcf744... showed a weird trend. On the 12th and 16th, its stability dropped to 91%, but the next day it magically went back to 100% without any code changes. Robots don’t make typos. Diagnosis: the script runs on a strict schedule, but sometimes external data is late. The robot tries to read an empty table, dies, and the next day everything works again.
2. The OWOX Flatline: Filtering the chart by admin.analytics@owox.com (the data integrator from Google Analytics), we saw a perfectly flat line at the bottom (0) for three days. 100% of queries failed. Going to the log details table, we confirmed the reason: accessDenied. Someone took away the robot’s IAM rights, and the business lived without fresh web analytics for three days without even suspecting it!

Block 3. Storage Audit: The Time Travel Tax

Storing data is cheap, but BigQuery knows how to charge you even for the things you deleted. We displayed the vw_storage_efficiency_daily view and sorted it by time_travel_gb. This is the trash bin of deleted data that BigQuery keeps for 7 days just in case you need to restore it.

Looking at the metrics, we discovered the ugly truth about our pipeline code quality.

For example, the user table was 78 GB physically. But its Time Travel volume was 71 GB! The firstVisitorsSession table weighed 32 GB, generating 26 GB of trash.

Diagnosis: Instead of carefully updating new rows (MERGE) or adding fresh partitions (INSERT), the developers took the path of least resistance: they run a hard DROP TABLE and CREATE TABLE every single night. The engine obediently deletes the old version, puts it in Time Travel (which you pay for), and creates a new one. Having these metrics on the dashboard allows you to catch these lazy scripts and send them for a mandatory rewrite.

Parameter in DB	Engineering Meaning and Interpretation
time_travel_ratio	Deleted data divided by active data. Perfect = 0. A value > 0.5 is a sign of lazy DDL (abusing DROP/CREATE or TRUNCATE).
compression_ratio	Compression coefficient. How many times BigQuery squeezed the logical data. 10.0 is great columnar architecture. 1.5 means the table is stuffed with “fat” JSONs or arrays without clustering.
long_term_ratio	The share of old data (older than 90 days) that gets a 50% discount. A regular DROP TABLE resets this timer, forcing you to pay full price.

Block 4. The Secret Sauce: Calculated Fields

A dashboard becomes truly powerful when you start synthesizing new metrics right in the UI (things we can add, but didn’t use in the basic build). Here are three examples of calculated fields for a deep FinOps audit:

1. Wasted Storage Cost USD

Shows the physical money lost on recreating tables.

• Formula: time_travel_gb * 0.02 (assuming $0.02 per GB/month).
• Meaning: It’s hard to inspire an engineer by saying “you have a bad time_travel_ratio.” But if you show a dashboard column that says their DROP TABLE script throws $450 a month into the trash bin—the task to rewrite the code gets the highest priority.

2. Full Scan Penalty

Catches query imbalance.

• Formula: total_bytes_billed / IF(total_rows_returned = 0, 1, total_rows_returned)
• Meaning: If a query scanned 1 Terabyte of data just to return 5 rows, it is an architectural crime. This indicator will highlight such anomalies in red. The higher the index, the more dangerous the code.

3. Severity Heatmap

• Formula: 0.90 - reliability_score
• Meaning: If the value is negative (the system runs at 99%, delta = -0.09), the cell turns a calming green. If the delta is positive (SLA broken at 80%, delta = 0.10), it turns aggressive red. The operator’s eye scans hundreds of rows and only catches the anomalies.

Architecture Bottlenecks (Trade-offs)

A mandatory chapter for any honest engineering report. This control system has its compromises:

1. The Observer Effect (Cost of Monitoring): Queries to INFORMATION_SCHEMA.JOBS process huge amounts of metadata. If your Scheduled Queries collect these logs without strict partition filtering (e.g., creation_time >= TIMESTAMP_SUB(...)), your money-saving tool will quickly become the project’s biggest expense. Incremental updates here are a matter of survival.
2. Billing Delay: Physical table size (TABLE_STORAGE) does not update in the exact same second. BigQuery’s internal background processes need hours to recalculate long_term_bytes. This dashboard is a fantastic tool for retrospective analysis and a trigger for refactoring, but it is not for fighting fires in real-time.
3. Alert Fatigue: If you connect Slack notifications to this monitor for every time reliability_score drops below 99%, the team will go crazy from the “white noise”. Analysts are living humans; their syntax errors while exploring data are inevitable. That is exactly why we strictly filter stability metrics by service accounts. Robots are not allowed to make mistakes.

Building an analytical firewall in Looker Studio is about bringing mathematical and financial discipline into the chaos of cloud development. We walked the path from naked logs to meaningful patterns, learned how to find broken integrations (like with OWOX), and caught lazy scripts with DROP/CREATE.

The conclusion is simple: data is transparent. If your budget is flying out the window, the database knows exactly who is guilty. Your job is just to ask it the right SQL question and draw a red baseline on a chart.