Why Every Data Engineer Must Learn Excel in 2026

Source            Target                   Rule                          Owner
crm.cust_id       dim_customer.customer_id Cast to INT                   Data eng
crm.full_name     dim_customer.name        TRIM, Proper case             Data eng
crm.city          dim_customer.city        Map aliases (Gurgaon->Gurugram) Business
orders.amount     fact_sales.amount_inr    Round to 2 decimals           Finance
orders.ts         fact_sales.order_date    Convert UTC to IST, DATE only Data eng

import pandas as pd

mapping = pd.read_excel("source_to_target.xlsx", sheet_name="Mapping")
for row in mapping.itertuples(index=False):
    print(f"{row.Source:20s} -> {row.Target:30s} | {row.Rule}")
# Use the sheet to generate checks or documentation automatically

-- Run on source and target, export both to Excel
SELECT CAST(order_ts AS DATE) AS day,
       COUNT(*)                 AS row_count,
       COUNT(DISTINCT order_id) AS orders,
       ROUND(SUM(amount), 2)    AS total_amount
FROM orders
WHERE order_ts >= DATE '2026-10-01'
GROUP BY CAST(order_ts AS DATE)
ORDER BY day;

Sheet 'Source' and sheet 'Target', both: day | row_count | orders | total_amount
In 'Compare' sheet, row 2:
A2: =Source!A2
B2: =Source!B2 - XLOOKUP(A2, Target!A:A, Target!B:B, "missing")
C2: =ROUND(Source!D2 - XLOOKUP(A2, Target!A:A, Target!D:D, 0), 2)
D2: =IF(AND(B2=0, C2=0), "OK", "CHECK")
Conditional formatting: highlight 'CHECK' in red

import pandas as pd

# Keep codes and IDs as text; parse dates explicitly
df = pd.read_csv(
    "customers.csv",
    dtype={"pin_code": "string", "customer_id": "string", "phone": "string"},
    parse_dates=["signup_date"],
    encoding="utf-8",
)
assert df["pin_code"].str.len().eq(6).all(), "PIN codes lost digits"

-- Find PIN codes that lost a leading zero after a manual edit
SELECT pin_code, COUNT(*)
FROM staging_customers
WHERE LENGTH(pin_code) < 6
GROUP BY pin_code;

-- Find IDs that look like rounded numbers (ending in many zeros)
SELECT customer_id FROM staging_customers
WHERE customer_id LIKE '%000000';

Sheet: Mapping
Columns:
ID | Source system | Source table.field | Data type | Target table.field |
Transformation rule | Business definition | Nullable? | Owner | Status
Use Data Validation lists for 'Status': Draft, Agreed, Built, Tested

Sheet: Compare
Columns: Day | Src rows | Tgt rows | Row diff | Src amount | Tgt amount | Amount diff | Result
Row diff:    =B2-C2
Amount diff: =ROUND(E2-F2,2)
Result:      =IF(AND(D2=0,G2=0),"OK","CHECK")
Summary:     =COUNTIF(H:H,"CHECK") & " days need review"

Rules for files shared with business teams
1. Use the provided .xlsx template, not raw CSV
2. Do not change column names or order
3. ID and code columns are Text - do not convert
4. Dates in YYYY-MM-DD
5. Save and send; do not re-save as CSV
6. One file per day, named data_YYYY-MM-DD.xlsx