ETL at your Fingertips

Author: Drew Shea, Created: 2025-11-24

ETL Capabilities in XBR

This article explores the objects and methods that XBR provides for moving data between the systems introduced in the previous section. By the end of this article, you'll understand how to define data sources and destinations, choose the right ETL manager for your use case, and leverage convenience methods for common operations.

The DataDefinition Model

At the heart of XBR's ETL system is the DataDefinition class. A DataDefinition describes a data source or destination through three components:

Component	Purpose	Examples
ConnectionContext	Where the data lives	OneStream SQL database, OneStream FileSystem, MetaFileSystem
DataContainerContext	How the data is organized	File path, table name, SQL query
DataFormat	What format the data is in	CSV, Parquet, SQL, Unstructured

Component

Purpose

Examples

ConnectionContext

Where

the data lives

OneStream SQL database, OneStream FileSystem, MetaFileSystem

DataContainerContext

How

the data is organized

File path, table name, SQL query

DataFormat

What

format the data is in

CSV, Parquet, SQL, Unstructured

When you create a DataDefinition, you're essentially telling XBR: "Here's where my data is, here's what container it's in, and here's what format it's stored as."

Data Storage Landscape

OneStream applications interact with several distinct storage locations. Understanding where data lives is the first step to building effective ETL pipelines.

+------------------------------------------------------------------------------+
<table>
 <tbody>
  <tr ac:local-id="43cf5593d372">
   <th>
    <p local-id="263c250d8c96">
     <strong>
      Storage System
     </strong>
    </p>
   </th>
   <th>
    <p local-id="13270b68113e">
     <strong>
      Access Via
     </strong>
    </p>
   </th>
   <th>
    <p local-id="157bd79b2426">
     <strong>
      Identifier
     </strong>
    </p>
   </th>
   <th>
    <p local-id="aa83678a4362">
     <strong>
      Formats
     </strong>
    </p>
   </th>
  </tr>
  <tr ac:local-id="48e911359b3a">
   <td>
    <p local-id="6bc61f181271">
     OneStream File System
    </p>
   </td>
   <td>
    <p local-id="93469fb459bf">
     <code>
      BRApi.FileSystem
     </code>
    </p>
   </td>
   <td>
    <p local-id="de1683f42178">
     <code>
      FileSystemLocation
     </code>
     enum
    </p>
   </td>
   <td>
    <p local-id="3d76eac7de2b">
     <code>
      .csv
     </code>
     ,
     <code>
      .parquet
     </code>
     , any file
    </p>
   </td>
  </tr>
  <tr ac:local-id="494b257deebc">
   <td>
    <p local-id="9e5efb052f1b">
     OneStream SQL Database
    </p>
   </td>
   <td>
    <p local-id="5a52b19b9fc6">
     <code>
      BRApi.Database
     </code>
    </p>
   </td>
   <td>
    <p local-id="0cda226910d3">
     Connection key
     <code>
      string
     </code>
    </p>
   </td>
   <td>
    <p local-id="b84428bb31e6">
     SQL tables &amp; views
    </p>
   </td>
  </tr>
  <tr ac:local-id="37ad2075d5e9">
   <td>
    <p local-id="9b0636269ff6">
     MetaFileSystem
    </p>
   </td>
   <td>
    <p local-id="3e1a95ad1f61">
     <code>
      XBRApi.MetaFileSystem
     </code>
    </p>
   </td>
   <td>
    <p local-id="35973d7d0965">
     Connection key
     <code>
      string
     </code>
    </p>
   </td>
   <td>
    <p local-id="71bb785d1f15">
     <code>
      .csv
     </code>
     ,
     <code>
      .parquet
     </code>
     , any file
    </p>
   </td>
  </tr>
 </tbody>
</table>

+------------------------------------------------------------------------------+

Scenario	Recommended Approach
Simple extract or load to/from a known location	Convenience methods ( `ExtractMetaFileSystemParquetAsDataTable` , etc.)
Moving data between two locations	ETL Manager with `ExtractAndLoad()`
Complex pipeline with intermediate transformations	ETL Manager with separate `Extract` and `Load` calls
Large datasets where memory is a concern	Use `IDataReader` extraction methods
Non-tabular files (JSON, images, PDFs)	Unstructured ETL Manager

Scenario

Recommended Approach

Simple extract or load to/from a known location

Convenience methods (

ExtractMetaFileSystemParquetAsDataTable

, etc.)

Moving data between two locations

ETL Manager with

ExtractAndLoad()

Complex pipeline with intermediate transformations

ETL Manager with separate

Extract

and

Load

calls

Large datasets where memory is a concern

Use

IDataReader

extraction methods

Non-tabular files (JSON, images, PDFs)

Unstructured ETL Manager

Connection Contexts

XBR supports three connection context types:

// OneStream SQL Database
var sqlContext = new OneStreamSqlConnectionContext("MyDataSource");

// OneStream FileSystem
var osfsContext = new OneStreamFileSystemConnectionContext(FileSystemLocation.ApplicationDatabase);

// MetaFileSystem
var mfsContext = new MetaFileSystemConnectionContext("sql-server-shared");

Data Container Contexts

Data containers define how the data is organized within the connection:

// A file at a specific path
var fileContainer = new FilePathDataContainerContext("data/results.parquet");

// A database table
var tableContainer = new TableDataContainerContext("CustomerSegments");

// A SQL query (for extraction only)
var queryContainer = new SqlQueryDataContainerContext("SELECT * FROM Sales WHERE Year = 2025");

Data Formats

XBR recognizes four data format types:

CsvDataFormat – Delimited text files (CSV, TSV, pipe-delimited, etc.)
ParquetDataFormat – Apache Parquet columnar storage format
SqlDataFormat – Data stored in SQL database tables
UnstructuredDataFormat – Any other format (JSON, XML, PDF, images, etc.)

Creating DataDefinitions

While you can construct DataDefinitions manually, XBR provides factory methods that handle the details for you—including automatic format inference based on file extensions:

// MetaFileSystem file - format inferred from .parquet extension
DataDefinition mfsDefinition = XBRApi.Etl.CreateMetaFileSystemDataDefinition(
    si, 
    "sql-server-shared", 
    "data/results.parquet"
);

// OneStream FileSystem file - format inferred from .csv extension
DataDefinition osfsDefinition = XBRApi.Etl.CreateOneStreamFileSystemDataDefinition(
    si, 
    FileSystemLocation.ApplicationDatabase, 
    "exports/report.csv"
);

// OneStream SQL table
DataDefinition sqlDefinition = XBRApi.Etl.CreateOneStreamSqlConnectionDataDefinition(
    si, 
    "MyDataSource", 
    "CustomerData"
);

ETL Managers

XBR provides two ETL managers, each optimized for different data types.

Tabular ETL Manager

The ITabularEtlManager handles structured, row-and-column data—the kind you'd find in database tables, CSVs, and Parquet files. It works with DataTable and IDataReader objects, making it easy to integrate with existing .NET data access patterns.

// Get the manager
ITabularEtlManager etlManager = XBRApi.Etl.GetTabularEtlManager(si);

// Extract to a DataTable
DataTable data = etlManager.ExtractDataTable(sourceDefinition);

// Load from a DataTable
etlManager.Load(destinationDefinition, data);

// Or do both in one call
etlManager.ExtractAndLoad(sourceDefinition, destinationDefinition);

The Tabular ETL Manager supports:

Extraction as IDataReader (forward-only, memory-efficient for large datasets)
Extraction as DataTable (full in-memory representation)
Loading from either IDataReader or DataTable
Direct extract-and-load without intermediate in-memory storage

Unstructured ETL Manager

The IUnstructuredEtlManager handles non-tabular data—JSON files, XML documents, PDFs, images, or any binary content. It works with Stream objects, providing a flexible mechanism for moving arbitrary file content.

// Get the manager
IUnstructuredEtlManager etlManager = XBRApi.Etl.GetUnstructuredEtlManager(si);

// Extract to a Stream
Stream content = etlManager.Extract(sourceDefinition);

// Load from a Stream
etlManager.Load(destinationDefinition, content);

// Or do both in one call
etlManager.ExtractAndLoad(sourceDefinition, destinationDefinition);

Note: The Unstructured ETL Manager does not support SQL-based connections, containers, or formats—it's designed for file-to-file operations only.

Convenience Methods

For common operations, XBR provides direct methods on XBRApi.Etl that bypass the need to create DataDefinitions entirely. These methods are ideal for straightforward extract or load operations.

Extraction Methods

From OneStream Databases:

// Extract a table as a DataTable
DataTable dt = XBRApi.Etl.ExtractOneStreamDatabaseTableAsDataTable(si, "MyDataSource", "CustomerData");

// Extract as an IDataReader for streaming large datasets
IDataReader reader = XBRApi.Etl.GetOneStreamDatabaseTableAsDataReader(si, "MyDataSource", "LargeTable");

From MetaFileSystem:

// Extract Parquet
DataTable parquetData = XBRApi.Etl.ExtractMetaFileSystemParquetAsDataTable(
    si, 
    MetaFileSystemLocation.Shared, 
    "data/results.parquet"
);

// Extract CSV with options
DataTable csvData = XBRApi.Etl.ExtractMetaFileSystemCsvAsDataTable(
    si,
    "sql-server-shared",
    "data/input.csv",
    headersIncluded: true,
    numRows: 1000  // limit rows
);

From OneStream FileSystem:

// Extract Parquet from Application Database
DataTable dt = XBRApi.Etl.ExtractOneStreamFileSystemParquetAsDataTable(
    si, 
    FileSystemLocation.ApplicationDatabase, 
    "exports/data.parquet"
);

// Extract CSV
DataTable csvData = XBRApi.Etl.ExtractOneStreamFileSystemCsvAsDataTable(
    si,
    FileSystemLocation.ApplicationDatabase,
    "imports/data.csv",
    headersIncluded: true
);

Loading Methods

To OneStream Databases:

// Load DataTable to a SQL table (table name comes from dt.TableName)
XBRApi.Etl.LoadTableToOneStreamDatabase(si, "MyDataSource", dt, overwriteOk: true);

// With explicit load options
XBRApi.Etl.LoadTableToOneStreamDatabase(
    si, 
    "MyDataSource", 
    dt, 
    BlendTableLoadTypes.DropAndRecreate, 
    BlendTableIndexTypes.MirrorDataTableIndexes
);

To MetaFileSystem:

// Load as Parquet
XBRApi.Etl.LoadParquetToMetaFileSystem(
    si, 
    MetaFileSystemLocation.Shared, 
    "output/results.parquet", 
    dt,
    overwriteOk: true
);

// Load as CSV
XBRApi.Etl.LoadCsvToMetaFileSystem(
    si,
    "sql-server-shared",
    "output/results.csv",
    dt,
    includeHeaders: true,
    delimiter: ','
);

To OneStream FileSystem:

// Load as Parquet
XBRApi.Etl.LoadParquetToOneStreamFileSystem(
    si, 
    FileSystemLocation.ApplicationDatabase, 
    "exports/data.parquet", 
    dt
);

// Load as CSV
XBRApi.Etl.LoadCsvToOneStreamFileSystem(
    si,
    FileSystemLocation.ApplicationDatabase,
    "exports/data.csv",
    dt,
    includeHeaders: true
);

Choosing Your Approach

Scenario	Recommended Approach
Simple extract or load to/from a known location	Convenience methods (`ExtractMetaFileSystemParquetAsDataTable`, etc.)
Moving data between two locations	ETL Manager with `ExtractAndLoad()`
Complex pipeline with intermediate transformations	ETL Manager with separate `Extract` and `Load` calls
Large datasets where memory is a concern	Use `IDataReader` extraction methods
Non-tabular files (JSON, images, PDFs)	Unstructured ETL Manager

Putting It Together

Here's a complete example that stages OneStream data in the MetaFileSystem for AI processing:

// 1. Extract customer data from OneStream
DataTable customerData = XBRApi.Etl.ExtractOneStreamDatabaseTableAsDataTable(
    si, 
    "Application", 
    "CustomerMetrics"
);

// 2. Stage it in the MetaFileSystem as Parquet for AI consumption
XBRApi.Etl.LoadParquetToMetaFileSystem(
    si,
    MetaFileSystemLocation.Shared,
    "ai-staging/customer_metrics.parquet",
    customerData,
    overwriteOk: true
);

// 3. Later, after AI processing, bring results back
DataTable predictions = XBRApi.Etl.ExtractMetaFileSystemParquetAsDataTable(
    si,
    MetaFileSystemLocation.Shared,
    "ai-output/customer_predictions.parquet"
);

// 4. Load predictions into OneStream for reporting
predictions.TableName = "CustomerPredictions";
XBRApi.Etl.LoadTableToOneStreamDatabase(si, "Application", predictions, overwriteOk: true);

ETL Capabilities in XBR​

The DataDefinition Model​

Data Storage Landscape​

Connection Contexts​

Data Container Contexts​

Data Formats​

Creating DataDefinitions​

ETL Managers​

Tabular ETL Manager​

Unstructured ETL Manager​

Convenience Methods​

Extraction Methods​

Loading Methods​

Choosing Your Approach​

Putting It Together​

ETL Capabilities in XBR

The DataDefinition Model

Data Storage Landscape

Connection Contexts

Data Container Contexts

Data Formats

Creating DataDefinitions

ETL Managers

Tabular ETL Manager

Unstructured ETL Manager

Convenience Methods

Extraction Methods

Loading Methods

Choosing Your Approach

Putting It Together