Test Case Design and Edge Case Analysis Questions

Direct answer
SQL NULL, an empty string (''), and numeric zero (0) are three distinct values with three distinct meanings, NULL means "unknown or not applicable," '' means "known to be an empty piece of text," and 0 means "known to be the number zero," and treating any two of them as equivalent is a common, silent source of incorrect aggregates, comparisons, and dashboard numbers.

Structured elaboration
The behavioral differences that actually cause bugs:

• Aggregates. AVG() and SUM() skip NULL values entirely but include 0 and '' (where applicable) in their computation; COUNT(column) counts non-NULL rows only, while COUNT(*) counts every row regardless.
• Comparisons. NULL = NULL evaluates to NULL (not TRUE), which is why WHERE column = NULL never matches anything, the correct predicate is IS NULL/IS NOT NULL. '' and 0 compare normally with =, they are ordinary, known values.
• Concatenation. NULL propagates through string concatenation ('x' || NULL evaluates to NULL in standard SQL), silently turning an otherwise-valid string into NULL; '' does not propagate this way ('x' || '' is just 'x').

Worked example (executed, SQLite)
Table of 4 customers with revenue [100, 200, NULL, 300]:

SELECT AVG(revenue), COUNT(revenue), COUNT(*) FROM customers;

Result: AVG(revenue) = 200.0, COUNT(revenue) = 3, COUNT(*) = 4. The average correctly excludes the NULL row: (100+200+300)/3 = 200.0.

SELECT AVG(COALESCE(revenue, 0)) FROM customers;

Result: 150.0, i.e. (100+200+0+300)/4. This is a genuinely different number from the first query (200.0 vs 150.0), confirming that silently treating a missing revenue as 0 measurably changes the metric, exactly the mistake the question asks about; whichever choice is correct depends entirely on the business meaning of a missing revenue row (a customer who hasn't been billed yet is not the same as a customer confirmed to owe $0).

Table of 4 users with email ['a@x.com', NULL, '', 'b@x.com']:

SELECT COUNT(*) AS total, COUNT(email) AS count_email,
       SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS null_email,
       SUM(CASE WHEN email = '' THEN 1 ELSE 0 END) AS empty_email
FROM users;

Result: total = 4, count_email = 3, null_email = 1, empty_email = 1. COUNT(email) = 3 conflates "has an email" with "is not NULL", it silently counts the empty-string row as if the user had a real email address, an incorrect "3 of 4 users have an email" conclusion when the honest number is 2 of 4 (one is NULL, one is '', and neither is a usable email).

Concatenation and equality, confirmed directly:

SELECT 'x' || NULL, 'x' || '';   -- results: NULL, 'x'
SELECT NULL = NULL, NULL IS NULL;  -- results: NULL, 1 (true)

Prevention in queries and dashboards
Use IS NULL/IS NOT NULL explicitly rather than = NULL; choose COUNT(column) vs COUNT(*) deliberately based on what the dashboard is actually claiming to measure; only apply COALESCE/NULLIF when a documented business rule justifies the substitution (as shown above, COALESCE(revenue, 0) is a real, defensible choice in some contexts and a real, measurable distortion in others, the query itself can't tell you which one is intended, the business rule has to); and surface data-quality metrics (percentage NULL, percentage '') directly on the dashboard so a viewer can see how much of the underlying data is missing or empty, rather than only seeing a single aggregate number that has already silently absorbed that ambiguity.

Trade-offs & pitfalls
The most common mistake is assuming COALESCE(x, 0) is a "safe default" that can't be wrong, as the revenue example shows, it changes the actual computed metric, it is a business decision disguised as a technical convenience, and applying it without checking whether "missing" and "zero" mean the same thing in context is exactly how averages get quietly biased. A second common mistake is auditing data quality by eyeballing COUNT(column) alone and concluding the data is mostly complete, when a meaningful fraction of "non-NULL" values are actually empty strings masquerading as present data, the honest audit needs the explicit NULL-count and ''-count breakdown shown above, not just one combined number.

Direct answer
A 64-bit to 32-bit integer downsize needs four layers of testing: unit tests on the conversion function itself using pinned boundary values, migration tests that run synthetic datasets containing those boundary values through the full pipeline, integration tests on the serialization/deserialization boundary where the width actually changes on the wire or on disk, and production validation queries that scan already-migrated data for values that could only exist if truncation happened. Executed below with real overflow behavior, not asserted from memory.

Structured elaboration

Unit tests on the conversion function. Pin the four boundary values every signed-32-bit range test needs: INT32_MIN ($-2^{31}$), INT32_MAX ($2^{31}-1$), and the two values one past each boundary (INT32_MAX + 1, INT32_MIN - 1). A correct migration function must reject or explicitly handle the out-of-range pair, not silently truncate them.

Migration tests with synthetic datasets. Generate a dataset that deliberately includes rows at and past each boundary, run it through the actual migration code path (not a reimplementation of it in the test), and assert on the migrated output.

Integration tests for serialization/deserialization. The width change is most dangerous exactly at a serialize/deserialize boundary: a message written by a producer still on the 64-bit schema and read by a consumer already on the 32-bit schema (or vice versa during a rolling deploy) is where silent truncation actually reaches production. Round-trip a boundary value through the real (de)serializer in both schema directions and assert the value survives or the reader explicitly rejects it.

Production validation queries. After migration, scan the downsized column for values that are suspiciously exactly at a wrap boundary (e.g. a cluster of values near INT32_MIN where the source distribution had none), and for any row where a and 64-bit shadow copy (kept temporarily during rollout) disagrees with the 32-bit value once cast back to 64 bits.

Rollback and alerting strategy. Keep the 64-bit source column (or a parallel shadow write) live until the 32-bit column has passed validation in production for a defined bake period, so rollback is a column-source swap, not a data-recovery exercise; wire an alert on any row where the shadow 64-bit value falls outside the 32-bit range (a true overflow candidate) and on any negative-value spike in a column that is semantically non-negative in the source domain (a wrap-around symptom), both checked continuously during the bake period, not just at migration time.

Worked example (executed)

import struct

INT32_MAX = 2**31 - 1
INT32_MIN = -2**31

def downsize_checked(v: int) -> int:
    if v > INT32_MAX or v < INT32_MIN:
        raise OverflowError(f"{v} does not fit in a signed 32-bit integer "
                             f"(range [{INT32_MIN}, {INT32_MAX}])")
    return v

def downsize_wrapping(v: int) -> int:
    # what an unchecked cast / raw struct pack-unpack round trip actually does
    packed = struct.pack('<q', v)
    return struct.unpack('<i', packed[:4])[0]

for name, v in {
    "max_boundary_ok": INT32_MAX,
    "max_plus_1_overflow": INT32_MAX + 1,
    "min_minus_1_overflow": INT32_MIN - 1,
}.items():
    try:
        print(name, v, "checked ->", downsize_checked(v))
    except OverflowError as e:
        print(name, v, "checked -> OverflowError")
    print(name, v, "wrapping ->", downsize_wrapping(v))

Actual run output:

max_boundary_ok 2147483647 checked -> 2147483647
max_boundary_ok 2147483647 wrapping -> 2147483647
max_plus_1_overflow 2147483648 checked -> OverflowError
max_plus_1_overflow 2147483648 wrapping -> -2147483648
min_minus_1_overflow -2147483649 checked -> OverflowError
min_minus_1_overflow -2147483649 wrapping -> 2147483647

This confirms the exact data-loss failure mode the test plan targets: INT32_MAX + 1 (2147483648) does not error under the wrapping (naive) migration path, it silently becomes -2147483648, a large positive value turning negative with no exception raised anywhere in the pipeline. A test suite that only asserts "no exception was thrown" would pass on this exact case while shipping corrupted data; the assertion has to be on the VALUE, not on the absence of an error.

Trade-offs and pitfalls
The most common mistake is writing migration tests against a synthetic dataset the test author generated to already fit in 32 bits, which proves the migration code works on well-behaved input and proves nothing about the boundary. A second common mistake is validating only the migration batch job's own logs for "0 errors," when a wrapping migration by construction never raises an error, so "0 errors in the migration job" is consistent with silent data corruption having occurred. The rollback plan is also frequently underspecified: rolling back application code is not the same as rolling back already-migrated and already-downstream-consumed data, so the shadow-column bake period exists specifically to make rollback a metadata operation instead of a data-recovery one, and skipping it to save storage cost during the migration window is the trade-off that turns a caught overflow into an unrecoverable one.

Direct answer
A column-split-and-backfill migration has to be tested as a multi-phase state machine, not a single change: dual-write, backfill, dual-read verification, cutover, and cleanup each have their own edge cases, and the single highest-leverage safety mechanism is a feature flag that controls the write path and read path independently, so any phase can be paused or reversed without a code deploy.

Structured elaboration

Phase model: (1) add the new column(s) and start dual-write (write both the old and new representation on every write); (2) backfill historical rows; (3) dual-read with new-column verification logged against the old column; (4) cut reads over to the new column; (5) remove the old column.

• Partial backfills: the backfill job (a batch process rewriting historical rows) is interrupted by a deploy, crash, or timeout partway through, leaving some rows migrated and others not. Test by running the backfill against a seeded table, killing it mid-run at a randomized row offset, and asserting a restart from its checkpoint completes correctly with no double-processing and no skipped rows, which requires the backfill job itself to be checkpointed and idempotent, exactly the property the test is proving.
• Concurrent writers: new writes land through the dual-write path while the backfill is still processing historical rows; a naive backfill query can race past a row a writer just touched, or overwrite a fresh write with stale backfilled data. Test by running the backfill concurrently against a synthetic writer producing updates in the exact range currently being backfilled, and asserting the final state resolves to "last writer wins by version or timestamp," not "whichever process happened to finish last."
• Rollback paths: a defect surfaces after cutover, and the system must fail back to the old column without losing writes made through the new-column-only window. Test by running a full forward migration to cutover, triggering rollback, and asserting reads served from the old column reflect every write made during that window, which only works if dual-write was never fully disabled before rollback becomes possible, a design requirement the test should enforce, not just assume.
• Region replication lag: in a multi-region setup, the backfill and dual-write commit to a primary region, and a dual-read verification step served from a lagging replica region can show an old-versus-new mismatch purely from replication delay, not a real bug. Test against a deliberately lagged replica (or induced replication delay) and assert the verification logic tolerates mismatches within a stated lag bound while still flagging ones that persist beyond it.

Safety checks: toggle flags independently control whether dual-write is active, whether backfill is running, whether dual-read verification is on, and whether cutover is live; test the small set of valid flag-state combinations (not full pairwise, since the flags are not independent, cutover implies dual-write already happened) against expected read/write behavior. Dual-read reads both columns on every request during the verification phase and logs or alerts on mismatch without failing the request, a live, low-risk signal before trusting the new column for real traffic.

Metrics and alerts: backfill completion percentage tracked over time (to catch a stalled job); dual-read mismatch rate, which should trend to and stay at zero, a nonzero steady-state rate after the lag-tolerance window indicates a genuine bug, not a timing artifact; and replication lag itself as a leading indicator, so the dual-read alert threshold can be lag-aware instead of a fixed number that spuriously fires during an ordinary lag spike unrelated to the migration.

Worked example
A concrete backfill checkpointing scenario: a table of 10,000,000 rows, backfilled in batches of 5,000 rows, giving exactly 2,000 batches ($10{,}000{,}000 / 5{,}000 = 2{,}000$). If the job crashes at batch 1,200 (600 batches short of 2,000; $1{,}200 \times 5{,}000 = 6{,}000{,}000$ rows completed), a correctly checkpointed restart resumes at row offset 6,000,000 and processes the remaining 800 batches ($2{,}000 - 1{,}200 = 800$), reaching the same final state whether the job ran straight through or crashed and restarted once. The partial-backfill test above is exactly this scenario made concrete: kill the job at an arbitrary batch, confirm the restart's final row count matches an uninterrupted run's.

Trade-offs and pitfalls

• Testing dual-write consistency only at the unit level, with a mocked database, misses the concurrent-writer edge case entirely; that specific edge case needs an integration test against a real or realistic database under actual concurrent load.
• Leaving dual-write on indefinitely "to be safe" has a real cost, doubled write amplification and doubled lock contention; state an explicit exit criterion instead, for example dual-read mismatch rate at zero for N consecutive days, rather than leaving it on forever.
• A rollback path that is never exercised until the day it is actually needed is the single most common migration failure; the test suite should run rollback routinely, for example on every migration release in staging, not just design it on paper.
• A replication-lag alert that ignores the lag itself will either cry wolf during an ordinary lag spike (and get silenced) or, if silenced too broadly, will miss the genuine bug once real lag is common; this is a real calibration trade-off, not something solved once and forgotten.

Direct answer
Timezone bugs in data pipelines are dangerous specifically because they are silent: a naive (timezone-unversion-unaware) timestamp, a mixed set of timezone representations, an ambiguous time during a Daylight Saving Time (DST) transition, and inconsistent storage (some rows Coordinated Universal Time (UTC), some local) all produce a value that parses successfully and looks like a valid timestamp, so the bug only surfaces later as an off-by-one-hour (or off-by-one-day) discrepancy in an aggregate that nobody can explain without re-deriving the ingestion path.

Structured elaboration

• Naive timestamps (no timezone offset attached). A naive timestamp like 2026-03-08 14:30:00 is only meaningful if you already know which timezone it was recorded in, and that knowledge is not encoded in the value itself. Detect this at ingestion by checking whether the parsed value carries a UTC offset or timezone identifier; if not, do not guess, look up the source system's documented timezone (or the timezone of the device/service that emitted it) and attach it explicitly during ingestion, never downstream where that context has been lost.
• Mixed timezone representations. One source sends 2026-03-08T14:30:00-05:00 (an explicit offset) and another sends 2026-03-08 14:30:00 EST (an abbreviation). Abbreviations are ambiguous (EST, Eastern Standard Time, is used by both North America and Australia with different offsets) and are not a reliable parsing target. Detect by validating that every ingested timestamp resolves to an unambiguous representation (a numeric UTC offset or a full IANA (Internet Assigned Numbers Authority) timezone database identifier like America/New_York, never a bare abbreviation) before it is accepted.
• Ambiguous local times during DST transitions. During the "fall back" transition, a local clock time such as 01:30 genuinely occurs twice in the same day (once before the clocks change, once after), and during "spring forward" a time such as 02:30 never occurs at all. A naive local-to-UTC conversion library will pick one interpretation silently, or raise, depending on the library and settings. Detect by testing explicitly against both transition dates for every timezone the pipeline ingests from, not just once for one region, since the transition dates and even whether DST is observed at all differ by region and by year (some jurisdictions have changed their DST rules).
• Inconsistent storage (some UTC, some local). The most damaging variant, because both are individually valid-looking values; a table with a created_at column silently mixing UTC and local-time rows (e.g. after a migration that only fixed new writes going forward) makes every aggregate across that column wrong in a way that will not throw an error. Detect by tracking, at the column or event-source level, a recorded assumption of which representation is in force, and by testing that a known historical value converts consistently regardless of which code path wrote it.

Fix and standardization strategy
Standardize on storing every timestamp in UTC, as an unambiguous instant, and only convert to a local representation at the display or reporting layer, never in storage or in aggregation logic. For any source emitting naive or ambiguous timestamps, attach the source's known offset explicitly at ingestion (do not defer the decision downstream), and store the original raw value alongside the normalized UTC value so a downstream investigation can re-derive what happened if the source's stated timezone assumption turns out to have been wrong.

Worked example
A ride-hailing pipeline ingests trip-start events from two regional services: Service A emits 2026-11-01 01:15:00 (naive, and this region observes DST, so 01:15 is ambiguous on the November 1 fall-back date) and Service B emits 2026-11-01T01:15:00-04:00 (explicit offset, unambiguous). If both are naively parsed as "local time, current offset," Service A's value could resolve to either 05:15:00 UTC or 06:15:00 UTC depending on which side of the fall-back transition it actually occurred on, a full hour of ambiguity for the exact same displayed clock time. The standardization fix is to require Service A to either emit an explicit offset (fixing it at the source) or, if that is not possible, to attach the offset at ingestion using the service's known DST-transition schedule rather than trusting a naive datetime library's default (which typically picks one interpretation without surfacing that it had to guess).

Trade-offs and pitfalls
The most common wrong turn is fixing this only for new data going forward and never backfilling or explicitly flagging historical rows, which produces exactly the inconsistent-storage case described above but self-inflicted by the fix itself. A second pitfall is testing DST handling against only one timezone's transition dates and assuming the logic generalizes; DST rules are set by individual jurisdictions and have changed historically (and differ in whether they exist at all), so a hardcoded transition date is a latent bug for any other region the pipeline later ingests from. A third is treating timezone abbreviations as if they were an acceptable, if slightly annoying, input format; they are not reliably parseable at all once more than one region is in scope, and a design that accepts them is deferring an unresolvable ambiguity rather than avoiding it.

Direct answer
A groupby().shift() lag implementation has five edge cases that a happy-path test (one clean, sorted, multi-row group) will never exercise: single-row groups, duplicated timestamps within a group, non-monotonic (out-of-order) input, a group present in training but absent from a given test/inference batch, and a group whose values are entirely NaN. Each needs its own assertion, because each exercises a different part of the implementation: the groupby boundary, the sort-then-shift ordering, and NaN propagation are three genuinely separate code paths.

Structured elaboration
The five edge cases each isolate a different part of groupby().shift()'s behavior: the groupby boundary (does a lag ever leak across groups), the sort-then-shift ordering (does the shift respect true time order or just row order), and NaN propagation (does an all-missing group stay missing rather than being fabricated). A sample dataset and the concrete unit tests for each case follow in the worked example below.

Worked example: sample dataset, code, and unit test inputs/expected outputs

import pandas as pd, numpy as np

def add_lag1(df, group_col="id", time_col="ts", value_col="value", sort_first=True):
    if sort_first:
        df = df.sort_values([group_col, time_col], kind="mergesort")  # stable sort: preserves tie order
    df = df.copy()
    df["lag_1"] = df.groupby(group_col)[value_col].shift(1)
    return df

sample = pd.DataFrame({
    "id":    ["A", "A", "A", "B",  "C", "C", "D",  "E",  "E"],
    "ts":    [1,   2,   3,   1,    1,   1,   1,    1,    2],   # C has a duplicated timestamp
    "value": [10,  20,  30,  100,  5,   7,   np.nan, np.nan, np.nan],
})

Actually calling add_lag1 and the non-monotonic / missing-group cases below, for real

result = add_lag1(sample)
for gid in ["A", "B", "C", "D", "E"]:
    lags = result.loc[result["id"] == gid, "lag_1"].tolist()
    print(f"{gid}: lag_1 = {lags}")

f_raw = pd.DataFrame({"id": ["F", "F", "F", "F"], "ts": [3, 1, 4, 2], "value": [300, 100, 400, 200]})
f_sorted = add_lag1(f_raw, sort_first=True)
f_unsorted = add_lag1(f_raw, sort_first=False)
row_ts2_sorted = f_sorted.loc[f_sorted["ts"] == 2, "lag_1"].iloc[0]
row_ts2_unsorted = f_unsorted.loc[f_unsorted["ts"] == 2, "lag_1"].iloc[0]
print(f"Non-monotonic group F, row ts=2: lag_1 with sort_first=True -> {row_ts2_sorted}; with sort_first=False -> {row_ts2_unsorted}")

train = sample.copy()
inference_batch = pd.DataFrame({"id": ["A", "B"], "ts": [4, 2], "value": [40, 200]})
combined = pd.concat([train, inference_batch], ignore_index=True)
combined_result = add_lag1(combined)
new_a_row = combined_result[(combined_result["id"] == "A") & (combined_result["ts"] == 4)]
new_b_row = combined_result[(combined_result["id"] == "B") & (combined_result["ts"] == 2)]
print(f"Group A new row lag_1 = {new_a_row['lag_1'].iloc[0]} (A's last known value was 30)")
print(f"Group B new row lag_1 = {new_b_row['lag_1'].iloc[0]} (B's last known value was 100, not leaked from another group)")
train_counts = {g: int((train['id'] == g).sum()) for g in ['C', 'D', 'E']}
combined_counts = {g: int((combined_result['id'] == g).sum()) for g in ['C', 'D', 'E']}
print(f"C/D/E row counts unchanged by the inference batch: {train_counts} -> {combined_counts}")

Running add_lag1(sample) produces: A gets lag_1 = [NaN, 10, 20] (each row lags the previous within the group); B (single row) gets lag_1 = [NaN]; C (duplicated ts=1 twice) gets lag_1 = [NaN, 5.0]; D (single row, value is NaN) gets lag_1 = [NaN]; E (two rows, both NaN) gets lag_1 = [NaN, NaN].

Unit tests, inputs and expected outputs

1. Single-row group (B). Input: one row, ts=1, value=100. Expected: lag_1 = NaN. This catches an implementation that tries to "borrow" a lag value from an adjacent group instead of correctly scoping the shift to the group boundary.
2. Duplicated timestamps (C). Input: two rows both at ts=1, values 5 and 7. Observed (and asserted) behavior: shift() is POSITIONAL after the sort, not time-aware, so the second row in sort order still receives lag_1 = 5.0 even though its timestamp did not actually advance. The test asserts this documented behavior explicitly (rather than silently trusting it), because a duplicated-timestamp row's lag value does not represent a genuine time step and downstream consumers need a companion flag (e.g. a has_duplicate_ts column) to know that.
3. Non-monotonic input. A group F arriving in raw order ts=[3,1,4,2], value=[300,100,400,200]. Expected: after sorting, the row at ts=2 gets lag_1 = 100 (the value at ts=1, its true temporal predecessor). With sort_first=False, the SAME row instead gets lag_1 = 400, because it lags off whatever row happened to precede it in the raw, unsorted input, a distinctly different and wrong number, not a subtly-off one, which makes this an especially easy regression to catch once tested but an especially easy bug to ship if sort_first is ever accidentally disabled or bypassed upstream.
4. Group missing from a test/inference batch. Training data contains groups A through E; an inference batch contains a NEW row for group A (ts=4, value=40) and a row for group B, but no rows at all for C, D, or E. Expected: group A's new row correctly lags off A's last known value (30); groups C, D, E simply produce no output rows (they are absent from the batch, which is not an error), and critically, no lag value leaks FROM one group INTO another just because both were concatenated into the same batch.
5. Group with only NaN values (E). Input: two rows, both value=NaN. Expected: lag_1 stays NaN for both rows. This confirms the implementation never fabricates a numeric lag (e.g. via an unintended forward-fill) for a group that was never actually observed.

Trade-offs & pitfalls
The single most common bug in a hand-rolled lag implementation is forgetting sort_first entirely, or sorting by time_col alone without including group_col in the sort key, which lets rows from different groups interleave and produces a lag that silently crosses group boundaries. The second most common bug is treating duplicated timestamps as a data-quality problem to fix upstream rather than a case the lag function must have DEFINED (even if imperfect) behavior for, since upstream data will eventually contain them regardless of how the pipeline is documented, and a lag function that raises on duplicates instead of behaving predictably turns a data-quality issue into a production outage. Finally, testing lag features purely at the unit level misses the missing-group case, which only shows up once training data and a live inference batch genuinely diverge in which groups are present, so that case belongs in an integration-style test that constructs training and inference frames independently rather than slicing them from the same dataframe.