Erp Master Data Management

Production engineering guide for erp master data management covering patterns, implementation strategies, and operational best practices.

Master data in enterprise resource planning systems is the foundation of consistent, reliable operations. ERP Master Data Management ensures that core entities—such as customers, vendors, products, and locations—are defined once, validated, and synchronized across all integrated business processes. It matters when data inconsistency causes transaction errors, reporting discrepancies, or failed integrations. This page details the practical engineering discipline of managing master data across ERP systems, from ingestion and validation to governance and lifecycle management.

Ingesting Master Data

Master data ingestion begins with data from external sources: CSV files, API responses, or database dumps. The canonical pattern uses a staging table where raw data is loaded, then transformed and validated before being imported into the ERP’s authoritative master data tables.

A typical ingestion pipeline uses curl to fetch data from a data lake, followed by psql to load into a staging schema:

curl -s https://data-lake.example.com/datasets/customers.csv \
  | psql -h erp-db.example.com -U erp_user -d erp -c "
    COPY staging.customers_raw FROM STDIN WITH (FORMAT csv, HEADER true, DELIMITER ',', NULL 'null');
  "

The COPY command is preferred over INSERT for bulk loads due to its minimal logging overhead and direct file-to-table streaming. After loading, data is validated using SQL queries that check for required fields, referential integrity, and business rules:

SELECT customer_id, name, country_code
FROM staging.customers_raw
WHERE country_code NOT IN (SELECT code FROM master.countries)
  AND name IS NULL
  AND created_date > CURRENT_DATE;

The result is a report of validation failures, which are logged to a master.data_validation_issues table. Each issue includes a severity (critical, warning, info), description, and source_record_id.

Validation errors are not just logged—they trigger alerts via systemctl-managed daemons. A validate-master-data.service runs every 30 minutes:

[Unit]
Description=Validate master data from staging
After=network.target

[Service]
Type=oneshot
ExecStart=/opt/erp/bin/validate-master-data.sh
StandardOutput=journal
StandardError=journal
Environment=LOG_LEVEL=info

[Install]
WantedBy=timers.target

The validate-master-data.sh script runs the above SQL, sends a summary email via mailx, and logs to /var/log/erp/master-data-validate.log.

Standardizing and Cleansing Data

Once ingested, data must be standardized—ensuring consistent formats, codes, and terminology. This step resolves differences like "USA" vs. "United States" or "1001" vs. "0001001" for customer IDs.

Standardization is driven by a master.data_standardization_rules table with columns: rule_id, field_name, source_pattern, target_value, priority, and applies_to. A transformation script uses psql to apply these rules:

WITH standardized AS (
  SELECT 
    customer_id,
    CASE 
      WHEN name ~* 'usa|united states' THEN 'US'
      WHEN name ~* 'uk|united kingdom' THEN 'GB'
      ELSE UPPER(SUBSTRING(name FROM 1 FOR 2))
    END AS country_code,
    TRIM(UPPER(name)) AS name_upper,
    LPAD(customer_id::text, 8, '0') AS customer_id_padded
  FROM staging.customers_raw
)
UPDATE staging.customers_standardized
SET country_code = s.country_code,
    name = s.name_upper,
    customer_id = s.customer_id_padded
FROM standardized s
WHERE staging.customers_standardized.customer_id = s.customer_id;

The TRIM and UPPER functions are applied to ensure case-insensitive matching. The LPAD function pads customer IDs to eight digits, critical for downstream systems expecting fixed-length identifiers.

A common failure mode is rule ambiguity: multiple rules apply to the same input. For example, both "USA" and "US" match the pattern ^us.*$, but the first rule in the table takes precedence. A priority column ensures correct ordering, and a merge_strategy flag controls whether rules are applied sequentially or in parallel.

Cleansing is also driven by deduplication. The master.duplicate_candidates table holds pairs of potential duplicates, scored by Levenshtein distance and exact match on key fields:

INSERT INTO master.duplicate_candidates (id1, id2, score, reason)
SELECT
  a.customer_id,
  b.customer_id,
  0.8 * levenshtein(a.name, b.name) +
    0.2 * (CASE WHEN a.country_code = b.country_code THEN 1 ELSE 0 END) AS score,
  'Name similarity and country match'
FROM staging.customers_standardized a
CROSS JOIN staging.customers_standardized b
WHERE a.customer_id < b.customer_id
  AND a.name ILIKE b.name
  AND a.country_code = b.country_code;

The levenshtein function computes edit distance between strings. A score above 0.75 indicates a likely duplicate. These candidates are reviewed manually or automatically via a deduplicate-master-data CLI script:

./deduplicate-master-data --threshold 0.75 --batch-size 100 --dry-run --output /tmp/duplicates.json

The --dry-run flag shows which records would be merged without updating the database. The --batch-size option controls how many pairs are processed per worker. The output is a JSON array of merge actions, each with merge_into, merge_from, and conflict_resolution.

Validating and Approving Master Data

After cleansing, data must be validated and approved before going live. This involves a workflow where changes are reviewed and approved by domain experts.

A master.data_approval_workflow table defines the approval stages: draft, review, approved, rejected. Each master data record has a status and approver_id.

An approval process is initiated by a start-approval script:

curl -X POST https://api.erp.example.com/v1/master-data/approvals \
  -H "Content-Type: application/json" \
  -d '{
    "entity_type": "customer",
    "record_id": "CUST-1005",
    "initiator": "[email protected]",
    "comments": "New customer from acquisition, needs approval from finance team."
  }'

The response returns a workflow_id and status: pending.

The approval-service polls the /v1/master-data/approvals endpoint every minute:

import requests
import time

while True:
    response = requests.get("https://api.erp.example.com/v1/master-data/approvals?status=pending")
    for approval in response.json():
        workflow_id = approval["workflow_id"]
        record_id = approval["record_id"]
        entity_type = approval["entity_type"]

        # Fetch the record
        record = requests.get(f"https://api.erp.example.com/v1/master-data/{entity_type}/{record_id}").json()

        # Send email
        requests.post("https://mail.example.com/send", json={
            "to": approval["approver"],
            "subject": f"Approve {entity_type} {record_id}",
            "body": f"Review {record['name']} in {entity_type} master data.\n\n{record['description']}"
        })

    time.sleep(60)

Approvals are stored in master.approval_events with action, timestamp, and comment. When an approval is submitted, the system updates the status and triggers a data-sync event to propagate the change.

Synchronizing Master Data

Once approved, master data must be synchronized across integrated systems. The canonical approach uses change data capture (CDC) via pg_logical in PostgreSQL to track changes to master tables.

A publication is created on the master data tables:

CREATE PUBLICATION erp_master_data_pub
  FOR TABLE master.customers,
                     master.vendors,
                     master.products,
                     master.locations;

A subscriber system—such as a data warehouse or a middleware—uses pg_recvlogical to stream changes:

pg_recvlogical \
  --start \
  --slot-name erp_master_data_slot \
  --dbname erp \
  --host erp-db.example.com \
  --dbname erp \
  --slot erp_master_data_slot \
  --output json \
  --start-timeline 123 \
  --start-timestamp '2024-01-01 00:00:00' \
  --filter-by-table master.customers \
  --filter-by-table master.products

The output is a sequence of JSON messages, each describing a change_type, table_name, old_values, and new_values. Each change is processed by a sync-master-data service that updates target systems via REST APIs.

A common failure is missing old_values in the JSON stream. When a record is updated but old_values is not included, the target system cannot detect changes correctly. This leads to stale data or duplicate records.

The synchronization process includes idempotency: each change is processed only once. A synced_events table tracks event_id, event_type, and processed_at. The service uses a SELECT FOR UPDATE lock to avoid race conditions:

SELECT id, payload
FROM sync_events
WHERE processed_at IS NULL
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 100;

This ensures that multiple sync workers do not process the same event twice.

Governing Master Data

Governance ensures that master data remains accurate and up-to-date over time. It includes defining data ownership, setting validation rules, and monitoring data quality.

A master.data_governance table defines roles and responsibilities:

entity_typeowner_roleapproval_requiredlast_updatedvalidation_rules
customerfinancetrue2024-03-15[101, 103, 105]
productinventoryfalse2024-03-10[102, 104]

The validation_rules column stores an array of rule IDs. A governance-dashboard service queries this table and generates a health report:

SELECT
  entity_type,
  COUNT(*) AS total_records,
  COUNT(CASE WHEN status = 'approved' THEN 1 END) AS approved,
  AVG(CASE WHEN last_updated < CURRENT_DATE - INTERVAL '30 days' THEN 0 ELSE 1 END) AS recent_data
FROM master.data_governance
GROUP BY entity_type;

This report is used to identify entities with stale data, triggering renewal workflows.

The final sharp edge is the failure mode of orphaned records: a customer exists in the ERP but not in the CRM. This is detected by a check-orphaned-records script that compares master.customers with crm.customers:

SELECT c.customer_id, c.name
FROM master.customers c
LEFT JOIN crm.customers cr ON c.customer_id = cr.customer_id
WHERE cr.customer_id IS NULL;

The script runs daily and logs results to /var/log/erp/orphaned-customers.log. When more than 5% of customers are orphaned, it triggers a master-data-health-check alert.

Managing Master Data Lifecycle

Master data evolves over time. A record may be created, updated, archived, and eventually retired.

The master.data_lifecycle table tracks each state change:

record_identity_typestateeffective_fromeffective_toarchived_by
CUST-1001customeractive2024-01-01NULL[email protected]
CUST-1002customerinactive2024-03-012024-08-15[email protected]

A archive-inactive-data service runs monthly:

./archive-inactive-data --since '2024-01-01' --target 'archive-db' --batch-size 500

It sets effective_to on all inactive records, moves them to an archive schema, and updates the state in the master table. If effective_to is NULL, the record is currently active.

A silent failure occurs when a record is archived but not updated in the master.data_lifecycle table. This leads to incorrect reporting and inconsistent data across systems. The service logs both success and failure counts, and sends alerts when more than 10% of records fail to archive.

The entire master data system is monitored via systemctl and journalctl. The master-data-sync.service logs to journal, and journalctl -u master-data-sync.service --since '2024-03-15' shows all events since the last major deployment.

This page was rewritten on 18 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.