Data type fidelity across ecosystem tools¶
A successful connection or matching row count does not establish that an ETL pipeline preserves values. Check the source value, connector metadata, intermediate representation and destination value separately. A mapping that preserves a decimal as text is useful, but it does not provide native decimal arithmetic in the intermediate engine.
Define the value contract¶
Record the MatrixOne version or commit, source version, driver and tool versions, SQL mode, session time zone, input schema and destination schema. State whether the pipeline uses default inference or explicit mappings. Qualify read and write directions independently.
For each field, choose an acceptance rule before loading data: exact integer or decimal value, identical binary bytes, temporal value and precision, or documented floating-point tolerance. State any normalization of JSON, SET members or CHAR trailing spaces. Keep both the original value and its normalized representation so a comparison does not hide a conversion.
Choose explicit mappings¶
SQL value |
Intermediate representation |
Acceptance rule |
|---|---|---|
DECIMAL with precision above 38 |
A decimal type supporting the declared precision, or an explicit decimal text column |
Preserve sign, declared scale and every significant digit. Converting through float64 is insufficient. |
BIGINT UNSIGNED |
uint64, a sufficiently wide decimal, or exact integer text |
Include |
BIT(64) |
uint64, bytes with a declared byte order, or exact decimal text |
Check high bits and the all-ones value; preserve NULL separately. |
FLOAT and DOUBLE |
float32 and float64 respectively |
Record the chosen tolerance. Six significant digits for FLOAT are a semantic check, not a bitwise guarantee. |
BINARY, VARBINARY and BLOB |
Bytes or HEX with explicit decoding |
Compare byte lengths and bytes, including |
TIME with negative values or values above 24 hours |
Signed duration in integer microseconds or exact SQL time text |
Preserve sign and fractional seconds. A time-of-day object cannot represent the full SQL duration domain. |
DATE and DATETIME |
A representation supporting the input range and precision |
Test boundary years and microseconds; do not assume a DataFrame timestamp supports every SQL date. |
TIMESTAMP |
Timestamp with an explicit time-zone contract |
Record source and sink session time zones and UTC conversion. A DATETIME check does not qualify TIMESTAMP behavior. |
CHAR |
Text with an explicit trailing-space policy |
Preserve the original bytes if spaces matter; SQL comparison semantics may differ from byte equality. |
ENUM and SET |
Explicit member text or an agreed representation |
Distinguish NULL, empty string and empty set. Record SET member ordering and enum-domain enforcement. |
JSON |
JSON document or exact document text |
Distinguish SQL NULL, JSON |
UUID |
Native UUID or validated UUID text |
A test using CHAR(36) does not qualify the MatrixOne UUID type or driver UUID inference. |
Unicode text |
UTF-8 text with a declared normalization policy |
Include non-ASCII text, emoji, quotes, delimiters and newlines. |
See DECIMAL, JSON, SET and UUID for SQL type behavior. Limits depend on the server release and on every intermediate engine.
Exact decimal values¶
For a server supporting DECIMAL256, use an exact text input and inspect the returned decimal without converting it to a binary float:
SELECT CAST('9007199254740993.123456789012345678901234567890' AS DECIMAL(65,30)) AS exact_value;
The result must preserve the input value and 30 fractional digits. If a Python adapter uses decimal.Decimal, set a sufficient decimal context in each worker before transformations; a context adequate for ordinary monetary values may round a 65-digit value.
Unsigned integer values¶
SELECT CAST('18446744073709551615' AS UNSIGNED) AS unsigned_value;
The result must remain the exact integer. Inspect both the driver value and the reflected column type before constructing a DataFrame or cloning a table definition. An explicit CAST-to-text read can preserve the value while leaving default metadata inference unqualified.
Binary values¶
SELECT HEX(UNHEX('0000FF80')) AS binary_hex;
The expected result is 0000FF80. HEX is an explicit transport encoding; decode it back to bytes before comparing the destination.
SQL durations¶
SELECT CAST('-37:00:00.000001' AS TIME(6)) AS negative_duration;
The result represents minus 37 hours and one microsecond. When using integer microseconds, calculate the signed duration rather than taking only the positive seconds component of a language duration object.
JSON null and invalid empty text¶
SELECT JSON_VALID('null') AS valid_json_null, JSON_VALID('') AS valid_empty_document;
The expected flags are 1 and 0. JSON null is a valid document. Preserve SQL NULL separately, and test the connector’s CSV/JSONLines null markers rather than assuming an empty field has the same meaning.
Check values after loading¶
Keep a deterministic source dataset with stable keys and boundary values.
Read through the actual tool API and record its types, NULL handling and any explicit conversion.
Write through the intended sink, then read the destination through a fresh connection.
Compare the key set, row multiplicity and all field values. Use counts and hashes only after defining a deterministic ordering and typed encoding.
Repeat after batching, parallel partitions, retry and schema changes. Preserve failed inputs and intermediate files for diagnosis.
Validate malformed input as well as valid input: an incomplete final JSONLines record, decimal overflow, invalid enum member or out-of-range time should produce a documented result. A successful job must not silently omit records.
Separate component failures from server failures¶
Run a minimal SQL probe and a MySQL control with the same logical values where the type domains overlap. Reproduce a file-format or Arrow transformation failure without SQL when possible. Preserve the original error before trying an adapter.
Historical compatibility reports include Arrow/Parquet mapping profiles, UInt64 metadata, empty ENUM/SET file loading and decimal parsing. Check affected versions and fix availability; an issue link is not a statement that every current release has the problem.