# Consistency Rules

{% hint style="success" %}

#### 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

{% endhint %}

x

***

{% hint style="info" %}

#### 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.
{% endhint %}

To access your catalog, please follow these steps:

1. Open **Google Chrome** web browser.
2. Navigate to:

{% embed url="<https://pdc.pentaho.lab>" %}

3. Enter following email and password, then click **Sign In**.

Username: <david.park@adventureworks.com> (mapped to Business Steward role)

Password: Welcome123!

***

x

x

{% tabs %}
{% tab title="Customer Address" %}
{% hint style="info" %}

#### Customer Address

**Scenario:** Ensure customer billing and shipping addresses exist in the address table.
{% endhint %}

**Business Rule Configuration:**

```
Rule Name: Customer_Address_Consistency
Description: Validates customer addresses reference valid address records
Data Quality Dimension: Consistency
Schedule: Daily
Target: Sales.Customer
```

**SQL Query:**

```sql
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 Shipping
```

***

x
{% endtab %}

{% tab title="Product Price" %}
{% hint style="info" %}

#### Product Price Consistency Across Time

**Scenario:** Detect unusual price changes that may indicate data entry errors.
{% endhint %}

**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.ProductListPriceHistory
```

**SQL Query:**

```sql
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 NULL
```

***

x

{% endtab %}
{% endtabs %}

x

x

{% tabs %}
{% tab title="First Tab" %}
x
{% endtab %}

{% tab title="Second Tab" %}

{% endtab %}
{% endtabs %}

x


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://academy.pentaho.com/pentaho-data-catalog-en/data-catalog/business-rules/consistency-rules.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.