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/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(256)
activity_key	Activity key	VARCHAR(256)
candidate_group	Candidate group	VARCHAR(256)
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(256)
activity_status	Activity status	VARCHAR(256) INACTIVE,ACTIVE,COMPLETED,SKIPPED,EXPIRED,SCHEDULED,SUPERSEDED
order_id	Order ID	INTEGER
asset_id	Asset ID	INTEGER
activity_class_name	Activity class name	VARCHAR(256)
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(256)
order_number	Order number associated with activity	VARCHAR(256)
order_name	Order name associated with activity	VARCHAR(256)

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(256)
item_key	Item key	VARCHAR(256)
string_value	String value	VARCHAR(256)
value	Raw value	VARCHAR(256)
numeric_value	Numeric value	NUMERIC
upper_limit	Upper limit	NUMERIC
lower_limit	Lower limit	NUMERIC
target	Target value	NUMERIC
assignee	Assignee	VARCHAR(256)
pass	Pass status	BOOLEAN
display_order	Display order	INTEGER
tolerance_type	Tolerance type	VARCHAR(256) MATERIAL,ASSET,ORDER,ACTIVITY_ITEM,PROCESS_VARIABLE
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(256)
activity_name	Activity name associated with the activity item	VARCHAR(256)
asset_name	Asset name associated with the activity item	VARCHAR(256)
order_number	Order number associated with the activity item	VARCHAR(256)
order_name	Order name associated with the activity item	VARCHAR(256)
process_variable_name	Process variable name	VARCHAR(256)

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(256)
event_subtitle	Event subtitle	VARCHAR(256)
priority	Event priority	INTEGER
event_timestamp	Event timestamp in UTC	TIMESTAMP
closed_at	Event closure timestamp in UTC	TIMESTAMP
status	Event status	VARCHAR(256) - COMPLETED,ACTIVE,SCHEDULED
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(256)
change_over	Change over status	BOOLEAN
event_reason_1	Event reason 1	VARCHAR(256)
event_reason_2	Event reason 2	VARCHAR(256)
event_reason_3	Event reason 3	VARCHAR(256)
event_reason_4	Event reason 4	VARCHAR(256)
downtime_type	Downtime type	VARCHAR(256)
event_comments	Event comments	TEXT
triggered_by	Triggered by	VARCHAR(256)
duration	Duration in minutes (if closed)	INTERVAL
event_type_name	Event type name	VARCHAR(256)
event_type_priority	Event type priority	INTEGER
event_type_display_order	Event type display order	INTEGER
asset_name	Asset name associated with event	VARCHAR(256)
order_number	Order number associated with event	VARCHAR(256)
order_name	Order name associated with the event	VARCHAR(256)

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(256)
order_date	Order date	DATE
order_number	Order number	VARCHAR(256)
batch_number	Batch number	VARCHAR(256)
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(256) NEW READY RUNNING SUSPENDED COMPLETED CANCELLED CHANGEOVER
asset_id	Asset ID	INTEGER
material_id	Material ID	INTEGER
uom_id	Unit of measure ID	INTEGER
operation	Operation	VARCHAR(256)
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(256)
material_name	Material name	VARCHAR(256)
material_external_id	Material external ID	VARCHAR(256)

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	NUMERIC
running_minutes	Running minutes	NUMERIC
downtime_minutes	Downtime minutes	NUMERIC
planned_production_time	Planned production time	NUMERIC
planned_downtime	Planned downtime	NUMERIC
current_rate	Current rate	NUMERIC
timestamp_utc	Timestamp in UTC	TIMESTAMP
site_id	Site ID	INTEGER
asset_name	Asset name	VARCHAR(256)
material_name	Material name	VARCHAR(256)
order_number	Order number	VARCHAR(256)
order_name	Order name	VARCHAR(256)
order_date	Order date	DATE
shift_name	Shift name	VARCHAR(256)
default_run_rate	Default run rate	NUMERIC
potential_completed	Potential completed quantity based on run rate	NUMERIC
changeover_minutes	Changeover minutes	NUMERIC
unplanned_stopped_minutes	Unplanned Stoppages in Minutes	NUMERIC
unplanned_changeover_minutes	Changeover overrun	NUMERIC
production_target	Quantity at Target Run Rate on Planned Production time	NUMERIC

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 NEW READY RUNNING SUSPENDED COMPLETED CANCELLED CHANGEOVER
status	Current status	VARCHAR(256) READY RUNNING STOPPED CHANGEOVER
status_cause	Cause of the current status	VARCHAR(256) ABORTED BLOCKED FAULTED STARVED SUSPENDED COMPLETED
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(256)
hour	Hour of the timestamp	INTEGER
asset_name	Asset name	VARCHAR(256)
site_name	Site name	VARCHAR(256)

Order Component

Field	Desc	Data type
id		INT4
created_at		TIMESTAMP
updated_at		TIMESTAMP
order_number		VARCHAR
component_external_id		VARCHAR
order_id		INT4
target		NUMERIC(18,6)
uom_id		INT4
uom_name		VARCHAR

Material Movements

Field	Description	Data Type
id		INT4
created_at		TIMESTAMP
updated_at		TIMESTAMP
timestamp		TIMESTAMP
quantity		NUMERIC(18,6)
inventory_key		VARCHAR
order_number		VARCHAR
order_operation		VARCHAR
asset_id		INT4
material_external_id		VARCHAR
movement_type_key		VARCHAR
attributes		JSONB
key_attributes		JSONB
order_id		INT4
unit_of_measure_id		INT4
best_before_date		DATE
manufacturing_date		DTAE
contains		VARCHAR
asset_name		VARCHAR
uom_name		VARCHAR

Order Number Attribute

Field	Desc	Data type
id		INT4
created_at		TIMESTAMP
updated_at		TIMESTAMP
order_number		TIMESTAMP
attribute_name		VARCHAR
string_value		VARCHAR
numeric_value		NUMERIC(18,6)
boolean_value		BOOL

Event

Field	Desc	Data Type
id		INT4
created_at		TIMESTAMP
updated_at		TIMESTAMP
event_name		VARCHAR
event_subtitle		VARCHAR
priority		INT4
event_timestamp		TIMESTAMP
closed_at		TIMESTAMP
status		VARCHAR
asset_id		INT4
event_type_id		INT4
order_id		INT4
activity_instance_id		INT4
activity_key		VARCHAR
change_over		BOOL
event_reason_1		VARCHAR
event_reason_2		VARCHAR
event_reason_3		VARCHAR
event_reason_4		VARCHAR
downtime_type		VARCHAR
event_comments		VARCHAR
triggered_by		VARCHAR
duration		FLOAT8
event_type_name		VARCHAR
event_type_priority		INT4
event_type_display_order		INT4
asset_name		VARCHAR
order_number		VARCHAR
order_name		VARCHAR

Shift

Includes the shift instances with its Start TIme, and foreign keys.

Field	Description	Data Type
id	Shift Instance ID	INTEGER
updated_at	Update timestamp	TIMESTAMP
name	Shift Name	VARCHAR(256)
start_time	Start of the shift	TIMESTAMP
end_time	End of the shift	TIMESTAMP
asset_id	Asset ID	INTEGER