Amazon Data Analyst Interview Preparation Guide (Mid-Level)

Direct answer: Pick the tool by matching its physical behavior to what the KPI (key performance indicator) actually needs: a common table expression (CTE, a named subquery written with WITH ... AS (...)) for a one-off analysis you'll run once and throw away, a temp or staging table when the calculation is expensive but the reuse window is short (a single session, a single ETL run, meaning extract-transform-load), and a materialized view (a query whose result is physically stored on disk and refreshed on a schedule or trigger, rather than recomputed on every read) once the same expensive logic is read repeatedly by multiple dashboards and can tolerate being slightly stale. Near-real-time dashboards are the one case where none of the "precompute it" options fit cleanly: they usually need a lean, indexable live query instead, or an incrementally-updated summary table rather than a full materialization.

Structured elaboration

Recommendations by scenario:

• One-off ad hoc analysis: a CTE (or a plain subquery). There is no second reader to amortize setup cost against, so the fastest path to an answer wins over any persistence machinery.
• Repeatedly-used expensive calculation: a materialized view (or, where the engine lacks native materialized views, a scheduled job that populates a plain table). The cost of computing it is paid once per refresh instead of once per dashboard load, and it becomes indexable, which a CTE never is.
• Near-real-time dashboard: avoid full materialization; either query the base tables directly with supporting indexes so the planner can push filters down, or maintain an incrementally-updated summary table (updated on write, not recomputed wholesale) if the underlying computation is too heavy to run live on every page load.

When does a CTE pipeline graduate to a materialized view? Three independent signals, any one of which is usually enough on its own: (1) reuse -- the same CTE logic is being copy-pasted into a second, third, or fourth query or dashboard, which is a sign the definition should live in one place instead of many; (2) performance -- the CTE's underlying computation starts showing up as the dominant cost in an EXPLAIN plan across multiple callers, so the aggregate cost of recomputing it everywhere now exceeds the cost of maintaining a refreshed copy; (3) governance -- different query authors start writing slightly different filters around a copy-pasted CTE, so the "same" KPI silently drifts between dashboards. Any of these is the point to promote the logic into a materialized view (or persisted table) with one refresh schedule and one definition that every consumer reads.

Worked example

A KPI like "monthly active accounts" (distinct accounts with at least one qualifying event in the trailing 30 days) is a good stand-in: it is expensive on a large events table because it needs a distinct count over a rolling window, and it is exactly the kind of metric multiple dashboards want to show.

-- (a) one-off exploration: CTE, thrown away after this single query
WITH active_accounts AS (
  SELECT DISTINCT account_id
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT COUNT(*) AS mau FROM active_accounts;

-- (b) repeatedly-used, refresh-tolerant: materialized view, indexed, refreshed nightly
CREATE MATERIALIZED VIEW mv_monthly_active_accounts AS
SELECT account_id, MAX(event_time) AS last_active_at
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY account_id;

CREATE INDEX idx_mv_maa_account ON mv_monthly_active_accounts(account_id);
-- refreshed on a schedule, e.g. REFRESH MATERIALIZED VIEW mv_monthly_active_accounts;

-- (c) near-real-time: query live, relying on an index on events(account_id, event_time)
-- rather than pre-aggregating, so results are current to the second
SELECT COUNT(DISTINCT account_id) AS mau_live
FROM events
WHERE event_time >= NOW() - INTERVAL '30 days';

ML (machine learning) feature pipeline framing: this exact decision tree also applies when the "dashboard" is actually a model training job reading a feature. A feature explored once in a notebook is a CTE; a feature reused across many training runs (and needing point-in-time correctness, i.e. only using data available as of each label's timestamp) is exactly the "repeatedly-used expensive calculation" case, and belongs in a materialized feature table (or a feature store) that is refreshed on a known cadence, not recomputed inline in every training query. The freshness criterion becomes sharper here: a stale feature table used for training is often fine, but the same staleness at serving/inference time can silently create training-serving skew, so the acceptable staleness window has to be decided per use case, not assumed.

Spark SQL / Catalyst optimizer angle: the reuse math is different in Spark. Spark's Catalyst optimizer treats a WITH clause referenced multiple times by inlining and re-planning the underlying logical plan at each reference site by default, rather than computing it once and sharing the result the way a materialized view would. A CTE joined against three times in one Spark SQL query can trigger the same expensive computation three separate times unless you explicitly force sharing (persisting the intermediate DataFrame with .cache()/.persist(), or writing it out and reading it back). This makes the "graduate to materialization" decision arrive sooner in Spark than in a single-node warehouse: a CTE reused even twice within one query is worth checking for repeated work, not just a CTE reused across many separate queries.

Trade-offs & pitfalls

The common wrong turn is reaching for a materialized view purely because a query is slow, without checking whether the consumer can actually tolerate the staleness that comes with it: a materialized view refreshed hourly is the wrong answer for a dashboard that promises "as of right now." Conversely, leaving an expensive, frequently-reused CTE unmaterialized "for simplicity" quietly multiplies its cost by however many dashboards call it, since nothing about a CTE shares work across separate queries. Temp/staging tables sit in between and are easy to over- or under-use: they are the right tool inside a single multi-step job (build once, query several times, then discard), but reaching for one to serve a dashboard means re-inventing a materialized view's refresh logic by hand, usually worse. Always confirm the actual freshness requirement with the stakeholder before choosing: "near-real-time" and "updated every 15 minutes" are very different engineering problems that get conflated in casual requirements language.

Direct answer
Nowcasting means estimating the current, not-yet-fully-observed period's value using whatever partial signal and leading indicators ARE available before the full data lands - here, building a model that predicts today's eventual conversion rate from today's PARTIAL data (and historical patterns of how partial-day data relates to the eventual full-day figure) rather than waiting the full 24 hours for complete data.

Structured elaboration

• Model choice: a regression (or ratio-based) model relating "partial-day cumulative signal at hour h" to "eventual full-day conversion rate," trained on historical days where you have both; because the relationship between partial and full data likely varies by hour-of-day and day-of-week (different traffic composition earlier vs later in the day), the model needs to condition on those factors rather than using one flat ratio for every hour.
• Features/leading indicators: same-day partial cumulative conversions and traffic so far, the typical historical intra-day SHAPE of conversion accumulation (what fraction of a typical day's conversions have historically arrived by this hour), day-of-week and any known promotional/event context for today specifically.
• Training and validating the model: train on historical days with the SAME partial-vs-full structure you'll use at inference (e.g. "given only the first 8 hours of data, predict the full day"), and validate by holding out recent days and checking how the nowcast's error shrinks as more of the day's partial data accumulates - a well-behaved nowcast should get systematically more accurate later in the day, since it has more signal to work with.
• Presenting the estimate and its confidence to stakeholders: report the nowcast alongside an explicit confidence/uncertainty band that WIDENS the earlier in the day the estimate is made (very little partial data = wide uncertainty; late in the day = narrow), and label it clearly as a same-day ESTIMATE rather than the final, fully-reconciled figure, to avoid it being mistaken for the eventual official number once real data finishes landing.

Worked example
By early morning, a nowcast might have wide uncertainty (little partial signal accumulated yet) and lean heavily on the historical typical intra-day shape as its prior; by mid-afternoon, with a meaningful fraction of the day's traffic already observed, the nowcast should be visibly more confident and closer to what the eventual 24-hours-later true figure turns out to be - presenting BOTH the point estimate and this narrowing confidence band over the course of the day gives stakeholders an honest picture of how much to trust the number at any given check-in time, rather than a static, equally-confident-looking number all day.

Trade-offs & pitfalls
The most consequential mistake is presenting an early-morning nowcast with the same apparent confidence as a late-afternoon one, or silently reconciling the two without flagging the transition - stakeholders making decisions off a same-day estimate need to know explicitly HOW early (and therefore how uncertain) the current estimate is, not just its point value. Validate the whole approach specifically by checking that the confidence band's stated coverage is honest at each hour of the day (an 80% interval should actually contain the eventual true value about 80% of the time at that hour), not just that the point estimate looks reasonable.

Direct answer

Run for a whole number of full weekly cycles, decide before looking at any data how a holiday inside that window will be handled, and hold traffic allocation balanced by day-of-week (and by region and time zone if the test spans them) rather than trusting that a single aggregate 50/50 split will average out. When a weekday-positive, weekend-negative pattern shows up later, treat it as a hypothesis to falsify with three specific checks, real heterogeneity, a traffic-composition artifact, or noise, rather than reading the raw split at face value.

Structured elaboration

Duration and analysis window

Run for at least two, ideally three or more, full 7-day cycles. A partial week biases the pooled result toward whichever days happen to be over-represented, and a single week does not let you separate a real weekday/weekend pattern from that week's idiosyncrasies. If a holiday falls inside the planned window, decide up front, before seeing any results, between two options: exclude the holiday period from the primary analysis window and report a "typical week" estimate, or explicitly include it and report a distinct holiday-period estimate. Choosing between those two after looking at which one produces a better-looking result is a form of after-the-fact window selection and should be avoided; pre-register the choice in the analysis plan.

Traffic allocation and balance across time and geography

Stratify random assignment by day-of-week, and by region or time zone if the rollout spans them, so the same proportion of each arm is exposed every day and in every zone rather than relying on an aggregate split that could hide a skew. For a multi-region or multi-time-zone test, anchor "day" and "week" boundaries to each user's local time rather than a single server or UTC clock; otherwise one region's weekend gets miscounted against another region's weekday, and verify the treatment-to-control ratio stays constant across regions and hour-of-day buckets individually, not just in the combined total. Aggregate balance can look fine while a specific region or time window is quietly imbalanced, and that imbalance is exactly what later gets mistaken for a day-of-week effect.

Modeling the temporal structure instead of ignoring it

Rather than computing one pooled treatment effect and hoping seasonality washes out, fit day-of-week (and holiday, and region, if relevant) as explicit terms: outcome ~ treatment + day_of_week + treatment:day_of_week + region. This is standard regression-formula shorthand: ~ means "model the left-hand outcome using the terms on the right," so this line reads as "predict the outcome from the treatment, the day type, and the region," and treatment:day_of_week is an interaction term, a piece that lets the treatment's effect itself differ by day type rather than assuming it is the same on weekdays and weekends. The interaction term is what actually tells you whether the treatment effect differs by day type, instead of a single pooled number that could be hiding it.

Investigating a weekday-positive, weekend-negative split

Three checks, run in this order:

1. Is it real? Fit the treatment-by-day-type interaction term from the model above and check whether it is distinguishable from a null effect. This is one specific comparison, not a license to slice every available dimension until something looks significant; keep the interaction pre-specified as part of the analysis plan for exactly this reason.
2. Is it a traffic-composition artifact? Check whether the user mix itself differs by day type: a different device split, acquisition channel, or new-versus-returning ratio on weekends than weekdays. Re-run the interaction model with that covariate added and interacted; if the day-type interaction shrinks toward zero once the segment mix is controlled for, the apparent weekday/weekend split was really a segment-level pattern wearing a calendar label. Also check whether the rollout itself was staggered mid-week (a ramp that reached full exposure partway through the window) or whether an assignment-pipeline issue caused the treatment:control ratio to drift on certain days; both produce a day-type-looking artifact that has nothing to do with actual weekday or weekend behavior.
3. Is it noise? Compare the confidence interval on each day-type's estimate rather than the point estimates alone. Weekend traffic is frequently a fraction of weekday traffic, so a "negative" weekend estimate often carries a wide interval that comfortably contains the weekday estimate.

Worked example

Suppose the weekday arm has 8,000 users per group with control conversion 10.0% and treatment conversion 10.6% (a +0.6 percentage point delta), and the weekend arm has 2,000 users per group (lower weekend traffic) with control conversion 10.0% and treatment conversion 9.4% (a -0.6 percentage point delta). This is exactly the pattern in the question: positive on weekdays, negative on weekends.

Standard error of each delta, using $SE = \sqrt{\frac{p_c(1-p_c)}{n} + \frac{p_t(1-p_t)}{n}}$:

Weekday: $SE_{wd} = \sqrt{\frac{0.10\times0.90}{8000} + \frac{0.106\times0.894}{8000}} = 0.00481$, so the weekday delta's 95% interval is roughly $-0.34\text{pp}$ to $+1.54\text{pp}$, which already crosses zero.

Weekend: $SE_{we} = \sqrt{\frac{0.10\times0.90}{2000} + \frac{0.094\times0.906}{2000}} = 0.00936$, so the weekend delta's 95% interval is roughly $-2.43\text{pp}$ to $+1.23\text{pp}$, also crossing zero.

Testing whether the two deltas actually differ from each other: $z = \frac{0.006-(-0.006)}{\sqrt{0.00481^2+0.00936^2}} = \frac{0.012}{0.01052} \approx 1.14$, well under the 1.96 threshold for a two-sided 5% test. Both individual intervals already contain zero, and the two deltas are not statistically distinguishable from each other. With these particular sample sizes, the weekday-positive-weekend-negative pattern is fully consistent with noise, before ever needing to invoke a real behavioral difference or an artifact.

Trade-offs & pitfalls

• Trusting the point estimate over the interval. A sign flip between two point estimates feels meaningful; whether it survives a formal comparison of the two deltas, as above, is what actually determines whether there is anything to explain.
• Deciding the holiday treatment after seeing results. Choosing whether to include or exclude a holiday period based on which choice produces the preferred outcome is a subtle form of p-hacking through window selection, even when no single test is repeated.
• Assuming aggregate balance implies balance everywhere. A day-of-week or region-level imbalance can hide inside an aggregate 50/50 split and later masquerade as a real seasonal effect.
• Over-correcting into paralysis. Not every day-type split needs a full forensic investigation; reserve the three-check process for patterns that would actually change a rollout decision, and size the investigation to the stakes.

Direct answer. Slicing a metric by more dimensions always increases the number of resulting segments, and every additional split divides the available sample among more, smaller buckets. The core tradeoff is that finer granularity gives you more actionable specificity, but past a point the per-segment sample is too small for the estimate to be trustworthy. The fix is not to stop slicing, it is to size and stabilize each slice explicitly rather than reporting every combination at face value.

Structured elaboration.

• Minimum-sample rule. Before reporting a segment's rate as a standalone number, require a minimum count, commonly a rule like "at least 30 to 100 conversions" depending on how much precision the decision needs; below that, the confidence interval around the rate is wide enough that the point estimate alone is misleading.
• Hierarchical grouping / shrinkage (empirical Bayes). Instead of reporting a small segment's raw rate, pull it partway toward a more reliable estimate (the grand mean, or the mean of a broader group it belongs to), with the amount of pulling ("shrinkage") increasing as the segment's own sample size shrinks. A common formula is a weighted average of the segment's own rate and the overall rate, where the weight on the segment's own data grows with its sample size:
  $\hat p_i^{shrunk} = w_i \cdot \hat p_i + (1-w_i)\cdot \bar p, \quad w_i = \frac{n_i}{n_i + k}$
  where $n_i$ is the segment's sample size, $\bar p$ is the overall rate, and $k$ is a chosen pooling-strength constant (larger $k$ shrinks small segments harder).
• Which combinations are worth reporting at all. Not every combination of country x traffic_source x device is worth a dedicated row. A practical rule is to report a combination only if it clears both a minimum sample threshold and a minimum share of total volume, and to fold everything else into a coarser grouping (drop one dimension, or collapse into "other").

Worked example. Suppose the overall conversion rate across all traffic is $\bar p = 0.05$ (5%). A specific country-traffic_source-device combination has only 40 visitors and 4 conversions, a raw rate of $\hat p_i = 4/40 = 0.10$ (10%). Using a pooling constant $k=200$, the shrinkage weight is $w_i = 40/(40+200) = 1/6 \approx 0.167$. The shrunk estimate is:
$\hat p_i^{shrunk} = 0.167 \times 0.10 + 0.833 \times 0.05 = 0.0167 + 0.0417 = 0.0583$
So instead of reporting this thin segment at a headline-grabbing 10%, the shrunk estimate of about 5.8% reflects that 40 visitors is not enough evidence to move far from the overall rate, while still nudging the estimate slightly above the baseline because the segment's own data does carry some signal.

Trade-offs and pitfalls. A hard minimum-sample cutoff is simple to explain to stakeholders but creates a visible cliff (a segment with 29 conversions is hidden, one with 30 is shown at full precision); shrinkage avoids the cliff by degrading gracefully but is harder to explain and requires choosing (or fitting) the pooling constant, which is itself a judgment call. Whichever approach is used, the biggest pitfall is silently reporting a thin segment's raw rate as if it had the same reliability as a well-powered one, since a single volatile 10%-vs-5% headline from a 40-visitor slice is exactly the kind of number that gets over-interpreted in a stakeholder meeting.

Direct answer

Pick a project that genuinely needed more than one function, and be specific about two things: what YOU owned (not what 'the team' did), and the one concrete mechanism that determined whether the collaboration worked, such as a shared definition of done, a clear handoff point, or clarity on who decided what when opinions differed. Vague answers ('we communicated well') sound rehearsed; specific answers sound lived-in.

What the story needs to show

Your specific contribution. Interviewers are listening for what you personally decided or built, distinct from what your collaborators did. If every sentence is 'we', the interviewer cannot tell what you'd do differently on the next team.

A mechanism-level explanation. Organize the story around one of three lenses:

• Shared goal: did every function agree on what 'done' looked like and how success would be measured, or was each function quietly optimizing for its own definition?
• Interface or handoff: was there a clear point where work crossed from one function to another, and was that point actually defined, or did people guess?
• Decision rights: when functions disagreed, was it clear whose call it was, or did disagreement just stall until someone got tired of arguing?

Honesty if it's a struggle story. The question explicitly allows 'succeed or struggle'. A good struggle story ends on what you changed about the collaboration, not on who was at fault.

Worked example

Situation: [your team] needed to deliver [a feature or initiative] that required real work from [Team A, for example a design or research function] and [Team B, for example a data or infra function], against a fixed external date.
Task: your role was the one connecting the three groups, for example owning the shape of the interface between design and engineering, or owning how data requirements got translated into a schema.
Action: early on, each function had a different idea of what 'done' meant for their piece, which caused rework when the pieces met. You wrote a short one-page agreement naming the shared definition of done and who would sign off on each handoff, and used it to resolve the next two disagreements without a meeting.
Result: the project shipped on the revised date, and the agreement itself became something the group reused on the next cross-functional piece of work, which is the real marker of a story about redesigning the collaboration rather than just pushing through it.

To make that skeleton concrete rather than a fill-in-the-blank: picture a checkout redesign that needed real work from the design function and the payments engineering function, against a fixed external date tied to a promotional campaign launch. The specific disagreement was about what 'done' meant for the new payment-method selector: design considered the screen done once every state (loading, error, empty) matched the approved mockups pixel-for-pixel, while payments engineering considered it done once the integration correctly handled every payment-provider response code, even ones with no mockup drawn yet. That mismatch caused two rounds of rework when a payment-provider error state shipped without a design pass. The one-page agreement that resolved it included this line: 'A screen is done when it matches an approved mockup for every state the payments API can return, and any new state discovered after mockups are drawn triggers a joint 15-minute review before either side builds it.' That single sentence is what let the two functions stop re-litigating 'done' every time a new edge case appeared, and both sides signed off on it before the next round of work began.

Trade-offs and pitfalls

• A generic 'we all communicated well' answer with no mechanism is the single most common weak version of this story, avoid it.
• Over-crediting the team at the expense of your own specific contribution leaves the interviewer unable to evaluate you.
• If you pick a struggle story, resist framing it as the other function's fault. The senior version of this answer explains what you changed about how the groups worked together, not who dropped the ball.
• The strongest answers show you redesigning a structure (a handoff, a shared definition, a decision rule), not just working harder inside a broken one.

A two-dimension bucket (customer AND month) is a GROUP BY on both a foreign key and a truncated date expression together, producing one row per customer per active month rather than one total per customer or one total per month.

Structured elaboration

SELECT customer_id, strftime('%Y-%m', order_date) AS month, SUM(amount) AS monthly_revenue
FROM orders
GROUP BY customer_id, month
ORDER BY customer_id, month;

This is the same GROUP BY-on-multiple-columns mechanic as any multi-dimension breakdown, just with one dimension being a plain identifier (customer_id) and the other a computed date-truncation expression (month). The output naturally has one row per customer per month in which that customer had at least one order, again omitting customer/month combinations with zero orders unless combined with the zero-fill technique (generate every customer/month combination that should appear, for example via a calendar/date-series CTE cross-joined against customers, then LEFT JOIN the aggregated revenue onto that full combination list so a zero-order month shows an explicit 0 in the result instead of simply being absent from it).

Worked example

Given customer 1 with orders of $100 (January) and $50 (January), and $30 (February): the query returns two rows for customer 1, January with $150 and February with $30, correctly separating the two months rather than combining them into one customer-level total.

Trade-offs and pitfalls

This shape (entity x time period) is the foundation of most cohort and retention-style reporting; combined with the zero-fill technique (to show customer/month pairs with zero activity) and a running total (a window function, out of this topic's scope), it becomes a full cohort revenue table.

Transaction isolation levels control visibility of concurrent transactions and how the DB prevents anomalies (dirty reads, non-repeatable reads, phantom reads):

• Read Uncommitted: lowest; allows dirty reads; no shared locks — fastest but unsafe.
• Read Committed: only committed data visible; prevents dirty reads by acquiring/releasing short-term read locks.
• Repeatable Read: read locks held for transaction duration; prevents non-repeatable reads but can block writers.
• Serializable: strongest; behaves as if transactions run serially — highest consistency and most locking/contention.
• Snapshot / MVCC (e.g., PostgreSQL snapshot, SQL Server READ_COMMITTED_SNAPSHOT or SNAPSHOT): readers see a transaction-consistent snapshot without taking read locks; avoids blocking writers by using versioned row images.

Locking and contention: stronger levels (Repeatable Read, Serializable) hold locks longer or escalate them, causing blocking, deadlocks, longer latencies, and throughput drop during heavy OLTP. Even Read Committed can cause contention when long-running reporting queries acquire many locks.

Recommendation for occasional consistent reporting during heavy OLTP:

• Prefer snapshot-based reads (MVCC) or enable READ_COMMITTED_SNAPSHOT where supported. This gives a transaction-consistent view without blocking writers, minimizing impact on OLTP. It protects reporting from seeing partial changes while keeping OLTP throughput high.
• If MVCC isn’t available, run reports against a read replica or a dedicated reporting/data-warehouse copy (ETL) to isolate load; replicas can be slightly stale but avoid contention.
• For critical, up-to-the-second reports: use short, targeted queries, appropriate indexes, and set transaction isolation to Read Committed to reduce lock durations; avoid long transactions.

Trade-offs:

• MVCC increases storage/versioning overhead and can require VACUUM/cleanup.
• Replicas/ETL add latency (staleness) and operational complexity.
• Serializable gives correctness but is usually unacceptable for heavy OLTP due to throughput loss.

For a data analyst: default to snapshot reads or reporting replicas for occasional consistent reads; reserve stronger isolation only for small, critical transactions.

Direct answer

When a full scan is too expensive, random, stratified, reservoir, and hash-based sampling each estimate data-quality metrics (null rate, mean, distinct count) with different trade-offs in bias risk and implementation complexity, and the choice between sampling and full-scan validation is a trade-off between cost and the risk of missing a rare-but-important issue that a sample, by definition, may not contain.

Structured elaboration

• Simple random sampling: unbiased for uniformly-distributed metrics, but requires knowing the total population size up front or an efficient way to sample uniformly, which is not always trivial on a distributed dataset without a full scan to begin with.
• Stratified sampling: samples proportionally within known subgroups (by region, by product category), which is important when the metric of interest varies meaningfully across strata, since plain random sampling could, by chance, under-represent a small-but-important stratum.
• Reservoir sampling: allows uniform random sampling from a stream of unknown total size in a single pass, without needing to know the population size in advance, well suited to a continuously-arriving pipeline rather than a static table.
• Hash-based sampling: deterministically sample by hashing a key (a user ID) and keeping records whose hash falls in a target range, which gives reproducible sampling (the same sample every run) and is efficient to compute in a distributed system without coordination.
• Sampling versus full scan trade-off: sampling dramatically reduces cost and lets you validate a much larger table on a tighter budget, but it fundamentally cannot guarantee catching a rare issue that affects a small fraction of rows, if the sample size is smaller than the reciprocal of the rare issue's rate, you have a real, non-negligible chance of missing it entirely, no matter how the sample is constructed.

Worked example

For estimating a null rate with 95% confidence and a 1% margin of error on a very large table, standard sample-size formulas for a proportion (assuming the true rate is near 0.5, the most conservative assumption for worst-case sample sizing) suggest roughly 9,600 rows regardless of the total population size, once the population is large enough that the finite-population correction becomes negligible (the finite-population correction is a small downward adjustment to the sample-size formula that only matters when the sample itself would be a large fraction of a small total population, for example sampling 5,000 rows out of a 6,000-row table; for a table with millions or billions of rows, the correction shrinks to essentially zero and the plain formula above already gives the right answer), a number the vast majority of practical dashboards or pipelines could compute far more cheaply than a full multi-billion-row scan.

Trade-offs and pitfalls

The pitfall most teams miss is assuming a sample-based estimate of an average rate (like an overall null rate) automatically extends to confidence about a rare, specific event (like "did any row have a catastrophically wrong value"), it does not; a random sample is well-suited to estimating aggregate statistics with a known confidence interval, but is poorly suited to guaranteeing detection of a rare anomaly, which needs either a much larger sample specifically sized for the rare event's expected rate, or a complementary full-scan check targeted narrowly at just that specific rare-event pattern rather than a general-purpose sample.

Direct answer. Write out each source's SELECT explicitly, renaming columns to a common set of names and adding any column that's missing on one side as an explicit NULL (or a sensible default) cast to the right type, so every branch of the UNION ALL produces an identically-shaped row.

Structured elaboration. UNION ALL requires the same number of columns, in the same order, with compatible types, across every branch, it does NOT reconcile mismatched column names or missing columns for you. The fix is mechanical: pick one canonical column list, and for each source, alias its columns to match, and explicitly supply a typed NULL for any column that source doesn't have at all.

Worked example. logs_v1(event_time, event_type): ('2025-01-01 10:00:00', 'view'). logs_v2(ts, type, user_id): ('2025-01-01 11:00:00', 'click', 5) (logs_v2 has an extra user_id column that logs_v1 lacks entirely).

SELECT event_time, event_type, CAST(NULL AS INTEGER) AS user_id FROM logs_v1
UNION ALL
SELECT ts AS event_time, type AS event_type, user_id FROM logs_v2
ORDER BY event_time;

Result: ('2025-01-01 10:00:00', 'view', NULL), ('2025-01-01 11:00:00', 'click', 5). logs_v1's rows correctly show a NULL user_id (since that source never captured it), and both sources' columns are now under one consistent set of names.

Trade-offs and pitfalls. The choices you make for a column that only exists on one side matter: a bare NULL correctly communicates "this source never captured this," and is usually the honest choice; a manufactured default (0, or an empty string) can look like real data to anyone querying the combined view later and should only be used if it's genuinely meaningful, not just to avoid a NULL. It's also worth explicitly casting the NULL to the target type (as shown above), since some engines will otherwise infer a type for an untyped NULL that doesn't match the corresponding column in the other branch, causing a type-mismatch error or an unwanted implicit cast across the whole UNION.

Direct answer

Solve the margin-of-error formula backward for $n$: given a target margin $E$, a confidence level (95%, so $z^{*}=1.96$), and an estimate of the standard deviation from a pilot sample, the required sample size is $n = (z^{*} s / E)^2$, rounded up. With the pilot's $s \approx 4$ and a target margin of 0.5, that comes out to about 246 customers, treated as a starting plan to be revisited once real data comes in.

Structured elaboration

Setting up the formula. A 95% CI for a mean has half-width (margin of error) $E = z^{*} \cdot SE = z^{*} \cdot \frac{s}{\sqrt n}$. Solving for $n$:

Using $z^{*}=1.96$ for planning (not $t$) is a standard simplification: at the sample sizes this formula tends to produce, $t$ and $z$ are close enough that using $z$ up front and refining with $t$ afterward is fine.

Why the pilot is only a starting point. The formula needs a standard deviation, but the true population $\sigma$ for customer lifetime value is unknown; that's exactly what the 40-customer pilot supplies as an estimate. Because it's an estimate from a small sample, it's noisy, and CLV in particular is often right-skewed with a long tail of high-value customers, which can make a 40-person pilot understate the true variance if none of the top-tail customers happened to land in it.

Worked example

Target margin $E = 0.5$, 95% confidence ($z^{*}=1.96$), pilot standard deviation $s = 4$ from $n_{pilot}=40$:

(Verified by direct computation.) If historical response/completion rates for this kind of data pull run around 80%, plan to sample or invite more than 246 to end up with 246 completed observations: $246 / 0.8 = 307.5 \Rightarrow 308$ invites.

Iterative refinement in practice.

1. Collect the initial planned batch (or an early tranche of it).
2. Recompute the sample standard deviation from the larger, more reliable batch. If it's meaningfully different from the pilot's 4, recompute $n$ with the updated $s$; a larger true $s$ means the original plan undershoots the target margin.
3. Once close to the target $n$, switch the final margin-of-error check to the $t$-distribution with $df = n-1$ for the precise value; at $n \approx 246$, $t_{0.975,245} \approx 1.970$ versus $z=1.96$, a difference of about 0.01 in the critical value, small enough that it rarely changes the plan.
4. Most product sample-size calculations stop at step 3. Two further refinements apply only in specific cases: if the population of eligible customers is small relative to $n$ (e.g. a niche segment with only a few thousand total customers), apply a finite-population correction (a downward adjustment to $n$ that accounts for sampling a large fraction of a small, finite population rather than an effectively infinite one), which shrinks the required sample size.
5. If sampling is clustered (e.g. by region or cohort) rather than a simple random sample, inflate $n$ by a design effect, or DEFF (a multiplier that accounts for the extra correlation between units sampled from the same cluster, which reduces how much independent information the same number of clustered units carries compared to a true simple random sample), to account for the extra correlation clustering introduces.

Trade-offs & pitfalls

• Treating the pilot-based $n=246$ as final rather than a planning estimate is the main risk: if the true variance is higher than the pilot suggested (common with skewed CLV data and a small pilot), the study will land with a wider-than-intended interval unless the sample size gets revisited.
• Non-response and attrition are easy to forget until data collection is already underway; inflating for expected response rate up front avoids a late scramble to recruit more.
• The margin-of-error formula assumes simple random sampling; ignoring clustering or stratification in the actual collection design while using the unadjusted formula will understate the sample size actually needed.