Many applications produce JSON documents that do not match a fixed database schema. Logs, API payloads, transaction events, product metadata, and application telemetry can all contain different fields from one record to another.
A common solution is to preprocess these JSON documents before loading them into a data warehouse. That might mean writing Python scripts, Lambda functions, ETL jobs, or custom parsers that read the JSON, extract fields, transform the structure, and create another file for Redshift.
That approach works, but it adds another processing layer.
Amazon Redshift can work directly with JSON data and store semi-structured content in the SUPER data type. This makes it possible to design ingestion pipelines where the original JSON structure can reach Redshift with much less preprocessing.
The important part is understanding what “without preprocessing” actually means. It does not mean that every JSON file can be loaded blindly. The source format, JSON structure, ingestion method, validation requirements, and target table design still need to be considered.
This article walks through a practical approach to loading large JSON documents into Redshift while keeping the ingestion pipeline simple.
Why Preprocessing JSON Becomes a Problem
Suppose an application produces an event like this:
{
"event_id": "evt-1001",
"event_type": "purchase",
"timestamp": "2026-11-09T10:30:00Z",
"customer": {
"id": 501,
"region": "IN"
},
"order": {
"id": "ORD-1001",
"amount": 2499,
"currency": "INR"
},
"items": [
{
"sku": "KB-100",
"quantity": 1
}
]
}
A traditional ETL pipeline might perform these steps:
JSON file
|
v
Parser
|
v
Extract fields
|
v
Transform structure
|
v
Create warehouse-ready data
|
v
Redshift
Every transformation layer creates another place where failures can occur.
A change in the JSON structure may require changes to the parser. A new optional property may require another mapping. A nested array may require additional transformation logic.
With SUPER, the pipeline can be simplified:
JSON file
|
v
Redshift ingestion
|
v
SUPER column
The JSON structure can remain available for later analytical processing.
Create a Table for JSON Data
Start with a Redshift table that contains a SUPER column.
CREATE TABLE application_events
(
event_id VARCHAR(100),
event_data SUPER
);
The first column can contain an identifier used for operational or analytical queries, while the SUPER column stores the flexible document.
You can also combine SUPER with additional relational columns.
CREATE TABLE application_events
(
event_id VARCHAR(100),
event_type VARCHAR(100),
event_time TIMESTAMP,
event_data SUPER
);
This hybrid design is often useful when a few attributes are consistently important while the rest of the payload changes over time.
Understanding the Difference Between JSON Text and SUPER
A JSON document arriving from an application is text.
For example:
{
"event": "login",
"user": {
"id": 1001
}
}
Redshift needs to represent that JSON as a SUPER value before you can work with it as semi-structured data.
When inserting JSON through SQL, JSON_PARSE can convert JSON text into SUPER.
INSERT INTO application_events
(
event_id,
event_data
)
VALUES
(
'evt-1001',
JSON_PARSE('{
"event": "login",
"user": {
"id": 1001
}
}')
);
After the conversion, nested attributes can be queried directly.
SELECT
event_data.event,
event_data.user.id
FROM application_events;
The important distinction is that the application does not need to flatten every nested property into separate columns before Redshift can understand the document.
Loading JSON From Amazon S3
For larger workloads, JSON files are commonly placed in Amazon S3 before being loaded into Redshift.
The general architecture looks like this:
Application
|
v
JSON documents
|
v
Amazon S3
|
v
Redshift COPY
|
v
SUPER column
This design separates ingestion from application execution.
The application can continue producing JSON while Redshift loads the files later as part of a scheduled or event-driven process.
A typical COPY-based workflow looks like this:
COPY application_events
FROM 's3://my-data-bucket/events/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
FORMAT AS JSON 'auto';
The exact COPY options should match the structure of the source files and the Redshift ingestion setup used by the environment.
The important design point is that the JSON can be loaded directly into a SUPER-oriented table rather than first converting every document into a flattened relational format.
JSON Auto Parsing
When the incoming JSON structure matches the target table design, automatic JSON mapping can reduce the amount of custom transformation code.
For example, a JSON document might contain:
{
"event_id": "evt-2001",
"event_type": "purchase",
"event_time": "2026-11-09T12:15:00Z",
"event_data": {
"customer": {
"id": 501
},
"amount": 2499
}
}
A table could expose the stable fields while keeping the variable payload inside SUPER.
CREATE TABLE purchase_events
(
event_id VARCHAR(100),
event_type VARCHAR(100),
event_time TIMESTAMP,
event_data SUPER
);
This approach avoids writing custom parsing code for every nested property.
What Does “Without Preprocessing” Mean?
It is important not to interpret the phrase literally.
You still need to perform normal ingestion tasks such as:
Uploading files to S3.
Configuring IAM permissions.
Choosing the correct Redshift table.
Validating the source format.
Handling malformed records.
Monitoring failed loads.
Managing duplicate files.
Designing the target schema.
What you avoid is unnecessary application-side restructuring of every JSON document.
Instead of:
JSON
↓
Custom Python parser
↓
Flatten
↓
Transform
↓
CSV
↓
Redshift
you can move toward:
JSON
↓
S3
↓
Redshift
↓
SUPER
That reduction in transformation steps can make the pipeline easier to maintain.
Handling Large JSON Documents
Large JSON documents require more planning than small event records.
The first question should be whether the entire document actually belongs in Redshift.
For example, a document may contain:
Customer metadata
Order information
Processing status
Application configuration
Large PDF content
Image data
Debug logs
Putting everything into one database document is usually not a good design.
Instead, keep analytical information in Redshift and large binary objects in Amazon S3.
For example:
{
"document_id": "DOC-1001",
"customer_id": 501,
"document_type": "invoice",
"status": "processed",
"storage": {
"bucket": "invoice-data",
"key": "2026/11/DOC-1001.pdf"
}
}
The SUPER document contains metadata and the S3 location rather than the PDF itself.
This keeps the warehouse focused on analytics.
Handling Nested Objects
Nested JSON does not need to be flattened before ingestion.
Consider:
{
"customer": {
"id": 501,
"address": {
"city": "Patna",
"country": "India"
}
}
}
After loading into SUPER, nested attributes can be queried directly.
SELECT
event_data.customer.id,
event_data.customer.address.city
FROM application_events;
This makes it possible to preserve the original document structure while still querying the values needed for analytics.
Handling Arrays
Arrays are another reason preprocessing often becomes complicated.
For example:
{
"order_id": "ORD-1001",
"items": [
{
"sku": "KB-100",
"quantity": 1
},
{
"sku": "MS-200",
"quantity": 2
}
]
}
With SUPER and PartiQL-style querying, the array can be traversed directly.
SELECT
o.event_data.order_id,
item.sku,
item.quantity
FROM application_events AS o,
o.event_data.items AS item;
This is useful when one JSON document contains multiple child records.
Instead of creating an ETL step just to split the array, the warehouse can retain the original document and expand it when the analytical query requires it.
Handling Schema Changes
One of the main benefits of this approach is handling evolving JSON structures.
Suppose version one contains:
{
"event": "purchase",
"customer_id": 501
}
Later, the application adds:
{
"event": "purchase",
"customer_id": 501,
"device": {
"type": "mobile",
"os": "Android"
}
}
A preprocessing pipeline may require a change to its mapping logic.
With SUPER, the additional structure can remain part of the document without requiring a table schema change for every new property.
However, this does not mean that schema governance is unnecessary.
Teams should still document important fields and monitor changes that affect dashboards, reports, machine learning pipelines, or downstream applications.
Validating Before Loading
Direct ingestion does not mean that validation should be skipped.
For business-critical data, validate the document before it reaches the warehouse.
For example, a purchase event may require:
event_id
event_type
timestamp
customer_id
order_id
Optional fields could include:
coupon
device
campaign
shipping
payment_metadata
A validation layer can reject documents that are malformed or missing required business information.
The key difference is that validation should focus on data quality rather than unnecessarily flattening the entire JSON document.
Handling Malformed Documents
Production ingestion pipelines should expect bad records.
Common examples include:
Do not let one malformed document cause an entire ingestion process to become difficult to diagnose.
Use monitoring and error handling around the COPY workflow, and keep failed input available for investigation when the data pipeline requires it.
A useful operational pattern is:
S3 source
|
v
Redshift COPY
|
+---- Successful records
|
+---- Failed records
|
v
Investigation / Replay
This is much easier to operate than hiding all ingestion failures inside a custom parser.
Avoiding Duplicate Loads
Removing preprocessing does not remove the need for idempotency.
If the same S3 file is loaded twice, the same JSON records may appear twice unless the pipeline prevents duplicate ingestion.
Applications should maintain a reliable identifier such as:
event_id
transaction_id
document_id
source_record_id
A separate ingestion tracking mechanism can also record which source files have already been processed.
For example:
source file
|
+-- file name
+-- object version
+-- ingestion timestamp
+-- load status
This becomes especially important when pipelines support retries.
A retry should recover from a failed operation without unintentionally duplicating successful data.
Query Only What You Need
Large SUPER documents can contain considerably more information than a query requires.
Avoid patterns like:
SELECT *
FROM application_events;
when the application only needs a few fields.
Instead:
SELECT
event_id,
event_data.customer.id,
event_data.order.amount
FROM application_events;
This makes the query intent clearer and avoids unnecessary processing.
For frequently accessed attributes, consider whether they should be extracted into relational columns.
When to Preprocess JSON Anyway
Direct loading is useful, but preprocessing still has valid use cases.
Consider preprocessing when:
The source JSON contains unusable data.
Business rules require significant transformation.
Multiple source formats need to be standardized.
Sensitive fields must be removed before loading.
The source structure is inconsistent.
Data needs extensive enrichment before analytics.
The target system requires a strongly normalized model.
For example, if five different systems produce customer records using five different field names, preprocessing may be the right place to standardize them.
The goal should not be to eliminate ETL.
The goal should be to remove ETL work that provides little value.
Common Mistakes
Flattening Everything Before Loading
Developers sometimes create a large mapping layer simply because the source is JSON.
If the source structure is useful and Redshift can work with it as SUPER, unnecessary flattening adds maintenance cost.
Treating SUPER as a Replacement for Data Modeling
SUPER is not an excuse to put every business field into one document.
Stable fields that are frequently queried should still be modeled appropriately.
Storing Binary Content Inside JSON
Large PDFs, images, videos, and other objects belong in object storage rather than inside an analytical JSON document.
Ignoring Data Quality
Flexible schemas still need validation.
A malformed or inconsistent document can create problems for downstream analytics.
Forgetting Duplicate Handling
A successful retry can become a duplicate load if the ingestion process has no idempotency strategy.
Building One Giant Document
A JSON document should represent a useful business or event boundary.
Do not combine unrelated application data simply because SUPER can hold nested structures.
Production Checklist
Before moving a direct JSON ingestion pipeline into production, verify:
JSON files are stored in a controlled S3 location.
The Redshift table has an appropriate SUPER column.
IAM permissions follow least privilege.
The JSON format matches the selected ingestion approach.
Required business fields are validated.
Malformed records can be identified and investigated.
Duplicate ingestion is controlled.
Large binary objects remain in object storage.
Frequently queried fields are modeled appropriately.
Nested arrays and objects have representative test data.
Query performance has been tested using realistic document sizes.
Load monitoring and alerting are configured.
Source files can be replayed when necessary.
Changes to important JSON properties are documented.
Advantages and Disadvantages
Advantages
Reduces unnecessary JSON transformation.
Preserves nested document structure.
Supports evolving JSON schemas.
Works with nested objects and arrays.
Simplifies ingestion architecture.
Reduces custom parsing code.
Allows analytical queries directly against semi-structured data.
Disadvantages
Direct ingestion does not eliminate data-quality requirements.
Complex source formats may still require transformation.
Poorly designed documents can become difficult to query.
Large documents can increase storage and query costs.
Frequently accessed attributes may still need relational modeling.
Duplicate handling requires additional pipeline design.
Summary
Loading JSON directly into Amazon Redshift can simplify data pipelines when the source already contains useful semi-structured information.
The SUPER data type allows Redshift to retain nested JSON objects and arrays without forcing developers to flatten every property before ingestion. A pipeline can place JSON files in Amazon S3, load them into Redshift, and query the required attributes later.
The practical approach is not to remove all preprocessing. Instead, remove preprocessing that only exists to make JSON fit into a rigid schema.
Keep stable business attributes in relational columns, use SUPER for flexible document data, validate important records, manage duplicates, and keep large binary objects in Amazon S3.
This approach gives development and data teams a simpler ingestion pipeline while preserving the flexibility of the original JSON data.
Amazon RedshiftJSONSUPER data typeAmazon S3COPY commandsemi-structured data
Join the conversation! Your thoughts help the community grow.