About Domain Tables¶

A domain table is created for each data feed that has been configured to ingest a data source into Amperity. The data in a domain table is processed to include semantic tagging alongside the original fields from the data feed. Domain tables that contain customer records are made available to the Stitch process to identify unique individuals and assign them Amperity IDs. Domain tables that contain interaction records are used to create attributes that are associated with the unique individuals who have been assigned Amperity IDs.

The Domain Tables section of the Sources tab 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.

Note

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

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.
The _uuid_pk column contains a system-generated UUID. This UUID helps Amperity distribute workers during Stitch processing.
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.

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 may be present in domain tables:

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, itemized 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 Metrics tab, the Segment Brief (a component within the Segments tab), predicted customer lifetime value (PCLCV) models, and the Campaigns tab.

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 is usable by 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 and itemized transactions.

These semantic tags generate Stitch output tables for Unified_Transactions, Unified_Itemized_Transactions, Transaction_Attributes, and Transaction_Attributes_Extended.

A 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. Spark SQL used for the purpose of building a domain table is referred to (in Amperity) as Domain SQL.

Note

A custom domain table should be designed to be as static as possible. A change to the customer record will often require a change to the Spark SQL and then will require a complete reload of that dataset.

Note

When a database is run, any custom domain table that has changed is run first, and then Stitch will run if any of those custom domain tables are configured for Stitch. 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
Hash PII data that has been resent to Amperity
Parse fields with multiple separators
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:

Find situations where the five- and four-digit codes are both present, and then combine them.
Find situations where only the five-digit code is present, and then use only the five-digit code.
Uses the CONCAT_WS function to return “zip_code” and “zip_code_plus_four” separated by “-“.
Use NULL for situations where the five-digit code is not present.
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:

Trim whitespace from before (or after) the first and last names.
Individually extract the first and last names from the field that contains the full name.
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`

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.

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:

The correct two-character value
The correct spelled out value
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.

How-tos¶

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

Add domain table
Add custom domain table
Delete domain table
Edit domain table
Explore domain table
View sample data
View schema
Purge domain table
Rename domain table
Search domain tables
Truncate domain table
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 and itemized transactions details, including extending the schema and adding new fields.
Supporting workflows that require filtered sets of raw clickstream data.

Important

A custom domain table may only reference a source domain table. A custom domain table may not reference a custom domain table.

To add a custom domain table

From the Sources tab, under Custom Domain Tables, click Add Table. The Create Custom Table dialog box opens.
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.
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.
Click Validate to verify that the SQL query runs correctly.
On the Configure and save page, update for interaction records.

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

Apply Transactions to any column in the data schema that can be matched with a 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.
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_cancelled_quantity,
    SUM(IF(is_cancellation = TRUE, item_revenue, 0)) AS order_cancelled_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_cancelled_quantity
  ,uitr.order_returned_revenue
  ,uitr.order_cancelled_revenue
  -- Add in custom txn/ 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

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

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

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

From the Sources tab, under Domain Tables, click the name of a domain table. The Data Explorer page opens.
Browse the columns and rows. Click Schema to view the schema and Samples to view sample data.
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

From the Sources tab, under Domain Tables, click the name of a domain table. The Data Explorer page opens.
Click Samples to view sample data.
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

From the Sources tab, under Domain Tables, click the name of a domain table. The Data Explorer page opens.
Click Schema to view the schema.
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.

Purge domain table¶

Occasionally Amperity will recommend to purge domain table data, such as when primary keys for a table are updated, when field names change, or when fields are removed. Purging domain tables will remove all data in the existing data table, and then will reload that data upon feed reactivation.

Purging this data ensures that Stitch only runs against a consistent set of field names, field values, and semantic tags. Purging this data also guarantees that old field names, old field values, and previously-applied semantic tags do not remain in the domain table and are not available to Stitch.

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, if the primary key is changed, if the schema changes, and so on. These types of changes will require data to be purged so as to not allow the Stitch process to 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

From the Sources tab, next to Domain Tables, enter the search term.
The search results are displayed automatically, grouped by domain table, then by column name, and then by semantic tag.
Select a column name to open the data explorer for that domain table.

Truncate domain table¶

You can empty the contents of a domain table directly from the Sources tab.

To truncate a domain table

From the Sources tab, open the menu for a domain table, and then select Truncate. The Truncate Feed dialog box opens.
Click Truncate.

View domain table¶

Domain tables are visible from the Sources tab 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

From the Sources tab, open the menu for a domain table, and then select History. The Table History dialog box opens.
Select Filter unchanged transactions to remove transactions that did not change the data within the domain table.
Click Load more to load additional rows to the table history.
When finished, click Close.