This tutorial will guide you through the process of querying data in Semarchy xDM using the SQL API. You will learn how to run basic and advanced queries, as well as important integration concepts.

This tutorial is based on PostgreSQL and pgAdmin 4. You can run the same queries on SQL Developer for Oracle or SQL Server Management Studio for SQL Server, or use a universal client such as DBeaver.

Learning outcomes

Learning track

This tutorial is the second SQL-based unit within the Data Publishing & Consumption track. Before beginning this unit, you must:

If you have not completed these prerequisites, return to the Tutorials menu.


Otherwise, enjoy this tutorial!

Knowing how to query data using SQL is a critical step to building a successful MDM program.

While you ordinarily would rely on a middleware tool or a user-designed program to consume data from xDM, it is still important for you, as the integration developer, to understand the consumption methods described in this tutorial unit, starting with simple queries.

Learning outcomes

Get all customer golden records

The most basic query selects Person records from the golden data (GD) table. You will also select specific columns which is a more useful query for analyzing data.

  1. Run the following query in pgAdmin (or another SQL client) to select all columns from the Person golden data (GD_PERSON) table:
    gd_person gd;

These are all the customer golden records, with all available columns.

  1. Run the following query to select only the golden record ID, first_name, and last_name columns:
    gd_person gd;

These are all the customer golden records with only the golden record ID, first_name, and last_name columns.

Get master and golden records side-by-side

When looking at matched customer records, it is useful to analyze groups of master records alongside the resulting golden record, in order to compare how the master records were deduplicated. To get this view, you must join the master data (MD) table with the golden data (GD) table:

  1. Run the following query to select the publisher ID, source ID, first name, and last name from the master records, as well as the golden record ID, first name, and last name from the golden records.
    md.b_pubid publisherid,
    md.b_sourceid sourceid,
    md.first_name masterfirstname,
    md.last_name masterlastname, goldenid,
    gd.first_name goldenfirstname,
    gd.last_name goldenlastname
    md_person md
    inner join gd_person gd on =
order by asc;

The join is on the golden ID, which appears on both the master and golden tables.

  1. Observe the differences between master (highlighted in blue in the screenshot below) and golden (highlighted in yellow) data.

  1. Have a closer look at customer Jass Ellerbusch (golden record ID 10017):

Next, you will get a golden record given the source ID.

Query golden records with a given source ID

At times, you might need to look up a golden record, but all you have is an ID from the source system. To query the golden records when you know their source ID, proceed as follows:

  1. Run the following query to select the golden record related to a source record from the CRM system having source ID 1483060.
    md.b_pubid publisherid,
    md.b_sourceid sourceid,
    md.first_name masterfirstname,
    md.last_name masterlastname, goldenid,
    gd.first_name goldenfirstname,
    gd.last_name goldenlastname
    md_person md
    inner join gd_person gd on ( =
        md.b_pubid = 'CRM'
        md.b_sourceid = '1483060'; /* SourceID */
  1. Have a look at the result of the query:


You have successfully run basic queries via SQL to get information in xDM.

In this section, you created:

Next, you will execute advanced queries to retrieve information, such as customer records and the products they purchased.

Now that you are familiar with basic SQL queries, this section will delve into advanced queries to expand the scope of analyses you can perform.

Learning outcomes

Query customers' records and their products

You can query customers and see the products they have purchased. This is useful for analytics and BI tools to answer questions like "Which customers have purchased a certain product?" or "Who are our top-paying customers?"

  1. Run the following query to select the customer's golden record ID, first name, last name, and the products each customer has bought.
from gd_person pe
    inner join gd_person_product pp on = pp.f_person
    inner join gd_product pr on = pp.f_product
order by pe.first_name desc;
  1. Have a look at the results:

Next, you will query master records to see the corresponding duplicate masters.

Side-by-side duplicates

In this section, you will learn to query a specific master record and see the corresponding master records that are considered duplicates side-by-side. The query will also show the corresponding golden record that the master records consolidate into.

  1. Run the following query:
   md.b_pubid dup1_publisher,
   md.b_sourceid dup1_id,
   md.first_name dup1_first_name,
   md.last_name dup1_last_name,
   md2.b_pubid dup2_publisher,
   md2.b_sourceid dup2_id,
   md2.first_name dup2_first_name,
   md2.last_name dup2_last_name, gold_id,
   gd.first_name golden_first_name,
   gd.last_name golden_last_name
from md_person md
   inner join gd_person gd on (
   inner join md_person md2 on (
where   md.b_pubid = 'CRM'           /* Publisher ID */
   and md.b_sourceid = '1419728'    /* Source ID */
/* and md.b_pubid = md2.b_pubid */   /* uncomment this line to restrict the duplicates to those within the CRM application */;
  1. Note that the dup2_publisher and dup2_id columns show the master records that matched with the record of Antonia Mattos with ID 1419728 from CRM.


You have successfully run advanced queries via SQL to get information in xDM.

To summarize, you have created:

Next, you will learn how integration works to understand the concepts to allow you to write your own queries.

Now that you have explored a data location with several SQL queries, it is time to learn more about some integration concepts.

Learning outcomes


When you load data into xDM, we refer to the source applications where the data came from as publishers.

An application that publishes data into xDM is assigned a publisher code in the Application Builder. This code is known as the publisher ID and is loaded into the column b_pubid.

The publisher ID is necessary for loading data. It is also useful when you query data to trace which applications the master data came from. xDM displays the publisher ID in the master record's ID in the Customer B2C Demo application.

Types of records

Semarchy xDM deals with several types of records that correspond to different steps of the integration process:

The transformation process from source records to golden data within Semarchy xDM is called the data certification process or integration process.

Matching and survivorship

Matching is a key step within the data certification process. Its role is to detect duplicates to consolidate them into a golden record. There are two different types of matching:

When records are matched together, survivorship rules select for a given attribute the most appropriate value from the various source records.

Entity types

Each entity you design in the Application Builder has a given entity type that defines the entity's capabilities for matching, merging, and authoring. Entity types are:


You have gained a better understanding of fundamental integration concepts in xDM.

Next, we will have a more detailed look at each type of entity.

If you have two records with the same ID in different systems, duplicate data within the same system (which is common with customer data), or no common identifier across systems, you will need to match records and consolidate them to become a "best-of-breed" golden record.

Fuzzy-matching entities

A fuzzy-matching entity is designed to handle the scenario where there is no common identifier across systems. A golden record ID will then be generated when records are consolidated. As such, you will define match and survivorship rules with a fuzzy-matching entity.

We will now look at how primary keys work in fuzzy-matching entities.

Fuzzy-matching-entity primary keys

The primary key is useful for filtering data in your queries, as you learned when querying golden records based on a given source ID.

In practice

Here is an example of a fuzzy-matching entity:

  1. Navigate to the Customers view under the Browse Data section of the navigation drawer. Customer data is modeled using a fuzzy-matching entity.
  2. Look for the Person record with the name Callie Aldaba.

  1. Click on this customer to explore their information. Note the value of the golden record ID (in our example, 10039).

  1. Navigate to the Master records tab.

  1. Note the two master records that contributed to this customer's golden record: CRM.1386237 and MKT.1438732.
    We will retrieve these values in the database later on.

  1. Now, switch to your SQL client and take a look at the database.
  2. Start by querying the GD_PERSON table:
    gd_person gd;

The golden data customer table (GD_PERSON) contains the golden record ID (ID column).
b_pubid and b_sourceid are the publisher ID and source ID which won the survivorship process.

  1. Continue exploring by scrolling right.

b_mastercount is the number of master records underlying the golden record.
b_confscore is the confidence score of the matching and b_confirmationstatus indicates whether master records have been confirmed within a golden record or not.

  1. Scroll to the right again.

These are the golden data fields of the values that have won the survivorship process.

  1. Query the master table MD_PERSON:
    md_person md;

Observe that the master data table stores the publisher and source IDs as primary keys.

  1. Scroll to the right until you find the ID column: this column provides the golden record ID associated with the master record.

Also note that the MD table stores information about the matching process, such as match group (b_matchgrp), confirmation status (b_confirmationstatus), confidence score (b_confscore), and suggested merge ID (b_suggmergeid).

  1. Now run the following query to retrieve all master records related to the Customer golden record of Callie Aldabasubstitute /* golden ID for Callie Aldaba */ with the ID you retrieved in the Customer B2C Demo application (10039 in our example):
select golden_id,
   gd.first_name golden_first_name,
   gd.last_name golden_last_name,
   md.b_pubid md_pub_id,
   md.b_sourceid md_sourceid,
   md.first_name md_first_name,
   md.last_name md_last_name
from md_person md
   inner join gd_person gd on (
where  /* Golden ID */

This query returns the same master records as the ones observed using the user interface:

Fuzzy-matching-entity foreign keys

Because fuzzy-matching entities separate the publisher and source IDs in their primary keys, the references to fuzzy-matching entities, likewise, have foreign keys that separate publisher and source when it comes to source data.

Consider the following example, featuring the SD_COMM_CHAN_PREF table: the foreign key to Person source data in the SD_COMM_CHAN_PREF table is composed of two columns:

For golden data, the foreign key references the xDM system-generated ID and therefore uses a single column for the foreign key. For example in SD_COMM_CHAN_PREF, F_PERSON references the Customer golden record ID.

To illustrate that, we will start by reviewing references among entities.

  1. To do so, open your model diagram.

  1. Now, compare it to how the references are created in the database foreign keys for Communication Channel Preferences. To do so, look at the table GD_COMM_CHAN_PREF in pgAdmin.
  2. In the GD_COMM_CHAN_PREF table, scroll to the far right.

The F_PERSON foreign key is the parent Person golden record ID. In the same way, FP_PERSON is the parent publisher ID and FS_PERSON is the parent source ID.

  1. Now, repeat the process for GD_PERSON_PRODUCT. Remember that PersonProduct is a child of the fuzzy-matching entity Person and basic entity Product.

You can see that it contains both Product and Person foreign keys.

  1. Finally, notice that the primary key for the PersonProduct entity is concatenated from the columns F_PRODUCT, FP_PERSON, and FS_PERSON.

ID-matching entities

ID-matching entities assume that data comes from several applications that share a common ID.

Records in entities using ID matching are matched if they have the same ID and then merged into golden records. For such entities, the golden record ID is the common source ID (no ID is generated).

This entity type is well suited when there is a truly unique identifier for all the applications communicating with the MDM hub.


You have gained a better understanding of ID- and fuzzy-matching entities, along with insights into how they function in xDM.

Next, you will learn about basic entities.


In contrast to fuzzy-matching entities, basic entities are designed to handle data coming from a unique data source and thus do not support match and merge. This is suitable for simple reference data entities or when data is authored exclusively in the hub. In the latter case, xDM serves as a source system for authoring data.

Querying data in a basic entity is simpler than in a fuzzy-matching entity because the ID is the same across the source and golden (SD and GD) tables. Also, basic entities do not have the concept of a publisher or MD/MI tables. Therefore, you can use the same identifier in the source system to query basic entity records in the SD and GD tables and bypass joining with the master data (MD) table.

  1. To learn about basic entities, go to SA_NICKNAME.

  1. Now go to sa_product, another basic entity.

SA_PRODUCT uses the default ID physical name for its primary key. The product ID in this table remains the same as in the golden data GD_PRODUCT table.

We will now see how the basic entity primary key is configured in Semarchy xDM Application Builder.

  1. Open the CustomerB2CDemo model, right-click on the Nickname entity, and select Alter Entity.

  1. Click Next.

  1. The next page allows you to set up the primary key for this table.

With the Physical Column Name property, you can control the name of the primary key in the database. The Manual option relates to the ID generation method and means that you must load the ID and that you will not rely on xDM to generate it.

  1. Click on Cancel to quit.
  2. Edit the Product entity like we just did for Nickname.

Notice that the entity type (basic, fuzzy-matching, or ID-matching) can be selected on this page and that Product type is a basic entity.

  1. Press Next to access the primary key settings.

The column name of the primary key for the Product entity is ID (default name).

Also observe that the ID can be entered manually or generated using a sequence, a universal unique identifier (UUID), or a custom rule via a SemQL expression. The ID generation method is selected when the entity is created.

In practice

  1. Navigate to the Products view under the Browse Data section of the navigation drawer. Product data is modeled using a basic entity.
  2. Look for the product record with the name "Carbonite Helmet".

  1. Click on this product to see more information on the Product tab.

  1. Note the product record's ID: CARBON4861OK.
  2. Now switch to your SQL client and execute the following query:
select golden_id,
   gd.product_name golden_product_name,
   gd.description golden_desc, source_id,
   sa.product_name sa_product_name
from sa_product sa
   inner join gd_product gd on (
where'CARBON4861OK'  /* Golden ID */
  1. Observe that golden_id and source_id values are the same.

The demo data assumes that product IDs are consistent across all systems and, therefore, are suitable for a basic entity. This same ID flows from the source system to the Source Authoring (SA) table and eventually to the Golden Data (GD) table.

Basic foreign keys

In contrast to fuzzy-matching entities, basic entities do not separate the publisher and source IDs, neither at the source nor at the golden record level. Therefore, foreign keys that reference a basic entity use that parent entity's original foreign key.

For example, the SA_PERSON_PRODUCT table references both a fuzzy-matching entity (customer) and a basic entity (product):


You have successfully learned the basics about entity types in Semarchy xDM.

Here is a cheat sheet to remind you of the differences between fuzzy-matching, ID-matching, and basic entities:

This cheat sheet may come in handy to understand which entity type to use during development time, when you are building your model. It will also help you remember which tables are available and how to use primary keys when you query data from Oracle or PostgreSQL databases.

Next, you will learn to query errors that were raised during the integration process.

Two types of errors can be raised during the integration process:

The SE, AE, and GE tables serve as error queues and inform you why records failed integration. xDM saves the name of the constraint in b_constraintname and the type of the constraint in the b_constrainttype columns.

Get all errors on customer records

You can view pre-consolidation errors via the source error (SE) table.

  1. Run this first query:
select  *
from    se_person;
  1. Have a look at the results:

  1. Run this second query and compare the SE_PERSON view with the SD_PERSON.
select  *
from    sd_person
where   b_error_status = 'ERROR';
  1. Notice that this second query shows you that the SD table has a b_error_status column, which provides a simple way to detect records with errors.

The SD_PERSON table flags records as errors and provides customer information to guide troubleshooting. However, there is no information explaining why the records are in the error queue.

Source data and errors

In this section, you will learn how to join the SE and SD tables to view data, alongside constraint violation.

  1. Run this query and join SD_PERSON and SE_PERSON tables to view the reason for errors and the customer information required for troubleshooting.
from se_person se
inner join sd_person sd on (
       sd.b_pubid = se.b_pubid
   and sd.b_sourceid = se.b_sourceid
   and sd.b_loadid = se.b_loadid
where   sd.b_error_status = 'ERROR';
  1. Observe the record errors containing data values (from the SD table) and constraint violation details (from the SE table).

These records are in the error queue due to missing email in the source_email column. A validation rule in the CustomerB2CDemo model requires all customers to have an email address, leading to these records being identified as errors.

You also obtain business-relevant fields to aid troubleshooting.


You successfully queried xDM to check for errors and their causes using the SQL API.

Great job! You are now familiar with different types of queries that will prove useful in your tasks involving the SQL API.

Learning recap

Next steps

In the next unit of the Data Publishing & Consumption track, Load data via SQL, you will learn how to load data in xDM via the SQL API. You will learn basic and advanced loading queries as well as more integration concepts, such as how the integration process works.

To explore other resources, return to the Tutorials menu.


Thank you for completing this tutorial.