CrossjoinSync Type Translation Guide

This guide explains how CrossjoinSync translates data types between source and destination providers, and how to safely customize behavior in type-mappings.json.

Why Type Translation Exists

Different databases use different type systems. For example:

• Oracle NUMBER does not map 1:1 to SQL Server numeric types
• SQL Server UNIQUEIDENTIFIER has no native GUID type in some targets
• Timestamp semantics differ across SQL Server and Snowflake

CrossjoinSync resolves this during schema generation (create-destination) by applying provider-pair mapping rules.

Where Translation Rules Live

• Mapping configuration: CrossjoinSync/type-mappings.json
• Mapping engine: CrossjoinSync/Services/TypeMappingService.cs

The mapping file is copied to output/publish locations and read at runtime.

Mapping Model

Mappings are grouped by provider pair key:

• oracle:sqlserver
• oracle:snowflake
• sqlserver:snowflake
• snowflake:sqlserver
• odbc:sqlserver
• odbc:snowflake

Each key contains an ordered list of rules. First match wins.

Rule Schema

Each rule supports:

• match (required): pattern to match source type name
• matchType (optional): exact, prefix, or regex (default is exact)
• target (required): destination type expression
• defaultSize (optional): fallback for {size}
• defaultPrec (optional): fallback for {prec}
• defaultScale (optional): fallback for {scale}
• note (optional): documentation only

Example rule:

{ "match": "^NUMBER$", "matchType": "exact", "target": "decimal({prec},{scale})", "defaultPrec": 38, "defaultScale": 4 }

Placeholder Substitution

target can include placeholders:

• {size}
• {prec}
• {scale}

Values come from source metadata when available:

• ColumnSize
• NumericPrecision
• NumericScale

If metadata is missing, CrossjoinSync uses rule defaults, then internal fallbacks.

Default Behavior and Fallbacks

When values are missing:

• Precision falls back to 38
• Scale falls back to 4
• Size uses defaultSize where provided; otherwise {size} may resolve to MAX in some targets

This means your defaultPrec/defaultScale/defaultSize choices in JSON directly affect generated DDL.

How to Change Mappings Safely

1. Open CrossjoinSync/type-mappings.json.
2. Find the provider pair you need to change.
3. Add or edit rules in that block.
4. Keep more specific rules above more general rules.
5. Save the file.
6. Run create-destination for a test extract and validate generated DDL.

Recommended change process

• First, test with output-to-file mode:

.\CrossjoinSync.exe create-destination --job DailySync --file .\sql\preview-ddl.sql --source

• Review type outputs in the SQL file.
• Then run create-destination without --file when satisfied.

Common Customizations

1) Change default precision/scale for numerics

If you want Oracle NUMBER to default to scale 2 instead of 4, update:

{ "match": "^NUMBER$", "matchType": "exact", "target": "decimal({prec},{scale})", "defaultPrec": 38, "defaultScale": 2 }

2) Force string lengths

To make SQL Server VARCHAR map to Snowflake VARCHAR(1000) by default:

{ "match": "^VARCHAR$", "matchType": "regex", "target": "VARCHAR({size})", "defaultSize": 1000 }

3) Add a new type mapping

Add a new rule near the top of the relevant provider pair:

{ "match": "^XMLTYPE$", "matchType": "exact", "target": "VARIANT" }

Rule Ordering Guidance

Because first match wins:

• Put exact and special-case rules first
• Put broad regex rules later
• Keep one clear fallback pattern at the end where needed

Bad ordering can cause broad rules to swallow specific ones.

Troubleshooting

If translated types are not what you expect:

• Confirm the provider pair is correct (from:to)
• Confirm your matchType aligns with your match pattern
• Check for a broader earlier rule that matched first
• Use --file + --source to inspect generated DDL and source-type comments

Practical Notes

• Changes in type-mappings.json affect schema generation, not extract command syntax.
• Keep the file valid JSON; malformed JSON causes the mapper to fall back and may leave types untranslated.
• Version-control your mapping changes so you can compare and roll back safely.