9.4 Visualization, Notebooks, SQL Analytics, Sampling & Skew
Key Takeaways
- QuickSight dashboards and DataBrew profiles are consumption and exploration tools; correctness still depends on governed datasets, refresh status, row-level controls, and reconciliation.
- SQL window functions calculate rolling values without collapsing detail rows, while GROUP BY aggregates rows and conditional aggregation can implement pivots.
- Athena notebooks use Apache Spark for interactive exploration; move repeatable production logic into version-controlled jobs with bounded resources and observable outputs.
- Use random or stratified samples for representative analysis, targeted samples for rare errors, and key-frequency plus partition-size metrics to diagnose data skew.
9.4 Visualization, Notebooks, SQL Analytics, Sampling & Skew
Data operations include analyzing whether output is credible, not merely checking that a job reached SUCCEEDED. Exploration, visualization, SQL summaries, and sampling help an engineer find anomalies before consumers do.
Visualization and profiling
Amazon QuickSight builds datasets, analyses, and dashboards over supported sources. A dashboard can expose a sudden null spike, lagging Region, or distribution shift, but the visual is only as current and authorized as its dataset. Monitor ingestion or direct-query errors, refresh time, row count, and row-level-security rules. Publishing a dashboard is not proof that its underlying calculation is correct.
AWS Glue DataBrew provides visual profiling and recipe-based preparation. A profile can reveal missing values, duplicates, distributions, and correlations. Use it for exploration and repeatable low-code preparation, then persist recipe and job versions. For production enforcement, express critical expectations in Glue Data Quality or another test framework so failure has a defined pipeline outcome.
SQL aggregation and windows
GROUP BY collapses rows into one row per grouping key. A window function calculates across related rows while retaining each row.
| Need | SQL pattern |
|---|---|
| Daily revenue by store | SUM(amount) with GROUP BY store and date |
| Seven-day rolling average | AVG(daily_amount) OVER ordered rows frame |
| Latest record per customer | ROW_NUMBER OVER partition by customer ordered by event time descending |
| Categories as columns | Conditional SUM with CASE expressions or engine pivot support |
A rolling average needs an explicit frame. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW counts rows, not necessarily seven calendar days when dates are missing. Build a date spine or use the engine's time-range semantics when the business requires calendar windows.
Create views to centralize reusable logic, but remember that a standard view stores SQL, not materialized results. A materialized view stores derived results and needs refresh behavior. In Redshift, automatic refresh eligibility and incremental behavior depend on the definition; monitor staleness rather than assuming refresh is immediate.
Athena notebooks with Apache Spark
Athena notebooks provide interactive Apache Spark sessions for exploration and development. They can inspect data, test PySpark transformations, and visualize results without provisioning an EMR cluster. A notebook is not automatically a production pipeline. Interactive state, manually executed cells, and uncommitted code make a result hard to reproduce.
Move stable logic into version control, parameterize inputs and output paths, test it, and run it through a scheduled job or orchestrator. Stop idle sessions and set capacity controls because serverless removes cluster administration, not cost.
Provisioned versus serverless
Serverless services reduce capacity planning and fit bursty or intermittent work. Provisioned services can be better for steady utilization, custom configuration, predictable reserved capacity, or requirements not supported by the serverless variant.
Evaluate:
- startup latency and concurrency;
- sustained versus burst utilization;
- resource or configuration control;
- maximum runtime and workload isolation;
- networking and software dependencies;
- cost at the measured duty cycle.
Do not call serverless free when idle without checking minimum commitments, storage, requests, or provisioned-capacity settings.
Sampling techniques
A simple random sample gives each row an equal chance, but a rare class may be absent. A stratified sample samples within groups so important Regions, labels, or customer tiers are represented. A systematic sample selects every nth ordered record and can be biased by periodic patterns. Reservoir sampling maintains a fixed-size sample from a stream of unknown length.
For quality monitoring, combine representative samples with targeted samples of recent schema changes, rare categories, quarantined rows, and high-value records. Record the population, seed or method, strata, and sample time so results can be interpreted and reproduced.
Diagnosing data skew
Skew means partitions receive unequal work. Symptoms include one Spark task running far longer, a large maximum partition size, spill or OOM on one executor, Redshift distribution skew, or one Kinesis shard throttling while others are idle.
Measure key frequencies before choosing a remedy. Options include:
- Salt a hot join key and expand the matching small-side keys.
- Pre-aggregate high-volume keys before the shuffle.
- Broadcast a truly small dimension.
- Use adaptive query execution or engine skew-join handling where supported.
- Redesign partition or distribution keys around actual access patterns.
Random repartitioning can spread work but may destroy required co-location or ordering. Fix the business-key distribution intentionally, then compare maximum task duration and shuffle size before and after the change.
Operational publication
Every analytical output should expose source watermark, generation time, rule or query version, row count, and known exclusions. Consumers need to distinguish no events occurred from the refresh failed. Freshness and completeness belong next to the chart, not hidden in an operator log.
Which SQL feature computes a seven-row rolling average while retaining one result row for every input day?
A fraud label appears in only 0.1% of records, but every quality review must include enough fraud examples. Which sampling method best fits?
One Spark shuffle task holds most rows because a single key dominates the dataset. Which mitigation is most directly targeted at the cause?