Does a SPICE dataset automatically stay in sync with the source data?

No, SPICE is an in-memory snapshot imported at ingestion time, it only reflects source data as of the last successful refresh (scheduled or manual), not live. A SPICE dataset without a configured refresh schedule keeps showing the data from whenever it was first imported indefinitely, this is one of the most common causes of a QuickSight dashboard appearing to show stale numbers when the underlying source data has actually changed.

What's the tradeoff between SPICE and DIRECT_QUERY import modes?

SPICE offers much faster query performance for dashboard viewers since data is pre-loaded into QuickSight's in-memory engine, but introduces data staleness bounded by refresh frequency and has row-count limits per dataset. DIRECT_QUERY always reflects live source data (no staleness) but query performance depends entirely on the underlying data source's live query speed, and heavy dashboard traffic translates directly into query load on the source database.

Does RowLevelPermissionDataSet filtering apply before or after DataTransforms in LogicalTableMap?

Row-level permission filtering is applied by QuickSight as an access-control layer on the resulting dataset, it's evaluated independently of and in addition to the LogicalTableMap's own FilterOperation-based transforms, both filtering mechanisms can apply simultaneously, one for business-logic filtering (visible to everyone using the dataset) and one for per-user access control.

AWS QuickSight Dataset Builder

MonitoringAWS

Build QuickSight dataset configurations with physical/logical tables, calculated fields, SPICE import, and row-level security.

Amazon QuickSight Configuration

Build QuickSight dataset configurations with physical/logical tables, calculated fields, SPICE import, and row-level security.

Required Fields

AwsAccountIdDataSetIdNameImportModePhysicalTableMap

{
  "AwsAccountId": "123456789012",
  "DataSetId": "sales-analytics-dataset",
  "Name": "Sales Analytics Dataset",
  "ImportMode": "SPICE",
  "PhysicalTableMap": {
    "sales-table": {
      "RelationalTable": {
        "DataSourceArn": "arn:aws:quicksight:us-east-1:123456789012:datasource/redshift-sales",
        "Catalog": "sales_db",
        "Schema": "public",
        "Name": "sales_transactions",
        "InputColumns": [
          {"Name": "transaction_id", "Type": "STRING"},
          {"Name": "customer_id", "Type": "STRING"},
          {"Name": "product_name", "Type": "STRING"},
          {"Name": "amount", "Type": "DECIMAL"},
          {"Name": "quantity", "Type": "INTEGER"},
          {"Name": "transaction_date", "Type": "DATETIME"},
          {"Name": "region", "Type": "STRING"}
        ]
      }
    }
  },
  "LogicalTableMap": {
    "sales-logical": {
      "Alias": "SalesData",
      "Source": {
        "PhysicalTableId": "sales-table"
      },
      "DataTransforms": [
        {
          "RenameColumnOperation": {
            "ColumnName": "transaction_date",
            "NewColumnName": "Date"
          }
        },
        {
          "CreateColumnsOperation": {
            "Columns": [
              {
                "ColumnName": "Revenue",
                "ColumnId": "revenue-calc",
                "Expression": "{amount} * {quantity}"
              }
            ]
          }
        },
        {
          "FilterOperation": {
            "ConditionExpression": "{amount} > 0"
          }
        }
      ]
    }
  },
  "Permissions": [
    {
      "Principal": "arn:aws:quicksight:us-east-1:123456789012:group/default/Analysts",
      "Actions": [
        "quicksight:DescribeDataSet",
        "quicksight:ListIngestions",
        "quicksight:PassDataSet"
      ]
    }
  ],
  "RowLevelPermissionDataSet": {
    "Arn": "arn:aws:quicksight:us-east-1:123456789012:dataset/row-level-security",
    "PermissionPolicy": "GRANT_ACCESS",
    "FormatVersion": "VERSION_2"
  },
  "DataSetUsageConfiguration": {
    "DisableUseAsDirectQuerySource": false,
    "DisableUseAsImportedSource": false
  },
  "Tags": [
    {
      "Key": "Department",
      "Value": "analytics"
    }
  ]
}

Generated Output

Output will appear here...

What This Tool Does

Build a QuickSight dataset combining a PhysicalTableMap (the raw source table connection) with a LogicalTableMap layering DataTransforms (rename, calculated column, filter) on top, plus ImportMode SPICE for QuickSight's in-memory engine versus DIRECT_QUERY for live pass-through to the source. SPICE has real per-dataset row and refresh-frequency limits and requires an explicit, scheduled or manual refresh to pick up new source data, a SPICE dataset without a configured refresh schedule shows increasingly stale data indefinitely, since SPICE is a snapshot import, not a live connection, a common cause of 'why does this dashboard show old numbers' confusion.

Technical Details

The builder validates that AwsAccountId, DataSetId, Name, ImportMode, and PhysicalTableMap all resolve before accepting the JSON as a valid CreateDataSet request, the fields QuickSight needs to identify the dataset and its underlying physical source table; it can't verify the referenced DataSourceArn is reachable or that calculated field expressions in DataTransforms reference columns that actually exist in the physical table, those errors surface only when QuickSight actually processes the dataset.

Common Use Cases

1Using a CreateColumnsOperation to define a calculated field (Revenue as amount * quantity) once in the LogicalTableMap, so every analysis built on this dataset gets a consistent revenue calculation instead of each dashboard author re-deriving the formula slightly differently.
2Choosing SPICE ImportMode for a dataset feeding frequently-viewed executive dashboards, trading off data freshness (bounded by refresh schedule) for much faster query performance than hitting the source database live on every dashboard view.
3Setting RowLevelPermissionDataSet to point at a separate row-level-security dataset with PermissionPolicy GRANT_ACCESS, so different QuickSight users see different subsets of the same underlying data based on their identity, without maintaining separate datasets per user segment.
4Scoping Permissions to a specific QuickSight group (Analysts) with narrowly-listed actions (DescribeDataSet, ListIngestions, PassDataSet) rather than granting broader dataset management permissions that group doesn't need.

Common Questions

Does a SPICE dataset automatically stay in sync with the source data?: No, SPICE is an in-memory snapshot imported at ingestion time, it only reflects source data as of the last successful refresh (scheduled or manual), not live. A SPICE dataset without a configured refresh schedule keeps showing the data from whenever it was first imported indefinitely, this is one of the most common causes of a QuickSight dashboard appearing to show stale numbers when the underlying source data has actually changed.
What's the tradeoff between SPICE and DIRECT_QUERY import modes?: SPICE offers much faster query performance for dashboard viewers since data is pre-loaded into QuickSight's in-memory engine, but introduces data staleness bounded by refresh frequency and has row-count limits per dataset. DIRECT_QUERY always reflects live source data (no staleness) but query performance depends entirely on the underlying data source's live query speed, and heavy dashboard traffic translates directly into query load on the source database.
Does RowLevelPermissionDataSet filtering apply before or after DataTransforms in LogicalTableMap?: Row-level permission filtering is applied by QuickSight as an access-control layer on the resulting dataset, it's evaluated independently of and in addition to the LogicalTableMap's own FilterOperation-based transforms, both filtering mechanisms can apply simultaneously, one for business-logic filtering (visible to everyone using the dataset) and one for per-user access control.

Expert Tips

TIP

Always pair a SPICE dataset with an explicit refresh schedule appropriate to how fresh the data genuinely needs to be, an unscheduled SPICE dataset is a silent staleness trap that looks fine until someone notices the numbers don't match the source.

TIP

Define calculated fields and renames once in LogicalTableMap's DataTransforms rather than letting each dashboard author recreate the same calculated field independently, inconsistent metric definitions across dashboards (three different 'revenue' formulas) is a common and avoidable analytics credibility problem.

TIP

Row-level security via RowLevelPermissionDataSet needs its own careful testing per user/group, a misconfigured row-level permission dataset can either over-expose data (a filter condition that doesn't actually restrict anything) or under-expose it (a condition that's too restrictive and hides legitimate rows).

Related Learning Guides

DynamoDB Data Modeling28 min read
Cost Optimization Strategies26 min read

Was this tool helpful?

Disclaimer: This tool runs entirely in your browser. No data is sent to our servers. Always verify outputs before using them in production. AWS, Azure, and GCP are trademarks of their respective owners.

AWS QuickSight Dataset Builder

MonitoringAWS

Build QuickSight dataset configurations with physical/logical tables, calculated fields, SPICE import, and row-level security.

Amazon QuickSight Configuration

Build QuickSight dataset configurations with physical/logical tables, calculated fields, SPICE import, and row-level security.

Required Fields

AwsAccountIdDataSetIdNameImportModePhysicalTableMap

Generated Output

Output will appear here...

What This Tool Does

Technical Details

Common Use Cases

1Using a CreateColumnsOperation to define a calculated field (Revenue as amount * quantity) once in the LogicalTableMap, so every analysis built on this dataset gets a consistent revenue calculation instead of each dashboard author re-deriving the formula slightly differently.
2Choosing SPICE ImportMode for a dataset feeding frequently-viewed executive dashboards, trading off data freshness (bounded by refresh schedule) for much faster query performance than hitting the source database live on every dashboard view.
3Setting RowLevelPermissionDataSet to point at a separate row-level-security dataset with PermissionPolicy GRANT_ACCESS, so different QuickSight users see different subsets of the same underlying data based on their identity, without maintaining separate datasets per user segment.
4Scoping Permissions to a specific QuickSight group (Analysts) with narrowly-listed actions (DescribeDataSet, ListIngestions, PassDataSet) rather than granting broader dataset management permissions that group doesn't need.

Common Questions

Does a SPICE dataset automatically stay in sync with the source data?: No, SPICE is an in-memory snapshot imported at ingestion time, it only reflects source data as of the last successful refresh (scheduled or manual), not live. A SPICE dataset without a configured refresh schedule keeps showing the data from whenever it was first imported indefinitely, this is one of the most common causes of a QuickSight dashboard appearing to show stale numbers when the underlying source data has actually changed.
What's the tradeoff between SPICE and DIRECT_QUERY import modes?: SPICE offers much faster query performance for dashboard viewers since data is pre-loaded into QuickSight's in-memory engine, but introduces data staleness bounded by refresh frequency and has row-count limits per dataset. DIRECT_QUERY always reflects live source data (no staleness) but query performance depends entirely on the underlying data source's live query speed, and heavy dashboard traffic translates directly into query load on the source database.
Does RowLevelPermissionDataSet filtering apply before or after DataTransforms in LogicalTableMap?: Row-level permission filtering is applied by QuickSight as an access-control layer on the resulting dataset, it's evaluated independently of and in addition to the LogicalTableMap's own FilterOperation-based transforms, both filtering mechanisms can apply simultaneously, one for business-logic filtering (visible to everyone using the dataset) and one for per-user access control.

Expert Tips

TIP

Related Learning Guides

DynamoDB Data Modeling28 min read
Cost Optimization Strategies26 min read

Was this tool helpful?