A query can run without errors and still count the same sale twice. In written answers to LDS, dbt Labs product director Elias DeFaria explains the difference between SQL validation and business correctness, supplies a small revenue example, and describes a seeded-fault evaluation. The practical lesson is to test the approved metric definition, check the reference itself, and compare data carefully before changing engines.
The query ran. Every column existed. September revenue was still wrong.
In a synthetic example supplied to Let's Data Science, dbt Labs shows how an AI-generated query turns $160 of completed orders into $260 of category revenue. The mistake is an ordinary join: one order has two items, and the query counts the whole order once for each item.
Elias DeFaria, Director of Product Management at dbt Labs, uses that example to explain a boundary that matters as teams give coding agents more responsibility for analytics. A compiler can check whether a query is valid against the project. It cannot settle what the business intended revenue to mean.
In six written answers to LDS, DeFaria describes how tests, reviewed metric definitions and a separate reference calculation can help. He also supplies an evaluation in which automated reconciliation found planted errors that a narrow manual check missed, while failing when the reference contained the same mistake.
How $160 becomes $260
The sample contains two completed orders: one for $100 and another for $60. A third, refunded order is excluded. The $100 order contains a $60 apparel item and a $40 home item; the other completed order contains a $60 apparel item.
The incorrect query joins orders to their items, groups by product category, and calculates sum(o.order_total). That repeats the $100 order total across its two item rows. Apparel receives $100 plus $60, while home receives another $100.
The corrected query keeps the same join and completed-order/date filters, but calculates sum(i.item_amount). For these supplied rows, the item amounts add up to their order totals, so the category figures reconcile to the approved $160 total.
| Category | Incorrect order-total sum | Correct item-amount sum |
|---|---|---|
| Apparel | $160 | $120 |
| Home | $100 | $40 |
| Total | $260 | $160 |
LDS reproduced these two aggregations in a local SQLite check using the supplied rows and obtained the figures above. This verifies the small SQL example, not dbt's engine or its separate evaluation.
The underlying issue is grain: what one row represents. The joined rows represent items, but the incorrect query sums a measure belonging to an entire order. The database has no reason to reject that combination merely because it is a poor way to answer this business question.
DeFaria says SQL validation "doesn't check whether the SQL means what the business means."
That distinction applies beyond revenue. A join can duplicate customers, a missing filter can include refunded orders, and a timezone choice can move transactions into a different reporting period. A successful execution does not resolve those decisions.
Tests need to express the intended answer
DeFaria proposes two checks for the sample. A unit test supplies known orders and items, then expects apparel revenue of $120 and home revenue of $40. A reconciliation test sums category revenue and compares it with completed-order revenue for the same month.
They address different questions. The first checks a particular piece of logic against deliberately chosen inputs. The second asks whether the result agrees with the approved calculation on the data being processed. Neither removes the need to review that calculation.
The totals check catches this example's extra $100. It would not necessarily catch a category error that moves revenue between categories while leaving the overall total unchanged. Testing relevant slices, such as product category or store-local month, helps expose mistakes that disappear in a grand total.
dbt's unit-test documentation describes testing model logic with small supplied inputs. The important design choice is which inputs and expected results a team supplies: include the multi-item order, refund or time-boundary condition that could make apparently reasonable SQL wrong.
The item-based correction also depends on the metric. For a real dataset, a team must decide how discounts, tax, refunds and order-level adjustments belong in category revenue. The sample establishes the arithmetic for its own rows; it does not provide a universal revenue policy.
What stricter SQL checking can establish
dbt v2 adds static analysis, checks performed before executing model SQL. Its current documentation distinguishes baseline mode from strict mode. Strict mode adds checks such as data types and function signatures, along with precise column-level lineage, and validates the project before execution.
Lineage traces where a result column came from. In DeFaria's example, seeing revenue derived from an order-level amount but grouped by an item-level category is a reason to investigate. He calls it "a signal, not a verdict."
A valid business allocation could involve columns from different levels. The reviewer still needs to understand the intended measure and the data relationships. More detailed SQL understanding makes that review better informed; it does not replace it.
DeFaria recommends keeping the approved metric definition versioned and reviewed, and exposing governed definitions to agents rather than having each agent invent its own join. If two plausible definitions exist, the agent should identify them and ask a named owner to choose.
Teams should also check the mode actually in use. The current static-analysis documentation says strict runs require dbt login; unauthenticated runs fall back to baseline. Having the software installed does not establish that the intended validation gate is active.
What the evaluation found, and what it missed
The evaluation report supplied by dbt compares dbt v1.12.5 and v2.0.6 on a local DuckDB workload, with known faults planted in six metric models. It separates early structural checks from reconciliation of business results.
For 13 semantic fault variants detectable by its reference, the limited manual check found three. AI-generated reconciliation found all 13 in each of three repetitions: 39 successful detections in 39 runs, not 39 different semantic faults. The manual baseline used two total-level queries; the automated workflow could investigate many more slices. This does not compare AI with an unrestricted expert audit. The report records no false positives across 18 clean-control runs after adjudication and about $0.155 in model usage per validation reaching reconciliation. That figure is not the complete cost of validation. LDS reviewed the published report and records but did not rerun the evaluation.
The most revealing failure concerns the reference. When the dashboard calculation shared the model's bug, all ten runs across the compared workflows missed it. Agreement between two outputs was therefore insufficient evidence that either represented the approved business definition.
DeFaria also says reduced production rework is "not yet measured." The planted-fault results show what the tested workflows detected under stated conditions, rather than proving a reduction in a customer's operating costs or incident rate.
Compare the data before changing the engine
For migration, DeFaria recommends building equivalent selections in separate schemas against the same source data, then comparing row counts, keys and important metric values. Incremental models need another precaution: both runs must start from the same stored state. Clone the existing table into both check schemas, freeze the input window, run the same incremental batch and compare. Then compare full-refresh builds too, because those two paths can execute different SQL.
His stop condition is concrete. If the same batch produces duplicate order IDs or additional rows, revenue calculated above it can be inflated. A team should explain and fix that difference rather than accepting a faster run as evidence that migration succeeded.
What to watch
A useful adoption test includes a case that passes syntax checks but violates the metric definition, a case whose overall total is correct but whose categories are wrong, and a reference calculation deliberately checked against known inputs.
Record the engine versions, validation mode, source snapshot and test coverage. Measure investigation and review effort as well as model-token spending. For incremental pipelines, retain an explicit comparison of starting state and final keys.
The unanswered business question is whether earlier detection reduces real rework under a team's own workload. DeFaria's interview supplies a method for investigating it. The strongest result a team can seek is a correct, reviewable business number, with enough evidence to explain why it is correct.
Reporting note
This LDS Exclusive is based on six written answers attributed to Elias DeFaria, Director of Product Management at dbt Labs, supplied directly through Method Communications. LDS reviewed dbt's documentation and the linked evaluation report, and reproduced only the interview's small synthetic aggregation example in SQLite. The seeded-fault findings are attributed to the supplied evaluation; LDS did not run its dbt or AI-agent workflow. Practical test suggestions beyond the supplied example are LDS's interpretation.
Key Points #
- 1The supplied two-order example turns $160 into $260 because an item-level join repeats an order-level amount. LDS verified the arithmetic in SQLite.
- 2The seeded-fault evaluation detected 13 semantic cases in three repetitions, but missed faults shared with the reference. Its manual comparison was limited to two total-level queries.
- 3Strict SQL validation, reviewed metric definitions and matched-input migration checks address different risks. Reduced production rework has not yet been measured.
Scoring Rationale #
Original named interview gives practitioners concrete failure examples, a clear evidence boundary and practical checks for adopting AI in their work.
Sources #
Original reporting, with the public references used alongside it.
LDS Exclusive
Reporting based on written answers given directly to Let's Data Science by Elias DeFaria, Director, Product Management, dbt Labs.
Practice interview problems based on real data
1,625 SQL & Python problems across 15 industry datasets — the exact type of data you work with.