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
numeric
Daton generated id of user
companyid
numeric
Daton generated id of user company
role
integer
-
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
-
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
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
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
numeric
Daton generated id of the integration
userid
numeric
Daton generated id of the user
companyid
numeric
Daton generated id of user company
tablename
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
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

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
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
-
sourcelogid
numeric
Id of job
userid
numeric
Daton generated id of user
companyid
numeric
Daton generated id of user company
tablename
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
-
userid
numeric
Daton generated id of user
companyid
numeric
Daton generated id of user company
sourceid
numeric
Daton generated id of integration
lasterrorcode
integer
Last error code
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
?
lastscheduledat
numeric
Last scheduled timestamp of job
nextfiretime
numeric
Timestamp of when next job will be fired
created
numeric
Timestamp when table was created
updated
numeric
Timestamp when table was updated
tablename
string
Name of table in the integration
lasterror
string
Last noted error
_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

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. 1.
    When the source is a full load table
  2. 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.