Ctrlk
For the complete documentation index, see llms.txt. This page is also available as Markdown.

Storage

Setup Object Stores & SMB ..

Object Stores

Object storage systems like Amazon S3 and MinIO provide a way to store and retrieve large amounts of unstructured data such as files, images, videos, and backups through a simple web-based API. Unlike traditional file systems that organize data in hierarchical folders, object stores use a flat namespace where each piece of data (called an object) is stored in containers called buckets and accessed via unique keys or URLs.

Amazon S3 is AWS's flagship object storage service that offers virtually unlimited scalability, multiple storage classes for different use cases, and integration with other AWS services.

MinIO is an open-source alternative that provides S3-compatible APIs and can be deployed on-premises or in private clouds, making it popular for organizations that want object storage capabilities without vendor lock-in.

Both systems are designed for high durability, availability, and can handle massive scale while providing simple REST API access for applications to store and retrieve data programmatically.

Storage

The following steps are intended for setting up a Pentaho Lab environment and need to be completed in order to complete the Workshops.

Ensure you have downloaded the Workshop--Installation:

To install git:

Prerequisites

  • Ubuntu 24.04 LTS system (physical or virtual machine)

  • User account with sudo privileges

  • Internet connection

  • Basic familiarity with Linux command line

MinIO

Follow the instructions below to setup a MinIO Docker Container.

Select your OS & add the Sample Data, finally configure a VFS connection in Data Integration:

Installs and configures MinIO on Ubuntu 24.04 running in Docker.

  1. Create a MinIO folder and copy the required files.

Create directory & copy

copy-minio.sh

  1. Ensure all the files have successfully been copied over.

  1. Execute the docker-compose script to create the container.

MinIO Container

run-docker-minio.sh

Following best industry practices, MinIO is installed as root in the /opt/minio directory. If you wish the pentaho user to also manage the service then you may need to add the user to the Docker group.

  1. Check the container is up and running in Docker.

Check Docker minio container

  1. Log into MinIO.

http://localhost:9002/loginlocalhost

Username: minioadmin

Password: minioadmin

If you have completed the setup: MinIO then you should have pre-populated buckets with various data objects in different formats.

New Bucket

If you need to create a Bucket:

  1. Click the 'Create Bucket' link.

  2. Enter: sales-data & 'Create Bucket'.

Create Bucket.

  1. Click on the Upload button.

Upload sales_data.csv

  1. Upload your data - for example some sales data:

Windows - PowerShell

Linux

Workshops

Pentaho Data Integration: MinIO Object Storage Workshop Series

Modern organizations increasingly store their data in cloud-native object storage systems like MinIO and Amazon S3, moving away from traditional file servers and databases. This architectural shift enables scalable, cost-effective data lakes but introduces new challenges: data arrives in multiple formats (CSV, JSON, XML, Parquet), exists across distributed buckets, and requires sophisticated transformation pipelines to unlock its analytical value. Learning to efficiently extract, transform, and integrate data from object storage is now essential for any data integration professional working with contemporary data architectures.

In this comprehensive workshop series, you'll build progressively complex transformation pipelines that leverage MinIO object storage as both a source and destination for enterprise data integration scenarios. Starting with fundamental ETL patterns like denormalized fact table creation, you'll advance through intermediate challenges involving multi-format parsing and reconciliation, ultimately mastering advanced techniques like sessionization, anomaly detection, and schema normalization across heterogeneous data sources.

Each workshop introduces real-world business scenarios - from sales dashboards to customer analytics to operational monitoring - demonstrating PDI's versatility in solving diverse integration challenges while maintaining cloud-native architecture principles.

What You'll Accomplish:

  • Configure VFS (Virtual File System) connections to access S3-compatible MinIO object storage

  • Build multi-source ETL pipelines using Text File Input steps with S3 paths (s3a://)

  • Implement Stream Lookup and Merge Join patterns to enrich data from multiple CSV sources

  • Parse semi-structured formats including XML inventory feeds and JSONL event streams

  • Apply full outer joins to identify discrepancies between warehouse and catalog systems

  • Aggregate customer data across transactional, demographic, and behavioral dimensions

  • Perform sessionization and funnel analysis on clickstream data using Group By and pivoting

  • Extract structured data from unstructured logs using Regular Expression evaluation

  • Detect anomalies in time-series data through rolling averages and conditional logic

  • Normalize schemas across CSV, JSON, and XML sources into unified data lake structures

  • Implement data validation, deduplication, and quality controls for multi-format ingestion

  • Calculate derived metrics including customer lifetime value, engagement scores, and conversion rates

  • Route data dynamically using Switch/Case and Filter Rows for conditional processing

  • Output transformed data to staging and curated layers following data lake architecture patterns

By the end of this workshop series, you'll have mastered the complete spectrum of cloud-native data integration patterns using Pentaho Data Integration. You'll understand how to handle diverse source formats, implement sophisticated join and aggregation logic, perform advanced text parsing and time-series analysis, and build production-ready pipelines that leverage object storage for scalable, distributed data processing.

Instead of treating each data format as a unique challenge requiring custom scripts, you'll confidently design reusable, visual transformation workflows that automate complex integration scenarios - from operational reconciliation to customer intelligence to real-time anomaly detection.

Prerequisites: MinIO running with sample data populated; basic understanding of transformation concepts (steps, hops, preview); familiarity with joins and aggregations

Estimated Time: 4-6 hours total (individual workshops range from 20-60 minutes based on complexity)

Workshop
Key Skills

Sales Dashboard

joins, lookups, aggregations

Inventory Reconciliation

XML parsing, outer joins, variance

Customer 360

multi-source, JSONL, calculations

Clickstream Funnel

sessionization, pivoting

Log Parsing

regex, time-series analysis

Data Lake Ingestion

schema normalization, validation

1. Verify that MinIO is running and populated.

  1. Start Pentaho Data Integration.

Windows - PowerShell:

Linux:

Workshops

Sales Dashboard

The workshop demonstrates how Pentaho Data Integration enables organizations to rapidly create denormalized fact tables that power real-time business intelligence dashboards. By integrating data from multiple sources (customer data, product catalogs, and sales transactions), business users gain immediate access to actionable insights without waiting for IT to build complex data warehouses.

Scenario: A mid-sized e-commerce company needs to track daily sales performance across products, customer segments, and regions. Currently, sales managers wait 24-48 hours for IT to generate reports from disparate systems. With PDI, they can automate this process and refresh dashboards hourly.

Key Stakeholders:

  • Sales Directors: Need to identify top-performing products and regions

  • Marketing Teams: Require customer segmentation for targeted campaigns

  • Finance: Need accurate revenue reporting by product category

  • Operations: Must monitor inventory turnover rates

Sales Dashboard

Follow the steps to create the transformation:

Text File Input

The Text File Input step is used to read data from a variety of different text-file types. The most commonly used formats include Comma Separated Values (CSV files) generated by spreadsheets and fixed width flat files.

The Text File Input step provides you with the ability to specify a list of files to read, or a list of directories with wild cards in the form of regular expressions. In addition, you can accept filenames from a previous step making filename handling more even more generic.

Test File Inputs

  1. Drag & drop 3 Text File Input Steps onto the canvas.

  2. Save transformation as: sales_dashboard_etl.ktr in your workshop folder.

Sales (Order Management)

  1. Double-click on the first TFI step, and configure with the following properties:

Setting
Value

Step name

Sales

Filename

pvfs://Minio/raw-data/csv/sales.csv

Delimiter

,

Head row present

✅

Format

mixed

Select - sales.csv from VFS connections

  1. Click: Get Fields to auto-detect columns.

Business Logic: Note that sale_amount may differ from price * quantity due to:

  • Volume discounts

  • Promotional pricing

  • Customer-specific pricing tiers

  • Currency conversion (for international sales)

Get Fields - Sales

  1. Preview data.

Preview data - Sales

Business Significance:

  • sale_amount: Actual revenue (may include discounts)

  • quantity: Volume metrics for demand planning

  • payment_method: Payment preference insights

  • status: Filter out cancelled/refunded orders

Products (ERP system)

  1. Double-click on the second TFI step, and configure with the following properties:

Setting
Value

Step name

Products

Filename

pvfs://Minio/raw-data/csv/products.csv

Delimiter

,

Head row present

✅

Format

mixed

Select - products.csv from VFS connections

  1. Click: Get Fields to auto-detect columns.

Get Fields - Customers

  1. Preview the data.

Preview data - Products

Business Significance:

  • category: Enables product performance analysis by segment

  • price: Base pricing for margin calculations

  • stock_quantity: Inventory turnover insights

Customers (CRM System)

  1. Double-click on the third TFI step, and configure with the following properties:

Setting
Value

Step name

Customers

Filename

pvfs://MinIO/raw-data/csv/customers.csv

Delimiter

,

Header row present

✅

Format

mixed

Select - customers.csv from VFS connections

  1. Click: Get Fields to auto-detect columns.

Get Fields - Customers

  1. Preview the data.

Preview data - Customers

Business Significance:

  • customer_id: Primary key for joining to sales

  • country: Critical for geographic segmentation

  • status: Identifies churned vs. active customers

  • registration_date: Enables customer tenure analysis

Stream Lookup

A Stream lookup step enriches rows by looking up matching values from another stream.

In a transformation, you feed your main rows into one hop and a reference dataset into the other hop. The step then matches rows using key fields and returns the lookup fields on the output. It’s the in-memory alternative to a database lookup, but the reference stream must be available in the same transformation flow.

Lookups

  1. Drag & drop 2 Stream Lookups Input Steps onto the canvas.

  2. Save transformation as: sales_dashboard_etl.ktr in your workshop folder.

Product Lookup

  1. Draw a Hop bewteen 'Sales' step & 'Product Lookup' step.

  2. Draw a Hop bewteen 'Product' step & 'Product Lookup' step.

The Sales is acting as our Fact table. It holds the transaction data for our Products & Customers.

  1. Double-click on the 'Product Lookup' step, and configure with the following properties:

Tab
Setting
Value

General

Step name

Product Lookup

General

Lookup step

Products

Keys

Field (from Sales)

product_id

Keys

Field (from Products)

product_id

  1. In Values to retrieve, add:

    • product_name (rename to product_name)

    • category (rename to product_category)

    • price (rename to unit_price)

Product Lookup

Customers Lookup

  1. Draw a Hop bewteen 'Product Lookup' step & 'Customers Lookup' step.

  2. Draw a Hop bewteen 'Customers' step & 'Customers Lookup' step.

  3. Double-click on the 'Customer Lookup' step, and configure with the following properties:

Setting
Value

Step name

Customers Lookup

Lookup step

Customers

Key field (stream)

customer_id

Key field (lookup)

customer_id

  1. Values to retrieve:

    • first_name

    • last_name

    • country (rename to customer_country)

    • status (rename to customer_status)

Customers Lookup

Preview data

  1. Save the transformation.

  2. RUN & Preview the data.

Lookups - Preview data

Calculator

The Calculator step provides predefined functions that you can run on input field values. Use Calculator as a quick alternative to custom JavaScript for common calculations.

To use Calculator, specify the input fields and the calculation type, and then write results to new fields. You can also remove temporary fields from the output after all values are calculated.

Calculator step

  1. Drag & drop a 'Calculator' step onto the canvas.

  2. Draw a Hop from the 'Customers Lookup' step to the 'Calculator' step.

  3. Double-click on the 'Calculator' step, and configure the following properties:

New field
Calculation
Field A
Field B
Value type

line_total

A * B

quantity

unit_price

Number

profit_margin

A - B

sale_amount

line_total

Number

Calculator

Preview data

  1. Save the transformation.

  2. RUN & Preview the data.

Preview data

Business Insight Enabled:

  • Positive discount_amount: Customer received a discount (common)

  • Negative discount_amount: Customer paid MORE than catalog price (premium service, expedited shipping, etc.)

  • Zero discount_amount: Sold at list price

Formula

The Formula step can calculate Formula Expressions within a data stream. It can be used to create simple calculations like [A]+[B] or more complex business logic with a lot of nested if / then logic.

Formula step

  1. Drag & drop a 'Formula' step onto the canvas.

  2. Draw a Hop from the 'Calculator' step to the 'Formula' step.

  3. Double-click on the 'Formula' step, and configure the following properties:

New Field
Formula

customer_full_name

CONCATENATE([first_name];" ";[last_name])

is_high_value

IF([sale_amount]>500;"Yes";"No")

Formula step

Preview data

  1. Save the transformation.

  2. RUN & Preview the data.

Preview data

Business Applications:

  • is_high_value: Trigger VIP customer service workflows

  • discount_percentage: Measure promotion effectiveness by channel

  • payment_risk: Flag transactions for fraud review

Add Constants

The Add constant values step is a simple and high performance way to add constant values to the stream.

Add constants

  1. Drag & drop 'Add constants' step onto the canvas.

  2. Draw a Hop from the 'Formula' step to the 'Add constants ' step.

  3. Double-click on the 'Add constants' step, and configure the following properties:

Name
Type
Value

data_source

String

minio_workshop

Add constants

Get system info

This step retrieves system information from the Kettle environment. The step includes a table where you can designate a name and assign it to any available system info type you want to retrieve. This step generates a single row with the fields containing the requested information.

It can also accept any number of input streams, aggregate any fields defined by this step, and send the combined results to the output stream.

get system info

  1. Drag & drop 'Get system info' step onto the canvas.

  2. Draw a Hop from the 'Add constants' step to the 'Get system info ' step.

  3. Double-click on the 'Add constants' step, and configure the following properties:

Name
Type

etl_timestamp

system date (variable)

Get system info

Select Values

The Select Values step can perform all the following actions on fields in the PDI stream:

Select fields - The Select Values step can perform all the following actions on fields in the PDI stream.

Remove fields - Use this tab to remove fields from the input stream.

Meta-data - Use this tab to remove fields from the input stream.

Select values

  1. Drag & drop a 'Select values' step onto the canvas.

  2. Draw a Hop from the 'Get system info' step to the 'Select values' step.

  3. Double-click on the 'Select values' step, and configure the following properties:

  4. On Select & Alter tab, choose fields in order:

  • sale_id

  • sale_date

  • customer_id

  • customer_full_name

  • customer_country

  • customer_status

  • product_id

  • product_name

  • product_category

  • quantity

  • unit_price

  • sale_amount

  • line_total

  • profit_margin

  • is_high_value

  • payment_method

  • status (rename to sale_status)

  • etl_timestamp

  • data_source

Select

Preview data

  1. Save the transformation.

  2. RUN & Preview the data.

Preview data

Text file output

The Text File Output step exports rows to a text file.

This step is commonly used to generate delimited files (for example, CSV) that can be read by spreadsheet applications, and it can also generate fixed-length output.

You can’t run this step in parallel to write to the same file.

If you need to run multiple copies, select Include stepnr in filename and merge the resulting files afterward.

Text File output

  1. Drag & drop a 'Select values' step onto the canvas.

  2. Draw a Hop from the 'Select values' step to the 'Write to staging' step.

  3. Double-click on the 'Write to staging' step, and configure with the following properties:

Setting
Value

Step name

Write to Staging

Filename

pvfs://MinIO/staging/dashboard/sales_fact

Extension

csv

Include date/time in filename

✅

Separator

,

Add header

✅

Remember to: Get Fields

Business Benefit: Timestamped files enable:

  • Historical tracking: "What did the data look like last Tuesday?"

  • Incremental processing: Keep processing latest file without overwriting history

  • Rollback capability: "The 3pm run had bad data, revert to 2pm version"

MinIO

  1. Sve the transformation.

  2. Log into MinIO:

MinIO - Dashboard data

Checklist

Inventory Reconciliation - XML + CSV Integration

This workshop demonstrates how Pentaho Data Integration eliminates costly inventory discrepancies by automatically reconciling data between warehouse management systems (XML feeds) and ERP product catalogs (CSV files). Organizations lose millions annually due to inventory inaccuracies, stockouts, and overstocking. PDI's ability to parse complex XML and perform full outer joins enables real-time discrepancy detection that would require hours of manual spreadsheet work.

Business Value Delivered:

  • Cost Reduction: Eliminate manual reconciliation labor ($75K-150K annually per analyst)

  • Inventory Optimization: Reduce excess inventory carrying costs by 15-25%

  • Stockout Prevention: Identify missing items before customers notice

  • Compliance: Audit trail for SOX, ISO 9001, and supply chain regulations

  • Real-Time Visibility: Know your actual inventory position within minutes, not days

Scenario: A manufacturing company operates 12 distribution warehouses. Each warehouse uses a legacy WMS (Warehouse Management System) that exports XML inventory files nightly. The corporate ERP system maintains a CSV product master catalog. Discrepancies cause:

  • Phantom stock: ERP shows item in stock, warehouse says it's not → Lost sales

  • Ghost inventory: Warehouse has items ERP doesn't recognize → Dead capital

  • Quantity variances: Mismatches of 10+ units trigger expensive physical counts

Key Stakeholders:

  • Supply Chain Directors: Need accurate inventory positions across all locations

  • Warehouse Managers: Require daily reconciliation reports to prioritize cycle counts

  • Finance Teams: Must report accurate inventory valuations for financial statements

  • Procurement: Need to identify slow-moving items and prevent overstocking

Inventory Conciliation

Follow the steps to create the transformation:

Get data form XML

x

  1. Drag & drop 'Get data from XML' onto the canvas.

  2. Save transformation as: sales_dashboard_etl.ktr in your workshop folder.

  3. Double-click on the 'Get data from XML' step, and configure with the following properties:

Setting
Value

Step name

Read Warehouse XML

File or directory

pvfs://MinIO/raw-data/xml/inventory.xml

Loop XPath

/inventory/items/item

Encoding

UTF-8

Ignore comments

✅

Validate XML

No

Ignore empty file

✅

XPath Explanation:

  • /inventory = Start at root element

  • /items = Navigate to items container

  • /item = Loop over each item element

  1. Browse & Add the path to the inventory.xml

x

  1. Click on the Content tab

Configure XPath

  1. Click on the Fields tab & Get Fields.

  2. Remap the fields & Preview rows.

Business Field Naming:

  • Prefix with warehouse_ to distinguish from ERP fields later

  • warehouse_quantity vs. stock_quantity makes joins clearer

  • Keep original field names in a data dictionary for auditing

Name
XPath

warehouse_item_name

name

warehouse_quantity

quantity

warehouse_location

location

last_physical_count

last_checked

Remap field names & Preview data

x

x

x

x

x

x

Customer 360

Create unified customer profiles combining demographic data, purchase history, and behavioral events.

Skills: Multiple joins, JSONL parsing, aggregations, calculated metrics

Customer 360

x

x

Text file input

The Text File Output step exports rows to a text file.

This step is commonly used to generate delimited files (for example, CSV) that can be read by spreadsheet applications, and it can also generate fixed-length output.

You can’t run this step in parallel to write to the same file.

If you need to run multiple copies, select Include stepnr in filename and merge the resulting files afterward.

x

  1. Drag & drop 'Text file input' steps onto the canvas.

  2. Save transformation as: customer_360.ktr in your workshop folder.

Sales (Order Management)

  1. Double-click on the first TFI step, and configure with the following properties:

Setting
Value

Step name

Sales

Filename

pvfs://Minio/raw-data/csv/sales.csv

Delimiter

,

Head row present

✅

Format

mixed

Select - sales.csv from VFS connections

  1. Click: Get Fields to auto-detect columns.

Business Logic: Note that sale_amount may differ from price * quantity due to:

  • Volume discounts

  • Promotional pricing

  • Customer-specific pricing tiers

  • Currency conversion (for international sales)

Get Fields - Sales

  1. Preview data.

Preview data - Sales

Business Significance:

  • sale_amount: Actual revenue (may include discounts)

  • quantity: Volume metrics for demand planning

  • payment_method: Payment preference insights

  • status: Filter out cancelled/refunded orders

Sort

Group by

x

x

Text file input

x

x

Log Parsing and Anomaly Detection

Objective: Parse application logs, extract metrics, and detect anomalies.

Skills: Regex, timestamp parsing, time-series analysis, conditional logic

Log Analysis

x

x

x

x

x

Transactions & Fraud Detection

Objective: Process credit card transactions, enrich with account and merchant data, calculate transaction metrics, and detect suspicious patterns using rule-based fraud detection.

Skills: Financial data processing, multi-table joins, running totals, rule-based fraud detection, transaction velocity analysis

Business Context: A payment processor needs to analyze transaction data in real-time to detect potentially fraudulent activity before authorizing transactions. The system must flag high-risk transactions based on amount thresholds, unusual merchant activity, account balance checks, and transaction velocity patterns.

x

x

Financial Data

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x

xx

x

Data Lake Ingestion

Modern data lakes often receive the same entities (products, customers, orders) from multiple sources in different formats. This workshop demonstrates how to ingest, normalize, validate, and deduplicate multi-format data into a unified schema - a common data engineering pattern.

Objective: Combine data from CSV, JSON, and XML into a unified product schema.

Skills: Multi-format parsing, schema normalization, data validation, deduplication

x

x

Define Target Schema

Objective: Design a unified schema that accommodates all source formats.

Why Important: Before ingesting data, you need a clear target schema. This ensures consistency across all sources and makes downstream analytics easier.

x

Field
Type
Length
Description
Source Mapping

product_id

String

50

Unique product identifier

CSV: product_id JSON: product_id XML: sku

product_name

String

200

Product display name

CSV: product_name JSON: product_name XML: name

category

String

100

Product category

CSV: category JSON: (derived from order type) XML: category

price

Number

15,2

Unit price in USD

CSV: price JSON: unit_price XML: null (not available)

quantity

Integer

10

Available stock quantity

CSV: stock_quantity JSON: quantity XML: quantity

source_system

String

10

Origin system identifier

Constant: 'csv', 'json', or 'xml'

ingestion_time

Timestamp

-

When record was ingested

System timestamp

x

x

Schema Discovery & Analysis

Objective: Understand each source structure before you design the target schema.

Why it matters: You can’t normalize what you haven’t inspected.

1

Inspect each source

Use real samples. Avoid guessing field names.

Findings

  • Has product_id, product_name, category, price, stock_quantity.

  • Completeness looks high.

  • Naming is consistent and explicit.

Findings

  • Has product_id and product_name.

  • Uses unit_price instead of price.

  • quantity is order quantity, not stock.

  • category is missing.

  • Path is $.data.orders[*].items[*].

Findings

  • Uses sku for product_id.

  • Uses name for product_name.

  • Has category and warehouse quantity.

  • price is missing.

  • location is extra for a product master.

2

Build a field mapping matrix

This shows name differences and missing fields.

Unified field
CSV
JSON
XML
Notes

Identifier

product_id

product_id

sku

Same meaning. Different name in XML.

Name

product_name

product_name

name

Same meaning. Different name in XML.

Category

category

❌

category

Missing in JSON.

Price

price

unit_price

❌

Different name in JSON. Missing in XML.

Stock quantity

stock_quantity

quantity

quantity

JSON quantity is not stock.

What to watch

  • Missing data is normal in multi-source ingestion.

  • Same name can mean different things.

3

Make schema decisions

Write these down. You will forget them later.

Field names

  • Use CSV naming as the standard.

  • Map XML sku → product_id and name → product_name.

  • Map JSON unit_price → price.

Missing fields

  • Missing category in JSON: set a default like E-commerce.

  • Missing price in XML: leave NULL.

Data types

  • product_id: string. It contains PROD- prefix.

  • product_name: string. Allow up to 200 chars.

  • category: string. Allow up to 100 chars.

  • price: decimal(15,2).

  • quantity: integer.

Metadata

  • Add source_system for lineage.

  • Add ingestion_time for auditability.

4

Define a deduplication rule

Same product_id can appear in multiple sources.

Recommended rule

  1. Prefer CSV.

  2. Then JSON.

  3. Then XML.

Implement this with source_priority (CSV=1, JSON=2, XML=3).

5

Checklist

  • You inspected real records for each source.

  • You captured paths for nested formats.

  • You documented mappings and type choices.

  • You decided how to handle missing data.

  • You decided how to dedupe collisions.

x

x

x

Step 2: Ingest CSV Products

Objective: Read CSV products and map to target schema.

Why CSV First: CSV is the simplest format and requires minimal transformation - good for testing your target schema.

Configuration:

  1. Add Text file input step

    • Name: "Read CSV Products"

    • File/directory: pvfs://MinIO/raw-data/csv/products.csv

    • Separator: Comma (,)

    • Enclosure: " (double quote)

    • Header: ☑ Header row present

  2. Fields tab (click "Get Fields"):

    Name
    Type
    Format
    Length
    Precision

    product_id

    String

    50

    product_name

    String

    200

    category