# \[DRAFT]ETL 400: Attribute-Level Change Detection in Profile Snapshot Data

#### **Step-by-Step Tracking of Changes:**

1. **Daily Snapshot of Data:** Every day, you take a snapshot of customer attributes, segment memberships, and identity maps to track changes. You can compute the CRC32 checksum for each table to detect changes.
2. **Tracking Changes in Customer Attributes:**

   You can create a query to compare the current day’s data with the previous day’s data and identify any changes in customer attributes:

   <pre class="language-sql" data-overflow="wrap"><code class="lang-sql">SELECT 
     a.customer_id,
     CASE WHEN CRC32(CONCAT(a.first_name, a.last_name, a.email, a.phone_number, a.age, a.location, a.purchase_history, a.loyalty_status)) 
          != CRC32(CONCAT(b.first_name, b.last_name, b.email, b.phone_number, b.age, b.location, b.purchase_history, b.loyalty_status)) 
          THEN 'Changed' ELSE 'No Change' END AS attribute_change_status
   FROM customer_attributes a
   LEFT JOIN customer_attributes b
   ON a.customer_id = b.customer_id AND b.date_recorded = DATE_SUB(a.date_recorded, 1)
   WHERE a.date_recorded = CURRENT_DATE();
   </code></pre>

   This query compares the CRC32 checksum of the concatenated customer attributes for each customer and checks for differences between today’s and yesterday’s data.
3. **Tracking Changes in Segment Membership:**

   Similarly, you can track changes in segment membership by comparing the Boolean flags of different segments:

   <pre class="language-sql" data-overflow="wrap"><code class="lang-sql">SELECT 
     a.customer_id,
     CASE WHEN CRC32(CONCAT(a.high_spenders, a.frequent_buyers, a.location_based_promo)) 
          != CRC32(CONCAT(b.high_spenders, b.frequent_buyers, b.location_based_promo)) 
          THEN 'Segment Changed' ELSE 'No Change' END AS segment_change_status
   FROM customer_segments a
   LEFT JOIN customer_segments b
   ON a.customer_id = b.customer_id AND b.date_recorded = DATE_SUB(a.date_recorded, 1)
   WHERE a.date_recorded = CURRENT_DATE();
   </code></pre>

   This query tracks changes in segment membership, comparing today’s segment flags to the previous day.
4. **Tracking Changes in Identity Map:**

   Finally, to track changes in the identity map (email, phone, and loyalty ID):

   <pre class="language-sql" data-overflow="wrap"><code class="lang-sql">SELECT 
     a.customer_id,
     CASE WHEN CRC32(CONCAT(a.email_id, a.phone_id, a.loyalty_id)) 
          != CRC32(CONCAT(b.email_id, b.phone_id, b.loyalty_id)) 
          THEN 'Identity Changed' ELSE 'No Change' END AS identity_change_status
   FROM identity_map a
   LEFT JOIN identity_map b
   ON a.customer_id = b.customer_id AND b.date_recorded = DATE_SUB(a.date_recorded, 1)
   WHERE a.date_recorded = CURRENT_DATE();
   </code></pre>

   This query compares the identity information between today and the previous day, tracking any updates in the identity map.

#### **Summary Table of Changes:**

You can combine these queries into a single view or summary table that shows the daily changes per customer, across attributes, segments, and identity mappings:

{% code overflow="wrap" %}

```sql
SELECT 
  a.customer_id,
  attr_changes.attribute_change_status,
  seg_changes.segment_change_status,
  id_changes.identity_change_status
FROM customer_attributes a
JOIN (
  SELECT 
    customer_id,
    CASE WHEN CRC32(CONCAT(a.first_name, a.last_name, a.email, a.phone_number, a.age, a.location, a.purchase_history, a.loyalty_status)) 
         != CRC32(CONCAT(b.first_name, b.last_name, b.email, b.phone_number, b.age, b.location, b.purchase_history, b.loyalty_status)) 
         THEN 'Changed' ELSE 'No Change' END AS attribute_change_status
  FROM customer_attributes a
  LEFT JOIN customer_attributes b
  ON a.customer_id = b.customer_id AND b.date_recorded = DATE_SUB(a.date_recorded, 1)
  WHERE a.date_recorded = CURRENT_DATE()
) attr_changes ON a.customer_id = attr_changes.customer_id
JOIN (
  SELECT 
    customer_id,
    CASE WHEN CRC32(CONCAT(a.high_spenders, a.frequent_buyers, a.location_based_promo)) 
         != CRC32(CONCAT(b.high_spenders, b.frequent_buyers, b.location_based_promo)) 
         THEN 'Segment Changed' ELSE 'No Change' END AS segment_change_status
  FROM customer_segments a
  LEFT JOIN customer_segments b
  ON a.customer_id = b.customer_id AND b.date_recorded = DATE_SUB(a.date_recorded, 1)
  WHERE a.date_recorded = CURRENT_DATE()
) seg_changes ON a.customer_id = seg_changes.customer_id
JOIN (
  SELECT 
    customer_id,
    CASE WHEN CRC32(CONCAT(a.email_id, a.phone_id, a.loyalty_id)) 
         != CRC32(CONCAT(b.email_id, b.phone_id, b.loyalty_id)) 
         THEN 'Identity Changed' ELSE 'No Change' END AS identity_change_status
  FROM identity_map a
  LEFT JOIN identity_map b
  ON a.customer_id = b.customer_id AND b.date_recorded = DATE_SUB(a.date_recorded, 1)
  WHERE a.date_recorded = CURRENT_DATE()
) id_changes ON a.customer_id = id_changes.customer_id;
```

{% endcode %}

#### **Outcome:**

This combined query tracks changes in customer attributes, segment memberships, and identity map on a daily basis. You can use this for reporting, auditing customer data, or triggering specific actions (like personalized marketing updates) based on the detected changes.


---

# 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://data-distilller.gitbook.io/adobe-data-distiller-guide/unit-3-data-distiller-etl-extract-transform-load/draft-etl-400-attribute-level-change-detection-in-profile-snapshot-data.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.