Service Data Sources¶

In addition to the data sources you upload, Tinybird provides other “Service Data Sources” that allow you to inspect what’s going on in your account.

For example, you can get real time stats about API calls or a log of every operation over your Data Sources.

It’s the same as using system tables in any database but in this case contains information about the service usage.

Note: Service Data Sources can’t be used in Materialized View queries

These are the available Data Sources:

tinybird.snapshot_views¶

Contains stats about your snapshot views

Field	Type	Description
`date`	`Date`	View creation date.
`snapshot_id`	`String`	Snaphot id. It’s the same id other users can see in the URL (https://ui.tinybird.co/snapshot/:snapshot_id)
`views`	`Int32`	Visit count.

tinybird.pipe_stats_rt¶

Contains information about all requests made to your API Endpoints in real time.

This Data Source has a TTL of 7 days. If you need to query data older than 7 days you must use the aggregated by day data available at tinybird.pipe_stats.

Field	Type	Description
`start_datetime`	`DateTime`	API call start date and time.
`pipe_id`	`String`	Pipe Id as returned in our Pipes API (`query_api` in case it is a Query API request).
`pipe_name`	`String`	Pipe name as returned in our Pipes API (`query_api` in case it is a Query API request).
`duration`	`Float`	API call duration in seconds.
`read_bytes`	`UInt64`	API call read data in bytes.
`read_rows`	`UInt64`	API call rows read.
`url`	`String`	URL (`token` param is removed for security reasons).
`error`	`UInt8`	`1` if query returned error, else `0`.
`request_id`	`String`	API call identifier returned in `x-request-id` header.
`token`	`String`	API call token identifer used.
`token_name`	`String`	API call token name used.
`status_code`	`Int32`	API call returned status code.

tinybird.pipe_stats¶

Aggregates the request stats in tinybird.pipe_stats_rt by day.

Field	Type	Description
`date`	`Date`	Request date and time.
`pipe_id`	`String`	Pipe Id as returned in our Pipes API.
`pipe_name`	`String`	Name of the pipe.
`view_count`	`UInt64`	Request count.
`error_count`	`UInt64`	Number of requests with error.
`avg_duration_state`	`AggregateFunction(avg, Float32)`	Average duration state in milliseconds (see Querying state columns).
`quantile_timing_state`	`AggregateFunction(quantilesTiming(0.9, 0.95, 0.99), Float64)`	0.9, 0.95 and 0.99 quantiles state. Time in milliseconds (see Querying state columns).
`read_bytes_sum`	`UInt64`	Total bytes read.
`read_rows_sum`	`UInt64`	Total rows read.

tinybird.bi_stats_rt¶

Contains information about all requests to your BI Connector interface in real time.

This Data Source has a TTL of 7 days. If you need to query data older than 7 days you must use the aggregated by day data available at tinybird.bi_stats.

Field	Type	Description
`start_datetime`	`DateTime`	Query start timestamp.
`query`	`String`	Executed query.
`query_normalized`	`String`	Normalized executed query. This is the pattern of the query, without literals. Useful to analyze usage patterns.
`error_code`	`Int32`	Error code, if any. `0` on normal execution.
`error`	`String`	Error description, if any. Empty otherwise.
`duration`	`UInt64`	Query duration in milliseconds.
`read_rows`	`UInt64`	Read rows.
`read_bytes`	`UInt64`	Read bytes.
`result_rows`	`UInt64`	Total rows returned.
`result_bytes`	`UInt64`	Total bytes returned.

tinybird.bi_stats¶

Aggregates the stats in tinybird.bi_stats_rt by day.

Field	Type	Description
`date`	`Date`	Stats date.
`database`	`String`	Database identifier.
`query_normalized`	`String`	Normalized executed query. This is the pattern of the query, without literals. Useful to analyze usage patterns.
`view_count`	`UInt64`	Requests count.
`error_count`	`UInt64`	Error count.
`avg_duration_state`	`AggregateFunction(avg, Float32)`	Average duration state in milliseconds (see Querying state columns).
`quantile_timing_state`	`AggregateFunction(quantilesTiming(0.9, 0.95, 0.99), Float64)`	0.9, 0.95 and 0.99 quantiles state. Time in milliseconds (see Querying state columns).
`read_bytes_sum`	`UInt64`	Total bytes read.
`read_rows_sum`	`UInt64`	Total rows read.
`avg_result_rows_state`	`AggregateFunction(avg, Float32)`	Total bytes returned state (see Querying state columns).
`avg_result_bytes_state`	`AggregateFunction(avg, Float32)`	Total rows returned state (see Querying state columns).

tinybird.block_log¶

The Data Source contains details about how we ingested data into your Data Sources. We do ingestion in chunks.

For instance, when we ingest from a URL, if it is possible, we split the download in several requests resulting in different ingestion blocks. The same happens when the data upload happens with a multipart request: we ingest the data in several chunks as the data is arriving.

You can use this Service Data Source to spot problematic parts of your data.

Field	Type	Description
`timestamp`	`DateTime`	Date and time of the block ingestion.
`import_id`	`String`	Id of the import operation.
`job_id`	`Nullable(String)`	Id of the job that ingested the block of data, if it was ingested by URL. In this case, `import_id` and `job_id` must have the same value.
`request_id`	`String`	Id of the request that performed the operation. In this case, `import_id` and `job_id` must have the same value.
`source`	`String`	Either the URL or `stream` or `body` keywords.
`block_id`	`String`	Block identifier. You can cross this with the `blocks_ids` column from the tinybird.datasources_ops_log Service Data Source.
`status`	`String`	`done` \| `error`.
`datasource_id`	`String`	Data Source consistent id.
`datasource_name`	`String`	Data Source name when the block was ingested.
`start_offset`	`Nullable(Int64)`	The starting byte of the block, if the ingestion was split, where this block started.
`end_offset`	`Nullable(Int64)`	If split, the ending byte of the block.
`rows`	`Nullable(Int32)`	How many rows it ingested.
`parser`	`Nullable(String)`	Either if we used the native block parser or we had to fallback to row by row parsing.
`quarantine_lines`	`Nullable(UInt32)`	If any, how many rows went into the quarantine Data Source.
`empty_lines`	`Nullable(UInt32)`	If any, how many empty lines were skipped.
`bytes`	`Nullable(UInt32)`	How many bytes the block had.
`processing_time`	`Nullable(Float32)`	How long it took in seconds.
`processing_error`	`Nullable(String)`	Detailed message in case of error.

tinybird.datasources_ops_log¶

Contains all operations performed to your Data Sources. We track the following operations:

Event	Description
`create`	A Data Source is created.
`append`	Append operation.
`append-hfi`	Append operation using our High-frequency Ingestion API.
`append-kafka`	Append operation using our Kafka Connector.
`replace`	A replace operation took place in the Data Source.
`delete`	A delete operation took place in the Data Source.
`truncate`	A truncate operation took place in the Data Source.
`rename`	The Data Source was renamed.
`populateview-queued`	A populate operation was queued for execution.
`populateview`	A finished populate operation (up to 8 hours after it started).
copy`	A copy operation took place in the Data Source.

Materializations are logged with same event_type and operation_id as the operation that triggers them. You can track the materialization pipe with pipe_id and pipe_name.

We log all of them with extra information in this Data Source.

Field	Type	Description
`timestamp`	`DateTime`	Date and time when the operation started.
`event_type`	`String`	Operation being logged.
`operation_id`	`String`	Groups rows affected by the same operation. Useful for checking materializations triggered by an append operation.
`datasource_id`	`String`	Id of your Data Source. The Data Source id is consistent after renaming operations. You should use the id when you want to track name changes.
`datasource_name`	`String`	Name of your Data Source when the operation happened.
`result`	`String`	`ok` \| `error`
`elapsed_time`	`Float32`	How much time the operation took in seconds.
`error`	`Nullable(String)`	Detailed error message if the result was error.
`import_id`	`Nullable(String)`	Id of the import operation, if data has been ingested using one of the following operations: `create`, `append` or `replace`
`job_id`	`Nullable(String)`	Id of the job that performed the operation, if any. If data has been ingested, `import_id` and `job_id` must have the same value.
`request_id`	`String`	Id of the request that performed the operation. If data has been ingested, `import_id` and `request_id` must have the same value.
`rows`	`Nullable(UInt64)`	How many rows the operations affected. This depends on `event_type`: for the `append` event, how many rows got inserted; for `delete` or `truncate` events, how many rows the Data Source had; for `replace`, how many rows the Data Source has after the operation.
`rows_quarantine`	`Nullable(UInt64)`	How many rows went into the quarantine Data Source, if any.
`blocks_ids`	`Array(String)`	List of blocks ids that we used for the operation. See the tinybird.block_log Service Data Source for more details.
`options`	`Nested(Names String, Values String)`	We store key-value pairs with extra information for some of the operations. For the `replace` event, we use the `rows_before_replace` key to track how many rows the Data Source had before the replacement happened, the `replace_condition` key shows what condition was used. For `append` and `replace` events we store the data `source`, e.g. the URL, or body/stream keywords. For `rename` event, `old_name` and `new_name`. For `populateview` you can find there the whole populate `job` metadata as a JSON string.
`read_bytes`	`UInt64`	Read bytes in the operation.
`read_rows`	`UInt64`	Read rows in the operation.
`written_rows`	`UInt64`	Written rows in the operation.
`written_bytes`	`UInt64`	Written bytes in the operation.
`written_rows_quarantine`	`UInt64`	Quarantined rows in the operation.
`written_bytes_quarantine`	`UInt64`	Quarantined bytes in the operation.
`pipe_id`	`String`	If present, materialization pipe id as returned in our Pipes API.
`pipe_name`	`String`	If present, materialization pipe name as returned in our Pipes API.

tinybird.endpoint_errors¶

It provides the last 30 days errors of your published endpoints. We log all of them with extra information in this Data Source.

Field	Type	Description
`start_datetime`	`DateTime`	Date and time when the API call started.
`request_id`	`String`	The id of the request that performed the operation.
`pipe_id`	`String`	If present, pipe id as returned in our Pipes API.
`pipe_name`	`String`	If present, pipe name as returned in our Pipes API.
`params`	`Nullable(String)`	URL query params included in the request.
`url`	`Nullable(String)`	URL pathname.
`status_code`	`Nullable(Int32)`	HTTP error code.
`error`	`Nullable(String)`	Error message.

tinybird.kafka_ops_log¶

Contains all operations performed to your Kafka Data Sources during the last 30 days

Field	Type	Description
`timestamp`	`DateTime`	Date and time when the operation took place.
`datasource_id`	`String`	Id of your Data Source. The Data Source id is consistent after renaming operations. You should use the id when you want to track name changes.
`topic`	`String`	Kafka topic.
`partition`	`Int16`	Partition number, or `-1` for all partitions.
`msg_type`	`String`	‘info’ for regular messages, ‘warning’ for issues related to the user’s Kafka cluster, deserialization or materialized views, and ‘error’ for other issues.
`lag`	`Int64`	Number of messages behind for the partition. This is the difference between the high-water mark and the last commit offset.
`processed_messages`	`Int32`	Messages processed for a topic and partition.
`processed_bytes`	`Int32`	Amount of bytes processed.
`committed_messages`	`Int32`	Messages ingested for a topic and partition.
`msg`	`String`	Information in the case of warnings or errors. Empty otherwise.

tinybird.datasources_storage¶

Contains stats about your Data Sources storage. We log max values per hour, the same we use to calculate your storage consumption.

Field	Type	Description
`datasource_id`	`String`	Id of your Data Source. The Data Source id is consistent after renaming operations. You should use the id when you want to track name changes.
`datasource_name`	`String`	Name of your Data Source.
`timestamp`	`DateTime`	When storage was tracked. By hour.
`bytes`	`UInt64`	Max number of bytes the Data Source has, not including quarantine.
`rows`	`UInt64`	Max number of rows the Data Source has, not including quarantine.
`bytes_quarantine`	`UInt64`	Max number of bytes the Data Source has in quarantine.
`rows_quarantine`	`UInt64`	Max number of rows the Data Source has in quarantine.

Querying state columns¶

Querying Data Sources with state columns is easy. State is an intermediate aggregated state and to get the final value you just need to use the aggregation method plus -Merge.

See the following example:

how to get stats for a particular pipe¶

SELECT
  date,
  avgMerge(avg_duration_state) avg_time,
  quantilesTimingMerge(0.9, 0.95, 0.99)(quantile_timing_state) quantiles_timing_in_ms_array
FROM tinybird.pipe_stats
where pipe_id = 'PIPE_ID'
group by date