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 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

View count.

tinybird.pipe_stats_rt

Contains information about all requests made to your Pipe 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.

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 stats in tinybird.pipe_stats_rt by day.

Field

Type

Description

date

Date

View date.

pipe_id

String

Pipe Id as returned in our Pipes API.

pipe_name

String

Name of the pipe.

view_count

UInt64

View 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

Sum of bytes read by each API call.

read_rows_sum

UInt64

Sum rows read by each API call.

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 read bytes.

read_rows_sum

UInt64

Total read rows.

avg_result_rows_state

AggregateFunction(avg, Float32)

Total returned bytes state (see Querying state columns).

avg_result_bytes_state

AggregateFunction(avg, Float32)

Total returned rows 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

When the block was ingested.

import_id

String

The id of the import operation.

job_id

Nullable(String)

The Job id 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

The 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

The 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

The Data Source consistent id.

datasource_name

String

The 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.

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

When the operation happened.

event_type

String

The 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

The 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

The 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)

The id of the import operation, if data has been ingested using one of the following operations: create, append or replace

job_id

Nullable(String)

The 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

The 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)

The 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

When 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

When the operation happened.

datasource_id

String

The 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

The 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

The 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

The name of your Data Source. Last value found if it was renamed.

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