Data Engineering project developed during a CNJ hackathon, focused on the ingestion, transformation, processing, and analytical preparation of judicial data from the DataJud ecosystem.
The project combines Python, Pentaho Data Integration (PDI/Kettle), shell scripting, JSON processing, staging workflows, and metric calculation to build an ETL pipeline for Business Intelligence use cases.
The solution follows a traditional ETL architecture:
CNJ / DataJud Data
│
▼
┌─────────────────┐
│ Data Ingestion │
│ / Python │
└────────┬────────┘
│
▼
┌─────────────────┐
│ JSON Processing │
│ JSON → CSV │
└────────┬────────┘
│
▼
┌─────────────────┐
│ Staging │
│ ETL / PDI │
└────────┬────────┘
│
▼
┌─────────────────┐
│ Transformations │
│ & Validation │
└────────┬────────┘
│
▼
┌─────────────────┐
│ Metric / KPI │
│ Calculation │
└────────┬────────┘
│
▼
┌─────────────────┐
│ BI / Analytics│
└─────────────────┘
The repository contains dedicated ETL jobs and transformations for staging, JSON processing, metric calculation, and analytical preparation.
The main objectives of the project are:
- Integrate judicial data
- Automate data ingestion
- Process JSON-based datasets
- Transform data into analytical structures
- Implement ETL workflows
- Calculate analytical metrics
- Prepare data for BI consumption
- Automate execution and logging
| Technology | Purpose |
|---|---|
| Python | Data processing and utility scripts |
| Pentaho Data Integration | ETL orchestration and transformation |
| Kettle | ETL jobs and transformations |
| JSON | Source/intermediate data format |
| CSV | Data interchange and processing |
| Shell Script | Pipeline execution automation |
| BI / Analytics | Analytical consumption |
GitHub reports Python and Shell as the repository's primary programming languages.
The pipeline is organized into several processing stages.
The first stage retrieves or receives judicial data that will be processed by the pipeline.
The data is subsequently prepared for the ETL workflows.
Python utilities are included for handling JSON data.
The repository contains:
script/
├── json2csv.py
├── listaFiles.py
└── testes.txt
The json2csv.py utility is used to support conversion from JSON-oriented data into CSV structures suitable for subsequent ETL processing.
The staging layer provides an intermediate area where source data can be processed before being transformed into analytical structures.
The repository includes a dedicated Pentaho transformation:
ktr/
└── ktr_staging.ktr
This supports the separation between raw/input data and subsequent analytical processing.
The core ETL processing is implemented through Pentaho transformations and jobs.
The repository contains:
ktr/
├── calcular_medias.ktr
├── job_calcularMetricas.kjb
├── job_cnj.kjb
├── json2csv.kjb
├── ktr_staging.ktr
├── testeJson.ktr
└── testeJson (cópia).ktr
These components provide reusable ETL workflows for processing data and orchestrating multiple transformation steps.
The project includes dedicated processing for calculating metrics:
calcularMatricas/
calcular_medias.ktr
job_calcularMetricas.kjb
This demonstrates the transition from raw/staged data toward analytical indicators suitable for BI workloads.
Pentaho Data Integration (PDI/Kettle) is the main ETL technology used in the project.
The architecture uses two important types of Pentaho artifacts:
Transformations define individual data-processing operations.
Examples include:
ktr_staging.ktr
calcular_medias.ktr
testeJson.ktr
Jobs orchestrate larger ETL workflows.
Examples include:
job_cnj.kjb
job_calcularMetricas.kjb
json2csv.kjb
This separation between transformations and jobs allows individual processing components to be reused as part of larger workflows.
The repository includes a shell script for executing the Pentaho pipeline:
start_ktr_cnj.sh
The script launches the Pentaho kitchen.sh execution engine and redirects execution output to timestamped log files.
Conceptually:
Shell Script
│
▼
Pentaho Kitchen
│
▼
job_cnj.kjb
│
├── Data Processing
├── Transformations
├── Metric Calculation
└── Logging
This demonstrates the automation of ETL execution outside the graphical Pentaho environment.
The pipeline includes execution logging.
The launcher dynamically generates timestamped log files:
log_indicador_moodle.<timestamp>.log
This provides a mechanism for tracking ETL execution and troubleshooting processing failures.
cnj_datajud_bi/
│
├── ktr/
│ ├── calcularMatricas/
│ ├── logs/
│ ├── calcular_medias.ktr
│ ├── job_calcularMetricas.kjb
│ ├── job_cnj.kjb
│ ├── json2csv.kjb
│ ├── ktr_staging.ktr
│ ├── testeJson.ktr
│ └── testeJson (cópia).ktr
│
├── script/
│ ├── json2csv.py
│ ├── listaFiles.py
│ └── testes.txt
│
├── start_ktr_cnj.sh
└── README.md
The structure reflects the separation between ETL orchestration, transformations, Python utilities, logging, and execution automation.
A simplified representation of the processing pipeline is:
Judicial Data
│
▼
JSON Files
│
▼
Python Processing
│
▼
JSON → CSV
│
▼
Staging
│
▼
Pentaho Transformations
│
▼
Metric Calculation
│
▼
BI / Analytics
This project demonstrates practical experience with:
- ETL
- Data Integration
- Data Transformation
- Data Staging
- Data Processing
- Data Quality
- Analytical Data Preparation
- Pentaho Data Integration
- Kettle transformations
- Kettle jobs
- Kitchen command-line execution
- ETL orchestration
- ETL logging
- Python
- Shell scripting
- JSON processing
- CSV processing
- Metric calculation
- Average/aggregation processing
- BI-oriented datasets
- Analytical data preparation
This project is particularly relevant to enterprise Data Engineering environments where heterogeneous source systems must be integrated into reliable analytical pipelines.
The architectural principles demonstrated here can also be extended to modern cloud data platforms:
Traditional Data Engineering
│
├── Pentaho
├── ETL
├── Staging
└── Data Warehouse
│
▼
Modern Data Engineering
│
├── Python
├── PySpark
├── Databricks
├── Delta Lake
└── Cloud Data Platforms
The project therefore represents an important foundation for modernizing traditional ETL architectures into cloud-native data pipelines.
Project: CNJ DataJud BI Context: CNJ Hackathon Focus: Data Integration, ETL, Business Intelligence
The original repository describes the project as “Hackaton - CNJ - BI - ETL.”
Data Engineering
- ETL / ELT
- Data Integration
- Data Transformation
- Data Staging
- Data Processing
- Data Warehousing concepts
Tools & Technologies
- Python
- Pentaho Data Integration
- Kettle
- Shell scripting
- JSON
- CSV
Architecture
- ETL pipelines
- Staging architecture
- Batch processing
- Workflow orchestration
- Analytical data preparation
- BI data pipelines
Ruben Cruz
Data Engineering | Data Integration | ETL | Pentaho | Python | PySpark | Databricks | AI Integration
GitHub: https://github.com/rubencruz