Data Lake Transfer Gateway

This feature is only available for Enterprise customers. To activate it, you must contact TilliT Support.

Architecture

Interval and file structure

The export interval can be either once an hour or once a day. The data format can be the following options:

/yyyy/mm/dd/site/{data-type}.csv // For daily exports

/yyyy/mm/dd/HH/MM/site/{data-type}.csv // For hourly exports

The date/time of the files are against the update-at date of the record.

Files are generated in CSV format. All tables carry a unique primary key (ID). This key can be used to merge the data when ingesting since the same primary ID record may appear in multiple files in case the record changes.

Export Schema

Activity Instance

Activity Instance is the header of a form submitted. We will only export data for forms that have been submitted or superseded.

You will find here the reference to the order and asset in which this activity was submitted.

Field	Description	Data Type
id	Activity instance ID	INTEGER
created_at	Creation timestamp	TIMESTAMP
updated_at	Update timestamp	TIMESTAMP
activity_name	Activity name	VARCHAR
activity_key	Activity key	VARCHAR
candidate_group	Candidate group	VARCHAR
activity_pass	Activity pass status	BOOLEAN
retest	Retest status	BOOLEAN
activity_comments	Activity comments	TEXT
completed_at	Completion timestamp	TIMESTAMP
scheduled_at	Scheduled timestamp	TIMESTAMP
expires_at	Expiration timestamp	TIMESTAMP
assignee	Assignee	VARCHAR
activity_status	Activity status	VARCHAR
order_id	Order ID	INTEGER
asset_id	Asset ID	INTEGER
activity_class_name	Activity class name	VARCHAR
starting_event_id	Starting event ID	INTEGER
parent_activity_instance_id	Parent activity instance ID	INTEGER
display_order	Display order	INTEGER
priority	Priority	INTEGER
shift_id	Shift ID	INTEGER
claimed_at	Claimed timestamp	TIMESTAMP
asset_name	Asset name associated with activity	VARCHAR
order_number	Order number associated with activity	VARCHAR
order_name	Order name associated with activity	VARCHAR

Activity Instance Item

This table contains the answers to the questions submitted by the user.

Field	Description	Data Type
id	Activity instance item ID	INTEGER
created_at	Creation timestamp	TIMESTAMP
updated_at	Update timestamp	TIMESTAMP
name	Item name	VARCHAR
item_key	Item key	VARCHAR
string_value	String value	VARCHAR
value	Raw value	VARCHAR
numeric_value	Numeric value	NUMERIC
upper_limit	Upper limit	NUMERIC
lower_limit	Lower limit	NUMERIC
target	Target value	NUMERIC
assignee	Assignee	VARCHAR
pass	Pass status	BOOLEAN
display_order	Display order	INTEGER
tolerance_type	Tolerance type	VARCHAR
activity_instance_id	Activity instance ID	INTEGER
process_variable_id	Process variable ID	INTEGER
date_value	Date value	DATE
datetime_value	DateTime value	TIMESTAMP
activity_key	Activity key associated with the activity item	VARCHAR
activity_name	Activity name associated with the activity item	VARCHAR
asset_name	Asset name associated with the activity item	VARCHAR
order_number	Order number associated with the activity item	VARCHAR
order_name	Order name associated with the activity item	VARCHAR
process_variable_name	Process variable name	VARCHAR

Events

This represents detailed records of events, including their timestamps, types, priorities, associated assets, orders, and statuses and additional metadata. All dates are in UTC formatted as ISO.

Field	Description	Data Type
id	Event instance ID	INTEGER
created_at	Creation timestamp	TIMESTAMP
updated_at	Update timestamp	TIMESTAMP
event_name	Event name	VARCHAR
event_subtitle	Event subtitle	VARCHAR
priority	Event priority	INTEGER
event_timestamp	Event timestamp in UTC	TIMESTAMP
closed_at	Event closure timestamp in UTC	TIMESTAMP
status	Event status	VARCHAR
asset_id	Asset ID	INTEGER
event_type_id	Event type ID	INTEGER
order_id	Order ID	INTEGER
activity_instance_id	Activity instance ID	INTEGER
activity_key	Activity key	VARCHAR
change_over	Change over status	BOOLEAN
event_reason_1	Event reason 1	VARCHAR
event_reason_2	Event reason 2	VARCHAR
event_reason_3	Event reason 3	VARCHAR
event_reason_4	Event reason 4	VARCHAR
downtime_type	Downtime type	VARCHAR
event_comments	Event comments	TEXT
triggered_by	Triggered by	VARCHAR
duration	Duration in minutes (if closed)	INTERVAL
event_type_name	Event type name	VARCHAR
event_type_priority	Event type priority	INTEGER
event_type_display_order	Event type display order	INTEGER
asset_name	Asset name associated with event	VARCHAR
order_number	Order number associated with event	VARCHAR
order_name	Order name associated with the event	VARCHAR

Order instance

Represents detailed information about orders, including timestamps, quantities, and associated assets and materials.

Field	Description	Data Type
id	Order instance ID	INTEGER
created_at	Creation timestamp	TIMESTAMP
updated_at	Update timestamp	TIMESTAMP
order_name	Order name	VARCHAR
order_date	Order date	DATE
order_number	Order number	VARCHAR
batch_number	Batch number	VARCHAR
quantity_target	Target quantity	NUMERIC
scheduled_start	Scheduled start timestamp in UTC	TIMESTAMP
scheduled_end	Scheduled end timestamp in UTC	TIMESTAMP
actual_start	Actual start timestamp in UTC	TIMESTAMP
actual_end	Actual end timestamp in UTC	TIMESTAMP
default_run_rate	Default run rate	NUMERIC
order_status	Order status	VARCHAR
asset_id	Asset ID	INTEGER
material_id	Material ID	INTEGER
uom_id	Unit of measure ID	INTEGER
operation	Operation	VARCHAR
notes	Notes	TEXT
due_date_time	Due date and time	TIMESTAMP
previous_order_id	Previous order ID	INTEGER
scheduled_changeover_duration	Scheduled changeover duration	NUMERIC
actual_changeover_start	Actual changeover start timestamp	TIMESTAMP
actual_changeover_end	Actual changeover end timestamp	TIMESTAMP
asset_name	Asset name	VARCHAR
material_name	Material name	VARCHAR
material_external_id	Material external ID	VARCHAR

Order Performance

Tracks performance metrics of orders on a hourly bucket. It includes the sum of quantities completed and rejected, active and downtime minutes, and rate data. They provide the source data to calculate OEE.

Field	Description	Data Type
id	Order performance ID	INTEGER
asset_id	Asset ID	INTEGER
parent_id	Parent asset ID	INTEGER
order_id	Order ID	INTEGER
shift_id	Shift ID	INTEGER
material_id	Material ID	INTEGER
quantity_completed	Quantity completed	NUMERIC
quantity_rejected	Quantity rejected	NUMERIC
active_minutes	Active minutes	INTERVAL
running_minutes	Running minutes	INTERVAL
downtime_minutes	Downtime minutes	INTERVAL
planned_production_time	Planned production time	INTERVAL
planned_downtime	Planned downtime	INTERVAL
current_rate	Current rate	NUMERIC
timestamp_utc	Timestamp in UTC	TIMESTAMP
site_id	Site ID	INTEGER
asset_name	Asset name	VARCHAR
material_name	Material name	VARCHAR
order_number	Order number	VARCHAR
order_name	Order name	VARCHAR
order_date	Order date	DATE
shift_name	Shift name	VARCHAR
default_run_rate	Default run rate	NUMERIC
potential_completed	Potential completed quantity based on run rate	NUMERIC
changeover_minutes	Changeover minutes	INTERVAL

Order Progress

Monitors the minute-by-minute progress of the orders with timestamps, statuses, counts, and associated assets and shifts.

Field	Description	Data Type
id	Order progress ID	INTEGER
created_at	Creation timestamp	TIMESTAMP
updated_at	Update timestamp	TIMESTAMP
total_count	Total count	NUMERIC
timestamp	Timestamp	TIMESTAMP
order_status	Order status	VARCHAR
status	Current status	VARCHAR
status_cause	Cause of the current status	VARCHAR
asset_id	Asset ID	INTEGER
order_id	Order ID	INTEGER
reject_count	Count of rejected items	NUMERIC
shift_id	Shift ID	INTEGER
downtime_type	Type of downtime	VARCHAR
hour	Hour of the timestamp	INTEGER
asset_name	Asset name	VARCHAR
site_name	Site name	VARCHAR