S3 Sink¶

You can set up an S3 Sink to export your data from Tinybird to any S3 bucket in CSV, NDJSON, or Parquet format. The S3 Sink allows you to offload data on a batch-based schedule using Tinybird's fully managed connector.

Setting up the S3 Sink requires:

1. Configuring AWS permissions using IAM roles.
2. Creating a connection file in Tinybird.
3. Creating a Sink pipe that uses this connection

Tinybird represents Sinks using the icon.

Environment considerations¶

Before setting up the S3 Sink, understand how it works in different environments.

Cloud environment¶

In the Tinybird Cloud environment, Tinybird uses its own AWS account to assume the IAM role you create, allowing it to write to your S3 bucket.

Local environment¶

When using the S3 Sink in the Tinybird Local environment, which runs in a container, you need to pass your local AWS credentials to the container. These credentials must have the permissions described in the AWS permissions section, including access to S3 operations like PutObject, ListBucket, etc. This allows Tinybird Local to assume the IAM role you specify in your connection.

To pass your AWS credentials, use the --use-aws-creds flag when starting Tinybird Local:

tb local start --use-aws-creds
» Starting Tinybird Local...
✓ AWS credentials found and will be passed to Tinybird Local (region: us-east-1)
* Waiting for Tinybird Local to be ready...
✓ Tinybird Local is ready!

If you're using a specific AWS profile, you can specify it using the AWS_PROFILE environment variable:

AWS_PROFILE=my-profile tb local start --use-aws-creds

Set up the sink¶

tb connection create s3

TYPE s3
S3_REGION "<S3_REGION>"
S3_ARN "<IAM_ROLE_ARN>"

TYPE s3
S3_REGION "<S3_REGION>"
S3_ACCESS_KEY {{ tb_secret('s3_access_key') }}
S3_SECRET {{ tb_secret('s3_secret') }}

NODE node_0

SQL >
    SELECT *
    FROM events
    WHERE status = 'processed'

TYPE sink
EXPORT_CONNECTION_NAME "s3sample"
EXPORT_BUCKET_URI "s3://tinybird-sinks"
EXPORT_FILE_TEMPLATE "daily_prices" # Supports partitioning
EXPORT_SCHEDULE "*/5 * * * *" 
EXPORT_FORMAT "csv" # Optional
EXPORT_COMPRESSION "gz" # Optional

tb --cloud deploy --check

tb --cloud deploy

.connection settings¶

The S3 connector use the following settings in .connection files:

*Either S3_ARN (for IAM Role authentication) or both S3_ACCESS_KEY and S3_SECRET (for HMAC authentication) are required.

.pipe settings¶

The S3 Sink pipe uses the following settings in .pipe files:

Supported regions¶

The Tinybird S3 Sink feature only supports exporting data to the following AWS regions:

• us-east-*
• us-west-*
• eu-central-*
• eu-west-*
• eu-south-*
• eu-north-*

Scheduling considerations¶

The schedule applied to a Sink pipe doesn't guarantee that the underlying job executes immediately at the configured time. The job is placed into a job queue when the configured time elapses. It is possible that, if the queue is busy, the job could be delayed and executed after the scheduled time.

To reduce the chances of a busy queue affecting your Sink pipe execution schedule, distribute the jobs over a wider period of time rather than grouping them close together.

Query parameters¶

You can add query parameters to your Sink pipes, the same way you do in API Endpoints or Copy pipes.

• For on-demand executions, you can set parameters when you trigger the Sink pipe to whatever values you wish.
• For scheduled executions, the default values for the parameters will be used when the Sink pipe runs.

Execute the Sink pipe¶

On-demand execution¶

You can trigger your Sink pipe manually using:

tb sink run <pipe_name>

Scheduled execution¶

If you configured a schedule with EXPORT_SCHEDULE, the Sink pipe will run automatically according to the cron expression.

Once the Sink pipe is triggered, it creates a standard Tinybird job that can be followed via the v0/jobs API or using tb job ls --kind=sink.

File template¶

The export process allows you to partition the result in different files, allowing you to organize your data and get smaller files. The partitioning is defined in the file template and based on the values of columns of the result set.

Partition by column¶

Add a template variable like {COLUMN_NAME} to the filename. For instance, consider the following query schema and result for an export:

With the given file template invoice_summary_{customer_id}.csv you'd get 3 files:

invoice_summary_ACME.csv

invoice_summary_OSCORP.csv

invoice_summary_GLOBEX.csv

Values format¶

To help partition in a sensible way, you can add a format string to the column name using the following placeholders:

For instance, for a result like this:

Note that all 7 events have different times in the column timestamp. Using a file template like invoices_{timestamp} would create 7 different files.

If you were interested in writing one file per hour, you could use a file template like invoices_{timestamp, '%Y%m%d-%H'}. You'd then get only two files for that dataset:

invoices_20230707-09.csv

invoices_20230706-23.csv

By number of files¶

You also have the option to write the result into X files. Instead of using a column name, use an integer between brackets.

Example: invoice_summary.{8}.csv

This is convenient to reduce the file size of the result, especially when the files are meant to be consumed by other services, like Snowflake where uploading big files is discouraged.

The results are written in random order. This means that the final result rows would be written in X files, but you can't count the specific order of the result.

There are a maximum of 16 files.

Combining different partitions¶

It's possible to add more than one partitioning parameter in the file template. This is useful, for instance, when you do a daily dump of data, but want to export one file per hour.

Setting the file template as invoices/dt={timestamp, '%Y-%m-%d'}/H{timestamp, '%H}.csv would create the following file structure in different days and executions:

Invoices
├── dt=2023-07-07
│   └── H23.csv
│   └── H22.csv
│   └── H21.csv
│   └── ...
├── dt=2023-07-06
│   └── H23.csv
│   └── H22.csv

You can also mix column names and number of files. For instance, setting the file template as invoices/{customer_id}/dump_{4}.csv would create the following file structure in different days and executions:

Invoices
├── ACME
│   └── dump_0.csv
│   └── dump_1.csv
│   └── dump_2.csv
│   └── dump_3.csv
├── OSCORP
│   └── dump_0.csv
│   └── dump_1.csv
│   └── dump_2.csv
│   └── dump_3.csv

Supported file types¶

The S3 Sink supports exporting data in the following file formats:

You can optionally configure the export format using the EXPORT_FORMAT parameter (defaults to CSV) and compression using the EXPORT_COMPRESSION parameter in your Sink pipe configuration.

AWS permissions¶

The S3 connector requires an IAM Role with specific permissions to access objects in your Amazon S3 bucket:

• s3:GetObject
• s3:PutObject
• s3:PutObjectAcl
• s3:ListBucket
• s3:GetBucketLocation

You need to create both an access policy and a trust policy in AWS:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:PutObject",
                "s3:PutObjectAcl"
            ],
            "Resource": "arn:aws:s3:::{bucket-name}/*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetBucketLocation",
                "s3:ListBucket"
            ],
            "Resource": "arn:aws:s3:::{bucket-name}"
        }
    ]
}

Observability¶

Sink pipes operations are logged in the tinybird.jobs_log Service Data Source. You can filter by job_type = 'sink' to see only Sink pipe executions.

For more detailed Sink-specific information, you can also use tinybird.sinks_ops_log.

Data Transfer incurred by Sink pipes is tracked in tinybird.data_transfer Service Data Source.

Limits & quotas¶

Billing¶

Tinybird bills Sink pipes based on Data Transfer. When a Sink pipe executes, it uses your plan's included compute resources (vCPUs and active minutes) to run the query, then writes the result to a bucket (Data Transfer). If the resulting files are compressed, Tinybird accounts for the compressed size.

Data Transfer¶

Data Transfer depends on your environment. There are two scenarios:

• The destination bucket is in the same cloud provider and region as your Tinybird Workspace: $0.01 / GB
• The destination bucket is in a different cloud provider or region as your Tinybird Workspace: $0.10 / GB

Next steps¶

• Get familiar with the Service Data Source and see what's going on in your account
• Deep dive on Tinybird's pipes concept