Destination gotchas and troubleshooting
Destination-specific quirks and a troubleshooting matrix covering OOM errors, slow loads, duplicate rows, file bloat, and validation failures.
Known constraints and quirks, grouped by destination.
Snowflake
Auto-clusters in the background using warehouse credits.
cluster_bydoes not do real-time re-clustering.A warehouse must be active for every read/write. A small dedicated ingestion warehouse with
AUTO_SUSPEND = 60is the standard cost-control pattern.loader_file_format: parquetis auto-set. Safe to leave unset.
BigQuery
Bills per byte processed. Align
incremental_keywith the destination table's partition column so query jobs prune partitions.Set
loader_file_format: parquetexplicitly: parquet is not auto-set for BigQuery (the relevantPARQUET_SUPPORTED_DESTINATIONSentry is a stale typo at the time of writing).Clustered tables must be created with explicit DDL. BigQuery does not re-cluster existing tables in place. Re-clustering after-the-fact requires a
CREATE TABLE AS SELECTrebuild.staging_bucketmust live in the same region as the destination dataset.
DataOS Lakehouse
Iceberg is highly sensitive to file count. Keep
loader_file_sizelarge (200k to 500k rows) and partition counts low.loader_file_format: parquetis not auto-set. Set it explicitly.High-cardinality
partition_bywithtype: identitywill explode the manifest. Usetype: bucketinstead.
Redshift
incremental_strategy: replaceperformsDELETE+ reload, which is materially slower than Snowflake'sTRUNCATE+ reload.1,600 columns per table; 127-byte identifier limit. Wide source tables may need
sql_exclude_columnsto fit.Wire-protocol-compatible with PostgreSQL. Most PG-batch troubleshooting applies.
PostgreSQL
Partitioned tables on the source side need the dedicated runbook. See Working with PostgreSQL Partitioned Tables for CDC.
Identifier case folding (lower-case unless quoted) catches most "table not found" reports.
MongoDB
Destination
incremental_strategy: mergerequiresprimary_key(raisesMerge operation requires primary keys for table '<collection>').Destination
incremental_strategy: appendis not supported (raisesUnsupported write disposition 'append' for MongoDB destination).Source-side
filter_,projection,chunk_size,parallel,data_item_formatare not forwarded by the Nilus wrapper. Use thedatabase.collection:[<aggregation-pipeline>]form for source-side filtering.
Elasticsearch
Destination supports
replaceonly: drop-and-recreate-index semantics with batch_size=1000 and request_timeout=60.High-volume runs benefit from a pre-defined index template on the cluster side, since the destination does not configure mappings.
MS SQL Server
ODBC Driver 18 enforces TLS by default. Use
TrustServerCertificate=yesonly for development.Two auth modes: SQL Authentication, and Azure AD
Authentication=ActiveDirectoryAccessTokenviaMSSQL_COPT_SS_ACCESS_TOKEN.
AWS S3 file-system
incremental_strategy: replacerequiress3:DeleteObjectIAM permission.dest_tablemust be in<bucket-name>/<path>format, not a database-style schema.
Troubleshooting
Pipeline is OOMKilled
page_size × row_bytes × extract_parallelism exceeds pod memory
Lower extract_parallelism first, then page_size. Keep loader_file_size large. Raise pod memory if needed.
Run hits 100% jobs but stalls for many minutes
Commit-tail, too many small output files
Raise loader_file_size. Consider coarser partition_by. Ensure object-store bandwidth is adequate.
Source database throttles / rejects connections
extract_parallelism too high, or page_size too large for source's per-query budget
Lower both. Confirm the source's connection-pool limits.
Destination has thousands of tiny files
loader_file_size too low, or partition_by over-partitioned
Raise loader_file_size, simplify partitioning.
additionalProperties validation error on sink
Sink option is not in the schema's allow-list
Remove the option. Allowed sink options: aws_region, aws_endpoint, cluster_by, dest_table, incremental_strategy, loader_file_size, partition_by, staging_bucket.
MAX_TABLE_NESTING must be an integer, got 'foo'
max_table_nesting was set to a non-numeric string
Use "0", "1", or "2" (string-typed integers).
Merge operation requires primary keys for table
incremental_strategy: merge without a primary_key set on the source
Set primary_key on source.options, or switch to replace / append.
Run extracts the full table every time despite incremental_key
The source column is not actually monotonic, or pipeline state was reset
Confirm the column is truly monotonic and that incremental state still exists for the pipeline.
BigQuery jobs bill far more bytes than expected
incremental_key is not on a partition column, or the table is not partitioned, so the engine scans the whole table
Move incremental_key to the partition column (typically _PARTITIONTIME or an explicit DATE column).
Destination commit succeeds but data appears duplicated
incremental_strategy: append with overlapping interval_start/interval_end runs
Use merge for mutable rows; do not overlap windows in append runs.
Snowflake / Redshift / BigQuery loads are slow despite small data
loader_file_format defaulted to a non-parquet format, or staging_bucket is unset
Set loader_file_format: parquet; for very large loads, set staging_bucket: gs://... (BigQuery) or the equivalent for Snowflake/Redshift.
Stack-schema reference
Validation rules for source.options and sink.options:
source.options
additionalProperties: true (extras silently ignored if not forwarded)
source_table, primary_key, incremental_key, interval_start, interval_end, type_hints, mask, max_table_nesting, page_size, extract_parallelism, sql_limit, sql_exclude_columns, sql_reflection_level, yield_limit, plus connector-specific extras
sink.options
additionalProperties: false (extras rejected at YAML validation)
dest_table, incremental_strategy, loader_file_size, partition_by, cluster_by, staging_bucket, aws_region, aws_endpoint
loader_file_format is supplied at the runtime / CLI level (env var LOADER_FILE_FORMAT or pipeline runtime spec), not inside sink.options. The same is true for LOAD__WORKERS, NORMALIZE__WORKERS, and LOAD__PARALLELISM_STRATEGY.
Last updated
Was this helpful?