About domain tables

A source domain table is created for each data feed that has been configured to ingest a data source into Amperity. The data in a source domain table may be processed to include semantic tagging alongside the original fields from the data feed.

The Domain Tables section of the Sources page lists the domain tables that have been generated by feeds. Each row shows the name of the domain table, the type of record contained within that domain table (customer or interaction), and the number of columns.

Column types

Type	Description
Boolean	A value that represents an either/or, such as true or false, yes or no, 0 or 1, true or NULL.
date	An ISO-8601 compliant date values, such as a birthdate. For example: 2021-11-04
datetime	An ISO-8601 compliant date and time values, such as a purchase or transaction, the time at which data was last updated, or a campaign launch date. For example: Mon Nov 30 2020 16:00:00 GMT-0800 (Pacific Standard Time) Sat Sep 02 2017 14:36:19 GMT-0700 (Pacific Daylight Time) Important Some fields that store datetime values are set to the string data type.
decimal	A fixed point number, such as for prices or message sizes. (The number of characters in the decimal value is configurable during feed setup.). For example: 1.50 (prices) 1874.380 (message sizes) 141.48 (order subtotals)
float	A floating point number. (Use decimal for prices.) For example: 3.14 3.14159
integer	A numeric value, such as the quantity of items purchased. (Use decimal for prices.) For example: 1 12345
string	A sequence of characters, such as first and last names, email addresses, physical addresses, UUIDs (and other IDs), phone numbers, zip codes, product names, descriptions, and so on. May be empty. For example: John Smith John Smith johnsmith @ domain.com 123 Main Street 206-555-1111 00002ac0-0915-3cb4-b7c7-5ee192b3bd49 ACME pants “A data source that pulls from an Amazon S3 bucket.”

Record types

The following types of data are often present in domain table data:

• Customer records

• Interaction records

Note

An individual table may contain both customer and interaction records. As part of the Stitch process, customer records and interaction records are split into dedicated tables for use within the customer 360 database.

Customer records

A customer record is a row in a customer data table that contains information (columns) about the customer. Who they are, where they live, and how much they spend. For example, a email list table contains names, email addresses, phone numbers, and so on. All domain tables that contain customer records are stitched together to create Amperity IDs.

Customer records are defined by the presence of data that can be assigned semantic tags for customer profile data, specifically to fields that contain personally identifiable information (PII).

Interaction records

An interaction record is a row in a customer data table that contains information about customer behavior, such as purchases (items bought, items returned, costs of items, etc.) and preferences (brands, products, cart adds, etc.).

Interaction records are defined by the presence of data that can be assigned semantic tags for transactions, product catalogs, and other behavior data, such as custom semantic tags for loyalty programs.

Interaction records often require data to be reshaped using domain SQL (and custom domain tables) to ensure that the right combination of fields are present in the data to support components and workflows within Amperity, including functionality that is available from the Segment Brief (a component within the Segments page), predicted customer lifetime value models, and the Campaigns page.

Custom domain tables

Some customer data sources are only available in a state that requires the use of SQL to construct a complete record that can be made available to the Stitch process. This is often true with interaction records, which typically require some data shaping to map the data that is available in the data source to the semantic tags that are required by Amperity for transactions.

These semantic tags generate the Unified Itemized Transactions table, which is then used as the underlying reference for the Unified Transactions, Transaction Attributes, and Transaction Attributes Extended tables.

A custom domain table is built directly using Spark SQL to define a schema for that data source, after which semantic tags are applied and the primary key is identified. A custom domain table may reference other custom domain tables.

Note

When a database is run, any custom domain table that has changed is run first, and then Stitch will run. If there are no changes to custom domain tables or if custom domain tables have changed that are not configured for Stitch, Stitch will not run.

Domain SQL

Domain SQL is a reference to using Spark SQL to reshape data that has been loaded to Amperity prior to making it available to downstream processes, such as Stitch or the customer 360 database. Domain SQL is often used to build new tables from existing domain tables, and then reshaping that data into a new table that allows semantic tags for transactions and itemized transactions to be applied correctly.

Use cases

The following examples describe some of the more common uses cases for domain SQL:

• Combine day, month, year as birthdate

• Combine five- and four-digit postal codes

• Extract first and last names

• Fixed-width fields

• Hash PII data that has been resent to Amperity

• Parse fields with multiple separators

• Reference custom domain tables

• Semantic tags and bridge tables

• Set non-US-ASCII email addresses to NULL

• Standardize values for USA states

Note

These examples are not meant to be copied and pasted, but they should work for most tenants as a good starting point.

Combine day, month, year as birthdate

Some data sources do not contain fields for complete birthdates and instead contain values by day, month, and year in separate fields. These individual fields must be combined in order to use the birthdate semantic tag.

The following example shows an IF statement within a SELECT statement that finds the values in day, month, and year fields, and then combines them into a field that captures the birthdate value as DD/MM/YYYY:

SELECT
  *
  ,IF(birth_day != '0' AND birth_month != '0' AND birth_year != '0',
      birth_month||'/'||birth_day||'/'||birth_year, NULL) AS birthdate
FROM table

Combine five- and four-digit postal codes

Some data sources do not contain fields for complete postal codes and instead contain fields that separate the five- and four-digit codes. Some use cases require a single field for postal codes that includes both components, after which the postal semantic tag is applied.

The following example shows how to use a CASE statement to do the following:

1. Find situations where the five- and four-digit codes are both present, and then combine them.

2. Find situations where only the five-digit code is present, and then use only the five-digit code.

3. Uses the CONCAT_WS function to return “zip_code” and “zip_code_plus_four” separated by “-“.

4. Use NULL for situations where the five-digit code is not present.

5. Return as the postal field, to which the postal semantic tag may be applied.

,CASE
  WHEN zip_code != '(NULL)' AND zip_code_plus_four != '(NULL)'
  THEN CONCAT_WS('-',zip_code, zip_code_plus_four)

  WHEN zip_code != '(NULL)'
  THEN zip_code

  ELSE NULL
END AS postal

Extract first and last names

Some data sources do not contain fields that can be directly assigned the given-name and surname semantic tags. These tags are important to downstream Stitch processes. When a field is present in the data source that contains data that can be tagged with the full-name semantic tag, you can use domain SQL to extract the first and last name details from that field, add them as new columns, and then apply the correct semantic tags.

Use the REGEXP_EXTRACT() function to:

1. Trim whitespace from before (or after) the first and last names.

2. Individually extract the first and last names from the field that contains the full name.

3. Add columns for the first and last names.

The following example shows part of a SELECT statement that extracts first and last names from the BILLING_NAME field, and then adds columns for first and last names:

,REGEXP_EXTRACT(TRIM(BILLING_NAME),'(^\\S*)',1) AS GIVEN_NAME
,REGEXP_EXTRACT(TRIM(BILLING_NAME),'((?<=\\s).*)',1) AS SURNAME
,TRIM(BILLING_NAME) AS `BILLING_NAME`

Fixed-width fields

Some data sources contain fixed-width fields. Use a combination of the TRIM() and SUBSTR() functions within a custom domain table to define the length of each field in the file.

For example:

SELECT
  TRIM(SUBSTR(col_1, 2, 35)) AS NAME_LINE1,
  TRIM(SUBSTR(col_1, 416, 20)) AS FIRST_NAME,
  TRIM(SUBSTR(col_1, 436, 15)) AS MID_NAME,
  TRIM(SUBSTR(col_1, 451, 20)) AS LAST_NAME,
  TRIM(SUBSTR(col_1, 37, 35)) AS ADDR_LINE1,
  TRIM(SUBSTR(col_1, 109, 35)) AS ADDR_LINE2,
  TRIM(SUBSTR(col_1, 72, 30)) AS CITY,
  TRIM(SUBSTR(col_1, 102, 2)) AS STATE,
  TRIM(SUBSTR(col_1, 104, 5)) AS ZIP,
  TRIM(SUBSTR(col_1, 280, 10)) AS PHONE,
  TRIM(SUBSTR(col_1, 621, 60)) AS EMAIL_ADDRESS,
  TRIM(SUBSTR(col_1, 1, 1)) AS GENDER
FROM custom-domain-table-name

Hash PII data that has been resent to Amperity

Some segments send results downstream to support CCPA and/or GDPR workflows. Some CCPA and GDPR workflows send this data back to Amperity, which typically requires the data to be hashed using a domain table.

For example, to hash the name, email, and phone fields in a table named “tohash_ccpa”:

SELECT
  *
  ,SHA2(UPPER(TRIM(firstname))) AS Hash_firstname
  ,SHA2(UPPER(TRIM(lastname))) AS Hash_lastname
  ,SHA2(UPPER(TRIM(email))) AS Hash_email
  ,SHA2(UPPER(TRIM(phone_number))) AS Hash_phone
FROM tohash_ccpa

Parse fields with multiple separators

Sometimes incoming data will contain data that should be tagged with more than one semantic tag, but also contain different separators within the incoming field. For example:

----------- ---------- ------------------- ------- ---------------------- -------
 firstName   lastName   street              poBox   location               zip
----------- ---------- ------------------- ------- ---------------------- -------
 John        Smith      123 Main            #101    US - Yelm , WA         98597
 Andy        Jones      456 South Avenue            US - Bellingham, WA    98115
 Anne        Andersen   999 S. Bergen Way           US - Seattle ,WA       98104
----------- ---------- ------------------- ------- ---------------------- -------

where “location” represents country, city, and state, always separated with a dash ( - ) between the country and city, and then a comma ( , ) between the city and the state. Some fields contain extra white space between and around the strings.

The “location” field needs to be split into individual city, state, and country fields, the two delimiters can be removed, along with the extra whitespace.

Use domain SQL similar to the following:

SELECT
  location
  ,TRIM(SPLIT(location, '-')[0]) AS country
  ,TRIM(SPLIT(SPLIT(location, '-')[1],',')[0]) AS city
  ,TRIM(SPLIT(location, ',')[1]) AS state
FROM domain_table

and then tag the city, state, and country fields with the appropriate semantic tags.

Reference custom domain tables

A custom domain table may reference another custom domain table. For example:

SELECT
  order_id
  ,two.order_id
FROM custom_domain_table1 one
LEFT JOIN custom_domain_table2 two ON one.order_id = two.order_id

Semantic tags and bridge tables

You must use a custom domain table to apply semantic tags to data sources that are made available to Amperity using an Amperity Bridge sync. The most common use cases for applying semantic tags are the same as data sources that are made available without using Amperity Bridge sync. These use cases include customer profiles and transactions, and then apply customer keys and foreign keys. Any semantic tag may be used with tables that are made available using Amperity Bridge.

Set non-US-ASCII email addresses to NULL

The following CASE statement decodes customer emails, identifies customer emails that are not encoded using the US-ASCII character set, and then sets them to NULL.

CASE
  WHEN UPPER(DECODE(UNBASE64(customer_email),'US-ASCII')) = 'UNDEFINED'
  THEN NULL
  ELSE UPPER(DECODE(UNBASE64(customer_email),'US-ASCII'))
END AS email,

Standardize values for USA states

The following example standardizes values for all fifty states in the United States to only a two-character value (AK, AL, AR, etc.). The CASE statement uses the following strings to determine:

1. The correct two-character value

2. The correct spelled out value

3. Other variations that appear in the data, which may be common (or known) abbreviations, misspellings, slang, or shortcuts

CASE
  WHEN UPPER(TRIM(COALESCE(state))) IN ('AL','ALABAMA', 'BAMA') THEN 'AL'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('AK','ALASKA') THEN 'AK'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('AZ','ARIZONA') THEN 'AZ'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('AR','ARKANSAS') THEN 'AR'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('CA','CALIF','CALIFORNIA','CALIFORNIZ','CALIFRONIA') THEN 'CA'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('CO','COLORADO') THEN 'CO'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('CT','CONNECTICUT', 'CONNETICUT') THEN 'CT'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('DE','DELAWARE', 'DELWARE') THEN 'DE'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('FL','FLORIDA') THEN 'FL'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('GA','GEORGIA') THEN 'GA'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('HI','HAWAII', 'HAWAI\'I') THEN 'HI'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('ID','IDAHO') THEN 'ID'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('IL','ILLINOIS') THEN 'IL'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('IN','INDIANA') THEN 'IN'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('IA','IOWA') THEN 'IA'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('KS','KANSAS') THEN 'KS'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('KY','KENTUCKY') THEN 'KY'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('LA','LOUISIANA', 'LOUSIANA') THEN 'LA'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('ME','MAINE') THEN 'ME'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('MD','MARYLAND') THEN 'MD'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('MA','MASS','MASSACHUSETES','MASSACHUSETTS','MASSACHUSETTES') THEN 'MA'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('MI','MICHIGAN') THEN 'MI'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('MN','MINNESOTA') THEN 'MN'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('MS','MISSISSIPPI') THEN 'MS'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('MO','MISSOURI') THEN 'MO'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('MT','MONTANA') THEN 'MT'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('NE','NEBRASKA') THEN 'NE'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('NV','NEVADA') THEN 'NV'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('NH','NEW HAMPSHIRE') THEN 'NH'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('NJ','NEW JERSEY', 'JERSEY') THEN 'NJ'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('NM','NEW MEXICO') THEN 'NM'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('NY','NEW YORK') THEN 'NY'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('NC','NORTH CAROLINA') THEN 'NC'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('ND','NORTH DAKOTA') THEN 'ND'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('OH','OHIO') THEN 'OH'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('OK','OKLAHOMA') THEN 'OK'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('OR','ORE','OREGON','OREGONE') THEN 'OR'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('PA','PENNSYLVANIA') THEN 'PA'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('RI','RHODE ISLAND') THEN 'RI'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('SC','SOUTH CAROLINA') THEN 'SC'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('SD','SOUTH DAKOTA') THEN 'SD'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('TN','TENNESSEE') THEN 'TN'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('TX','TEXAS') THEN 'TX'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('UT','UTAH') THEN 'UT'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('VT','VERMONT') THEN 'VT'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('VA','VIRGINIA') THEN 'VA'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('WA','WASHINGTON') THEN 'WA'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('WV','WEST VIRGINIA') THEN 'WV'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('WI','WISCONSIN') THEN 'WI'
  WHEN UPPER(TRIM(COALESCE(state))) IN ('WY','WYOMING') THEN 'WY'
ELSE NULL

Update blocklists

The bad-values blocklist uses a regular expression to identify domain tables. Domain tables are built using a source:feed pattern, whereas custom domain tables use a SQL-safe pattern that uses underscores (_) instead of a colon (:) as a delimiter. When custom domain table names are present, the default regular expression will not identify the underscores (and any related custom domain tables), and may return NULL values.

If a blocklist returns NULL values and if custom domain tables are present, update the regular expression in the SELECT statements for the following sections:

• bad_addresses

• bad_emails

• bad_phones

For each SELECT statement, change:

REGEXP_EXTRACT(datasource, '.+?(?=:)') AS datasource,

to:

COALESCE(REGEXP_EXTRACT(datasource, '.+?(?=:)'), '*') AS datasource,

This update will allow these SELECT statements to continue using a regular expression to find domain tables, and then use * to find custom domain tables and will prevent NULL values from being returned.

Added columns

Amperity adds the following columns to all domain tables. The added columns start with underscores (_) and are used by Amperity during Stitch processing.

1. The _pk column is an identifier that is generated based on the all of the columns in the feed that were associated to the primary key.

2. The _uuid_pk column contains a system-generated UUID. This UUID helps Amperity distribute workers during Stitch processing.

3. The _updated column contains details about the last update; it is a system-generated 64-bit integer that combines a timestamp with file/line information.

   Amperity uses the value in the _updated column to ensure that the newest record is preferred over older records when both records have the same primary key. This preference is maintained between loads, between records in the same file, and between files/days in the same load.

These columns will be available in the customer 360 database when a domain table is configured as a passthrough table.

Stitched domain tables

All domain tables to which semantic tags are added and/or to which a foreign key is added that can be used to associate records in a domain table to an Amperity ID are processed by Stitch. This is in addition to all domain tables to which customer profile semantic tags were applied, and then were made available to Stitch.

A stitched domain table is created for each domain table to which semantic tags were applied and/or in which a foreign key exists that allows Amperity to link the records in that domain table to an Amperity ID that exists in a standard core table.

Stitched versions of domain tables have an added column for the Amperity ID and replace the source domain table within Amperity for all downstream use cases, but are otherwise identical to the source domain table.

How-tos

This section describes tasks related to managing domain tables in Amperity:

• Add domain table

• Add custom domain table

• Add linkage table

• Delete domain table

• Delete records

• Edit domain table

• Explore domain table

• View sample data

• View schema

• Purge domain table

• Rename domain table

• Search domain tables

• View domain table

• View domain table history

Add domain table

A domain table is created by a feed. The domain table name is a combination of the <source-name>:<feed-name>.

Add custom domain table

A custom domain table is table that is created from a Spark SQL query built against one (or more) source domain tables to build a table with reshaped data. The custom domain table is made available to downstream processes, such as Stitch or the customer 360 database, in the same manner as source domain tables.

Feeds quickly load data and apply a standard schema to customer data. Use a custom domain table to load this data in its raw form, and then reshape it to support any downstream workflow. For example:

• Enabling privacy rights workflows to help remove data based on individual requests from customers, as required by CCPA and GDPR.

• Applying semantic tags to data that contains transactions details, including extending the schema and adding new fields.

• Supporting workflows that require filtered sets of raw clickstream data.

To add a custom domain table

1. From the Sources page, under Custom Domain Tables, click Add Table. The Create Custom Table dialog box opens.

2. Add the name of the custom domain table, and then click Save. The Custom Domain Table page opens.

   Important

   All custom domain table names must be unique within the same tenant.

   Note

   The specific purpose of any custom domain table varies by tenant and by use case. That said, a very common use case for custom domain tables is to reshape data that has already been loaded by a feed into the structure required by Amperity for tagging data sources that contain interaction records with transaction and itemized transaction semantics.

3. Add SQL using Spark SQL to define the custom domain table. This is typically in the form of a SELECT statement that returns fields from a feed, but may be more complex if necessary.

   Tip

   Do any required data shaping to support interaction records, in particular for transactions and itemized transactions. Refer to the semantics reference for all requirements for all semantic tags. Refer to the data_tables reference for what types of fields you should expect to be available.

4. Click Validate to verify that the SQL query runs correctly.

5. On the Configure and save page, update for interaction records.

   Apply Transactions to any column in the data schema that can be matched with transactions semantics.

   Note

   Other semantic tags may be applied, including for customer records and product catalogs. Tables that contain only transactions, itemized transactions, or product catalog semantic tags are generally not made available to Stitch.

6. Click Activate.

Example: Unified transactions

WITH uit_rollup AS (
  SELECT
    order_id,
    MIN(order_datetime) AS order_datetime,
    SUM(IF(is_return IS NULL AND is_cancellation IS NULL, COALESCE(item_quantity, 1), 0)) AS order_quantity,
    SUM(IF(is_return IS NULL AND is_cancellation IS NULL, item_revenue, 0)) AS sum_item_revenue,
    SUM(IF(is_return = TRUE, COALESCE(item_quantity, -1), 0)) AS order_returned_quantity,
    SUM(IF(is_return = TRUE, item_revenue, 0)) AS order_returned_revenue,
    SUM(IF(is_cancellation = TRUE, COALESCE(item_quantity, -1), 0)) AS order_canceled_quantity,
    SUM(IF(is_cancellation = TRUE, item_revenue, 0)) AS order_canceled_revenue
  FROM
    Unified_Itemized_Transactions
  GROUP BY 1)

SELECT
  ut.amperity_id
  ,fk_onlinecustid AS customer_id
  ,ut.order_id
  ,ut.datasource
  ,ut.store_id
  ,ut.digital_channel
  ,ut.purchase_channel
  ,ut.purchase_brand
  ,uitr.order_datetime
  -- If order_revenue is not provided, replace with this:
  -- ,uitr.sum_item_revenue - ut.{order_discount_amount_field} AS order_revenue
  -- or this:
  -- ,uitr.sum_item_revenue AS order_revenue
  ,ut.order_revenue
  ,uitr.order_quantity
  ,uitr.order_returned_quantity
  ,uitr.order_canceled_quantity
  ,uitr.order_returned_revenue
  ,uitr.order_canceled_revenue
  -- Add in custom semantics as necessary. For example:
  --,ut.currency
  --,ut.order_shipping_amount
FROM
  uit_rollup uitr JOIN
  Unified_Transactions ut
  ON uitr.order_id = ut.order_id

Example: Loyalty programs

WITH Loyalty_cte AS (
  SELECT
    amperity_id
    ,row_number() OVER w AS row_number
    ,first(lm_id) OVER w AS Loyalty_Member_id
    ,first(lmProgramName) OVER w AS Loyalty_Program_Name
    ,first(current_tier) OVER w AS Loyalty_Tier
    ,SUM(points) OVER w AS Loyalty_Points
    ,MIN(created) OVER w AS Loyalty_Program_Join_Date
  FROM Loyalty_Members
  WINDOW w AS (PARTITION BY amperity_id ORDER BY created DESC)
)
SELECT
  amperity_id
  ,Loyalty_Member_id
  ,Loyalty_Program_Name
  ,Loyalty_Tier
  ,Loyalty_Points
  ,Loyalty_Program_Join_Date
FROM Loyalty_cte
WHERE row_number = 1

– and –

WITH info_from_last_update AS (
  SELECT
    amperity_id
    ,sort_array(collect_list(struct(
      created
      ,lm_id
      ,lmProgramName
      ,current_tier
      ,name_first
      ,name_last
      ,email
      ,gender
      ,address1
      ,city
      ,state
      ,postal_code
      ,birthdate
    )), false)[0] AS rep_row
  FROM Loyalty_Members
  WHERE amperity_id IS NOT NULL
  AND created IS NOT NULL
  GROUP BY amperity_id
),

other_info AS (
  SELECT
    amperity_id
    ,SUM(points) AS Loyalty_Points
    ,MIN(created) AS Loyalty_Program_Join_Date
  FROM Loyalty_Members
  WHERE amperity_id IS NOT NULL
  GROUP BY amperity_id
)

SELECT
  t1.amperity_id
  ,t1.rep_row.lm_id AS Loyalty_Member_id
  ,t1.rep_row.lmProgramName AS Loyalty_Program_Name
  ,t1.rep_row.current_tier AS Loyalty_Tier
  ,t2.Loyalty_Points
  ,t2.Loyalty_Program_Join_Date
FROM info_from_last_update AS t1
LEFT JOIN other_info AS t2 ON t1.amperity_id = t2.amperity_id

Add linkage table

To add a linkage table

1. Open the Sources page.

2. Under Custom domain tables click Add table.

3. Write SQL to specify which CDT records link to which source records. This will be four columns specifying the source table name, source table pk, cdt table name, and cdt pk.

4. Click Next.

5. Tag the the source table name with compliance/source-ds, source table pk with compliance/source-pk, cdt table name with compliance/cdt-ds, and cdt pk with compliance/cdt-pk.

6. Click Activate.

Delete domain table

Use the Delete option to remove a domain table from Amperity. This should be done carefully. Verify that both upstream and downstream processes no longer depend on this domain table prior to deleting it. This action will not delete the feeds associated with the domain table.

To delete a domain table

1. From the Sources page, open the menu for a domain table, and then select Delete. The Delete Domain Table dialog box opens.

2. Click Delete.

Delete records

Users who are assigned the Allow source data deletion policy option can delete records from a domain table.

Use one of the following options to find the records to be deleted:

1. Older than a date

2. Within a timeframe

3. With a matching value

Records that match will be deleted from the domain table.

Older than a date

You can delete all records in a domain table that are older than a date.

To delete records older than a date

1. From the Sources page, open the menu for a domain table, and then select Delete records. The Delete records dialog box opens.

2. Under Record criteria, select “Older than a set date”.

   Select a field in the domain table with a datetime data type, and then select a date. You may use relative dates.

3. Click Preview deletion, and then review the list of records that are returned.

4. Click Delete records. In the Remove records dialog box, confirm that you want to delete the list of records by clicking Remove records.

Within a timeframe

You can delete all records in a domain table that exist between two dates.

To delete records using a timeframe

1. From the Sources page, open the menu for a domain table, and then select Delete records. The Delete records dialog box opens.

2. Under Record criteria, select “Within a set timeframe”.

   Select a field in the domain table with a datetime data type, and then select the start and end dates for the timeframe. You may use relative dates.

   Note

   End dates are exclusive.

3. Click Preview deletion, and then review the list of records that are returned.

4. Click Delete records. In the Remove records dialog box, confirm that you want to delete the list of records by clicking Remove records.

With a matching value

You can delete records in a domain table that meet specific conditions. For example, records that match the domain in an email address (“email is like amperity.com”) or records that match a specific email address (“email is john@amperity.com”).

To delete records with a set value

1. From the Sources page, open the menu for a domain table, and then select Delete records. The Delete records dialog box opens.

2. Under Record criteria, select “With a set value”.

   Select a field in the domain table, choose a condition, and then specify a value.

3. Click Preview deletion, and then review the list of records that are returned.

4. Click Delete records. In the Remove records dialog box, confirm that you want to delete the list of records by clicking Remove records.

Edit domain table

A domain table cannot be edited directly. The data within the domain table is updated based on feed and courier settings. The name of the domain table is directly associated with the feed and its schema. Changes made to the feed (or feed schema) will update the data in the domain table automatically.

Explore domain table

The Data Explorer provides a detailed way to navigate through data tables in Amperity. The Data Explorer displays each column in the data table as a row, with the column name, data type, associated semantic, and a data example. A sample of real table data is available available on another tab.

Click the name of the domain table to open the data explorer. The data explorer opens to provide a view of the schema for a domain table, the data that is in the table, and example rows of data.

To explore a domain table

1. From the Sources page, under Domain Tables, click the name of a domain table. The Data Explorer page opens.

2. Browse the columns and rows. Click Schema to view the schema and Samples to view sample data.

3. Click Close when finished.

View sample data

Sample data is a representation of about 100 rows of data appear in the domain table.

To view sample data in a domain table

1. From the Sources page, under Domain Tables, click the name of a domain table. The Data Explorer page opens.

2. Click Samples to view sample data.

3. Click Close when finished.

View schema

The schema shows how data in the domain table maps to the semantic tagging applied by the feed.

To view the schema for a domain table

1. From the Sources page, under Domain Tables, click the name of a domain table. The Data Explorer page opens.

2. Click Schema to view the schema.

3. Click Close when finished.

Tip

The number of records in a domain table may not match the number of records loaded by Amperity after loading data. Amperity uses an UPSERT process when loading data and determines priority based on the Last Updated Field. If a large difference exists take a close look at the primary key and determine if the primary key is the cause.

Publish to Queries page

Domain tables are automatically published and made available to the Queries page in a “database” named Domain tables even when these domain tables are not part of your customer 360 database.

Custom domain tables may be made available to the same database. To publish custom domain tables to the Queries page, from the Sources page, next to Custom Domain Tables, click Publish to queries.

All custom domain tables will be published. Allow for this process to complete before writing queries against published domain tables.

Purge domain table

When primary keys for a table are updated the data in the existing domain table must be purged, and then reloaded upon feed reactivation.

Note

Purging a domain table removes all data from the existing table, and then stops. The data is reloaded the next time a courier pulls data to Amperity for that feed or after the feed is run manually. Downstream workflows, such as Stitch or database generation, are not run automatically after data is purged from a domain table.

A domain table cannot be purged directly (though it may be deleted). Operations that may cause a purge of domain table data are initiated when a feed is edited. For example, when the primary key is changed. Purging data ensures the Stitch process does not have to deal with both new and old data.

Rename domain table

You cannot rename a domain table directly because of their dependency on feeds. If you need to rename a domain table you must re-create the source/feed pair by adding a new feed, and then deleting the old feed to remove the old domain table from Amperity.

Search domain tables

You can search for the presence of fields and semantic tags that are present in domain tables.

To search domain tables

1. From the Sources page, next to Domain Tables, enter the search term.

2. The search results are displayed automatically, grouped by domain table, then by column name, and then by semantic tag.

3. Select a column name to open the data explorer for that domain table.

View domain table

Domain tables are visible from the Sources page under Domain Tables.

Note

An individual table may contain both customer and interaction records. As part of the Stitch process, customer records and interaction records are split into dedicated tables for use within the customer 360 database.

View domain table history

Domain table history shows accurate row counts a domain table, along with the ability to view a history of updates that were made to that domain table.

Use domain table histories to:

• Identifying issues that may arise with the data in the table

• View the types of changes that occur over time

To view domain table history

1. From the Sources page, open the menu for a domain table, and then select History. The Table History dialog box opens.

2. Select Filter unchanged transactions to remove transactions that did not change the data within the domain table.

3. Click Load more to load additional rows to the table history.

4. When finished, click Close.