Handling CLOB’s in Cloud
# Handling Legacy Oracle CLOB Data in Cloud Databases for Reporting & Analytics
## The Core Problem
Oracle CLOBs store large text payloads (XML, JSON, free-text, serialized objects) that cloud data warehouses and analytics engines don’t support natively. The strategy depends on **what’s in the CLOB**.
Step 1: Classify Your CLOB Content
Step 2: Extraction Strategies
### Option A — Flatten at ETL Layer (recommended)
Use your ETL tool (dbt, Spark, Glue, Dataflow) to decompose the CLOB during ingestion:
```sql
— dbt example: extract fields from a JSON CLOB
SELECT
id,
— Snowflake VARIANT
PARSE_JSON(clob_column):customer_id::STRING AS customer_id,
PARSE_JSON(clob_column):order_total::FLOAT AS order_total,
PARSE_JSON(clob_column):status::STRING AS status
FROM raw.legacy_orders
```
### Option B — Land Raw, Parse in Views
Store the raw text first, then create views that parse on read. Good when the schema is evolving.
```sql
— BigQuery: land as STRING, parse in a view
CREATE OR REPLACE VIEW analytics.orders_parsed AS
SELECT
id,
JSON_VALUE(clob_text, ‘$.customer_id’) AS customer_id,
CAST(JSON_VALUE(clob_text, ‘$.order_total’) AS FLOAT64) AS order_total
FROM raw.legacy_orders_raw;
```
### Option C — Semi-structured Column (Snowflake/Redshift)
If the CLOB schema varies, store as native semi-structured and let analysts query with dot-notation:
```sql
— Snowflake: cast VARCHAR to VARIANT once
ALTER TABLE raw.orders ADD COLUMN clob_parsed VARIANT;
UPDATE raw.orders SET clob_parsed = PARSE_JSON(clob_text);
— Query in Snowflake
SELECT clob_parsed:order_total::FLOAT FROM raw.orders;
```
## Step 3: Oracle-Side Extraction (before migration)
Use `DBMS_LOB` to truncate or export CLOBs that exceed varchar limits:
```sql
— Export CLOB as VARCHAR chunks (Oracle → staging)
SELECT
id,
DBMS_LOB.SUBSTR(clob_column, 4000, 1) AS clob_part_1,
DBMS_LOB.SUBSTR(clob_column, 4000, 4001) AS clob_part_2
FROM legacy_table;
```
For GoldenGate, Qlik or AWS DMS — configure CLOB handling explicitly:
```yaml
# AWS DMS task settings
“LobSettings”: {
“BulkMaxSize”: 32, # KB
“LobMaxSize”: 32,
“EnableLobHighPrecision”: true
}
Get Adithya Karunanidhi’s stories in your inbox
Join Medium for free to get updates from this writer.
```
## Step 4: Platform-Specific Best Practices
### Snowflake
- Cast CLOB → `VARCHAR(16777216)` (max 16 MB)
- Use `PARSE_JSON()` / `PARSE_XML()` + `FLATTEN()` for nested data
- Store raw in `VARIANT` columns for schema-on-read flexibility
### Google BigQuery
- Map CLOB → `STRING` (unlimited size)
- Use `JSON_VALUE`, `JSON_QUERY`, `JSON_EXTRACT_ARRAY` for parsing
- Use `SEARCH` indexes for full-text analytics (GA since 2024)
### Azure Synapse / Fabric
- Map to `NVARCHAR(MAX)` in dedicated pools or `STRING` in serverless
- Use `OPENJSON()` or `JSON_VALUE()` for structured extraction
### AWS Redshift
- Map CLOB → `VARCHAR(65535)` (hard limit — truncation risk!)
- Pre-parse in Glue/Spark before loading; Redshift is not suitable for raw CLOB storage
- Use Redshift Spectrum + S3 Parquet for large text payloads
### PostgreSQL (Aurora, AlloyDB)
- Map to `TEXT` (unlimited) or `JSONB` for JSON CLOBs
- Use GIN indexes on `JSONB` for fast analytics queries
## Step 5: Reporting & Analytics Patterns
**For BI tools (Tableau, Power BI, Looker):**
- Never expose raw CLOB columns — always pre-parse into typed columns in a semantic layer
- Use dbt models as the transformation layer
**For ad-hoc SQL analytics:**
```sql
— Snowflake: lateral flatten a JSON array inside a CLOB
SELECT
o.id,
item.value:product_id::STRING AS product_id,
item.value:qty::INT AS quantity
FROM raw.orders o,
LATERAL FLATTEN(input => PARSE_JSON(o.clob_text):line_items) item;
```
**For full-text search on free-text CLOBs:**
- BigQuery: `SEARCH(table, ‘keyword’)` with search indexes
- Snowflake: `SEARCH()` function or offload to Elasticsearch/OpenSearch
- Postgres: `tsvector` / `tsquery` with GIN index
## Step 6: Data Quality & Size Guardrails
```python
# Spark/Glue ETL: flag oversized CLOBs before load
from pyspark.sql.functions import length, col, when
df = df.withColumn(
“clob_size_flag”,
when(length(col(“clob_text”)) > 65000, “TRUNCATION_RISK”).otherwise(“OK”)
)
# Drop or archive rows that exceed target platform limits
clean_df = df.filter(col(“clob_size_flag”) == “OK”)
```
## Summary
├── JSON/XML → parse to columns (dbt/Spark) OR store as VARIANT/JSONB
├── Free text → VARCHAR/TEXT + search index
├── >64KB → Redshift❌, use Snowflake/BigQuery/Postgres
└── Unknown → land as raw STRING, profile first, then decide
**Recommended general pattern:**
> Land raw as STRING → profile in a staging schema → build typed dbt models → expose clean columns to BI/reporting layer.
This decouples extraction from transformation and lets you handle CLOB schema variations incrementally.
