How S3Queue streams new S3 files into ClickHouse
Consider a team that receives log or event files continuously into an S3 or S3-compatible bucket and wants each new file loaded into a MergeTree table without someone running a manual load. The S3Queue table engine is built for that pattern. It keeps track of which objects in the bucket it has seen, and when a materialized view is attached to the S3Queue table, ClickHouse starts collecting data in the background and writes the rows into the target table that the view points at. The S3 table engine and the s3 table function remain the right choice for one-off reads and ad hoc queries over files, but a bucket that keeps growing needs the queue-based path, because the engine itself decides which files are new.
The engine is shaped by a small set of documented controls. mode decides whether files are processed in order or as they appear, processing_threads_num sets how many threads consume the queue on each server, tracked_files_limit and tracked_file_ttl_sec bound how much file-tracking state is kept and for how long, and polling_backoff_ms controls how long the engine waits between listing attempts when the bucket is quiet. Each of these trades throughput, memory and load on the bucket against how quickly new files appear in the target table. Before choosing values, confirm the defaults and the exact semantics for your ClickHouse version in the S3Queue engine documentation, and test them against a copy of your real file layout and arrival rate.
What you need before creating an S3Queue table
Four things have to be in place before the S3Queue engine can read anything: a supported ClickHouse version, a Keeper ensemble to track files, a working path to the bucket, and a target table that matches the file format. Each card below is a gate to check before you create the engine and materialized view.
A supported ClickHouse version
Confirm the cluster runs a version that still receives security updates before you build on it. The security policy lists 26.9, 26.8, 26.7 and 26.3 as supported, and everything else is unsupported. Check the release notes for the version you choose, because S3Queue settings and their prefix rules changed across releases, and a settings name that works on one line may not parse on another. Test the engine on a pre-production cluster with a copy of your real file layout first.
A Keeper ensemble for file tracking
S3Queue records which files it has seen and processed in ClickHouse Keeper, so file tracking depends on Keeper health. The replication guide recommends at least three Keeper nodes on dedicated hosts in production, each with about 4 GB of RAM. Keeper outages stop ingestion or cause duplicate processing, so size it as a shared dependency and monitor it separately from the data nodes. Confirm the sizing against the current replication guide before you deploy.
Read access to the bucket
The ClickHouse node needs an IAM principal or equivalent credential that can list the bucket prefix and read the objects under it. Scope the policy to the one prefix that receives files, and deny writes to that prefix if the feed is append-only. Decide where the credential lives, usually a named collection or a secrets manager, rather than inline in the table definition. Ask your storage team whether the bucket uses server-side encryption with customer-managed keys, since the reading principal then needs key access too.
A network path to the bucket
Each ClickHouse node that runs the S3Queue background work must reach the S3 endpoint. In a restricted network, that usually means an egress rule to the storage endpoint, a VPC endpoint, or a proxy that ClickHouse is configured to use. Test from every node, not only one, because a node that cannot reach the bucket will fail quietly while its peers keep processing. Confirm DNS resolution and TLS trust for private S3-compatible endpoints before you add the engine.
A target MergeTree table
Create the destination MergeTree table before the materialized view, and make its column names and types match the fields in the file format exactly. Mismatched types cause parse errors that stop a batch, and a missing column can silently drop data if defaults are used. Choose the ordering key for your query pattern now, since changing it later means rebuilding the table. If the table is replicated, define it on ReplicatedMergeTree so every replica sees the same loaded data.
A file naming and arrival contract
Agree with the producer how files are named and when they appear in the bucket. S3Queue ordering depends on lexicographic file names in ordered mode, and files written in place or renamed after upload can be skipped or read half-written. Ask the producer to upload to a temporary prefix and move files into the watched prefix only when complete, and to never reuse a file name. Document this contract next to the table definition so future changes to the producer do not break ingestion.
Configure the S3Queue table, the target table and the materialized view
This sequence builds the three objects that make S3 ingestion work: a MergeTree target that holds the data, an S3Queue table that tracks which files have been seen, and a materialized view that moves rows from the queue into the target. Each step ends with a check so the team knows collection is running before data arrives.
Create the target MergeTree table
Your data engineers define the destination table first, using a MergeTree family engine that matches your query patterns. Choose the column types, the ORDER BY key and any partitioning now, because the S3Queue table only supplies rows and cannot reshape the schema later. Confirm the table exists by running a SELECT against system.tables for the target database and checking that the engine and sorting key match your design. Do not attach any ingestion object until this check passes.
Store bucket credentials in a named collection
Your platform team creates a named collection that holds the bucket URL, access key and secret for the S3 or S3-compatible endpoint, so credentials do not appear in table DDL or query logs. Scope the bucket permissions to read and list on the single prefix the pipeline uses. Confirm the collection is readable by the ingestion user and not by the default user, which starts with all permissions, by checking the named collection listing as that user.
Create the S3Queue table with mode and format
Your engineers create the S3Queue engine table with the bucket path, file format, the named collection, and the mode setting. Choose ordered or unordered mode at this point, because the mode decides how files are tracked and processed and should not be changed casually once files are flowing. Settings such as processing_threads_num, tracked_files_limit and tracked_file_ttl_sec can be set here as well. Before ClickHouse 24.7, most settings require the s3queue_ prefix, while mode, after_processing and keeper_path do not. From 24.7, the prefix is optional, so confirm your server version and use one style consistently. Confirm the table was created by describing it and checking the settings it reports.
Attach the materialized view to the queue
Your engineers create a materialized view that selects from the S3Queue table and inserts into the MergeTree target, mapping columns and applying any light transformation. Attaching the view is what starts background collection, so the view should be created only after the target and queue definitions have been reviewed. Keep the SELECT deterministic and map every column explicitly to avoid silent type mismatches. Confirm the view exists in system.tables and that its target is the intended table.
Confirm collection is running
Your operators check that the background loop has started by querying the system tables for the S3Queue ingestion state, which lists files by status and shows whether processing is advancing. Run a test by placing one small file in the prefix, then watch for a processed entry and matching rows in the target table. Compare the row count in the target with the expected count from the source file. The step is complete when the file shows as processed, the row count matches and no failed entries appear.
Check processed and failed files in system tables
Your data platform team sets a recurring query against the S3Queue system tables that counts processed, failed and in-progress files per queue. Record the baseline counts from the test file, then add an alert when failed counts rise or when the processed count stops changing while new files arrive. Include the error text in the query output so that failed files can be diagnosed without guessing. The check is complete when the query runs on a schedule, the baseline is documented and the team knows who responds to the alert.
Document the configuration and confirm the mode decision
Your team records the table DDL, the server version, the mode and the Keeper path in the runbook, along with the reason for the chosen mode. Changing mode after files have been ingested requires a deliberate plan, so the runbook should state what happens to tracked files if the configuration changes. Confirm the record with the people who will operate the pipeline, and confirm the server version against the release you have tested before promoting the configuration to production.
Choosing ordered or unordered mode for your file arrival pattern
Ordered mode fits a source that writes files in a predictable, increasing sequence, such as timestamped names like 2026-03-01T10-00-00.parquet written one after another by a single producer. In this mode the S3Queue engine processes files in lexicographic order and remembers a position, so a file sorting before the last processed name is not picked up later. That makes the engine cheap to track but strict about naming. The setting is mode = 'ordered' on the table, and the file name order must match the order you expect data to land.
- Strict sort order: the engine compares names as strings, so zero-padded timestamps and fixed-width sequence numbers are required for correct ordering.
- Lower tracking overhead: the engine stores a position rather than a record for every file, which keeps Keeper state small for long-running pipelines.
- Late files are skipped: a file that sorts before the current position, for example because a delayed producer wrote an older timestamp, will not be loaded.
- Single writer assumption: parallel producers that write interleaved names break the sequence and are the most common cause of silent gaps.
- Replay requires intent: reprocessing old files means resetting tracking state deliberately, so plan how an operator would do that.
Before you commit, confirm the naming scheme with the team that owns the producer and run a test with a sample of real file names, including any retries or backfills they plan to write.
Unordered mode fits sources where files land out of order, in parallel, or after delays, such as several producers or regional uploads writing to one bucket. Instead of a single position, the engine tracks each file it has seen, so a late file with an older name is still loaded once. The trade-off is that tracking state grows with the number of files, which is why the tracked_files_limit and tracked_file_ttl_sec settings matter.
- Any arrival order: each file is handled on its own, so naming order no longer decides what gets loaded.
- Growing tracking state: every tracked file is an entry in Keeper, so high file counts need a limit and an expiry policy that match your volume.
- Expiry can cause reprocessing: if a file's tracking record expires and the file is still listed, it may be loaded again, so the TTL must be longer than the window in which files can reappear.
- Higher throughput potential: with
processing_threads_numset, unordered mode can spread files across threads without waiting on a sequence. - Duplicate control moves to your design: the target table should tolerate or deduplicate repeated rows if a file is ever loaded twice.
Ask the producer whether files can be rewritten under the same name, and test how the pipeline behaves when a file arrives hours late.
The mode also determines what happens during replay, recovery and investigation of missing rows. In ordered mode a skipped file usually means the file sorted before the processed position, so the gap is visible only by comparing bucket listings with loaded data. In unordered mode a skipped file is more often the result of a tracking record that still exists, a file that was never listed under the configured path, or a pattern that did not match. Both modes need a way to answer the question of which files were loaded.
- Ordered gaps look like sequence holes: comparing the last processed name against the bucket listing shows which earlier names were passed over.
- Unordered gaps look like tracked-but-missing rows: check the file's tracking status and the path pattern before assuming a load failure.
- Reset is a documented operation: changing tracking state or re-creating the table affects what is loaded again, so record every reset in a runbook.
- Path patterns matter in both modes: a glob that excludes a prefix will silently skip files in either mode.
- Log the file name with the rows: a file name or path column in the target table makes reconciliation possible after a failure.
Review the S3Queue engine documentation for the current tracking and reset behavior in your release before writing the runbook.
Choosing a mode from a diagram is not enough, because the outcome depends on the exact names your producers generate. A short test with a copy of real or representative file names, including out-of-order uploads, retries and a delayed file, will show which mode loads every file exactly once in your environment. The test should run against a non-production bucket and a separate Keeper path so that tracking state does not leak into the real pipeline.
- Use a sample of real names: include the producer's naming edge cases such as padding, prefixes, and suffixes.
- Inject out-of-order arrivals: upload an older name after a newer one and confirm the outcome in each mode.
- Count rows against files: compare the number of loaded rows with the expected row count per file.
- Test the expiry window: verify that tracking records outlive the period in which files can reappear.
- Repeat after the producer changes: a new naming scheme or a second producer requires the test to be run again.
Confirm the final mode with the team that owns the producer and document the test results so auditors and operators can see how the choice was validated.
How Keeper tracking prevents files from loading twice
S3Queue keeps its record of which files it has already processed in ClickHouse Keeper, so the health of the Keeper ensemble decides whether the engine can tell a new file from one it has already loaded. If Keeper is unreachable or loses state, the engine cannot reliably deduplicate, and that failure mode is the main reason to treat Keeper as a dependency of this pipeline rather than an afterthought. Confirm which Keeper path the table uses through the keeper_path setting, make sure each replica or ingesting node points at the same ensemble, and alert on Keeper availability and latency from the same dashboard that tracks the queue.
The two bounds on that state are tracked_files_limit and tracked_file_ttl_sec. They cap how many file records are retained and how long each record lives, so once a record is gone, the engine no longer remembers that the file was loaded. A file that falls outside the tracking window, or whose state was removed during a Keeper repair or a path change, can be read again and loaded twice, or it can be skipped in ways that are hard to see later. Before you rely on the defaults, confirm the exact semantics for your ClickHouse version in the S3Queue table engine documentation, including what happens to files that are older than the TTL when they appear in the bucket.
Test this behavior before production. In a pre-production cluster, load a known set of files, let the tracking window pass, then deliberately re-upload or replay one file and count the rows that arrive in the target table. Repeat the test after a Keeper restart and after a simulated loss of tracking state. Record the results alongside the limit and TTL values you chose, and set a reconciliation check that compares source object counts with loaded row counts so that duplicates and gaps show up as a metric rather than as a surprise in a report.
Mapping bucket objects, file formats and table columns
This table maps each element of the S3 source, from object keys to file contents, onto the S3Queue table, the materialized view and the target MergeTree table. Read each row as a decision to settle before the queue is created, because changing it later usually means reprocessing files.
| Source element | ClickHouse mapping | Decide at design time |
|---|---|---|
| Bucket and key prefix | Path argument of the S3Queue table, with wildcards selecting the files the queue tracks | Whether one prefix holds one dataset, and whether producers ever rename or overwrite keys in place |
| Object key naming | Files are identified by their key, so the key is the unit of deduplication in file tracking | Naming that sorts in arrival order if you plan to use ordered mode |
| File format | Format setting on the S3Queue table, such as JSON or Parquet, matched to the actual file bytes | Whether all producers write the same format and compression, and what happens to malformed files |
| File columns | Names and types read from the file must match the columns the view selects | How new fields are handled (schema drift), and whether missing fields become Nullable columns or defaults |
| Materialized view select | Transforms and casts columns, then inserts into the target MergeTree table | Type conversions, timestamp parsing, and which filters drop rows silently |
| Target table columns and partitioning | MergeTree table receives rows from the view; partition and ordering keys are set here | Partition key and ordering key, since they are costly to change once data is loaded |
| Nullable and default values | Views handle missing or empty values explicitly before insert | Which columns allow NULL and which must be populated, and how invalid values are counted |
Securing the bucket credentials and the ingestion path
An S3Queue pipeline touches three trust boundaries: the bucket, the ClickHouse user that runs the queue, and the network paths between them. The cards below cover how to scope each one so the ingestion job can read only what it needs and nothing else. Bucket policy and key rotation remain controls you own and must confirm with your storage team.
Read-only bucket credentials in a named collection
Store the access key and secret for the source bucket in a named collection instead of inline in the CREATE TABLE statement, so the credentials do not appear in table definitions, query logs or SHOW CREATE TABLE output. The key should carry read and list permissions on the ingestion prefix only, with no write or delete rights. Confirm with your cloud or storage team that the key is scoped to that prefix and that its rotation schedule is documented. Check that the named collection is created by an administrator and not by the ingestion user.
Dedicated ingestion user
Create a separate ClickHouse user for the S3Queue pipeline with grants limited to the queue table, the target MergeTree table and the materialized view that connects them. Do not reuse an analyst account or the application account, because a compromised pipeline user should not be able to read unrelated tables. Give it the named collection usage it needs and no server-level or administrative privileges. Review the grants whenever the table set changes.
Restricting the default user
The default user starts with all permissions, so leaving it enabled with a known or empty password exposes every table on the cluster. Disable or restrict it, set a strong password or move it to an external authenticator, and confirm that no ingestion job or dashboard connects as default. Check the users configuration on every node, because a replica with a missed setting is a weak point even when the others are locked down.
Authentication for the ingestion user
Decide how the dedicated user authenticates before the pipeline goes live. Options documented for ClickHouse include LDAP directories, Kerberos, SSL X.509 certificates, an HTTP authenticator and JWT, all configured as external authenticators. Choose the method your identity team can operate and rotate, and record who owns the credential. Confirm which methods your ClickHouse version supports in its external authenticator configuration before committing to one.
TLS on bucket and cluster traffic
Use HTTPS endpoints for the bucket so object reads and listings are encrypted in transit, and enable TLS between ClickHouse nodes and between clients and the cluster. Without TLS on the bucket path, the access key and object contents can cross the network in the clear. Confirm the certificate chain your ClickHouse hosts trust for the object store, and plan certificate renewal alongside the Keeper and replica certificates so the pipeline does not stop on expiry.
Bucket policy and key rotation
The bucket policy, bucket encryption, object lock or versioning settings and the key rotation schedule are controls that sit outside ClickHouse. DeployOpen can document what the pipeline needs, but your cloud or storage owners set and attest to these controls. Ask them to confirm that the policy denies deletes from the ingestion principal, that rotation does not break the named collection without a planned update, and that access logging is on for the bucket.
What to monitor so files are not skipped or loaded twice
The first thing to monitor is whether every object in the bucket ends up as processed rows. Compare the object count under the ingestion prefix with the count of files the S3Queue engine reports as processed, and alert on the gap rather than on a single error. A growing gap that does not close between polls is the clearest sign that files are waiting, stuck or being passed over. Keep the comparison scoped to the same prefix and pattern the table uses, or the numbers will drift for reasons unrelated to health.
Track the processed and failed file states the engine exposes, and treat the failed-file trend as the earliest warning. A failed file that is never retried or reviewed is the usual route to a skipped file, so a rising failure count should page someone before the gap appears. Pair that with lag between when an object lands in S3 and when its rows are visible in the target table, and watch processing thread saturation, since threads that stay fully busy push lag up even when nothing has failed. Confirm with your ClickHouse version which system tables and metrics expose these states, because the names differ across releases.
Keeper is the coordination point for file tracking, so monitor its availability and request latency alongside the ingestion metrics. A Keeper outage or slow responses can stall tracking and make the engine look idle while files pile up. Also search server logs for S3Queue errors on a schedule and alert on error counts, not only on crashes. Confirm the Keeper monitoring approach against the ClickHouse replication documentation before you rely on it. Test these alerts by deliberately dropping a test file in a non-production bucket, so you know the gap alert fires before you depend on it in production.
Common S3Queue failures and how to fix them
This table lists six failure modes seen when running the S3Queue engine, with the likely cause, the check to run and the fix for each. Read it as a triage sheet: start with the symptom you see, confirm the cause with the check, then apply the fix. Most failures trace back to settings, Keeper health or bucket permissions.
| Failure | Likely cause | Check and fix |
|---|---|---|
| Settings rejected or ignored on a pre-24.7 server | Settings written without the s3queue_ prefix on a version before 24.7, where the prefix is required for all settings except mode, after_processing and keeper_path | Check the server version first. On earlier versions, add the s3queue_ prefix to processing_threads_num, tracked_files_limit, tracked_file_ttl_sec and polling_backoff_ms, then recreate the table definition |
| Keeper unavailable or slow | S3Queue stores file tracking state in ClickHouse Keeper, so Keeper outages, quorum loss or high latency stall ingestion or cause retries | Check Keeper health and latency on each node and confirm the Keeper path in the table definition is correct. Restore quorum, then confirm that file processing resumes and the tracked file count moves again |
| Access denied errors from the bucket | The ClickHouse server credentials lack list or read permission on the bucket or prefix, or the bucket policy blocks the source network | Test listing and reading the same prefix with the same credentials outside ClickHouse. Grant the missing actions on the bucket and prefix, then watch the error log for new access errors |
| Schema mismatch between files and target table | Files written with a different column order, type or delimiter from the table structure, so inserts fail or fill columns incorrectly | Sample a few files from the prefix and compare them with the target schema. Correct the format or column definition, or route the bad files to a separate prefix, and confirm that the materialized view inserts without errors |
| Files skipped after tracking limits are reached | The number of tracked files exceeds tracked_files_limit, and older tracking entries expire under tracked_file_ttl_sec, so files can be treated as unseen or dropped from tracking | Compare the number of objects in the prefix with the tracked count and review both settings. Raise the limit or retention to cover the expected file volume, and reconcile missing files against your source listing |
| Duplicate rows after a replica or Keeper event | A replica restart or Keeper disruption during processing can cause files to be read again if tracking state was not committed, and the target table has no deduplication | Count rows per source file in the target table and look for repeated file sources. Add a deduplication step such as a ReplacingMergeTree target or a unique file key, and test the restart path on a spare cluster before production |
Questions teams ask before running S3Queue in production
What drives the cost of an S3Queue ingestion setup?
The cost depends mostly on the work around the engine rather than the engine itself, which ships with ClickHouse under the Apache License 2.0. The main cost components are cluster sizing for the ingest load, ClickHouse Keeper nodes that track processed files, the volume and file count in the bucket, schema design for the target MergeTree table, and the testing needed to prove that files are neither skipped nor loaded twice. Ask any provider to price each component separately so you can see which parts recur after go-live, such as monitoring, upgrades and cluster growth, and which are one-time build work.
How long does a typical S3Queue setup take?
Duration depends on how many buckets and file formats are involved, how clean the file naming is, whether a test cluster already exists, and how much schema work the target tables need. A single bucket with consistent Parquet or CSV files and an existing ClickHouse cluster is a much smaller job than several buckets with mixed formats feeding a multi-shard cluster. Ask for a timeline tied to a written scope that lists the buckets, formats, tables, environments and test cases, rather than a generic estimate.
What does our team need to provide before the work starts?
You need read access to the bucket through an IAM role or credentials stored in your secrets system, a description of the file naming scheme and how files are finalized, the target table schemas and the columns each file carries, and a test ClickHouse cluster with representative data volumes. You also need a named owner who can approve schema changes and a decision on how files that fail parsing will be handled. Without a test cluster, the first real run becomes the test, which is the riskiest way to find out how the engine treats your files.
Can our in-house team build and run S3Queue ingestion?
Yes, if the team already operates ClickHouse, understands MergeTree tables and has experience with object storage permissions. The skills that are easy to underestimate are Keeper operations, since file tracking depends on it, and the discipline of restore and replay tests that show the pipeline recovers cleanly. A common split is to keep operations in-house and bring in a partner for the initial design, the test plan and the monitoring setup. DeployOpen plans, implements and documents these systems in infrastructure you control, so the handover can be scoped to match your team's capacity.
How do we know files are not skipped or loaded twice?
You verify it with a test plan, not by assumption. Use a test bucket with known files, including a deliberately malformed file and a late-arriving file, then compare row counts and checksums in the target table against the source. Monitor the S3Queue processing state and the target table for gaps, and confirm that the engine's tracking settings match the volume you expect. Ordered and unordered modes behave differently when files arrive out of sequence, so choose the mode during design and test it with the arrival pattern you actually have.
When should we use the S3 table engine or copy data into MergeTree instead?
Use the S3 table engine or the s3 table function for ad hoc queries, one-off exploration, or data that is read rarely and does not need to be fast. Use S3Queue with a materialized view when new files must land continuously in a table that dashboards and users query often. Copy data into a MergeTree table when query speed, indexing, retention rules and replication matter more than keeping the files as the source of record. The right choice also depends on whether you need to reprocess history, which is easier when the raw files remain in the bucket.
What should we monitor after go-live?
Monitor the count of files pending, processed and failed in the S3Queue state, the ingest lag between a file landing in the bucket and its rows appearing in the target table, Keeper health and disk use, and errors in the server logs tied to the materialized view. Set alerts for files that stop progressing rather than only for hard errors, because a stalled queue can look healthy. Review these signals after every ClickHouse upgrade, and confirm with your vendor which system tables and metrics are available in the version you run.
How DeployOpen scopes and delivers S3Queue ingestion
DeployOpen plans, builds, documents and supports continuous ingestion from S3 into self-hosted ClickHouse inside your own cloud accounts or data centers. Each engagement starts with a written scope so the bucket layout, ordering needs, file tracking and monitoring are agreed before any table is created.
How we scope
- Inventory the S3 buckets, prefixes, file naming and arrival pattern that the S3Queue table will read.
- Decide with you whether ordered or unordered mode fits how producers write files and whether late files can appear.
- Record the Keeper, processing thread, tracked file limit and alerting requirements in the written scope before work starts.
How we build
- Create the S3Queue table, the target MergeTree table and the materialized view in a pre-production cluster with representative files.
- Test duplicate, malformed and out-of-order file cases so the load behavior is verified before production traffic arrives.
- Configure file tracking settings and processing threads against your volume, then confirm the behavior with load tests on your data.
How we hand over
- Deliver the table definitions, materialized view logic, configuration and runbook as versioned, documented artifacts.
- Provide dashboards and alert rules for processed, failed and skipped files, plus a checklist for reprocessing a file safely.
- Walk your operators through the recovery steps, including how to act when the tracked file limit is reached.
How we support
- Engagements are fixed-scope, with scope, deliverables and timelines agreed in writing before work starts.
- Ongoing support, version upgrades and continuous testing of the ingestion path are available as a retainer.
- Confirm with us during scoping which ClickHouse versions your upgrade plan targets and how changes to the S3Queue settings will be tested.
