3.2 Data Stores, Entity Mapping & Table Publication

3.2 Data Stores, Entity Mapping & Table Publication

The Architecture of Appian Data Stores

A Data Store is an Appian design object that establishes the operational connection between Custom Data Types (CDTs) and physical relational database structures. While a CDT defines the shape and object-relational mapping rules of data in memory, the Data Store acts as the gateway that allows Appian processes, expression rules, and interfaces to query, write, and modify records in the target database.

Within a Data Store, each individual CDT-to-table binding is called a Data Store Entity (DSE). A single Data Store can contain multiple Data Store Entities, each representing a specific table or view in a designated relational database schema.

Configured Relational Sources

Select a business data source supported by the installed Appian release and the organization's infrastructure. The available source list and current system-requirements documentation determine supported engines and versions. Do not assume that every database supported by some integration is supported by every data-store operation.

Developers reference a Data Store Entity in SAIL expressions and process models using the constant design object or the syntax cons!MY_DATA_STORE_ENTITY. In Appian expressions, functions such as a!queryEntity() take this DSE reference to execute SQL queries safely without exposing raw database credentials.

---

Configuring Entity Mappings and Data Sources

When creating a Data Store, the developer selects a specific Data Source (a registered JDBC connection) and assigns an application-specific name and description. Inside the Data Store designer, the developer adds entity mappings by pairing:

1. An Entity Name: A logical identifier used within Appian expressions (e.g., Customer, InvoiceItem).
2. A Type (CDT): The specific Custom Data Type that models the table structure.

Appian automatically matches the CDT's JPA annotations (@Table, @Column, @Id) against the physical database catalog.

---

/* Standard expression rule querying the mapped data store entity */
a!queryEntity(
  entity: cons!DSE_CUSTOMER_RECORD,
  query: a!query(
    selection: a!querySelection(
      columns: {
        a!queryColumn(field: "customerId"),
        a!queryColumn(field: "legalName"),
        a!queryColumn(field: "accountStatus")
      }
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 50
    )
  ),
  fetchTotalCount: false
)

The Schema Verification Process

Before any Data Store can be saved and published, it must pass Schema Verification. Clicking the Verify button in the Data Store designer triggers Appian to compare the XSD definitions of all included CDTs against the current catalog schema of the target relational database.

---

Automated DDL Generation vs. Manual Database Administration

During schema verification, if tables or columns need to be created, Appian generates the necessary Data Definition Language (DDL) scripts in the background. Developers face two execution paths:

1. Automated Schema Updates ("Create tables automatically")

In development environments, developers frequently choose the automated option. When the developer clicks Save and Publish, Appian directly executes CREATE TABLE and ALTER TABLE ADD COLUMN statements against the database schema. This enables rapid prototyping without requiring DBA intervention.

Strict Safety Limitations of Automated DDL

Appian's automated DDL engine is intentionally conservative to prevent catastrophic data loss:

• No Drops: Appian never executes DROP TABLE or ALTER TABLE DROP COLUMN. If a field is deleted from a CDT, the physical database column remains untouched; Appian simply ceases mapping data to it.
• No Renaming: If a developer renames a field in a CDT (e.g., from custEmail to customerEmailAddress), Appian cannot deduce that this is a rename. Automated DDL will attempt to add a new column named customerEmailAddress, leaving custEmail intact with obsolete data.
• No Incompatible Alterations: Appian will not automatically shrink column lengths (e.g., VARCHAR(255) down to VARCHAR(50)) or change incompatible data types because doing so could truncate or corrupt existing data.

2. Manual Database Administration & DDL Export

In enterprise staged environments (Test, Stage, and Production), automated schema execution is typically prohibited by corporate compliance and security policies. Instead, developers use the Download DDL script button in the Data Store designer.

The exported DDL script contains standard SQL statements tailored to the specific database dialect (Oracle, SQL Server, MySQL, etc.). The developer supplies these scripts to a Database Administrator (DBA) who:

• Reviews constraints, default values, and foreign keys.
• Implements custom indexes, clustering keys, and partitioning schemes for performance optimization.
• Verifies storage engine configurations and table spaces.
• Executes the script during scheduled deployment windows under change ticket governance.

---

Publishing Data Stores, Schema Drift, and CDT Versioning

Publishing a Data Store commits the entity mappings and updates Appian's distributed runtime metadata cache across all execution engines.

Updating CDT Definitions and Schemas

Updating a CDT can automatically update dependent objects to the latest type, including data stores. If the updated data store verifies, Appian can publish it automatically. With automatic schema updates enabled both globally and locally, supported additive database changes may be applied. Existing running processes and old object versions require their own compatibility review.

Do not teach that every CDT edit leaves the data store permanently pinned to the old version until manually reopened. Instead, inspect the update result, affected dependents, verification outcome, and publication status. When verification fails, repair the source schema or mapping, verify again, and publish the intended definition with sufficient permissions.

A safe release checklist is: identify affected dependents; prepare database migrations; update the type; confirm mappings and verification; confirm publication; test both inserts and updates; and review active-process compatibility. Do not assume a successful save proves new data will persist correctly.

Best Practices for Data Store Organization & Modularity

In enterprise applications, proper architectural organization of Data Stores prevents deployment bottlenecks and dependency entanglements:

1. Modular Data Stores by Domain

Never place all application tables into a single monolithic Data Store. If an application contains 50 entities spanning Procurement, Human Resources, and Vendor Management, create separate Data Stores for each functional domain (e.g., PO_DataStore, HR_DataStore, VM_DataStore). Benefits include:

• Isolated Deployments: Publishing changes to HR entities does not lock or invalidate procurement cache.
• Granular Security: Access rights can be segregated across distinct functional teams.
• Reduced Lock Contention: Minimizes simultaneous check-outs by multiple developers in Appian Designer.

2. Data Store Security Roles

Like all Appian design objects, Data Stores enforce role-based security:

• Administrator: Can modify settings, add/remove entity mappings, download DDL, verify schema, publish, and delete the object.
• Editor: Can modify supported data-store definition properties; Save & Publish requires Administrator permission.
• Viewer: Can view configurations and query data through expressions. All business users and general developers writing interfaces require Viewer permission on the Data Store to execute a!queryEntity() and write data.
• Deny: Prevents any interaction with the Data Store.

---

Worked Scenario: A New Shipment Field

A developer adds trackingNumber to Shipment. Check whether the target table has the intended tracking_number column and whether the CDT maps to it. Review automatic dependent updates, schema verification, the published data store, and any old process versions still using the earlier type. Test a new shipment with a recognizable tracking value and query the persisted row.

If verification fails because the column is absent and automatic schema changes are disabled, apply the reviewed additive migration before verifying and publishing. If a column was renamed, preserve its existing data deliberately; adding a new empty column is not a data migration. The symptoms alone do not prove the data store was pinned to an older CDT version.

Exam Watchouts & Common Traps

• Publishing vs. Saving: Simply clicking "Save Changes" on a Data Store draft does not update runtime execution. You must click Save & Publish for entity changes to take effect across the environment.
• Automated DDL Never Deletes Columns: Exam questions often ask what happens when a column is deleted from a CDT. Remember: Appian never issues DROP statements automatically; existing columns and their data persist in the database.
• Schema Drift During Direct DB Changes: If a DBA modifies a column directly in the database (e.g., changing data type from INT to VARCHAR), Appian will not know until schema verification is run. At runtime, database write nodes may throw fatal JDBC execution errors.
• Security Inheritance: Granting Viewer access to a Process Model that writes to a Data Store does not automatically grant access to the Data Store itself. The process initiator or service account running the write operation must have at least Viewer access to the Data Store design object.

Source checked 2026-10-18: Appian CDT updates.