Daton Connector

What is this connector?

Daton connector exposes the data that Daton generates once you start using it. In addition to the information we show in the Daton UI, users can configure the Daton connector to sync essential data to your warehouse so that users can write their own queries and build alerts on the data being published. Configuring Daton connector is like configuring any other connector in Daton.

What data is made available?

The following tables will be sync’ed to your data warehouse.

USER_V

This table provides a list of all users belonging to a company.

Schema

Column Name	Data Type	Description
userid (Primary Key)	numeric	Daton generated id of user
companyid	numeric	Daton generated id of user company
role	integer	0, 1 - admin. 2 - user
status	integer	0 – Created, 1 – Active, 2 - Inactive
created	numeric	Timestamp when user was created
updated	numeric	Timestamp when user info was updated
createdby	numeric	Created by
User_type	string	User or Admin
email	string	User email
First_name	string	User first name
Last_name	string	User last name
Company_name	string	User company name
_daton_user_id	numeric	Daton generated id of user. Same as userid column in user_v table
_daton_batch_runtime	numeric	Timestamp when a particular entry was processed
_daton_batch_id	numeric	Batch id of the processed entry

SOURCE_V

This table provides a list of all sources configured in Daton and their status.

Column Name	Data Type	Description
sourceid (Primary Key)	numeric	Daton generated id of the integration
userid	numeric	Daton generated id of the user
companyid	numeric	Daton generated id of user company
created	numeric	Timestamp when user was created
updated	numeric	Timestamp when user info was updated
warehouseid	numeric	Daton generated id of destination warehouse of source
parallelism	integer	1 if jobs are processed simultaneously, 0 if jobs are processed sequentially
name	string	Name of integration
frequency	string	Frequency of data replication of the integration – once in the number of hours mentioned
history	string	Number of years of historical data from when the integration was configured
status	string	0 – Created, 1 – Disabled, 2 – Active, 3 – Updated, 4 – Configured, 5 – Deleted, 6 - Paused
type	string	Type of source
destinationtype	string	Type of destination (warehouse)
_daton_user_id	numeric	Daton generated id of user
_daton_batch_runtime	numeric	Timestamp when a particular entry was processed
_daton_batch_id	numeric	Batch id of the processed entry

WAREHOUSE_V

This table provides a list of all warehouses configured in Daton and their status.

Column Name	Data Type	Description
warehouseid (Primary Key)	numeric	Daton generated id of destination warehouse
userid	numeric	Daton generated id of the user
companyid	numeric	Daton generated id of user company
updated	numeric	Timestamp when warehouse info was updated
created	numeric	Timestamp when warehouse was created
type	numeric	Type of warehouse
name	string	User generated name of warehouse
status	string	0 – Created, 1 – Disabled, 2 – Enabled, 3 – Updated, 4 - Configuring
_daton_user_id	numeric	Daton generated id of user.
_daton_batch_runtime	numeric	Timestamp when a particular entry was processed
_daton_batch_id	numeric	Batch id of the processed entry

SOURCE_TABLES_V

This table provides a list of all the tables belonging to the sources configured in Daton and their status.

Column Name	Data Type	Description
maxdepthofnesting	integer	Maximum level of nesting in the table
fullload	boolean	If a job is processed every time along with all of the history it is a full load, else it is an incremental load when only new rows are added
updated	numeric	Timestamp when table was updated
created	numeric	Timestamp when table was created
sourceid (Primary Key)	numeric	Daton generated id of the integration
userid	numeric	Daton generated id of the user
companyid	numeric	Daton generated id of user company
tablename (Primary Key)	string	Name of table
tableschema	string	Schema of table
frequency	string	Frequency of data replication of the table – once in the number of hours mentioned
history	string	Number of years of historical data from when the integration was configured
status	string	0 – Created, 1 – Disabled, 2 – Active, 3 – Updated, 4 – Configured, 5 – Deleted, 6 - Paused
modificationtype	string	None, created, updated, dropped or drop_create
updatemode	string	Inherit, upsert, insert, write_truncate
_daton_user_id	numeric	Daton generated id of user
_daton_batch_runtime	numeric	Timestamp when a particular entry was processed
_daton_batch_id	numeric	Batch id of the processed entry

SOURCE_TRANSACTION_V

This table provides a history of all the jobs executed for tables configured in Daton starting from 1-Jan-2022.

Column Name	Data Type	Description
transactionid (Primary Key)	numeric	Daton generated id of job
sourceid	numeric	Daton generated id of integration
userid	numeric	Daton generated id of user
companyid	numeric	Daton generated id of user company
starttime	numeric	Timestamp when job started
endtime	numeric	Timestamp when job ended
records	numeric	Number of records processed
updated	numeric	Latest primary key from source used to upsert/append data (usually a timestamp)
created	numeric	Timestamp when job was created
errorcode	integer	Error code
transactionlogid	numeric	Id of the number of jobs replicated for that table
errortype	integer	Error type
callstatus	integer	-1 for when job failed and 0 for when job succeeded.
lastrecord	numeric	Last updated primary key of the table if job failed. (Usually timestamp)
scheduledat	numeric	Timestamp of when the job was scheduled
error	string	Error
tablename	string	Name of the table in the integration
_daton_user_id	numeric	Daton generated id of user
_daton_batch_runtime	numeric	Timestamp when a particular entry was processed
_daton_batch_id	numeric	Batch id of the processed entry

SOURCE_LOG_RECENT_V

This table provides the value of the last record replicated by Daton for each of the table configured for replication.

Column Name	Data Type	Description
sourceid (Primary Key)	numeric	Daton generated id of integration
lastrecord	numeric	Timestamp of when the last record was replicated in a table
created	numeric	Timestamp when job was created
updated	numeric	Timestamp when table was updated
status	integer	0 for inactive, 1 for active
sourcelogrecentid	numeric	Id of job in source_log_recent
sourcelogid	numeric	Id of job in source_log
userid	numeric	Daton generated id of user
companyid	numeric	Daton generated id of user company
tablename (Primary Key)	string	Name of table in the integration
state	string	Json describing additional information of job and job state
_daton_user_id	numeric	Daton generated id of user
_daton_batch_runtime	numeric	Timestamp when a particular entry was processed
_daton_batch_id	numeric	Batch id of the processed entry

LAST_JOB_STAT_V

This table provides the data related to the last job replicated by Daton for each of the table configured for replication.

Column Name	Data Type	Description
statid	numeric	stat id
userid	numeric	Daton generated id of user
companyid	numeric	Daton generated id of user company
sourceid (Primary Key)	numeric	Daton generated id of integration
lasterrorcode	integer	Error code if any in the last job. 0 if there was no error.
laststarttime	numeric	Timestamp of the start of last job
lastendtime	numeric	Timestamp of the end of last job
lastrecordcount	numeric	Count of records processed in the last job
totalrecordcount	numeric	Total number of records processed/in the table
sourceloglastrecord	numeric	Timestamp up to which data has been replicated in the last job.
lastscheduledat	numeric	Last scheduled timestamp of job
nextfiretime	numeric	Timestamp of when next job will be fired
created	numeric	Timestamp when this particular row was created
updated	numeric	Timestamp when this particular row was updated
tablename (Primary Key)	string	Name of table in the integration
lasterror	string	Error in the last job if any. Empty if no error.
_daton_user_id	numeric	Daton generated id of user
_daton_batch_runtime	numeric	Timestamp when this particular row was processed
_daton_batch_id	numeric	Batch id of this row

How can you use this data?

Tracking Jobs

This connector exposes data related to jobs being run by Daton. Users can analyze this data to understand what jobs are being run, at what time, and become more self-sufficient in troubleshooting in case they notice an issue.

Visibility into Last processed value for tables

Users can get visibility into the last replicated value for each table by going into the integration details page and checking the replicated up to value for any table. However, using the UI can be difficult when you are trying to analyze replicated up to values for tables across multiple integrations. Data from this connector can be synced to your warehouse and SQL queries can be written to analyze the data.

Understanding Usage

Users on usage-based billing plans can analyze which tables and integrations are contributing to their monthly bill. This data can be leverage to fine tune the setups to bring overages down.

Impact on Billing

Adding Daton connect will be counted towards your quota of available connector in your plan. Data replicated by this connector counts towards your monthly quota for usage-based billing plans.

Useful Queries

/*Get a list of sources by user*/ select * from SOURCE_V s , USER_V u where s.userid = u.userid ; /*Last job status of a table.*/ select s.name , ljs.* from SOURCE_V s , LAST_JOB_STAT_V ljs where s.sourceid = ljs.sourceid and s.name = 'INTEGRATION_NAME' and ljs.tablename = 'TABLE_NAME' ; /*History of jobs for a table*/ select * from SOURCE_V s , SOURCE_TRANSACTION_V st where s.sourceid = st.sourceid and s.name = 'INTEGRATION_NAME' and st.tablename = 'TABLE_NAME' ;

FAQ

How do I convert timestamp columns into a readable format?

BigQuery

• SELECT TIMESTAMP_MILLIS(CAST(COLUMN_NAME AS INT64)) FROM TABLE NAME

Redshift

• SELECT  timestamp 'epoch' + CAST(COLUMN_NAME  AS BIGINT)/1000 * interval '1 second' AS date FROM TABLE NAME

Snowflake

• select to_timestamp(daton_batch_runtime ) FROM table_name

Why is last record pointing to 1/1/1970 for some tables?

This happens in two cases.

1. When the source is a full load table

2. When the first job hasn’t run yet for a table

What is the “state” column in SOURCE_LOG_RECENT_V table?

Daton uses this column to store additional job-related data for some sources. If most cases, you don’t need to use this data as it is primarily for internal user. Reach out to support if you have any questions.

What is the parallelism column in SOURCE_V table?

Daton uses this column to determine how many jobs run in parallel for the source.

Why is one of my tables not in SOURCE_LOG_RECENT_V?

Although this scenario is very rate, this can happen when the table never managed even a single successful run.