Forum Discussion
Data Modeling - Customers
Hi,
Hello,
I have the DIM_CUSTOMERS table, which is populated based on the CURRNO column that represents the customer's version. When an attribute in the source application changes, a new CURRNO appears, and a new DIM_CUSTOMER_ID key is inserted with the date_from (modification date) and date_to as null.
The current request requires bringing attributes to the customer level that may change more rapidly than the customer's version. For example, whether or not they have marketing consent.
How can I model the data so that I can see the complete picture of the customer on a specific day?
Example: Customer 200142025 has 59 versions, the last version is valid from 31.05.2024 to the present. However, during this period, they changed their marketing consent from YES to NO on 15.08.2024.
How can I model the data so that I can see the complete and correct picture of the customer on any given date?
DIM_CUSTOMERS:
| DIM_CUSTOMER_ID | CURRNO | CUSTOMER_ID | DATE_FROM | DATE_TO |
| 13879786 | 1 | 200142025 | 3/7/17 17:29 | 3/8/17 0:10 |
| 13879797 | 2 | 200142025 | 3/8/17 0:10 | 4/7/17 3:33 |
| 13879808 | 3 | 200142025 | 4/7/17 3:33 | 4/27/17 13:00 |
| 13879812 | 4 | 200142025 | 4/27/17 13:00 | 9/21/17 12:53 |
| 13879813 | 5 | 200142025 | 9/21/17 12:53 | 10/4/17 10:26 |
| 13879814 | 6 | 200142025 | 10/4/17 10:26 | 10/4/17 10:26 |
| 13879815 | 7 | 200142025 | 10/4/17 10:26 | 2/27/18 10:52 |
| 13879816 | 8 | 200142025 | 2/27/18 10:52 | 2/28/18 8:18 |
| 13879817 | 9 | 200142025 | 2/28/18 8:18 | 3/7/18 18:27 |
| 13879787 | 10 | 200142025 | 3/7/18 18:27 | 5/26/18 12:45 |
| 13879788 | 11 | 200142025 | 5/26/18 12:45 | 7/26/18 16:23 |
| 13879789 | 12 | 200142025 | 7/26/18 16:23 | 7/26/18 16:24 |
| 13879790 | 13 | 200142025 | 7/26/18 16:24 | 7/26/18 17:05 |
| 13879791 | 14 | 200142025 | 7/26/18 17:05 | 7/27/18 17:19 |
| 13879792 | 15 | 200142025 | 7/27/18 17:19 | 8/2/18 18:34 |
| 13879793 | 16 | 200142025 | 8/2/18 18:34 | 8/6/18 10:18 |
| 13879794 | 17 | 200142025 | 8/6/18 10:18 | 1/11/19 16:05 |
| 13879795 | 18 | 200142025 | 1/11/19 16:05 | 1/15/19 17:48 |
| 13879796 | 19 | 200142025 | 1/15/19 17:48 | 3/2/19 12:41 |
| 13879798 | 20 | 200142025 | 3/2/19 12:41 | 4/12/19 7:28 |
| 13879799 | 21 | 200142025 | 4/12/19 7:28 | 10/1/19 17:38 |
| 13879800 | 22 | 200142025 | 10/1/19 17:38 | 10/2/19 10:48 |
| 13879801 | 23 | 200142025 | 10/2/19 10:48 | 11/6/19 9:37 |
| 13879802 | 24 | 200142025 | 11/6/19 9:37 | 2/28/20 1:58 |
| 13879803 | 25 | 200142025 | 2/28/20 1:58 | 3/31/20 1:50 |
| 13879804 | 26 | 200142025 | 3/31/20 1:50 | 5/29/20 1:39 |
| 13879805 | 27 | 200142025 | 5/29/20 1:39 | 6/2/20 15:22 |
| 13879806 | 28 | 200142025 | 6/2/20 15:22 | 7/31/20 1:36 |
| 13879807 | 29 | 200142025 | 7/31/20 1:36 | 8/8/20 15:02 |
| 13879809 | 30 | 200142025 | 8/8/20 15:02 | 9/30/20 2:11 |
| 13879810 | 31 | 200142025 | 9/30/20 2:11 | 10/3/20 17:05 |
| 13879811 | 32 | 200142025 | 10/3/20 17:05 | 12/18/20 8:46 |
| 184578 | 33 | 200142025 | 12/18/20 8:46 | 4/29/21 18:19 |
| 15945479 | 34 | 200142025 | 4/29/21 18:19 | 4/29/21 18:19 |
| 15945480 | 35 | 200142025 | 4/29/21 18:19 | 5/4/21 8:54 |
| 15952414 | 36 | 200142025 | 5/4/21 8:54 | 5/18/21 10:56 |
| 16013087 | 37 | 200142025 | 5/18/21 10:56 | 7/30/21 2:10 |
| 16368237 | 38 | 200142025 | 7/30/21 2:10 | 8/31/21 2:25 |
| 16517819 | 39 | 200142025 | 8/31/21 2:25 | 9/30/21 2:48 |
| 16784554 | 40 | 200142025 | 9/30/21 2:48 | 4/4/22 12:41 |
| 18108380 | 41 | 200142025 | 4/4/22 12:41 | 5/2/22 17:31 |
| 18366546 | 42 | 200142025 | 5/2/22 17:31 | 5/2/22 17:32 |
| 18366547 | 43 | 200142025 | 5/2/22 17:32 | 5/31/22 5:09 |
| 18539691 | 44 | 200142025 | 5/31/22 5:09 | 6/29/22 19:02 |
| 18693112 | 45 | 200142025 | 6/29/22 19:02 | 6/30/22 3:39 |
| 18693113 | 46 | 200142025 | 6/30/22 3:39 | 7/29/22 2:35 |
| 18933767 | 47 | 200142025 | 7/29/22 2:35 | 8/20/22 11:18 |
| 19088490 | 48 | 200142025 | 8/20/22 11:18 | 9/1/22 18:19 |
| 19264568 | 49 | 200142025 | 9/1/22 18:19 | 11/29/22 2:38 |
| 19836221 | 50 | 200142025 | 11/29/22 2:38 | 3/31/23 3:11 |
| 20509038 | 51 | 200142025 | 3/31/23 3:11 | 4/28/23 4:15 |
| 20647689 | 52 | 200142025 | 4/28/23 4:15 | 6/30/23 3:27 |
| 20952134 | 53 | 200142025 | 6/30/23 3:27 | 8/31/23 4:06 |
| 21249742 | 54 | 200142025 | 8/31/23 4:06 | 10/10/23 14:07 |
| 21455908 | 55 | 200142025 | 10/10/23 14:07 | 10/31/23 4:54 |
| 21561608 | 56 | 200142025 | 10/31/23 4:54 | 2/29/24 5:46 |
| 22197406 | 57 | 200142025 | 2/29/24 5:46 | 3/29/24 4:39 |
| 22338088 | 58 | 200142025 | 3/29/24 4:39 | 5/31/24 4:45 |
| 22674290 | 59 | 200142025 | 5/31/24 4:45 | NULL |
Marketing agreement "YES" until 15.08.2024 and "NO" after.
Thank you! ~~
2 Replies
Hi,Anonymous .I am glad to help you.
According to your description, you are having some problems designing your data model.
Although I'm not very good at data model design, I'd like to help you out
I think you can create a separate table specifically to record dynamic attribute changes for customers.
like this:ATTRIBUTE_ID
CUSTOMER_ID
ATTRIBUTE_NAME
ATTRIBUTE_VALUE
DATE_FROM
DATE_TO
1
200142025
Marketing Consent
YES
2024-05-31
2024-08-15
2
200142025
Marketing Consent
NO
2024-08-15
NULL
The table is associated with your existing table through the CUSTOMER_ID field to your existing table.
You can use join query (left outer join/right outer join) to fetch the user's consolidated data
Note that this is only my suggestion to you, you need to build the appropriate data model according to your actual situation.I hope the following information can help you.
URL:
Dimensional Modeling Techniques - Kimball Group
A Guide to Data Modeling & The Different Types of Models | Twilio Segment
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.