10 Walmart Data Analyst Interview Questions & Answers

I would first define the decision, population, unit of analysis, refund rate, suspicious-fraud refund rate, and retention rate. Then I would compare high-theft products with a credible comparison group before and after the policy change using difference-in-differences to estimate the policy-attributable refund lift. I would separately classify refunds as likely fraud or genuine dissatisfaction using behavioral, fulfillment, and customer-contact signals, then apply the same treated-versus-comparison change analysis to the suspicious-fraud refund rate to estimate incremental fraud. I would drill down by metro, product, and customer segment, target controls only where risk is concentrated, and monitor fraud reduction, valid refunds, repeat purchase, customer satisfaction, contact rate, and false positives.

The goal is not simply to explain why refunds rose. The business decision is whether stricter controls are justified, where they should apply, and how to avoid hurting good customers. I would measure the policy-attributable increase in refunds, then separate the portion that looks suspicious from genuine service problems. I would compare high-theft products with a credible comparison group over the same pre- and post-policy periods, examine metro and customer segments, quantify the tradeoff between fraud loss and retention risk, and recommend targeted actions with clear monitoring guardrails.

1. What exactly changed in the refund policy, when did it take effect, and did every metro, product, or customer receive the change at the same time?
2. Which products are classified as high-theft, and was that classification defined before the policy change?
3. What is the operational unit for analysis: order, order line, refund event, customer, or another grain?
4. Which refund reasons, fulfillment signals, customer contacts, and reviewed fraud labels are available to distinguish likely fraud from genuine dissatisfaction?
5. Is there a credible comparison group that was less affected by the policy, such as lower-theft products with similar pre-policy refund trends?
6. Which retention outcome matters most to the business, and is the 30-day repeat-purchase definition shown in the analysis the accepted guardrail?
7. Were there concurrent changes in delivery operations, substitutions, pricing, promotions, fraud systems, product mix, or instrumentation that could also affect refunds?
8. What level of customer friction is acceptable, and can new controls be tested or phased in rather than applied globally?

The decision is whether to tighten refund controls after the policy change, and if so, for which products, metros, and customers. I would avoid a blanket restriction until the analysis shows where the incremental risk is concentrated. The desired outcome is to recover fraud losses while preserving legitimate service recovery and retention for good customers.

I would use online grocery orders that were delivered or picked up and define the analytical grain consistently, ideally at the order-line level when refund eligibility and product risk are item-specific. I would keep the same eligibility definition before and after the policy change.

The core measures are:

• Refund rate = refunded eligible lines / eligible lines.
• Suspicious-fraud refund rate = consistently classified suspicious-fraud refunded lines / eligible lines.
• Retention rate = customers who place another order within the agreed retention window / eligible customers in the cohort. The diagram uses 30-day repeat purchase as the guardrail.
• Customer-experience measures = customer satisfaction or NPS/CSAT where available, valid-refund rate, and customer-contact rate.
• Operational measures = refund volume and false-positive rate for any controls or fraud classification.

I would preserve the same population, eligibility rules, denominator, reporting interval, policy boundary, and time-zone treatment across groups.

I would combine orders and refunds, the product catalog including the high-theft flag, customer refund history, fulfillment and quality signals such as substitutions or damaged-item reports, customer-service contacts, and location fields such as metro, region, or store.

Before analysis I would check missing or duplicate orders, refund-reason completeness, consistent product mapping, the exact policy-change timestamp, time-zone alignment, test or bot records, outliers, data freshness, and instrumentation changes. I would also reconcile refund counts and eligible-line totals to a trusted source. If a data definition changed near the policy date, the observed jump may be measurement rather than behavior.

A raw post-policy increase is descriptive evidence, not proof that the policy caused fraud. I would use a difference-in-differences design when a credible comparison group exists.

High-theft products are the treated group. The comparison group should contain products that were less exposed to the policy or its fraud incentive but had reasonably comparable pre-policy refund trends and customer or operational conditions. I would inspect several pre-policy periods rather than matching only the average level.

The calculation is: Policy-attributable refund lift = (post refund rate - pre refund rate for treated) - (post refund rate - pre refund rate for comparison).

This removes the change that also occurred in the comparison group. I would interpret the estimate causally only when the comparison provides a credible counterfactual, pre-policy trends are reasonably parallel, and there is no material differential concurrent change that explains the post-policy gap. I would investigate delivery quality, substitutions, product mix, promotions, seasonality, or instrumentation and report uncertainty rather than treating the estimate as exact.

The difference-in-differences result above estimates incremental refund lift, not automatically incremental fraud. Some extra refunds may be legitimate dissatisfaction.

I would classify refunds using multiple consistent signals. Patterns such as many refunds in a short period, repeated claims on high-theft products, or no matching delivery or quality issue may increase suspicion. Signals such as damaged items, substitutions, delivery problems, or an isolated first-time complaint may support genuine dissatisfaction. These are indicators, not proof by themselves.

If reviewed fraud labels are available, I would use them to validate the classification. Otherwise, I would use a transparent, consistently applied suspicious-fraud rule or model and validate it against reviewed cases where possible, while tracking false positives. I would then estimate incremental fraud by applying the same treated-versus-comparison pre/post change analysis to the consistently classified suspicious-fraud refund rate. This keeps the fraud estimate distinct from the broader refund effect.

I would segment the result by metro or region, customer history, and product category. Customer segments could include new versus existing customers and customers with different prior refund histories. Product segments should distinguish high-theft categories from comparable lower-risk products.

The purpose is diagnostic, not just descriptive. If the refund lift is concentrated in a few metros while fulfillment-quality complaints also rise there, I would investigate operational causes before tightening fraud controls. If suspicious refunds rise primarily among repeated claimants on high-theft products without a matching service-quality problem, that supports a more targeted fraud response.

I would avoid making decisions from very small segment samples. Sparse groups should be combined, reported with wider uncertainty, or treated as directional evidence only.

For each potential intervention, I would estimate expected fraud loss avoided and compare it with customer and operational costs. The relevant costs include legitimate refunds delayed or denied, extra verification steps, support contacts, false positives, and any reduction in repeat purchase or satisfaction.

I would not invent a single monetary threshold without business inputs. Instead, I would show decision scenarios: how much suspicious fraud would need to be prevented for a control to justify a given increase in friction or retention loss. This makes the tradeoff explicit for the decision owner.

If the evidence supports concentrated incremental fraud, I would avoid a universal restriction. I would apply step-up verification or additional evidence requirements only to higher-risk combinations of product, customer behavior, and metro. Examples consistent with the diagram include requiring a photo for selected high-risk claims, applying refund limits where justified, or using additional verification for high-risk orders.

For low-risk, trusted customers, I would keep the process fast. At the same time, I would address genuine operational causes such as item accuracy, packing, substitution quality, or delivery issues where the analysis shows legitimate dissatisfaction.

Where feasible, I would test the intervention with an A/B test or phased rollout. Randomization gives a cleaner estimate of the control's effect on suspicious fraud, legitimate refunds, and retention than relying only on another observational comparison.

The monitoring plan should include the suspicious-fraud refund rate with uncertainty, valid-refund rate, refund volume, 30-day repeat-purchase rate or the agreed retention KPI, customer satisfaction, customer-contact rate, and false-positive rate. I would review these overall and by metro, product, and customer segment.

I would also watch for displacement, such as suspicious activity moving to other product categories or claim types after controls are introduced. If fraud falls but legitimate customers experience a meaningful retention or satisfaction decline, I would loosen the threshold or narrow the intervention. If suspicious activity moves elsewhere, I would update the segmentation rather than assuming the problem is solved.

The final recommendation should therefore be conditional: use targeted controls only where the incremental suspicious-fraud evidence is strong, maintain low friction elsewhere, address genuine service failures separately, and keep measuring both fraud recovery and customer impact so the policy can be revised as new evidence arrives.

1. Define the decision, eligible population, analytical grain, policy boundary, and KPI denominators.
2. Validate orders, refunds, product mapping, customer history, fulfillment signals, locations, timestamps, duplicates, missingness, outliers, freshness, and instrumentation.
3. Select high-theft products as treated units and identify a credible comparison group with similar pre-policy behavior.
4. Compare pre-policy trends and investigate concurrent changes that could invalidate the counterfactual.
5. Estimate policy-attributable refund lift using difference-in-differences: (Post - Pre in treated) - (Post - Pre in comparison).
6. Separately classify likely fraud versus genuine dissatisfaction using consistent behavioral, fulfillment, and customer-contact signals, validating labels or rules where possible.
7. Apply the same treated-versus-comparison pre/post analysis to the suspicious-fraud refund rate to estimate incremental fraud.
8. Segment the estimates by metro, customer cohort, and product category while accounting for sparse groups and uncertainty.
9. Quantify fraud-loss reduction against retention, false-positive, customer-contact, and service-recovery costs.
10. Recommend targeted controls for high-risk segments, keep low friction for low-risk customers, and address genuine operational causes separately.
11. Test or phase in controls where possible.
12. Monitor fraud, valid refunds, retention, satisfaction, contact rate, false positives, and displacement; adjust thresholds and policy based on measured results.

The main cost is analytical rather than algorithmic. The data must be joined and summarized across orders, refunds, products, customers, fulfillment signals, locations, and time periods. More segmentation increases computation and reporting work, but the larger risk is statistical: very small metro or customer groups create noisy estimates. A simpler rule-based fraud classification is easier to explain and govern but may miss complex behavior; a predictive model can capture more patterns but needs reliable labels, validation, monitoring, and stronger governance. A broad restriction is operationally simple but risks retention, while targeted controls require more monitoring but reduce unnecessary customer friction.

Calling the entire post-policy refund increase fraud; using a comparison group without checking pre-policy trends; changing the denominator or eligibility rules across periods; mixing order, order-line, refund, and customer grain; ignoring concurrent changes in fulfillment, substitutions, promotions, seasonality, product mix, or instrumentation; treating suspicious signals as proven fraud; inventing precision from sparse metro or customer segments; optimizing only for fraud reduction while ignoring valid refunds and retention; applying blanket restrictions when the effect is concentrated; and failing to monitor false positives, customer contacts, satisfaction, or fraud displacement after the intervention.

Present the answer in this order: decision and metrics, data checks, causal comparison, fraud-versus-genuine classification, segmentation, tradeoff, targeted action, and monitoring. State explicitly that difference-in-differences first estimates policy-attributable refund lift; incremental fraud requires a consistent suspicious-fraud classification and a separate suspicious-fraud outcome. That distinction makes the reasoning much stronger.

I would treat the disagreement between the dashboard, store teams, and lost sales as evidence that the metric or its data pipeline may be wrong. I would start at the source and verify the expected fact and snapshot grain, row counts, keys, join cardinality, nulls, duplicates, and referential integrity. Then I would check time alignment, late-arriving dimensions, and Type 2 history. Next I would confirm the in-stock numerator, denominator, filters, and scope, reconcile source-to-model-to-dashboard totals, and slice differences by store, SKU, category, region, and date. Invalid or uncertain records would be quarantined with reasons rather than silently dropped. I would only trust the dashboard after reconciliation, then add alerts, ownership, lineage, audit trails, and regression checks.

The task is to explain why the dashboard says availability is improving while real-world signals say the opposite. I would not assume either side is correct. I would trace the metric from source data through transformations to the dashboard, checking whether each row represents the intended business event, whether joins or time windows distort results, and whether the KPI definition matches the business meaning. I would reconcile totals, investigate affected segments, quarantine questionable records, document limitations, and only restore trust after the source, model, and dashboard agree.

1. What is the approved business definition of in-stock rate, including numerator, denominator, exclusions, and reporting window?
2. What should one fact or inventory snapshot row represent: store-SKU-time, store-SKU-day, or another grain?
3. Which systems are the sources of truth for inventory or availability, sales and lost sales, store and item dimensions, and the calendar?
4. Are inventory snapshots and sales measured at the same timestamp, timezone, refresh cadence, and reporting period?
5. Are store or product attributes maintained as Type 2 history, and can dimension records arrive after facts?
6. When did the divergence first appear, and is it concentrated in certain stores, SKUs, categories, regions, or dates?
7. Were there recent schema, transformation, filtering, dimension, or KPI-definition changes?

1. Establish the expected grain and source of truth. I would first define what one row means at every important stage. The inventory or stock source may be at a store-SKU-timestamp grain, while the reporting fact may intentionally use a store-SKU-day snapshot grain. I would confirm the actual grain rather than assume it. I would compare row counts before and after transformations and investigate unexpected growth, loss, or mixing of grains. A grain mismatch can distort the numerator, denominator, or both and make an aggregate KPI look better even while shelf conditions worsen.

I would profile the source data for completeness, accuracy, consistency, validity, uniqueness, and timeliness. Completeness means expected records and fields are present. Accuracy means values agree with the source of truth. Consistency means equivalent fields and business rules agree across systems. Validity means values satisfy allowed rules or ranges. Uniqueness means business records are not duplicated unexpectedly. Timeliness means the data is current for the reporting window. I would check nulls, duplicates, ranges, unexpected categories, schema drift, missing periods, and freshness.

2. Validate keys, join cardinality, and referential integrity. I would trace the fact data into store, item, category, calendar, and other dimensions. For each join, I would verify the expected relationship and compare row counts before and after the join. For a standard dimension-to-fact relationship, the intended pattern is normally one valid dimension version to many fact rows. If a dimension join unexpectedly multiplies fact rows, that fanout can distort the in-stock calculation and make the KPI improve falsely. I would also check duplicate dimension keys, missing foreign keys, unmatched fact rows, and any many-to-many relationship that was not explicitly designed.

3. Check time alignment and historical dimensions. Inventory snapshots, sales, lost-sales evidence, and store observations must be compared over like-for-like periods. I would verify timestamps, timezone rules, reporting cutoffs, refresh timing, and whether the dashboard uses the same date range as the operational evidence. A current inventory snapshot compared with a different sales window can create an artificial disagreement.

For late-arriving dimensions, I would identify facts whose associated dimension record was not available when the fact was processed. For Type 2 history, I would check effective date, expiration date, current-row indicator, and the dimension version used by each fact. Historical facts should join to the dimension version that was valid at the fact's event time. Joining old facts to today's store, product, or category attributes can move records between segments and change the reported trend.

4. Rebuild and verify the KPI definition. The approved diagram expresses the metric as in-stock rate = number of in-stock opportunities / total stock opportunities. I would verify that this is the actual approved business definition before trusting the result. I would inspect the numerator, denominator, eligible population, filters, exclusions, store and SKU scope, and time window. I would specifically test whether the denominator has changed, whether out-of-stock observations are being excluded, or whether assortment and eligibility rules changed in a way that raises the reported rate without improving shelves.

I would independently reproduce the KPI from the lowest trustworthy grain rather than validating only the final aggregate. That helps distinguish a calculation defect from a genuine business change.

5. Reconcile source to model to dashboard. Reconciliation means proving that important counts and control totals remain explainable as data moves through the pipeline. I would compare source row counts and control totals with the transformed model and then with the dashboard. I would reconcile inventory or availability observations, eligible opportunities, in-stock opportunities, relevant sales or lost-sales measures, and record counts where those fields are available. Every mismatch should be classified as either an expected transformation or an exception that needs investigation.

Data lineage is the trace of how a dashboard field was produced. I would follow the suspicious in-stock metric backward through dashboard logic, reporting logic, transformations, joins, and source fields until I can determine whether the defect started in a source system or was introduced during transformation.

6. Find where the divergence occurs. I would slice the comparison by store, SKU, category, region, date, and other approved segments. The purpose is to identify where the dashboard trend diverges from lost-sales trends and store-team observations. A broad average can hide a problem that affects only certain stores, items, periods, or dimension versions.

I would keep observation separate from interpretation. A segment-level mismatch tells me where to investigate; it does not prove a particular join, source, or transformation caused the issue until lineage and reconciliation confirm it.

7. Quarantine exceptions instead of silently repairing them. Records with missing keys, out-of-range dates, unknown categories, invalid values, conflicting dimension history, or other uncertain conditions should enter an exception path with a reason. I would retain them for review and route them to the appropriate owner. I would not silently drop, overwrite, impute, or repair them without an auditable rule because doing so can hide the defect being investigated. The exception report should preserve enough evidence to reproduce the problem and distinguish source defects from transformation defects.

The main risks are fact or snapshot grain mismatch, join fanout, duplicate or missing keys, stale snapshots, timezone or reporting-window mismatch, late-arriving dimensions, incorrect Type 2 joins, denominator or filter changes, schema drift, and segment-level concentration. Another risk is false confidence from aggregate reconciliation: total counts may match while records are assigned to the wrong store, SKU, category, date, or historical dimension version, so both total-level and segment-level checks matter.

There is also a tradeoff between strict validation and data availability. Blocking all questionable data can delay reporting, while publishing uncertain data can mislead decision makers. I would use explicit quality rules, quarantine invalid records where appropriate, define safe-failure behavior, and document which data remains uncertain rather than presenting an unreliable output as trusted.

8. Restore trust and prevent recurrence. I would consider the dashboard trusted only after source, model, and dashboard logic reconcile and the discrepancy is explained. I would document the KPI definition, grain, filters, reporting window, assumptions, and known limits; align those definitions with store teams; and assign clear ownership.

Then I would add automated checks for row-count changes, nulls, duplicates, referential integrity, schema drift, freshness, and unexpected ranges or categories. I would add reconciliation control totals, alerts, exception reports, lineage, an audit trail, regression checks, and a recovery plan. The prevention loop should continuously test that future data or model changes do not recreate the same misleading trend.

1. Define the decision, approved in-stock KPI, source systems, reporting window, expected row grain, and source-of-truth boundaries.
2. Profile each source for row counts, nulls, duplicates, ranges, unexpected categories, schema drift, freshness, completeness, validity, and uniqueness.
3. Compare fact and snapshot grain before and after transformations; flag unexpected row growth, row loss, or mixed grain.
4. Validate primary and foreign keys, referential integrity, and expected join cardinality; test for row multiplication and unmatched facts.
5. Align inventory snapshots, sales or lost-sales windows, store reporting periods, timezone rules, cutoffs, and refresh timing.
6. Audit late-arriving dimensions and Type 2 history using the dimension version valid at each fact's event time.
7. Rebuild the in-stock KPI from the lowest trustworthy grain; confirm numerator, denominator, eligible population, filters, exclusions, and reporting window.
8. Reconcile control totals from source to transformed model to dashboard and classify every mismatch as expected or defective.
9. Slice the divergence by store, SKU, category, region, date, and other approved segments to localize the problem.
10. Trace discrepancies through lineage to determine whether each defect began in a source system or transformation.
11. Quarantine invalid or uncertain records with a reason and owner rather than silently dropping or changing them.
12. Document impact, assumptions, and known limits; correct the root cause, rerun reconciliation, and add alerts, ownership, audit trails, regression checks, and recovery evidence.

The cost depends on how much data and how many pipeline stages must be checked. Profiling and reconciliation usually require scanning the relevant rows, so the work grows with the amount of data. Duplicate detection, grouping, and join-cardinality checks can cost more because they compare or aggregate keys. Type 2 history and segment analysis add work because facts must be matched to historical dimension versions and broken down across multiple dimensions. For fast diagnosis, I would narrow the time window and affected segments first, then run full reconciliation and regression checks before declaring the dashboard trusted.

Common mistakes are trusting the dashboard aggregate first; checking only the KPI formula but not its grain, denominator, filters, and eligibility rules; ignoring row multiplication after joins; joining historical facts to the current Type 2 dimension row; comparing sources with different timestamps, timezones, or reporting windows; reconciling only totals while missing segment-level misclassification; assuming store feedback alone proves the model is wrong; silently dropping bad records; inventing expected values; and correcting the visible metric without adding lineage, alerts, ownership, audit trails, and regression controls.

Present the audit as a source-to-report trace: grain and data quality first, then joins and history, then time alignment and KPI logic, then reconciliation and affected segments, and finally exception handling, ownership, and prevention. Emphasize that you would not decide which signal is right until the evidence reconciles.

I would define refund rate as refunded orders divided by eligible completed orders using the same eligibility rules for both weeks. First I would validate counts, sample sizes, seasonality, promotions, policy changes, product mix, customer or channel mix, and distribution-center mix. For a simple unadjusted comparison, I would use a two-proportion z-test and report the risk difference with a 95% confidence interval. Because several regions and mix shifts are involved, I would also fit an adjusted logistic regression with week, region, and relevant covariates. I would conclude the shift is supported only if the adjusted evidence is statistically credible and practically meaningful.

The goal is to decide whether the observed 8% week-over-week rise in refund rate reflects a real underlying change or could be explained by random variation or a changing mix of orders. I would keep the metric definition identical across weeks, use order-level refunded versus not-refunded outcomes, check sample size and data quality, and look for seasonality, promotions, policy changes, product mix, customer mix, and distribution-center shifts. Then I would estimate the change, report a 95% confidence interval, and use an adjusted regional model before making a business conclusion.

1. Is the reported 8% change a relative increase or an 8-percentage-point increase?
2. Are refund eligibility rules and completion definitions identical for both weeks?
3. Do we have order-level data with week, region, refund status, product variant, customer or channel attributes, promotions, and policy changes?
4. Were there known holidays, fulfillment disruptions, pricing changes, SKU changes, return-policy changes, or other events during either week?
5. Is the main decision about the overall product-level change, or do we also need separate conclusions for individual regions?
6. What change would be large enough to matter operationally or financially?

I would start by defining the population and grain. The population is eligible completed orders for the same product in the relevant distribution-center regions. The unit of analysis is an order, with a binary outcome: refunded or not refunded. The primary metric is refund rate = refunded orders / eligible completed orders. The denominator must use the same eligibility rules in both weeks. Changing order volume changes the precision of the estimate, but it should not change the definition of the rate.

Next I would define the estimand as the week-over-week change in refund probability. Let p_this be the population refund probability this week and p_prior the population refund probability in the prior week. For a general change test, the null hypothesis is H0: p_this = p_prior, and the alternative is H1: p_this != p_prior. Because the observed direction is upward, I would still report the estimated direction and size explicitly rather than treating the p value as the effect.

Before testing, I would check comparability. I would reconcile eligible-order counts and refund counts by week and region, check missing or duplicate orders, confirm consistent eligibility and refund definitions, and inspect sample sizes. I would also check calendar and seasonal effects, promotions, fulfillment-channel changes, customer mix, SKU or pack-size changes, return-policy changes, and distribution-center mix shifts. These factors can create an apparent week effect even when the underlying refund propensity has not changed.

For a simple unadjusted comparison of two large independent weekly samples, I would use a two-proportion z-test. The effect size I care about is the risk difference, Delta p = p_this - p_prior, together with its 95% confidence interval. A p value tells me how compatible the observed data are with the null model under its assumptions; it does not tell me the size of the change or whether the change matters to the business. If sample sizes or event counts are too small for the large-sample approximation to be reliable, I would use an exact or otherwise appropriate small-sample method instead of forcing the z-test.

Because the question includes several regions and possible mix shifts, the adjusted analysis is more important. I would fit an order-level logistic regression with refund status as the outcome and include a week indicator, region effects, and relevant observed covariates such as seasonality, product mix, customer or channel mix, and promotions when those variables are available and justified. The week coefficient represents the adjusted week comparison on the log-odds scale; for communication, I would convert the model result to an adjusted difference in refund probability or another directly interpretable effect measure with a 95% confidence interval.

I would also check whether the week effect differs by region using a region-by-week interaction when heterogeneity matters. If the business only needs one adjusted overall conclusion, I would focus inference on that overall week effect. If separate regional claims are required, I would control the false-discovery risk across those multiple tests, for example with a false discovery rate procedure, rather than interpreting many unadjusted regional p values independently.

If observations are correlated within regions or another grouping unit, I would use an inference method that matches that dependence structure. Cluster-robust standard errors can be appropriate when there are enough independent clusters for the approximation to be credible; with only a few clusters, I would not rely on that correction mechanically.

Refund amounts are a different outcome from refund incidence. The primary question is about the refund rate, so I would not replace the binary refund metric with average refund dollars. If refund amounts are highly skewed, I would summarize them separately using the median, quantiles, or another distribution-aware analysis. That tells us whether monetary severity changed without confusing it with the probability that an order was refunded.

The confidence interval is central to the conclusion. I would report the adjusted estimate of Delta p and a 95% confidence interval around it. If the interval is entirely above zero, that supports a positive shift under the assumptions of the model. If it includes zero, the data do not clearly distinguish an increase from no change at that confidence level. The confidence interval quantifies sampling uncertainty around the estimated effect; it does not remove measurement error, selection bias, omitted confounding, or other design problems.

Finally, I would separate statistical significance from practical significance. Even a precisely estimated increase may be too small to justify operational action, while a business-important estimated increase may remain uncertain when data are sparse. I would compare the estimated effect and its confidence interval with a pre-agreed business threshold, investigate root causes such as product defects, shipping issues, or policy changes when the signal is supported, and continue monitoring to see whether the change persists. The final statement should be no stronger than the evidence: an observational week-over-week comparison can support an adjusted association, but it does not by itself prove causation.

1. Define the population as eligible completed orders for the same product and relevant regions, with one row per order and refund status as the binary outcome.
2. Define refund rate consistently as refunded orders divided by eligible completed orders for each week.
3. Reconcile counts, duplicates, missing data, eligibility rules, refund definitions, and sample sizes by week and region.
4. Check comparability for seasonality, holidays, promotions, pricing, SKU or pack-size changes, return-policy changes, customer or channel mix, fulfillment changes, and distribution-center mix shifts.
5. State H0: p_this = p_prior and H1: p_this != p_prior.
6. For the unadjusted large-sample comparison, estimate the risk difference and use a two-proportion test with a 95% confidence interval.
7. Fit an adjusted logistic regression with week, region, and relevant measured covariates; convert the adjusted week effect to an interpretable refund-probability difference or comparable effect measure with a 95% confidence interval.
8. Check a region-by-week interaction if regional heterogeneity matters.
9. If making separate regional claims, adjust for multiple testing; otherwise focus on the single adjusted overall effect.
10. Use cluster-robust inference only when the dependence structure and number of independent clusters make it appropriate.
11. Analyze skewed refund amounts separately with medians or quantiles instead of replacing the refund-rate metric.
12. Report the adjusted effect size, 95% confidence interval, statistical evidence, business threshold, limitations, and monitoring plan.

The simple proportion comparison is inexpensive because it needs only refund and eligible-order counts for the two periods. The adjusted regression is more computationally expensive because it works at order level and may include region and mix variables, but for normal analytics workloads the larger cost is usually data preparation and validation rather than model fitting. Adding many interactions or region-specific tests increases complexity and makes interpretation harder, so I would add them only when they answer a real business question. More data can improve precision, but more rows do not correct bias, inconsistent definitions, or omitted confounders.

Common mistakes are comparing refund counts instead of refund rates; changing the denominator or eligibility definition between weeks; treating the reported 8% change as automatically meaningful without clarifying whether it is relative or absolute; using a p value as the effect size; ignoring confidence intervals; averaging regional percentages without considering their volumes or mix; testing many regions independently without multiple-testing control; ignoring seasonality, promotions, return-policy changes, SKU changes, customer mix, or distribution-center mix; treating skewed refund amounts as if they were the same outcome as refund incidence; interpreting a logistic-regression coefficient directly as a probability difference; using cluster-robust inference with too few independent clusters; and claiming causation from an observational week-over-week comparison.

Present the answer in five steps: define the rate and denominator, validate comparability, test the binary outcome, adjust for regions and mix, then report the adjusted effect with a 95% confidence interval and business significance. Emphasize that volume affects precision, not the rate definition, and that a p value alone is never the conclusion.

I would first define the previous complete PostgreSQL week as a half-open interval: from the start of last week through, but not including, the start of the current week. I would filter order_items to that period, then join each item to the Type 2 product row where purchase_ts is within that version’s effective interval. Because bad dimension data can create overlapping matches, I would use ROW_NUMBER() per order_item_id, ordered by the newest effective_start_ts and then product_version_id, and keep rn = 1. Finally, I would calculate item net sales and SUM them by category, then validate row counts and control totals.

The goal is to calculate net sales for the previous fully completed week and assign every purchased item to the product category that was valid at the time of purchase. The order-item table has one row per purchased item, while the Type 2 product dimension can contain several historical versions of the same product. The query therefore needs a time-based join, not just a product_id join. It also needs protection against overlapping dimension records, clear week boundaries, null handling for discounts and open-ended versions, and checks that joining did not duplicate or lose sales.

1. Which SQL dialect should I use? I will use PostgreSQL here because the approved diagram uses PostgreSQL syntax.
2. Does discount_amount represent the total discount for the order-item row, so net sales is quantity * unit_price - discount_amount?
3. Are purchase_ts, effective_start_ts, and effective_end_ts stored in the same time zone or normalized consistently?
4. Is effective_end_ts exclusive, meaning a version is valid for [effective_start_ts, effective_end_ts)?
5. Should a NULL effective_end_ts mean that the product version is still current?
6. If overlapping Type 2 rows exist, is choosing the version with the latest effective_start_ts, then highest product_version_id, the approved business rule?
7. Should order items with no valid product-dimension match be excluded from the final result, or reported separately as a data-quality exception?

Start with the grain. order_items has one row per order item, identified by order_item_id. product_dim has one row per product version, identified by product_version_id, and several versions can share the same product_id. The requested output has one row per category for the previous complete week.

First, define the reporting period explicitly. In PostgreSQL, date_trunc('week', current_timestamp) gives the start of the current week. Subtract one week for the start of the previous week. Use a half-open interval: purchase_ts >= start_ts and purchase_ts < end_ts. Half-open intervals avoid double-counting a timestamp that sits exactly on a week boundary.

Second, filter order_items before the dimension join. This limits the working fact rows to the requested week. The item-level net-sales expression shown in the diagram is quantity * unit_price - COALESCE(discount_amount, 0). This assumes discount_amount is the discount for the complete order-item row. If its business meaning is different, I would change the formula only after confirming that definition.

Third, perform a point-in-time Type 2 join. Match on product_id and require purchase_ts >= effective_start_ts. For the end of the validity interval, use purchase_ts < COALESCE(effective_end_ts, 'infinity'::timestamp). A NULL end timestamp therefore represents an open-ended current version. The half-open [start, end) rule also prevents two normally adjacent versions from both matching at their shared boundary.

Fourth, protect against overlapping dimension rows. Correct Type 2 data should normally produce one valid product version for each product and purchase time, but bad data can contain overlapping ranges. After the temporal join, assign ROW_NUMBER() within each order_item_id. Order by effective_start_ts DESC so the most recently effective matching row wins, then product_version_id DESC as a deterministic tie-breaker. Keep only rn = 1. This stops an accidental one-to-many join from multiplying the item’s sales.

Fifth, aggregate only after restoring the intended fact grain. SUM(net_sales) and GROUP BY category. The final output grain is one row per category, with last week’s net_sales.

The INNER JOIN behavior matters. An order item with no valid product version will not appear in the joined result. That may be acceptable for the requested result, but it is also a data-quality signal. I would validate that the count of rn = 1 rows equals the count of filtered order items. If it does not, I would investigate missing Type 2 coverage rather than silently assuming the total is complete.

I would also compare SUM(net_sales) after rn = 1 with the item-level control total from the filtered fact rows, using the same net-sales formula. These totals should match when every order item has one selected dimension row. I would separately check for NULL discounts, overlapping effective ranges, missing product matches, and timestamp or time-zone inconsistencies. For performance, filtering the fact table early reduces work, but correctness comes first; indexes or physical design should only be discussed after knowing the actual schema, data volume, and query plan.

1. Define start_ts as date_trunc('week', current_timestamp) - interval '1 week' and end_ts as date_trunc('week', current_timestamp).
2. Filter order_items to purchase_ts >= start_ts and purchase_ts < end_ts.
3. Keep the fact grain at one row per order_item_id and compute net_sales as quantity * unit_price - COALESCE(discount_amount, 0).
4. Join product_dim on product_id plus the Type 2 validity conditions purchase_ts >= effective_start_ts and purchase_ts < COALESCE(effective_end_ts, 'infinity'::timestamp).
5. Use ROW_NUMBER() PARTITION BY order_item_id ORDER BY effective_start_ts DESC, product_version_id DESC to resolve overlapping matches deterministically.
6. Keep rn = 1 so each matched order item contributes once.
7. GROUP BY category and SUM(net_sales).
8. Validate that selected joined-row count matches the filtered fact-row count and that the selected item-level net-sales total matches the filtered fact control total.

The main cost is filtering the order-item rows for one week and finding the matching historical product version for each item. If the database can efficiently locate order_items by purchase_ts and product versions by product_id and validity timestamps, much less data needs to be examined. ROW_NUMBER() also has to order any multiple matches within each order_item_id. The exact runtime depends on table sizes, indexes, statistics, and the execution plan, so I would not claim a specific complexity or performance improvement without those details.

PostgreSQL implementation consistent with the approved diagram. The first CTE filters the previous complete week. The second CTE performs the point-in-time Type 2 join, calculates item net sales, and assigns ROW_NUMBER() so overlapping dimension matches can be reduced to one deterministic row per order item. The final query keeps rn = 1 and aggregates net sales by category.

Common mistakes are joining only on product_id and therefore using the wrong historical category; filtering with an ambiguous idea of 'last week' instead of an explicit complete-week interval; using inclusive end boundaries that can double-match adjacent Type 2 versions; aggregating before resolving one-to-many dimension matches; using ROW_NUMBER() without a deterministic tie-breaker; treating NULL effective_end_ts as an ordinary comparison value instead of an open-ended version; forgetting COALESCE for a nullable discount; and failing to check whether an INNER JOIN dropped order items with no valid product version.

Explain the grain before the SQL: one order-item fact row must contribute at most once, even though a product can have many historical dimension rows. Then walk through week filtering, point-in-time joining, deterministic deduplication, aggregation, and validation in that order.

I would first aggregate the sales table to one row per store, SKU, and complete week for the two weeks immediately before the current week. Then I would pivot those weekly totals into previous-week and current-week sales, keep only SKUs with at least $1,000 in both weeks, calculate growth as (current - previous) / previous, and use ROW_NUMBER() partitioned by store. I would order by growth descending, then current-week sales descending, then SKU ascending so ties are deterministic, and finally keep rows where rn <= 3.

The task is to find the three SKUs growing fastest inside each store by comparing the two latest completed weeks. The important part is the order of operations: first restrict the source data to those two complete weeks, then aggregate transaction-level rows to weekly store-and-SKU totals, then apply the $1,000 threshold to both weeks. After that, calculate week-over-week growth and rank SKUs separately inside each store. A deterministic secondary and tertiary sort makes the result repeatable when growth values tie. The final output has at most three rows per store.

1. Should a week follow PostgreSQL's Monday-based date_trunc('week', ...) boundary, and is the database/session time zone the intended reporting time zone?
2. Is net_sales already the final amount after returns and discounts, as represented in the supplied sales fact table?
3. Can store_id, sku, sale_ts, or net_sales be NULL, and should invalid-key rows be excluded before aggregation?
4. Should ties still return exactly three rows per store? I would use ROW_NUMBER() because the requirement asks for the top three and deterministic tie-breaking.

The source table is sales, with transaction or line-item grain and the fields store_id, sku, sale_ts, and net_sales. Because the question does not require a join, I would avoid adding one; that also avoids accidental row multiplication. The first CTE, weekly_sales, filters out the current in-progress week and keeps only the two preceding complete weeks. It groups by store_id, sku, and date_trunc('week', sale_ts) and sums net_sales, so the grain becomes one row per store, SKU, and week.

Next, pivoted converts those two weekly rows into prev_week_sales and curr_week_sales using conditional aggregation. The HAVING clause requires at least $1,000 in each week. Applying that rule after weekly aggregation is important: testing transaction rows individually would answer a different question. It also guarantees the previous-week denominator is at least 1,000, so the growth division cannot be a divide-by-zero case for retained rows.

Week-over-week growth is (curr_week_sales - prev_week_sales) / prev_week_sales. Positive values mean sales increased; negative values mean they decreased. Because net_sales is numeric in the approved diagram, PostgreSQL keeps the calculation in an exact numeric type rather than using integer division.

The ranked CTE uses ROW_NUMBER() with PARTITION BY store_id, which restarts numbering for each store. The primary ordering is growth descending. To break equal growth values deterministically, it then orders by curr_week_sales DESC and sku ASC, matching the approved diagram. ROW_NUMBER() is preferable to RANK() here because RANK() can produce more than three rows when multiple SKUs tie at the cutoff, while this requirement asks for the top three with deterministic tie-breaking.

The final query keeps rn <= 3 and orders by store_id, rn, and sku. The output grain is one row per selected store-and-SKU combination, with at most three rows per store.

For data checks, I would confirm that the two selected week boundaries are complete and use the intended reporting time zone. I would compare the weekly SUM(net_sales) totals with a trusted sales control total, check that the aggregated CTE has at most one row per store_id, sku, and week_start, and verify every retained SKU has at least $1,000 in both weeks. SUM ignores individual NULL net_sales values; if all values for a group are NULL, the sum is NULL and the threshold comparison does not pass. I would also investigate NULL store or SKU keys rather than silently treating them as valid business entities.

From a performance perspective, the expensive work is usually scanning the two-week slice, grouping transaction rows, and sorting each store's eligible SKUs for the window function. The query intentionally aggregates before ranking, which reduces the number of rows entering the ranking step. Any index recommendation would depend on the actual table size, distribution, and execution plan, so I would not invent one without that evidence.

1. Define current_week_start = date_trunc('week', CURRENT_DATE) and exclude the current in-progress week.
2. Filter sales to timestamps from current_week_start - interval '2 weeks' inclusive through current_week_start exclusive.
3. Aggregate to one row per store_id, sku, and week_start with SUM(net_sales).
4. Pivot the older complete week into prev_week_sales and the newer complete week into curr_week_sales.
5. Keep only store-SKU pairs with at least $1,000 net sales in each week.
6. Compute (curr_week_sales - prev_week_sales) / prev_week_sales as week-over-week growth.
7. Assign ROW_NUMBER() within each store, ordering by growth DESC, current-week sales DESC, and SKU ASC.
8. Return rows with rn <= 3, ordered by store, rank, and SKU.
9. Validate week boundaries, weekly totals, one-row-per-grain uniqueness, thresholds, and final row counts.

Let N be the transaction rows in the two-week range and K be the eligible aggregated store-SKU rows. The database first scans and groups the N rows, typically using hashing or sorting. Ranking then sorts eligible SKUs inside each store, which is roughly O(K log K) overall as a simple upper-bound explanation. Aggregating before the window function is important because K should usually be much smaller than the raw transaction count. Actual runtime depends on data volume, distribution, memory, indexes, and the PostgreSQL execution plan.

This PostgreSQL solution matches the approved diagram: it uses the two complete weeks immediately before the current week, aggregates transaction-level sales rows to weekly store-SKU totals, requires at least $1,000 in each week, computes week-over-week growth, and applies ROW_NUMBER() per store with deterministic tie-breakers.

Ranking raw transaction rows before aggregation; including the current incomplete week; comparing non-adjacent weeks; applying the $1,000 condition to individual transactions instead of weekly totals; filtering only one of the two weeks; using (current - previous) / current instead of dividing by previous-week sales; using a global rank instead of PARTITION BY store_id; using RANK() when exactly three deterministic rows are required; omitting secondary tie-breakers; using inclusive upper date boundaries that can leak rows from the current week; and failing to validate NULLs, duplicate grain, or weekly control totals.

State the grain changes out loud: transaction rows -> weekly store-SKU totals -> one comparison row per store-SKU -> ranked rows per store. Then explain why the threshold is applied after aggregation and why ROW_NUMBER() has explicit secondary sort keys. That demonstrates correctness before syntax.

I would define the metric at user–SKU–day grain. First, filter product_view and add_to_cart events to the last 14 UTC calendar days and join each SKU to its category. Then use ROW_NUMBER() partitioned by user, SKU, UTC day, and event type, keeping rn = 1 so repeated events do not inflate either side. The denominator is deduplicated user–SKU–day product views, and the numerator is deduplicated user–SKU–day add-to-cart events. I would group by UTC day and category, divide with a zero-denominator guard, and validate duplicates plus add-to-cart pairs that have no matching view.

The goal is to calculate one add-to-cart rate for each UTC day and product category over the latest 14 calendar days. A raw clickstream can contain repeated events, so counting rows directly can overstate activity. I would first join each event to one product category, then reduce the data to one row per user, SKU, day, and event type. Product-view rows form the opportunity denominator, while add-to-cart rows form the numerator. The final result has one row per UTC day and category, with a NULL rate when there is no denominator.

1. Should a product_view be the denominator event, or does the business use another opportunity event such as product-detail-page exposure?
2. Are event_ts values stored as timestamptz, and should reporting days be defined in UTC as shown here?
3. Is products guaranteed to contain exactly one row per sku_id during the reporting period, or is category history versioned?
4. Should an add_to_cart with no matching product_view still count in the numerator, or should it be treated only as a tracking-quality exception?
5. Should NULL user_id or sku_id rows be excluded from the metric and reported separately as data-quality rejects?

Start with the grain because that controls every later calculation. The clickstream input is one row per raw event. The product lookup is one row per SKU, so clickstream_events to products is a many-to-one join on sku_id. That join must remain many-to-one; duplicate product rows would multiply events and corrupt both counts.

Use UTC consistently. Convert event_ts to an event_date in UTC, and filter from UTC midnight 13 days ago up to, but not including, the next UTC midnight. This represents 14 inclusive UTC calendar dates: today plus the previous 13 days.

Next, deduplicate each event type independently. ROW_NUMBER() partitions by user_id, sku_id, event_date, and event_type, ordered by event_ts and event_id. Keeping rn = 1 means repeated product_view events for the same user, SKU, and day contribute once to the denominator, and repeated add_to_cart events contribute once to the numerator. event_id provides a deterministic tie-breaker when timestamps match.

The denominator is the number of deduplicated user–SKU–day product_view opportunities inside each category and date. The numerator is the number of deduplicated user–SKU–day add_to_cart occurrences inside the same category and date. The rate is numerator divided by denominator. This is deliberately not a raw-event rate and not a distinct-user rate; one user may contribute to multiple SKUs in the same category on the same day.

The aggregation uses GROUP BY event_date, category. SUM(CASE WHEN ...) counts the two deduplicated event types. The division checks whether the product-view denominator is zero; if it is, the rate is NULL rather than a misleading zero or a divide-by-zero error. Rows with NULL user_id or sku_id are excluded from the metric and should be counted separately as data-quality rejects.

Validation is as important as the query. First, compare raw add_to_cart counts with deduplicated add_to_cart counts to confirm repeated ingestion does not inflate the numerator. Second, compare deduplicated add-to-cart user–SKU–day pairs with product-view pairs and flag add-to-cart pairs that have no matching product_view. Also investigate any category/day where the numerator is greater than the denominator because it may indicate missing view events, tracking gaps, or a business definition that needs clarification. Finally, verify that the products join does not increase the number of clickstream rows unexpectedly and reconcile daily totals with a trusted event source when one is available.

The output grain is exactly one row per UTC day and category, with event_date, category, viewed_user_sku_days, atc_user_sku_days, and add_to_cart_rate. This design favors correctness and auditability. On a very large clickstream table, I would separately inspect the execution plan and physical design, but I would not change the metric definition merely for performance.

1. Define the raw clickstream grain as one row per event and the products grain as one row per SKU.
2. Join clickstream_events to products on sku_id, confirming the relationship is many events to one product.
3. Filter to product_view and add_to_cart events in the last 14 UTC calendar days.
4. Exclude NULL user_id and sku_id rows from the metric and track them separately as data-quality rejects.
5. Derive event_date using the UTC day.
6. Apply ROW_NUMBER() over user_id, sku_id, event_date, and event_type, ordered by event_ts and event_id.
7. Keep rn = 1 to deduplicate repeated events.
8. GROUP BY event_date and category.
9. Count deduplicated product_view rows as viewed_user_sku_days and deduplicated add_to_cart rows as atc_user_sku_days.
10. Calculate add_to_cart_rate as numerator divided by denominator, returning NULL when the denominator is zero.
11. Validate raw versus deduplicated counts, join cardinality, missing product-view pairs, date boundaries, and final control totals.

The expensive work is scanning the relevant clickstream rows and sorting or otherwise processing rows for ROW_NUMBER(). If N events survive the 14-day filter, the window step can require roughly O(N log N) work in a straightforward execution, while the final GROUP BY is approximately linear after that. The many-to-one product join should not multiply rows. Real performance depends on table size, statistics, physical layout, indexes, partitions, memory, and the PostgreSQL execution plan, so those should be checked separately from metric correctness.

PostgreSQL 17 implementation matching the diagram. The base CTE joins the event to its category, derives the UTC reporting date, filters the 14-day UTC window, and removes rows that cannot be assigned to a user–SKU grain. The dedup CTE uses ROW_NUMBER() so the first product_view or add_to_cart event for each user, SKU, UTC day, and event type is retained. The final SELECT aggregates the retained rows by UTC date and category and uses CASE to return NULL when no product-view opportunity exists.

Common mistakes are counting raw add_to_cart rows instead of deduplicated user–SKU–day rows; using distinct users as the numerator or denominator and accidentally changing the metric grain; deduplicating only add-to-cart while leaving repeated product views in the denominator; partitioning ROW_NUMBER() without event_type and therefore collapsing a product_view and add_to_cart into one row; joining to a product table that contains multiple rows per SKU and multiplying events; using session-local dates instead of explicit UTC boundaries; using an inclusive upper timestamp boundary that overlaps the next day; dividing by zero instead of returning NULL; silently treating add-to-cart events with no product view as normal data; and inventing sample output values instead of validating against actual data.

Say the grain and denominator before showing SQL: one user–SKU–day product_view is one opportunity, and one user–SKU–day add_to_cart is one numerator contribution. Then explain the many-to-one product join, the ROW_NUMBER() deduplication, the UTC half-open date window, and two validation checks. That makes the query easier to defend than starting with syntax.

I would use both an event fact and an accumulating fulfillment snapshot. FactGMVEvent would have one row per financial event × order line × fulfillment split and would connect to shared Date, Item, Channel, and Fulfillment Type dimensions. Booked order value is a positive SignedGMVDelta, while cancellations and refunds are negative adjustments. Shipment events can update quantity or status but do not re-book GMV. FactFulfillmentSnapshot would keep one lifecycle row per order line × fulfillment split for ordered, shipped, cancelled, and refunded quantities. Reports calculate Net GMV from the event fact and use the snapshot for lifecycle analysis, with no direct fact-to-fact relationship.

I would use two complementary fact tables because they answer different questions. The event fact records financial changes over time, while the accumulating snapshot shows the fulfillment lifecycle state of each order-line split. Both use shared descriptive dimensions for date, item, channel, and fulfillment type. This makes daily GMV easy to filter and aggregate without counting the same order value again when an item ships in parts. The key rule is simple: book GMV once, post cancellations and refunds as negative adjustments, and do not let ordinary shipment events re-book the same GMV.

1. Is GMV recognized at order placement, shipment, or another approved business event?
2. Can one order line be split across multiple fulfillment methods or shipments?
3. Should a refund reduce GMV on the refund date, or should reporting restate the original order date?
4. Which source event identifier can be used to make event loading idempotent?
5. How should category reporting behave if an item's classification changes over time?

I would begin by defining the grain of each fact table before creating relationships.

FactGMVEvent is the transaction fact. Its grain is one financial event × order line × fulfillment split. It contains EventKey as the unique source event identifier, EventDateKey, foreign keys such as ItemKey, ChannelKey, and FulfillmentTypeKey, and business identifiers such as OrderID, OrderLineID, and FulfillmentSplitID. EventType identifies order, cancellation, refund, or shipment activity.

The financial measure is SignedGMVDelta. A booked order value contributes a positive delta. A cancellation or refund contributes a negative delta for the affected amount. Shipment events can carry quantity or status information, but they do not add the order value again unless a separate shipment-based GMV definition is intentionally introduced. The reusable measures are therefore Booked GMV = SUM(positive BOOKED deltas) and Net GMV = SUM(SignedGMVDelta).

FactFulfillmentSnapshot is an accumulating snapshot. Its grain is one order line × fulfillment split. It contains OrderID, OrderLineID, FulfillmentSplitID, the shared dimensional foreign keys, lifecycle date keys such as OrderDateKey, nullable ShipDateKey, CancelDateKey, and RefundDateKey, and lifecycle measures such as OrderedQty, ShippedQty, CancelledQty, and RefundedQty, plus CurrentStatus. As the lifecycle changes, the same snapshot row is updated. It is useful for fulfillment-state and SLA analysis, but it is not the source used to sum GMV.

The shared dimensions form the star around both facts. DimDate provides attributes such as date, year, month, day, week, and quarter. DimItem contains item identifiers and descriptive attributes such as item name, category, brand, and subcategory. DimChannel describes channels such as web, app, or marketplace. DimFulfillmentType describes fulfillment choices such as ship-to-home, pickup, or delivery. Each applicable dimension has a one-to-many relationship to each fact table. There is no direct relationship from one fact table to the other; they are analyzed together through their shared dimensions.

For partial shipments, I keep distinct shipment events and update shipped quantity or status in the accumulating snapshot. Those shipment events do not re-book order GMV. For a cancellation, I post a negative financial event only for the previously booked amount being cancelled. For a refund, I post a separate negative financial event for the refunded amount. This preserves the event history and keeps Net GMV additive at the event grain.

Idempotency is essential. I would deduplicate on the unique source EventKey at the declared event grain so replaying a source batch cannot create duplicate financial events. I would validate the model by checking event-key uniqueness, reconciling booked, cancelled, refunded, and net amounts to trusted source control totals, and verifying that lifecycle quantities in the snapshot reconcile to the underlying order and fulfillment events.

In the BI semantic model, descriptive dimensions provide filter context and governed measures provide reusable calculations. Reports can show daily GMV by item, roll it up to category, and filter by channel or fulfillment type. GMV measures come from the event fact, while the snapshot supports lifecycle and SLA questions. This separation keeps the reporting model easier to understand and prevents accidental double counting in Power BI, Tableau, Excel, or another BI tool.

1. Confirm the business definition and recognition point for GMV.
2. Declare FactGMVEvent grain as one financial event × order line × fulfillment split.
3. Declare FactFulfillmentSnapshot grain as one order line × fulfillment split.
4. Create shared Date, Item, Channel, and Fulfillment Type dimensions.
5. Load booked value as positive event deltas and cancellations or refunds as negative deltas.
6. Treat normal shipment events as quantity or status changes rather than another GMV booking.
7. Update the accumulating snapshot as lifecycle milestones occur.
8. Relate dimensions one-to-many to the applicable facts and avoid a direct fact-to-fact relationship.
9. Publish governed Booked GMV and Net GMV measures from the event fact.
10. Validate source control totals, snapshot quantities, and EventKey uniqueness before publishing reports.

This uses more storage and ETL logic than one wide table because it keeps both financial event history and current fulfillment lifecycle state. The benefit is safer aggregation: the event fact is additive for financial changes, while the accumulating snapshot makes fulfillment analysis simple. The ETL must maintain dimension keys, deduplicate source events, apply signed financial adjustments correctly, and update snapshot rows without creating duplicates.

Common mistakes are defining the fact grain too loosely, booking GMV again when a partial shipment occurs, subtracting the same cancellation or refund twice, using snapshot lifecycle rows as GMV transactions, directly joining one fact table to another, mixing descriptive attributes with measures, and replaying source events without an idempotent EventKey.

State the two grains first, then walk through one lifecycle: book GMV once, allow partial shipments without re-booking it, post cancellations or refunds as negative deltas, and use the accumulating snapshot only for lifecycle state. That makes the anti-double-counting logic easy to verify.

Import mode loads selected source data into the Power BI semantic model, so reports usually respond fastest and you get the richest Power Query and DAX modeling options, but the data only changes when the model refreshes. DirectQuery leaves the data in the source and sends queries back as users interact with the report, so it is useful for near-real-time or very large data, but performance depends on the source, network, and gateway and some modeling features are more limited. For most standard retail dashboards, I would choose Import. I would choose DirectQuery when freshness or data volume makes importing inefficient and the source can handle the interactive query load.

The decision is about where Power BI reads the data when a retail report is used. Import mode loads selected source data into the Power BI semantic model and visuals query that in-memory copy. DirectQuery keeps the data in the source and sends queries back when users interact with the report. This affects freshness, performance, modeling flexibility, source workload, data volume, security, gateway use, and refresh operations. For most standard retail reporting, Import is the starting choice; DirectQuery is considered when near-real-time access or very large data makes importing less practical.

1. How fresh must sales, inventory, or store-operation data be: scheduled updates or near real time?
2. Is the required data volume practical to import and refresh within the available capacity and refresh window?
3. Can the source system support interactive queries from report users without slowing operational workloads?
4. Do we need Power Query transformations, DAX calculations, or modeling features that are restricted in DirectQuery?
5. Are the sources cloud-based or on-premises, and will an on-premises data gateway be required?
6. Does access require only Power BI row-level security, or should supported single sign-on pass user identity to the data source?
7. What dashboard response time is acceptable under realistic user concurrency?

I would compare the two modes across the business requirements shown in the decision flow.

Import mode stores the selected data inside the Power BI semantic model. Because report visuals query the cached in-memory data, interactions are typically fast. Import also provides the broadest Power Query, DAX, and semantic-model flexibility. The tradeoff is freshness: source changes appear only after the semantic model refreshes. Import therefore requires refresh processing, sufficient model capacity, and a refresh schedule. If the source is on-premises, a gateway may be required for refresh.

DirectQuery leaves the detailed data in the source. When a user filters or interacts with a visual, Power BI sends queries to that source. This can provide near-current data without importing a very large table, but performance now depends on the source, network latency, gateway path, query design, and concurrent workload. Interactive use also creates more source load because report actions can generate source queries.

The model-feature tradeoff is important. Import offers the richest Power Query and DAX support. DirectQuery supports many modeling capabilities, but some transformations, DAX expressions, calculated columns, and other modeling operations have restrictions. I would confirm required calculations and transformations before choosing DirectQuery.

Security works in both modes through Power BI row-level security. With supported DirectQuery sources and configurations, single sign-on can also pass a user's identity to the source so source-side security can be applied. I would test the identity path rather than assuming the storage mode alone guarantees the required access control.

For retail reporting, my practical rule is to use Import for most standard dashboards when scheduled freshness is acceptable and fast interactive performance is the priority. Typical examples are daily sales reporting, inventory trends, and customer analytics. I would use DirectQuery for cases such as near-real-time store sales, live inventory, or very large transaction tables when the source is optimized to answer interactive analytical queries.

If different tables have different requirements, a composite model can combine Import and DirectQuery so cached tables and directly queried tables can coexist in one semantic model.

Before publishing, I would validate row counts and control totals against a trusted source, test row-level security with representative users, confirm that refresh or source-query behavior matches the freshness requirement, and measure dashboard performance with realistic filters. I would also plan for failure modes: an Import refresh failure can leave users seeing older cached data, while a slow or unavailable DirectQuery source can make visuals slow or unavailable.

1. Define the required freshness for the retail report.
2. Check whether the required data volume can be imported and refreshed within available capacity and the refresh window.
3. Identify required Power Query transformations, DAX calculations, and semantic-model features.
4. Estimate source latency, concurrency, and interactive query load.
5. Confirm row-level security, single sign-on, gateway, and credential requirements.
6. Choose Import when scheduled freshness is acceptable and fast interactive performance plus full modeling flexibility are priorities.
7. Choose DirectQuery when near-real-time access or very large data makes Import less practical and the source can sustain the workload.
8. Consider a composite model when some tables should use Import and others DirectQuery.
9. Validate totals, security, freshness behavior, and dashboard performance before publishing.

Import moves more work into refresh and model memory, but report interactions are usually faster because visuals query cached in-memory data. DirectQuery reduces the amount of detailed data stored in the Power BI model, but report interactions generate queries against the source. That shifts more cost to the source, network, gateway, and concurrency. The decision is therefore mainly a tradeoff between refresh and memory cost on the Import side versus interactive source-query cost and latency on the DirectQuery side.

Assuming DirectQuery is automatically real time; assuming DirectQuery has the same Power Query and DAX flexibility as Import; choosing DirectQuery without testing source latency, concurrency, and source load; forgetting that Import freshness depends on a successful refresh; assuming an on-premises gateway is never needed; overlooking row-level security or supported single sign-on behavior; and forcing one storage mode on every table when a composite model would fit the requirements better.

State the decision rule first: choose Import for most retail dashboards when scheduled freshness is acceptable and fast performance with full modeling flexibility matters; choose DirectQuery when near-real-time access or very large data makes Import less practical and the source can support interactive queries. Then explain freshness, performance, model features, source load, data volume, security, gateway, and refresh tradeoffs.