AnalyticsBigQuery and Looker
Operations questions, answered with BigQuery and Looker.
Operations asks me things like which orders are stuck and why, and which integrations fail more than they should. I answer them from the data warehouse, and I make sure the number means what the person asking thinks it means.
- Data warehouse
- BigQuery
- Shared metrics
- Looker
- Data catalog
- DataHub: where data comes from, when it last loaded
- Access
- Read only, always
Two real questions, and the SQL that answers them
The two questions I get asked most, and the queries behind them. The table names and values are invented for this page. The structure is the one I use.
The question
Which stuck orders share a cause?
with latest as ( select order_id, status, timestamp_diff(current_timestamp(), updated_at, hour) as hours_in_status, row_number() over ( partition by order_id order by updated_at desc ) as recency from ops.order_events where event_date >= date_sub(current_date(), interval 30 day))select case l.status when 'ALLOCATED' then 'allocated, not released' when 'RELEASED' then 'released, not picked' when 'PAYMENT_HOLD' then 'waiting on payment' else 'unexplained' end as pattern, count(*) as orders, max(l.hours_in_status) as oldest_hoursfrom latest as ljoin defs.stuck_threshold as t on t.status = l.statuswhere l.recency = 1 and l.hours_in_status > t.max_hoursgroup by patternorder by orders descWhat comes back
| Column | Type | Holds |
|---|---|---|
| patternThe named pattern, or unexplained | STRING | The named pattern, or unexplained |
| ordersHow many stuck orders share it | INT64 | How many stuck orders share it |
| oldest_hoursHow long the oldest has waited | INT64 | How long the oldest has waited |
It is slower the first time. The second time someone asks, the answer is already shared and takes seconds.
Where this runs every day
Control Tower, the internal platform I built for stuck orders, reads from the warehouse in two places. Both are read only.
- Query that finds stuck orders
- Read only access
- Sensitive details removed
- Saved for ten minutes
- Report
Saved copy,ReportIf the data warehouse cannot answer
01
The fleet report
One fixed query sorts every stuck order into a pattern. The page never asks the warehouse directly: the query runs in the background and the report reads a saved copy with sensitive details removed.
02
Inventory search
When the live platform cannot answer a unit lookup, the search falls back to a saved copy of unit locations from the warehouse instead of returning nothing.
How I make sure the number is right
Two people counting stuck orders with two different queries get two different numbers. So before I write any SQL, I check four things in this order. Here is one question walked through all four.
Step 1 of 4Metric definition
Asks
What does stuck mean here?
Finds
Which statuses count, and after how long. Defined once, for every report
Verdict: Answers what stuck means. Not why. Keep going.
Step 1 of 4
Nine habits before I share a number
In the order a question meets them: the first four decide whether a number can be trusted, the next four whether it is right, and the last whether answering it is safe.
1Can it be trusted
Definition
Start from the shared definition
The metric comes from its shared definition, formula and caveats included, not from a query I wrote this morning.
Catalog
Know where the data comes from
Before I use a table I check what feeds it, so every number traces back to the system that produced it.
Catalog
Check when it last loaded
I check when the table last loaded. A correct query over last week's data is a wrong answer about today.
Looker
Shared metrics, not private ones
A number that leaves the team comes from a shared Looker report, so everyone who asks sees the same breakdown and the same totals.
2Is it right
SQL
Filter by date first
Every query names its date range first, so the range is a choice I make rather than one the system makes for me. It is also cheaper and faster.
SQL
Latest state per order
The query keeps only each order's most recent event, so no order is counted twice.
SQL
A name for everything
Every stuck order lands in a named pattern or in one called unexplained. Nothing drops out because no rule matched it.
SQL
A rate always shows its volume
A failure rate always travels with the number of messages behind it, so two percent of fifty never reads like two percent of fifty thousand.
3Is it safe
SQL
Read only, always
Warehouse queries run with read only access. Answering a question should never be able to change the data it is about.
My role
I am the person an operations question comes to, and the person who has to stand behind the number afterwards.
- 01Turn a question from operations into a definition first, and only then into a query.
- 02Write the warehouse SQL for what no shared report covers: event sequences, classification, rates over time.
- 03Check where the data comes from and how fresh it is before a number is shared, and say so when the data is late.
- 04Build and run the data side of Control Tower: the query that finds stuck orders, the read only access, the saved copy and the backup used when the warehouse cannot answer.
Next I would move the rules that name each pattern into the shared definitions, so anyone can use them, not just one query.
Numbers about how it is built, not business results
- Steps a question passes before raw SQL
- 4
- Definition of stuck, shared by every report
- 1
- Reads from the warehouse in production
- 2
- Saved copy kept for the fleet report
- 10 min
A question about stuck orders or failing integrations gets the same answer whoever asks it, and every number in that answer traces back to a definition and a table.
Built with
- BigQueryData warehouse
- LookerShared metrics
- DataHubData catalog: source and freshness
- Next.jsControl Tower, where it runs
- TypeScriptLanguage