Your BigQuery Totals Survived UNNEST. Did Every Parent?

A matching aggregate is not enough to accept a nested-data transformation.

The dangerous case is simple: the source contains four orders, two of them have explicitly empty repeated fields, and the three genuine line records sum to 125. A CROSS JOIN UNNEST can still return exactly 125 while silently reducing the parent population from four orders to two. Google documents that correlated INNER JOIN or CROSS JOIN expansion operates on the rows produced by UNNEST, and that flattening with an inner join excludes rows whose arrays are empty; preserving those source rows requires a left join. Google Cloud’s BigQuery array documentation states that behavior directly.

The release question is therefore not “Did the total survive?” It is: Did every required parent survive the actual expansion path, while the real child population, child multiplicity and measures remained correct?

This playbook answers that question with one synthetic fixture, one owned BigQuery project, one location, session/script-local temporary tables and a frozen GoogleSQL multi-statement script. It deliberately compares a losing CROSS JOIN UNNEST, a preserving LEFT JOIN UNNEST, placeholder-sensitive counts and an amount >= 30 predicate placed first in WHERE and then in ON.

One limitation must remain explicit: no BigQuery product laboratory was executed while preparing this article. The numerical outputs below are therefore arithmetic expectations, not manufactured job observations. The SQL is designed so an operator can turn those expectations into execution evidence. Until real job IDs and passing assertion results from the frozen revision exist, the evidence-based release decision is HOLD, not ACCEPT.

Define completeness at the parent and child grains

Start by defining the contract at the business grains, not at the row count produced by a SQL operator.

In this fixture, an order is the parent. A line is the child. A row emitted by an expanded query is merely an expanded row; it is not automatically a genuine child. That distinction becomes critical under a left expansion because a parent whose repeated field has no elements can still produce a preserved row with a null-valued right side.

The independent fixture contract is:

The keep-all-parents contract is explicit: orders with zero children remain part of the output parent population and therefore remain eligible for parent-level counts, denominators and audit reconciliation. The transformation is accepted only if that population survives.

This is narrower than the broader dbt and Snowflake analytics stack. The problem here is not stack selection, orchestration, ingestion or semantic-layer design. It is one representation boundary: converting a repeated child field into rows without changing the declared parent population.

The first false green follows immediately from the fixture. The genuine child amounts are 40, 60 and 25. Their sum is:

40 + 60 + 25 = 125.

Removing order-2 and order-4 from the flattened result does not remove any amount, because those parents have no child amounts to contribute. A query can consequently report the correct sum while failing the parent contract by 50%.

That is why acceptance requires at least three independently checked dimensions: parent identities, genuine-child identities and measures. A single aggregate cannot substitute for them.

Freeze a small GoogleSQL sandbox and its evidence

BigQuery is a managed service, so the evidence manifest should not invent an installable “BigQuery server version.” A moving documentation page is also not a runtime lock. What can be frozen is the SQL text and revision, the synthetic source revision, the execution project and location, client version, job IDs and query settings.

Google documents multi-statement queries as sequences of SQL statements with shared state and supports temporary tables inside them. Temporary tables are managed by BigQuery, do not need a user dataset, can be referenced for the duration of the multi-statement query, and can be qualified with _SESSION. Google Cloud’s multi-statement-query documentation also notes that child jobs are created for statements inside a script.

For this laboratory, freeze the following before execution:

SET @@location = 'US' can freeze the script location because BigQuery exposes @@location as a system variable and requires that assignment to be the first statement when it is set. BigQuery also exposes @@project_id, @@script.job_id, @@script.creation_time and script resource counters for runtime evidence. The BigQuery system-variable reference documents these fields.

Cost control belongs in the invocation as well as in the small fixture. Google recommends estimating and controlling query costs and documents maximum bytes billed as a mechanism that makes an on-demand query fail when its pre-execution estimate exceeds the configured limit. BigQuery’s cost-control guidance also explains dry runs, although dry runs do not support CREATE TEMP TABLE statements in a multi-statement script.

The runner therefore uses a deliberately tiny byte cap rather than relying on a claimed free allowance:

# Run only in an owned disposable BigQuery project.
export PROJECT_ID="REPLACE_WITH_OWNED_SANDBOX_PROJECT"
export BQ_LOCATION="US"
export SQL_REV="bq-unnest-parent-v1.0.0"

# Evidence: record, do not paraphrase, the installed client version.
bq version

# Use the same frozen SQL file for both runs.
# Give each execution an explicit job ID so provenance is not ambiguous.
RUN_UTC="$(date -u +%Y%m%dT%H%M%SZ)"
NEG_JOB_ID="unnest_negative_${RUN_UTC}"
ACCEPT_JOB_ID="unnest_accept_${RUN_UTC}"

bq \
  --project_id="${PROJECT_ID}" \
  --location="${BQ_LOCATION}" \
  query \
  --job_id="${NEG_JOB_ID}" \
  --use_legacy_sql=false \
  --use_cache=false \
  --maximum_bytes_billed=10000000 \
  --parameter="gate_mode::negative_cross" \
  < bigquery_unnest_parent_acceptance.sql

# The preceding run is EXPECTED TO FAIL an ASSERT.
# Do not treat a shell wrapper that suppresses its non-zero exit as success.

bq \
  --project_id="${PROJECT_ID}" \
  --location="${BQ_LOCATION}" \
  query \
  --job_id="${ACCEPT_JOB_ID}" \
  --use_legacy_sql=false \
  --use_cache=false \
  --maximum_bytes_billed=10000000 \
  --parameter="gate_mode::accept_corrected" \
  < bigquery_unnest_parent_acceptance.sql

The bq reference documents --job_id, --location, --maximum_bytes_billed, --use_cache=false and bq version; those are client and job controls, not a BigQuery engine-version pin.

Write the expected parent and child ledgers first

Acceptance becomes circular if the expected population is generated from the same UNNEST expression being tested. The expected ledgers therefore use independent literals.

The source fixture and expected ledgers can be created at the start of the frozen script as follows:

SET @@location = 'US';

DECLARE gate_mode STRING DEFAULT @gate_mode;
DECLARE sql_revision STRING DEFAULT 'bq-unnest-parent-v1.0.0';
DECLARE source_revision STRING DEFAULT 'fixture-orders-v1.0.0';
DECLARE execution_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP();

SET @@query_label = 'refonte_unnest_acceptance';

CREATE TEMP TABLE SESSION.sourceorders (
  order_id STRING,
  lines ARRAY<STRUCT<child_id STRING, amount INT64>>
);

INSERT INTO SESSION.sourceorders (order_id, lines)
VALUES
  (
    'order-1',
    [
      STRUCT('line-1' AS child_id, 40 AS amount),
      STRUCT('line-2' AS child_id, 60 AS amount)
    ]
  ),
  (
    'order-2',
    ARRAY<STRUCT<child_id STRING, amount INT64>>[]
  ),
  (
    'order-3',
    [
      STRUCT('line-3' AS child_id, 25 AS amount)
    ]
  ),
  (
    'order-4',
    ARRAY<STRUCT<child_id STRING, amount INT64>>[]
  );

-- Independently authored parent ledger: do not derive from source_orders.
CREATE TEMP TABLE SESSION.expectedparents AS
SELECT order_id
FROM UNNEST([
  'order-1',
  'order-2',
  'order-3',
  'order-4'
]) AS order_id;

-- Independently authored child ledger: do not derive from source_orders.
CREATE TEMP TABLE SESSION.expectedchildren AS
SELECT
FROM UNNEST([
  STRUCT('order-1' AS order_id, 'line-1' AS child_id, 40 AS amount),
  STRUCT('order-1' AS order_id, 'line-2' AS child_id, 60 AS amount),
  STRUCT('order-3' AS order_id, 'line-3' AS child_id, 25 AS amount)
]);

-- Independently authored expectation for amount >= 30.
CREATE TEMP TABLE SESSION.expectedfiltered_children AS
SELECT
FROM UNNEST([
  STRUCT('order-1' AS order_id, 'line-1' AS child_id, 40 AS amount),
  STRUCT('order-1' AS order_id, 'line-2' AS child_id, 60 AS amount)
]);

CREATE TEMP TABLE SESSION.runmanifest AS
SELECT
  sql_revision AS sql_revision,
  source_revision AS source_revision,
  execution_utc AS execution_utc,
  @@project_id AS project_id,
  @@location AS location,
  @@script.job_id AS script_job_id,
  gate_mode AS gate_mode,
  'refonte_unnest_acceptance' AS query_label,
  FALSE AS query_cache_enabled,
  10000000 AS maximum_bytes_billed_declared;

Before testing flattening, validate the source contract itself. Hiding malformed rows with DISTINCT, COALESCE or an early aggregation would weaken the experiment.

ASSERT (
  SELECT COUNT(*) = 4
  FROM SESSION.expectedparents
) AS 'Expected parent ledger must contain exactly four rows.';

ASSERT (
  SELECT COUNT(*) > 0
  FROM SESSION.expectedparents
) AS 'Expected parent ledger must not be empty.';

ASSERT (
  SELECT COUNT(*) = COUNT(DISTINCT order_id)
  FROM SESSION.expectedparents
) AS 'Expected parent IDs must be unique.';

ASSERT (
  SELECT COUNT(*) = COUNT(DISTINCT order_id)
  FROM SESSION.sourceorders
) AS 'Source parent IDs must be unique.';

ASSERT (
  SELECT COUNTIF(order_id IS NULL) = 0
  FROM SESSION.sourceorders
) AS 'Source parent IDs must be non-null.';

ASSERT (
  SELECT COUNTIF(line.child_id IS NULL OR line.amount IS NULL) = 0
  FROM SESSION.sourceorders AS s
  CROSS JOIN UNNEST(s.lines) AS line
) AS 'Genuine source children require non-null child_id and amount.';

ASSERT (
  SELECT COUNT(*) = COUNT(DISTINCT line.child_id)
  FROM SESSION.sourceorders AS s
  CROSS JOIN UNNEST(s.lines) AS line
) AS 'Child IDs must be unique within this fixture scope.';

ASSERT (
  SELECT COUNT(*) = 3
  FROM SESSION.expectedchildren
) AS 'Expected child ledger must contain exactly three children.';

ASSERT (
  SELECT COALESCE(SUM(amount), 0) = 125
  FROM SESSION.expectedchildren
) AS 'Expected child ledger amount must equal 125.';

ASSERT NOT EXISTS (
  SELECT order_id
  FROM SESSION.expectedchildren
  EXCEPT DISTINCT
  SELECT order_id
  FROM SESSION.expectedparents
) AS 'Every expected child parent must exist in the expected parent ledger.';

Those checks are transformation-level controls that complement, rather than duplicate, broader warehouse quality and management foundations.

Separate an empty array from an invalid source row

The baseline fixture contains two intentionally typed empty arrays. It does not test nullable-array persistence, missing JSON properties, nullable child identities or malformed amounts.

That boundary matters. An empty array here means “this parent has zero child elements under the declared source contract.” It does not mean “unknown children,” “source extraction failed” or “the field was absent.”

Accordingly, the script does not turn bad child data into a seemingly valid zero-child case. A null child_id or amount fails the source gate. A duplicate child ID fails the uniqueness gate. Later schema variants should get a new fixture and a new acceptance contract rather than silently inheriting the conclusions from this one.

Reproduce the INNER or CROSS expansion that loses parents

Now create the negative-control transformation.

Google’s array documentation explains that for a correlated UNNEST, each source row’s array is expanded and a correlated INNER JOIN or CROSS JOIN combines the resulting elements with that source row. It also states that flattening with an inner join excludes source rows whose arrays are empty. Work with arrays in BigQuery is the primary behavior reference for this case.

CREATE TEMP TABLE SESSION.casecross AS
SELECT
  s.order_id,
  line.child_id,
  line.amount,
  array_offset,
  ARRAY_LENGTH(s.lines) AS source_child_count
FROM SESSION.sourceorders AS s
CROSS JOIN UNNEST(s.lines) AS line
WITH OFFSET AS array_offset;

Before execution, the arithmetic expectation is deterministic:

The three expected rows are:

order-1  line-1  40  offset 0
order-1  line-2  60  offset 1
order-3  line-3  25  offset 0

order-2 and order-4 produce no array elements, so there is no right-side row for the cross expansion to emit.

Make the loss visible rather than inferring it from counts:

SELECT order_id AS missing_parent_id
FROM SESSION.expectedparents
EXCEPT DISTINCT
SELECT order_id
FROM SESSION.casecross
ORDER BY missing_parent_id;

Arithmetic expectation:

order-2
order-4

The corresponding summary query is:

SELECT
  COUNT(*) AS expanded_row_count,
  COUNT(DISTINCT order_id) AS distinct_parent_count,
  COUNT(child_id) AS real_child_count,
  COALESCE(SUM(amount), 0) AS amount
FROM SESSION.casecross;

Its expected result is 3, 2, 3, 125.

These are still predictions until an actual BigQuery job produces them. The negative execution matters precisely because it demonstrates that the acceptance gate can detect a query whose amount looks correct.

Why the aggregate gives a false green

The amount remains 125 because neither zero-child parent contributes a child amount. Their disappearance is invisible to SUM(amount).

That can still be materially wrong. If the flattened relation feeds an order denominator, an eligibility population, a completeness report or a reconciliation by parent ID, losing two orders changes the represented business population even though every genuine line and every dollar-like fixture amount survives.

The conclusion is not that CROSS JOIN UNNEST is universally incorrect. It is appropriate when the intended population is “one output row per actual array element” and parents with zero elements should not be represented.

It fails this contract because this contract says all four parent identities must survive.

That distinction is what turns syntax review into acceptance engineering: the join form is judged against the required population, not against a generic preference for one SQL pattern.

Use LEFT JOIN UNNEST to preserve the declared parents

For the keep-all-parents contract, compare the cross expansion with a correlated left expansion.

BigQuery’s GoogleSQL query-syntax documentation describes the relevant behavior directly: when the right side of a correlated LEFT JOIN is an UNNEST whose array produces no rows, BigQuery generates a row with null values for the right input and joins it to the left row.

Create the preserving case:

CREATE TEMP TABLE SESSION.caseleft AS
SELECT
  s.order_id,
  line.child_id,
  line.amount,
  array_offset,
  ARRAY_LENGTH(s.lines) AS source_child_count
FROM SESSION.sourceorders AS s
LEFT JOIN UNNEST(s.lines) AS line
WITH OFFSET AS array_offset
ON TRUE;

The expected expanded population is:

The physical row count rises to five, but the business populations are still four parents and three genuine children.

SELECT
  COUNT(*) AS expanded_row_count,
  COUNT(DISTINCT order_id) AS distinct_parent_count,
  COUNT(child_id) AS real_child_count,
  COUNT(array_offset) AS real_offset_count,
  COALESCE(SUM(amount), 0) AS amount
FROM SESSION.caseleft;

Expected result:

This is the core answer to the acceptance question: under the corrected left-expansion path, every required parent is expected to survive while all three real children and the amount 125 remain intact. But “expected” should not be silently upgraded to “observed.” Actual acceptance waits for the reviewed SQL revision to run and for its evidence to be recorded.

Do not count placeholder rows as children

The most immediate trap after fixing parent preservation is COUNT(*).

Google documents that COUNT(*) counts input rows, whereas COUNT(expression) counts rows where the expression evaluates to a non-null value. The GoogleSQL aggregate-function reference therefore supplies the semantic basis for counting child_id rather than physical rows under this fixture contract.

For case_left:

SELECT
  COUNT(*) AS expanded_rows,
  COUNT(child_id) AS genuine_children,
  COUNT(array_offset) AS genuine_children_by_offset,
  COUNTIF(child_id IS NOT NULL) AS genuine_children_by_predicate
FROM SESSION.caseleft;

Expected values are 5, 3, 3, 3.

COUNT(*) = 5 is not wrong as a count of expanded rows. It is wrong when labelled “number of children.”

COUNT(array_offset) is also valid in this fixture because each real element receives a non-null offset and a placeholder does not. Offset zero is a genuine offset. Never write a truthiness-style condition that accidentally interprets zero as absence; test nullability explicitly.

COUNT(child_id) is safe here because the fixture contract requires every genuine child identity to be non-null. If a future source contract permits nullable child IDs, that assumption disappears. The reliable presence indicator would then need to change, potentially to the offset or another explicit element-presence construction. That future schema is intentionally outside this fixture.

Place the child filter where the business contract requires it

Filtering children after a left expansion introduces a second preservation boundary.

The business condition for this case is amount >= 30. The expected matching child set is independently declared as line-1 = 40 and line-2 = 60, for a matching amount of 100.

First test the predicate in WHERE:

CREATE TEMP TABLE SESSION.casefilter_where AS
SELECT
  s.order_id,
  line.child_id,
  line.amount,
  array_offset,
  ARRAY_LENGTH(s.lines) AS source_child_count
FROM SESSION.sourceorders AS s
LEFT JOIN UNNEST(s.lines) AS line
WITH OFFSET AS array_offset
ON TRUE
WHERE line.amount >= 30;

The placeholder rows for order-2 and order-4 have line.amount = NULL; NULL >= 30 does not satisfy the WHERE predicate. order-3 has a genuine child, but its amount is 25, which also fails. The expected result therefore contains only the two qualifying children from order-1.

Expected summary:

Now place the child eligibility condition in the correlated left join:

CREATE TEMP TABLE SESSION.casefilter_on AS
SELECT
  s.order_id,
  line.child_id,
  line.amount,
  array_offset,
  ARRAY_LENGTH(s.lines) AS source_child_count
FROM SESSION.sourceorders AS s
LEFT JOIN UNNEST(s.lines) AS line
WITH OFFSET AS array_offset
ON line.amount >= 30;

For the preserve-all-parents contract, this form changes which children qualify on the right side without removing unmatched left-side parents. That follows the documented left-join rule that unmatched left rows are retained with nulls for the right input.

The expected result is:

That means five expanded rows, four required parents, two matching children and amount 100.

Neither query is universally preferable. Their populations differ. The WHERE form expresses “return only expanded rows having a qualifying child.” The ON form expresses “retain every parent and attach only qualifying children.” Acceptance must test the form required by the business contract.

Distinguish no children from no matching children

After filtering in ON, order-2 and order-3 both have null child fields, but they arrived there for different reasons.

order-2 started with zero children.

order-3 started with one child, line-3 = 25, but that child did not satisfy amount >= 30.

A filtered left result by itself should not be used to reconstruct that distinction. The script carries:

ARRAY_LENGTH(s.lines) AS source_child_count

so the output retains enough source-side evidence for this tiny fixture:

order-2 -> source_child_count = 0 -> no original children
order-3 -> source_child_count = 1 -> children existed, none matched

That distinction can matter when downstream consumers interpret “no eligible line” differently from “order had no lines.” The safest pattern is to keep the source-side count or an independently reconciled source ledger whenever that difference is business-significant.

Reconcile identities before reconciling measures

A reliable gate starts with identity reconciliation.

For each accepted variant, calculate missing and unexpected parents explicitly:

CREATE TEMP TABLE SESSION.leftmissing_parents AS
SELECT order_id
FROM SESSION.expectedparents
EXCEPT DISTINCT
SELECT order_id
FROM SESSION.caseleft;

CREATE TEMP TABLE SESSION.leftunexpected_parents AS
SELECT DISTINCT order_id
FROM SESSION.caseleft
EXCEPT DISTINCT
SELECT order_id
FROM SESSION.expectedparents;

CREATE TEMP TABLE SESSION.leftmissing_children AS
SELECT order_id, child_id
FROM SESSION.expectedchildren
EXCEPT DISTINCT
SELECT order_id, child_id
FROM SESSION.caseleft
WHERE child_id IS NOT NULL;

CREATE TEMP TABLE SESSION.leftunexpected_children AS
SELECT order_id, child_id
FROM SESSION.caseleft
WHERE child_id IS NOT NULL
EXCEPT DISTINCT
SELECT order_id, child_id
FROM SESSION.expectedchildren;

Those set checks answer “which identities are absent or unexpected?” They do not prove multiplicity.

Suppose line-1 were accidentally duplicated. EXCEPT DISTINCT would still see line-1 in both sets and could report no difference. Add a grouped multiplicity comparison:

CREATE TEMP TABLE SESSION.expectedchild_multiplicity AS
SELECT
  order_id,
  child_id,
  COUNT(*) AS expected_count
FROM SESSION.expectedchildren
GROUP BY order_id, child_id;

CREATE TEMP TABLE SESSION.actualchild_multiplicity AS
SELECT
  order_id,
  child_id,
  COUNT(*) AS actual_count
FROM SESSION.caseleft
WHERE child_id IS NOT NULL
GROUP BY order_id, child_id;

CREATE TEMP TABLE SESSION.childmultiplicity_diff AS
SELECT
  COALESCE(e.order_id, a.order_id) AS order_id,
  COALESCE(e.child_id, a.child_id) AS child_id,
  COALESCE(e.expected_count, 0) AS expected_count,
  COALESCE(a.actual_count, 0) AS actual_count
FROM SESSION.expectedchild_multiplicity AS e
FULL OUTER JOIN SESSION.actualchild_multiplicity AS a
  USING (order_id, child_id)
WHERE COALESCE(e.expected_count, 0) != COALESCE(a.actual_count, 0);

Only after identities and multiplicities reconcile should the measure become an acceptance condition:

SELECT
  (SELECT COALESCE(SUM(amount), 0)
   FROM SESSION.expectedchildren) AS expected_amount,
  (SELECT COALESCE(SUM(amount), 0)
   FROM SESSION.caseleft
   WHERE child_id IS NOT NULL) AS actual_amount;

The acceptance invariant is conjunctive:

exact parent coverage AND exact child identities AND exact child multiplicities AND expected measures.

The sum of 125 is one term in that expression, not the expression itself.

For the filtered-ON variant, repeat the same model against all four expected parents and the independently authored two-row expected_filtered_children ledger. Its expected amount is 100.

Turn the reconciliation into failing SQL assertions

BigQuery provides an executable ASSERT statement for GoogleSQL scripts. The GoogleSQL debugging-statement reference states that an assertion succeeds only when its Boolean expression evaluates to TRUE; FALSE or NULL generates an error.

That matters because a quality check must not accidentally become a successful null or empty comparison.

The current GoogleSQL debugging documentation footer observed during this research shows an update date of September 22, 2026; the earlier commissioning evidence recorded a different documentation-update date. That footer is a living-documentation marker, not a BigQuery runtime version or a feature-launch date.

Add the final acceptance gates:

-- Required expected state must exist.
ASSERT (
  SELECT COUNT(*) = 4
  FROM SESSION.expectedparents
) AS 'ACCEPTANCE: expected parent ledger must contain four parents.';

ASSERT (
  SELECT COUNT(*) = 3
  FROM SESSION.expectedchildren
) AS 'ACCEPTANCE: expected child ledger must contain three children.';

-- Correct unfiltered LEFT expansion.
ASSERT NOT EXISTS (
  SELECT order_id
  FROM SESSION.expectedparents
  EXCEPT DISTINCT
  SELECT order_id
  FROM SESSION.caseleft
) AS 'ACCEPTANCE: corrected LEFT expansion is missing required parents.';

ASSERT NOT EXISTS (
  SELECT DISTINCT order_id
  FROM SESSION.caseleft
  EXCEPT DISTINCT
  SELECT order_id
  FROM SESSION.expectedparents
) AS 'ACCEPTANCE: corrected LEFT expansion contains unexpected parents.';

ASSERT NOT EXISTS (
  SELECT order_id, child_id
  FROM SESSION.expectedchildren
  EXCEPT DISTINCT
  SELECT order_id, child_id
  FROM SESSION.caseleft
  WHERE child_id IS NOT NULL
) AS 'ACCEPTANCE: corrected LEFT expansion is missing expected children.';

ASSERT NOT EXISTS (
  SELECT order_id, child_id
  FROM SESSION.caseleft
  WHERE child_id IS NOT NULL
  EXCEPT DISTINCT
  SELECT order_id, child_id
  FROM SESSION.expectedchildren
) AS 'ACCEPTANCE: corrected LEFT expansion contains unexpected children.';

ASSERT (
  SELECT COUNT(*) = 0
  FROM SESSION.childmultiplicity_diff
) AS 'ACCEPTANCE: child multiplicity differs from expected.';

ASSERT (
  SELECT COUNT(DISTINCT order_id) = 4
  FROM SESSION.caseleft
) AS 'ACCEPTANCE: corrected LEFT expansion must retain four parents.';

ASSERT (
  SELECT COUNT(child_id) = 3
  FROM SESSION.caseleft
) AS 'ACCEPTANCE: corrected LEFT expansion must contain three genuine children.';

ASSERT (
  SELECT COALESCE(SUM(amount), 0) = 125
  FROM SESSION.caseleft
  WHERE child_id IS NOT NULL
) AS 'ACCEPTANCE: corrected LEFT expansion amount must equal 125.';

-- Correct filtered LEFT expansion: preserve all parents, retain two matches.
ASSERT NOT EXISTS (
  SELECT order_id
  FROM SESSION.expectedparents
  EXCEPT DISTINCT
  SELECT order_id
  FROM SESSION.casefilter_on
) AS 'FILTER ACCEPTANCE: ON-filtered expansion lost required parents.';

ASSERT (
  SELECT COUNT(DISTINCT order_id) = 4
  FROM SESSION.casefilter_on
) AS 'FILTER ACCEPTANCE: ON-filtered expansion must retain four parents.';

ASSERT (
  SELECT COUNT(child_id) = 2
  FROM SESSION.casefilter_on
) AS 'FILTER ACCEPTANCE: ON-filtered expansion must contain two matching children.';

ASSERT (
  SELECT COALESCE(SUM(amount), 0) = 100
  FROM SESSION.casefilter_on
  WHERE child_id IS NOT NULL
) AS 'FILTER ACCEPTANCE: ON-filtered matching amount must equal 100.';

ASSERT NOT EXISTS (
  SELECT order_id, child_id
  FROM SESSION.expectedfiltered_children
  EXCEPT DISTINCT
  SELECT order_id, child_id
  FROM SESSION.casefilter_on
  WHERE child_id IS NOT NULL
) AS 'FILTER ACCEPTANCE: expected qualifying child is missing.';

Now prove that the gate can fail. The same frozen script uses gate_mode to run a negative control against the deliberately faulty cross expansion:

IF gate_mode = 'negative_cross' THEN

  -- This must fail: case_cross has only order-1 and order-3.
  ASSERT NOT EXISTS (
    SELECT order_id
    FROM SESSION.expectedparents
    EXCEPT DISTINCT
    SELECT order_id
    FROM SESSION.casecross
  ) AS 'NEGATIVE CONTROL: CROSS expansion lost required parents.';

END IF;

The negative run should terminate with an assertion error. That failed job is useful evidence: it demonstrates that parent loss is detectable.

The deliberately wrong placeholder-sensitive acceptance attempt should remain visible but disabled:

-- REJECTED TEST DESIGN. Do not use this as a child-count acceptance gate:
--
-- ASSERT (
--   SELECT COUNT(*) = 3
--   FROM SESSION.caseleft
-- ) AS 'Wrong: COUNT(*) treats preserved placeholder rows as children.';
--
-- Expected COUNT(*) for case_left is 5, while genuine child count is 3.

Do not wrap the negative assertion in exception handling that converts its failure into overall success. A swallowed assertion is not a successful negative control.

Capture job evidence and intermediate populations

A test result without execution provenance is weaker than it looks.

BigQuery exposes project-level job metadata through INFORMATION_SCHEMA.JOBS; documented fields include job_id, parent_job_id, statement_type, priority, cache_hit, total_bytes_processed, total_bytes_billed, state and errors. For multi-statement queries, child jobs identify the parent script through parent_job_id. BigQuery’s JOBS view documentation also warns that parent-script and child-job summary values can otherwise lead to double-counting in some cost analyses.

After each execution, retain a compact evidence record:

The actual post-run query can inspect jobs using the same project and US location:

SELECT
  project_id,
  job_id,
  parent_job_id,
  creation_time,
  start_time,
  end_time,
  statement_type,
  priority,
  cache_hit,
  total_bytes_processed,
  total_bytes_billed,
  total_slot_ms,
  state,
  error_result.message AS error_message
FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id IN (
  'REPLACE_WITH_NEGATIVE_JOB_ID',
  'REPLACE_WITH_ACCEPT_JOB_ID'
)
   OR parent_job_id IN (
  'REPLACE_WITH_NEGATIVE_JOB_ID',
  'REPLACE_WITH_ACCEPT_JOB_ID'
)
ORDER BY creation_time, job_id;

Google requires the INFORMATION_SCHEMA region qualifier to match the query execution location, which is another reason not to drift between locations while collecting evidence.

For this tiny fixture, preserve the intermediate rows rather than retaining only summaries. There is no analytical advantage in hiding five-row evidence behind aggregation.

This job-level discipline fits within broader cloud-native pipeline responsibilities, but the evidence here is intentionally local to the transformation under review.

Mark observation separately from predicted output

Use three labels consistently.

Documented behavior is a claim supported by BigQuery documentation: for example, inner expansion excludes empty arrays, while a correlated left join can preserve the left row with a null right side.

Arithmetic expectation is calculated from the authored fixture: for example, three cross-expanded rows, five left-expanded rows and amount 125.

Actual job observation is what a specific BigQuery job emitted under a recorded project, location, SQL revision, source revision and settings.

The execution ledger should therefore look like this before the lab is run:

Those blank observations are not an editorial defect. Filling them without a product run would be fabricated evidence.

Recover an already published incomplete transformation

Suppose a production-like output was already published from the faulty cross-expansion path.

Do not infer that missing parents can be recovered from the flattened output. order-2 and order-4 are absent because they emitted no cross-expanded rows. Once the representation has collapsed to the child-bearing rows alone, nothing in that result independently proves which zero-child parents should have existed.

Recovery therefore starts upstream.

Retain or recover the parent-grain source containing the required identities. Freeze the defective SQL revision, the corrected SQL revision and the source revision used for the rebuild. Then build an isolated corrected candidate using the left-expansion logic.

The recovery sequence is:

1.      Identify the affected transformation revision. Record the exact query text or immutable commit/revision, destination scope and corresponding jobs.

2.      Confirm recoverable parent evidence. The source must still identify order-1 through order-4; a child-only output is insufficient.

3.      Repair the transformation query. Change the expansion path to the reviewed LEFT JOIN UNNEST form when the contract is preserve-all-parents.

4.      Build an isolated candidate. Do not overwrite the trusted output before reconciliation.

5.      Run the independent ledgers and assertions against the candidate.

6.      Promote only after parent identities, child multiplicities and measures reconcile.

A measure-only repair is specifically prohibited by the acceptance model. If somebody notices that the defective output already sums to 125 and concludes that there is “nothing to fix,” they are confusing a preserved measure with a preserved population.

Likewise, replaying the same cross-expansion query does not recover the absent parents. A rerun changes time, not semantics.

The key recovery asset is the retained parent source, because the missing identities cannot be reliably synthesized from a collapsed child-only result.

Promote the corrected query without changing the contract silently

Moving from cross expansion to left expansion changes the physical shape of the output.

In this fixture, the corrected result has five expanded rows instead of three even though the genuine child population remains exactly three. That is an expected consequence of preserving the two zero-child parents, not evidence that two children were created.

Before promotion, review the output schema and the meaning of nullable child-side fields. order_id originates on the preserved parent side. child_id, amount and array_offset can be null on the placeholder rows because there is no corresponding child.

Downstream consumers must therefore answer a concrete question: did any existing calculation treat every physical row as a child?

A downstream:

COUNT(*)

over the new flattened output changes from three to five.

A downstream:

COUNT(child_id)

remains three under the fixture’s non-null-child-ID contract.

That semantic review should happen before promotion, not after a dashboard changes unexpectedly.

Keep the prior query revision and prior published output available for controlled rollback while the candidate is being reconciled. Rollback here means restoring a known representation while the defect is investigated; it does not convert the old cross-expansion result into a correct preserve-all-parents output.

Likewise, cloud warehouse architecture as a separate decision should remain separate from this release. The acceptance question does not require re-platforming, warehouse redesign or a new orchestration architecture.

The release contract should state, in plain language:

Every required parent order appears at least once in the flattened relation. A parent with zero children appears once with no genuine child. Genuine children retain their identities and multiplicities. Child measures are reconciled only over genuine children.

That wording prevents a later maintainer from “simplifying” the left expansion back to a cross expansion without realizing they changed the parent population.

Decide accept, repair-query, rebuild or hold

The final decision should follow evidence, not intuition.

For the article as written, the current evidence decision is HOLD. The fixture and expected results are fully specified, but the “actual observation” cells remain intentionally empty because no BigQuery execution was performed for this research.

After execution, the expected decision path is straightforward.

The negative_cross job should fail its parent-preservation assertion because order-2 and order-4 are missing. If it unexpectedly passes, hold immediately: either the SQL revision, fixture or gate does not match what was reviewed.

The accept_corrected job should pass only if the independently authored ledgers agree with case_left and case_filter_on.

A release gate therefore requires all of the following evidence together:

•         nonempty, independently defined expected parent and child ledgers;

•         successful source-validity assertions;

•         a confirmed failing negative control;

•         exact parent-set reconciliation;

•         exact genuine-child identity and multiplicity reconciliation;

•         expected unfiltered and filtered measures;

•        completed parent-script job with its project, location and UTC;

•        exact SQL and source revisions;

•        actual client version and relevant query settings.

The release must not infer success from absence of output. An empty test result is meaningful only when the test was explicitly designed so emptiness means “no differences.” The expected-ledger nonemptiness assertions prevent an omitted expectation from generating a vacuous green comparison.

Likewise, a wrapper must not convert the negative job's expected assertion error into a generic zero exit and then call that a passing control.

Set a release gate that cannot pass by omission

The frozen script can finish with evidence-producing result sets after the positive gates:

IF gate_mode = 'accept_corrected' THEN

  SELECT
    'LEFT_UNFILTERED' AS query_variant,
    COUNT(*) AS expanded_row_count,
    COUNT(DISTINCT order_id) AS parent_count,
    COUNT(child_id) AS real_child_count,
    COALESCE(SUM(amount), 0) AS amount
  FROM SESSION.caseleft;

  SELECT
    'LEFT_FILTER_ON' AS query_variant,
    COUNT(*) AS expanded_row_count,
    COUNT(DISTINCT order_id) AS parent_count,
    COUNT(child_id) AS real_child_count,
    COALESCE(SUM(amount), 0) AS amount
  FROM SESSION.casefilter_on;

  SELECT
    'LEFT_FILTER_WHERE' AS query_variant,
    COUNT(*) AS expanded_row_count,
    COUNT(DISTINCT order_id) AS parent_count,
    COUNT(child_id) AS real_child_count,
    COALESCE(SUM(amount), 0) AS amount
  FROM SESSION.casefilter_where;

  SELECT
  FROM SESSION.caseleft
  ORDER BY order_id, array_offset;

  SELECT
  FROM SESSION.casefilter_on
  ORDER BY order_id, array_offset;

  SELECT *
  FROM SESSION.runmanifest;

END IF;

The positive run is acceptable only if the script job itself completes successfully. BigQuery’s ASSERT semantics make a false or null assertion an error, so job success plus the expected evidence rows is stronger than merely inspecting one result grid.

The acceptance invariant cannot be allowed to disappear because an expected table was empty, a child-presence test counted placeholders, or an error-handling layer swallowed a failed assertion.

Assign ownership of the parent-preservation invariant

Correct SQL is necessary, but long-lived reliability also needs ownership.

The source owner owns the meaning of the repeated field: in this fixture, an explicitly empty array represents zero children, and genuine children require non-null unique identities and non-null amounts.

The transformation author owns the representation change. That includes choosing CROSS versus LEFT according to the declared population, deciding whether a child predicate belongs in ON or WHERE, and preserving enough source context to distinguish no children from no qualifying children when required.

The data-quality reviewer owns the independent reconciliation. That reviewer should be able to read the expected ledgers without reverse-engineering them from the implementation being tested.

The downstream metric owner owns the effect of the representation on denominators and row-based measures. When parent placeholders become visible, every downstream COUNT(*) that previously behaved like a child count deserves review.

A concise handover contract can state:

Parent grain:
  order_id, required to survive even when lines is empty.

Child grain:
  child_id, non-null and unique within fixture scope.

Unfiltered expectation:
  parents = 4
  genuine children = 3
  child amount = 125

Filtered amount >= 30 expectation:
  parents = 4
  genuine matching children = 2
  matching amount = 100

Presence rule:
  placeholder output row != genuine child.

Release evidence:
  independent expected ledgers + ASSERT gates + exact job provenance.

Revalidation is required when the relevant contract changes: child-key nullability, child filtering, repeated-field shape, aggregation grain or downstream denominator meaning. Those changes can invalidate an otherwise correct counting or preservation rule.

Only after correctness is established should the team move on to correctness checks before SQL optimization. Runtime tuning is a different decision. A faster query that drops required parents is simply a faster incorrect query.

Connect the exercise to data-engineering foundations

This exercise sits inside broader data-engineering responsibilities: transformation design, data-quality controls, pipeline ownership and explicit data contracts. Refonte Learning’s verified Data Engineering programme currently describes a three-month programme at 12–14 hours per week, with coverage including data pipelines, batch and streaming processing, transformations, storage, governance, Hadoop and Spark, plus practical-project and virtual-internship components. The page does not establish that this exact BigQuery UNNEST acceptance lab or named GoogleSQL instruction is part of the curriculum, so no such claim should be inferred.

The operational lesson is narrower and more durable than any tool syllabus: the owner of a nested-to-flat transformation must prove that every required parent survives the actual query path, that placeholders are not miscounted as children, that genuine child multiplicities and measures reconcile, and that the evidence belongs to the exact SQL revision being released.

For this fixture, that means four parents, three genuine children and 125 before filtering; and, for amount >= 30 under the preserve-all-parents contract, four parents, two matching children and 100. Until an owned-sandbox execution records those observations and its assertion evidence, the responsible release decision remains HOLD.