System tables

Query connectors that use CData data models allow you to query system tables in Advanced mode.

You can see the available system tables in the corresponding Query connector data model. For example, the Salesforce Query data model includes these system tables.

Querying system tables can help in various use cases, for example:

• Managing schema drift: Let's say you have a table called Accounts that contains account data from Facebook Ads. You can use the Facebook Ads Query component to query system tables in order to store a list of all columns in this table in Snowflake. This gives you a view of your Accounts table schema at this point in time. You can then programmatically compare this to the schema of your Accounts table in the event of schema drift. This in turn enables you to programmatically manage schema drift.
• Building metadata-driven pipelines: Let's say you want to run the same pipeline for 20 different Salesforce tables. You can use the Salesforce Query component to query system tables in order to store a list of all the columns in these Salesforce tables in Snowflake. You can then use a Query Result to Grid component to obtain all these columns, and then use this grid variable in the Data Selection property of a Salesforce component. This means you don't have to build the same pipeline repeatedly for each of your Salesforce tables.

---

Primary keys for incremental load

To perform an incremental load update or merge, you need one or more primary keys.

To obtain the primary keys, query the sys_tablecolumns system table to obtain the IsKey boolean.