Field Mapping Report: StockSheet → inventory.stock_levels_current Source of truth: Excel (StockSheet) is the source; MySQL (inventory.stock_levels_current) is the target. Use case: Recurring weekly reconciliation (not a one-time load, not a live pipeline). Approval status of this report: None of the mappings below are approved for implementation. See Section 8. 1. Definitions (Reference Only) These terms are used by name throughout the rest of this report and are not redefined elsewhere. 1.1 Relationship Types Same concept and unit — fields represent the identical real-world quantity, in the identical unit or format, with no transformation needed beyond type casting. Same concept with unit or scale conflict — fields represent the identical real-world quantity, but a documented or suspected difference in unit, scale, precision, or format requires a transformation. Same concept needing a join or lookup — the correspondence only exists by traversing another table or reference structure, not by direct field-to-field mapping. Partial overlap — the fields overlap in meaning for only a subset of values or cases, and diverge for the remainder. No candidate — no target field represents this source concept, or vice versa, given the structures provided. 1.2 Review Statuses Ready to approve — evidence fully supports the mapping and transformation as stated; no open questions block it. Blocked pending clarification — a mapping is plausible but cannot be certified without additional evidence or a business decision. Rejected — evidence contradicts the proposed mapping; it should not be implemented as stated. Unmatched — no correspondence exists on the other side, by design or by evidence gap. 1.3 Severity Tiers (for Blocked Items) Row or relational integrity risk — could cause duplicate rows, missing rows, mismatched keys, orphaned records, or incorrect row counts in the target. Single-value risk — row identity and row count are unaffected, but an individual field's value could be wrong, stale, or misinterpreted. Cosmetic only — affects labeling, display, or non-critical descriptive data with no downstream correctness impact. 2. Field Inventory 2.1 Source: StockSheet (Excel) ProductSKU — text, stated unique, grain context: sheet is documented as one row per SKU per warehouse. ProductName — text, no stated unit, descriptive attribute. UnitsOnHand — integer, no stated unit of measure (each, case, pallet, etc. not specified). WarehouseCode — text, no stated code vocabulary or format documented. 2.2 Target: inventory.stock_levels_current (MySQL) sku_id — text, part of composite primary key (sku_id, warehouse_id). warehouse_id — text, part of composite primary key (sku_id, warehouse_id). qty_available — integer, no stated unit of measure documented. last_counted_at — timestamp, no population rule documented, no source-side counterpart provided. 2.3 Structural Completeness Flags No additional structures (product master table, warehouse reference table, unit-of-measure lookup, etc.) were referenced by either side; there is nothing to flag as "referenced but undetailed." The target's composite primary key (sku_id, warehouse_id) is explicitly documented. The source has no explicit statement that (ProductSKU, WarehouseCode) pairs are themselves unique — only ProductSKU alone is labeled unique, which conflicts with the stated per-SKU-per-warehouse grain. This conflict is carried into Section 3 and Section 5. 3. Field-Level Mapping Analysis 3.1 ProductSKU Proposed correspondence: ProductSKU → sku_id. Relationship type: Same concept and unit (identifier-to-identifier), pending resolution of the uniqueness conflict below. Required transformation: Type-compatible pass-through (text to text), pending confirmation of consistent formatting (case, leading zeros, whitespace) on both sides — no evidence was provided confirming format parity. Review status: Blocked pending clarification. Severity: Row or relational integrity risk. Supporting evidence: ProductSKU is declared "unique" in the source description, but the source grain is declared as "one row per SKU per warehouse," meaning the same SKU value must legitimately repeat across multiple rows (one per warehouse). These two statements are contradictory as given. See Open Question 1 and Open Question 8. 3.2 ProductName Proposed correspondence: None available in inventory.stock_levels_current. Relationship type: No candidate. Required transformation: Not applicable — there is no target field to transform into. Review status: Unmatched. Severity: Cosmetic only (no impact to inventory quantity or key integrity; impact is limited to descriptive/reporting completeness). Supporting evidence: The provided target table lists only sku_id, warehouse_id, qty_available, and last_counted_at. No column, view, or joinable table carrying a product name was documented. See Open Question 4. 3.3 UnitsOnHand Proposed correspondence: UnitsOnHand → qty_available. Relationship type: Same concept with unit or scale conflict (status of the conflict is unconfirmed, not ruled out). Required transformation: Undetermined pending unit confirmation; if units match, a direct integer pass-through; if units differ (e.g., cases vs. eaches), a scale-conversion factor is required before load. Review status: Blocked pending clarification. Severity: Single-value risk. Supporting evidence: Neither the source nor the target definitions state a unit of measure for these integer fields. Matching them on name and data type alone does not satisfy the "never match on naming alone" requirement. See Open Question 2. Related business rule: Per the stated exclusion rule, only rows with UnitsOnHand > 0 are "active"; rows with UnitsOnHand = 0 that are not discontinued are not addressed by the rule as given. See Open Question 6. 3.4 WarehouseCode Proposed correspondence: WarehouseCode → warehouse_id. Relationship type: Same concept and unit, pending confirmation of shared code vocabulary. Required transformation: Type-compatible pass-through (text to text), pending confirmation that both sides use the identical warehouse identifier scheme (same code list, same case sensitivity, same padding/format). Review status: Blocked pending clarification. Severity: Row or relational integrity risk (this field participates in the target's composite primary key). Supporting evidence: No shared reference list, sample values, or format specification was provided for either WarehouseCode or warehouse_id. See Open Question 3. 3.5 last_counted_at (Target Field Without Source Correspondence) Proposed correspondence: None available in StockSheet. Relationship type: No candidate. Required transformation: Not applicable from the source; a population rule (e.g., set to load timestamp, or left null, or backfilled from an external count-date source) must be defined at the pipeline level, not derived from a source field. Review status: Unmatched, and additionally blocked at the pipeline-design level because weekly reconciliation logic may depend on this field to detect staleness. Severity: Row or relational integrity risk (if this field is used to gate which rows are considered "current" during reconciliation, an undefined population rule can silently corrupt that logic). Supporting evidence: The source has no timestamp field of any kind. No population rule for last_counted_at was documented. See Open Question 5. 4. Interaction Effects Across Combined Mappings The composite key correspondence (ProductSKU, WarehouseCode) → (sku_id, warehouse_id) depends on both individual field mappings being correct and on (ProductSKU, WarehouseCode) pairs being unique in the source. Since ProductSKU alone is labeled unique in a sheet whose grain is per-SKU-per-warehouse, the composite key assumption cannot be certified until this is resolved; an incorrect assumption here risks a fan-out (one source row loading into or overwriting multiple target rows) or silent key collisions during the weekly load. The exclusion rule ("discontinued SKUs are excluded from the sheet entirely") creates a silent-exclusion risk at the target: rows already present in inventory.stock_levels_current for a SKU+warehouse combination that stops appearing in the weekly sheet will not be touched by a simple upsert, and will silently persist as stale, incorrect "current" inventory unless the reconciliation logic explicitly detects and handles removals. The zero-quantity ambiguity (Section 3.3) interacts with the exclusion rule: a reconciliation process cannot correctly distinguish "SKU still exists but has zero stock" from "SKU was discontinued and removed" unless both cases are explicitly defined, because both could plausibly need different target-side treatment (update to zero vs. delete/deactivate). The absence of a source-side timestamp interacts with the recurring/weekly nature of this feed: without a rule for populating last_counted_at, every weekly run risks either overwriting a meaningful prior count time with an arbitrary load time, or leaving the field stale/null in a way that could be misread as "not yet reconciled." 5. Open Questions (Consolidated) [Row or relational integrity risk] ProductSKU is documented as unique, but the sheet's grain is one row per SKU per warehouse, which requires ProductSKU to repeat across rows. Affects: 3.1 (ProductSKU → sku_id), 4 (composite key mapping). [Single-value risk] No unit of measure is documented for UnitsOnHand or qty_available, so it cannot be confirmed they represent the same quantity type. Affects: 3.3 (UnitsOnHand → qty_available). [Row or relational integrity risk] No shared code vocabulary, format, or sample values are documented for WarehouseCode and warehouse_id. Affects: 3.4 (WarehouseCode → warehouse_id), 4 (composite key mapping). [Cosmetic only] No target field, table, or view is documented to hold a product name equivalent to ProductName. Affects: 3.2 (ProductName). [Row or relational integrity risk] No rule is documented for how last_counted_at should be populated on each weekly load, and no source field supplies a count timestamp. Affects: 3.5 (last_counted_at). [Row or relational integrity risk] No rule is documented for how rows with UnitsOnHand = 0 that are not discontinued should be treated versus rows that are absent because a SKU is discontinued. Affects: 3.3 (UnitsOnHand → qty_available), 4 (interaction effects). [Row or relational integrity risk] No rule is documented for how the target should reflect a SKU+warehouse combination that disappears from the sheet due to discontinuation (hard delete, soft delete/flag, or left as-is). Affects: 4 (silent exclusion), 3.5 (last_counted_at staleness). [Row or relational integrity risk] No explicit statement confirms that (ProductSKU, WarehouseCode) pairs are unique in the actual sheet data, independent of the naming conflict in Open Question 1. Affects: 3.1 (ProductSKU → sku_id), 3.4 (WarehouseCode → warehouse_id), 4 (composite key mapping). 6. Validation Steps For Open Question 1 and Open Question 8: pull one full weekly extract of StockSheet and run a duplicate check on ProductSKU alone and on the (ProductSKU, WarehouseCode) pair; confirm which grain actually holds in real data. For Open Question 2: request the data dictionary or source-system documentation for UnitsOnHand and qty_available; if unavailable, cross-reference a sample of matching SKU+warehouse rows between the sheet and the live table and compare raw values for a scale mismatch pattern (e.g., consistent 12x or 1/12x ratios suggesting case-vs-each). For Open Question 3: request a full list of valid warehouse codes from both the sheet's data source and the database's warehouse_id domain, and diff the two lists for coverage and format mismatches. For Open Question 4: ask the business owner whether product name is required in the reconciliation output; if yes, identify or request access to the table or view that holds it. For Open Question 5: ask the data engineering or DBA team how last_counted_at is currently populated for existing rows in inventory.stock_levels_current, and whether the weekly job is expected to update it. For Open Question 6: ask the business owner for an explicit definition of what should happen to a SKU+warehouse row when UnitsOnHand = 0 but the SKU is not discontinued. For Open Question 7: ask the business owner or system owner for the intended behavior when a previously-present SKU+warehouse row disappears from the weekly sheet, and confirm whether soft-delete flags already exist on the target table beyond the four documented columns. 7. Quality Check Every source and target field received an explicit disposition (ProductSKU, ProductName, UnitsOnHand, WarehouseCode, last_counted_at): Pass. Every proposed mapping cites specific supporting evidence rather than naming similarity alone: Pass — where evidence was insufficient (ProductSKU, UnitsOnHand, WarehouseCode), the mapping was marked blocked rather than approved. No transformation assumes an unstated unit or format: Pass — the UnitsOnHand/qty_available unit conflict and the WarehouseCode/warehouse_id format conflict were both flagged rather than assumed away. Every structure flagged as incomplete in Section 2 appears in the Open Questions list: Pass (trivial) — Section 2.3 identified no referenced-but-undetailed external structures, so there is nothing requiring carryover. Every review status assigned matches the evidence presented for that field: Pass — no field was marked "ready to approve" without matching evidence, since none currently qualifies. The business rule for active SKUs (UnitsOnHand > 0) was checked against every field it could affect: Pass — its incompleteness for zero-quantity, non-discontinued rows was surfaced as Open Question 6 rather than silently assumed. 8. Reviewer Handoff Data/business owner (warehouse operations): review Open Questions 1, 6, 7, and 8 — these require a business decision or knowledge of the actual source data, not just technical inspection. Database administrator or data engineering owner of inventory.stock_levels_current: review Open Questions 3, 5, and 7 — these require knowledge of the live table's current population logic and warehouse code domain. Reporting or content stakeholder (if product name is needed downstream): review Open Question 4 to confirm whether ProductName must be sourced from elsewhere. Technical reviewer approving this document: confirm the relationship-type and severity assignments in Section 3 and Section 5 are consistent with the definitions in Section 1 before signing off on any mapping as "ready to approve." Nothing in this report has been approved, validated against live data, or deployed; all correspondences above are proposals pending the validation steps in Section 6.