PURPOSE / DIRECTION Direction: Excel model (HeadcountModel.xlsx) → database views. Identifying which hr_analytics fields could populate the four Excel conceptual fields. SOURCE INVENTORY Excel: HeadcountModel.xlsx, sheet "Employees," 4 fields read (EmployeeID, FullName, AnnualSalary_USD, DepartmentName). Database: hr_analytics.v_employee_current (v3) and hr_analytics.v_department_lookup (referenced but not fully detailed — only column names given, no definitions or key constraints supplied for v_department_lookup). Deferred: none on the Excel side. On the database side, v_department_lookup's key structure and whether dept_code is unique are not confirmed. NORMALIZED METADATA SUMMARY Excel source fields: S001 Employee.EmployeeID — text, grain: one employee, documented as unique identifier S002 Employee.FullName — text, grain: one employee S003 Employee.AnnualSalary_USD — decimal, grain: one employee, annualized, USD S004 Employee.DepartmentName — text, grain: one employee, department name (not code) Database target fields: V001 v_employee_current.emp_id — text, documented unique identifier V002 v_employee_current.full_name — text V003 v_employee_current.base_salary_monthly_usd — decimal, MONTHLY not annual, USD V004 v_employee_current.dept_code — text, department CODE not name V005 v_department_lookup.dept_name — text, resolves dept_code → name (key structure of this view not supplied — treated as unconfirmed) Assumption flagged: v_department_lookup is assumed one-row-per-dept_code for the join in M004 below; this is NOT confirmed by supplied metadata — evidence gap. MAPPING REGISTER M001 | S001 EmployeeID → V001 emp_id Relation: EQUIVALENT CONCEPT | Evidence: E01 (Excel row 2), E02 (view v3 definition) Transformation: DIRECT | Disposition: PROPOSED Both documented as unique employee identifiers, same grain, both text type. M002 | S002 FullName → V002 full_name Relation: EQUIVALENT CONCEPT | Evidence: E01, E02 Transformation: DIRECT | Disposition: PROPOSED Same grain, same declared meaning, same type. M003 | S003 AnnualSalary_USD → V003 base_salary_monthly_usd Relation: EQUIVALENT CONCEPT (with unit conflict) | Evidence: E01, E02 Transformation: CONVERT (multiply by 12) | Disposition: NEEDS CLARIFICATION Support: same underlying concept (base salary, same currency). Conflict: Excel field is explicitly annualized; view field is explicitly monthly. A straight multiply-by-12 assumes no mid-year raises, bonuses, or pro-ration in either source — this is a reasonable default conversion but not verified against actual pay data. Also unverified: whether "current" in the view means "as of today" or "as of last payroll run," which matters for a reconciliation report. M004 | S004 DepartmentName → V004 dept_code (via V005 dept_name) Relation: EQUIVALENT CONCEPT, requires JOIN | Evidence: E01, E02, E03 (view mention only, no full definition) Transformation: JOIN + LOOKUP | Disposition: NEEDS CLARIFICATION The Excel field stores a name; the primary view stores only a code. Resolving requires a join to v_department_lookup. Cannot confirm cardinality (one dept_code per dept_name) or whether v_department_lookup itself has any historical/versioned rows — if it does, joining without a time filter could multiply rows. This is exactly the join-risk pattern the prompt is built to catch rather than silently accepting a name match. ALTERNATIVES / CONFLICTS / UNMATCHED None unmatched — all 4 Excel fields have a candidate. Two of four (M003, M004) carry open clarification needs; neither is a rejection, both are solvable with one follow-up question each. COMBINED-MAPPING CHECK M001-M004 together assume consistent grain (one row per active employee) across both sources. If v_employee_current includes terminated employees with a status flag not shown here, that would affect population comparability — flagged as an open question below, not assumed either way. VALIDATION PLAN - M001: confirm emp_id uniqueness constraint exists in the live view (not stated as a formal key here, only as "uniquely identifies"). - M003: reconcile the ×12 conversion against a sample of employees with known annual figures before trusting it at scale. - M004: confirm v_department_lookup's key (is dept_code unique in that view?) before building the join. DRAFT SQL (requested? — not requested in this run; dialect and full keys for v_department_lookup were not fully supplied, so none is speculatively drafted) APPROVAL HANDOFF Mapping bundle v0.1 — 4 fields proposed, 2 with open clarification. Proposed reviewer roles: HR data owner (confirm salary timing/definition), database engineer (confirm v_department_lookup key structure). This is a proposal only — no approval granted or implied. QUALITY GATE Q1 Traceability: PASS — every target ID traces to admitted evidence. Q2 Meaning: PASS — unit and grain conflicts surfaced, not hidden behind name matches. Q3 Relational validity: PASS — join risk on M004 explicitly flagged as unconfirmed rather than assumed safe. Q4 Transformation: PASS — unit conversion and lookup both specified with stated assumptions. Q5 Review separation: PASS — no fabricated approval; "current" ambiguity flagged. Q6 Coverage: PASS — 4/4 Excel fields have a disposition (2 PROPOSED, 2 NEEDS CLARIFICATION, 0 UNMATCHED, 0 REJECTED). Totals reconcile. OVERALL: REVIEWABLE MAPPING DRAFT WITH OPEN GAPS Database validation: NOT EXECUTED. No mapping is owner-approved or deployed. TOP QUESTIONS (max 5) 1. Does v_employee_current include terminated employees, and if so, via what flag? 2. Is dept_code guaranteed unique in v_department_lookup, or does it carry history? 3. Does "AnnualSalary_USD" include bonuses/commissions, or base pay only? 4. What does "current" mean in v_employee_current — as-of-today or as-of-last-run? 5. Is a straight ×12 conversion acceptable for this report, or is pro-ration needed?