ETL Extraction using Zero Copy Connector for ERP

Enevoldsen · 6 hours ago

ETL Extraction pulls large ERP data volumes into ServiceNow on a schedule and persist them in Glide tables. Use them when you need searchable, reportable ERP data without hitting the ERP system every query.

Three Read Patterns

Remote tables/Flow Designer Actions query ERP live on demand. No copy in ServiceNow. Use for critical validation (cost center exists now, has budget etc.)
Data Fabric tables provide real-time access through the WDF semantic layer. No persistent copy. Use when you need WDF semantic mapping.
ETL Extraction stores a persistent copy in ServiceNow. ETL runs on schedule. Queries hit ServiceNow, not ERP. Tradeoff: instant performance and search at the cost of freshness (data is as old as last extraction).

When to Use

Dashboards and reports at scale. Extract nightly, query locally without ERP latency.

Searchable lists over large datasets. Procurement finds open POs instantly. No ERP load except during extraction window.
Searchable master data. Finance searches cost centers. Procurement finds vendors. 24-hour freshness acceptable.

Reduce ERP load. Analytics with transaction history. Extract months of transactions nightly, query for reports instead of hitting ERP.

Setting Up an Extraction Table

The flow for creating an extraction table has four steps.

Start with a ZCC for ERP Model that connects to your ERP entity (BAPI, OData service, or SAP table). The model defines what data you're extracting. If you don't have a model yet, create one in Model Manager and test the Read operation to confirm it connects to your ERP system.

Then follow the ServiceNow extraction configuration process:

Add a new extraction table. Choose your source model and target ServiceNow table. See Add a new Extraction table.
Select the fields from your model that you want to extract. Not every field in the model needs to be extracted. Choose the ones your use case needs. See Select fields for an extraction.
Create a table transform map to specify how model fields map to your target ServiceNow table columns. This is where you handle field naming, data type conversions, and any transformations. See Create a table transform map.
Create a scheduled extraction. Set the extraction frequency (daily, hourly, etc.), choose full load or delta load, configure retry and monitoring. See Create a scheduled extraction.

Once configured, run a manual extraction to verify the data loads correctly. Then the extraction runs on schedule automatically.

How Extraction Actually Works

When an extraction runs, the system executes this flow:

MID Server receives the job request and authenticates to your ERP system using the connection details stored in the ZCC for ERP system config.
MID Server executes the Read operation from your ZCC for ERP Model.
The ERP system returns data using pagination
ServiceNow applies any transformations or field mappings you defined.
Data is written to the target Glide table. Existing records are updated if the extraction runs again; new records are inserted.
ServiceNow logs the extraction: timestamp, rows processed, duration, status (success or failure).

Master Data vs Transaction Data: Data Types and Extraction Fit

The type of data you're extracting matters. Master data and transaction data have fundamentally different characteristics and different extraction requirements. Master data is reference information that rarely changes: Cost Centers, Company Codes, Vendors, Materials, Customers, Employees, Chart of Accounts.

Master data is ideal for extraction tables. Extract nightly or even weekly. The data is stable enough that daily or weekly freshness is acceptable. Your ServiceNow users get instant searches and list views without any real-time query overhead. This is the cleanest extraction use case.

Transaction data is operational information that changes constantly: Purchase Orders, Sales Orders, Invoices, Material Movements, Production Orders. A Purchase Order might go from "Open" to "Received" to "Invoiced" in just a day. An invoice might be posted and then updated hours later. Transaction data changes at high frequency.

Transaction data extraction depends on freshness requirements. If your dashboard reports on invoices from the prior month or year, nightly extraction works fine. The data is historical and doesn't change after posting. If your approval workflow needs to see open POs that were just created that morning, hourly extraction might be necessary. But if you need to know the current status of an order right now (is it received yet? has it been invoiced?) extraction tables are the wrong tool. Use a remote table query or Flow designer Action instead. The freshness cost of extraction outweighs the performance benefit.

The decision point is simple: ask what freshness your use case actually needs.

Mixed scenario: you need both. Extract historical transaction data nightly for reporting. Use remote tables for live lookups on current transactions. This is common: your dashboard shows closed invoices from extraction tables, but your payment approval workflow validates current invoice status with a live remote table query.

Indexing Extraction Tables

ETL Extraction stores data just like any other ServiceNow table. The difference is volume: you're often extracting thousands of records. Without proper indexes, list views and searches become slow.

When you create an extraction table, ServiceNow automatically indexes the primary key field (usually the ERP record ID: the PO number, Invoice number, Cost Center code). But that's not enough. You need to index the fields that users will actually search and filter on.

This matters more as your extraction grows. A small extraction of 5,000 records might be fast even without indexes. A million-record extraction will be unusable without them.

Data Coalescing in Extractions

Coalescing in extraction context means consolidating data properly so duplicate records don't accumulate. It's about ensuring that when you extract the same data repeatedly, you update existing records instead of creating new ones.

When you extract the same dataset multiple times over weeks, you don't want duplicate records accumulating in your ServiceNow table. On the first extraction, you load 10,000 vendors. On the second extraction the next night, you load 10,000 vendors again. The coalescing logic determines whether this creates 20,000 records or updates the existing 10,000.

ServiceNow handles this through a reconciliation key. You specify which field(s) uniquely identify a record in your extraction: the Vendor ID, PO Number, Cost Center Code, Material Number. During extraction, ServiceNow matches incoming records against existing records using this key. If a record with that key already exists, the record is updated with new values. If it's new, it's inserted.

Without proper reconciliation, you'll accumulate duplicate records. After 30 days of nightly extractions, your vendor table has 300,000 records instead of 10,000. Users see duplicates in lists. Dashboards double-count data. This breaks everything.

Delta load extraction compounds coalescing importance. If you're only extracting changed records and using delta load, you're relying on the reconciliation key to match the changed record to the existing record and update it in place. If the reconciliation key is wrong or missing, delta load creates duplicates or orphaned records.

Access Control Lists (ACLs) on Extraction Tables

When you extract ERP data into ServiceNow, you're making that data visible to ServiceNow users. Without proper access control, every user in your ServiceNow instance can read, create, update, or delete records in your extraction table. That's a security problem.

ERP data is often sensitive. Vendor pricing, cost center budgets, supplier payment terms. You can't expose this to all ServiceNow users. You need ACLs (Access Control Lists) on the extraction table to control who can see what.

Extraction Tables Must Remain Read-Only: System of Record

This is critical: extraction tables are copies. Do not use them for inserts or updates. Do not let users modify records in extraction tables. This breaks the system of record contract with your ERP system and corrupts your data.

Here's why. The ERP system is the system of record. SAP is the source of truth for vendors, cost centers, purchase orders, everything you extract. When you extract that data into ServiceNow, you're creating a copy for searching and reporting. That copy should never be modified in ServiceNow. Changes belong in the ERP system, and the extraction job keeps the copy in sync.

Document this architectural principle clearly. Include it in your ZCC for ERP implementation guide and in user training. Users should understand that extraction tables are read-only copies of ERP data, not standalone ServiceNow tables they can modify.

Summary

Extraction tables excel at scale: dashboards, searchable lists, reference data, analytics.

Real-time patterns for validation and small datasets. Data Fabric for WDF semantic consistency. Use all three where each wins.