Q: Handle slowly changing dimensions for product catalog.

Products change over time — price, category, supplier. SCD Type 2 tracks full history: Update logic (Delta Lake MERGE ): Then insert the new current record for changed products.

Q: Write SQL for top-selling products by region.

Top 5 per region (using window function):

Q: Query customers with no purchases in past 6 months.

Use case : identify churned customers for win back promotional campaigns.

Q: Handle streaming sales ingestion with Kafka.

Architecture (Walmart real time POS): Key configuration : During peak (Black Friday) : scale Kafka partitions + Spark executor count ahead of time. Use maxOffsetsPerTrigger to prevent individual micro batches from taking too long.

Q: Handle GDPR/CCPA compliance for customer data.

Data inventory : catalog all PII fields — name, email, phone, address, loyalty ID. Right to erasure (GDPR Article 17 / CCPA): Data minimization : Don't collect data beyond what's needed Purge transactional data older than required retention period Access controls : RBAC — only CRM team accesses PII columns Column masking — analysts see user 12345 not the actual email Audit trail : log all access to PII tables with user, timestamp, query purpose. Cross border : don't transfer EU customer data to non EU regions without adequacy decision or SCCs.

Q: Ensure data quality in sales pipelines.

Checks to implement : 1. Row count validation : compare source POS record count vs loaded records — alert if 1% gap 2. Null checks : net revenue , product id , store id must not be null 3. Range checks : quantity 0 , net revenue = 0 , sale date <= today() 4. Referential integrity : every product id in fact sales must exist in dim product 5. Deduplication : check for duplicate transaction id values Implementation with Great Expectations : Quarantine pattern : invalid records → dead letter Delta table → reviewed daily by data engineering team → root cause fixed at source.

Question 1

Design a retail data warehouse schema for sales.

Accepted Answer

Fact tables : Dimension tables : dim store(store key, store id, name, city, state, region, format) — SCD Type 1 dim product(product key, sku, name, category, department, brand, supplier) — SCD Type 2 for price changes dim customer(customer key, loyalty id, tier, zip code, signup date) — SCD Type 2 dim date(date key, date, year, quarter, month, week, is holiday, is sale day) Partitioning : partition fact sales by year/month . Z order on store key + product key for store product analytics. Metrics unlocked : comparable store sales, inventory turnover, customer LTV, margin by department.

Question 2

Optimize ETL pipeline for nightly sales data loads.

Accepted Answer

Incremental loads : load only new records ( WHERE sale date = yesterday ) — avoid full reload Parallel extraction : split source by store id ranges and run parallel Glue/Spark jobs COPY command (Redshift) / LOAD DATA (Snowflake): bulk load from S3 is 10 100x faster than row by row inserts Partition pruning : write to date partitioned table — downstream queries auto skip older partitions Pre aggregate : compute daily summaries (sales per store/product) as a separate Gold table — avoids recomputing on every BI query Compact small files : Walmart stores generate many small POS files → consolidate with coalesce() before writing to Delta/Parquet Schedule : run at 2 AM — off peak, avoids competing with real time POS systems Monitoring : alert if row count < 90% of 7 day moving average (catches load failures)

Question 3

Handle slowly changing dimensions for product catalog.

Accepted Answer

Products change over time — price, category, supplier. SCD Type 2 tracks full history: Update logic (Delta Lake MERGE ): Then insert the new current record for changed products.

Question 4

Write SQL for top-selling products by region.

Accepted Answer

Top 5 per region (using window function):

Question 5

Query customers with no purchases in past 6 months.

Accepted Answer

Use case : identify churned customers for win back promotional campaigns.

Question 6

Handle streaming sales ingestion with Kafka.

Accepted Answer

Architecture (Walmart real time POS): Key configuration : During peak (Black Friday) : scale Kafka partitions + Spark executor count ahead of time. Use maxOffsetsPerTrigger to prevent individual micro batches from taking too long.

Question 7

Handle GDPR/CCPA compliance for customer data.

Accepted Answer

Data inventory : catalog all PII fields — name, email, phone, address, loyalty ID. Right to erasure (GDPR Article 17 / CCPA): Data minimization : Don't collect data beyond what's needed Purge transactional data older than required retention period Access controls : RBAC — only CRM team accesses PII columns Column masking — analysts see user 12345 not the actual email Audit trail : log all access to PII tables with user, timestamp, query purpose. Cross border : don't transfer EU customer data to non EU regions without adequacy decision or SCCs.

Question 8

Ensure data quality in sales pipelines.

Accepted Answer

Checks to implement : 1. Row count validation : compare source POS record count vs loaded records — alert if 1% gap 2. Null checks : net revenue , product id , store id must not be null 3. Range checks : quantity 0 , net revenue = 0 , sale date <= today() 4. Referential integrity : every product id in fact sales must exist in dim product 5. Deduplication : check for duplicate transaction id values Implementation with Great Expectations : Quarantine pattern : invalid records → dead letter Delta table → reviewed daily by data engineering team → root cause fixed at source.

Walmart Interview Questions

Design a retail data warehouse schema for sales.

Optimize ETL pipeline for nightly sales data loads.

Handle slowly changing dimensions for product catalog.

Write SQL for top-selling products by region.

Query customers with no purchases in past 6 months.

Handle streaming sales ingestion with Kafka.

Handle GDPR/CCPA compliance for customer data.

Ensure data quality in sales pipelines.