Migration REST service

The Migration REST service assists in migrating an in-memory Atoti FRTB deployment to one that uses DirectQuery. It generates the SQL schema for the remote database and exports the in-memory data so it can be loaded there. The REST service is available at: host:port/frtb-starter/migrationHelper/ The service provides three endpoints:

SQL Schema Generation - Generate DDL scripts without exporting data
Database Template Extraction - Export data for a specific database using project defaults
Custom Database Extraction - Export data with custom store and field configuration

Supported databases: ClickHouse, Databricks, MSSQL, Snowflake.

Security configuration

The security configuration is handled by the dqMigrationHelperFilterChain bean defined in the FRTBSecurityFilterChainsConfig. The default security config limits the use of this service to only ADMIN users.

Configuration

The migration export can be enabled or disabled via the following property:

Property	Default	Description
`frtb.migration.export.enabled`	`true`	Whether the migration REST controller is registered in the Spring context.
`frtb.migration.export.base-dir`	`${java.io.tmpdir}`	Allowed base directory for CSV file exports. Requests outside this path are rejected.

SQL schema generation

Generates the SQL DDL script (CREATE DATABASE, CREATE SCHEMA, CREATE TABLE, foreign keys) from the in-memory schema without exporting any data. URL: host:port/frtb-starter/migrationHelper/sqlSchema Request Type: GET Query Parameters:

Parameter	Required	Description
`database`	No	Target database type (`SNOWFLAKE`, `CLICKHOUSE`, `DATABRICKS`, `MSSQL`).
`databaseName`	No	Name of the database in the generated DDL.
`schema`	No	Name of the schema in the generated DDL.

When a parameter is not provided, the following fallback chain is applied:

Parameter	1st fallback	2nd fallback	Default
`database`	`directquery.database.type`	-	`SNOWFLAKE`
`databaseName`	`directquery.database.<type>.database`	`spring.application.name`	`FRTB_DATABASE`
`schema`	`directquery.database.<type>.schema`	`spring.application.name`	`FRTB_SCHEMA`

Examples:

GET host:port/frtb-starter/migrationHelper/sqlSchema
GET host:port/frtb-starter/migrationHelper/sqlSchema?database=CLICKHOUSE
GET host:port/frtb-starter/migrationHelper/sqlSchema?database=SNOWFLAKE&databaseName=PROD_DB&schema=FRTB

Database template extraction

Exports data using the project defaults (SA and IMA cubes, lookup stores) for the specified database type. URL: host:port/frtb-starter/migrationHelper/extractForDatabase/{database} Where {database} is one of: clickhouse, databricks, mssql, snowflake. Request Type: POST Body: LocalFileOutputHandler

{
    "outputDirectory": "path/to/extraction/directory",
    "compressionType": "GZIP"
}

The response body contains the generated SQL DDL script for the extracted schema.

Custom database extraction

A custom extraction can be provided to export specific stores and datastore schemas. User-provided values are merged on top of the project defaults: user fields take precedence, omitted fields inherit the defaults. URL: host:port/frtb-starter/migrationHelper/extract Request Type: POST Body: DatabaseExtractionDTO Example:

{
    "databaseType": "SNOWFLAKE",
    "onlyIncludeStarSchemaStores": true,
    "cubeSchemasToExport": ["StandardisedApproachCube", "InternalModelApproachCube"],
    "extraStores": [
        "DeskDescription", "FXRates",
        "LegalEntityParentChild", "BookParentChild",
        "PublicSectorObligorRiskWeights",
        "TrancheOverrides", "ObligorOverrides",
        "RiskFactorDescriptionOverrides", "RRAOOverrides",
        "UnderlyingDescriptionOverrides",
        "Scenarios", "EsScenarioFxRates"
    ],
    "localFileOutputHandler": {
        "outputDirectory": "path/to/extraction/directory",
        "compressionType": "GZIP"
    }
}

DDL-only mode: When the localFileOutputHandler is omitted from the request body, the endpoint skips the data export and only returns the SQL DDL script. This is useful for previewing the generated schema without writing any files.

Populating hierarchy tables from parent-child stores

The generated DDL creates BOOK_HIERARCHY and LEGAL_ENTITY_HIERARCHY tables. These are the join targets used by the cubes’ organizational hierarchies. In the in-memory accelerator, these tables are computed by Atoti FRTB from the parent-child input data (BOOK_PARENT_CHILD, LEGAL_ENTITY_PARENT_CHILD). In DirectQuery mode, the equivalent transformation must happen in the remote database. The migration helper exports the parent-child stores as the source of truth, but populating the flattened hierarchy tables is left to the operator. Two equivalent patterns are available as drop-in reference scripts (15-level book hierarchy, 5-level legal-entity hierarchy, Snowflake syntax; adjust depth and dialect per project):

Views + materialization: one intermediate view per hierarchy depth, unioned into a final view, then persisted as the hierarchy table. See hierarchy-from-views.sql.
INSERT ... SELECT query: a single statement per hierarchy with one UNION branch per depth. See hierarchy-from-queries.sql.

Without this step, queries against the organizational hierarchies will return empty results. The migration helper produces the table schema but not the derivation logic, which is project- and database-specific.

Sign-Off fields in the generated DDL

The default extraction preserves whichever Sign-Off fields are present in the in-memory schema. _Version is always excluded (it is an in-memory task-tracking column with no DQ equivalent); Source and Input type flow through, matching the DQ-with-adjustments deployment shape. For Sign-Off without adjustments in DQ, override fieldsToExclude to drop them too.

Request objects

`DatabaseExtractionDTO`

Parameter	Description
`databaseType`	One of the supported databases (all uppercase): `CLICKHOUSE`, `DATABRICKS`, `MSSQL`, `SNOWFLAKE`.
`onlyIncludeStarSchemaStores`	When `true`, exports only the stores that are part of the star schema.
`cubeSchemasToExport`	The cube schemas to export.
`extraStores`	Additional stores to export (stores not in the star schema).
`storesToIgnore`	Stores to exclude from the extraction.
`vectorStoreBehaviours`	Per-store vector emulation mode (`ROW`, `ROW_SAME_TABLE`, `NATIVE`, `DEFAULT`).
`fieldsToExclude`	Per-table field exclusions. A map from table name to the set of field names to exclude from both DDL and exported data.
`autoRowSameTable`	When `true`, vector tables with only key and vector fields are automatically promoted to `ROW_SAME_TABLE`. Defaults to `false`.
`localFileOutputHandler`	The output handler. This writes the results into the local file system. When omitted, only the DDL is returned.
`performanceModifiers`	Performance tuning options. See PerformanceModifiers.
`sortedOutput`	When `true`, exported rows are sorted before writing. Cannot be combined with `performanceModifiers.isStreamingResultsToFile`.

`PerformanceModifiers`

Parameter	Default	Description
`isStreamingResultsToFile`	`true`	Stream rows directly to file instead of buffering in memory.
`parallelProcessingOfSingleFiles`	`true`	Process individual export files in parallel.
`minVerticalVectorFiles`	`10`	Number of files to split vector rows into when using `ROW` vector emulation.

`LocalFileOutputHandler`

Parameter	Description
`outputDirectory`	Where to output the CSV files. This directory must already exist. Validated against `frtb.migration.export.base-dir`.
`compressionType`	The compression to apply to the file. Either `NONE` or `GZIP`.

​Security configuration

​Configuration

​SQL schema generation

​Database template extraction

​Custom database extraction

​Populating hierarchy tables from parent-child stores

​Sign-Off fields in the generated DDL

​Request objects

​DatabaseExtractionDTO

​PerformanceModifiers

​LocalFileOutputHandler

Security configuration

Configuration

SQL schema generation

Database template extraction

Custom database extraction

Populating hierarchy tables from parent-child stores

Sign-Off fields in the generated DDL

Request objects

`DatabaseExtractionDTO`

`PerformanceModifiers`

`LocalFileOutputHandler`