Sale Price Data Validation Rules
Scope
- Applies to sale_price for product pricing records keyed by product_id, channel_id, currency_code, and effective date range.
- Assumes presence of: list_price (optional), cost (optional), price_floor/price_ceiling (from a reference table), min_margin_pct (configurable), map_price (Minimum Advertised Price; optional), price_effective_start, price_effective_end, is_active, price_includes_tax, tax_region_code, derivation_method (e.g., MANUAL, FX_DERIVED).
Severity Levels
- Critical: Failing records must be blocked.
- Major: Failing records require correction or explicit override.
- Warning: Records can pass but require monitoring.
Rule Set
Critical
- Type, nullability, and domain
- sale_price must be numeric and not null when is_active = true and current_timestamp within [price_effective_start, price_effective_end].
- sale_price >= 0.
- Currency and precision
- currency_code must be a valid ISO 4217 code in the currency reference table.
- Decimal precision must match the currency minor unit:
- round(sale_price, minor_unit) = sale_price.
- Example: JPY minor_unit = 0; USD = 2; KWD/BHD/JOD/TND often 3 (rely on reference table, not hardcoding).
- Configured range limits
- Join to a product-price policy table to enforce:
- sale_price >= price_floor.
- sale_price <= price_ceiling.
- If no policy found, enforce a platform hard limit (e.g., sale_price <= 10,000,000) to prevent extreme values.
- Effective window integrity
- price_effective_start < price_effective_end.
- No overlapping active price windows for the same product_id, channel_id, and currency_code:
- For any pair of records: [start1, end1) must not overlap [start2, end2).
- Referential completeness
- product_id, channel_id, currency_code must exist in their respective master data tables and be active.
Major
6) Relationship to list price (if present)
- If pricing_type = 'SALE' and list_price is present: sale_price <= list_price + tolerance_abs, where tolerance_abs defaults to 0.01 in applicable currency units.
- If pricing_type = 'REGULAR': sale_price should equal list_price within tolerance_abs; otherwise flag.
- Margin guardrail (if cost present)
- If cost is present and margin policy applies:
- sale_price >= cost × (1 + min_margin_pct) unless override_approved = true.
- If override_approved = true, record override reason and approver_id must be not null.
- MAP compliance (if map_price present)
- sale_price >= map_price unless map_override = true and policy permits.
- Tax inclusion consistency
- If price_includes_tax = true:
- sale_price ≈ net_price × (1 + tax_rate) within tolerance_abs, based on tax_region_code and tax effective date.
- If price_includes_tax = false:
- sale_price should equal net_price within tolerance_abs.
- If net_price not modeled, this rule can be applied where net_price is derived upstream.
- FX-derived consistency (if derivation_method = 'FX_DERIVED')
- sale_price must equal base_currency_price × fx_rate × (1 + rounding_rule), rounded to currency minor unit, within tolerance_abs.
- fx_rate must come from approved source and be valid for price_effective_start date.
Warning
11) Outlier detection vs historical price
- Flag if sale_price deviates from the product’s 90-day median by more than max(percent_threshold, absolute_threshold).
- Example defaults: 50% or 100 units of minor currency.
- Cross-channel parity (optional)
- For channels that should maintain parity, flag if sale_price deviates by more than parity_threshold_pct from the base channel.
- Sudden change rate limit
- Flag if day-over-day price change exceeds change_threshold_pct (e.g., >60%) without change_reason.
Implementation Notes (SQL patterns)
A) Currency precision
SELECT p.*
FROM price p
JOIN currency_ref c ON p.currency_code = c.code
WHERE ROUND(p.sale_price, c.minor_unit) <> p.sale_price;
B) Overlapping windows
WITH w AS (
SELECT p.*,
LAG(price_effective_end) OVER (
PARTITION BY product_id, channel_id, currency_code
ORDER BY price_effective_start
) AS prev_end
FROM price p
)
SELECT *
FROM w
WHERE prev_end > price_effective_start;
C) Floor/ceiling
SELECT p.*
FROM price p
JOIN price_policy pol
ON p.product_id = pol.product_id
AND p.currency_code = pol.currency_code
WHERE p.sale_price < pol.price_floor
OR p.sale_price > pol.price_ceiling;
D) List price relationship
SELECT *
FROM price
WHERE pricing_type = 'SALE'
AND list_price IS NOT NULL
AND sale_price > list_price + :tolerance_abs;
E) Margin guardrail
SELECT p.*
FROM price p
JOIN product_cost c
ON p.product_id = c.product_id
AND p.currency_code = c.currency_code
WHERE p.sale_price < c.cost * (1 + :min_margin_pct)
AND COALESCE(p.override_approved, false) = false;
F) MAP compliance
SELECT *
FROM price p
JOIN map_policy m
ON p.product_id = m.product_id
AND p.currency_code = m.currency_code
WHERE p.sale_price < m.map_price
AND COALESCE(p.map_override, false) = false;
Operational Controls
- Run critical and major checks in the staging layer before publishing to downstream systems.
- Enforce critical rules via database constraints where possible (NOT NULL, CHECK, FK, unique with non-overlapping windows if modeled).
- Log all failures with rule_id, record key, observed_value, expected_value, and event_time.
- Track data quality KPIs: failure rate by rule, by source, and by product category; alert on threshold breaches.
- Maintain configuration tables for: currency minor units, price floors/ceilings, min_margin_pct, parity rules, and tolerances to avoid hardcoding.
This rule set provides clear, enforceable validations for sale_price while allowing policy-driven configuration and explicit overrides where business exceptions are required.
