Morning data freshness board for your BigQuery tables

Build me a data freshness board that my data team opens first thing every morning. The whole point is to answer one question fast: did last night's data land where it was supposed to? I want a single grid covering every table across the BigQuery datasets we track, and I want the broken things impossible to miss.

The main view is one table-per-row grid with these columns: table name (project.dataset.table), dataset, owner, last updated, row count, the freshness window we expect it to land inside, and a status. Anything past its window is stale and pins to the top of the board in red with the overdue amount spelled out ("4h late", "2 days late"). Everything healthy collapses into a section below that starts closed, with a count on it like "38 tables healthy". Sort the stale group by how badly overdue it is, worst first.

Build the grid with BigQuery handlers. Use List Projects to discover the projects the connected account can read, List Datasets to enumerate datasets in the projects we have chosen to track, and List Tables to enumerate the tables in each tracked dataset. Then call Get Table per table for lastModifiedTime and numRows, which are the last-updated and row-count columns. Cache the assembled board in app state and refresh it when someone opens the app or hits a Refresh button, rather than re-walking every table on every render, because a wide warehouse means a lot of metadata calls.

lastModifiedTime is cheap but it lies for tables written by streaming inserts, which get touched constantly whether or not new business data arrived. So let each table optionally declare a freshness timestamp column instead. When one is set, measure freshness with a Run Query (Synchronous) call that selects MAX of that column for the table, and use that value as last-updated rather than the metadata timestamp. Show in the row which measure is being used so nobody is confused about where the number came from.

Clicking a row opens a detail panel for that table. The panel runs Run Query (Synchronous) against INFORMATION_SCHEMA.JOBS_BY_PROJECT (region-qualified for the dataset's location) to list the recent load and query jobs whose destination was this table, showing job id, type, who or what submitted it, start time, duration, rows written, and outcome. Below that, show a row count trend for the last 14 days from the same job history so the team can see a load that ran but landed far fewer rows than usual. Use List Jobs on the project and Get Job on individual job ids to surface failed and cancelled jobs, including the error message, since a job that errored is usually the actual explanation for a stale table.

Every stale row gets an Investigate button that kicks off a background agent for that table. The agent should: read the table's configured upstream dependencies and check whether those upstream tables are themselves fresh (Get Table, or the MAX-timestamp query where configured); pull the recent job history for the table with List Jobs, Get Job, and an INFORMATION_SCHEMA query; and then write a short plain-English diagnosis back into the app card for that table. The diagnosis should classify what happened into one of three buckets, in plain words: the pipeline failed (a job errored or was cancelled, quote the error), it ran late (the job is still running or started behind schedule), or it landed empty (the job succeeded but wrote zero or far fewer rows than the usual daily volume). If the real cause is an upstream table rather than this one, say so and name the upstream table, because pointing at the symptom is what makes these boards useless. Show a running state on the card while the agent works, then replace it with the diagnosis and a timestamp.

Once a diagnosis exists, two follow-up actions appear on that card. The first files it as a Linear issue with Create Issue, prefilled with the table name in the title and the diagnosis plus recent job details in the description, letting the user pick or default the team and assign it to the table owner where that maps to a Linear user. Store the created issue key on the card and link to it so nobody files the same thing twice. The second posts the same summary to our data channel with the Slack Bot Send a Message action, formatted as a short readable update: table, how late it is, the diagnosis, and the Linear issue link if one was filed.

Freshness windows and table owners are configured per table inside the app, not in SQL, so anyone on the team can adjust the expected cadence when a pipeline's schedule changes. Each tracked table stores: expected freshness window (a duration, plus an optional expected-by time of day for daily tables), owner name or email, optional freshness timestamp column, and an optional list of upstream tables it depends on. Include a settings view for choosing which projects and datasets are tracked, and let people edit a table's config inline from the grid too.

The board must remember which stale tables have already been acknowledged, so the same known-broken table does not shout at the team every single morning. An Acknowledge button on a stale row moves it into a muted "known issues" group below the active stale group, keeping its diagnosis and any linked Linear issue visible, along with who acknowledged it and when. The acknowledgement clears automatically the moment the table lands fresh again, so a table that breaks a second time comes back to the top in red rather than staying quietly muted forever.

A few implementation notes. BigQuery returns numRows, lastModifiedTime, and other numeric fields as strings, so parse them carefully. Jobs and queries must run in the dataset's location, so read the location from List Datasets and pass it through on query and job calls. Keep the default board build to metadata calls only, and reserve actual queries for the detail panel, the optional MAX-timestamp freshness measure, and the investigate agent, so opening the board every morning stays cheap. Show a last-refreshed timestamp on the board so people know how current the view itself is.