QuickSight and the consumer end

Lesson 5 of 5 in Analyzing Data.

A data engineer's work is finished when somebody reads a number and acts on it. Which makes the BI layer worth understanding properly, because the consumer's requirements propagate all the way back up the pipeline — and they propagate as three questions, each of which decides something you build.

How fresh must the number be? This decides whether a dashboard reads through to the source every time (direct query) or from an imported snapshot (SPICE), and if SPICE, how often it refreshes. It also decides how aggressive your result reuse max age may be, and whether a materialized view needs auto refresh. A dashboard that genuinely needs sub-minute freshness is an architectural requirement, not a preference, and it costs real money upstream.

How many people will read it, how often? This decides whether every visual becomes a query against your warehouse. Twenty analysts opening a six-visual dashboard on direct query is a hundred and twenty queries; the same dashboard on SPICE is zero. At that point SPICE is not a BI setting, it is a load-shedding decision about your warehouse.

Who may see which rows? Row-level and column-level security belong in the dataset, because a filter applied in a visual is a presentation choice that the next author can remove. Getting this wrong is not a performance problem, it is a disclosure.

The failure mode specific to this layer, and the one worth internalising: a dashboard has no natural way to say how old it is. The pipeline can be broken for a day and the dashboard will render beautifully.

Direct query or SPICE — and what each choice sends upstream

  1. A consumer needs a dashboard

    Start from what they will do with it, not from the chart type. "Decide whether to reroute a lorry" and "review last quarter" have different freshness requirements by three orders of magnitude.

  2. How fresh must it be?

    The one question that cannot be renegotiated later without rebuilding. Ask for the decision latency — how long after an event must someone be able to act — rather than asking how fresh they would like it, to which the answer is always "live".

  3. Direct query

    Every visual queries the source on every view, so the data is as current as the source. The cost is that every reader is load on your warehouse or another Athena scan — and filter controls refresh on their own 24-hour cycle, which surprises people.

  4. SPICE import

    An in-memory snapshot serves dashboards fast and insulates the warehouse completely. The data is exactly as current as the last successful refresh, and nothing in the interface tells the reader when that was.

  5. Full or incremental refresh?

    Full replaces the dataset. Incremental queries only a look-back window on a date column and replaces that window — SQL-based sources only. Up to five schedules per dataset, as frequent as 15 minutes, with an API for refreshes in between.

  6. Propagate the requirement upstream

    A 15-minute dashboard means the pipeline must land data every 15 minutes, the materialized view must refresh, and the Athena result-reuse max age must be under 15 minutes. One consumer promise, four upstream settings.

  7. Row and column security on the dataset

    Attach rules to the dataset, so they apply to every analysis and dashboard built on it and cannot be removed by an author. A filter inside one visual is a presentation choice, not a control.

  8. Alarm on refresh failure and on freshness

    A failed SPICE refresh leaves the previous snapshot in place with no error on the dashboard. So publish the dataset’s age as a metric and alarm on it, and alarm on refresh failures from the API — because the reader will not notice and will act on the number anyway.

Two details about SPICE refreshes are worth stating exactly, because they are the mechanism behind the most common "the dashboard is wrong" conversation.

Incremental refresh is a look-back window, not a change feed. You nominate a date column and a window — say seven days. Each refresh queries the source for rows in that window, deletes the data currently in SPICE for the same window, and appends what it just read. That is why it is efficient (7,000 rows rather than 180,000) and also why it is blind in two specific ways: a row older than the window that changed will never be picked up, and a row whose date column is itself corrected to fall outside the window disappears from the dataset rather than moving. It is available for SQL-based sources only, and the documentation notes that complex custom SQL may not optimise the window filter at all, making an incremental refresh slower than a full one.

A failed refresh is a successful dashboard. SPICE keeps the existing dataset active until a refresh completes, so dashboards never flicker and never show a half-loaded state — admirable, and the reason a week of failed refreshes is invisible. The dataset is available, the visuals render, the numbers are old. The only defences are an alarm on refresh failure and a visible as-of timestamp on the dashboard itself, which costs one calculated field and converts a silent failure into a thing a reader notices.

That second point is the bridge to the next module. The consumer end is where freshness stops being an abstraction, because it is the only place where somebody acts on the number.

Interactive sorting exercise: Each requirement below is satisfied at exactly one layer. Sort them — the point is that several things that feel like dashboard settings are actually pipeline commitments.

Key terms: SPICE, Freshness check, Materialized view, Athena query result reuse

Interactive checkpoint quiz (2 questions) — open this page in a browser to take it.