# Data Platform for ERP Data: Replica, Warehouse or Lakehouse

> Source: https://netray.co/erp-help/cto-data-platform-for-erp-data-lakehouse-vs-replica
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## Short answer

Start with the smallest thing that removes reporting load from the ERP: a read replica or vendor replication feed. Add a warehouse or lakehouse when you must join ERP data with other sources, keep history, or serve many consumers. Put a governed semantic layer on top, because raw ERP tables are not a usable analytics or AI interface on their own.

## Kind

cio

## System

it-leadership

## Erp

ERP data platform (vendor-neutral)

## Module

Data Architecture

## Query

best data platform for ERP data warehouse lakehouse or read replica

## Alternate Queries

- how to build a data warehouse from ERP data
- ERP reporting database vs data lake
- should we replicate ERP data to Snowflake or Databricks
- ERP data platform architecture for analytics and AI

## Applies To

Any ERP with a relational database or vendor export: SyteLine and Dynamics on SQL Server, SAP S/4HANA on HANA, Oracle EBS, NetSuite via SuiteAnalytics Connect, M3 and LN via data lake or replication.

## Steps

### How to choose and build the ERP data platform

**Items:**

Write down the consumers: finance reporting, operations dashboards, planning models, data science, and AI assistants. Note each one's freshness need and whether it joins ERP data with other systems.

If consumers only need ERP data with minutes of lag, begin with a read replica or vendor reporting feed. Confirm the vendor supports it (for example SQL Server Always On readable secondary, or SuiteAnalytics Connect).

Add a warehouse or lakehouse only when you need cross-system joins, long history, or more than a few consumers. Pick one that your team can operate or buy as a service.

Capture changes with a supported method: vendor replication, log-based CDC, or incremental extracts on reliable last-modified columns. Avoid full reloads of large tables.

Model in layers: raw (as extracted), cleaned and conformed (typed, deduplicated, keys resolved), and a business layer (customers, items, orders, inventory, WIP) with documented definitions.

Handle deletes and history explicitly. Many ERPs soft-delete or purge, so decide how to represent deleted rows and how to keep slowly changing dimensions.

Apply access control at the platform: restrict by role and, for regulated data, by row or column, mirroring ERP security rather than relying on a single service account.

Monitor freshness, row counts and schema changes per table, and publish a data dictionary that maps ERP field names to business terms.

## Sections

### Three tiers and when each is enough

**Body:**

A read replica or vendor reporting feed is the lowest-effort tier. It keeps heavy queries off the transactional database and gives analysts SQL access to near-current data. It does not give you history beyond what the ERP keeps, cross-system joins, or a stable schema, so the first custom field or upgrade can break downstream reports.

A warehouse, whether cloud (Snowflake, BigQuery, Synapse, Redshift) or on-prem (PostgreSQL, SQL Server, ClickHouse), adds a modelled, stable layer. You decouple consumers from ERP schema changes, join ERP data with CRM or MES data, and keep history. It needs an ingestion pipeline, modelling discipline and an owner.

A lakehouse (Databricks, Delta Lake, Iceberg on object storage) is justified when you also hold unstructured data, such as PDFs, drawings and quality documents, or run machine learning at scale. For a mid-sized manufacturer whose questions are all tabular, a warehouse is simpler and usually sufficient.

### Getting data out of the ERP without hurting it

**Body:**

Prefer vendor-supported paths. NetSuite offers SuiteAnalytics Connect over ODBC, JDBC or ADO.NET for read access. SAP landscapes use CDS-view based extraction, SLT replication or Datasphere. Infor customers can use Infor Data Lake through ION. On-prem SQL Server ERPs can use a readable secondary or transactional replication. Check licensing, because some vendor feeds are separately priced.

Log-based CDC (Debezium reading the transaction log, or database-native change tracking) captures inserts, updates and deletes with low load. Incremental extracts keyed on a last-modified column are simpler but miss hard deletes and fail when an ERP does not update that column consistently, which is common for some child tables.

Schedule heavy extracts away from period close and MRP runs. A badly timed full extract of inventory transactions during month-end close is a classic cause of user complaints about a slow ERP.

**Code:**

-- SQL Server: confirm a readable secondary for reporting
SELECT ag.name, ar.replica_server_name, ar.secondary_role_allow_connections_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id;

### Modelling and governance matter more than the engine

**Body:**

Raw ERP tables carry cryptic names, status codes and custom fields. Business users and AI assistants both fail on them. Build a business layer with named entities (customer, item, work order, purchase order line, inventory by location) and document each measure, such as how on-hand excludes quarantined stock or how late is defined for an order line.

Make definitions single and testable. Add automated tests for row counts against the ERP, null keys, orphan lines and reconciliation of totals such as inventory value against the general ledger. A platform that is fast but disagrees with finance loses trust within one close cycle.

Governance should mirror the ERP. If a plant controller sees only their site in the ERP, they should see only that site in the platform. Export-controlled or customer-restricted data needs row-level filters and an audit trail, and for defense work the platform may need to stay in a controlled environment.

## Pitfalls

- Replicating every table. Start with the entities you need, or storage, cost and governance scope balloon with little value.
- Using incremental extracts without handling deletes. Deleted or cancelled rows linger in the warehouse and inflate totals.
- Pointing BI tools at raw ERP tables. Every ERP upgrade or custom field becomes a reporting incident.
- One shared service account for all consumers. You lose auditability and cannot mirror ERP row or site restrictions.
- Skipping reconciliation to the general ledger. Numbers that disagree with finance destroy trust in the platform.
- Choosing a lakehouse for tabular-only needs. The extra moving parts add operating cost without added capability.

## Ai Angle

An AI assistant needs the same thing a human analyst does: stable, named, governed data. ERPray can query the ERP directly in read-only mode or a replica of it, using the semantic definitions you provide, and shows the SQL it ran. DataRay covers adjacent sources such as file shares and documents on-prem so one assistant can answer across ERP rows and the PDFs around them.

## Faq

### Do we need a data warehouse if the ERP has built-in reporting?

**Answer:** Not always. If your questions stay within one ERP module and tolerate its report designers, built-in reporting may be enough. A warehouse earns its cost when you join ERP data with other systems, need history the ERP purges, or want several consumers to share one set of definitions.

### Snowflake, Databricks or PostgreSQL for ERP data?

**Answer:** It depends on scale, skills and data residency. A tabular manufacturer with modest volume can run PostgreSQL or SQL Server comfortably. Cloud warehouses add elastic scale and managed operations. Choose a lakehouse if you also need unstructured documents or large ML workloads. Residency or air-gap needs may rule out public cloud.

### How fresh can ERP data be in the platform?

**Answer:** With log-based CDC or vendor replication, minutes is typical. With nightly extracts, 24 hours. Choose per consumer: finance close reports tolerate a day, shop-floor dashboards usually need minutes, and a live ATP check should call the ERP directly.

### Does copying ERP data create a compliance problem?

**Answer:** It can. Replicated data inherits the sensitivity of the source, including personal data, pricing and export-controlled technical data. Apply the same access control, retention and location rules to the copy, and keep the platform inside the boundary your regulations require.

## Search terms

ERP data warehouse, ERP data lakehouse, ERP read replica reporting, ERP data platform architecture, ERP CDC to Snowflake, SuiteAnalytics Connect, ERP analytics data model, ERP data for AI, ERP reporting database, replicate ERP data

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json