A schema specifies the target structure of a dataset: its columns, the meaning of those columns, and the level at which each row represents an entity. For a customer model, the intended granularity is one row per customer, so purchase-level facts must be aggregated before they can belong in that model. section 3.3.1
Define the schema before combining sources. Compare available columns, decide which fields are necessary, and choose whether missing values are acceptable rather than dropping useful information simply because one source lacks it. This prevents effort spent transforming data that cannot appear in the final model. section 3.3.1 section 3.4.2
A common schema makes separate datasets compatible for combination:
- Explore each source first: check that columns match their data dictionaries, identify missing values, and inspect implausible prices, dates, times, or postcodes. section 3.3.1
- Retain a field such as age when it matters to the model even if it is unavailable for guest records. section 3.4.2
- Standardize equivalent fields into shared names and formats, such as normalized names and trimmed postcodes. section 3.4.2
- Add source-presence indicators rather than a single exclusive source field when one customer can occur in multiple datasets. section 3.4.2
- Transform every source to the common structure, combine the resulting customer datasets, then deduplicate records. section 3.4.1
Customer schema across purchases, CRM, and customer data
A customer schema can include customer_id, first_name, surname, postcode, age, and is_guest. It can also include in_purchase_data, in_crm_data, and in_customer_data flags. A guest may have no customer ID or age, while the flags preserve the fact that a customer can be represented by more than one source. section 3.4.2
Table 3.5 The data model schema that all data sources need to be transformed into
|
Column
|
Description
|
|---|---|
customer_id
|
The unique ID of the customer record, or NULL for guests.
|
first_name
|
Either from the customer or CRM tables or the guest information.
|
surname
|
Either from the customer or CRM tables or the guest information.
|
postcode
|
Either from the customer or CRM tables or the guest information.
|
age
|
From the customer or CRM tables, unavailable for guests.
|
is_guest
|
True if the data comes from a guest checkout.
|
in_purchase_data
|
True if this customer record appears in the purchase table. It is not exclusive since the customer could also appear in the customer or CRM datasets.
|
in_crm_data
|
True if the customer record exists in the CRM database.
|
in_customer_data
|
True if the customer record exists in the customer database.
|
Inspect the target columns, their definitions, and the source-presence flags used to align all three customer datasets. section 3.4.2
A schema is a practical decision about what the final model must support. Keeping guest status and source indicators makes later questions possible, such as measuring how many customers buy without registering, while retaining enough lineage to understand where each record originated. section 3.4.2
