TerraLog: SQL and Spark Over 900 TB Without a Second Copy
TerraLog instruments 120,000 agricultural sensors. It lands raw telemetry as Parquet in OCI Object Storage: 900 TB today, growing 60 TB a month. Analysts run roughly 40 SQL queries a day, 85% of them against the last 30 days; data older than a year is queried fewer than five times a month but must be retained for ten years for regulator audits. Data scientists need Spark for feature engineering and write results back as Parquet. The current attempt runs a three-node Big Data Service cluster 24x7 to hold the same data on HDFS, plus a separate Autonomous Data Warehouse holding only a 2 TB curated mart. Storage cost has doubled year over year and the cluster is idle 80% of the time. The team wants one governed place to query everything with SQL, Spark only when it is needed, and no second copy of the archive.
1. Propose the target architecture for SQL over the lake plus Spark. Name the services and state what each one owns.
Read the worked answer
Retire the 24x7 Big Data Service cluster; it exists to hold a copy of data that already lives in Object Storage.
- Object Storage remains the system of record for raw and curated Parquet, with lifecycle rules per bucket.
- OCI Data Flow runs Spark only when a job runs: feature engineering reads Parquet and writes Parquet back. Serverless Spark means paying for the job rather than for idle nodes, which directly addresses the 80% idle finding.
- Autonomous Data Warehouse is the SQL surface. Query the lake through external tables created with DBMS_CLOUD and partitioned on the date prefix, and keep only the 2 TB curated mart and the last-30-days hot slice as internal tables, where indexes, the result cache and materialized views actually help.
- Data Catalog, plus a documented prefix convention enforced by the external table definitions, governs what the 900 TB means so 40 daily queries do not each invent their own path parsing.
The outcome is one governed SQL endpoint, Spark on demand, and no second copy of the archive.
2. Which Object Storage tiers and lifecycle rules would you apply, and what is the trap?
Read the worked answer
Tier by access pattern, not by age alone.
- Standard for the last 30 days: 85% of the queries, and the tier with no retrieval delay.
- Infrequent Access for 30 days to 12 months: cheaper storage, retrieval still fast enough for ad-hoc analysis, but confirm the minimum storage duration before committing.
- Archive for the year-plus data queried fewer than five times a month: cheapest, but retrieval is measured in hours, so an auditor request becomes a planned restore.
The trap: an ADW external table reads whatever tier the object sits in, and an archived object is not readable until restored. Either document and rehearse the restore step, or keep a thin Parquet projection of the archived data (date, sensor, reading) in Standard tier so routine audit queries never touch Archive. Also check each tier's minimum retention - moving a 45-day-old object into a tier with a 90-day minimum costs more, not less.
3. Why not simply load the 900 TB into ADW internal tables, and which file-layout habit matters most for external tables?
Read the worked answer
Cost and physics. Internal ADW storage is priced and maintained per terabyte, and 900 TB of cold Parquet gains nothing from indexes, the result cache or materialized views because almost nobody queries it. Loading it also means a copy plus a load window that grows by 60 TB every month. External tables read the bytes in place, push predicates down, and fetch only the columns in the select list - the right trade for the long tail.
The habit that matters most is file layout:
- Partition the external table on the date prefix (year=/month=/day=) so a 30-day query prunes to 30 partitions instead of scanning 900 TB.
- Compact small files. Streaming ingest leaves thousands of 2 MB objects per hour, and both Spark and ADW external scans pay a per-file cost; 900 TB in 2 MB files is millions of open operations.
- Target roughly 128 MB to 1 GB objects, clustered or sorted on the common filter columns, so partition pruning is followed by effective skipping rather than a full scan of each partition.