Prompt and Context
A relational dataset contains customers, accounts, and transfers. The interviewer asks when to use BigQuery Graph versus recursive SQL and how to design a migration and rollback plan that can be verified.
This targets data engineering, analytics engineering, and data platform roles. Assume the data already lives in BigQuery and the graph capability is still in Preview; do not treat Preview as an unconditional production promise. The task is to compare relationship expression, governance, cost, and compatibility rather than claim that one query language is always faster.
What the Interviewer Evaluates
The interviewer wants to see whether you classify the query shape before choosing a graph or relational model, and whether you separate business semantics, execution plans, and platform lifecycle. A strong answer explains how a graph model coexists with existing tables, when recursive SQL is simpler, and how the same benchmark data validates a migration.
Clarifying Questions Before Answering
- Is the traversal fixed-depth, or do depth and path predicates change frequently?
- Must the result return nodes, edges, and paths, or only aggregated metrics?
- Is this batch analytics or a low-latency online traversal?
- Must results match existing SQL reports row for row, and how long is the Preview compatibility window?
- What are the budget, scan volume, concurrency, freshness, and downstream-client constraints?
Thirty-Second Answer Framework
“I first route by query shape and delivery target. I keep SQL for fixed one- or two-hop traversals, simple aggregates, and valuable existing SQL assets; I evaluate BigQuery Graph when multi-hop patterns, path predicates, and relationship reuse are frequent. I define nodes, edges, labels, and keys, then compare GQL and recursive SQL on the same snapshots for results, bytes, latency, and cost. Because Graph is Preview, SQL remains the primary path while Graph runs in shadow mode until the gates pass.”
Step-by-Step Deep Dive
1. Translate the relational question into a graph
Model Person and Account as nodes and Owns and Transfers as directed edges, with time, amount, and stable identifiers on each edge. “Find accounts reachable within three hops and aggregate risk” is naturally a path query; “sum transfer amounts by day” is easier to audit as a relational aggregation. The model should follow the question, not the novelty of the feature.
2. Choose the route from the query shape
For fixed depth, fixed columns, and metric-only output, recursive SQL often wins on readability and existing access controls. For variable depth, reusable path patterns, and results that include nodes and edges, GQL constructs such as GRAPH, MATCH, NEXT, and RETURN express intent closer to the relationship. The decision is about change frequency and maintenance cost, not replacing SQL wholesale.
3. Model and lock semantic boundaries
Define labels, direction, nullable properties, validity time, and duplicate-edge handling. Give each business relationship a key so repeated loads cannot inflate path counts. Define whether a traversal may revisit a node to prevent an unbounded cycle. Graph syntax expresses relationships; dataset permissions, column policies, and audits still protect sensitive fields.
4. Build a reproducible dual-run benchmark
Use representative snapshots and query families: one-hop neighbors, two-hop transfers, time-bounded paths, duplicate edges, and empty results. Project both recursive SQL and GQL into node IDs, edge IDs, path length, and aggregates before comparing sets and counts. Record bytes processed, slot usage, p50/p95 latency, failures, and freshness; one wall-clock sample is not evidence.
snapshot = freeze_partition(as_of)
expected = run_recursive_sql(snapshot, query_family)
candidate = run_gql_graph(snapshot, query_family)
assert canonicalize(expected) == canonicalize(candidate)
gate = error_rate < 0.01 and p95_ms <= budget and cost_per_query <= limit5. Keep Graph and SQL interoperable
When reports still need tabular inputs, project graph results into a table and join them with SQL aggregates or dimensions. When both paths use the same relationships, avoid copying nodes and edges. Google’s documentation describes combining graph queries with SQL through GRAPH_TABLE, so migration can be split by query family instead of rewriting every pipeline.
6. Price Preview risk and rollback
Record Preview regions, versions, quotas, and support boundaries separately. Keep SQL as the primary path and run Graph in shadow mode. Enable it by query family only after result parity, budget, permissions, and monitoring gates pass. Schema drift, result differences, quota failures, or cost anomalies should route back to SQL while preserving snapshots, query text, and execution metrics.
High-Quality Sample Answer
I would start with query shape. Reports with fixed depth, fixed columns, and simple aggregates stay on recursive SQL to limit Preview dependence and migration cost. I would evaluate BigQuery Graph for exploratory analysis with variable depth, reusable path patterns, and node-and-edge output. I would model customers and accounts as nodes, ownership and transfers as directed edges, and lock keys, direction, validity time, and cycle rules. Migration would dual-run both paths on the same snapshot, canonicalize outputs to the same nodes, edges, path lengths, and metrics, then compare results, bytes, p95, failures, and cost. SQL remains primary while Graph runs in shadow mode. A Preview region, quota, or version change—or any failed gate—routes back to SQL. That covers expression, governance, cost, and rollback instead of simply asserting that graph queries are faster.
Common Mistakes
- Switching to a graph after seeing several JOINs → fixed-depth aggregation may not need a graph → route by query shape first.
- Comparing latency for one query → cache, snapshot, and skew create accidental results → use query families and fixed snapshots.
- Ignoring duplicate edges and cycles → path counts inflate or traversal fails to terminate → define keys, visit rules, and maximum depth.
- Treating Graph Preview as a stable dependency → regions, quotas, and semantics can change → keep SQL primary with shadow execution and gates.
- Copying a second graph dataset during migration → freshness and governance diverge → reuse the source and project results.
Follow-up Questions and Responses
What changes if depth grows from two hops to arbitrary depth?
The decision can change. Arbitrary depth and path predicates raise recursive SQL maintenance and resource risks, so Graph’s path expression becomes more attractive. Still set a maximum depth, node-visit limit, and cost gate; never accept unbounded traversal.
What if the business requires a one-second online result?
First check whether batch BigQuery analytics meets the latency target. If not, retain an online graph store or precomputed index and use BigQuery Graph for batch validation and history. Do not sacrifice the online SLO for language uniformity.
How do you prove GQL and recursive SQL are equivalent?
Freeze the same partition snapshot, run a query family covering empty results, duplicate edges, time boundaries, and cycles, then canonicalize stable keys and aggregates before comparison. Preserve the smallest counterexample, query version, and data snapshot for every difference.
When can the SQL fallback be removed?
Only after Preview status, regions, and client compatibility are stable and several data cycles pass result, cost, latency, permission, and failure-drill gates with data-owner approval. Otherwise keep SQL.
How should permissions work when graph relationships are sensitive?
Reuse dataset, column, and row policies, then re-check visible fields at the graph-result projection. Reachability of a node or edge can itself reveal a relationship, so path exposure belongs in the audit cases.