RougeWarehouse | Multi-Warehouse Writes with Redshift Data Sharing: Isolating ETL from BI

Most Redshift estates eventually hit the same wall: one cluster runs the ELT jobs, serves the BI dashboards, and hosts the data science sandbox, and all three fight over the same slots. The classic fix was data sharing — a producer warehouse and read-only consumers. The gap in that design was writes. Every job that needed to create or update a table had to run on the producer, which is exactly the workload you wanted to move off.

Multi-data-warehouse writes through data sharing close that gap. A consumer warehouse can now run INSERT, UPDATE, DELETE, COPY, CREATE TABLE and MERGE against objects in a producer's database, using its own compute, while the data stays in one place. This tutorial covers when that is the right architecture, how to set it up end to end, how permissions actually behave, and the operational traps we hit on client engagements.

When multi-warehouse writes are the right tool

Reach for this pattern when:

ETL isolation. A heavy nightly transformation run should not compete with interactive dashboards. Put the ETL on its own Serverless workgroup sized for throughput, and let the BI warehouse stay small and responsive.
Per-team cost attribution. Each warehouse has its own bill. When the marketing team's ingestion job runs on the marketing workgroup, the chargeback conversation writes itself.
Spiky ingestion. A weekly backfill that needs 512 RPUs for two hours shouldn't force you to size the primary warehouse for it all week.
Different workload profiles. Short, concurrent writes and long analytical scans want different concurrency scaling and query priority settings. Separate warehouses give you separate knobs.

It is not a replacement for schema-per-team inside one warehouse when your total workload is modest. Two warehouses are two things to monitor, patch and secure. Below roughly a few hundred RPU-hours a month, the operational overhead usually outweighs the isolation.

Prerequisites

Producer and consumer must both be RA3 provisioned clusters or Redshift Serverless workgroups. DC2 cannot participate.
Both must be on a current patch; if your provisioned cluster is pinned to an old maintenance track, move it to current first.
Producer and consumer can be in the same account or different accounts, same or different Regions. Cross-Region adds data transfer cost and latency on every write — test before committing.

Step 1 — Create the datashare with write scope on the producer

On the producer, create the datashare and add the objects the consumer will write to. The key difference from a read-only share is the GRANT of write privileges on the datashare itself.

-- Producer, database "warehouse"
CREATE DATASHARE etl_share;

ALTER DATASHARE etl_share ADD SCHEMA staging;
ALTER DATASHARE etl_share ADD TABLE staging.orders_raw;
ALTER DATASHARE etl_share ADD ALL TABLES IN SCHEMA staging;
ALTER DATASHARE etl_share SET INCLUDENEW = TRUE FOR SCHEMA staging;

-- Write privileges that travel with the share
GRANT USAGE, CREATE ON SCHEMA staging TO DATASHARE etl_share;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE
  ON ALL TABLES IN SCHEMA staging TO DATASHARE etl_share;

CREATE ON SCHEMA is what lets the consumer create new tables inside the producer's schema — the privilege most people forget, then spend an hour debugging a permission denied for schema staging error that names the schema, not the datashare.

Grant the share to the consumer namespace or account as usual:

GRANT USAGE ON DATASHARE etl_share TO NAMESPACE 'c1d2e3f4-5678-90ab-cdef-1234567890ab';
-- or, cross-account:
GRANT USAGE ON DATASHARE etl_share TO ACCOUNT '222222222222';

Cross-account shares still need the producer-side authorization (console, or aws redshift authorize-data-share) and the consumer-side association before anything is visible.

Step 2 — Mount the share on the consumer

-- Consumer namespace
CREATE DATABASE etl_db FROM DATASHARE etl_share
  OF ACCOUNT '111111111111' NAMESPACE 'a1b2c3d4-...';

Then hand the privileges to the role that actually runs the jobs. Privileges arrive on the consumer as grantable only if the producer granted them to the datashare; the consumer's superuser then re-grants locally:

GRANT USAGE ON DATABASE etl_db TO ROLE etl_runner;
GRANT ALL ON SCHEMA etl_db.staging TO ROLE etl_runner;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA etl_db.staging TO ROLE etl_runner;

Step 3 — Write across the share

Writes use three-part naming and behave like local DML:

-- Load into the producer's schema from the consumer's compute
COPY etl_db.staging.orders_raw
FROM 's3://acme-landing/orders/dt=2026-02-11/'
IAM_ROLE 'arn:aws:iam::222222222222:role/RedshiftConsumerLoad'
FORMAT AS PARQUET;

-- Transform in place
MERGE INTO etl_db.core.dim_customer AS t
USING etl_db.staging.customer_delta AS s
  ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET name = s.name, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT VALUES (s.customer_id, s.name, s.updated_at);

-- Create a new table directly in the producer's schema
CREATE TABLE etl_db.staging.orders_dedup AS
SELECT DISTINCT * FROM etl_db.staging.orders_raw;

Note the IAM role in the COPY: the credentials come from the consumer side, because the consumer's compute reads S3. A very common failure is a producer-owned bucket policy that never granted the consumer role access.

Transactions are scoped to a single warehouse — you cannot open one transaction that spans a local table and a shared table in a single atomic unit. Design your pipelines so the unit of work lives on one side.

Concurrency and conflicts

Writes from multiple warehouses to the same table are serialized. Two consumers updating the same rows will produce a serializable isolation error on one of them, exactly as two sessions on one cluster would. Practical rules we apply:

Partition ownership by table or by date partition. One writer per target table is the calm design.
Retry serialization failures with backoff in the orchestrator rather than fighting them in SQL.
Prefer append-then-swap over in-place UPDATE for large rebuilds.

Observability

Query history lives with the warehouse that ran the query, not with the warehouse that owns the data. To see who wrote to a producer table, look at the producer's SYS_QUERY_HISTORY — cross-warehouse writes are recorded there too — and correlate on user_id plus database_name:

SELECT start_time, database_name, user_id, LEFT(query_text, 120) AS q
FROM SYS_QUERY_HISTORY
WHERE database_name = 'warehouse'
  AND query_type IN ('INSERT','UPDATE','DELETE','COPY','CTAS')
ORDER BY start_time DESC
LIMIT 50;

Also watch SVV_DATASHARE_OBJECTS on both sides after every schema change; an INCLUDENEW schema silently expands the blast radius of your grants.

Traps we see in the field

Missing CREATE ON SCHEMA — read works, CREATE TABLE fails. Check the grant to the datashare, not the user.
Cross-Region writes. Every row crosses the Region boundary. Fine for a few million rows a night, painful for a terabyte.
Consumer-side maintenance assumptions. VACUUM and ANALYZE on shared tables still belong to the producer. Schedule them there, or statistics go stale and the consumer's plans degrade for reasons nobody can find.
Two writers, one table. See the concurrency section. This is the number-one cause of "data sharing is flaky" complaints; it is not flaky, it is correctly serializable.
IAM confusion on COPY/UNLOAD. Credentials follow compute. Document which account owns which bucket policy before you build.

A sensible target architecture

For a mid-size estate we usually land on: one RA3 producer holding the conformed warehouse; one Serverless workgroup for ELT with a generous base RPU and a price-performance target tuned for throughput; one smaller Serverless workgroup for BI with concurrency scaling on; and read-only datashares for any data science sandbox. Loads and transforms write across the share from the ELT workgroup, dashboards read from the BI workgroup, and the producer itself runs almost no user queries.

If you are weighing this against simply making one cluster bigger, or you are stuck on a permissions error that makes no sense, our Redshift architects do this work every week — get in touch and we will walk your setup with you.