Lineage and drill-down
Following a cell back to the records behind it.
Every query result remembers how its columns were computed. Click a cell in the table and a strip opens below it with:
- the column name and its role
- the SQL expression behind the column
- Sources, the tables the query reads
- a drill-down button, or the reason drill-down isn't available for that cell
Column roles
| Role | What the column is |
|---|---|
| Grouping Field | A GROUP BY column, such as customer or month. |
| Measure | An aggregate such as SUM, COUNT, AVG, MIN or MAX. |
| Source Field | A column passed through from an input table unchanged. |
| Derived Field | A value computed by an expression, such as arithmetic or CASE. |
| Window Value | The output of a window function. |
| Constant | A literal value. |
| Unknown | A column Beetl could not classify. |
A ratio built from 2 aggregates, such as SUM(amount) / COUNT(*), is a derived field. To drill into
the records behind it, return the 2 measures as their own columns and drill from either one.
Drilling down
Drill-down works on aggregate results. In a grouped result, Grouping Field and Measure cells offer
Show contributing records: the input rows that went into that row, with the same filters and
join applied, matched on that row's grouping values. In an aggregate without GROUP BY, the button
reads Show source rows and returns the rows behind the total. Double-clicking a cell does the
same. Every drillable cell in a row returns the same records.
The records open as a new result with its own URL. A Records behind this value banner links Back to source result, and Copy drill query copies the SQL Beetl generated, which also appears in the editor. You can chart and download a drill-down like any other result, but you can't drill into it again.
A drill-down returns at most 1,000 records. If you get exactly 1,000, assume there are more, and
count them by running the copied query as SELECT COUNT(*) over the same FROM and WHERE.
A drill-down expires no later than the result it came from.
When drill-down isn't available
Drill-down reads the same data the value was computed from, and it is offered only where Beetl can rebuild that path exactly. It isn't available for:
- results without an aggregate, and Derived Field, Window Value and Constant columns
- queries with more than 1 join, a join other than an inner join, or a join on more than 1 condition
UNIONandUNION ALL- columns that use a non-deterministic function such as
random()ornow() - results that read registered or external datasets
- results whose source dataset has changed since the query ran
- drill-down results
In the strip, the reason appears as a short code in place of the button:
| Code | Meaning |
|---|---|
DrillProfileUnavailable | The column has no drill path, for example in a query without an aggregate. |
ShapeNotSupported | The joins or filters are outside the supported shape. |
UnionNotSupported, WindowNotSupported | The value comes from a union or a window function. |
NonDeterministicExpression | The column uses a function whose result can change between runs. |
InputRelationUnresolved | Beetl could not match a table in the query to a dataset. |
RecursiveDrillUnsupported | The result is already a drill-down. |
For registered and external datasets, and for datasets that changed after the query ran, the button can still appear, and selecting it returns an error that drill-down is unavailable. If the dataset changed, run the query again and drill from the new result.
In chat, the artifact panel shows the strip but doesn't drill. Select Open full page to open the same result on the Query page and drill from there.
Checking a number
A figure the assistant quotes or an automation report states comes with a result you can open. Check it before you act on it.
Open the result from the chat artifact or from the query listed in the automation report.
Click the number and check its expression and sources.
Select Show contributing records and look for rows that shouldn't be there, or a filter you didn't expect.
Results expire after 7 days. For an older automation report, run the automation or the query again.
Last updated on