Architecture

This document explains the architecture and design principles behind Derafu ETL.

ETL Pattern

The Extract-Transform-Load (ETL) pattern is a data integration process used to collect data from various sources, transform it to fit operational needs, and load it into a target database for analysis and storage.

The Three Phases

1. Extract: Gathering data from source systems.
2. Transform: Converting the extracted data to satisfy operational requirements.
3. Load: Writing the transformed data to the target system.

Derafu ETL Implementation

Derafu ETL implements this pattern with a clean, object-oriented approach centered around the Pipeline concept.

Core Components

Extract Phase

• DataSource: Encapsulates the data source (spreadsheet, database).
• DataExtractor: Handles extraction logic.
• SchemaSource: Extracts schema information from the source.

Transform Phase

• DataRules: Defines transformation rules.
• DataTransformer: Applies transformations to extracted data.

Load Phase

• DataTarget: Represents the destination system.
• DataLoader: Manages loading data into the target.
• SchemaTarget: Applies schema to the target system.

Pipeline Orchestration

The Pipeline class orchestrates the entire ETL process, providing a fluent interface:

$pipeline
    ->extract($source)    // Configure extraction.
    ->transform($rules)   // Configure transformation.
    ->load($target)       // Configure loading.
    ->execute()           // Run the pipeline.
;

When execute() is called, the pipeline:

1. Validates the configuration.
2. Extracts data from the source.
3. Transforms the data according to rules.
4. Synchronizes the target schema with the source.
5. Loads the transformed data into the target.
6. Returns a result object with statistics.

Dependency Injection

Import the package configuration to use the shared ETL and Spreadsheet services:

imports:
    - { resource: '../vendor/derafu/etl/resources/config/etl-services.yaml' }

Inject Derafu\ETL\Pipeline\Contract\PipelineInterface into the consuming service. The YAML also registers DatabaseManagerInterface, DataExtractorInterface, DataTransformerInterface and DataLoaderInterface under their respective contracts. Extractor and loader use the same database manager, which passes Spreadsheet’s shared factory, caster, loader and dumper to the runtime database adapters. Spreadsheet configuration and caster overrides therefore apply to those operations too.

Each request for PipelineInterface creates a separate pipeline, because its source, target and rules belong to one configured transfer. A service holding a pipeline keeps that instance; request another pipeline when independent configuration is needed.

The YAML registers Derafu\ETL\Command\PipelineCommand with the configured pipeline. The consuming application registers the command with its console. Database connections, schemas, data sources, targets and transformation rules remain runtime values.

Opening an ETL spreadsheet file preserves business-cell values. The shared caster decodes the __schema metadata there; dump loading and spreadsheet exports use their existing Spreadsheet loader and dumper casting boundaries.

Atomic Loads

The built-in database adapters group schema synchronization and the loading of all tables into one operation. A failure while loading a later table also discards earlier changes, within the backend limitations described below. Extraction and transformation run before this operation; external side effects performed by custom rules are outside its scope.

• SQLite and PostgreSQL: ordinary table schema changes and row loads share one database transaction. If the caller already opened a transaction, the pipeline uses nested transactions/savepoints and does not commit the caller’s transaction.
• Spreadsheets: changes are prepared in a separate document. Success makes that document the database’s current connection; failure preserves the original document. References previously obtained through spreadsheet() still point to the original document. File persistence through save() is a separate operation and is not included in this guarantee.
• MySQL: row rollback requires transactional tables, such as InnoDB. When schema synchronization requires structural changes, full atomicity cannot be guaranteed because DDL causes implicit commits. The pipeline remains usable but cannot restore the previous schema and all data after an error. This also affects transactions opened by the caller. With sync disabled, or when the schema already matches, the pipeline wraps row loading in one transaction.

The MySQL limitation follows the server’s implicit commit behavior; a transaction around schema changes does not make those changes reversible.

Custom database adapters opt in by implementing TransactionalDatabaseInterface. Its transactional() method receives the complete synchronization/load operation and, when synchronization is enabled, the source database. Existing adapters that implement only DatabaseInterface continue to work, but the pipeline does not provide an atomicity guarantee for them. Custom loaders and targets must perform writes through the database they expose; unrelated connections or external side effects are outside its scope.

Key Abstractions

Database

The Database abstraction provides a unified interface for different database types:

• SpreadsheetDatabase: Treats spreadsheets as databases using Derafu Spreadsheet.
• DoctrineDatabase: Works with any database supported by Doctrine DBAL.

Schema

The Schema system represents database structure:

• Tables, columns, indexes, foreign keys.
• Import/export to various formats (Spreadsheet, Doctrine, Markdown, D2, etc.).
• The model is independent from Doctrine DBAL: see Schema Model, including the IndexType of an index.

Extension Points

Derafu ETL is designed for extensibility:

1. New Data Sources: Implement DataSourceInterface.
2. Custom Transformations: Extend DataRules.
3. New Data Targets: Implement DataTargetInterface.
4. Schema Visualization: Implement SchemaTargetInterface.

Design Principles

1. Separation of Concerns: Each component has a clear responsibility.
2. Fluent Interface: Expressive, chainable API.
3. Flexibility: Support for various formats and systems.
4. Extensibility: Easy to extend with custom components.