Loading ERP and e-shop data into a cloud data warehouse with Boomi Data Integration

Management wants one sales dashboard: invoices from the ERP, orders and carts from the e-shop, refreshed every morning. Today someone builds it by hand in a spreadsheet. This article explains the pattern that replaces that work, then builds it with Boomi Data Integration, the ELT service Boomi gained when it acquired Rivery in 2024, loading into a cloud data warehouse such as Snowflake or Google BigQuery.

Why the direct approach fails

The tempting shortcuts are to point the BI tool straight at the ERP database, or to write one big script that extracts, cleans and joins everything before loading the result. Both break down:

Querying the ERP directly puts analytical load on the system that issues invoices, usually during working hours. Joining it with e-shop data in the same query is not possible anyway.
Transform-then-load scripts throw away the raw data. When the definition of “net sales” changes, you have to re-extract months of history.
Hand-written extracts handle the happy path. Schema changes, API limits, retries and alerts are left for later, and later arrives on the morning of the board meeting.

The pattern: land raw, transform in the warehouse

The ELT pattern splits the work into layers:

ERP (SQL Server) ─┐
                  ├──► raw layer (copies of source tables) ──► SQL models (clean, joined) ──► dashboard
E-shop (API) ─────┘

Extract and load each source into a raw schema in the warehouse, as close to the source as possible. One pipeline per source, scheduled.
Transform inside the warehouse with SQL: clean types, join ERP customers with e-shop customers, calculate metrics. Results go into an analytics schema.
Orchestrate so transformations run only after all loads have succeeded.

The raw layer is your safety net: any model can be rebuilt from it without touching the sources again.

Building it in Boomi Data Integration

In Boomi Data Integration a pipeline is called a river. The two kinds we use here are source-to-target rivers, which copy data from a source into a target, and logic rivers, which run a sequence of steps such as SQL queries and other rivers. The service runs in the cloud; exact labels and options can change between releases, so check the current documentation as you go.

Step 1: Prepare the warehouse

Create a database (Snowflake) or project and datasets (BigQuery) with two schemas, for example raw and analytics. Create a dedicated service user for Boomi Data Integration with rights to create and write tables in raw only, and to run queries in analytics. Don’t reuse a personal account.

Step 2: Create the connections

Create a connection for each system:

Target: Snowflake or BigQuery, using the service user (or a service account key for BigQuery).
ERP: many ERPs used by Greek companies run on Microsoft SQL Server, so a database connection with a read-only user is usually the cleanest route. If the database is on-premises, you will need to allow access from the service, for example through an SSH tunnel or allowed IP addresses; ask your IT team which option fits your network.
E-shop: use a ready-made connector if one exists for your platform. If not, Boomi Data Integration can call a REST API through a custom connection definition; check what the current version offers for custom REST sources.

Step 3: Build the ERP river

Create a source-to-target river with the SQL Server connection as source and the warehouse as target.

Select the tables you need, not the whole database: for example customers, items, invoice headers and invoice lines.
For each table, set the target table name in the raw schema, for example raw.erp_invoice_lines.
Choose the loading mode. For small reference tables (items, branches), overwriting each run is simplest. For large tables, use an upsert/merge mode on the primary key, so rows are updated rather than duplicated.
For the first version, extract all rows. Switch large tables to incremental extraction once the pipeline works; see Incremental loads and CDC with Boomi Data Integration.

Run it once manually and compare row counts with the ERP.

Step 4: Build the e-shop river

Create a second river for orders and order lines from the e-shop. API sources return nested JSON (an order contains lines, a shipping address, discounts). Two common options are to load the JSON as-is into a semi-structured column and flatten it in SQL, or to let the river split it into separate tables. Landing it raw is more robust to changes in the e-shop’s API.

Step 5: Transform in the warehouse

Write the business logic as SQL views or tables in analytics. For example, a daily sales model that combines both channels:

create or replace table analytics.daily_sales as
select invoice_date::date as day, 'ERP' as channel, sum(net_amount) as net_sales
from raw.erp_invoice_lines
group by 1
union all
select created_at::date as day, 'E-shop' as channel, sum(subtotal - discount_total) as net_sales
from raw.eshop_orders
where status in ('completed', 'shipped')
group by 1;

Keep this SQL in version control. As the number of models grows, a dedicated transformation tool such as dbt is worth considering; Boomi Data Integration can also run SQL steps itself, which is enough to start.

Step 6: Orchestrate and schedule

Create a logic river that runs, in order:

the ERP river and the e-shop river (they can run in parallel),
the SQL step that rebuilds analytics.daily_sales,
optionally, a data quality query that fails the run if, for example, yesterday has zero sales.

Schedule the logic river, for example daily at 05:30 Athens time, so the dashboard is fresh at the start of the day. Pay attention to the time zone of the schedule setting. Configure failure notifications to go to a shared mailbox or team channel, not to one person.

Before you go live

Reconcile with the source. Compare monthly totals in the warehouse with the ERP’s own reports for a few months before anybody trusts the dashboard.
Least privilege everywhere. Read-only ERP user, target user limited to its schemas.
Personal data. E-shop customers are personal data. Load only the columns you need for analysis, and restrict access to raw tables.
Watch costs. Warehouse compute and data volume both cost money. Schedule heavy transformations once a day rather than every few minutes unless you need fresher data.
Document ownership. Write down who owns each river and each model, and who gets paged when it fails. See also Monitoring and alerts for integrations in Boomi.

Summary

The ELT pattern keeps extraction simple and puts business logic in one place, the warehouse, where it can be versioned, tested and rebuilt from raw data. In Boomi Data Integration that means source-to-target rivers per source, SQL for the models and a logic river to run everything in order. For the bigger picture, see our Data integration, ETL and ELT: a practical guide.