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.
result_rows	UInt64	Rows returned by the API call.
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

Several of the Service Data Sources include columns suffixed with _state. This suffix identifies columns with values that are in an intermediate aggregated state. When reading these columns, you need to merge the intermediate states to get the final value.

To do this, wrap the column in the original aggregation function and apply the -Merge combinator.

Learn more about the -Merge combinator here.

For example, to finalize the value of the avg_duration_state column, you use the avgMerge function:

finalize the value for the avg_duration_state column

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