Skip to main content
Question

Silent Data Loss from SQL Writer: Need Error on Type Mismatch


ecx
Supporter
Forum|alt.badge.img+4
  • Supporter

I've run into this issue multiple times now, and it's quite serious. My SQL table writer was set to automatic and selected TINYINT as the datatype. However, one of the values I attempted to write was 5000, which exceeds the valid range for TINYINT. As a result, I expected 57 rows to be written, but only 51 actually made it into the database.

 

Because the system only throws a warning (and buries it in a long list of logs), this kind of data loss can easily go unnoticed unless you're scanning through potentially hundreds of warnings.

 

The warning I wish to flag or somehow cause the workbench to fail if it occurs:

2025-07-17 14:15:40| 0.5| 0.0|WARN |Microsoft SQL Server Non-Spatial Writer: Failed to parse Unsigned TinyInt' from attribute value 5000' for column `How many kilolitres (kL) of liquid waste was generated?'
 

Silent data loss is extremely dangerous in data pipelines. How can I flag this better. I am quite surprised FME doesn’t do this by default. I am sure this has impacted many people other than myself, other people on my team have also had this same issue.
 

Has anyone found a workaround or a way to force an error on this kind of type mismatch?

4 replies

ebygomm
Influencer
Forum|alt.badge.img+39
  • Influencer
  • July 17, 2025

What I find frustrating is that depending on the underlying database these sorts of errors are handled in different ways. 

I may be misremembering but I’m pretty sure that if you try and write a value that is too long into a text field

  • Oracle truncates it by default
  • MSSQL skips the whole record
  • Postgres causes the workspace to terminate 

In the advanced parameters for Oracle formats there’s an option to Enforce Strict Attribute Conversion

Enforce Strict Attribute Conversion

This parameter specifies how the writer proceeds when a problem arises while converting a value from one of a feature’s attributes to an Oracle column value.

Examples of such problems would be the truncation of a string value to fit into the target character column, an error in writing a non-numeric attribute to a numeric column, or an error converting an FME geometry into an SDO_GEOMETRY value.

  • Yes: The conversion errors result in warnings. Features are logged and dropped from the translation.
  • No: The writer will silently write null values or truncated strings when conversion errors occur.
  • Warn: The writer warns and writes null values or truncated strings when conversion errors occur.

Ideally, I'd like to see the same sort of options across all database writers, with an additional option of a rejected port that reports features that are dropped.

 

I understand though, that the variation in functionality is down to how the DB itself reports and handles the errors so the functionality in FME is limited by that.

 

I resorted to checking values against a schema prior to writing when I encountered a similar issue but I agree it’s easy for people to be caught out by this, and it’s also why the superfluous warning messages that seem to have crept into FME Flow 2024 are irritating. Do you check every job with a warning to check if it’s the ‘normal’ warning you can ignore or a new warning that might actually require some action.


ebygomm
Influencer
Forum|alt.badge.img+39
  • Influencer
  • July 17, 2025

If you want to terminate the workspace on this sort of error could you use the log message streamer to look for the parse warning and send to a terminator? (nb. I’ve not tried this)


jt
Contributor
Forum|alt.badge.img+3
  • Contributor
  • July 17, 2025

In my experience, the safest thing to do is first write data to a staging table in the database. Once this is complete, then immediately read the dataset back out of the staged table into FME and perform a full comparison between the ‘before’ and ‘after’ datasets. I find the Matcher transformer to be quite effective here. While this is obviously resource intensive, it not only allows you to detect if records have been silently dropped by the target database, but it also allows you to detect if the database has truncated or rounded data in ways you did not intend.

If you are not concerned about truncating/rounding discrepancies, a simpler approach is to just compare record counts before and after.

Either way, if these data completeness checks pass, then you can proceed with loading the data from the database staging table into the main table.

I know this is more to configure, but this design pattern is generic (can use it for any target data format) and frees you and Safe from the expectation of perfect logging.


ecx
Supporter
Forum|alt.badge.img+4
  • Author
  • Supporter
  • July 17, 2025
jt wrote:

In my experience, the safest thing to do is first write data to a staging table in the database. Once this is complete, then immediately read the dataset back out of the staged table into FME and perform a full comparison between the ‘before’ and ‘after’ datasets. I find the Matcher transformer to be quite effective here. While this is obviously resource intensive, it not only allows you to detect if records have been silently dropped by the target database, but it also allows you to detect if the database has truncated or rounded data in ways you did not intend.

If you are not concerned about truncating/rounding discrepancies, a simpler approach is to just compare record counts before and after.

Either way, if these data completeness checks pass, then you can proceed with loading the data from the database staging table into the main table.

I know this is more to configure, but this design pattern is generic (can use it for any target data format) and frees you and Safe from the expectation of perfect logging.

This is a good idea, a generic checking workflow, or custom transformer. I might have to do this. Thanks.

Although I do believe this should be addressed by SAFE. If a user is intending to write 100 records to a table and only 98 get written, this is obviously something the user does not want to happen, and should be flagged appropriately with an error.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings