Articles
Dataform Interview Questions: SQLX Dependencies, Assertions, and Failed Quality Gates
Answer Dataform interview questions with SQLX dependencies, assertion gates, ref versus resolve, and locally compiled examples of blocked downstream actions.
A Dataform assertion can fail while a downstream table still has no dependency on that assertion. If you answer an interview question with “the quality check runs upstream, so the revenue table is protected,” you have skipped the edge that determines the result.
The useful preparation task is to draw the compiled graph, identify what each SQLX action produces, and explain which failures should stop which consumers. This guide uses one original three-action example to separate a table dependency from a quality gate, then tests the difference through local compilation.
Evidence boundary: Product behavior is grounded in current Google Cloud documentation, checked in October 2026. We did not use candidate reports to infer interview frequency or a hiring process. No BigQuery workflow was executed for this article.
What does SQLX compile into?
Official fact: Dataform lets you develop SQL workflows with dependencies and data-quality assertions for BigQuery. SQLX combines SQL with configuration and templating that Dataform compiles into an action graph and queries. Dataform overview
For an interview, keep three forms of evidence distinct:
| Evidence | What it establishes | What it leaves open |
|---|---|---|
| A successful compilation | The definitions produced a graph and compiled SQL without reported compilation errors. | Whether the warehouse accepts the query and the runtime identity has access. |
| A successful table action | That action completed in the observed workflow execution. | Whether the produced rows satisfy a separate assertion. |
| A passing assertion | Its checked data met its predicate during that execution. | Whether every downstream consumer depends on it or every business risk is covered. |
Hearing “the pipeline passed” can leave you defending a revenue number whose inputs were never approved. Ask which action passed, in which execution, and under which assertion rules. A clean compiler result and a trustworthy revenue number answer different questions.
Official fact: The Dataform CLI supports local compilation, including JSON output. Our fixture uses CLI and core version 3.0.50 as a reproducible baseline, not a claim about the newest available version. Use the Dataform CLI
We inspected the compiled dependency targets without supplying cloud credentials. That proves the edges in this local example. It does not prove permissions, warehouse execution, or an observed scheduler outcome after a failure.
What does ref add that resolve does not?
Official fact: In Dataform's core reference, ref() resolves an action's SQL reference and adds a dependency. resolve() returns the reference without adding that dependency. Dataform core reference
Consider a downstream aggregation:
config { type: "table" }
SELECT SUM(amount) AS revenue
FROM ${ref("orders")}
The SQL reads orders, and the compiled action depends on orders. Replacing ref with resolve can preserve a qualified table name in the SQL while removing the implied execution edge. A reviewer must inspect the configuration for any separately declared dependency before concluding that the overall graph changed.
An explicit dependency can also express a prerequisite that the query does not read. That is exactly what we need when a revenue table must wait for an assertion. The consumer does not need to select rows from the assertion's diagnostic output; it needs the assertion to pass first.
Name both relationships: the data relationship connects the consumer to its input table, while the quality relationship connects the consumer to the check that approves that input. Drawing both prevents a SQL reference from being mistaken for every prerequisite the business requires.
Build a three-action quality-gate exercise
Original exercise: Create orders.sqlx, assert_valid_orders.sqlx, and daily_revenue.sqlx. The sample contains one negative amount and one missing identifier. These are deliberately invalid records under this exercise's rule; a real finance pipeline would need a policy for refunds and adjustments before rejecting every negative value.
-- orders.sqlx
config { type: "table" }
SELECT 1 AS order_id, 10 AS amount
UNION ALL SELECT 2, -4
UNION ALL SELECT CAST(NULL AS INT64), 7
The manual assertion selects violations:
-- assert_valid_orders.sqlx
config { type: "assertion" }
SELECT order_id, amount
FROM ${ref("orders")}
WHERE order_id IS NULL
OR amount IS NULL
OR amount < 0
Official fact: Dataform assertions pass when their query returns zero rows and fail when it returns violating rows. Built-in assertions can check non-null columns, uniqueness, and row conditions; manual assertions let you define a query for the violated rule. Test data quality
The amount IS NULL term is intentional. SQL comparisons involving null do not become true merely because a number is missing. Checking only amount < 0 would leave a gap in this exercise's rule. Naming the invalid state is more precise than assuming a comparison detects it.
Our original predicate returns two rows: (2, -4) and (NULL, 7). The ungated aggregation is still a valid arithmetic operation on the three numeric amounts and produces 13. The plausible total of 13 can reach a consumer even though two input rows violate its rule.
Those outputs were checked with a local SQLite predicate-and-sum oracle over the synthetic values. They are not BigQuery execution logs. The SQLX graph was compiled separately with Dataform, so we can discuss the logical check and dependency structure without pretending to have run a cloud workflow.
Does a failed upstream assertion block daily_revenue?
Official fact: A downstream action does not automatically depend on an upstream table's assertions. To require a selected assertion to pass, add that assertion as a dependency. The documented automatic assertion options can add direct assertion dependencies during compilation. Set dependencies
Our table-only configuration compiles these edges:
orders -> assert_valid_orders
orders -> daily_revenue
Both consumers depend on orders; neither edge makes daily_revenue depend on assert_valid_orders. The revenue action has no quality prerequisite in this graph. Whether a particular workflow invocation schedules or selects that action still depends on its execution settings.
Add the selected gate:
config {
type: "table",
dependencies: ["assert_valid_orders"]
}
SELECT SUM(amount) AS revenue
FROM ${ref("orders")}
Observed local compilation: The downstream dependency targets now contain both orders and assert_valid_orders. Under the documented dependency behavior, a failed prerequisite assertion blocks the dependent action. That is a graph-based prediction here, not an observed BigQuery scheduler result.
An interview answer should identify the missing edge before proposing a repair. “Fix the data” addresses the failing rule. “Add the assertion dependency” addresses the consumer's permission to proceed. Both may be required, but they solve different problems.
Compare explicit gates and automatic assertion dependencies
We compiled five versions of the same original consumer. The input and manual assertion stayed unchanged. Only daily_revenue.sqlx changed; orders.sqlx and assert_valid_orders.sqlx stayed fixed.
| Local fixture variant | Compiled daily_revenue dependency names | Gate present? |
|---|---|---|
Plain ref("orders") | orders | No |
| Explicit selected assertion | assert_valid_orders, orders | Yes |
dependOnDependencyAssertions: true | orders, assert_valid_orders | Yes |
ref({name: "orders", includeDependentAssertions: true}) | orders, assert_valid_orders | Yes |
Global inclusion with this ref explicitly set to false | orders | No |
These are observed dependency names for the fixture, not universal names for built-in assertions. Inspect your compiled targets rather than guessing a generated assertion name from memory.
Documented scope: includeDependentAssertions applies to a selected dependency; dependOnDependencyAssertions applies across dependency actions. The per-dependency setting takes priority. These options concern direct assertions, not every check anywhere upstream. Dependency options
A local failed compilation exposed another useful trap. Declaring orders with assertion inclusion enabled in config.dependencies, then using an ordinary ref("orders") that introduced a conflicting setting, produced a compilation error in our 3.0.50 fixture. The corrected selected-dependency version put the setting directly in the ref object. The local records retain the failed compilation and the corrected dependency targets; they contain no BigQuery execution result.
When discussing automatic gates, describe the intended policy first. Explicit assertion dependencies make a narrow prerequisite visible. Automatic inclusion can express a broader requirement but also adds checks you must understand. Neither option substitutes for inspecting the graph after compilation, especially when a repository already declares the same dependency elsewhere.
Diagnose the failed quality gate without weakening it
Suppose orders completes, assert_valid_orders fails, and the gated revenue action cannot proceed. First inspect the violating rows. For our sample, one row lacks an identifier and another breaks the nonnegative-amount rule. The failure is a data-contract problem, not evidence that the sum expression has a syntax error.
Original repair: Change the missing identifier to 3 and the negative amount to 4. The local predicate then returns zero rows, and the synthetic sum becomes 21. We chose these values to make the fixture easy to inspect; they are not a recommendation to invent identifiers or flip signs in production data.
In a real pipeline, establish the source of truth for each correction. A missing identifier might require a source-system repair or quarantine. A negative amount might be a valid refund under a different contract. Ask who owns the rule, what records it covers, and whether downstream consumers require the same rule.
Do not delete an assertion simply because it blocks a table. Decide whether the rule is wrong, the data is wrong, or the consumer's gate policy is wrong. Each diagnosis leads to a different change and a different regression check.
After a legitimate repair, verify the relevant table and assertion in the actual invocation, then verify the consumer. A failed gate may leave a previously materialized table available to readers; downstream freshness and correctness need explicit verification. Do not call an existing table current merely because the object still exists.
Explain the result as an interview answer
A concise answer to our scenario would be: “The SQL reference gives the revenue action an orders dependency. It does not give it an assert_valid_orders dependency. I would add the selected assertion as a prerequisite, compile the graph to verify both targets, and inspect the assertion's violation rows. Then I would verify the repaired workflow in BigQuery before claiming the new revenue table is current.”
That answer names the graph defect, the data defect, the repair, and the remaining evidence. It also invites useful follow-ups: should every consumer wait for this assertion, should bad records be quarantined, and how will a reader detect a stale table after a blocked run?
Practice drawing the graph before reading a solution. If the diagram cannot explain why a consumer can proceed, more SQL syntax memorization will not resolve the reasoning gap.
Practice quality rules and dependency failures
The task is to translate a general quality or dependency problem into the specific SQLX graph you would inspect.
| Practice question | Dataform reasoning to apply |
|---|---|
| Design Transaction Data Quality Checks | Define violation predicates before attaching consumer gates. |
| Design Data Quality and Observability Pipeline | Separate validation, failure visibility, and freshness checks. |
| Diagnose data quality and pipeline performance issues | Distinguish a failed quality rule from execution or performance problems. |
| Audit flight data quality from metadata | State the rule and its evidence before declaring a dataset usable. |
| Implement Task Dependencies and Failure | Explain which failed prerequisites block each dependent task. |
Start with Design Transaction Data Quality Checks. For this practice question, write one violation query, name its consumer, and draw the assertion-to-consumer edge before considering a cloud run. You can practice that reasoning before connecting a warehouse; cloud execution is the later step that validates runtime behavior.