ETL Editor

Overview

The ETLEditor class is the core component for Extract, Transform, Load operations in the Beep Data Management Engine. It provides comprehensive functionality for data migration, synchronization, and transformation between different data sources with advanced error handling, progress tracking, and validation capabilities.

Key Capabilities

ETLEditor handles complex data migration scenarios with entity creation, data validation, relationship management, and comprehensive error tracking.

Signature-Verified Snapshot (Current ETL Surfaces)

// Legacy/classic surface
public class ETLEditor
{
    // Source file: Editor/ETL_BK/ETLEditor.cs
    // (used by existing DME flows and docs examples)
}

// New pipeline engine surface
public class PipelineEngine
{
    public PipelineEngine(IDMEEditor editor);
    public ObservabilityStore? ObservabilityStore { get; set; }
    public MetricsEngine? Metrics { get; set; }
    public AlertingEngine? Alerting { get; set; }
    public SecurityPolicyEngine? SecurityPolicy { get; set; }

    public Task<PipelineRunResult> RunAsync(
        PipelineDefinition def,
        IProgress<PassedArgs>? progress = null,
        CancellationToken token = default,
        Dictionary<string, object>? overrideParams = null,
        SecurityContext? securityContext = null);
}

Script Generation

Automated Ddl script generation for entity creation and data migration

Data Transformation

Advanced data mapping and transformation capabilities

? Async Processing

Full async/await support with progress reporting and cancellation

Error Management

Comprehensive error tracking, validation, and recovery mechanisms

Core Properties

▶ Essential Properties

Property	Type	Description
DMEEditor	IDMEEditor	Reference to the main data management editor
RulesEngine	IRuleEngine	Business rules processing engine
Script	ETLScriptHDR	Current ETL script header with all operations
LoadDataLogs	List<LoadDataLogResult>	Detailed logging of data load operations
StopErrorCount	decimal	Maximum errors allowed before stopping (default: 10)
ScriptCount	int	Total number of scripts to process
CurrentScriptRecord	int	Current record being processed

▶ Events

Event	Type	Description
PassEvent	EventHandler<PassedArgs>	Event raised during ETL processing for progress reporting

Script Generation

▶ Script Creation Methods

void CreateScriptHeader(IDataSource sourceDs, IProgress<PassedArgs> progress, CancellationToken token)

Creates a comprehensive ETL script header for migrating all entities from a source data source.

List<ETLScriptDet> GetCreateEntityScript(IDataSource ds, List<string> entities, IProgress<PassedArgs> progress, CancellationToken token, bool copydata = false)

Generates ETL script details for creating specific entities with optional data copying.

List<ETLScriptDet> GetCreateEntityScript(IDataSource dest, List<EntityStructure> entities, IProgress<PassedArgs> progress, CancellationToken token, bool copydata = false)

Generates scripts from entity structures for more control over the creation process.

List<ETLScriptDet> GetCopyDataEntityScript(IDataSource dest, List<EntityStructure> entities, IProgress<PassedArgs> progress, CancellationToken token)

Creates scripts specifically for data copying operations between entities.

Script Generation Example

// Initialize ETL Editor
var etlEditor = new ETLEditor(dmeEditor);

// Create source and destination data sources
var sourceDs = dmeEditor.GetDataSource("SourceDatabase");
var destDs = dmeEditor.GetDataSource("DestinationDatabase");

// Generate create entity scripts for specific entities
var entities = new List<string> { "Customers", "Orders", "Products" };
var createScripts = etlEditor.GetCreateEntityScript(
    destDs, entities, progress, cancellationToken, true);

// Set up the ETL script
etlEditor.Script.ScriptDetails.AddRange(createScripts);

// Save the script for later use
etlEditor.SaveETL("MigrationProject");

Data Copying Operations

▶ Entity Structure Copying

IErrorsInfo CopyEntitiesStructure(IDataSource sourceDs, IDataSource destDs, List<string> entities, IProgress<PassedArgs> progress, CancellationToken token, bool createMissingEntity = true)

Copies the structure of multiple entities between data sources with optional entity creation.

IErrorsInfo CopyEntityStructure(IDataSource sourceDs, IDataSource destDs, string srcEntity, string destEntity, IProgress<PassedArgs> progress, CancellationToken token, bool createMissingEntity = true)

Copies the structure of a single entity with foreign key constraint management.

▶ Data Copying Methods

IErrorsInfo CopyDatasourceData(IDataSource sourceDs, IDataSource destDs, IProgress<PassedArgs> progress, CancellationToken token, bool createMissingEntity = true, EntityDataMap_DTL map_DTL = null)

Copies all data from source to destination data source with optional mapping.

IErrorsInfo CopyEntitiesData(IDataSource sourceDs, IDataSource destDs, List<string> entities, IProgress<PassedArgs> progress, CancellationToken token, bool createMissingEntity = true, EntityDataMap_DTL map_DTL = null)

Copies data for specific entities with advanced mapping and transformation support.

IErrorsInfo CopyEntityData(IDataSource sourceDs, IDataSource destDs, string srcEntity, string destEntity, IProgress<PassedArgs> progress, CancellationToken token, bool createMissingEntity = true, EntityDataMap_DTL map_DTL = null)

Copies data for a single entity with comprehensive error handling and validation.

Data Copying Example

// Copy entity structures first
var structureResult = etlEditor.CopyEntitiesStructure(
    sourceDs, destDs, 
    new List<string> { "Customers", "Orders" },
    progress, cancellationToken, true);

if (structureResult.Flag == Errors.Ok)
{
    // Then copy the data
    var dataResult = etlEditor.CopyEntitiesData(
        sourceDs, destDs,
        new List<string> { "Customers", "Orders" },
        progress, cancellationToken, true);
        
    if (dataResult.Flag == Errors.Ok)
    {
        Console.WriteLine("Data migration completed successfully!");
    }
}

? Script Execution

▶ Script Execution Methods

Task<IErrorsInfo> RunCreateScript(IProgress<PassedArgs> progress, CancellationToken token, bool copydata = true, bool useEntityStructure = true)

Executes the complete ETL script with entity creation and optional data copying.

Task<IErrorsInfo> RunChildScriptAsync(ETLScriptDet parentScript, IDataSource srcDs, IDataSource destDs, IProgress<PassedArgs> progress, CancellationToken token)

Executes child scripts for complex hierarchical ETL operations.

Script Execution Considerations

Always check data source connections before execution
Monitor the StopErrorCount to prevent runaway processes
Use progress reporting for long-running operations
Implement proper cancellation token handling

Script Execution Example

// Set up progress reporting
var progress = new Progress<PassedArgs>(args => 
{
    Console.WriteLine($"Progress: {args.ParameterInt1}/{args.ParameterInt2} - {args.Messege}");
});

// Set up cancellation
using var cancellationTokenSource = new CancellationTokenSource();
var cancellationToken = cancellationTokenSource.Token;

try
{
    // Execute the ETL script
    var result = await etlEditor.RunCreateScript(
        progress, cancellationToken, 
        copydata: true, useEntityStructure: true);
    
    if (result.Flag == Errors.Ok)
    {
        Console.WriteLine("ETL script executed successfully!");
        
        // Review the logs
        foreach (var log in etlEditor.LoadDataLogs)
        {
            Console.WriteLine(log.InputLine);
        }
    }
    else
    {
        Console.WriteLine($"ETL script failed: {result.Message}");
    }
}
catch (OperationCanceledException)
{
    Console.WriteLine("ETL operation was cancelled.");
}

Import Operations

▶ Import Script Methods

IErrorsInfo CreateImportScript(EntityDataMap mapping, EntityDataMap_DTL selectedMapping)

Creates an import script based on entity mappings for data transformation and import.

Task<IErrorsInfo> RunImportScript(IProgress<PassedArgs> progress, CancellationToken token, bool useEntityStructure = true)

Executes import operations with advanced mapping and transformation capabilities.

Import Features

Entity mapping and field transformation
Default value resolution and application
Relationship validation and parent entity creation
Comprehensive error tracking and logging

Error Handling & Validation

▶ Entity Insertion & Validation

IErrorsInfo InsertEntity(IDataSource destDs, EntityStructure destEntityStructure, string destEntity, EntityDataMap_DTL map_DTL, object record, IProgress<PassedArgs> progress, CancellationToken token)

Inserts entities with comprehensive validation, mapping application, and relationship management.

Error Management Features

Configurable Error Thresholds: Set StopErrorCount to control when operations should halt
Detailed Logging: LoadDataLogs captures all operation details
Relationship Validation: Automatic parent entity creation for referential integrity
Default Value Application: Rule-based default value resolution

Error Handling Configuration

// Configure error handling
etlEditor.StopErrorCount = 50; // Allow up to 50 errors before stopping

// Monitor errors during execution
etlEditor.PassEvent += (sender, args) => 
{
    if (args.EventType == "Stop")
    {
        Console.WriteLine("ETL stopped due to error threshold reached");
    }
    else if (args.EventType == "Update")
    {
        Console.WriteLine($"Processing: {args.ParameterInt1}/{args.ParameterInt2}");
    }
};

// Review detailed logs after execution
foreach (var log in etlEditor.LoadDataLogs)
{
    if (log.InputLine.Contains("Failed"))
    {
        Console.WriteLine($"Error: {log.InputLine}");
    }
}

Usage Examples

Complete ETL Migration Workflow

// Initialize ETL Editor
var etlEditor = new ETLEditor(dmeEditor);

// Set up data sources
var sourceDs = dmeEditor.GetDataSource("LegacySystem");
var destDs = dmeEditor.GetDataSource("ModernSystem");

// Open connections
dmeEditor.OpenDataSource("LegacySystem");
dmeEditor.OpenDataSource("ModernSystem");

// Configure error handling
etlEditor.StopErrorCount = 100;

// Create comprehensive migration script
etlEditor.CreateScriptHeader(sourceDs, progress, cancellationToken);

// Execute the migration
try 
{
    var result = await etlEditor.RunCreateScript(
        progress, cancellationToken, 
        copydata: true, useEntityStructure: true);
    
    if (result.Flag == Errors.Ok)
    {
        Console.WriteLine($"Migration completed! Processed {etlEditor.CurrentScriptRecord} records.");
        
        // Save script for future reference
        etlEditor.SaveETL("LegacyToModernMigration");
    }
    else
    {
        Console.WriteLine($"Migration failed: {result.Message}");
        
        // Review error logs
        var errorLogs = etlEditor.LoadDataLogs
            .Where(log => log.InputLine.Contains("Failed"))
            .ToList();
            
        foreach (var errorLog in errorLogs)
        {
            Console.WriteLine($"Error: {errorLog.InputLine}");
        }
    }
}
finally
{
    // Clean up connections
    dmeEditor.CloseDataSource("LegacySystem");
    dmeEditor.CloseDataSource("ModernSystem");
}

Selective Entity Migration

// Migrate specific entities with custom mapping
var etlEditor = new ETLEditor(dmeEditor);

// Define entities to migrate
var entitiesToMigrate = new List<string> 
{ 
    "Customers", "Orders", "OrderDetails", "Products" 
};

// Generate creation scripts
var createScripts = etlEditor.GetCreateEntityScript(
    destDs, entitiesToMigrate, progress, cancellationToken, false);

// Generate data copy scripts separately for more control
var copyScripts = etlEditor.GetCopyDataEntityScript(
    destDs, 
    createScripts.Select(s => s.SourceEntity).ToList(),
    progress, cancellationToken);

// Combine scripts
etlEditor.Script.ScriptDetails.AddRange(createScripts);
etlEditor.Script.ScriptDetails.AddRange(copyScripts);

// Execute with detailed monitoring
var result = await etlEditor.RunCreateScript(
    progress, cancellationToken, copydata: false, useEntityStructure: true);

Best Practices

Performance Optimization

Disable foreign key constraints during bulk operations
Use batch processing for large datasets
Monitor memory usage during long-running operations
Implement proper connection pooling

Data Integrity

Always backup destination data before major migrations
Test scripts on sample data first
Validate entity relationships before data copying
Implement transaction boundaries for critical operations

Operational Excellence

Use progress reporting for user feedback
Implement proper cancellation token handling
Save ETL scripts for repeatability and auditing
Monitor and log all operations comprehensively

Table of Contents

Overview

Signature-Verified Snapshot (Current ETL Surfaces)

Script Generation

Data Transformation

? Async Processing

Error Management

Core Properties

Script Generation

Script Generation Example

Data Copying Operations

Data Copying Example

? Script Execution

Script Execution Example

Import Operations

Error Handling & Validation

Error Handling Configuration

Usage Examples

Complete ETL Migration Workflow

Selective Entity Migration

Best Practices