Is there an easy way to tell when a particular record was written to the Data Lake?

Apologies if this was already asked and answered. I looked through some posts but did not see.

At the start of the week a non production tenant's data was wholly refreshed, after that task was done the data lake for that tenant was purged. The issue a colleague of mine is running into has to do with MITBAL records in the MITBAL table in the data lake, that is not in M3. There are 444 oneview records of writes to the data lake on the day that initial loads were run on that table. I'm just wondering if there is an easier way to find exactly when a particular record was written to the data lake?

Find more posts tagged with

Infor Data Fabric

Accepted answers

Legacy Contributor

Hey Ben,

Was the purge done via the Purge UI, Atlas, or the Purge APIs? My most immediate thought is that this may have been purged via APIs which has a subsequent administrative step needed - infor.Clear_Data().

It sounds like while the data objects were purged, the cache Compass uses to query against wasn't removed and so you still may see those results present in your queries. That could explain why you're seeing some records in MITBAL when querying via Compass SQL that don't exist in M3.

There's a few ways to validate and learn more about the challenge. Compass has a suite of "synthetic functions" that retrieve corresponding metadata about a given row in your query results. Using the infor.dataObjectID() function, you could find the address of the data object for one of your suspicious rows.

SELECT infor.dataObjectID(), * FROM MITBAL WHERE...

You'll see the address from which the record came from and so you can copy that to your clipboard and navigate over to Atlas. Filter for your data object and in the header row, paste the data object ID.

In my particular example above, I was able to find my data object by address and so it's a pretty good signal that Compass is in sync with the storage layer here.

However, if you notice that Atlas returns 0 data objects matching that address, then that's a pretty strong indication that Compass has cached information that needs to be re-synced with what's in storage. As I mentioned, this is something that can be expected if purging by the APIs but is often automatically handled for users when purging via the UI or Atlas.

sneustadt

We actually had a very similar issue where it appeared that some records were not being purged when the purge was run via the Purge UI. As Mike K just stated, it turned out that we needed to do the infor.Clear_Data in order to completely purge the tables. An easy way we found to do this was by going to this option in the Data Fabric Compass. Clicking the Remove Formatted Data option here will automatically insert the the code that is needed to purge this data.

All comments

Legacy Contributor

Do you see a "lastModified" field in the raw data in oneview?

Legacy Contributor

Hey Ben,

SELECT infor.dataObjectID(), * FROM MITBAL WHERE...

In my particular example above, I was able to find my data object by address and so it's a pretty good signal that Compass is in sync with the storage layer here.

Legacy Contributor

I missed an opportunity to address the more direction question, @ben-pope . My bad

You can see when your data was successfully stored to the Data Lake using Compass' infor.lastModified() function. The timestamp returned in this column corresponds to the time and date of when the data object that contained that row of data was stored.

It's a synthetic function as well and interfaces with the storage layer of Data Lake. It can be helpful in reconciling or understanding when data arrived and is often used for incremental data loading patterns. I wouldn't recommend referencing the timestamps inside of the table results that M3 replicates - like lmts, for example. That timestamp reflects when the data was entered into the M3 table but that's not an indicator of when it was sent or received by Data Lake.

sneustadt

ben-pope

Mike, thanks so much for the answer and even more so for the awesome information. I looked at two of the items in question and they did not appear in Atlas using the Object ID which, if I'm following you, would mean that the items are coming from the cache. Would you happen to know off hand where these nifty synthetic functions would be documented? They seem fairly useful. The purge was done in the Atlas UI.

ben-pope

Scott, this is exactly what must have happened. Thanks so much for the information!

Legacy Contributor

Hey Ben,

Of course - happy to help. If you're not seeing the object in Atlas, then 99% confident this is a case of running the infor.CLEAR_DATA() stored procedure and having Compass rebuild its cache based on what's in storage.

This isn't necessarily a bug in behavior but certainly an opportunity for us to make improvements on our side to orchestrate this process better. We have that in our roadmap to better manage the cache when using the purge APIs.

Re: documentation, you can grab the Data Fabric documentation by navigating through https://docs.infor.com > Technology Platform > Operating Service > Infor OS (Cloud) Version 2023.x Documentation List > Infor Data Fabric User Guide

Admittedly, that's annoying to have to sort through

You could just bookmark the link above or, from the Data Fabric home page, click the link for "Learn About Data Fabric" which takes you to the documentation.

ben-pope

So, I tried putting all of the tables in there

exec INFOR.CLEAR_TABLE('ACLHED', 'True');

exec INFOR.CLEAR_TABLE('ACLSPC', 'True');

something like that.

It doesn't like that. If I try to add more than one table in the query window an error is thrown

Process ERROR 202Error message: The exec name is a keyword. Enclose table or column names that are keywords with double quotes.

Any trick that I am missing here?

If no way to send a bunch in one go from the UI I can set up a postman collection and use a runner to do it through the API's ... but it seems a little overkill for the task.

Legacy Contributor

Hey Ben,

You'll need to run each statement individually. You're not wrong - it's a pain. We're currently working through a design to improvement the stored procedure behaviors to support multiple arguments and other wildcarding capabilities.

It's in our roadmap backlog at the moment and I'll be sure to update our internal notes about your feedback which helps elevate attention

Copyright © 2025 Infor. All rights reserved.