Calculated fields or warehouse logic

If two people have to agree on the number, the logic goes upstream. If one person is answering one question this afternoon, it goes in the chart. Everything else is a judgement call between those two poles.

That sounds like a slogan and it is actually a permissions statement. Google's documentation on where calculated fields can live sets out the constraint directly: a data source calculated field needs edit rights on the data source and is available in every report using it, while a chart-specific field exists only in the chart it was created in and cannot reference other chart-specific fields.

So the question is never really where the formula is convenient. It is who is allowed to change it, and how many places have to be edited when the definition changes.

Read the blended data row carefully, because it inverts the usual hierarchy. A data source calculated field does not work with blended data at all, so the moment a metric needs two sources joined, the only in-tool option is a chart-specific field. That is one of the reasons blended reports drift: the fields that make them work cannot be shared, which is covered in more detail in blended data and the joins that quietly break.

The recompute row is the one that turns into money. A chart field and a data source field both run inside the query, every time the query runs. A warehouse calculation runs once per refresh if it is materialised, and once per query if it is only a view.

Most wrong calculated fields are not wrong formulas. They are correct formulas applied at the wrong grain.

Take profit margin. Write it as Profit / Revenue in a data source and you get an unaggregated numeric dimension, which the chart then sums by default. Summing a column of per-row ratios is meaningless. Write it as SUM(Profit) / SUM(Revenue) and Google sets the field's default aggregation to Auto, which prevents the field from breaking if somebody changes the default aggregation later.

Do the arithmetic on a three-row example and it is obvious. Rows of (profit 10, revenue 100), (profit 5, revenue 25) and (profit 1, revenue 100). Per-row ratios are 0.10, 0.20 and 0.01, which sum to 0.31 and average to 0.103. The correct blended margin is 16 divided by 225, which is 0.071. The average of ratios is 45 percent higher than the truth, and it is higher in a way that flatters whoever built the chart.

There is a hard stop as well. Platform metrics arrive pre-aggregated, and Looker Studio refuses to aggregate them again: re-aggregating metrics is not supported, so SUM(Sessions) on a Google Analytics source produces an error. The aggregation methods available to a field are documented and short, and a metric left as None in the data source defaults to Sum in reports, which is where silent errors come from.

I am not arguing everything belongs upstream. Report-layer logic earns its place in three cases.

Presentation. Rounding, currency symbols, custom bins and custom groups are display decisions, and Google notes that custom value formatting can only be applied at the chart level anyway, not when creating a field in a data source. Pushing formatting into the warehouse turns numbers into strings and breaks sorting.

Exploration. When you do not yet know whether a metric is useful, a chart field answers the question in thirty seconds. Building a warehouse column for a metric that gets deleted next week is the more expensive mistake.

Blend-dependent maths. If the metric only exists once two sources are joined in the report, a chart field is the only tool available, and pretending otherwise wastes an afternoon.

Anything a client will see repeatedly, and anything more than one tool consumes.

The mechanical argument is cost. A logical view in BigQuery is a stored query that runs its logic every time something reads it: a view has no data of its own and the query is executed each time the view is queried. A materialised view is different in kind, because BigQuery precomputes and periodically caches the result, and can route queries against the base table to the materialised result automatically.

Put numbers on it. A ten-chart dashboard, viewed by 30 people, four times a month, with a calculation that scans 5 GB when it runs. If the calculation sits in the report, the worst case is 1,200 chart loads each triggering that scan. If it sits in a materialised table refreshed twice a day, the calculation runs 60 times a month regardless of how many people look at it, and the dashboard reads a small result. The report-layer version is not slightly worse, it is twenty times the work.

The governance argument is stronger than the cost one. A definition in the warehouse is a named object, in version control, with a review history. A definition in a chart is a string in a properties panel that nobody can search for. When a client asks why Cost per Acquisition changed, only one of those two lets you answer.

Choosing between a view and a table for the reporting layer is its own decision, worked through in BigQuery views versus tables for reporting.

Ask three questions in order and stop at the first yes.

Will a client or an executive see this number and act on it? Warehouse. It needs a definition somebody can be held to.

Will more than one report or more than one tool use it? Data source at minimum, warehouse if the second tool is not Looker Studio. A definition that only exists inside one BI tool becomes a migration problem the day the tool changes. On a migration of 154 data sources with no unplanned outages, the logic that had been buried in individual workbooks was the slowest part to move, because each buried formula had to be found before it could be rebuilt.

Am I still working out whether this metric is any good? Chart field. Promote it when it proves itself, and delete it when it does not.

The failure I see most often is a metric that passed test one months ago and never got promoted. It sits in a chart, the person who wrote it has left, and nobody can reproduce it. That is the same failure as an undocumented pipeline and it deserves the same response, which is why the reporting build described in the client reporting cycle that eats your first week treats definitions as deliverables rather than as configuration.

If the answer keeps landing on warehouse and there is no warehouse, that is the actual finding. Read marketing data warehouse or dashboard before scoping a marketing data warehouse, because the honest answer is often that a dashboard is still enough.