Daton Connector
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 |
| 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 |
| created | numeric | Timestamp when warehouse info was updated |
| updated | numeric | Timestamp when warehouse was created |
| type | string | 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 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.
- When the source is a full load table
- 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.