Connecting to Salesforce Data 360

For information on the available WHL files for supported environments, and how to install the appropriate file for your Python distribution, see Package Installation.

For information on the module to import, and how to configure the necessary connection properties in a connection string, see Establishing a Connection.

Other available connection properties can be used to configure other aspects of the connector capabilities.

Python Version Support

The CData Python Connector for Salesforce Data 360 can be installed and used in Python 3.10 or newer.

Salesforce Data 360 Version Support

The connector leverages the Salesforce Data 360 API to enable access to Salesforce Data 360 resources.

See Also

• Using the Connector: Establish connections and query Salesforce Data 360 through Python code.
• From SQLAlchemy: Use SQLAlchemy to establish a connection with dialect URL, and interact with Salesforce Data 360 data using mapped classes and Sessions.

Dependencies

The Python connectors require that Python 3.10 or newer be installed.

Installation

The CData Python Connector for Salesforce Data 360 is available as a WHL file for Windows, Linux, and Mac. Each connector is built using the Python 3.10 Stable ABI (indicated by the abi3 tag in the filename), so a single wheel supports any Python 3.10 or newer installation — there is no need to match your exact Python minor version. Use the "pip install" command with the appropriate WHL file for your platform.

Windows:

pip install cdata_salesforcedata360_connector-26.0.9655-cp310-abi3-win_amd64.whl

Linux:

pip install cdata_salesforcedata360_connector-26.0.9655-cp310-abi3-linux_x86_64.whl

macOS:

pip install cdata_salesforcedata360_connector-26.0.9655-cp310-abi3-macosx_12_0_arm64.whl

The macOS wheel supports arm64 (Apple Silicon) architectures only on macOS 12 and newer.

Regardless of the environment, certain distributions might require that the "pip3 install" command be used instead, to differentiate from a Python 2 distribution that might exist already. After installation, confirm whether the connector is successfully installed by running the "pip list" command. If "cdata_salesforcedata360_connector" is present in the list output by the command, then the installation was successful.

Upgrading

When upgrading, "pip install" does not automatically clean up old JRE files. To avoid leftover files that could cause JVM errors, uninstall the previous version before installing the new one.

Licensing

After the installation is complete, a separate step is needed to activate a license for the connector. Among the CData assets in the distribution's site packages, there is an install-license tool that activates this license. From within the distribution's site-packages folder, after navigating to the "cdata/installlic_salesforcedata360" folder, simply use a command like the below to activate the license. Omitting the <key> argument activates a trial license:

• Windows:
  

  ./install-license.exe <key>

• Linux / Mac:
  

  ./install-license.sh <key>

Sometimes, file access issues may cause pip to install the connector in a fallback file path that is not the python distribution's main or primary site-packages location. This can make it difficult to find where the connector was installed, and from there, the license activator. In that event, this python script below will print out the full file path of the connector's native file. This file will be stored in the mentioned cdata folder, from which the installlic_salesforcedata360 folder is trivial to find:

import os
import cdata.salesforcedata360
path = os.path.abspath(cdata.salesforcedata360.__file__)
print(path)

Uninstallation

If the connector needs to be uninstalled for any reason, do so by running the pip uninstall command, as in the example below:

pip uninstall cdata-salesforcedata360-connector

The objects available within our connector are accessible from the "cdata.salesforcedata360" module. To use the module's objects directly:

1. Import the module as follows:
   

   import cdata.salesforcedata360 as mod

2. To establish a connection string, call the connect() method from the connector object using an appropriate connection string, such as:
   

   mod.connect("InitiateOAuth=GETANDREFRESH;")

Connecting to Salesforce Data 360

Salesforce Data 360 supports authentication via the OAuth standard.

OAuth

Set AuthScheme to OAuth.

Desktop Applications

CData provides an embedded OAuth application that simplifies authentication at the desktop.

You can also authenticate from the desktop via a custom OAuth application, which you configure and register at the Salesforce Data 360 console. For further information, see Creating a Custom OAuth App.

Before you connect, set these properties:

• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthClientId (custom applications only): The Client ID assigned when you registered your custom OAuth application.
• OAuthClientSecret (custom applications only): The Client Secret assigned when you registered your custom OAuth application.

When you connect, the connector opens Salesforce Data 360's OAuth endpoint in your default browser. Log in and grant permissions to the application.

The connector then completes the OAuth process as follows:

• Extracts the access token from the callback URL.
• Obtains a new access token when the old one expires.
• Saves OAuth values in OAuthSettingsLocation so that they persist across connections.

Web Applications

Authenticating via the Web requires you to create and register a custom OAuth application with Salesforce Data 360, as described in Creating a Custom OAuth App. You can then use the connector to get and manage the OAuth token values.

This section describes how to get the OAuth access token, how to have the connector refresh the OAuth access token automatically, and how to refresh the OAuth access token manually.

Get the OAuth access token:

1. To obtain the OAuthAccessToken, set these connection properties:

   • OAuthClientId: The client Id in your custom OAuth application settings.
   • OAuthClientSecret: The client secret in your custom OAuth application settings.

2. Call stored procedures to complete the OAuth exchange:
   • Call the GetOAuthAuthorizationUrl stored procedure. Set the CallbackURL input to the callback URL you specified in your custom OAuth application settings. If necessary, set the Scope parameter to request custom permissions. The stored procedure returns the URL of the OAuth endpoint.
   • Navigate to the URL that the stored procedure returned in Step 1. Log in and authorize the web application. You are redirected back to the callback URL.
   • Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the code parameter in the query string of the callback URL. If necessary, set the Scope parameter to request custom permissions.

After you obtain the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the connector automatically refresh the OAuth access token, set these parameters on the first data connection:

• InitiateOAuth: REFRESH.
• OAuthClientId: The client Id in your custom OAuth application settings.
• OAuthClientSecret: The client secret in your custom OAuth application settings.
• OAuthAccessToken: The access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: The refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: The location where the connector saves the OAuth token values, which persist across connections.

On subsequent data connections, the connector obtains the values for OAuthAccessToken and OAuthRefreshToken from OAuthSettingsLocation.

Manual refresh of the OAuth Access Token:

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token.

First use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed.

Then set these properties:

• OAuthClientId: The client Id in your custom OAuth application settings.
• OAuthClientSecret: The client secret in your custom OAuth application settings.

Now call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken. This opens a new connection.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Headless Machines

To configure the connector to use OAuth with a user account on a headless machine, you must authenticate on another device that has an internet browser.

Do one of the following:

• Option 1: Obtain the OAuthVerifier value (see "Obtain and Exchange a Verifier Code", below).
• Option 2: Install the connector on a machine with a browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow (see "Transfer OAuth Settings", below).

Option 1: Obtain and exchange a verifier code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the embedded OAuth credentials, click Salesforce Data 360 OAuth endpoint to open the endpoint in your browser.
   • If you are using a Custom OAuthd Application, create the Authorization URL by setting the following properties:
     • InitiateOAuth: OFF.
     • OAuthClientId: The client Id assigned when you registered your custom OAuth application.
     • OAuthClientSecret: The client secret assigned when you registered your custom OAuth application.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.

2. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. Later you will set this in the OAuthVerifier connection property.

Next, you need to exchange the OAuth verifier code for OAuth refresh and access tokens. Set the following properties:

On the headless machine, set the following connection properties to obtain the OAuth authentication values.

• InitiateOAuth: REFRESH.
• OAuthVerifier: The verifier code.
• OAuthSettingsLocation: Persist the encrypted OAuth authentication values to the specified location.
• OAuthClientId (custom applications only): The client Id in your custom OAuth application settings.
• OAuthClientSecret (custom applications only): The client secret in your custom OAuth application settings.

After the OAuth settings file is generated, re-set these properties to connect:

• InitiateOAuth: REFRESH.
• OAuthSettingsLocation: The location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.
• OAuthClientId (custom applications only): The client Id assigned when you registered your custom OAuth application.
• OAuthClientSecret (custom applications only): The client secret assigned when you registered your custom OAuth application.

Option 2: Transfer OAuth settings

Before you connect on a headless machine, you must install and create a connection with the connector on a device that supports an internet browser. Set the connection properties as described above, in "Desktop Applications".

After you complete the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

After you have successfully tested the connection, copy the OAuth settings file to your headless machine.

At the headless machine, set these properties:

• InitiateOAuth: REFRESH.
• OAuthSettingsLocation: The location of your OAuth settings file. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.
• OAuthClientId (custom applications only): The client Id assigned when you registered your custom OAuth application.
• OAuthClientSecret (custom applications only): The client secret assigned when you registered your custom OAuth application.

OAuth Password Grant

Follow these steps to set up the Password Grant option:

1. Set the AuthScheme to OAuthPassword to perform authentication with the password grant type.
2. Set all the properties specified in either the web or desktop authentication sections above.
3. Set the User and Password to your login credentials.

Note: If you have enabled Session Settings > Lock sessions to the IP address from which they originated, make sure that your IP address does not change while using the connector. If the IP changes during the usage of the connector, an "INVALID_SESSION_ID" error is returned from Salesforce Data 360 and the connector will no longer be able to retrieve data. If you receive this error, ask your Salesforce Data 360 administrator to disable this configuration or make sure to configure a static IP for the instance where you are using the connector. Then, reset the connection to continue using the connector.

OAuth Client Grant

To use an OAuth client grant, follow these steps:

1. Set the AuthScheme to OAuthClient to perform authentication with the client grant type.
2. Set all the properties specified in either the web or desktop authentication sections above.

OAuth PKCE

Follow these steps to set up OAuth PKCE authentication:

1. Set the AuthScheme to OAuthPKCE to perform authentication with PKCE.
2. InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
3. OAuthClientId: The client Id assigned when you registered your custom OAuth application.
4. OAuthClientSecret: The client secret assigned when you registered your custom OAuth application.
5. PKCEVerifier: The PKCE code verifier generated from executing the GetOAuthAuthorizationUrl stored procedure.

OAuthJWT

Set the AuthScheme to OAuthJWT.

To obtain the OAuthJWT consumer key:

1. Log in to Salesforce.com.
2. From Setup, enter Apps in the Quick Find box and then click the resulting link to create an app. In the Connected Apps section of the resulting page, click New.
3. Enter a name to be displayed to users when they log in to grant permissions to your app, along with a contact Email address.
4. Click Enable OAuth Settings and enter a value in the Callback URL box. This value is not needed for this type of authentication, but the Salesforce UI requires that it is set. The Callback URL is in the format:
   

   http://localhost:8019/src/oauthCallback.rst

5. Enable Use digital signatures.
6. Upload your certificate.
7. Select the scope of permissions that your app requests from the user.
8. Click your app name to open a page with information about your app. The OAuth consumer key is displayed.

After creating your OAuth Application, set the following connection properties:

• InitiateOAuth: GETANDREFRESH.
• OAuthJWTCert: The JWT certificate store.
• OAuthJWTCertType: The type of certificate store specified by OAuthJWTCert.
• OAuthJWTCertPassword: The password of the JWT certificate store.
• OAuthJWTIssuer: The OAuth Client ID.
• OAuthJWTSubject: The username (email address) of the permitted user profile configured in the connected OAuth app.

Note: This flow never issues a refresh token.

Java Native Interface (JNI) is a standard programming interface for writing Java native methods and embedding the Java virtual machine into native applications.

The connector leverages the JNI for improved performance on Mac and Linux.

Configure the Config INI File

The Linux and Mac editions of the Salesforce Data 360 python connector are configured with an ini file. This file is used to set several parameters, including JNI behavior. This file is to be located in:

{path_to_distribution_site-packages}/cdata/config.ini

Ensure that any configuration properties you set in the ini file fall under the following section name (adjust the 311 number if you are using an different python version from 3.11):

• For Linux:
  

  [salesforcedata360.cpython-311-x86_64-linux-gnu.so]

• For Mac:
  

  [salesforcedata360.cpython-311-darwin.so]

Configure the JNI connector's behavior by editing the properties in the connector's config.ini file. The connector can be configured as follows:

• LOGFILE: Set this the same way as the CDATA_LOGFILE envrionment variable below.
• JAVA_HOME: Configure the path to the JVM library location used to launch the JVM.
• CLASS_PATH: Use a colon-separated list to configure the paths to the third-party jar libraries.

Configure Environment Variables

Additionally, set the following environment variables:

• CDATA_JAVA_HOME: Configure the path to the JVM library location used to launch the JVM.
• CDATA_JVM_OPTIONS: Place JVM options here.
• CDATA_LOGFILE: Set this in the following scheme: <SCHEME>://<TAG>[|<LEVEL>]
  • SCHEME: The options are STDOUT, FILE.
    • STDOUT: Both the native wrapper and odbc core log into stdout. The Logfile and Verbosity properties can override the behavior of ODBC core.
    • FILE: The native wrapper logs into <FILENAME> while the odbc core logs into <FILENAME>.driver.log. The Logfile and Verbosity properties can override the behavior of ODBC core.
  • TAG
    • For STDOUT, set this to 1. For FILE, set this to the filename.
  • LEVEL
    • Set to one of: FATAL | ERROR | WARNING | INFO | DEBUG

The following are some examples of this syntax:

• STDOUT://1|DEBUG
• FILE:///tmp/my_py.log|DEBUG

Custom Logger

The Python connector supports a custom logging mechanism for redirecting log output to any destination, such as a cloud storage service or logging framework. Use setCustomLoggerFactory() to register a factory function that creates a logger instance for each connection.

The factory function receives the context string from the Logfile connection property (the portion after CUSTOM://) and must return an object with a writeLog(verbosity, message) method.

To enable custom logging:

1. Call setCustomLoggerFactory() with your factory function before opening connections.
2. Set Logfile to CUSTOM:// followed by a context string to identify the connection.
3. Set Verbosity to the desired log level.

The following example demonstrates a custom logger factory that creates a separate logger instance per connection:

import cdata.salesforcedata360 as mod
import time

class MyLogger:
    def __init__(self, loggerId):
        self.loggerId = loggerId
    def writeLog(self, verbosity, message):
        print("[MyLogger " + self.loggerId + "] " + message)

def createLogger(context):
    return MyLogger(context[len("MyLoggerId="):])

mod.setCustomLoggerFactory(createLogger)

conn1 = mod.connect("...;Logfile=CUSTOM://MyLoggerId=1;Verbosity=2;")
# do something with conn1
time.sleep(1)  # Wait for logs to flush from conn1

conn2 = mod.connect("...;Logfile=CUSTOM://MyLoggerId=2;Verbosity=2;")
# do something with conn2
time.sleep(1)  # Wait for logs to flush from conn2

General Changes

Date	Version	Source	Category	Type	Description
2026-05-27	26.0.9643	General	Connection	Removed	Removed the deprecated ReplaceInvalidTypesWithNull connection property. Use the ReplaceInvalidValuesWithNull property instead.
2026-05-22	26.0.9638	Python		Removed	Remove support for Intel x64 architecture on macOS
2026-05-08	26.0.9624	Salesforce Data 360	Connection	Changed	Changed the default option for the AuthScheme connection property to OAuthPKCE.
2026-05-07	26.0.9623	General	Data Model	Added	Added the ColumnCapabilities column to the sys_tablecolumns system table. This column is a bit mask denoting the column's write capabilities.
2026-05-07	26.0.9623	Python		Changed	Updated embedded JRE to jre-17.0.19+10 (Linux x64 / MacOs x64).
2026-05-06	26.0.9622	Salesforce Data 360	Query Exec	Added	Added IN and NOT IN server-side support for date/timestamp columns.
2026-04-15	26.0.9601	General	Query Exec	Changed	String comparisons using GREATER, LESS, and CONTAINS operators are now case-insensitive by default.
2026-04-01	26.0.9587	Salesforce Data 360	Compatibility	Changed	SalesforceDataCloud has been renamed to SalesforceData360.
2026-01-13	25.0.9509	General		Added	Added support for the REGEXP_REPLACE() string function.
2025-12-21	25.0.9486	Python		Added	Added support for custom loggers in Python connectors on Linux and macOS.
2025-12-05	25.0.9470	General		Added	Added support for the INSERT INTO SELECT statement, with driver-side execution for providers that do not support the operation natively.
2025-10-30	25.0.9434	Python		Changed	Updated embedded JRE to jre-17.0.17+10 (Linux x64 / MacOs x64).
2025-10-06	25.0.9410	General		Added	Support for parsing datetime formats using ".S" and ",S" for milliseconds and nanoseconds.
2025-09-12	25.0.9386	General		Added	Added the IsInsertable, IsUpdateable, and IsDeleteable columns to the sys_tables table.
2025-09-11	25.0.9385	Salesforce Data 360		Added	Introduced the QueryTimeout connection property, which specifies how long the driver waits for the server-side query to complete before timing out.
2025-09-11	25.0.9385	Salesforce Data 360		Changed	The driver uses the latest Salesforce Data 360 query endpoint. The service automatically determines the optimal pagesize. As a result, the default pagesize property is set to -1, which means the driver accepts the pagesize determined by the service. If you specify a particular pagesize, the driver complies with your setting. However, it's important to note that Salesforce can still return fewer rows than you requested.
2025-09-10	25.0.9384	General		Changed	All columns in statically defined Views are now reported as read-only.
2025-09-03	25.0.9377	General		Changed	Corrected the behavior when IN criteria with NULL values are used in the projection part. It now returns NULL instead of 0. For example, "NULL IN (1,2)" returns "NULL".
2025-09-01	25.0.9375	General		Added	Added support for using the CAST function with infinity values. This function can cast "inf" and "-inf" to DOUBLE, FLOAT, or REAL.
2025-08-21	25.0.9364	General		Changed	Report behavior change: Fixed inconsistent string value comparisons in non-table queries. For example, "SELECT 'A' = 'a'" previously returned false, but it now returns true.
2025-08-15	25.0.9358	Salesforce Data 360		Added	Added the Scope connection property.
2025-08-13	25.0.9356	General		Changed	Changed the maximum number of pages held in memory from 15 to 5 for the page providers to decrease heap usage.
2025-07-21	25.0.9333	Salesforce Data 360		Added	Added a new error message for Update and Upsert operations: The error is triggered when using an Ingest Data Stream with 'UPSERT' as the RefreshMode, which can overwrite existing data with NULL if not specified in the statement. To bypass this error, set the Overwrite column to true.
2025-07-07	25.0.9319	Python		Removed	Removed the 32-bit version of Windows Python.
2025-07-02	25.0.9314	Python		Removed	Removed support for Python 3.9.
2025-06-25	25.0.9307	General		Removed	Removed the "ADLS Gen 1" value from the ConnectionType property.
2025-06-25	25.0.9307	Python		Added	Added support for Python 3.13 in Windows, Linux, and Mac editions.
2025-06-25	25.0.9307	Python		Removed	Removed support for Python 3.8 as it is no longer supported.
2025-06-20	25.0.9302	General		Added	Created the following functions: TEXT_ENCODE: encodes a string into a different charset (UTF8 → UTF7 and returns a binary array as the result). TEXT_DECODE: takes a binary array and decodes it back into a string when provided the charset. BASE64_ENCODE: takes a binary array and encodes it as a base64 string (varchar). BASE64_DECODE: takes a base 64-encoded string and decodes it into a binary array.
2025-06-18	25.0.9300	General		Changed	The internal code for exception handling has been refactored. Exception messages returned during certain error conditions may now have different wording or formatting.
2025-05-27	25.0.9278	General		Removed	Removed the "Proprietary" enum option from ProxyAuthscheme.
2025-05-12	25.0.9263	Python		Changed	Updated embedded JRE to jre-17.0.15+6 (Linux x64 / MacOS x64) and jre-17.0.15+6 (MacOS aarch64).
2025-02-15	24.0.9177	General		Added	Added support for converting unsigned integer types to the nearest signed data type that has enough precision to hold the unsigned value.This is done for JDBC only because it does not have support for unsigned data types.
2024-11-27	24.0.9097	General		Added	Added ThreadId to LogModule output. Logfile lines now include the Thread ID associated with the action being performed.

This section provides a walk-through for writing Salesforce Data 360 data access code in Python script.

For more information on the available data source entities and how to query them with SQL, see Data Model. For the SQL syntax, see SQL Compliance.

Connecting from Code

For information on how to deploy the connector and configure the connection to Salesforce Data 360, see Package Installation and Establishing a Connection.

For information on how to connect with the salesforcedata360.connector module and its related classes, see Connecting.

Executing SQL

The connection's cursor object is used to directly execute SQL queries. For information on how to execute SELECT statements and process the returned result sets, see Querying Data. For information on to modify the data in Salesforce Data 360 with INSERT, UPDATE, and DELETE statements, see Modifying Data .

Executing Stored Procedures

You can call stored procedures by using the EXECUTE statement. For further information, see Calling Stored Procedures.

Connecting with the cdata.salesforcedata360 Module:

The connector's module is used directly to establish a connection with the data source. It does this by using a connection string as its argument. For example:

import cdata.salesforcedata360 as mod
conn = mod.connect("InitiateOAuth=GETANDREFRESH;")

Once the connection is created, you can use it to execute subsequent SQL queries.

After connecting as described in Connecting, you can use the open connection to execute SQL statements.

Executing Queries

To execute SQL statements that return data, use the execute() method. Once a query is executed, the result set is fetched from the cursor. This result set can then be iterated over to process the records individually.

For example:

cur = conn.execute("SELECT Id, Name FROM Account")
rs = cur.fetchall()
for row in rs:
	print(row)

Parameterized Queries

Various Python collections, such as arrays and tuples, can act as additional arguments for the execute() method. This enables you to parameterize the queries executed and help to prevent SQL Injection.

For example:

cmd = "SELECT Id, Name FROM Account WHERE Industry = ?"
params = ["Floppy Disks"]
cur = conn.execute(cmd, params)
rs = cur.fetchall()
for row in rs:
	print(row)

The connection is also used to issue INSERT, UPDATE, and DELETE commands to the data source. Parameters can be used with these statements if desired.

Note that the connector does not support transactions. As with normal write operations, all SQL statements executed by this connector affect the data source immediately. Call the connection's commit() method following the execution.

You can execute stored procedures using either the execute() or callproc() method of the connection.

Calling Stored Procedures Using Execute()

When you call stored procedures by issuing EXECUTE commands, the stored procedure arguments are parameterized. For example:

cmd = "EXECUTE SelectEntries ObjectName = ?"
params = ["Account"]
conn.execute(cmd, params)

Calling Stored Procedures Using Callproc()

When you call stored procedured by issuing the callproc() method, the stored procedure arguments are a procedure name and a list of parameters. For example:

cur = conn.cursor()
params = ["Account"]
cur.callproc("SelectEntries", params)

The connector is integrated with other tools and packages within Python.

Python Integration Guides

The following sections show how to create and use connections with the connector in common packages in Python:

• From SQLAlchemy
• From Pandas
• From Matplotlib
• From Petl

Complete List of Salesforce Data 360 Integration Quickstarts

For information on connecting from other applications, see Salesforce Data 360 integration guides.

The CData Python Connector for Salesforce Data 360 includes a Dialect class that enables integration with SQLAlchemy. Bear in mind that several aspects of connector functionality are not currently supported in SQLAlchemy 2.0 or above. If necessary, downgrade SQLAlchemy to version 1.4 or 1.3 before using this connector.

The following sections detail various aspects of this integration:

Connecting From SQLAlchemy

To construct a URL with which SQLAlchemy loads and uses the appropriate connector automatically, see Connecting

Reflecting Metadata With SQLAlchemy

To learn how to model Salesforce Data 360 tables with mapped classes, see Reflecting Metadata.

Querying Data From SQLAlchemy

To learn how to use mapped classes to query the associated tables, see Querying Data.

Modifying Data From SQLAlchemy

The connector provides INSERT/UPDATE/DELETE functionality in SQLAlchemy. To learn how to call the session's execute() method to affect the data in the data source, see Modifying Data.

Connecting With a Dialect URL

Establishing a connection using SQLAlchemy requires a specific URL format.

from sqlalchemy import create_engine
engine = create_engine("salesforcedata360:///?InitiateOAuth=GETANDREFRESH;")

For SQLAlchemy 2.0, the dialect name is salesforcedata360_2. To establish a connection, use the following URL format:

from sqlalchemy import create_engine
engine = create_engine("salesforcedata360_2:///?InitiateOAuth=GETANDREFRESH;")

SQLAlchemy can act as an Object-relational Map (ORM). This enables you to treat records of a database table as instantiable records. To leverage this functionality, you must reflect the underlying metadata in one of the following ways.

Note: The following examples employ SQLAlchemy 1.4.

Modeling Data Using a Mapping Class

Use "sqlalchemy.ext.declarative.declarative_base" to declare a mapping class for the table you wish to model in the ORM. A known table in the data model is modeled either partially or completely, as shown in the following example:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Account(Base):
	__tablename__ = "Account"
	Id = Column(String, primary_key=True)
	Id = Column(String)
	Name = Column(String)

Automatically Reflecting Metadata

Rather than mapping tables manually, SQLAlchemy can discover the metadata for one or more tables automatically. To accomplish this across the entire data model, use automap_base:

from sqlalchemy import MetaData
from sqlalchemy.ext.automap import automap_base
meta = MetaData()
abase = automap_base(metadata=meta)
abase.prepare(autoload_with=engine)
Account = abase.classes.Account

You can also reflect a single table with an inspector. When reflecting this way, providing a list of specific columns to map is optional:

from sqlalchemy import MetaData, Table
from sqlalchemy import inspect
meta = MetaData()
insp = inspect(engine)
Account_table = Table("Account", meta)
insp.reflect_table(Account_table, ["Id","Name"])

After you use the steps in Connecting to connect, and use one of the methods in Reflecting Metadata to reflect some of the metadata, you can use a session object to query data.

Querying Data Using the Query Method

If the mapping class has been prepared, use it with a session object to query the data source. After binding the engine to the session, provide the mapping class to the session's query method.

For example:

engine = create_engine("salesforcedata360:///?InitiateOAuth=GETANDREFRESH;")
factory = sessionmaker(bind=engine)
session = factory()
for instance in session.query(Account).filter_by(Industry="Floppy Disks"):
	print("Id: ", instance.Id)
	print("Id: ", instance.Id)
	print("Name: ", instance.Name)
	print("---------")

Querying Data Using the Execute Method

The session object can also run the query with the execute() method alongside the appropriate Table object. Assuming you have an active session, the following is just as viable:

Account_table = Account.metadata.tables["Account"]
for instance in session.execute(Account_table.select().where(Account_table.c.Industry == "Floppy Disks")):
	print("Id: ", instance.Id)
	print("FullName: ", instance.Name)
	print("City: ", instance.BillingCity)
	print("---------")

Implicit Joining

If mapped classes of related Salesforce Data 360 objects have a singular foreign key relationship, the classes are implicitly joined. After importing the necessary objects, a relationship is established between your two mapped classes, as in the example below:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, DateTime, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship

Base = declarative_base()
class Contact(Base):
	__tablename__ = "Contact"
	Id = Column(Integer, primary_key=True)
	Name = Column(String)
	Email = Column(String)
	BirthDate = Column(DateTime)
	AccountId = Column(String, ForeignKey("Account.Id"))
	Account_Link = relationship("Account", back_populates="Contact_Link")

class Account(Base):
	__tablename__ = "Account"
	Id = Column(String, primary_key=True)
	Name = Column(String)
	BillingCity = Column(String)
	NumberOfEmployees = Column(Integer)
	Contact_Link = relationship("Contact", order_by=Contact.Id, back_populates="Account_Link")

Once the relationship is established, the tables are queried simultaneously using the session's query() method. For example:

rs = session.query(Account, Contact).filter(Account.Id == Contact.AccountId)
for Ac, Ct in rs:
  print("AccountId: ", Ac.Id)
  print("AccountName: ", Ac.Name)
  print("ContactId: ", Ct.Id)
  print("ContactName: ", Ct.Name)

Other Join Forms

In situations where mapped classes have either no foreign keys or multiple foreign keys, you may need different forms of the JOIN query to accommodate them. Using the earlier classes as examples, the following JOIN queries are possible as well:

• Explicit condition (necessary if there are no foreign keys in your mapped classes):
  

  rs = session.query(Account, Contact).join(Contact, Account.Id == Contact.AccountId)
  for Ac, Ct in rs:

• Left-to-right relationship:
  

  rs = session.query(Account, Contact).join(Account.Contact_Link)
  for Ac, Ct in rs:

• Left-to-right relationship with explicit target:
  

  rs = session.query(Account, Contact).join(Contact, Account.Contact_Link)
  for Ac, Ct in rs:

• String form of a left-to-right relationship:
  

  rs = session.query(Account, Contact).join("Contact_Link")
  for Ac, Ct in rs:

SQLAlchemy ORM also exposes support for other clauses in SQL, such as ORDER BY, GROUP BY, LIMIT, and OFFSET. All of these are supported by this connector:

ORDER BY

The following example sorts by a specified column using the session object's query() method:

rs = session.query(Account).order_by(Account.AnnualRevenue)
for instance in rs:
	print("Id: ", instance.Id)
	print("Id: ", instance.Id)
	print("Name: ", instance.Name)
	print("---------")

You can also use the session object's execute() method perform an ORDER BY. For example:

rs = session.execute(Account_table.select().order_by(Account_table.c.AnnualRevenue))
for instance in rs:

GROUP BY

The following example uses the session object's query() method to group records with a specified column:

rs = session.query(func.count(Account.Id).label("CustomCount"), Account.Id).group_by(Account.Id)
for instance in rs:
	print("Count: ", instance.CustomCount)
	print("Id: ", instance.Id)
	print("---------")

You can also use the session object's execute() method to perform a GROUP BY:

rs = session.execute(Account_table.select().with_only_columns([func.count(Account_table.c.Id).label("CustomCount"), Account_table.c.Id]).group_by(Account_table.c.Id))
for instance in rs:

LIMIT and OFFSET

The following example uses the session object's query() method to skip the first 100 records and fetch the following 25:

rs = session.query(Account).limit(25).offset(100)
for instance in rs:
	print("Id: ", instance.Id)
	print("Id: ", instance.Id)
	print("Name: ", instance.Name)
	print("---------")

You can also use the session object's execute() method to set a LIMIT or OFFSET:

rs = session.execute(Account_table.select().limit(25).offset(100))
for instance in rs:

Certain aggregate functions can also be used within SQLAlchemy by using the func module.

To import this module, execute:

from sqlalchemy.sql import func

Once func is imported, the following aggregate functions are available:

COUNT

The following example counts the number of records in a set of groups using the session object's query() method.

rs = session.query(func.count(Account.Id).label("CustomCount"), Account.Id).group_by(Account.Id)
for instance in rs:
	print("Count: ", instance.CustomCount)
	print("Id: ", instance.Id)
	print("---------")

You can also execute COUNT using the session object's execute() method:

rs = session.execute(Account_table.select().with_only_columns([func.count(Account_table.c.Id).label("CustomCount"), Account_table.c.Id])group_by(Account_table.c.Id))
for instance in rs:

SUM

This example calculates the cumulative amount of a numeric column in a set of groups.

rs = session.query(func.sum(Account.AnnualRevenue).label("CustomSum"), Account.Id).group_by(Account.Id)
for instance in rs:
	print("Sum: ", instance.CustomSum)
	print("Id: ", instance.Id)
	print("---------")

You can also invoke SUM using the session object's execute() method.

rs = session.execute(Account_table.select().with_only_columns([func.sum(Account_table.c.AnnualRevenue).label("CustomSum"), Account_table.c.Id]).group_by(Account_table.c.Id))
for instance in rs:

AVG

This example uses the session object's query() method to calculate the average amount of a numeric column in a set of groups:

rs = session.query(func.avg(Account.AnnualRevenue).label("CustomAvg"), Account.Id).group_by(Account.Id)
for instance in rs:
	print("Avg: ", instance.CustomAvg)
	print("Id: ", instance.Id)
	print("---------")

You can also use the session object's execute() method to invoke AVG:

rs = session.execute(Account_table.select().with_only_columns([func.avg(Account_table.c.AnnualRevenue).label("CustomAvg"), Account_table.c.Id]).group_by(Account_table.c.Id))
for instance in rs:

MAX and MIN

This example finds the maximum value and minimum value of a numeric column in a set of groups.

rs = session.query(func.max(Account.AnnualRevenue).label("CustomMax"), func.min(Account.AnnualRevenue).label("CustomMin"), Account.Id).group_by(Account.Id)
for instance in rs:
	print("Max: ", instance.CustomMax)
	print("Min: ", instance.CustomMin)
	print("Id: ", instance.Id)
	print("---------")

You can also use the session object's execute() method to invoke MAX and MIN:

rs = session.execute(Account_table.select().with_only_columns([func.max(Account_table.c.AnnualRevenue).label("CustomMax"), func.min(Account_table.c.AnnualRevenue).label("CustomMin"), Account_table.c.Id]).group_by(Account_table.c.Id))
for instance in rs:

Commands can be executed individually by the session with a call to "execute()".

Obtaining the Table Object

The query supplied to this method is constructed using the associated Table object of a mapped class. This Table object is obtained from the mapped class's metadata field, as below:

Account_table = Account.metadata.tables["Account"]

Once the table object is obtained, the write operations are executed in the following ways. The queries are executed immediately without the need for a call to "commit()":

When combined with the connector, Pandas can be used to generate data frames that contain your Salesforce Data 360 data. Once created, a data frame can be passed to various other Python packages.

Connecting

Pandas relies on an SQLAlchemy engine to execute queries. Before you can use Pandas you must import it:

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine("salesforcedata360:///?InitiateOAuth=GETANDREFRESH;")

Querying Data

In Pandas, SELECT queries are provided in a call to the read_sql() method, alongside a relevant connection object. Pandas executes the query on that connection, and returns the results in the form of a data frame, which can be used for a variety of purposes.

df = pd.read_sql("""
	SELECT
	   Id,
	   Name,
     $exNumericCol;
	FROM Account;""", engine)
print(df)

Modifying Data

To insert new records into a table, create a new data frame, and define its fields accordingly. When that is done, call to_sql() on the data frame to perform the INSERT operation with the connector, as shown in the example below. You must set the "if _exists" argument to "append" to prevent Pandas from attempting building the table from scratch. To prevent Pandas from writing the data frame index as a column, set index=False.

df = pd.DataFrame({"Id": ["Jon Doe"], "Name": ["John"]})
df.to_sql("Account", con=engine, if_exists="append", index=False)

Matplotlib contains a number of tools that can graphically model Salesforce Data 360 data after being fed a data frame From Pandas.

Using PyPlot

Before any Matplotlib tool, such as pyplot, can be used, it must be imported:

from matplotlib import pyplot as plt

Once a Pandas data frame is obtained, it can be used to create a plot visualizing Salesforce Data 360 data. For example, the following plot generates and displays a bar graph relating Id and AnnualRevenue values:

df.plot(kind="bar", x="Id", y=["AnnualRevenue"])
plt.show()

The connector can be used to create ETL applications and pipelines for CSV data in Python using Petl.

Install Required Modules

Install the Petl modules using the pip utility.

pip install petl

Connecting

After you import the modules, including the CData Python Connector for Salesforce Data 360, you can use the connector's connect function to create a connection using a valid Salesforce Data 360 connection string. If you prefer not to use a direct connection, you can use a SQLAlchemy engine.

import petl as etl
import cdata.salesforcedata360 as mod
cnxn = mod.connect("InitiateOAuth=GETANDREFRESH;")

Extract, Transform, and Load the Salesforce Data 360 Data

Create a SQL query string and store the query results in a DataFrame.

sql = "SELECT	Id, Name FROM Account "
table1 = etl.fromdb(cnxn,sql)

Loading Data

With the query results stored in a DataFrame, you can load your data into any supported Petl destination. The following example loads the data into a CSV file.

etl.tocsv(table1,'output.csv')

Modifying Data

Insert new rows into Salesforce Data 360 tables using Petl's appenddb function.

table1 = [['Id','Name'],['Jon Doe','John']]
etl.appenddb(table1,cnxn,'Account')

The extension supports schema discovery by using SQL queries to available System Tables.

Using SQL

The following sections describe the discovery of metadata through several System Tables:

• Tables and Views
• Columns
• Procedures

The connector possesses system tables that are used to discover the tables and views available in the data model. Of these system tables, "sys_tables" and "sys_views" are used to fetch information about the available tables and views respectively:

Tables

import cdata.salesforcedata360 as mod
conn = mod.connect("InitiateOAuth=GETANDREFRESH;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_tables"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

Views

import cdata.salesforcedata360 as mod
conn = mod.connect("InitiateOAuth=GETANDREFRESH;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_views"
cur.execute(cmd, params)
rs = cur.fetchall()
for row in rs:
	print(row)

The available columns for any given table are fetched from a system table called "sys_tablecolumns". A specific table name is provided in the WHERE criteria to restrict the table from which the column information is fetched:

import cdata.salesforcedata360 as mod
conn = mod.connect("InitiateOAuth=GETANDREFRESH;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_tablecolumns WHERE TableName = 'Account'"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

Procedures

A system table called "sys_procedures" is queried to obtain the available stored procedures that are executed:

import cdata.salesforcedata360 as mod
conn = mod.connect("InitiateOAuth=GETANDREFRESH;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_procedures"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

Parameters

The input parameters of any stored procedure are similarly obtained from the "sys_procedureparameters" system table:

import cdata.salesforcedata360 as mod
conn = mod.connect("InitiateOAuth=GETANDREFRESH;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'SelectEntries'"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

The CData Python Connector for Salesforce Data 360 supports several operations on data, including querying, deleting, modifying, and inserting.

SELECT Statements

See SELECT Statements for a syntax reference and examples.

See Data Model for information on the capabilities of the Salesforce Data 360 API.

CACHE Statements

CACHE statements allow granular control over the connector's caching functionality. For a syntax reference and examples, see CACHE Statements.

For more information on the caching feature, see Caching Data.

EXECUTE Statements

Use EXECUTE or EXEC statements to execute stored procedures. See EXECUTE Statements for a syntax reference and examples.

Names and Quoting

• Table and column names are considered identifier names; as such, they are restricted to the following characters: [A-Z, a-z, 0-9, _:@].
• To use a table or column name with characters not listed above, the name must be quoted using square brackets ([name]) in any SQL statement.
• Parameter names can optionally start with the @ symbol (e.g., @p1 or @CustomerName) and cannot be quoted.
• Strings must be quoted using single quotes (e.g., 'John Doe').

The connector provides functions that are similar to those that are available with most standard databases. These functions are implemented in the CData provider engine and thus are available across all data sources with the same consistent API. Three categories of functions are available: string, date, and math.

The connector interprets all SQL function inputs as either strings or column identifiers, so you need to escape all literals as strings, with single quotes. For example, contrast the SQL Server syntax and connector syntax for the DATENAME function:

• SQL Server:
  

  SELECT DATENAME(yy,GETDATE())

• connector:
  

  SELECT DATENAME('yy',GETDATE())

String Functions

These functions perform string manipulations and return a string value. See STRING Functions for more details.

Date Functions

These functions perform date and date time manipulations. See DATE Functions for more details.

Math Functions

These functions provide mathematical operations. See MATH Functions for more details.

ASCII(character_expression)

Returns the ASCII code value of the left-most character of the character expression.

• character_expression: The character expression.

                      SELECT ASCII('0');
                      --  Result: 48
                    

BASE64_ENCODE(input_binary)

Returns the Base64-encoded string form of a binary input.

• input_binary: The binary value to encode.

                        SELECT BASE64_ENCODE(BinaryData);
                    -- Result: 'QmFzZTY0RW5jb2RlZA=='
                    

BASE64_DECODE(input_string)

Returns the binary result of decoding a Base64-encoded string.

• input_string: The Base64-encoded string.

                        SELECT BASE64_DECODE('QmFzZTY0RW5jb2RlZA==');
                    -- Result: (binary output)
                    

CHAR(integer_expression)

Converts the integer ASCII code to the corresponding character.

• integer_expression: The integer from 0 through 255.

                      SELECT CHAR(48);
                      -- Result: '0'
                    

CHARINDEX(expressionToFind ,expressionToSearch [,start_location ])

Returns the starting position of the specified expression in the character string.

• expressionToFind: The character expression to find.
• expressionToSearch: The character expression, typically a column, to search.
• start_location: An optional character position to start searching for expressionToFind in expressionToSearch.

                      SELECT CHARINDEX('456', '0123456');
                      -- Result: 4

                      SELECT CHARINDEX('456', '0123456', 5);
                      -- Result: -1
                    

CHAR_LENGTH(character_expression),

Returns the number of UTF-8 characters present in the expression.

• character_expression: The set of characters to be evaluated for length.

				 SELECT CHAR_LENGTH('sample text') FROM Account LIMIT 1
				 -- Result: 11			
				

CONCAT(string_value1, string_value2, ..., string_valueN)

Returns the string that is the concatenation of two or more string values.

• string_value1: The first string to be concatenated.
• string_value2: The second string to be concatenated.
• string_valueN: (optional) Any additional strings to be concatenated.

                      SELECT CONCAT('Hello, ', 'world!');
                      -- Result: 'Hello, world!'
                    

CONTAINS(expressionToSearch, expressionToFind)

Returns 1 if expressionToFind is found within expressionToSearch; otherwise, 0.

• expressionToSearch: The character expression, typically a column, to search.
• expressionToFind: The character expression to find.

                      SELECT CONTAINS('0123456', '456');
                      -- Result: 1

                      SELECT CONTAINS('0123456', 'Not a number');
                      -- Result: 0
                    

ENDSWITH(character_expression, character_suffix)

Returns 1 if character_expression ends with character_suffix; otherwise, 0.

• character_expression: The character expression.
• character_suffix: The character suffix to search for.

                      SELECT ENDSWITH('0123456', '456');
                      -- Result: 1

                      SELECT ENDSWITH('0123456', '012');
                      -- Result: 0
                    

FILESIZE(uri)

Returns the number of bytes present in the file at the specified file path.

• uri: The path of the file from which to read the size.

				SELECT FILESIZE('C:/Users/User1/Desktop/myfile.txt');
				-- Result: 23684
				

FORMAT(value [, parseFormat], format )

Returns the value formatted with the specified format.

• value: The string to format.
• format: The string specifying the output syntax of the date or numeric format.
• parseFormat: The string specifying the input syntax of the date value. Not applicable to numeric types.

                      SELECT FORMAT(12.34, '#');
                      -- Result: 12

                      SELECT FORMAT(12.34, '#.###');
                      -- Result: 12.34

                      SELECT FORMAT(1234, '0.000E0');
                      -- Result: 1.234E3
                      
                      SELECT FORMAT('2019/01/01', 'yyyy-MM-dd');
                      -- Result: 2019-01-01
                      
                      SELECT FORMAT('20190101', 'yyyyMMdd', 'yyyy-MM-dd');
                      -- Result: '2019-01-01'
                    

HASHBYTES(algorithm, value)

Returns the hash of the input value as a byte array using the given algorithm. The supported algorithms are MD5, SHA1, SHA2_256, SHA2_512, SHA3_224, SHA3_256, SHA3_384, and SHA3_512.

• algorithm: The algorithm to use for hashing. Must be one of MD5, SHA1, SHA2_256, SHA2_512, SHA3_224, SHA3_256, SHA3_384, or SHA3_512.
• value: The value to hash. Must be either a string or byte array.

                      SELECT HASHBYTES('MD5', 'Test');
                      -- Result (byte array): 0x0CBC6611F5540BD0809A388DC95A615B
                    

INDEXOF(expressionToSearch, expressionToFind [,start_location ])

Returns the starting position of the specified expression in the character string.

• expressionToSearch: The character expression, typically a column, to search.
• expressionToFind: The character expression to find.
• start_location: An optional character position to start searching for expressionToFind in expressionToSearch.

                      SELECT INDEXOF('0123456', '456');
                      -- Result: 4

                      SELECT INDEXOF('0123456', '456', 5);
                      -- Result: -1
                    

ISALPHABETIC(character_expression)

Returns 1 if the character expression consists only of alphabetic characters; otherwise, 0.

• character_expression: The string expression to evaluate.

                      SELECT ISALPHABETIC('Hello');
                      -- Result: 1

                      SELECT ISALPHABETIC('Hello123');
                      -- Result: 0

                      SELECT ISALPHABETIC('Hello!');
                      -- Result: 0
                    

ISALPHANUMERIC(character_expression)

Returns 1 if the character expression consists only of alphabetic and numeric characters; otherwise, 0.

• character_expression: The string expression to evaluate.

                      SELECT ISALPHANUMERIC('Hello123');
                      -- Result: 1

                      SELECT ISALPHANUMERIC('123');
                      -- Result: 1

                      SELECT ISALPHANUMERIC('Hello.123');
                      -- Result: 0
                    

ISNUMERIC(character_expression)

Returns 1 if the character expression consists only of numeric digits and up to one decimal point; otherwise, 0.

• character_expression: The string expression to evaluate.

                      SELECT ISNUMERIC('123');
                      -- Result: 1

                      SELECT ISNUMERIC('123.45');
                      -- Result: 1

                      SELECT ISNUMERIC('123.45.67');
                      -- Result: 0

                      SELECT ISNUMERIC('12a3');
                      -- Result: 0
                    

JSON_EXTRACT(json, jsonpath)

Selects any value in a JSON array or object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to extract.
• jsonpath: The XPath used to select the nodes. The JSONPath must be a string constant. The values of the nodes selected will be returned in a token-separated list.

                      SELECT JSON_EXTRACT('{"test": {"data": 1}}', '$.test');
                      -- Result: '{"data":1}'

                      SELECT JSON_EXTRACT('{"test": {"data": 1}}', '$.test.data');
                      -- Result: 1

                      SELECT JSON_EXTRACT('{"test": {"data": [1, 2, 3]}}', '$.test.data[1]');
                      -- Result: 2
                    

LEFT ( character_expression , integer_expression )

Returns the specified number of characters counting from the left of the specified string.

• character_expression: The character expression.
• integer_expression: The positive integer that specifies how many characters will be returned counting from the left of character_expression.

                      SELECT LEFT('1234567890', 3);
                      -- Result: '123'
                    

LEN(string_expression)

Returns the number of characters of the specified string expression.

• string_expression: The string expression.

                      SELECT LEN('12345');
                      -- Result: 5
                    

LOCATE(substring,string)

Returns an integer representing how many characters into the string the substring appears.

• substring: The substring to find inside larger string.
• string: The larger string that is searched for the substring.
• start locations: An optional integer that sets the character position (offset) from which to start searching.

				SELECT LOCATE('sample','XXXXXsampleXXXXX');
				-- Result: 6

                SELECT LOCATE('sample', 'XXXXXsampleXXXXX', 7)
                -- Result: 0
				

LOWER ( character_expression )

Returns the character expression with the uppercase character data converted to lowercase.

• character_expression: The character expression.

                      SELECT LOWER('MIXED case');
                      -- Result: 'mixed case'
                    

LTRIM(character_expression)

Returns the character expression with leading blanks removed.

• character_expression: The character expression.

                      SELECT LTRIM('     trimmed');
                      -- Result: 'trimmed'
                    

MASK(string_expression, mask_character [, start_index [, end_index ]])

Replaces the characters between start_index and end_index with the mask_character within the string.

• string_expression: The string expression to be searched.
• mask_character: The character to mask with.
• start_index: The optional number of characters to leave unmasked at beginning of string. Defaults to 0.
• end_index: The optional number of characters to leave unmasked at end of string. Defaults to 0.

                        SELECT MASK('1234567890','*',);
                        -- Result: '**********'
                        SELECT MASK('1234567890','*', 4);
                        -- Result: '1234******'
                        SELECT MASK('1234567890','*', 4, 2);
                        -- Result: '1234****90'  
                    

NCHAR(integer_expression)

Returns the Unicode character with the specified integer code as defined by the Unicode standard.

• integer_expression: The integer from 0 through 65535 (0 through xFFFF).

OCTET_LENGTH(character_expression),

Returns the number of bytes present in the expression.

• character_expression: The set of characters to be be evaluated.

				 SELECT OCTET_LENGTH('text') FROM Account LIMIT 1
				 -- Result: 4
				

PATINDEX(pattern, expression)

Returns the starting position of the first occurrence of the pattern in the expression. Returns 0 if the pattern is not found.

• pattern: The character expression that contains the sequence to be found. The wild-card character % can be used only at the start or end of the expression.
• expression: The expression, typically a column, to search for the pattern.

                      SELECT PATINDEX('123%', '1234567890');
                      -- Result: 1

                      SELECT PATINDEX('%890', '1234567890');
                      -- Result: 8

                      SELECT PATINDEX('%456%', '1234567890');
                      -- Result: 4
                    

POSITION(expressionToFind IN expressionToSearch)

Returns the starting position of the specified expression in the character string.

• expressionToFind: The character expression to find.
• expressionToSearch: The character expression, typically a column, to search.

                      SELECT POSITION('456' IN '123456');
                      -- Result: 4

                      SELECT POSITION('x' IN '123456');
                      -- Result: 0
                    

QUOTENAME(character_string [, quote_character])

Returns a valid SQL Server-delimited identifier by adding the necessary delimiters to the specified Unicode string.

• character_string: The string of Unicode character data. The string is limited to 128 characters. Inputs greater than 128 characters return null.
• quote_character: An optional single character to be used as the delimiter. These include:
  • a single quotation mark (')
  • a left or right bracket ([])
  • a double quotation mark (")
  • a left or right parenthesis ( () )
  • a greater or less than sign (><)
  • a left or right brace ({})
  • a backtick (`)

  If quote_character is not specified brackets are used. If an unacceptable character is supplied, it returns NULL.

                      SELECT QUOTENAME('table_name');
                      -- Result: '[table_name]'

                      SELECT QUOTENAME('table_name', '"');
                      -- Result: '"table_name"'

                      SELECT QUOTENAME('table_name', '[');
                      -- Result: '[table_name]'
                    

REGEXP_REPLACE(expr, pattern [, replacement [, position [, occurrence [, match_type]]]])

Replaces occurrences of a regular expression pattern in the input string with a specified value and returns the resulting string.

• expr: The string expression to be searched.
• pattern: The regular expression pattern to match.
• replacement: (optional) The string to replace each matched occurrence of pattern with. Supports backreferences \1 through \9 and escape sequences \n, \r, \t, and \\. By default, this argument is an empty string, meaning matched portions are removed from the output string.
• position: (optional) The 1-based starting position used when searching for regular expression matches in expr. The default is 1. All characters prior to the starting position are included in the output string unaltered. Skipped characters are ignored when calculating regular expression matches, even if they match pattern.
• occurrence: (optional) Specifies whether all occurrences of pattern,, or only a specific occurrence of pattern are replaced. The default is 0, which means all occurrences of pattern are replaced with replacement. Set to 1 to only replace the first instance of the pattern; 2 to replace the second; etc.
• match_type: (optional) Modifiers used to customize matching behavior. Supported values are: 'c' (case-sensitive, default), 'i' (case-insensitive), 'm' (multiline), 'n' (dot matches newline), 'x' (extended mode). These can be freely combined by including the letters back to back. For example, 'im' applies the functionality of both 'i' and 'm'. The regular expression syntax used is that of the Extended mode ('x') ignores whitespace and allows inline comments. If the pattern needs to match a literal space, it must be explicitly escaped.

                      SELECT REGEXP_REPLACE('abc123def456', '\d+', 'NUM');
                      -- Result: 'abcNUMdefNUM'

                      SELECT REGEXP_REPLACE('Hello\nHELLO\nhello', '^hello', 'X', 1, 0, 'im');
                      -- Result: 'X\nX\nX'
                    

REPLACE(string_expression, string_pattern, string_replacement)

Replaces all occurrences of a string with another string.

• string_expression: The string expression to be searched. This can be a character or binary data type.
• string_pattern: The substring to be found. Cannot be an empty string.
• string_replacement: The replacement string.

                      SELECT REPLACE('1234567890', '456', '|');
                      -- Result: '123|7890'

                      SELECT REPLACE('123123123', '123', '.');
                      -- Result: '...'

                      SELECT REPLACE('1234567890', 'a', 'b');
                      -- Result: '1234567890'
                    

REPLICATE ( string_expression ,integer_expression )

Repeats the string value the specified number of times.

• string_expression: The string to replicate.
• integer_expression: The repeat count.

                      SELECT REPLACE('x', 5);
                      -- Result: 'xxxxx'
                    

REVERSE ( string_expression )

Returns the reverse order of the string expression.

• string_expression: The string.

                      SELECT REVERSE('1234567890');
                      -- Result: '0987654321'
                    

RIGHT ( character_expression , integer_expression )

Returns the right part of the string with the specified number of characters.

• character_expression: The character expression.
• integer_expression: The positive integer that specifies how many characters of the character expression will be returned.

                      SELECT RIGHT('1234567890', 3);
                      -- Result: '890'
                    

RTRIM(character_expression)

Returns the character expression after it removes trailing blanks.

• character_expression: The character expression.

                      SELECT RTRIM('trimmed     ');
                      -- Result: 'trimmed'
                    

SOUNDEX(character_expression)

Returns the four-character Soundex code, based on how the string sounds when spoken.

• character_expression: The alphanumeric expression of character data.

                      SELECT SOUNDEX('smith');
                      -- Result: 'S530'
                    

SPACE(repeatcount)

Returns the string that consists of repeated spaces.

• repeatcount: The number of spaces.

                      SELECT SPACE(5);
                      -- Result: '     '
                    

SPLIT(string, delimiter, offset)

Returns a section of the string between to delimiters.

• string: The string to split.
• delimiter: The character to split the string with.
• offset: The number of the split to return. Positive numbers are treated as offsets from the left, and negative numbers are treated as offsets from the right.

                      SELECT SPLIT('a/b/c/d', '/', 1);
                      -- Result: 'a'
                      SELECT SPLIT('a/b/c/d', '/', -2);
                      -- Result: 'c'
                    

STARTSWITH(character_expression, character_prefix)

Returns 1 if character_expression starts with character_prefix; otherwise, 0.

• character_expression: The character expression.
• character_prefix: The character prefix to search for.

                      SELECT STARTSWITH('0123456', '012');
                      -- Result: 1

                      SELECT STARTSWITH('0123456', '456');
                      -- Result: 0
                    

STR ( float_expression [ , integer_length [ , integer_decimal ] ] )

Returns the character data converted from the numeric data. For example, STR(123.45, 6, 1) returns 123.5.

• float_expression: The float expression.
• length: The optional total length to return. This includes decimal point, sign, digits, and spaces. The default is 10.
• decimal: The optional number of places to the right of the decimal point. The decimal must be less than or equal to 16.

                      SELECT STR('123.456');
                      -- Result: '123'

                      SELECT STR('123.456', 2);
                      -- Result: '**'

                      SELECT STR('123.456', 10, 2);
                      -- Result: '123.46'
                    

STUFF(character_expression , integer_start , integer_length , replaceWith_expression)

Inserts a string into another string. It deletes the specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

• character_expression: The string expression.
• start: The integer value that specifies the location to start deletion and insertion. If start or length is negative, null is returned. If start is longer than the string to be modified, character_expression, null is returned.
• length: The integer that specifies the number of characters to delete. If length is longer than character_expression, deletion occurs up to the last character in replaceWith_expression.
• replaceWith_expression: The expression of character data that will replace length characters of character_expression beginning at the start value.

                      SELECT STUFF('1234567890', 3, 2, 'xx');
                      -- Result: '12xx567890'
                    

SUBSTRING(string_value FROM start FOR length)

Returns the part of the string with the specified length; starts at the specified index.

• string_value: The character string.
• start: The positive integer that specifies the start index of characters to return.
• length: Optional. The positive integer that specifies how many characters will be returned.

                      SELECT SUBSTRING('1234567890' FROM 3 FOR 2);
                      -- Result: '34'

                      SELECT SUBSTRING('1234567890' FROM 3);
                      -- Result: '34567890'
                    

You can also drop the FROM and FOR clauses:

                    SELECT SUBSTRING('1234567890', 3, 2)
                    --Result: '34'
                    SELECT SUBSTRING('1234567890', 3)
                    --Result: '34567890'
                    

TEXT_ENCODE(input_string, charset)

Returns binary output by encoding a string using the specified character set.

• input_string: The plain text string.
• charset: The character set to use, such as 'UTF-8', 'ISO-8859-1'.

                    SELECT TEXT_ENCODE('Café', 'UTF-8');
                    -- Result: (binary output)
                    

TEXT_DECODE(input_binary, charset)

Returns a string decoded from binary data using the specified character set.

• input_binary: The binary value to decode.
• charset: The character set used for decoding.

                    SELECT TEXT_DECODE(BinaryData, 'UTF-8');
                    -- Result: 'Café'
                    

TOSTRING(string_value1)

Converts the value of this instance to its equivalent string representation.

• string_value1: The string to be converted.

                      SELECT TOSTRING(123);
                      -- Result: '123'

                      SELECT TOSTRING(123.456);
                      -- Result: '123.456'

                      SELECT TOSTRING(null);
                      -- Result: ''
                    

TRIM(trimspec trimchar FROM string_value)

Returns the character expression with leading and/or trailing blanks removed.

• trimspec: Optional. If included must be one of the keywords BOTH, LEADING or TRAILING.
• trimchar: Optional. If included should be a one-character string value.
• string_value: The string value to trim.

                      SELECT TRIM('     trimmed     ');
                      -- Result: 'trimmed'

                      SELECT TRIM(LEADING FROM '     trimmed     ');
                      -- Result: 'trimmed     '

                      SELECT TRIM('-' FROM '-----trimmed-----');
                      -- Result: 'trimmed'

                      SELECT TRIM(BOTH '-' FROM '-----trimmed-----');
                      -- Result: 'trimmed'

                      SELECT TRIM(TRAILING '-' FROM '-----trimmed-----');
                      -- Result: '-----trimmed'
                    

UNICODE(ncharacter_expression)

Returns the integer value defined by the Unicode standard of the first character of the input expression.

• ncharacter_expression: The Unicode character expression.

UPPER ( character_expression )

Returns the character expression with lowercase character data converted to uppercase.

• character_expression: The character expression.

                      SELECT UPPER('MIXED case');
                      -- Result: 'MIXED CASE'
                    

XML_EXTRACT(xml, xpath [, separator])

Extracts an XML document using the specified XPath to flatten the XML. A comma is used to separate the outputs by default, but this can be changed by specifying the third parameter.

• xml: The XML document to extract.
• xpath: The XPath used to select the nodes. The nodes selected will be returned in a token-separated list.
• separator: The optional token used to separate the items in the flattened response. If this is not specified, the separator will be a comma.

                      SELECT XML_EXTRACT('<vowels><ch>a</ch><ch>e</ch><ch>i</ch><ch>o</ch><ch>u</ch></vowels>', '/vowels/ch');
                      -- Result: 'a,e,i,o,u'

                      SELECT XML_EXTRACT('<vowels><ch>a</ch><ch>e</ch><ch>i</ch><ch>o</ch><ch>u</ch></vowels>', '/vowels/ch', ';');
                      -- Result: 'a;e;i;o;u'
                    

ABS ( numeric_expression )

Returns the absolute (positive) value of the specified numeric expression.

• numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

                      SELECT ABS(15);
                      -- Result: 15

                      SELECT ABS(-15);
                      -- Result: 15
                    

ACOS ( float_expression )

Returns the arc cosine, the angle in radians whose cosine is the specified float expression.

• float_expression: The float expression that specifies the cosine of the angle to be returned. Values outside the range from -1 to 1 return null.

                      SELECT ACOS(0.5);
                      -- Result: 1.0471975511966
                    

ASIN ( float_expression )

Returns the arc sine, the angle in radians whose sine is the specified float expression.

• float_expression: The float expression that specifies the sine of the angle to be returned. Values outside the range from -1 to 1 return null.

                      SELECT ASIN(0.5);
                      -- Result: 0.523598775598299
                    

ATAN ( float_expression )

Returns the arc tangent, the angle in radians whose tangent is the specified float expression.

• float_expression: The float expression that specifies the tangent of the angle to be returned.

                      SELECT ATAN(10);
                      -- Result: 1.47112767430373
                    

ATN2 ( float_expression1 , float_expression2 )

Returns the angle in radians between the positive x-axis and the ray from the origin to the point (y, x) where x and y are the values of the two specified float expressions.

• float_expression1: The float expression that is the y-coordinate.
• float_expression2: The float expression that is the x-coordinate.

                      SELECT ATN2(1, 1);
                      -- Result: 0.785398163397448
                    

CEILING ( numeric_expression ) or CEIL( numeric_expression )

Returns the smallest integer greater than or equal to the specified numeric expression.

• numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

                      SELECT CEILING(1.3);
                      -- Result: 2

                      SELECT CEILING(1.5);
                      -- Result: 2

                      SELECT CEILING(1.7);
                      -- Result: 2
                    

COS ( float_expression )

Returns the trigonometric cosine of the specified angle in radians in the specified expression.

• float_expression: The float expression of the specified angle in radians.

                      SELECT COS(1);
                      -- Result: 0.54030230586814
                    

COT ( float_expression )

Returns the trigonometric cotangent of the angle in radians specified by float_expression.

• float_expression: The float expression of the angle in radians.

                      SELECT COT(1);
                      -- Result: 0.642092615934331
                    

DEGREES ( numeric_expression )

Returns the angle in degrees for the angle specified in radians.

• numeric_expression: The angle in radians, an expression of an indeterminate numeric data type except for the bit data type.

                      SELECT DEGREES(3.1415926);
                      -- Result: 179.999996929531
                    

EXP ( float_expression )

Returns the exponential value of the specified float expression. For example, EXP(LOG(20)) is 20.

• float_expression: The float expression.

                      SELECT EXP(2);
                      -- Result: 7.38905609893065
                    

EXPR ( expression )

Evaluates the expression.

• expression: The expression. Operators allowed are +, -, *, /, ==, !=, >, <, >=, and <=.

                      SELECT EXPR('1 + 2 * 3');
                      -- Result: 7

                      SELECT EXPR('1 + 2 * 3 == 7');
                      -- Result: true
                    

FLOOR ( numeric_expression )

Returns the largest integer less than or equal to the numeric expression.

• numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

                      SELECT FLOOR(1.3);
                      -- Result: 1

                      SELECT FLOOR(1.5);
                      -- Result: 1

                      SELECT FLOOR(1.7);
                      -- Result: 1
                    

GREATEST(int1,int2,....)

Returns the greatest of the supplied integers.

				SELECT GREATEST(3,5,8,10,1)
				-- Result: 10			
				

HEX(value)

Returns a the equivalent hex for the input value.

• value: A string or numerical value to be converted into hex.

				SELECT HEX(866849198);
				-- Result: 33AB11AE
				
				SELECT HEX('Sample Text');
				-- Result: 53616D706C652054657874
				

JSON_AVG(json, jsonpath)

Computes the average value of a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

                      SELECT JSON_AVG('[1,2,3,4,5]', '$[x]');
                      -- Result: 3

                      SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 3

                      SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
                      -- Result: 4.5
                    

JSON_COUNT(json, jsonpath)

Returns the number of elements in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

                      SELECT JSON_COUNT('[1,2,3,4,5]', '$[x]');
                      -- Result: 5

                      SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 5

                      SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
                      -- Result: 2
                    

JSON_MAX(json, jsonpath)

Gets the maximum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

                      SELECT JSON_MAX('[1,2,3,4,5]', '$[x]');
                      -- Result: 5

                      SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 5

                      SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[..3]');
                      -- Result: 4
                    

JSON_MIN(json, jsonpath)

Gets the minimum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

                      SELECT JSON_MIN('[1,2,3,4,5]', '$[x]');
                      -- Result: 1

                      SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 1

                      SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
                      -- Result: 4
                    

JSON_SUM(json, jsonpath)

Computes the summary value in JSON according to the JSONPath expression. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

                      SELECT JSON_SUM('[1,2,3,4,5]', '$[x]');
                      -- Result: 15

                      SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 15

                      SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
                      -- Result: 9
                    

LEAST(int1,int2,....)

Returns the least of the supplied integers.

				SELECT LEAST(3,5,8,10,1)
				-- Result: 1			
				

LOG ( float_expression [, base ] )

Returns the natural logarithm of the specified float expression.

• float_expression: The float expression.
• base: The optional integer argument that sets the base for the logarithm.

                      SELECT LOG(7.3890560);
                      -- Result: 1.99999998661119
                    

LOG10 ( float_expression )

Returns the base-10 logarithm of the specified float expression.

• float_expression: The expression of type float.

                      SELECT LOG10(10000);
                      -- Result: 4
                    

MOD(dividend,divisor)

Returns the integer value associated with the remainder when dividing the dividend by the divisor.

• dividend: The number to take the modulus of.
• divisor: The number to divide the dividend by when determining the modulus.

				SELECT MOD(10,3);
				-- Result: 1
				

NEGATE(real_number)

Returns the opposite to the real number input.

• real_number: The real number to find the opposite of.

				SELECT NEGATE(10);
				-- Result: -10
				
				SELECT NEGATE(-12.4)
				--Result: 12.4
				

PI ( )

Returns the constant value of pi.

                  SELECT PI()
                  -- Result: 3.14159265358979 
                

POWER ( float_expression , y )

Returns the value of the specified expression raised to the specified power.

• float_expression: The float expression.
• y: The power to raise float_expression to.

                      SELECT POWER(2, 10);
                      -- Result: 1024

                      SELECT POWER(2, -2);
                      -- Result: 0.25
                    

RADIANS ( float_expression )

Returns the angle in radians of the angle in degrees.

• float_expression: The degrees of the angle as a float expression.

                      SELECT RADIANS(180);
                      -- Result: 3.14159265358979
                    

RAND ( [ integer_seed ] )

Returns a pseudorandom float value from 0 through 1, exclusive.

• seed: The optional integer expression that specifies the seed value. If seed is not specified, a seed value at random will be assigned.

                      SELECT RAND();
                      -- This result may be different, since the seed is randomized
                      -- Result: 0.873159630165044

                      SELECT RAND(1);
                      -- This result will always be the same, since the seed is constant
                      -- Result: 0.248668584157093
                    

ROUND ( numeric_expression [ ,integer_length] [ ,function ] )

Returns the numeric value rounded to the specified length or precision.

• numeric_expression: The expression of a numeric data type.
• length: The optional precision to round the numeric expression to. When this is omitted, the default behavior will be to round to the nearest whole number.
• function: The optional type of operation to perform. When the function parameter is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

                      SELECT ROUND(1.3, 0);
                      -- Result: 1

                      SELECT ROUND(1.55, 1);
                      -- Result: 1.6

                      SELECT ROUND(1.7, 0, 0);
                      -- Result: 2

                      SELECT ROUND(1.7, 0, 1);
                      -- Result: 1
                      
                      SELECT ROUND (1.24);
                      -- Result: 1.0
                    

SIGN ( numeric_expression )

Returns the positive sign (1), 0, or negative sign (-1) of the specified expression.

• numeric_expression: The expression of an indeterminate data type except for the bit data type.

                      SELECT SIGN(0);
                      -- Result: 0

                      SELECT SIGN(10);
                      -- Result: 1

                      SELECT SIGN(-10);
                      -- Result: -1
                    

SIN ( float_expression )

Returns the trigonometric sine of the angle in radians.

• float_expression: The float expression specifying the angle in radians.

                     SELECT SIN(1);
                     -- Result: 0.841470984807897
                    

SQRT ( float_expression )

Returns the square root of the specified float value.

• float_expression: The expression of type float.

                      SELECT SQRT(100);
                      -- Result: 10
                    

SQUARE ( float_expression )

Returns the square of the specified float value.

• float_expression: The expression of type float.

                      SELECT SQUARE(10);
                      -- Result: 100

                      SELECT SQUARE(-10);
                      -- Result: 100
                    

TAN ( float_expression )

Returns the tangent of the input expression.

• float_expression: The expression of type float.

                      SELECT TAN(1);
                      -- Result: 1.5574077246549
                    

TRUNC(decimal_number,precision)

Returns the supplied decimal number truncated to have the supplied decimal precision.

• decimal_number: The decimal value to truncate.
• precision: The number of decimal places to truncate the decimal number to.

				SELECT TRUNC(10.3423,2);
				-- Result: 10.34
				

_ROW_NUMBER_()

Returns a row index as an additional column.

				SELECT ColumnName, _ROW_NUMBER_() FROM TableName
				-- Result: ColumnData, 0
				ColumnData2, 1
				ColumnData3, 2
				

CURRENT_DATE()

Returns the current date value.

                  SELECT CURRENT_DATE();
                  -- Result: 2018-02-01
                

CURRENT_TIMESTAMP()

Returns the current time stamp of the database system as a datetime value. This value is equal to GETDATE and SYSDATETIME, and is always in the local timezone.

                  SELECT CURRENT_TIMESTAMP();
                  -- Result: 2018-02-01 03:04:05
                

DATEADD (datepart , integer_number , date [, dateformat])

Returns the datetime value that results from adding the specified number (a signed integer) to the specified date part of the date.

• datepart: The part of the date to add the specified number to. The valid values and abbreviations are
  • year (yy, yyyy)
  • quarter (qq, q)
  • month (mm, m)
  • week (wk, ww)
  • weekday (dw)
  • dayofyear (dy, y)
  • day (dd, d)
  • hour (hh)
  • minute (mi, n)
  • second (ss, s)
  • millisecond (ms)
• number: The number to be added.
• date: The expression of the datetime data type.
• dateformat: The optional output date format.

                  SELECT DATEADD('d', 5, '2018-02-01');
                  -- Result: 2018-02-06

                  SELECT DATEADD('hh', 5, '2018-02-01 00:00:00');
                  -- Result: 2018-02-01 05:00:00
                

DATEDIFF ( datepart , startdate , enddate )

Returns the difference (a signed integer) of the specified time interval between the specified start date and end date.

• datepart: The part of the date that is the time interval of the difference between the start date and end date. The valid values and abbreviations are:
  • Year (year, yyyy, yy)
  • Quarter (quarter, qq, q)
  • Month (month, mm, m)
  • Week (week, wk, ww)
  • Weekday (weekday, dw)
  • Dayofyear (dayofyear, dy, y)
  • Day (day, dd, d)
  • Hour (hour, hh)
  • Minute (minute, mi, n)
  • Second (second, ss, s)
  • Millisecond (millisecond, ms)
• startdate: The datetime expression of the start date.
• enddate: The datetime expression of the end date.

                  SELECT DATEDIFF('d', '2018-02-01', '2018-02-10');
                  -- Result: 9

                  SELECT DATEDIFF('hh', '2018-02-01 00:00:00', '2018-02-01 12:00:00');
                  -- Result: 12
                

DATE_FORMAT(date,format)

Returns the date or timestamp in the format specified. This function mirrors the MySQL DATE_FORMAT function.

• date: A date or timestamp string.
• format: The specifier string of the desired output format. The list of supported format specifiers comes from the MySQL DATE_FORMAT function (see link to MySQL documentation above).

					SELECT DATE_FORMAT('9/4/2021 3:11:53 AM','%h')
					-- Result: 03
				  

DATEFROMPARTS(integer_year, integer_month, integer_day)

Returns the datetime value for the specified year, month, and day.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.

                    SELECT DATEFROMPARTS(2018, 2, 1);
                    -- Result: 2018-02-01
                  

DATENAME(datepart , date)

Returns the character string that represents the specified date part of the specified date.

• datepart: The part of the date to return. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), millisecond (ms), microsecond (mcs), and nanosecond (ns).
• date: The datetime expression.

                     SELECT DATENAME('yy', '2018-02-01');
                     -- Result: '2018'

                     SELECT DATENAME('dw', '2018-02-01');
                     -- Result: 'Thursday'
                   

DATEPART(datepart, date [,integer_datefirst])

Returns a character string that represents the specified date part of the specified date.

• datepart: The part of the date to return. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), millisecond (ms), microsecond (mcs), nanosecond (ns), ISODOW, ISO_WEEK (isoweek, isowk,isoww), and ISOYEAR.
• date: The datetime string.
• datefirst: The optional integer representing the first day of the week. The default is 7, Sunday.

                    SELECT DATEPART('yy', '2018-02-01');
                    -- Result: 2018

                    SELECT DATEPART('dw', '2018-02-01');
                    -- Result: 5
                  

DATETIMEFROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute, integer_seconds, integer_milliseconds)

Returns the datetime value for the specified date parts.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.
• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.
• seconds: The integer expression specifying the seconds.
• milliseconds: The integer expression specifying the milliseconds.

                    SELECT DATETIMEFROMPARTS(2018, 2, 1, 1, 2, 3, 456);
                    -- Result: 2018-02-01 01:02:03.456
                  

DATETIME2FROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute, integer_seconds, integer_fractions, integer_precision)

Returns the datetime value for the specified date parts.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.
• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.
• seconds: The integer expression specifying the seconds.
• fractions: The integer expression specifying the fractions of the second.
• precision: The integer expression specifying the precision of the fraction.

				    SELECT DATETIME2FROMPARTS(2018, 2, 1, 1, 2, 3, 456, 3);
                    -- Result: 2018-02-01 01:02:03.456
                  

DATE_TRUNC(date, datepart)

Truncates the date to the precision of the given date part. Modeled after the Oracle TRUNC function.

• date: The datetime string that specifies the date.
• datepart: Refer to the Oracle documentation for valid datepart syntax.

				    SELECT DATE_TRUNC('05-04-2005', 'YY');
                    -- Result: '1/1/2005'
					
                    SELECT DATE_TRUNC('05-04-2005', 'MM');
                    -- Result: '5/1/2005'                    
                  

DATE_TRUNC2(datepart, date, [weekday])

Truncates the date to the precision of the given date part. Modeled after the PostgreSQL date_trunc function.

• datepart: One of 'millennium', 'century', 'decade', 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute' or 'second'.
• date: The datetime string that specifies the date.
• weekday: The optional day of the week to use as the first day for 'week'. One of 'sunday', 'monday', etc.

                    SELECT DATE_TRUNC2('year', '2020-02-04');
                    -- Result: '2020-01-01'

                    SELECT DATE_TRUNC2('week', '2020-02-04', 'monday');
                    -- Result: '2020-02-02', which is the previous Monday
                  

DAY(date)

Returns the integer that specifies the day component of the specified date.

• date: The datetime string that specifies the date.

                    SELECT DAY('2018-02-01');
                    -- Result: 1
                  

DAYNAME(date)

Returns the name of the day of the week of the specified date.

• date: The datetime string that specifies the date.

                    SELECT DAYNAME('8/18/2021');
                    -- Result: Wednesday
                  

DAYOFMONTH(date)

Returns the day of the month of the given date part.

• date: The datetime string that specifies the date.

				  SELECT DAYOFMONTH('04/15/2000');
				  -- Result: 15
				  

DAYOFWEEK(date)

Returns the day of the week of the given date part.

• date: The datetime string that specifies the date.

				  SELECT DAYOFWEEK('04/15/2000');
				  -- Result: 7
				  

DAYOFYEAR(date)

Returns the day of the year of the given date part.

• date: The datetime string that specifies the date.

				  SELECT DAYOFYEAR('04/15/2000');
				  -- Result: 106
				  

EOMONTH(date [, integer_month_to_add ]) or LAST_DAY(date)

Returns the last day of the month that contains the specified date with an optional offset.

• date: The datetime expression specifying the date for which to return the last day of the month.
• integer_month_to_add: The optional integer expression specifying the number of months to add to the date before calculating the end of the month.

                  SELECT EOMONTH('2018-02-01');
                  -- Result: 2018-02-28
                  
                  SELECT LAST_DAY('2018-02-01');
                  -- Result: 2018-02-28

                  SELECT EOMONTH('2018-02-01', 2);
                  -- Result: 2018-04-30
                

EXTRACT(date_part FROM date_column_name)

Returns the last day of the month that contains the specified date with an optional offset.

• date_part: One of the following date components: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
• date_column_name: The name of a date column in a table.

                  SELECT EXTRACT(YEAR FROM DateColumn)
                  -- Result: 2021
                

FDWEEK(date)

Returns the first day of the week of the given date part.

• date: The datetime string that specifies the date.
• weeks to add: An optional integer expression specifying the number of months to add to the date before calculating the first day of the week.

				  SELECT FDWEEK('02-08-2018');
				  -- Result: 2/4/2018

          SELECT FDWEEK('02-08-2018', 1)
          --Result: 02/11/2018
				  

FDMONTH(date)

Returns the first day of the month of the given date part.

• date: The datetime string that specifies the date.
• month to add: An optional integer expression specifying the number of months to add to the date before calculating the first day of the month.

				  SELECT FDMONTH('02-08-2018');
				  -- Result: 2/1/2018

          SELECT FDMONTH('02-08-2018', 1) 
          --Result: 03/01/2018
				  

FDQUARTER(date)

Returns the first day of the quarter of the given date part.

• date: The datetime string that specifies the date.
• quarters to add: An optional integer expression specifying the number of months to add to the date before calculating the first day of the quarter.

				  SELECT FDQUARTER('05-08-2018');
				  -- Result: 4/1/2018

          SELECT FDQUARTER('05-08-2018',1)
          --Result: 07/01/2018
				  

FILEMODIFIEDTIME(uri)

Returns the time stamp associated with the Date Modified of the relevant file.

• uri: An absolute path pointing to a file on the local file system.

				 SELECT FILEMODIFIEDTIME('C:/Documents/myfile.txt');
				 -- Result: 6/25/2019 10:06:58 AM
				 

FROM_DAYS(datevalue)

Returns a date derived from the number of days after 1582-10-15 (based upon the Gregorian calendar). This will be equivalent to the MYSQL FROM_DAYS function.

• datevalue: A integer value representing the number of days since 1582-10-15.

				SELECT FROM_DAYS(736000);
				-- Result: 2/6/2015
				

FROM_UNIXTIME(time, issecond)

Returns a representation of the unix_timestamp argument as a value in YYYY-MM-DD HH:MM:SS expressed in the current time zone.

• time: The time stamp value from epoch time. Milliseconds are accepted.
• issecond: Indicates the time stamp value is milliseconds to epoch time.

                      SELECT FROM_UNIXTIME(1540495231, 1);
                      -- Result: 2018-10-25 19:20:31

                      SELECT FROM_UNIXTIME(1540495357385, 0);
                      -- Result: 2018-10-25 19:22:37
                    

GETDATE()

Returns the current time stamp of the database system as a datetime value. This value is equal to CURRENT_TIMESTAMP and SYSDATETIME, and is always in the local timezone.

                  SELECT GETDATE();
                  -- Result: 2018-02-01 03:04:05
                

GETUTCDATE()

Returns the current time stamp of the database system formatted as a UTC datetime value. This value is equal to SYSUTCDATETIME.

In addition, GETUTCDATE can take an optional second parameter, a date and time that are converted to UTC.

                  SELECT GETUTCDATE();
                  -- For example, if the local timezone is Eastern European Time (GMT+2)
                  -- Result: 2018-02-01 05:04:05

                  SELECT GETUTCDATE('2020/08/31 13:56:00')
                  --Result: '2020-08-31 17:56:00'
                

HOUR(date)

Returns the hour component from the provided datetime.

• date: The datetime string that specifies the date.

				SELECT HOUR('02-02-2020 11:30:00');
				-- Result: 11
				

ISDATE(date, [date_format])

Returns 1 if the value is a valid date, time, or datetime value; otherwise, 0.

• date: The datetime string.
• date_format: The optional datetime format.

                      SELECT ISDATE('2018-02-01', 'yyyy-MM-dd');
                      -- Result: 1

                      SELECT ISDATE('Not a date');
                      -- Result: 0
                    

LAST_WEEK()

Returns a time stamp equivalent to exactly one week before the current date.

				SELECT LAST_WEEK();	//Assume the date is 3/17/2020	
			 -- Result: 3/10/2020 00:00:00
				

LAST_MONTH()

Returns a time stamp equivalent to exactly one month before the current date.

	
				SELECT LAST_MONTH(); //Assume the date is 3/17/2020
				-- Result: 2/17/2020 00:00:00
				

LAST_YEAR()

Returns a time stamp equivalent to exactly one year before the current date.

				SELECT LAST_YEAR();	//Assume the date is 3/17/2020	
				-- Result: 3/10/2019 00:00:00
				

LDWEEK(date)

Returns the last day of the provided week.

• date: The datetime string.
• weeks to add: An optional integer expression specifying the number of months to add to the date before calculating the last day of the week.

				SELECT LDWEEK('02-02-2020');
				-- Result: 2/8/2020
				

LDMONTH(date)

Returns the last day of the provided month.

• date: The datetime string.
• months to add: An optional integer expression specifying the number of months to add to the date before calculating the last day of the month.

				SELECT LDMONTH('02-02-2020');
				-- Result: 2/29/2020

        SELECT LDMONTH('02-08-2020', 1)
        --Result: 03/31/2020
				

LDQUARTER(date)

Returns the last day of the provided quarter.

• date: The datetime string.
• quarters to add: An optional integer expression specifying the number of months to add to the date before calculating the last day of the quarter.

				SELECT LDQUARTER('02-02-2020');
				-- Result: 3/31/2020

        SELECT LDQUARTER('02-02-2020',1)
        --Result: 06/30/2020
				

MAKEDATE(year, days)

Returns a date value from a year and a number of days.

• year: The year
• days: The number of days into the year. Value must be greater than 0.

          SELECT MAKEDATE(2020, 1);
          -- Result: 2020-01-01
        

MINUTE(date)

Returns the minute component from the provided datetime.

• date: The datetime string that specifies the date.

				SELECT MINUTE('02-02-2020 11:15:00');
				-- Result: 15
				

MONTH(date)

Returns the month component from the provided datetime.

• date: The datetime string that specifies the date.

				SELECT MONTH('02-02-2020');
				-- Result: 2
				

QUARTER(date)

Returns the quarter associated with the provided datetime.

• date: The datetime string that specifies the date.

				SELECT QUARTER('02-02-2020');
				-- Result: 1
				

SECOND(date)

Returns the second component from the provided datetime.

• date: The datetime string that specifies the date.

				SELECT SECOND('02-02-2020 11:15:23');
				-- Result: 23
				

SMALLDATETIMEFROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute)

Returns the datetime value for the specified date and time.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.
• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.

                      SELECT SMALLDATETIMEFROMPARTS(2018, 2, 1, 1, 2);
                      -- Result: 2018-02-01 01:02:00
                    

STRTODATE(string,format)

Parses the provided string value and returns the corresponding datetime.

• string: The string value to be converted to datetime format.
• format: A format string which describes how to interpret the first string input. A few special formats are available as well, including UNIX, UNIXMILIS, TICKS, and FILETICKS.

				SELECT STRTODATE('03*04*2020','dd*MM*yyyy');
				-- Result: 4/3/2020
				

SYSDATETIME()

Returns the current time stamp as a datetime value of the database system. It is equal to GETDATE and CURRENT_TIMESTAMP, and is always in the local timezone.

                  SELECT SYSDATETIME();
                  -- Result: 2018-02-01 03:04:05
                

SYSUTCDATETIME()

Returns the current system date and time as a UTC datetime value. It is equal to GETUTCDATE.

                  SELECT SYSUTCDATETIME();
                  -- For example, if the local timezone is Eastern European Time (GMT+2)
                  -- Result: 2018-02-01 05:04:05
                

TIMEFROMPARTS(integer_hour, integer_minute, integer_seconds, integer_fractions, integer_precision)

Returns the time value for the specified time and with the specified precision.

• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.
• seconds: The integer expression specifying the seconds.
• fractions: The integer expression specifying the fractions of the second.
• precision : The integer expression specifying the precision of the fraction.

                      SELECT TIMEFROMPARTS(1, 2, 3, 456, 3);
                      -- Result: 01:02:03.456
                    

TO_DAYS(date)

Returns the number of days since 0000-00-01. This will only return a value for dates on or after 1582-10-15 (based upon the Gregorian calendar). This will be equivalent to the MYSQL TO_DAYS function.

• date: The datetime string that specifies the date.

				SELECT TO_DAYS('02-06-2015');
				-- Result: 736000
				

WEEK(date)

Returns the week (of the year) associated with the provided datetime.

• date: The datetime string that specifies the date.

				SELECT WEEK('02-17-2020 11:15:23');
				-- Result: 8
				

YEAR(date)

Returns the integer that specifies the year of the specified date.

• date: The datetime string.

                      SELECT YEAR('2018-02-01');
                      -- Result: 2018
                    

A SELECT statement can consist of the following basic clauses.

• SELECT
• INTO
• FROM
• JOIN
• WHERE
• GROUP BY
• HAVING
• UNION
• ORDER BY
• LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the connector:

SELECT {
  [ TOP <numeric_literal> | DISTINCT ]
  { 
    * 
    | { 
        <expression> [ [ AS ] <column_reference> ] 
        | { <table_name> | <correlation_name> } .* 
      } [ , ... ] 
  }
  { 
    FROM <table_reference> [ [ AS ] <identifier> ] 
  } [ , ... ]
  [ [  
      INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } 
    ] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ] 
  ] [ ... ] 
  [ WHERE <search_condition> ]
  [ GROUP BY <column_reference> [ , ... ]
  [ HAVING <search_condition> ]
  [ UNION [ ALL ] <select_statement> ]
  [ 
    ORDER BY 
    <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
  ]
  [ 
    LIMIT <expression>
    [ 
      { OFFSET | , }
      <expression> 
    ]
  ] 
} | SCOPE_IDENTITY() 

<expression> ::=
  | <column_reference>
  | @ <parameter> 
  | ?
  | COUNT( * | { [ DISTINCT ] <expression> } )
  | { AVG | MAX | MIN | SUM | COUNT } ( <expression> ) 
  | NULLIF ( <expression> , <expression> ) 
  | COALESCE ( <expression> , ... ) 
  | CASE <expression>
      WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
    [ ELSE { <expression> | NULL } ]
    END 
  | {RANK() | DENSE_RANK()} OVER ([PARTITION BY <column_reference>] {ORDER BY <column_reference>})
  | <literal>
  | <sql_function> 

<search_condition> ::= 
  {
    <expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN | IS DISTINCT FROM | IS NOT DISTINCT FROM } [ <expression> ]
  } [ { AND | OR } ... ] 

Examples

1. Return all columns:
   

   SELECT * FROM Account

2. Rename a column:
   

   SELECT [Name] AS MY_Name FROM Account

3. Cast a column's data as a different data type:
   

   SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Account

4. Search data:
   

   SELECT * FROM Account WHERE Industry = 'Floppy Disks'

5. Return the number of items matching the query criteria:
   

   SELECT COUNT(*) AS MyCount FROM Account 

6. Return the number of unique items matching the query criteria:
   

   SELECT COUNT(DISTINCT Name) FROM Account 

7. Return the unique items matching the query criteria:
   

   SELECT DISTINCT Name FROM Account 

8. Sort a result set in ascending order:
   

   SELECT Id, Name FROM Account  ORDER BY Name ASC

9. Restrict a result set to the specified number of rows:
   

   SELECT Id, Name FROM Account LIMIT 10 

10. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
    

    SELECT * FROM Account WHERE Industry = @param

See Explicitly Caching Data for information on using the SELECT statement in offline mode.

Pseudo Columns

Some input-only fields are available in SELECT statements. These fields, called pseudo columns, do not appear as regular columns in the results, yet may be specified as part of the WHERE clause. You can use pseudo columns to access additional features from Salesforce Data 360.

    SELECT * FROM Account WHERE Pseudo = '@Pseudo'
    

JOIN Queries

See JOIN Queries for SELECT query examples using JOINs.

Window Functions

See Window Functions for SELECT examples containing window functions.

Table-Valued Functions

See Table-Valued Functions for SELECT examples with table-valued functions.

Window functions allow you to create computed fields from a group of rows (a window) that return a result for each row, as opposed to one computed result for a set of rows, as is the case with aggregate functions. The connector supports the following window function syntax.

Note: Window function support is an experimental feature of the connector. This functionality extends beyond the connector's core scope of being SQL-92 compliant. As such, performance with window functions may not be optimal.

Window Function Clauses

OVER

The OVER clause defines the window over which window functions are performed.

SELECT A, B, <window function> OVER (<window frame>) FROM TableName

The <window function> refers to any supported window function clause, and the <window frame> refers to one or more clauses that specify the logic by which the window is defined.

PARTITION BY

The PARTITION BY clause subdivides a window into sub-windows called partitions. For each unique value in the column specified in the PARTITION BY clause, every record with that value collectively forms an individual partition.

SELECT A, B, <window function> OVER (PARTITION BY A ORDER BY B) From Account

The <window function> refers to any supported window function clause.

Window Functions

The connector supports math, ranking, and analytic window functions.

Math

These window functions perform mathematical operations on the records within the window.

COUNT()

Calculates the number of records in each partition. The calculated column is of the data type "int".

In each partition, every record will display the total number of records in that partition.

SELECT Name, Role, Earnings, COUNT() OVER (PARTITION BY Role) FROM Employees

COUNT_BIG()

Calculates the number of records in each partition. The calculated column is of the data type "bigint".

In each partition, every record will display the total number of records in that partition.

SELECT Name, Role, Earnings, COUNT_BIG() OVER (PARTITION BY Role) FROM Employees

MIN(numeric_column)

Calculates the minimum value of a numerical column per partition.

In each partition, every record will display the minimum value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, MIN(Earnings) OVER (PARTITION BY Role) FROM Employees

MAX(numeric_column)

Calculates the maximum value of a numerical column per partition.

In each partition, every record will display the maximum value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, MAX(Earnings) OVER (PARTITION BY Role) FROM Employees

SUM(numeric_column)

Calculates the sum of a numerical column per partition.

In each partition, every record will display the sum of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, SUM(Earnings) OVER (PARTITION BY Role) FROM Employees

AVG(numeric_column)

Calculates the average value of a numerical column per partition.

In each partition, every record will display the average value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, AVG(Earnings) OVER (PARTITION BY Role) FROM Employees

MEDIAN(numeric_column)

Calculates the median value of a numerical column per partition.

In each partition, every record will display the median value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, MEDIAN(Earnings) OVER (PARTITION BY Role) FROM Employees

STDEV(numeric_column)

Calculates the standard deviation of a numerical column per partition.

In each partition, every record will display the standard deviation of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, STDEV(Earnings) OVER (PARTITION BY Role) FROM Employees

STDEVP(numeric_column)

Calculates the population standard deviation of a numerical column per partition.

In each partition, every record will display the population standard deviation of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, STDEVP(Earnings) OVER (PARTITION BY Role) FROM Employees

VAR(numeric_column)

Calculates the statistical standard variance of a numerical column per partition.

In each partition, every record will display the statistical standard variance of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, VAR(Earnings) OVER (PARTITION BY Role) FROM Employees

VARP(numeric_column)

Calculates the variance population of a numerical column per partition.

In each partition, every record will display the variance population of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, VARP(Earnings) OVER (PARTITION BY Role) FROM Employees

Ranking

These window functions rank records that fall within the window and its partitions.

RANK()

Assigns a rank number to each record in a window based on the value of the column specified in the required ORDER BY clause.

If two or more records have an equal value in the in ranked column, they all receive the same rank number and the rank count increments internally, skipping ahead one rank number for each record with a duplicate value in the ORDER BY column.

SELECT Id, Name, RANK() OVER (ORDER BY Name) AS Rank FROM Account

If you add a PARTITION BY clause, a separate set of ranks is calculated for each partition.

SELECT Id, Name, RANK() OVER (PARTITION BY Id ORDER BY Name) AS Rank FROM Account

DENSE_RANK()

Operates like the RANK() function, but it doesn't increment the internal rank counter for each record with a duplicate value in the ranked column.

This means that, while records with identical values in the ORDER BY column still share a rank number, the function never skips a rank number.

SELECT Id, Name, DENSE_RANK() OVER (PARTITION BY Id ORDER BY Name) AS Rank FROM Account

If you add a PARTITION BY clause, a separate set of ranks is calculated for each partition.

SELECT Id, Name, DENSE_RANK() OVER (PARTITION BY Id ORDER BY Name) AS Rank FROM Account

ROW_NUMBER()

Calculates a row number for each record. An ORDER BY clause in the OVER clause is required.

SELECT Name, Role, Earnings, ROW_NUMBER() OVER (ORDER BY Role) FROM Employees

If you define multiple partitions with PARTITION BY, a new set of row numbers are calculated for each partition.

SELECT Name, Role, Earnings, ROW_NUMBER() OVER (PARTITION BY Role ORDER BY Earnings) FROM Employees

NTILE()

Distributes rows of an ordered partition into a specified number of approximately equal groups, or buckets. It assigns each group a bucket number starting from one. For each row in a group, the NTILE() function assigns a bucket number representing the group to which the row belongs.

The syntax of NTILE() is:

NTILE(buckets) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

The following are paramaters that NTILE() supports:

• buckets: The number of buckets into which the rows are divided. The buckets can be an expression or subquery that evaluates to a positive integer. It cannot be a window function.
• PARTITION BY: distributes rows of a result set into partitions to which the NTILE() function is applied.
• ORDER BY is clause that specifies the logical order of rows in each partition to which the NTILE() is applied.

If the number of rows is not divisible by the buckets, the NTILE() function returns groups of two sizes with the difference by one. The larger groups always precede the smaller group in the order set by ORDER BY in the OVER() clause.

If the total of rows is divisible by the number of buckets, the function divides the rows evenly among buckets. The following statement creates a new table named ntile_demo that stores 10 integers:

CREATE TABLE sales.ntile_demo (
	v INT NOT NULL
);
	
INSERT INTO sales.ntile_demo(v) 
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);	
	
SELECT * FROM sales.ntile_demo;

This statement uses the NTILE() function to divide ten rows into three groups:

SELECT 
	v, 
	NTILE (3) OVER (
		ORDER BY v
	) buckets
FROM 
	sales.ntile_demo;

Analytical

These window functions perform analytical operations on the records within the window.

PERCENT_RANK()

Calculates the relative rank SQL Percentile of each row. It returns values greater than zero, but the maximum value is one. It does not count any NULL values. This function is nondeterministic.

The syntax of PERCENT_RANK() is:

PERCENT_RANK() OVER (
      [PARTITION BY partition_expression, ... ]
      ORDER BY sort_expression [ASC | DESC], ...
  )
  

This syntax uses the following parameters.

• PARTITION BY: By default, SQL Server treats the whole data set as a single set. You can specify the PARTITION BY clause to divide data into multiple sets. The Percent_Rank function performs the analytical calculations on each set. This parameter is optional.
• ORDER BY: Sorts the data in either ascending or descending order. This parameter is required.

Table-valued functions are functions that return a table (rowset).

Note: Table-valued function support is an experimental feature of the connector. This functionality extends beyond the connector's core scope of being SQL-92 compliant. As such, performance with these functions may not be optimal.

Table-Valued Function Clauses

CROSS APPLY

The CROSS APPLY operator is used to perform a subquery on each row of a table or resultset produced by a preceding table expression.

<table_expression_1> CROSS APPLY <table_expression_2>

The second table expression can reference results from the first table expression to create derived columns or an altered recordset via a table-valued function.

Each resulting record is an instance of the record it's splitting, with all the same column values, except for the column(s) containing the value split by the function.

WITH

The WITH clause is used alongside certain table-valued functions to match against constructs within the structure being split (keys, element names, attribute names, etc.) and/or to specify metadata for the columns generated from the function.

SELECT A.ColumnName, X.DerivedColumnName FROM TableName A CROSS APPLY <table-valued function> WITH (DerivedColumnName varchar(255)) AS X

Table-Valued Functions

STRING_SPLIT(input_text,delimiter)

Takes each record in the recordset of the preceding table expression, splits the column containing delimiters (input_text) into substrings separated by the delimiter, and returns one record per substring.

• input_text: A column whose value you want to parse.
• delimiter: The character used to split the value of the column specified in input_text.

Suppose there is a column called "SplitColumn" with the following content:

One-Two-Three

To unpack this value across multiple records:

SELECT A.ID, X.Value FROM [TableWithDelimitedStringField] A CROSS APPLY STRING_SPLIT(A.SplitColumn,'-') WITH (Value VARCHAR(255)) AS X

-- Results:
-----------
|ID|Value|
|1|One|
|1|Two|
|1|Three|

JSONTABLE(json_content,[jsonpath])

For each record in the recordset of the preceding table expression, returns one record for each instance of a key in a JSON array (json_content) that matches the key(s) specified in the WITH clause, at the scope specified by the "jsonpath" input.

• json_content: A JSON "table" (array of objects). The contents can nest, but this must be a single JSON array, not any other JSON structure, at the root level.
  • The values of every instance of the key(s) provided in the WITH clause are retrievable only for substructures which are immediate children of the root-level JSON array.
• jsonpath: An optional JSONPath query defining the scope, within the json_content array, that you want to pull content from. The JSON key(s) identified in the WITH clause must exist at the scope defined in this parameter. This defaults to the JSON root ($).

Consider a sample table with a single record, including an ID column and column with JSON content called "JSONColumn" with the following content:

[
	{
		"name": "Samuel",
		"email": "sam@gmail.com",
		"extrainfo": {
			"city": "Seattle"
		}
	},
	{
		"name": "Katherine",
		"email": "kat@gmail.com",
	},
	{
		"name": "George",
		"email": "george23@gmail.com",
	},
	{
		"name": "Carlos",
		"email": "carlos32@gmail.com",
	}
]

To extract all values for a certain key, specify the scope in the JSONTABLE function and provide the desired key(s) in the WITH clause.

SELECT A.ID, X.name FROM [TableWithJSONField] A CROSS APPLY JSONTABLE(A.JSONColumn) WITH (name VARCHAR(255)) AS X

-- Results: 
|ID|name|
---------
|1 |Samuel|
|1 |Katherine|
|1 |George|
|1 |Carlos|

XMLTABLE(xml_content,[xpath,child_type])

For each record in the resultset of the preceding table expression, returns one record for each of the elements and/or attributes in an XML structure (xml_content) that match the tag name(s) and/or attribute name(s) specified in the WITH clause, at the scope specified in the "xpath" input.

• xml_content: A column containing an XML structure.
• xpath: An optional XPath that specifies the scope within the XML structure at which the connector extracts content matching the tag/attribute name(s) specified in the WITH clause.
  • When extracting the content of sub-elements, the connector can retrieve all content from tags at the root level, (depth 0) immediate children of the root (depth 1), and children of those children (depth 2).
  • When extracting element attribute content, the connector can retrieve all content from tags containing the specified attribute at the root level (depth 0) and from immediate children of root-level elements (depth 1).
• child_type: An optional parameter that specifies the part(s) of the parent element (specified in the xpath input) that the column(s) provided in the WITH clause are checked against to identify content.
  • You can supply the following values:
    • 0: The column(s) in the WITH clause are checked for matches against the parent element's attribute names and sub-element tag names.
    • 1: The column(s) in the WITH clause are checked for matches against the parent element's attribute names.
    • 2: The column(s) in the WITH clause are checked for matches against the parent element's sub-element tag names.
  • When not supplied, this defaults to 0.

Extracting Sub-Element Values

Consider a sample table with a single record, including an ID column and a column with XML content called "XMLContent" with the following content:

<shoppingList>
    <item>
        <name>Apples</name>
        <quantity>3</quantity>
        <unit>Kg</unit>
    </item>
    <item>
        <name>Bread</name>
        <quantity>2</quantity>
        <unit>Loaf</unit>
		<extrainfo>
			<Type>Whole-Grain</Type>
		</extrainfo>
    </item>
    <item>
        <name>Milk</name>
        <quantity>1</quantity>
        <unit>Carton</unit>
    </item>
    <item>
        <name>Eggs</name>
        <quantity>12</quantity>
        <unit></unit>
    </item>
</shoppingList>

To extract sub-element content, specify the scope in the XMLTABLE function and provide the desired element name(s) in the WITH clause. Note that this will not work if the XMLTABLE function's child_type input is set to 1.

SELECT A.ID, X.name FROM [TableWithXMLField] A CROSS APPLY XMLTABLE(A.XMLContent,'//*/item') WITH (name VARCHAR(255)) AS X

-- Results: 
|ID|name|
---------
|1|Apples|
|1|Bread|
|1|Milk|
|1|Eggs|

Extracting Values Using Element Tag Attributes

Suppose you have this sample table with a single record, including an ID column and a column with XML content called "XMLContent" with the following content:

<restaurant>
  <dish type="appetizer">
    <name lang="en">Caprese Salad</name>
    <chef>Chef Giovanni</chef>
    <price currency="USD">9.99</price>
  </dish>
  <dish type="main-course">
    <name lang="fr">Boeuf Bourguignon</name>
    <chef>Chef Marie</chef>
    <price currency="EUR">19.99</price>
  </dish>
  <dish type="dessert">
    <name lang="es">Tres Leches Cake</name>
    <chef>Chef Alejandro</chef>
    <price currency="MXN">89.99</price>
  </dish>
</restaurant>

To extract attribute content, specify the scope in the XMLTABLE function and provide the desired attribute name(s) in the WITH clause. Note that this will not work if the XMLTABLE function's child_type input is set to 2.

SELECT A.ID, X.type FROM [TableWithXMLField] A CROSS APPLY XMLTABLE(A.XMLContent,'//*/dish') WITH (type VARCHAR(255)) AS X

-- Results: 
|ID|type|
---------
|1|appetizer|
|1|main-course|
|1|dessert|

CSVTABLE(csv_content,[delimiter])

For each record in the resultset of the preceding table expression, reads from a column that contains a CSV table (csv_content) and for each record in that CSV table, returns one record containing the value of the CSV column(s) specified in the WITH clause.

• csv_content: A column containing a CSV table.
• delimiter: An optional custom delimiter (instead of a comma) which splits the CSV content contained in the csv_content input.

Consider a sample table with a single record, including an ID column and a column containing CSV table called "CSVContent" with the following content:

Name;Category;Price
Apple;Fruit;0.99
Spaghetti;Pasta;5.49
Chicken Breast;Meat;8.99
Broccoli;Vegetable;2.49

To select every value in the "Name" column and account for the custom delimiter (;):

SELECT A.ID, X.Name FROM [TableWithCSVField] A CROSS APPLY CSVTABLE(A.CSVContent,';') WITH (Name VARCHAR(255)) AS X

-- Results:
|ID|Name|
-----------
|1|Apple|
|1|Spaghetti|
|1|Chicken Breast|
|1|Broccoli|

When caching is enabled, CACHE statements provide complete control over the data that is cached and the table to which it is cached. The CACHE statement executes the SELECT statement specified and caches its results to a table with the same name in the cache database or to table specified in <cached_table_name>. The connector updates or inserts rows to the cache depending on whether or not they already exist in the cache, so the primary key, which is used to identify existing rows, must be included in the selected columns.

See Caching Data for more information on different caching strategies.

CACHE Statement Syntax

The cache statement may include the following options that alter its behavior:

CACHE [ <cached_table_name> ] [ WITH TRUNCATE | AUTOCOMMIT | SCHEMA ONLY | DROP EXISTING | ALTER SCHEMA ] <select_statement> 

WITH TRUNCATE

If this option is set, the connector removes existing rows in the cache table before adding the selected rows. Use this option if you want to refresh the entire cache table but keep its existing schema.

AUTOCOMMIT

If this option is set, the connector commits each row individually. Use this option if you want to ignore the rows that could not be cached due to some reason. By default, the entire result set is cached as a single transaction.

DROP EXISTING

If this option is set, the connector drops the existing cache table before caching the new results. Use this option if you want to refresh the entire cache table, including its schema.

SCHEMA ONLY

If this option is set, the connector creates the cache table based on the SELECT statement without executing the query.

ALTER SCHEMA

If this option is set, the connector alters the schema of the existing table in the cache if it does not match the schema of the SELECT statement. This option results in new columns or dropped columns, if the schema of the SELECT statement does not match the cached table.

Common Queries

Use the following cache statement to cache all rows of a table:

CACHE SELECT * FROM Account

Use the following cache statement to cache all rows of a table into the cache table CachedAccount:

CACHE CachedAccount SELECT * FROM Account

Use the following cache statement for incremental caching. The DateModified column may not exist in all tables. The cache statement shows how incremental caching would work if there were such a column. Also, notice that, in this case, the WITH TRUNCATE and DROP EXISTING options are specifically omitted, which would have deleted all existing rows.

CACHE CachedAccount SELECT * FROM Account WHERE DateModified > '2013-04-04'

Use the following cache statements to create a table with all available columns that will then cache only a few of them. The sequence of statements cache only Id and Name even though the cache table CachedAccount has all the columns in Account.

CACHE CachedAccount SCHEMA ONLY SELECT * FROM Account
CACHE CachedAccount SELECT Id, Name FROM Account

To execute stored procedures, you can use EXECUTE or EXEC statements.

EXEC and EXECUTE assign stored procedure inputs, referenced by name, to values or parameter names.

Stored Procedure Syntax

To execute a stored procedure as an SQL statement, use the following syntax:

 
{ EXECUTE | EXEC } <stored_proc_name> 
{
  [ @ ] <input_name> = <expression>
} [ , ... ]

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

Example Statements

Reference stored procedure inputs by name:

EXECUTE my_proc @second = 2, @first = 1, @third = 3;

Execute a parameterized stored procedure statement:

EXECUTE my_proc second = @p1, first = @p2, third = @p3; 

PIVOT and UNPIVOT can be used to change a table-valued expression into another table.

PIVOT

PIVOT rotates a table-value expression by turning unique values from one column into multiple columns in the output. PIVOT can run aggregations where required on any column value.

PIVOT Synax

 
"SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]
FROM
(
SELECT DaysToManufacture, StandardCost
FROM Production.Product
) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;"

UNPIVOT

UNPIVOT carries out nearly the opposite to PIVOT by rotating columns of a table-valued expressions into column values.

UNPIVOT Sytax

 
"SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;"

For further information on PIVOT and UNPIVOT, see FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)

The CData Python Connector for Salesforce Data 360 models Salesforce Data 360 objects as tables and views. The following sections show the available API objects and provide more information on executing SQL to Salesforce Data 360 APIs.

Schemas for most database objects are defined in simple, text-based configuration files.

Custom Data Source DLOs (Tables)

Salesforce Data 360 allows you to use Ingestion API Connectors to define connections to data sources that are not natively supported by Salesforce Data 360.

After setting up an Ingestion API Connector for your data source and creating a data stream from it, that data stream is made available in the connector as a table. These tables support UPSERT and DELETE operations.

See Custom Data Sources as Tables for more information.

Using CalculatedInsights

See CalculatedInsights Data Model for the available entities in the CalculatedInsights schema.

Using DataGraphs

See DataGraphs Data Model for the available entities in the DataGraphs schema.

Using DataModelObjects

See DataModelObjects Data Model for the available entities in the DataModelObjects schema.

Using DataLakeObjects

See DataLakeObjects Data Model for the available entities in the DataLakeObjects schema.

Salesforce Data 360 allows you to use Ingestion API Connectors to create data streams for data sources that the Salesforce Data 360 platform does not natively support.

Create an Ingestion API Connector and an associated data stream to access your custom data sources from the connector.

Prepare your Custom Data Source Schema

Before you can import data from a custom data source into Salesforce Data 360, you must create a schema file that describes the data model of your custom data source.

Salesforce Data 360 supports custom data sources that are defined in OpenAPI (OAS) formatted schema files (.yaml format).

Creating an Ingestion API Connector

To create an Ingestion API Connector:

1. Click the Gear icon in the top-right corner of the page, then click Data Cloud Setup.
2. In the sidebar on the left side of the page, click EXTERNAL INTEGRATIONS > Ingestion API.
3. Click New. Under Connector Name, set a name for the new connector and click Save. The setup page for your new Ingestion API Connector opens.

4. In the Schema section, click Upload Files.
5. In the upload files prompt, select an OpenAPI (OAS) formatted schema file (.yaml format) and click Open. The Preview Schema window opens.

   
   

6. Click Save.

Creating a Data Stream

Next, set up a data stream for your Ingestion API Connector to create a DLO to your custom data:

1. Return to the Salesforce Data 360 home page.
2. Click the Data Streams tab (click the text "Data Streams", not the dropdown menu attached to it). The Data Streams page opens.
3. Click New. The New Data Stream window opens.

4. Under Connected Sources, click Ingestion API. If it is not visible here, you may need to search for "Ingestion API" in the Other Sources section.
5. Click Next.
6. In the Ingestion API dropdown menu, select the Ingestion API Connector you created earlier.
7. In the Objects section, select each object that you want to include in the data stream, then click Next.
8. For each object, select a Category and Primary Key from their respective dropdown menus, then click Next.
9. In the Data Space dropdown, select the data space you want to deploy your data stream to.
10. To enable standard SQL-compatible behavior, we recommend setting Refresh Mode to Partial. Set Upsert mode only if you want to re-write all missing columns to NULL when updating a row.
11. Click Deploy to create the data stream.

Accessing Custom Data Objects as Tables

Once an Ingestion API Connector and an associated data stream have been created, Salesforce Data 360 automatically populates a Data Lake Objects (DLO) for each custom data object. The connector detects these custom DLOs and makes them available as tables in the DataLakeObjects schema. These tables support UPSERT and DELETE operations.

The CData Python Connector for Salesforce Data 360 models the CalculatedInsights objects as views. Calculated insight define and calculate multidimensional metrics on your entire digital state in Data 360. You can create metrics at the profile, segment, and population levels through the UI.

To use CalculatedInsights schema, simply set Schema to CalculatedInsights.

Views

Views are data that are read-only and cannot be modified.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Python Connector for Salesforce Data 360 Views

Name	Description
CalculatedInsightExample	This is an example of a CalculatedInsight object.

This is an example of a CalculatedInsight object.

Columns

Name	Type	References	Description
accountid_c	String
countaccountnumber_c	Double

The CData Python Connector for Salesforce Data 360 models the DataGraphs objects as views. A data graph combines and transforms normalized table data from data model objects into new, materialized views of your data.

To use DataGraphs schema, simply set Schema to DataGraphs.

Views

Views are data that are read-only and cannot be modified.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Python Connector for Salesforce Data 360 Views

Name	Description
DataGraphExample	This is an example of a DataGraph object.

This is an example of a DataGraph object.

Columns

Name	Type	References	Description
json_blob_c	String
last_engagement_time_c	Datetime
DataSourceObject_c	String
last_refreshed_on_c	Datetime
InternalOrganization_c	String
DataSource_c	String
KQ_Id_c	String
version_c	String
Id_c	String
cdp_sys_SourceVersion_c	String

The CData Python Connector for Salesforce Data 360 models the DataModelObjects objects as views. Data model objects are a harmonized grouping of data created from data streams, insights, and other sources.

To use DataModelObjects schema, simply set Schema to DataModelObjects.

Views

Views are data that are read-only and cannot be modified.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Python Connector for Salesforce Data 360 Views

Name	Description
Account	The Account DMO is a Data 360 data model object for how a party wantsto interact with your company. Provided in both the Sales and Service Data Kits.
AccountContact	The Account Contact DMO is a Data 360 data model object for anindividual who has a role specific to an account. Provided in both the Sales and ServiceData Kits.
Affiliation	The Affiliation DMO is a Data 360 data model object for affiliation orhow to map data Marketing Cloud Engagement business unit use cases.
AgentServicePresence	The Agent Service Presence DMO is a Data 360 data model object (DMO) for a presence user’s real-time presence status.
AgentWork	The Agent Work DMO is a Data 360 data model object (DMO) for a work assignment that has been routed to an agent.
AgentWorkSkill	The Agent Work Skill DMO is a Data 360 data model object (DMO) for a for a skill used to route a work assignment to an agent.
AuthorizationForm	The Authorization Form DMO is a Data 360 data model object (DMO) for the set of terms and conditions, such as privacy policy, contract, or consent forms.
AuthorizationFormConsent	The Authorization Form Consent DMO is a Data 360 data model object (DMO). This DMO captures the where, when, and how a party gives consent for a form, a set of terms and conditions, or a privacy policy.
AuthorizationFormDataUse	The Authorization Form Data Use data model object (DMO) is a Data 360 DMO for the data uses consented to in an authorization form.
AuthorizationFormText	The Authorization Form Text DMO is a Data 360 data model object (DMO) for an authorization form’s text and language settings.
BenefitAction	The Benefit Action DMO is a Data 360 data model object (DMO) for actions triggered when a program benefit is assigned to a loyalty program member.
BenefitType	The Benefit Type DMO is a Data 360 data model object (DMO) for the types of benefits of a loyalty program, such as healthcare, financial, and loyalty.
Brand	The Brand DMO is a Data 360 data model object (DMO) for the product’s brand, for example, Northern Trail Outfitters.
CardAccount	Represents a financial tool offered by a bank as a type of loan, with a line of revolving credit that you can access via your card and your card's account. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
Case	The Case DMO is a Data 360 data model object (DMO) for support cases based on recorded issues, for example, a laptop connectivity problem. Provided in both the Sales and Service Data Kits.
CaseUpdate	The Case Update DMO is a Data 360 data model object (DMO) for a for historical information about changes made to the associated case.
CommunicationSubscription	The Communication Subscription DMO is a Data 360 data model object (DMO) for a customer’s subscription preferences for a specific communication.
CommunicationSubscriptionChannelType	The Communication Subscription Channel Type DMO is a Data 360 data model object (DMO) for the engagement channel through which a customer is reached for a communication subscription.
CommunicationSubscriptionConsent	The Communication Subscription Consent DMO is a Data 360 data model object (DMO) for the engagement or communication channel preferences of a customer.
CommunicationSubscriptionTiming	The Communication Subscription Timing DMO is a Data 360 data model object (DMO) for a customer's timing preferences for receiving a communication subscription.
ConsentAction	The Consent Action DMO is a Data 360 data model object (DMO) for what a user consents to be done with their data, for example, data collection or web activity tracking.
ConsentStatus	The Consent Status DMO is a Data 360 data model object (DMO) for the status of consent, for example opted in or out of data collection.
ContactPointAddress	The Contact Point Address data model object (DMO) is a Data 360DMObased on the mailing address of a party. Provided in both the Sales and Service DataKits.
ContactPointApp	The Contact Point App DMO is a Data 360 data model object for the softwareapplication of a party on a specific device.
ContactPointConsent	The Contact Point Consent DMO is a Data 360 data model object (DMO) for recording information about consent for a specific contact point. This data includes when, how, for how long, and whether the party has double opted-in.
ContactPointEmail	The Contact Point Email DMO is a Data 360 data model object for theemail address of a party. Provided in both the Sales and Service Data Kits.
ContactPointPhone	The Contact Point Phone data model object (DMO) is a Data 360 DMO forthe phone number of a party. Provided in both the Sales and Service Data Kits.
ContactPointSocial	The Contact Point Social DMO is a Data 360 data model object (DMO) for the social media handle for a party, for example @trustednews on Twitter.
ConversationEntryTranscriptExcerpt	The Conversation Entry Transcript Excerpt DMO is a Data 360 data modelobject for a portion of a Conversation Entry that includes a portion of a transcript.
ConversationReason	The Conversation Reason DMO is a Data 360data model object for the reason a conversation started. It contains aggregated metrics for excerpts. Example values include cancel order, update order, and check on order status.
ConversationReasonCategory	The Conversation Reason Category DMO is a Data 360 data model object for a grouping of conversation reasons that have the same overall topic. It contains aggregated metrics for the associated conversation reasons. Example values include Order Management, Payments, and Account Management.
ConversationReasonReportDefinition	The Conversation Reason Report Definition DMO is a Data 360 data model object for a conversation mining report that contains an overview of the conversational data shape and groups of conversation reasons and excerpts.
ConversationReasonReportSegmentDef	The Conversation Reason Report Segment Def DMO is a Data 360 data model object for a segment definition of a conversation reason report.
DataUseLegalBasis	The Data Use Legal Basis DMO is a Data 360 data model object (DMO) for the legal reason for contacting a customer, such as billing or contract.
DataUsePurpose	The Data Use Purpose DMO is a Data 360 data model object (DMO) for the purpose of contacting a prospect or customer, such as for billing, marketing, or surveys.
DataUsePurposeConsentAction	The Data Use Purpose Consent Action DMO is a Data 360 data model object (DMO) for individual consent preferences for consent actions.
DepositAccount	Represents a subtype of a financial account. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
Device	The Device data model object (DMO) is a Data 360 DMO for a specificelectronic unit that you want to track signals from, for example, a refrigerator, watch, orcar.
DeviceApplicationEngagement	The Device Application Engagement DMO is a Data 360 data model object(DMO) for data about device engagement, for example mobile app usage.
DeviceApplicationTemplate	The Device Application Template DMO is a Data 360 data model object(DMO) for a reusable standard format for applications to exchange information betweendevices.
EmailEngagement	The Email Engagement DMO is a Data 360 data model object (DMO) for data captured from various data sources about engagement in the Email channel.
EmailMessage	The Email Message DMO is a Data 360 data model object (DMO) for an email message, usually text, but possibly HTML, including attachments sent or received over the network.
EmailPublication	The Email Publication DMO is a Data 360 data model object (DMO) that contains information about a publication such as a campaign or an orchestration used in the Email channel
EmailTemplate	The Email Template DMO is a Data 360 data model object (DMO) for the standard form of an email message that can be personalized and customized based on a campaign.
EngagementChannelType	The Engagement Channel Type DMO is a Data 360 data model object (DMO) for which channels are supported by individual preferences. For example, individuals can set consent preferences for SMS but not for a phone call.
EngagementChannelTypeConsent	The Engagement Channel Type Consent DMO is a Data 360 data model object (DMO) for an individual’s consent preferences specific to a type of communication, such as email.
EngagementTopic	The Engagement Topic DMO is a Data 360 data model object (DMO) that is used to refer to multiple topics such as multiple campaigns or promotions.
FinancialAccount	Represents a financial account held at a financial institution such as a bank. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
FinancialAccountBalance	Represents types of balances associated to a financial account. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
FinancialAccountFee	Represents fees associated with a financial account. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
FinancialAccountInterestRate	Represents the interest rate associated with a financial account. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
FinancialAccountLimit	Represents the limits associated with a financial account. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
FinancialAccountParty	Represents the role of an organization account or person account related to a financial account. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
FinancialAccountTransaction	Represents transactions related to a financial account. There can be various types of transactions such as credit, debit, and so forth. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
FinancialCustomer	Represents an extension of an account to capture financial services attributes. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
FinancialGoal	Represents the money to achieve a financial goal such as education or home purchase. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
FinancialGoalFunding	Represents a financial goal of an individual or person account that requires funding. This DMO is available in API version 61 and later.
FinancialGoalParty	Represents an association between a financial goal and the related party. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
FinancialHolding	Represents the financial holdings associated with either an account or a financial account. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
FinancialPlan	Represents a financial plan for a person account. This DMO is available in API version 61 and later.
FinancialSecurity	Represents a financial holding such as securities, bonds, mutual funds, and so forth in relation to either an account or a financial account (investment account). Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
Flow	The Flow DMO is a Data 360 data model object (DMO) for details about a flow.
FlowElement	The Flow Element DMO is a Data 360 data model object (DMO) for details about a single element within a flow version.
FlowElementRun	The Flow Element Run DMO is a Data 360 data model object (DMO) for the status of a single element executed within a flow run.
FlowRun	The Flow Run DMO is a Data 360 data model object (DMO) for details about a single execution of a flow.
FlowVersion	The Flow Version DMO is a Data 360 data model object (DMO) for details about a version of a flow.
FlowVersionOccurrence	The Flow Version Occurrence DMO is a Data 360 data model object (DMO) for an instance of a recurring flow that runs on a schedule. For example, a flow that runs weekly on Wednesdays creates an occurrence each time it runs.
GoodsProduct	The Goods Product DMO is a Data 360 data model object (DMO) for a specific product, for example a carton of milk or a set of towels
Individual	The Individual DMO is a Data 360 data model object for contacts,customers, or other people interested in your company's products or services.
InsurancePolicy	Represents an insurance policy. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
InterestTagDefinition	Represents products, services, features in which a party has expressed interest. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
InvestmentAccount	Represents a subtype of a financial account. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
KnowledgeArticleEngagement	Stores the user engagement details related to a Knowledge Article. It’s a logical subtype of EngagementAction. Provided by the Knowledge Engagement Ingestion API. This DMO is available in API version 58 and later.
Lead	The Lead data model object (DMO) is a Data 360 DMO for a person orcompany that shows interest in a company’s products or services.
LoanAccount	Represents a subtype of financial account. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
LoyaltyBenefit	The Loyalty Benefit DMO is a Data 360 data model object (DMO) for a perk or betterment that is available to the members of a Loyalty Program. Examples include waived airline baggage fees, complimentary hotel stays, or a rental car upgrade.
LoyaltyBenefitType	The Loyalty Benefit Type DMO is a Data 360 data model object (DMO) for the type of loyalty benefit, such as rewards or gift cards.
LoyaltyJournalSubtype	The Loyalty Journal Subtype DMO is a Data 360 data model object (DMO) for a subtype of a loyalty journal type, such as a watched video or product review.
LoyaltyJournalType	The Loyalty Journal Type DMO is a Data 360 data model object (DMO) for the type of loyalty journal.
LoyaltyLedger	The Loyalty Ledger DMO is a Data 360 data model object (DMO) to record the points credited or debited for a member across transactions.
LoyaltyMemberCurrency	The Loyalty Member Currency DMO is a Data 360 data model object (DMO) representing the value a loyalty member selects to receive, for example, as airline miles or as points.
LoyaltyMemberTierDataModelObject	The Loyalty Member Tier DMO is a Data 360 data model object (DMO) for the benefit tier within the program that a member is assigned.
LoyaltyPartnerProduct	The Loyalty Partner Product DMO is a Data 360 data model object (DMO) for a product offered by a loyalty program partner, such as a coupon from another company.
LoyaltyProgram	The Loyalty Program DMO is a Data 360 data model object (DMO) for a strategy designed to encourage customers to continue to be loyal to the business associated with the program.
LoyaltyProgramCurrency	The Loyalty Program Currency DMO is a Data 360 data model object (DMO) representing the value or currency that the loyalty program offers to customers.
LoyaltyProgramMemberDataModelObject	The Loyalty Program Member DMO is a Data 360 data model object (DMO) for a person who has joined a loyalty program.
LoyaltyProgramMemberPromotion	The Loyalty Program Member Promotion DMO is a Data 360 data model object (DMO) that represents details about a promotion available to a loyalty program member. For example, if a program allows double points on outdoor purchases.
LoyaltyProgramPartner	The Loyalty Program Partner DMO is a Data 360 data model object (DMO) for companies with loyalty program offerings.
LoyaltyTier	The Loyalty Tier DMO is a Data 360 data model object (DMO) for a level of a loyalty program where member benefits increase at higher levels of the hierarchy.
LoyaltyTierBenefit	The Loyalty Tier Benefit DMO is a Data 360 data model object (DMO) for a benefit that is available in a specific loyalty member tier.
LoyaltyTierGroup	The Loyalty Tier Group DMO is a Data 360 data model object (DMO) for loyalty programs that have multiple tiers of benefits. Tiers can be organized based on objectives, for example, lifetime, marketing, or regular.
LoyaltyTransactionJournal	The Loyalty Transaction Journal DMO is a Data 360 data model object (DMO) for a collection of transactions related to a loyalty program. Loyalty Transaction Journals are related to a voucher, but could relate to other payment method types.
MarketJourneyActivity	The Market Journey Activity data model object (DMO) is a Data 360 DMO for a step or activity within a journey in Journey Builder.
MarketSegment	The Market Segment DMO is a Data 360 data model object (DMO) for a group of people who share one or more common characteristics, grouped for marketing purposes.
MasterProduct	The Master Product DMO is a Data 360 data model object (DMO) for data about a company’s products.
MemberBenefit	The Member Benefit DMO is a Data 360 data model object (DMO) for a benefit available within the loyalty program that a member is qualified for and has elected to receive.
MessageEngagement	The Message Engagement DMO is a Data 360 data model object (DMO) for a user’s engagement with a marketing message.
OperatingHours	The Operating Hours DMO is a Data 360 data model object (DMO) for when a business or business function is available for use.
Opportunity	The Opportunity DMO is a Data 360 data model object (DMO) for deals or sales that are in progress and not yet completed.
OpportunityProduct	The Opportunity Product DMO is a Data 360 data model object (DMO) for connecting an opportunity to the product that it represents, allowing for a many-to-many relationship.
OrderDeliveryMethod	The Order Delivery Method data model object (DMO) is a Data 360 DMO for the order and delivery methods for products or service fulfillment.
Party	Represents information about who you are dealing with. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
PartyConsent	The Privacy Consent DMO is a Data 360 data model object (DMO) for an individual’s consent preferences.
PartyExpense	Represents the expense incurred by an individual or account. This DMO is available in API version 61 and later.
PartyFinancialAsset	Represents a financial asset associated with an individual or an organization. For example, cash in hand, owned property, and so forth. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
PartyFinancialLiability	Represents a financial liability associated with an individual or an organization. For example a mortgage or loan. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
PartyIdentification	The Party Identification DMO is a Data 360 data model object for theways to identify a party, such as a driver’s license or a birth certificate.
PartyIncome	Represents the income of an individual or a business. The income can be from salaries, commissions, fees, rental properties, and other sources. This DMO is available in API version 61 and later.
PartyInterestTag	Represents an association between a party and interest tag. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
PaymentMethod	Represents the way a customer pays for a transaction.
PersonLifeEvent	Represents a major life event for an individual. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
PrivacyConsentLog	The Privacy Consent Log DMO is a Data 360 data model object (DMO) for information about a user’s requested consent and privacy information.
ProductBrowseEngagement	Product Browse Engagement DMO is a Data 360 data model object (DMO) for data captured from a user action, such as searching for products or viewing a list of products.
ProductCatalog	The Product Catalog DMO is a Data 360 data model object (DMO) for a company’s inventory or merchandising catalog.
ProductCatalogCategory	The Product Catalog Category DMO is a Data 360 data model object (DMO) for the category of the product catalog, such as shoes, trucks, or housewares.
ProductCategory	The Product Category data model object (DMO) is a Data 360 DMO for the types of products a company has or offers, such as shoes or types of services.
ProductCategoryProduct	The Product Category Product data model object (DMO) is a Data 360 DMO used to identify how products are assigned to categories. For example, Northern Trail Outfitters can use this DMO to identify how a specific running shoe is assigned to a shoe and running categories.
ProductOrderEngagement	The Product Order Engagement DMO is a Data 360 data model object (DMO) for a user’s online shopping order data.
Promotion	The Promotion DMO is a Data 360 data model object (DMO) for loyalty promotion details such as the type of promotion.
PromotionLoyaltyPartnerProduct	The Promotion Loyalty Partner Product DMO is a Data 360 data model object (DMO) for the promotion of a product that a partner is co-marketing to loyalty program members.
RecordAlert	Represents record alerts for an account. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.
SalesOrder	The Sales Order DMO is a Data 360 data model object (DMO) that provides information around current and pending sales orders.
SalesOrderProduct	The Sales Order Product DMO is a Data 360 data model object (DMO) for the component of a sales order that identifies a product or service to be sold to the customer.
SalesStore	The Sales Store DMO is a Data 360 data model object (DMO) that provides information regarding a retail establishment selling items to the public.
ServicePresenceStatus	The Service Presence Status DMO is a Data 360 data model object (DMO) for a presence status that can be assigned to a service channel. For example, Available for Leads, Out for Lunch, or Busy.
ShoppingCartEngagement	The Shopping Cart Engagement DMO is a Data 360 Data Platform data model object (DMO) for data captured from user actions, such as adding and removing items from a shopping cart.
ShoppingCartEventType	The Shopping Cart Event Type DMO is a Data 360 data model object (DMO) for when a customer interacts with a commerce site’s shopping cart.
ShoppingCartProductEngagement	The Shopping Cart Product Engagement DMO is a Data 360 data model object (DMO) for data captured from user actions, such as adding and removing items from a shopping cart.
Skill	The Skill DMO is a Data 360 data model object (DMO) for proficiency, competence, or expertise that an employee possesses, which is useful to the mission of an organization.
SMSPublication	The SMS Publication DMO is a Data 360 data model object (DMO) for the process that sends out a set of SMS messages to multiple recipients.
SMSTemplate	The SMS Template DMO is a Data 360 data model object (DMO) for a reusable, standard format for text (SMS) messages.
SoftwareApplication	The Software Application DMO is a Data 360 data model object (DMO) for defining programs created for the end user, such as an app for Northern Trail Outfitters loyalty members.
Survey	The Survey DMO is a Data 360 data model object (DMO) for a survey.
SurveyInvitation	The Survey Invitation DMO is a Data 360 data model object (DMO) for the invitation sent to a participant to complete the survey.
SurveyQuestion	The Survey Question DMO is a Data 360 data model object (DMO) for a question in a survey under a section.
SurveyQuestionResponse	The Survey Question Response DMO is a Data 360 data model object (DMO) for participants who answer specific questions.
SurveyQuestionSection	The Survey Question Section DMO is a Data 360 data model object (DMO) for a section, such as the title section or a question section, in a survey.
SurveyResponse	The Survey Response DMO is a Data 360 data model object (DMO) for an answer to a survey question.
SurveySubject	The Survey Subject DMO is a Data 360 data model object (DMO) for a relationship between a survey and another object, such as an account or a case.
SurveyVersion	The Survey Version DMO is a Data 360 data model object (DMO) for a version of the survey.
User	The User DMO is a Data 360 data model object (DMO) for an account, a person or a machine, that can log in to use the deployed software system.
UserGroup	The User Group DMO is a Data 360 data model object (DMO) for a set of system users with common characteristics. User Groups are often created to simplify the granting of system privileges and granting access to resources.
Voucher	The Voucher DMO is a Data 360 data model object (DMO) for a loyalty program’s voucher.
VoucherDefinition	The Voucher Definition DMO is a Data 360 data model object (DMO) for details about a voucher definition associated with a loyalty program.
WebSearchEngagement	The Web Search Engagement DMO is a Data 360 data model object (DMO) for web search engagement data.
WebsiteEngagement	The Website Engagement DMO is a Data 360 data model object (DMO) for any data associated with website engagement, such as views or clicks.

The Account DMO is a Data 360 data model object for how a party wantsto interact with your company. Provided in both the Sales and Service Data Kits.

Columns

Name	Type	References	Description
Account Business Type	Varchar		The business type of the account.
Account Description	Varchar		A text description of the contact account.
Account ID	Varchar		A unique ID used as the primary key for the account DMO.
Account Name	Varchar		The name of the contact account.
Account Number	Varchar		The number assigned to the contact account.
Account Ownership Type	Varchar		The ownership type of the account.
Account Rating Type	Varchar		The rating type of the account.
Account Source	Varchar		The source of the account.
Account Type	Varchar		The reference ID for the type of contact account, for example, aloyalty account or business credit account.
Account Service Entitlements	Varchar
Account Website URL	Varchar		The website URL for the account.
Annual Revenue Amount	Double		The annual revenue amount for the account.
Assign Territory Flag	Varchar		An indicator if an account needs an assigned territory.
Auto Pay Enabled Flag	Varchar		An indicator if auto pay is enabled for this contact.
Auto Payment Amount	Double		The amount to be automatically paid next time.
Auto Payment Amount Currency	Varchar		The currency of the auto payment amount.
Auto Payment Method	Varchar		A reference ID for the payment method for auto pay, for example acredit card or bank account.
Balance Amount	Double		The number in an account balance, for example, 100 points, 3miles, or 6 visits.
Balance Amount Currency	Varchar		The balance amount’s currency.
Balance Amount Limit	Varchar		The max balance allowed, for example a credit limit.
Balance Amount Limit Currency	Varchar		The balance amount limit’s currency.
Balance Unit Of Measure	Varchar		A reference ID for the unit of measure for balance, for examplemoney, points, or miles.
Balance Unit Of Measure Currency	Varchar		A reference ID for the currency type used if balance unit ofmeasure is money.
Bill Contact Address	Varchar		A reference ID for the contact point billing address.
Bill Delivery Method	Varchar		A reference ID for the preference to send the bill via electronicor physical mail delivery.
Bill Frequency	Varchar		The frequency for billing the account.
Contact Point Address	Varchar		A reference ID for the contact point address.
Contact Point App	Varchar		A reference ID for the contact point app.
Contact Point Email	Varchar		A reference ID for the contact point email.
Contact Point Location	Varchar		A reference ID for the contact point location.
Contact Point Phone	Varchar		A reference ID for the contact point phone.
Contact Point Social	Varchar		A reference ID for the contact point social.
Created Date	Datetime		The date the record was created.
Data Source	Varchar		A reference ID for the logical name of the object where thisrecord came from, whether that is a name of a cloud storage file oranother connector’s external object.
Data Source Object	Varchar		A reference ID for the logical name of the object where thisrecord came from, whether that is a name of a cloud storage file oranother connector’s external object.
Default Freight Terms	Varchar		A reference ID to the standard freight terms for this contact forexample, Free On Board Shipping Point (FOB).
Default Price Book	Varchar		A reference ID to the standard price book for thiscontact.
Effective Date	Datetime		The effective date of the record.
Employee Count	Double		The count of employees.
End Date	Datetime		The end date of the record.
External Record ID	Varchar		A reference ID for an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID wasassigned.
Fax Phone	Varchar		A reference ID to the fax number for the account.
General Ledger Account	Varchar		A reference ID for a contact’s financial account where credit anddebits are posted.
Hold Status Reason	Varchar		A reference ID for the reason why the account is on hold.
Individual	Varchar		A reference ID for the individual associated with theaccount.
Internal Organization	Varchar		A reference ID to the business unit or other internalorganization that owns the business account.
Is Active	Varchar		An indicator if the account is active.
Is Customer	Varchar		An indicator if the account is a customer account.
Is Internal	Varchar		An indicator if the account is an internal account.
Is Partner	Varchar		An indicator if the account is a partner account.
Is Seller	Varchar		An indicator if the account is a seller account.
Is Supplier	Varchar		An indicator if the account is a supplier account.
Last Activity Date	Datetime		The date of the most recent account activity.
Last Modified Date	Datetime		The date when a user last modified the record.
Next Interaction Date	Datetime		The date when the account should be contacted again.
Next Review Date	Datetime		The date when the account should be reviewed again.
Ninety Day Balance Amount	Double		The account balance 90 days ago.
Ninety Day Balance Amount Currency	Varchar		The 90-day account balance currency.
Operating Hours	Varchar		A reference ID to the operating hours associated with theaccount.
Order Delivery Method	Varchar		A reference ID to the standard method for delivery such asovernight or in-person pickup.
Organization	Varchar		A reference ID to the account organization.
Parent Account	Varchar		A reference ID to the parent contact account.
Party	Varchar		A reference ID to the parent party, for example, an individual,business, or affiliation group.
Party Object	Varchar		A reference ID to the associated party object.
Party Role	Varchar		A reference ID to the associated party role, for example, acustomer, supplier, or competitor.
Party Web Address	Varchar		A reference ID to the associated party web address.
Payment Term	Varchar		The payment term for the account.
Primary Industry	Varchar		The primary industry for the account.
Primary Sales Contact Point	Varchar		A reference ID for the best way to communicate with the contactregarding sales.
Primary Sales Rep	Varchar		A reference ID for the sales owner of the account.
Review Frequency	Varchar		The frequency for reviewing the account.
Sales Phone	Varchar		A reference ID for the phone number for this account.
Shipping Address	Varchar		A reference ID for the shipping billing address.
Shipping Contact	Varchar		A reference ID for the shipping contact.
Shipping Email	Varchar		A reference ID for the email used for shipping inquiries.
Shipping Phone	Varchar		A reference ID for the phone for shipping inquiries.
Sixty Day Balance Amount	Double		The account balance 60 days ago.
Sixty Day Balance Amount Currency	Varchar		The 60-day account balance currency.
SLA Expiration Date	Datetime		The date when a Service Level Agreement (SLA) expires.
SLA Type	Varchar		A reference ID for the contact’s Service Level Agreement (SLA)type.
Source System Identifier	Varchar		The identifier for the source system.
Source System Modified Date	Datetime		The date and time for when the source system wasmodified.
Thirty Day Balance Amount	Double		The account balance 30 days ago.
Thirty Day Balance Amount Currency	Varchar		The 30-day account balance currency.
Use As Billing Account	Varchar		An indicator if the account is used for billing purposes.
Use As Sales Account	Varchar		An indicator if the account is used for sales.
Use As Service Account	Varchar		An indicator if the account is used for service.
Use As Shipping Account	Varchar		An indicator if the account is used for shippingpurposes.
Website	Varchar		The website address for the account.

The Account Contact DMO is a Data 360 data model object for anindividual who has a role specific to an account. Provided in both the Sales and ServiceData Kits.

Columns

Name	Type	References	Description
Account	Varchar		A reference ID to the account where this contact is included orlinked.
Account Contact ID	Varchar		A unique ID used as the primary key for the account contactDMO.
Account Contact Roles	Varchar		The business role of the contact at the account.
Assistant Name	Varchar		The name of the contact’s assistant.
Assistant Phone	Varchar		The phone number of the assistant.
Business Phone	Varchar		A reference ID to the phone number for the contact.
Contact Email	Varchar		A reference ID to the email address for the contact.
Contact Note	Varchar		A description of the contact.
Created Date	Datetime		The date the record was created.
Data Source	Varchar		A reference ID for the logical name for a system that is thesource of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where thisrecord came from, whether that is a name of a cloud storage file oranother connector’s external object.
Deceased	Varchar
Department Name	Varchar		The department the contact works in.
External Record ID	Varchar		A reference ID for an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID wasassigned.
Fax Phone	Varchar		A reference ID for the fax number for the contact.
First Name	Varchar		The first name of the contact.
Gender	Varchar		The gender of the contact.
Home Phone	Varchar		A reference ID for the home phone number for the contact.
Indirect Relation Account Contact	Varchar		A reference ID to a contact with an indirect relationship to theaccount contact.
Individual	Varchar		A reference ID for the person that is the contact for theaccount.
Internal Organization	Varchar		A reference ID to the business unit or other internalorganization that owns the business account.
Last Activity Date	Datetime		The date of the most recent account activity.
Last Modified Date	Datetime		The date when a user last modified the record.
Last Name	Varchar		The last name of the contact.
Mailing Address	Varchar		A reference ID to the mailing address.
Marital Status	Varchar		The marital status of the contact.
Middle Name	Varchar		The middle name of the contact.
Mobile Phone	Varchar		A reference ID to the contact’s mobile phone number.
Other Contact Address	Varchar		A reference ID to another contact address.
Person Name	Varchar		The person name of the contact.
Reports To Account Contact	Varchar		A reference ID to the contact’s manager.
Sequence In Multiple Birth	Double
Title	Varchar		The contact’s title for example, vice president orspecialist.

The Affiliation DMO is a Data 360 data model object for affiliation orhow to map data Marketing Cloud Engagement business unit use cases.

Columns

Name	Type	References	Description
Affiliated To	Varchar		Provides info about the business unit like brand or region.
Affiliation ID	Varchar		A unique ID used as the primary key for the affiliation DMO.
Affiliation Type	Varchar		The type or category of the affiliation such as product category.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of recordsidentified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whetherthat is a name of a cloud storage file or another connector’sexternal object.
External Record ID	Varchar		A reference ID for an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID was assigned.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns thebusiness account.
Party	Varchar		A reference ID to the parent party, for example, an individual, business, or affiliationgroup.

The Agent Service Presence DMO is a Data 360 data model object (DMO) for a presence user’s real-time presence status.

Columns

Name	Type	References	Description
Agent Service Presence Id	Varchar		A unique ID used as the primary key for the agent service presence DMO.
At Capacity Duration	Double		The duration a service agent was at capacity.
Average Capacity	Double		The average capacity for a service agent.
Away	Varchar		An indicator if a service agent is away.
Configured Capacity	Double		The configured capacity for a service agent.
Current State	Varchar		An indicator of the current state of a service agent.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
Description	Varchar		The description for the service agent presence.
Idle Duration	Double		The duration a service agent was idle.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the agent service presence.
Name	Varchar		The name of a service agent.
Service Presence Status Id	Varchar		A reference ID to the status of the agent service presence.
Status Duration	Double		The duration a service agent status lasted.
Status End Date	Datetime		The date a service agent status ended.
Status Start Date	Datetime		The date a service agent status started.
User ID	Varchar		A reference Id to the agent service presence user.

The Agent Work DMO is a Data 360 data model object (DMO) for a work assignment that has been routed to an agent.

Columns

Name	Type	References	Description
Accept Date	Datetime		The date the agent work was accepted.
Active Time	Double		The time an agent is active on work in seconds.
After Conversation Work Actual Time	Double		The actual time an agent works after a conversation.
After Conversation Work Extension Count	Double		The count an agent works extended time.
Agent Work Id	Varchar		A unique ID used as the primary key for the agent work DMO.
Agent Work Routing Type	Varchar		The routing type for agent work.
Agent Work Routing Type	Varchar		The status of the agent work.
Bot	Varchar		A reference ID to the agent work bot.
Close Date	Datetime		The date the agent work was closed.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
Description	Varchar		The description of the agent work.
Handle Time	Double		The time to handle agent work in seconds.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the agent service presence.
Name	Varchar		The name of the agent work.
Preferred User	Varchar		A reference ID to the preferred user for the agent work.
Related To	Varchar		A reference ID to the related item for the agent work.
Request DateTime	Datetime		The date the agent work was requested.
Speed To Answer	Double		The time to answer the agent work in seconds.
User	Varchar		A reference Id to the agent work user.
User Group	Varchar		A reference Id to the agent work user group.

The Agent Work Skill DMO is a Data 360 data model object (DMO) for a for a skill used to route a work assignment to an agent.

Columns

Name	Type	References	Description
Additional Skill	Varchar		An indicator if the agent work skill is an additional skill.
Agent Work	Varchar		A reference ID to the agent work for the agent work skill.
Agent Work Skill Id	Varchar		A unique ID used as the primary key for the agent work DMO.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
Description	Varchar		The description of the agent work skill.
Dropped	Varchar		An indicator if the agent work skill has been dropped.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the agent service presence.
Name	Varchar		The name of the agent work skill.
Skill	Varchar		A reference ID to the skill for the agent work skill.
Skill Level	Double		The level of the skill.
Skill Priority	Double		The priority of the skill.

The Authorization Form DMO is a Data 360 data model object (DMO) for the set of terms and conditions, such as privacy policy, contract, or consent forms.

Columns

Name	Type	References	Description
Authorization Form ID	Varchar		A unique ID used as primary key for the Authorization Form DMO.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
Details URL	Varchar		A URL link with details about terms and conditions.
Effective From Date	Datetime		The date when consent form is in effect.
Effective To Date	Datetime		The date when consent form is no longer in effect.
External Record ID	Varchar		A reference ID for an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID was assigned.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the data record.
Revision Number	Varchar		A free text field to store a revision number for consent form, for example
Summary Text	Varchar		A summary of consent form content.

The Authorization Form Consent DMO is a Data 360 data model object (DMO). This DMO captures the where, when, and how a party gives consent for a form, a set of terms and conditions, or a privacy policy.

Columns

Name	Type	References	Description
Authorization Form Text	Varchar		A reference ID to an authorization form’s text and language settings.
AuthorizationFormConsent ID	Varchar		A unique ID used as the primary key for the Authorization Form Consent DMO.
Consent Captured Date Time	Datetime		The date and time consent was captured.
Consent Captured Source	Varchar		The location consent was captured.
Consent Status	Varchar		A reference ID to the status of consent, for example opted in or out of data collection.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by an external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
Effective From Date	Datetime		The date when a consent form is in effect.
Effective To Date	Datetime		The date when a consent form is no longer in effect.
Engagement Channel Type	Varchar		A reference ID to the channels supported by individual preferences.
Entity	Varchar		A reference ID to the entity related to the authorization consent form.
External Record ID	Varchar		A reference ID to an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID was assigned.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the business account.
Name	Varchar		The name of the authorization consent form.
Party	Varchar		A reference ID to the parent party, for example, an individual, business, or affiliation group.
Party Role	Varchar		A reference ID to an associated party role, for example, a customer, supplier, or competitor.

The Authorization Form Data Use data model object (DMO) is a Data 360 DMO for the data uses consented to in an authorization form.

Columns

Name	Type	References	Description
Authorization Form Data Use ID	Varchar		A unique ID used as the primary key for the Authorization Form Data Use DMO.
Authorization Form ID	Varchar		A reference ID to a set of terms and conditions, such as privacy policy, contract, and consent form. This field name includes version numbers and associated references to external documents.
Data Source	Varchar		A reference ID for a system’s logical name that is the source of records identified by an external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record originated. For example, the name of a cloud storage file or another connector’s external object.
Data Use Purpose ID	Varchar		A reference ID for the reason for contacting a prospect or customer, for example, billing, marketing, or surveys.
External Record Id	Varchar		A reference ID for an external data source system.
External Source Id	Varchar		A reference ID for the system in which the external record ID was assigned.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the data record.

The Authorization Form Text DMO is a Data 360 data model object (DMO) for an authorization form’s text and language settings.

Columns

Name	Type	References	Description
Authorization Form	Varchar		A reference ID to set of terms and conditions (such as privacy policy, contract, and consent form), including version numbers and associated references to external documents.
Authorization Form Text	Varchar		A unique ID used as primary key for the Authorization Form Text DMO.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
External Record ID	Varchar		A reference ID for an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID was assigned.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the data record.
Locale	Varchar		A reference ID to range of longitudes where common standard time is used.
Version Number	Varchar		An edition or variant of the authorization form text.

The Benefit Action DMO is a Data 360 data model object (DMO) for actions triggered when a program benefit is assigned to a loyalty program member.

Columns

Name	Type	References	Description
Benefit Action ID	Varchar		A unique ID used as primary key for the Benefit Action DMO.
Benefit Action Process Type	Varchar		A reference ID to the benefit action process type, such as loyalty or rebates.
Created Date	Datetime		The date the record was created.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
Description	Varchar		A description of the benefit action.
External Record ID	Varchar		A reference ID for an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID was assigned.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the data record.
Is Benefit Expiration Allowed	Varchar		An indicator if the benefits expire.
Is Benefit Update Allowed	Varchar		An indicator if updates can be made to benefits.
Last Modified Date	Datetime		The date when a user last modified the record.

The Benefit Type DMO is a Data 360 data model object (DMO) for the types of benefits of a loyalty program, such as healthcare, financial, and loyalty.

Columns

Name	Type	References	Description
Benefit Type ID	Varchar		A unique ID used as primary key for the Benefit Type DMO.
Created Date	Datetime		The date the record was created.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
External Record ID	Varchar		A reference ID for an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID was assigned.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the data record.
Last Modified Date	Datetime		The date when a user last modified the record.
Name	Varchar		The name of the benefit type.

The Brand DMO is a Data 360 data model object (DMO) for the product’s brand, for example, Northern Trail Outfitters.

Columns

Name	Type	References	Description
Brand Grade	Varchar		A reference ID to the brand’s grade, for example, premium or regular.
Brand ID	Varchar		A unique ID used as primary key for the Brand DMO.
Created Date	Datetime		The date the record was created.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
External Record ID	Varchar		A reference ID to an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID was assigned.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the business account.
Last Modified Date	Datetime		The date when a user last modified the record.
Name	Varchar		The brand name of a product, for example Northern Trail Outfitters.
Parent Brand	Varchar		A reference ID to the parent brand.

Represents a financial tool offered by a bank as a type of loan, with a line of revolving credit that you can access via your card and your card's account. Provided in the Financial Services Cloud Data Kit. This DMO is available in API version 58 and later.

Columns

Name	Type	References	Description
Description	Varchar		The credit card account description.
Financial Account	Varchar		The associated financial account.
Card Account ID	Varchar		The primary key.
Name	Varchar		The credit card account. name.

The Case DMO is a Data 360 data model object (DMO) for support cases based on recorded issues, for example, a laptop connectivity problem. Provided in both the Sales and Service Data Kits.

Columns

Name	Type	References	Description
Account	Varchar		A reference ID to the account where this contact is included or linked.
Account Contact	Varchar		The reference ID for the account contact of the case.
Assigned User	Varchar		A reference ID to the assigned customer support rep.
Assigned User Object	Varchar		The assigned user object for the case.
Case Category	Varchar		A reference ID to the category of the case.
Case Closure Reason	Varchar		A reference ID to the reason the case was closed.
Case Comments	Varchar		The comments for the case.
Case Comments Relationship	Varchar
Case Creation Channel	Varchar		A reference ID to the channel where the case was created.
Case ID	Varchar		A unique ID used as the primary key for the account contact DMO.
Case Number	Varchar		A number assigned to the case.
Case Priority	Varchar		A reference ID to the priority of the case, for example, high, medium, or low.
Case Status	Varchar		A reference ID to the status of the case, for example, new, in progress, or closed.
Case Support Work Hours	Varchar		A reference ID to the working hours of support.
Case Type	Varchar		A reference ID to the type of case, for example, a question or a problem.
Closed	Varchar		An indicator if the case is closed.
Closed Date Time	Datetime		The date and time that the case was closed.
Closed When Created	Varchar		An indicator if the case was closed when created.
Created Date	Datetime		The date the record was created.
Date Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
Description	Varchar		The case description.
Email Messages	Varchar		The email messages for a case.
Escalated	Varchar		An indicator if the case is escalated.
Escalation Date	Datetime		The date the case was escalated.
External Record ID	Varchar		A reference ID for an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID was assigned.
Individual	Varchar		A reference ID for the individual associated with the case.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the business account.
Is Web Portal Visible	Varchar		An indicator if the web portal is visible.
Last Modified Date	Datetime		The date when a user last modified the record.
Name	Varchar		The name of the case.
Origin	Varchar		The origin of the case.
Parent Case	Varchar		A reference ID to an associated parent case.
Service Entitlement	Varchar		A reference ID to the customer’s support level, for example, phone or chat support only.
Subject	Varchar		A short description of the case.

The Case Update DMO is a Data 360 data model object (DMO) for a for historical information about changes made to the associated case.

Columns

Name	Type	References	Description
Case Id	Varchar		A reference ID to the associated case.
Case Update Id	Varchar		A unique ID used as the primary key for the case update DMO.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
Description	Varchar		The case update description.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the case.
Last Modified By	Varchar		A reference ID to the user that made the last update.
Last Modified Date	Datetime		The date when a user last modified the record.
Name	Varchar		The name of the case update.
Previous Update Date	Datetime		The date and time the case was previously updated.
Related Owner	Varchar		A reference ID to the related user for the update.
Related Owner Object	Varchar		The related owner object for the case update.
Status	Varchar		The status of the case update.

The Communication Subscription DMO is a Data 360 data model object (DMO) for a customer’s subscription preferences for a specific communication.

Columns

Name	Type	References	Description
Communication Subscription ID	Varchar		A unique ID used as primary key for the Communication Subscription DMO.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
Data Use Purpose ID	Varchar		A reference ID to reason for contacting a prospect or customer, for example billing, marketing, or surveys.
External Record ID	Varchar		A reference ID to an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID was assigned.
Internal Organization	Varchar		A reference ID to business unit or other internal organization that owns the business account.
Name	Varchar		The name of the communication subscription.

The Communication Subscription Channel Type DMO is a Data 360 data model object (DMO) for the engagement channel through which a customer is reached for a communication subscription.

Columns

Name	Type	References	Description
Communication Subscription Channel Type ID	Varchar		A unique ID used as primary key for the Communication Subscription Channel Type DMO.
Communication Subscription ID	Varchar		A reference ID to customer’s subscription preferences for a specific communication.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
Engagement Channel Type	Varchar		A reference ID to channel type by which message can be delivered, for example email, phone call, SMS message, or TV advertisement.
External Record ID	Varchar		A reference ID for an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID was assigned.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the data record.

The Communication Subscription Consent DMO is a Data 360 data model object (DMO) for the engagement or communication channel preferences of a customer.

Columns

Name	Type	References	Description
Communication Subscription	Varchar		A reference ID to customer’s subscription preferences for a specific communication.
Communication Subscription Consent ID	Varchar		A unique ID used as primary key for the Communication Subscription Consent DMO.
Consent Captured Date Time	Datetime		The date and time consent was captured.
Consent Captured Source	Varchar		The source where consent was captured.
Consent Status	Varchar		A reference ID to a user’s consent status.
Contact Point	Varchar		A reference ID to contact point for party, for example phone number or email address.
Contact Point Consent	Varchar		A reference ID to contact point where customer gives their consent.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
Effective From Date	Datetime		The date when consent form is in effect.
Effective To Date	Datetime		The date when consent form is no longer in effect.
Engagement Channel Type	Varchar		A reference ID to engagement channel type.
Engagement Channel Type Consent	Varchar		A reference ID to engagement channel consent type.
External Record ID	Varchar		A reference ID to an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID was assigned.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the business account.
Name	Varchar		The name of the communication subscription.
Party	Varchar		A reference ID to the parent party, for example, an individual, business, or affiliation group.
Party Role	Varchar		A reference ID to associated party role, for example, a customer, supplier, or competitor.

The Communication Subscription Timing DMO is a Data 360 data model object (DMO) for a customer's timing preferences for receiving a communication subscription.

Columns

Name	Type	References	Description
Communication Subscription Consent	Varchar		A reference ID to preferences for one engagement channel on one communication subscription (channel) created by a care coordinator or patient.
Communication Subscription Timing ID	Varchar		A unique ID used as primary key for the Communication Subscription Timing DMO.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
External Record ID	Varchar		A reference ID to an external data source system.
External Source ID	Varchar		A reference ID to system that External Record ID was assigned.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the business account.
Offset	Varchar		When a communication subscription occurs based on the unit of measure, for example, if UOM is
Preferred Time End	Datetime		The user’s preferred end time to stop sending communications.
Preferred Time Start	Datetime		The user’s preferred start time for sending communications.
Preferred Time Zone	Varchar		A reference ID to range of longitudes where common standard time is used.
Time UOM	Varchar		A reference ID to unit of measure (time) used in communication subscription, for example hours or days.

The Consent Action DMO is a Data 360 data model object (DMO) for what a user consents to be done with their data, for example, data collection or web activity tracking.

Columns

Name	Type	References	Description
Consent Action ID	Varchar		A unique ID used as primary key for the Consent Action DMO.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
External Record ID	Varchar		A reference ID to an external data source system.
External Source ID	Varchar		A reference ID to system that External Record Id was assigned.
Internal Organization	Varchar		A reference ID for the system in which the external record ID was assigned.
Name	Varchar		The consent action name.

The Consent Status DMO is a Data 360 data model object (DMO) for the status of consent, for example opted in or out of data collection.

Columns

Name	Type	References	Description
Consent Status ID	Varchar		A unique ID used as primary key for the Consent Status DMO.
Data Source	Varchar		A reference ID for the logical name for a system that is the source of records identified by external record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where this record came from, whether that is a name of a cloud storage file or another connector’s external object.
External Record ID	Varchar		A reference ID for an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID was assigned.
Internal Organization	Varchar		A reference ID to the business unit or other internal organization that owns the business account.
Name	Varchar		The consent status name.

The Contact Point Address data model object (DMO) is a Data 360DMObased on the mailing address of a party. Provided in both the Sales and Service DataKits.

Columns

Name	Type	References	Description
Active From Date	Datetime		The date from which the contact’s address is active.
Active To Date	Datetime		The date the contact’s address becomes inactive.
Address	Varchar		A reference ID for the contact’s address.
Address Line 1	Varchar		The first line of the address, including the street name andnumber.
Address Line 2	Varchar		The second line of an address, for example, a suitenumber.
Address Line 3	Varchar		The third line of an address.
Address Line 4	Varchar		The fourth line of an address.
Best Time To Contact End Time	Datetime		A contact’s preferred date and time to stop receivingcommunication.
Best Time To Contact Start Time	Datetime		A contact’s preferred date and time to start receivingcommunication.
Best Time to Contact Timezone	Varchar		A reference ID to the contact’s preferred time zone.
City	Varchar		The reference ID of a city, town, or village.
City Name	Varchar		The name of a city, town, or village.
Contact Point Address ID	Varchar		A unique ID used as the primary key for the contact point addressDMO.
Contact Point Type	Varchar		A reference ID to the contact type.
Country	Varchar		The reference ID of the country where the address is located.
Country Name	Varchar		The country where the address is located.
Country Region	Varchar		A reference ID for the country or region where the address islocated.
Created Date	Datetime		The record’s creation date.
Data Source	Varchar		A reference ID for the logical name of a system that is thesource of records identified byanexternal record ID.
Data Source Object	Varchar		A reference ID for the logical name of the object where thisrecord came from, for example, a name of a cloud storage file oranother connector’s external object.
External Record ID	Varchar		A reference ID for an external data source system.
External Source ID	Varchar		A reference ID for the system in which the external record ID wasassigned.
For Business Use	Varchar		Whether the address is for business use.
For Personal Use	Varchar		Whether the address is for personal use.
Geo Accuracy	Double		Additional information about an address’ latitude andlongitude.
Geocode Accuracy Type	Double		The geographic code accuracy type for the contact point.
Geo Latitude	Double		The address’ geographical latitude.
Geo Longitude	Double		The address’ geographical longitude.
Internal Organization	Varchar		A reference ID for the business unit or other internalorganization that owns the business account.
Is Active	Varchar		Whether the address is active.
Is Undeliverable	Varchar		Whether the address is undeliverable.
Is Used For Billing	Varchar		Whether the address is the contact’s billing address.
Is Used For Mailing	Varchar		Whether the contact point address can be used formailing.
Is Used For Shipping	Varchar		Whether the address is the contact’s shipping address.
Last Modified Date	Datetime		The date when a user last modified the record.
Party	Varchar		A reference ID to the parent party, for example, an individual,business, or affiliation group. This ID is the same as the oneused in the individual object.
Party Role	Varchar		A reference ID to the associated party role, for example, acustomer, supplier, or competitor.
Physical Location	Varchar		A reference ID to the physical location of the address.
Postal Code	Varchar		The reference ID for the postal code.
Postal Code text	Varchar		The contact’s postal code.
Preference Rank	Double		The preference rank number for the contact point address.
Primary Contact Phone	Varchar		A reference ID for the primary phone number for thisaddress.
Primary Flag	Varchar		Indicates whether the address is the primary address of theindividual or account.
Profile First Created Date	Datetime		The date the profile was created.
Profile Last Updated Date	Datetime		The date the profile was last updated.
Profile Occurrence Count	Double		The number of occurrences of the profile for the contact pointaddress.
State Province	Varchar		The reference ID for the state or province where the address islocated.
State Province Name	Varchar		The state or province where the address is located.
Timezone Code	Varchar		A single-character code indicating the time zone’s relationshipto Greenwich Mean Time (GMT).
Usage Type	Varchar		The usage type for the contact point address.