MATCH_RECOGNIZE and Data Vault Effectivity Satellite
Effectivity Satellite is a powerful Data Vault artifact that is used to track the effectivity of a relationship. For e.g. it can be used to track the relationship between:
- Order and Customer, or
- Opportunity and Account, or
- Subscription Tier and Customer etc.
A Data Vault Effectivity Satellite hangs from the LINK, and only contains the Driving Business Key, Secondary Foreign Key and the Start Date and End Date for the given Relationship.
Example of an Effectitity Satellite
The above Effectivity Satellite is used to track the subscription tier a Customer has purchased. The
CUSTOMER_BK is the Driving Key, and the
SUBSCRIPTION_TIER_BK is the Secondary Foreign Key. The
START_DATE and the
END_DATE indicate when the Subscription was purchased and when it was ended.
Note: Since Data Vault 2.0 is an insert only pattern, we do not update the existing record to set the
END_DATE, instead we insert a new record with the
Effectivity Satellite and Data Analysis
The structure of an Effectivity Satellite lends itself to Longitudinal Data Analysis. You can identify patterns, perform timeseries analysis or even use the data directly in statistical learning model.
MATCH_RECOGNIZE and pattern recognition
In relational systems, a row pattern recognition task is to detect a sequence of ordered rows from an input table that match a specified pattern. For example, a financial service provider needs to identify sequences of suspicious transactions that match known patterns of criminal activities; an e-commerce site analyzes the steps taken by customers from landing through a social media referrer to a successful purchase. The MATCH_RECOGNIZE clause in SQL allows users to search for patterns in rows of data using a powerful and expressive syntax that is based on RegEx.
MATCH_RECOGNIZE clause in a SQL Query allows us to:
- Define patterns using REGEX convention
- Match longitudinal data against those patterns
- Identify records that match the patterns
In the MATCH_RECOGNIZE clause the pattern is constructed from basic building blocks, called pattern variables, to which operators (quantifiers and other modifiers) like Kleene Star
* and Kleene Plus
+ and can be applied. The whole pattern must be enclosed in paranthesis. For example a pattern can be defined as following:
select * from sate_customer_subscription match_recognize( partition by customer_bk order by start_date measures paid.subscription_tier_bk as paid_subscription_tier , paid.start_date paid_subscription_start_date , free.start_date basic_susbcription_start_date after match skip past last row pattern (paid+ free) define paid as paid.subscription_tier_bk in ('Personal', 'Premium', 'Business'), free as free.subscription_tier_bk = 'Free' )
There are three tiers of PAID plans– Personal, Premium and Business. A Customer may switch between these tiers multiple times before finally downgrading to a FREE tier.
The query identified the following Customers that had PAID subscription and then eventually downgraded to a FREE tier