In Redshift, how do you keep a table fed continuously without scheduling COPY jobs yourself?

## The problem these features solve The traditional Redshift ingest loop is an external scheduler: something notices new files in S3, issues a `COPY`, records what it loaded, and handles retries. That orchestration is real work and a real source of bugs — double loads on retry, missed files, a scheduler outage that silently stops ingest. Redshift has two managed alternatives that move the loop inside the warehouse. ## Auto-copy: a standing COPY job over an S3 prefix You define the load once, as a job attached to the COPY statement: ```sql COPY sales FROM 's3://landing/sales/' IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoad' FORMAT AS PARQUET JOB CREATE sales_autoload AUTO ON; ``` Redshift then watches the prefix and loads new objects as they arrive, without an external trigger. The important property is that it **tracks which files it has already loaded**, so a file that appears once is loaded once — the idempotency you would otherwise have to build into your scheduler. What it does not change is the physics from the previous questions. Files still need reasonable sizes; a producer dropping thousands of tiny objects will produce thousands of tiny loads, each paying commit and block overhead. Auto-copy removes the orchestration, not the need for sensible batching upstream. ## Streaming ingestion: Kinesis and MSK through a materialized view The streaming path skips object storage entirely. You declare an external schema over the stream source, then build a materialized view on it that refreshes itself: ```sql CREATE EXTERNAL SCHEMA kds FROM KINESIS IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftStream'; CREATE MATERIALIZED VIEW clicks_raw AUTO REFRESH YES AS SELECT approximate_arrival_timestamp, partition_key, sequence_number, JSON_PARSE(kinesis_data) AS payload FROM kds."clickstream" WHERE CAN_JSON_PARSE(kinesis_data); ``` Records arrive as a raw binary payload column alongside stream metadata — arrival time, partition key, sequence information for Kinesis; the equivalent topic, partition and offset metadata for MSK. Parsing is yours to do: `CAN_JSON_PARSE` guards against malformed records so one bad message does not fail the refresh, and `JSON_PARSE` turns the payload into a `SUPER` value you can navigate or shred into typed columns in a downstream transform. The usual shape is two layers: a thin raw materialized view that does nothing but land and parse, and a second transform that types, deduplicates and merges into the modelled fact table. ## What can go wrong - **Retention is the real deadline.** The materialized view refresh must keep up with the stream. If refreshes fall behind the source's retention window, records expire before they are read and are simply gone. Monitor refresh lag against retention, not just refresh success. - **Refresh cost is not free.** An auto-refreshing view competes for the same cluster resources as your queries. High-volume streams on a busy cluster need that contention thought about deliberately. - **At-least-once, not exactly-once.** Streams redeliver. The raw layer will contain duplicates, so the merge into the modelled table still needs the dedupe-and-upsert discipline of a batch pipeline. - **Small, frequent writes have the usual cost.** Continuous ingest means continuously appending unsorted rows and continuously creating maintenance work. Freshness measured in seconds is a real spend, not just a configuration flag. - **Not every view can refresh incrementally.** Complex SQL in the view can force a more expensive refresh, so keep the streaming view minimal and push the logic downstream. ## A third path worth naming For operational databases specifically, AWS offers zero-ETL integrations that replicate from Aurora into Redshift without a pipeline of your own. It is not part of the COPY family and its details differ, but in a design discussion about "how does data get in continuously" it belongs on the list of options alongside auto-copy and streaming ingestion. ## Choosing between them Ask what the source is and what freshness is actually required. Files arriving in S3 with minutes of tolerance: auto-copy, and keep the batches sensibly sized. Event streams with seconds of tolerance and a real business need for it: streaming ingestion, with monitoring on refresh lag versus retention. Everything else: a scheduled batch `COPY`, which remains the cheapest and most predictable option and is still what most warehouses run on.