Amazon Quick Sight • Data Engineering Best Practices
1. Overview
In Amazon Quick, Amazon Quick Sight datasets built directly on Redshift or RDS tables are sensitive to structural changes in the underlying database schema. When a table undergoes column renames, data type changes, or column removals, all dependent Quick Sight analyses and dashboards will error out — often requiring manual intervention to restore functionality.
This document outlines a recommended approach using a semantic view layer to decouple Quick Sight datasets from underlying table structures, enabling graceful handling of schema changes with zero impact to end users.
Common Schema Changes That Break Quick Sight
| Change Type | Description | Impact Without View Layer |
|---|---|---|
| Column Rename | Column identifier changes (e.g., Profit → Revenue) | Dataset field reference breaks; dashboard errors |
| Data Type Change | Column type changes (e.g., Integer → String) | Type mismatch causes dataset refresh failure |
| Column Removal | Column is dropped from the source table | Missing field errors in all dependent visuals |
2. Recommended Solution: Semantic View Layer
The recommended approach is to introduce a semantic layer in the database by creating a dedicated schema (e.g., view_db) that contains database views pointing to the underlying tables. Quick Sight datasets reference these views — not the tables directly.
When a table definition changes, the update is absorbed at the view level. Quick Sight continues to read the same view structure and remains unaffected.
Key Benefits
- Decoupling: Quick Sight is isolated from table-level structural changes.
- Continuity: Dashboards and analyses continue to function without modification.
- Flexibility: Schema changes can be applied and tested independently before views are updated.
- Governance: A clear interface boundary makes impact analysis and change management easier.
Architecture
The pattern introduces a two-layer data access model:
- Source layer — fact_db schema holds the physical tables (e.g., fact_db.Total_Sales).
- Semantic layer — view_db schema holds views that map to the source tables (e.g., view_db.Total_Sales).
- Quick Sight datasets connect exclusively to the view_db schema.
3. Implementation Example
3.1 Source Table Definition
Consider the following table in the fact_db schema:
| Column | Data Type | Description |
|---|---|---|
| Customer_ID | INTEGER | Unique customer identifier |
| Year_month | DATE | Sales reporting period |
| Sales | DECIMAL | Total sales amount |
| Profit | DECIMAL | Net profit amount |
3.2 Semantic View Definition
Create a corresponding view in the view_db schema. Quick Sight datasets should reference this view:
CREATE OR REPLACE VIEW view_db.Total_Sales AS
SELECT
Customer_ID AS Customer_ID,
Year_month AS Year_month,
Sales AS Sales,
Profit AS Profit
FROM fact_db.Total_Sales;
With this setup in place, Quick Sight connects to view_db.Total_Sales and is shielded from any downstream structural changes to the underlying table.
4. Handling Common Schema Changes
The following examples demonstrate how each type of schema change is absorbed at the view level.
4.1 Column Rename
Scenario: The column Profit is renamed to Revenue in fact_db.Total_Sales.
Resolution: Update the view to alias the new column name back to the original name expected by Quick Sight.
CREATE OR REPLACE VIEW view_db.Total_Sales AS
SELECT
Customer_ID AS Customer_ID,
Year_month AS Year_month,
Sales AS Sales,
Revenue AS Profit -- Column renamed in source; alias preserves Quick Sight field name
FROM fact_db.Total_Sales;
4.2 Data Type Change
Scenario: The data type of Customer_ID is changed from INTEGER to VARCHAR.
Resolution: Use an explicit CAST in the view to maintain the data type expected by Quick Sight.
CREATE OR REPLACE VIEW view_db.Total_Sales AS
SELECT
CAST(Customer_ID AS VARCHAR) AS Customer_ID, -- Cast to preserve expected type
Year_month AS Year_month,
Sales AS Sales,
Revenue AS Profit
FROM fact_db.Total_Sales;
4.3 Column Removal
Scenario: The column Profit is dropped entirely from fact_db.Total_Sales.
Resolution: Return a placeholder value (e.g., 0 or NULL) under the original column alias so that the Quick Sight field reference remains valid.
CREATE OR REPLACE VIEW view_db.Total_Sales AS
SELECT
CAST(Customer_ID AS VARCHAR) AS Customer_ID,
Year_month AS Year_month,
Sales AS Sales,
0 AS Profit -- Column removed from source; placeholder preserves field contract
FROM fact_db.Total_Sales;
5. Operational Best Practices
Beyond the technical pattern, a structured change management process ensures that schema changes are handled safely and collaboratively.
- Treat schema changes as formal change requests. Any structural change to a source table should be raised as a support ticket or change request before implementation.
- Identify all impacted teams. Before applying a change, assess which teams, datasets, and dashboards are affected.
- Perform impact analysis. Review all views and Quick Sight datasets that reference the affected table to understand downstream effects.
- Update views as part of the change. View definitions should be updated in the same change window as the table modification to minimize any gap in data availability.
- Test before promoting. Validate the updated view in a non-production environment before applying changes to production datasets.
- Communicate proactively. Notify downstream teams in advance so they can plan for any temporary disruption or testing requirements.
6. Summary
Introducing a semantic view layer is a lightweight but highly effective pattern for protecting Quick Sight dashboards from upstream database schema changes. By routing all Quick Sight dataset connections through managed views, organizations can:
- Absorb column renames, type changes, and column removals without modifying Quick Sight datasets.
- Maintain dashboard continuity and eliminate unplanned analysis failures.
- Establish a clean separation of concerns between the data engineering and analytics layers.
- Enforce a consistent change management process for schema modifications.
This pattern is broadly applicable to any Redshift or RDS-backed Quick Sight deployment and requires no changes to existing Quick Sight configuration — only a one-time setup of the view_db semantic schema and an updated dataset connection.
About the Authors
Vaidy Janardhanam is a Specialist Solutions Architect at AWS focused on Agentic AI and Generative BI with Amazon Quick. Vaidy works closely with partners and customers to architect AI-powered analytics solutions and drive adoption of unified AI workspaces.
Pegah Ojaghi (@parmisa) is a Generative AI Applied Architect at AWS with a PhD in Computer Science focused on large language models, generative AI, and reinforcement learning. Her expertise and research span foundation model development, RLHF techniques, and novel optimization methods for LLMs. Her passion is translating cutting-edge research into production systems across healthcare, financial services, and insurance industries.