Consistency Rules
Consistency Rules
In this series of detailed workshops we're going to:
Understand the business value and ROI of implementing business rules
Master the 7 Data Quality Dimensions in PDC
Create and configure business rules with detailed step-by-step guidance
Implement rule actions (Status, Tags, Webhooks) and understand their purpose
Organize rules using rule groups for operational efficiency
Monitor and interpret rule execution results for continuous improvement
x
Accessing your Catalog
To get started using the Data Catalog, log in using the address and credentials provided by your Data Catalog service user or administrator.
To access your catalog, please follow these steps:
Open Google Chrome web browser.
Navigate to:
Enter following email and password, then click Sign In.
Username: [email protected] (mapped to Business Steward role)
Password: Welcome123!
x
x
Customer Address
Scenario: Ensure customer billing and shipping addresses exist in the address table.
Business Rule Configuration:
Rule Name: Customer_Address_Consistency
Description: Validates customer addresses reference valid address records
Data Quality Dimension: Consistency
Schedule: Daily
Target: Sales.CustomerSQL Query:
SELECT
COUNT(*) AS total_count,
SUM(CASE
WHEN a.AddressID IS NOT NULL
THEN 1 ELSE 0
END) AS scopeCount,
SUM(CASE
WHEN a.AddressID IS NULL
THEN 1 ELSE 0
END) AS nonCompliant
FROM Sales.Customer c
LEFT JOIN Person.BusinessEntityAddress ba ON c.PersonID = ba.BusinessEntityID
LEFT JOIN Person.Address a ON ba.AddressID = a.AddressID
WHERE ba.AddressTypeID IN (2, 3) -- Billing and Shippingx
Product Price Consistency Across Time
Scenario: Detect unusual price changes that may indicate data entry errors.
Business Rule Configuration:
Rule Name: Product_Price_Change_Consistency
Description: Flags products with >50% price changes in single update
Data Quality Dimension: Consistency
Schedule: Daily
Target: Production.ProductListPriceHistorySQL Query:
WITH PriceChanges AS (
SELECT
ProductID,
ListPrice,
LAG(ListPrice) OVER (PARTITION BY ProductID ORDER BY StartDate) AS PrevPrice,
CASE
WHEN LAG(ListPrice) OVER (PARTITION BY ProductID ORDER BY StartDate) > 0
THEN ABS(ListPrice - LAG(ListPrice) OVER (PARTITION BY ProductID ORDER BY StartDate)) /
LAG(ListPrice) OVER (PARTITION BY ProductID ORDER BY StartDate)
ELSE 0
END AS PriceChangePercent
FROM Production.ProductListPriceHistory
)
SELECT
COUNT(*) AS total_count,
SUM(CASE WHEN PriceChangePercent <= 0.50 THEN 1 ELSE 0 END) AS scopeCount,
SUM(CASE WHEN PriceChangePercent > 0.50 THEN 1 ELSE 0 END) AS nonCompliant
FROM PriceChanges
WHERE PrevPrice IS NOT NULLx
x
x
x
x
Last updated
Was this helpful?