Feature writer - feature types order

This is more from observation than from technical documentation, but FME uses workspace optimisation that tries to purge Features held in memory as early as possible by finalising their output to a Writer.

This optimisation will lead to some Feature Types being written in a different order than what is ordered within the Writers or within a FeatureWriter and is dependent on which workflow pathways FME feels it can resolve and finish with “first”.  Your example of going from bottom-to-top is coincidental, in my experience, it can happen in any order due to the above memory usage optimisation techniques used by default.

Some strategies are explained here and similar to what I have generally done, by using things like separate Writers for particular Feature Types, or Transformers that block things from writing until say all features that are needed are present (say into the Input Port of a FeatureHolder) or similarly using a FeatureJoiner that in its behaviour won’t allow anything on the LHS to be output to a downstream writer until all RHS features have been received, or Sorters to block writing and prioritise the order of write within a Writer.

Write Feature Types in a Specific Order

If you have a common identifier that links the parent and child features, you can use it as a Group By in the FeatureWriter. This will ensure that the parent and child records are written in a single transaction.

However, be aware that this will require more available memory, as FME will have to cache all the records in memory before writing them. You can mitigate this using the “Complete Groups = When Group Changes”, but it will require that your features arrive in the correct order to avoid problems.

Check the FeatureWriter documentation for more details.

I would give a try to disabling and enbling FK constraints for the Feature writers as is in Example 3
But rather I’ll use “SQL to Run before write” and “After”. I dont want to overcomplicate with addition succesive writers as im already dealing with 3 different RDMS in one workspace.

disabling and enbling FK constraints

I can’t do that, because I don’t have the rights to do that, so I have to make sure the order is correct. That is usually the fastest way, especially with lots of features, but I only have the rights for DML, DDL is out of the question.

Disabling FK constraints is somewhat risky unless you’re the sole source of modification to these tables. If FME fails for some reason, there’s a risk of having tables without foreign key constraints until the problem is noticed and fixed. This can even impact query plans (depending on the DB) and lead to a performance degradation.

My personal preference is to import important data to a staging (“scratch”) schema without constraints, where validation occurs. The records are then transferred to the production database using regular SQL and “proper” transaction control for each logical object collection. This is usually very fast as you avoid reading the data back and forth to FME, it all stays within the database. FME is great for orchestrating all of this.