About Databases¶
The Customer 360 tab allows databases to be created from any combination of stitched output, passed-through domain tables, and custom domain tables. At least one database must be designated as your “Customer 360” database, but there is no limit to the number of databases you may configure for use with any downstream workflow. For example:
Use a passthrough database to separate raw source data from stitched customer data.
Use a QA database to build tables for use with validating Stitch quality and for ensuring that interactions records (for orders and items) are being measured correctly.
Use a custom database for experimentation.
Important
This topic assumes that you have completed the processes for adding customer records and adding interation records to Amperity and have created your customer 360 database.
About Spark SQL¶
Spark SQL is a high performance SQL query engine that is used by Amperity to ingest data, create domain tables, and extend the outcome of the Stitch process in your customer 360 database.
Use Spark SQL to build database tables.
About table types¶
Tables are organized into the following categories:
Standard tables¶
A standard database table forms the foundation of your databases. Standard tables are output by Stitch and provide unified data structures for all data sources to which semantic tags were applied.
Standard tables include:
Campaign_Recipients
Customer_360
Customer_Attributes
Email_Engagement_Attributes
Email_Engagement_Summary
Lifecycle_Events
Merged_Customers
Transaction_Attributes
Transaction_Attributes_Extended
Unified_Customer
Unified_Coalesced
Unified_Email_Events
Unified_Itemized_Transactions
Unified_Product_Catalog
Unified_Scores
Unified_Transactions
along with tables that are associated with predictive modeling and AmpIQ, such as Predicted_CLV_Attributes.
Source and custom tables¶
Source and custom tables make raw source data and custom domain tables available to your customer 360 database as a series of passthrough tables. Source and custom tables will vary from tenant to tenant, but typically represent data that can provide useful context to your customer profile data and to support a variety of upstream and downstream workflows, such as:
Behavioral data
Clickstream data
Marketing campaign response data
Display advertising performance data
Passthrough tables that contain original customer data
And so on …
Any domain table or custom domain table can be configured as a passthrough table for any database.
QA tables¶
A QA database table is output by Stitch for use with the Stitch QA process.
QA tables include:
Detailed_Examples
Stitch_Blocking_Keys
Stitch_Scores
Unified_Changes_Clusters
Unified_Changes_PKS
Unified_Coalesced
Unified_Preprocessed_Raw
Unified_Scores
Define customer profile¶
Customer profile details are pulled from the Customer_360 table, which represents all of your unified customer profiles, including:
Names (first names, last names), email addresses, physical addresses, phone numbers
Transaction details (first purchases, last purchases, total purchases, etc)
Other custom profile values that are unique to your company
You can choose to summarize these customer profile details directly on the Customer 360 tab.

Customer profile details are displayed in the following order:
Fields with an associated icon
Fields marked as a favorite
Fields without an associated icon or not marked as a favorite
Note
Profile details that appear in the Customer Profile section are selected during the Configure and Save step when configuring the Customer_360 table.
Manage databases¶
The Customer 360 tab may define any number of individual databases, in addition to your customer 360 database. For example, it is common to add a dedicated database from which you can manage the Stitch QA process.
The All Databases section of the Customer 360 tab lists all of the databases that are available, including both active and inactive databases.

Note
Only databases in an Active state may be run.
Add empty database¶
An empty database contains no tables. Use any combination of passthrough, SQL, or SQL template tables to build a custom database.
To add an empty database
From the Customer 360 tab click Create Database.
Enter the name of the database.
Click Create.
Add database from template¶
Use the Customer 360, Passthrough, or Stitch QA database templates to add a database that contains the set of tables for use with that type of database.
To add a database from a template
From the Customer 360 tab click Create Database.
Enter the name of the database.
From the Template drop-down, select “Customer 360”, “Passthrough”, or “Stitch QA”.
Click Create.
Customer 360 template¶
The “Customer 360” template adds tables based on the types of records that are available to Stitch. It is recommended to use this template after at least one data source for customer records and interactions records have been configured in the Sources tab.
The following tables are added by the “Customer 360” template when profile semantic tags are applied to customer records:
Customer_360
Merged_Customers
Unified_Coalesced
Unified_Customer
Unified_Scores
The following tables are added by the “Customer 360” template when transaction and itemized transaction semantic tags are applied to interactions records:
Customer_Attributes
Transaction_Attributes
Transaction_Attributes_Extended
Unified_Itemized_Transactions
Unified_Transactions
These tables link brand interactions to customer profiles and are a specific requirement of AmpIQ.
If the “Customer 360” template is used to create the customer 360 database before interactions records are also available, you must add those tables to the customer 360 database manually.
Passthrough template¶
A database that is created using the passthrough template will add a table for each domain table or custom domain table that can be passed through to the database.
Stitch QA template¶
The following tables are added by the “Customer 360” template when profile semantic tags are applied to customer records:
Detailed_Examples
Unified_Changes_Clusters
Unified_Changes_PKS
Unified_Coalesced
Unified_Preprocessed_Raw
Unified_Scores
Use this database to validate the quality of Stitch output.
Use multiple databases¶
You may configure more than one database to be a customer 360 database. Each customer 360 database will contain a set of standard tables, and then may have an optional set of source and custom tables added as passthrough tables.

Apply flexible merge rules¶
Amperity allows multiple databases to exist within the same tenant. Each database may define its own unique set of rules for merging customer profile data. These merge rules are configured using Spark SQL and each field within the merge rules can be customized.
For example, a tenant may have data sources from call centers, online transactions, and email platforms that may contain slightly different sets of customer profile data:

After loading this data to Amperity and assigning the Amperity ID to each of your customers, you can use flexible merge rules to support multiple customer 360 databases.
Your operations teams can combine prioritizing the most common values for each customer with deterministic matching
Your email marketing team can combine prioritizing customer profile values from your email platform with probabilistic matching
Your paid media team can combine all possible values to improve match rates on platforms like Google Ads and Facebook
Tip
Ask your Amperity implementation team for recommendations and best practices for how you can configure flexible merge rules to support all of your use cases.
Manage tables¶
Use any of the following methods to add tables to databases:
Add as passthrough¶
A passthrough table adds a table to the customer 360 database using an existing table without making any changes to its schema.
If a table already contains an Amperity ID, you may use a passthrough table.
To add a table as a passthrough table
From the Customer 360 tab, under All Databases, open the menu for a database, and then click Edit.
Click Add Table.
Set Build Mode to “Passthrough”, and then select a table from the drop-down menu.
Note
The name of the table is assigned automatically and defaults to the name of the table as shown in the drop-down menu. You may change the name of the table after selecting a table.
Update the name of the passthrough table, if necessary.
Verify semantic tags and ensure that fields that contain PII are marked correctly.
Make the table available to the Segment editor in AmpIQ by selecting the Make available in Visual Segment Editor option.
Note
Only tables that contain an Amperity ID may be made available to the Segment editor in AmpIQ and/or used with campaigns.
Click Activate to update the database with your changes.
Add as SQL¶
A SQL table adds a table to the customer 360 database using Spark SQL and a SELECT
statement to define the table’s schema.
Note
If a table was not stitched and/or does not have an Amperity ID, you must use SQL to associate the unique ID in that table to the Amperity ID. For example, CCUST associates the unique ID in a table to the Amperity ID:
SELECT
CCUST.amperity_id
,CTX.customer_id
,CTX.orderid
,CTX.purchasedate
,CTX.transactiontotal
,CTX.pointsearned
,CTX.numberofitems
,CTX.avgitemprice
,CTX.productcode
,CTX.productcategory
,CTX.storeorwebid
FROM POS_transactions AS CTX
LEFT JOIN POS_customers AS CCUST ON (CCUST.customer_id = CTX.customer_id)
To add a table as a SQL table
From the Customer 360 tab, under All Databases, open the menu for a database, and then click Edit.
Click Add Table.
Set Build Mode to “SQL”, and then define a SQL query using Spark SQL.
Click Validate to verify that the SQL query runs correctly.
Verify semantic tags and ensure that fields that contain PII are marked correctly.
Make the table available to the Segment editor in AmpIQ by selecting the Make available in Visual Segment Editor option.
Note
Only tables that contain an Amperity ID may be made available to the Segment editor in AmpIQ and/or used with campaigns.
Click Activate to update the database with your changes.
Add from table template¶
Use table templates to add tables to your database that use Amperity standard tables as their starting points. Each template provides complete SQL that matches the default use case for the table. Some templates require additional configuration within the SQL, including a series of steps that are required for using table templates with multi-brand databases.
To add a table using a SQL template
From the Customer 360 tab, under All Databases, open the menu for a database, and then click Edit.
Click Add Table.
Set Build Mode to “SQL”, open the Apply template drop-down menu, and then select a table template.
Update the name of the table template, if necessary.
Update the SQL in the table template to support your use case.
Important
Refer to the individual topic for each standard table template for more information about how to configure any required and/or recommended steps.
Verify semantic tags and ensure that fields that contain PII are marked correctly.
Make the table available to the Segment editor in AmpIQ by selecting the Make available in Visual Segment Editor option.
Note
Only tables that contain an Amperity ID may be made available to the Segment editor in AmpIQ and/or used with campaigns.
Click Activate to update the database with your changes.
Standard table templates¶
Table templates are available for all standard tables:
Multi-brand templates¶
Multi-brand templates should be used as a set of templates. Verify the brand for which you want to use brand-specific tables and that the requirements for the Unified_Product_Catalog table are met, and then ensure that the brand is available in the purchase_brand
field in your customer 360 database.
To add all multi-brand tables
For each table, from the Customer 360 tab, under All Databases, open the menu for a database, and then click Edit.
Click Add Table.
Set Build Mode to “SQL”, open the Apply template drop-down menu, and then select the appropriate table template.
Important
Multi-brand table templates should be configured in the following order:
Update the name of the table template to append the brand name to the table name.
Update the SQL in the table template to support using multiple brands with that table.
Verify semantic tags and ensure that fields that contain PII are marked correctly.
Make the table available to the Segment editor in AmpIQ by selecting the Make available in Visual Segment Editor option.
Note
Only tables that contain an Amperity ID may be made available to the Segment editor in AmpIQ and/or used with campaigns.
Click Activate to update the database with your changes.
Customer360¶
A brand-specific version of the Customer360 table with joined transaction attributes must be updated to specify the name of the brand as part of the table name and to specify the correct table name as part of the LEFT JOIN.
To add a brand-specific Customer360 table
Select the Customer360 with Transaction_Attributes + Brand Filters template.
Append the name of the brand at the end of the table name.
Update the LEFT JOIN for the brand-specific Transaction_Attributes table
Transaction_Attributes/*_{brand}*/ ta
Transaction_Attributes¶
A brand-specific version of the Transaction_Attributes table must be updated to specify the name of the brand as part of the table name, to update a FROM statement for the correct table name, and to specify the brand in the brand filter at the end of the query.
To add a brand-specific Transaction_Attributes table
Select the Transaction_Attributes + Brand Filters template.
Append the name of the brand at the end of the table name.
Update the FROM statement to replace
{brand}
with the correct name for the brand-specific Unified_Transactions table:Unified_Transactions/*_{brand}*/
Update the query at the end of the query to replace
{insert_purchase_brand_here}
with the name of the brand:AND purchase_brand = '{insert_purchase_brand_here}'
Transaction_Attributes_Extended¶
A brand-specific version of the Transaction_Attributes_Extended table must be updated to specify the name of the brand as part of the table name, to update a FROM statement for the correct table name, and to specify the brand in the brand filter at the end of the query.
To add a brand-specific Transaction_Attributes_Extended table
Select the Transaction_Attributes_Extended + Brand Filters template.
Append the name of the brand at the end of the table name.
Update the FROM statement to replace
{brand}
with the correct name for the brand-specific Unified_Transactions table:Unified_Transactions/*_{brand}*/
Update the query at the end of the query to replace
{insert_purchase_brand_here}
with the name of the brand:AND purchase_brand = '{insert_purchase_brand_here}'
Unified_Itemized_Transactions¶
A brand-specific version of the Unified_Itemized_Transactions table must be updated to specify the name of the brand as part of the table name and to specify the brand in the brand filter at the end of the query.
To add a brand-specific Unified_Itemized_Transactions table
Select the Unified_Itemized_Transactions + Brand Filters template.
Append the name of the brand at the end of the table name.
Update the brand filter at the end of the query to replace
{insert_purchase_brand_here}
with the name of the brand:WHERE uit.product_brand = '{insert_product_brand_here}'
Unified_Transactions¶
A brand-specific version of the Unified_Transactions table must be updated to specify the name of the brand as part of the table name and to specify the brand in the brand filter at the end of the query.
To add a brand-specific Unified_Transactions table
Select the Unified_Transactions + Brand Filters template.
Append the name of the brand at the end of the table name.
Update the brand filter at the end of the query to replace
{insert_purchase_brand_here}
with the name of the brand:WHERE ut.purchase_brand = '{insert_purchase_brand_here}'
Other templates¶
Table templates are available for other tables:
Customer360 that joins Transaction_Attributes
Merged_Households; this table requires access to address standardization lookup tables.
Validation alerts¶
The SQL editor shows a validation alert when syntax is detected in your query that has the potential to cause performance issues with database generation, the length of time it may take to complete running a query, or situations where you can improve the quality of your SQL syntax.
What should I do if my query has a validation alert?
A validation alert does not mean your SQL is invalid. If your query has a validation alert, review the alert, review your SQL, and consider alternates that can help you avoid potential performance issues or avoid the situation the alert describes. There are situations where the SQL you need is the SQL that is causing the validation warning. You may activate a query even when it contains a validation alert.
Examples of validation alerts include:
Implicit CROSS JOIN¶
An implicit CROSS JOIN
occurs when a query unintentionally returns a Cartesian product. A Cartesian product combines every item in the first table with every item in the second. For example, if table A has three items and table B has three items, the Cartesian product is 9 pairs.
A Cartesian product in Amperity between any two tables is often a very, very large number of pairs and, as a result, is an expensive operation. It is recommended to optimize your Spark SQL queries to avoid an implicit CROSS JOIN
.
The database editor will show a validation alert for an implicit CROSS JOIN
in situations like:
A join statement does not use an
ON()
orUSING()
clause to specify one (or more) column names as the join criteria.A
SELECT
statement returns too many columns from two tables. For example, usingSELECT * FROM Table_A, Table_B
can behave like aCROSS JOIN
. In this situation you should first filter outNULL
values to avoid performance issues, or refactor your query to be more specific about which columns from which tables are to be joined.
OVER without PARTITION BY¶
A window function with an OVER
statement that does not include a PARTITION BY
clause often leads to performance issues when the OVER
statement is asked to run across a large number of rows.
If you see this alert, add the PARTITION BY
clause to your window function.
Unescaped backslashes in regular expressions¶
Unescaped backslashes can cause errors with functions that use regular expressions, such as RLIKE()
, that are difficult to diagnose.
Caution
A backslash ( ) contained within a regular expression MUST be escaped using another backslash. For example, a regular expression that matches a single numeric digit should be \\d
and not \d
.
Unintentional broadcast joins¶
A broadcast join sends the smaller table in a join operation to all Spark executors, and then evaluates the larger table across each executor’s partitions. When a broadcast join is small, it’s fast. Above a certain threshold they can performance issues.
A broadcast join often looks like:
WHERE column_name NOT IN values
When values
is not a small list of constants a broadcast join may occur.
Note
In some situations you may want to use a LEFT ANTI JOIN
, which returns values from the left-side table when they do not have matches on the right-side table. Use a LEFT ANTI JOIN
to avoid performance issues that may be caused by a broadcast join.
Extend databases¶
Extend databases to support any use case or requirement.
The following topics represent starting points for some of the ways you can extend your customer 360 database:
This list represents a small percentage of the use cases you can enable with Amperity. Talk with your Amperity representative about how you can best enable these (and other) use cases for your tenant.
Export databases and tables¶
A database may be configured to export one (or more) tables (or even the entire database) from Amperity. Each database export must be assigned a unique name, and then is configured to select one (or more) tables to be included in the export. A database export must be associated with a configured destination and must be added to an orchestration.
To add a database export
From the Customer 360 tab click Configure Exports. This opens the Database Exports page.
Click Create New Export. This opens the Add Export dialog box.
Add the name of the database export, and then click Add. This will add a draft database export to the Database Exports page.
Open the menu for the draft database export, and then select Edit.
From the Database drop-down, select a database.
From the Entities list, select one (or more) database tables to add to the database export.
For a single table, click the table. For multiple tables, use click + command for each table to be selected. For all tables click the first table, hold shift, and then click the last table.
Click Activate.
Assign the database export to an orchestration.