Why Retail Companies Are Hiring More Data Engineers in 2026

-- Standardise three channels into one sales table
CREATE OR REPLACE VIEW mart.all_sales AS
SELECT 'store' AS channel, bill_id AS order_id, sku, qty, amount, sale_ts
FROM staging.pos_sales
UNION ALL
SELECT 'web', order_id, sku, quantity, total_amount, created_at
FROM staging.web_orders
UNION ALL
SELECT 'marketplace', m.order_ref, map.internal_sku, m.units, m.net_value, m.order_time
FROM staging.marketplace_orders m
JOIN ref.sku_mapping map ON map.marketplace_sku = m.seller_sku;

import json
import pandas as pd

raw_events = [
    '{"order_id": "A1", "ts": "2026-11-05T10:01:00", "items": [{"sku": "S1", "qty": 2, "price": 250}]}',
    '{"order_id": "A2", "ts": "2026-11-05T11:20:00", "items": [{"sku": "S2", "qty": 1, "price": 999}, {"sku": "S1", "qty": 1, "price": 250}]}',
]
rows = []
for line in raw_events:
    e = json.loads(line)
    for item in e["items"]:
        rows.append({"order_id": e["order_id"], "ts": e["ts"], **item,
                     "amount": item["qty"] * item["price"]})
print(pd.DataFrame(rows))

-- Compare stock implied by events with the daily snapshot
WITH movements AS (
  SELECT store_id, sku, SUM(qty_change) AS net_change
  FROM staging.stock_events
  WHERE event_date = DATE '2026-11-05'
  GROUP BY store_id, sku
)
SELECT s.store_id, s.sku,
       y.on_hand + COALESCE(m.net_change, 0) AS expected_on_hand,
       s.on_hand AS snapshot_on_hand
FROM mart.stock_snapshot s
JOIN mart.stock_snapshot y
  ON y.store_id = s.store_id AND y.sku = s.sku AND y.snapshot_date = DATE '2026-11-04'
LEFT JOIN movements m ON m.store_id = s.store_id AND m.sku = s.sku
WHERE s.snapshot_date = DATE '2026-11-05'
  AND s.on_hand <> y.on_hand + COALESCE(m.net_change, 0);

import pandas as pd

events = pd.DataFrame({
    "event_id": ["e1", "e2", "e2", "e3"],
    "sku": ["S1", "S1", "S1", "S2"],
    "qty_change": [-2, 10, 10, -1],
})
dupes = events[events.duplicated("event_id", keep=False)]
print("duplicate events:\n", dupes)
clean = events.drop_duplicates("event_id")
print("net change by SKU:\n", clean.groupby("sku")["qty_change"].sum())

import hashlib
import pandas as pd

def key(value: str) -> str:
    return hashlib.sha256(value.strip().lower().encode()).hexdigest()[:12]

store = pd.DataFrame({"phone": ["9876543210"], "email": [None], "spend": [1200]})
web = pd.DataFrame({"phone": ["9876543210"], "email": ["asha@example.com"], "spend": [800]})

for df in (store, web):
    df["customer_key"] = df["phone"].map(key)   # pseudonymous key, not raw phone
combined = pd.concat([store, web]).groupby("customer_key")["spend"].sum()
print(combined)
# Real matching needs consent, clear rules and privacy review.

import pandas as pd

sales = pd.DataFrame({
    "date": pd.to_datetime(["2026-11-01", "2026-11-02", "2026-11-04"]),
    "units": [12, 9, 15],
})
stock_out_days = {pd.Timestamp("2026-11-03")}

full = (sales.set_index("date")
             .reindex(pd.date_range("2026-11-01", "2026-11-04"), fill_value=0)
             .rename_axis("date").reset_index())
full["stock_out"] = full["date"].isin(stock_out_days)
print(full)
# Zero sales on a stock-out day is not zero demand; flag it for the model.

Project: Omnichannel retail data platform (dummy data)

1. Generate POS files, web order JSON and marketplace CSVs
2. Load into staging tables in DuckDB or PostgreSQL
3. Build mart.all_sales with SKU mapping
4. Add stock events, daily snapshots and reconciliation
5. Build a daily sales history table with stock-out flags
6. Schedule with cron or Airflow; add quality checks

staging.pos_sales, staging.web_orders, staging.marketplace_orders
ref.sku_mapping, ref.calendar (festivals, promotions)
mart.all_sales        (channel, order_id, sku, qty, amount, ts)
mart.stock_snapshot   (store_id, sku, snapshot_date, on_hand)
mart.daily_sku_sales  (date, store_id, sku, units, stock_out)
mart.customer_key_map (source_id_hash, customer_key)

[ ] Each channel loaded for yesterday (row counts > 0)
[ ] No duplicate order_id within a channel
[ ] Every marketplace SKU mapped
[ ] Stock reconciliation mismatches below agreed level
[ ] No negative stock on hand
[ ] Stock-out days flagged in sales history
[ ] Personal data hashed or masked in shared tables