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

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