Connecting QuickSight, Tableau and Power BI to Amazon Redshift Without Wrecking Performance

Almost every Redshift engagement eventually turns into a BI conversation. The warehouse is modeled, the loads are incremental, the cost report looks sane — and then three hundred Tableau users and a pair of Power BI gateways start pointing at it, and the price-performance you tuned for evaporates. The consumption layer is where most Redshift bills go wrong, and it is the layer that data teams own least.

This tutorial covers how to connect Amazon QuickSight, Tableau and Power BI to Amazon Redshift properly: the connection patterns, where to cache, how to isolate BI workloads from ETL, and the specific settings that stop a dashboard refresh from becoming a full table scan.

Decide extract vs. live before you decide anything else

Every BI tool offers the same two modes under different names:

Tool	Cached / extracted	Live / direct
QuickSight	SPICE	Direct Query
Tableau	Extract (.hyper)	Live Connection
Power BI	Import	DirectQuery

The choice is an architecture decision, not a preference. Use cached mode when the dashboard serves a bounded dataset (say under a few hundred million rows after aggregation), the business tolerates data that is 15 minutes to a day old, and concurrency is high. Use live mode when users need current data, when row-level security must be evaluated per user at query time, or when the underlying dataset is too large to extract.

The cost consequence is blunt. A cached dashboard hits Redshift once per refresh. A live dashboard hits Redshift once per filter click, per user. With Redshift Serverless, that difference shows up directly as RPU-seconds; with provisioned RA3, it shows up as concurrency scaling clusters spinning up.

The pattern that works for most clients: live on a small number of curated aggregate tables, cached for everything else. Do not connect a BI tool to raw or staging schemas. Ever.

Build a reporting layer the BI tool is allowed to see

Point BI at a dedicated schema of purpose-built tables, not at your dimensional core and certainly not at raw landing tables.

CREATE SCHEMA IF NOT EXISTS reporting;

-- Pre-aggregated, BI-shaped, no surprises
CREATE TABLE reporting.sales_daily
DISTSTYLE KEY DISTKEY (customer_id)
SORTKEY (order_date)
AS
SELECT
    o.order_date,
    o.customer_id,
    c.region,
    c.segment,
    p.category,
    SUM(o.net_amount)     AS net_amount,
    SUM(o.quantity)       AS quantity,
    COUNT(DISTINCT o.order_id) AS orders
FROM core.fct_orders o
JOIN core.dim_customer c ON c.customer_id = o.customer_id
JOIN core.dim_product  p ON p.product_id  = o.product_id
GROUP BY 1,2,3,4,5;

For live dashboards, an incrementally refreshed materialized view is often the better container, because Redshift can also rewrite ad-hoc queries to use it automatically:

CREATE MATERIALIZED VIEW reporting.mv_sales_daily
AUTO REFRESH YES
AS
SELECT order_date, region, category, SUM(net_amount) AS net_amount
FROM core.fct_orders o
JOIN core.dim_customer c USING (customer_id)
JOIN core.dim_product  p USING (product_id)
GROUP BY 1,2,3;

Grant the BI service account read access to that schema and nothing else:

CREATE ROLE bi_reader;
GRANT USAGE ON SCHEMA reporting TO ROLE bi_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO ROLE bi_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting
    GRANT SELECT ON TABLES TO ROLE bi_reader;

CREATE USER bi_tableau PASSWORD DISABLE;   -- IAM / IdP authenticated
GRANT ROLE bi_reader TO bi_tableau;

PASSWORD DISABLE forces federated authentication. Static BI passwords sitting in a gateway config are the most common Redshift credential leak we find during reviews.

Isolate BI from ETL so neither can starve the other

There are two ways to keep a 9 a.m. dashboard stampede from colliding with the overnight load, and they are not mutually exclusive.

1. Query priority within one warehouse. Put BI users in their own workload management queue at a lower priority than ELT, or higher if dashboards are the SLA that matters:

-- Via the console/API you map user groups to WLM queues; in SQL you can
-- verify what a session actually resolved to:
SELECT user_id, query_priority, service_class, status
FROM SYS_QUERY_HISTORY
WHERE user_id = (SELECT usesysid FROM pg_user WHERE usename = 'bi_tableau')
  AND start_time > DATEADD(hour, -1, GETDATE())
ORDER BY start_time DESC;

Enable concurrency scaling for the BI queue specifically. Dashboard queries are short, repetitive and read-only — the ideal concurrency-scaling workload, and the credits usually cover it.

2. A separate consumer warehouse via data sharing. The stronger pattern: keep ELT on the producer, create a Redshift Serverless workgroup as a read-only consumer, and let every BI tool connect there. BI spikes scale that workgroup's RPUs and cannot touch load performance; the workgroup also pauses to zero cost overnight.

-- On the producer
CREATE DATASHARE bi_share;
ALTER DATASHARE bi_share ADD SCHEMA reporting;
ALTER DATASHARE bi_share ADD ALL TABLES IN SCHEMA reporting;
GRANT USAGE ON DATASHARE bi_share TO NAMESPACE '<bi-consumer-namespace-id>';

This also gives you clean chargeback: the BI workgroup's bill is the BI bill.

Amazon QuickSight

QuickSight is the cheapest path if your users are already in AWS, and its VPC connection support means the Redshift endpoint never needs to be public.

• Create a VPC connection in QuickSight, then add the QuickSight VPC connection's security group to your Redshift cluster's inbound rules on port 5439. Do not enable PubliclyAccessible on the cluster to make QuickSight work.
• For SPICE datasets, use a custom SQL query that pre-aggregates, and set an incremental refresh on a date column so each refresh scans a window rather than the whole table.
• SPICE refreshes are just Redshift queries. Stagger them. Twelve datasets all refreshing at 06:00 is a self-inflicted concurrency spike.
• For Direct Query dashboards, QuickSight leans on Redshift's result cache heavily — which only helps if the generated SQL is byte-identical between users. Per-user parameters defeat it.
• Prefer trusted identity propagation through IAM Identity Center so the Redshift query runs as the actual human, and your RLS policies apply server-side instead of in the BI layer.

Tableau

• Use the native Amazon Redshift connector, not a generic ODBC/JDBC entry. The native connector generates Redshift-aware SQL and supports initial SQL.
• Set SET query_group TO 'tableau'; as Initial SQL on the connection. That single line is what makes WLM routing and cost attribution possible later:

SET query_group TO 'tableau_prod';

Then you can see exactly what BI is costing you:

SELECT query_text, elapsed_time/1000000.0 AS seconds, returned_rows
FROM SYS_QUERY_HISTORY
WHERE query_label = 'tableau_prod'
  AND start_time > DATEADD(day, -1, GETDATE())
ORDER BY elapsed_time DESC
LIMIT 20;

• Extracts should be built by an incremental refresh on a monotonically increasing column, not a full refresh. Full extracts of a fact table are the single most expensive thing Tableau does to a warehouse.
• Turn off Assume Referential Integrity guessing and instead declare your joins in a published data source so every workbook inherits one join graph. Ad-hoc joins in individual workbooks are how you get six different revenue numbers.
• Watch out for Tableau's Quick Filter "show all values in database" option — it issues a SELECT DISTINCT against the full column on every dashboard load. Use "values in extract" or a small dedicated dimension table.

Power BI

• DirectQuery against Redshift works, but Power BI generates one query per visual. A twelve-visual page is twelve concurrent Redshift queries per page load per user. Budget for that or use Import mode.
• Use the built-in Amazon Redshift connector and enable Microsoft Entra ID / IAM federated auth rather than storing a database password in the on-premises data gateway.
• If you use Import mode, configure incremental refresh with RangeStart/RangeEnd parameters bound to a sortkey date column. Redshift will then prune to the partition range:

SELECT * FROM reporting.sales_daily
WHERE order_date >= @RangeStart AND order_date < @RangeEnd

• Avoid Table.Combine and other Power Query transformations that break query folding. When folding breaks, Power BI pulls the entire table and does the work locally — you will see a single enormous SELECT * in SYS_QUERY_HISTORY.
• The gateway needs VPC connectivity or a PrivateLink path. Public endpoints for Power BI gateways are a finding on every security review we have participated in.

Verify it with the system views

After the BI layer is live, spend an hour here. These two queries tell you whether your consumption layer is healthy.

Which BI queries are scanning the most data:

SELECT
    q.query_label,
    COUNT(*)                                   AS query_count,
    ROUND(AVG(q.elapsed_time)/1000000.0, 2)    AS avg_seconds,
    SUM(s.input_bytes)/1024/1024/1024          AS gb_scanned
FROM SYS_QUERY_HISTORY q
JOIN SYS_QUERY_DETAIL  s ON s.query_id = q.query_id
WHERE q.start_time > DATEADD(day, -7, GETDATE())
  AND q.query_label IN ('tableau_prod', 'powerbi_prod', 'quicksight')
GROUP BY 1
ORDER BY gb_scanned DESC;

Whether the result cache is actually earning its keep:

SELECT
    SUM(CASE WHEN result_cache_hit THEN 1 ELSE 0 END)::FLOAT / COUNT(*) AS cache_hit_rate,
    COUNT(*) AS total_queries
FROM SYS_QUERY_HISTORY
WHERE start_time > DATEADD(day, -1, GETDATE())
  AND query_type = 'SELECT';

A BI-heavy warehouse with a cache hit rate below roughly 20% usually has one of three problems: dashboards injecting GETDATE() or a session variable into every query, per-user filters preventing identical SQL, or DML landing on the underlying tables constantly and invalidating the cache. All three are fixable, and all three are cheaper to fix than to scale around.

A short checklist

• BI connects to a dedicated reporting schema through a federated, read-only role.
• Every BI tool sets a query_group / query label so its cost is attributable.
• Extract/Import refreshes are incremental and staggered, never full and simultaneous.
• Live dashboards read pre-aggregated tables or auto-refreshing materialized views.
• Concurrency scaling is on for the BI queue; ETL sits in a different queue or a different warehouse entirely.
• No public Redshift endpoint, and no static passwords in any gateway.
• SYS_QUERY_HISTORY is reviewed weekly with the BI team in the room.

Where this usually goes wrong in practice

The recurring failure mode is not technical. It is that the BI team and the data platform team never agree on which tables are contract and which are internal, so dashboards end up bound to staging tables that change shape, and every warehouse optimization breaks a report. Publishing a reporting schema with a stated refresh cadence and a stated stability guarantee fixes more Redshift performance problems than any distribution key change.

If you are standing up a reporting layer, splitting BI onto its own Serverless consumer warehouse, or trying to work out why your dashboard refreshes cost more than your ETL, our senior Redshift consultants do this as a scoped engagement. Get in touch and we will look at your SYS_QUERY_HISTORY with you.