file_metadata is a STRUCT column on every nested IDS Lakehouse table. It records where each row came from: which Intermediate Data Schema (IDS) JSON file produced it, which IDS type and version that file used, which organization owns it, and which pipeline run staged it into the Lakehouse. There is one logical entry per source IDS JSON file version.

Use file_metadata to filter a query to a specific IDS type, restrict results to one organization, trace a row back to its source file, or isolate everything a single pipeline run staged.

📘

NOTE

This reference covers file_metadata on nested IDS Lakehouse tables. Datacubes Lakehouse tables handle this information differently. As of TDP v4.4.0, their file_metadata column is NULL for all newly inserted rows, and four top-level columns replace it: file_metadata_file_id, file_metadata_ids_path, file_metadata_created_at, and file_metadata_created_at_timestamp. If you query datacubes Lakehouse tables, use those columns instead. For more information, see Datacubes Lakehouse Tables Best Practices.

Access file_metadata Subfields

To access a subfield, use dot notation. The syntax is identical in Amazon Athena (Trino) and Databricks SQL.

SELECT
  file_metadata.file_id,
  file_metadata.ids_slug,
  file_metadata.ids_version,
  file_metadata.created_at
FROM my_nested_table
LIMIT 10;

Field Reference

Field Behavior to Know Before You Query

Three fields behave in ways that are easy to misread. Review these before you build queries on them.

event_type Never Contains update

event_type describes the staging event, not the outcome of the Lakehouse merge. When you reprocess a file through a raw-to-ids pipeline, the new IDS version stages as a new insert row. The Lakehouse job merges that row over the previous data, but the surviving row still reads insert.

🚧

IMPORTANT

Don't write queries that expect an update value. To keep only live rows, filter on file_metadata.event_type = 'insert'.

created_at Isn't a Change Watermark

created_at and created_at_timestamp record when the IDS JSON file was created in the data lake. The value advances when a new IDS version is created, but pipeline reruns and backfills change it in ways that break incremental reads.

⚠️

WARNING

Don't use created_at or created_at_timestamp as a "changed since last check" watermark. Use task_script_execution_timestamp instead, which reflects processing time. For an example, see Use a Processing-Time Watermark.

datacubes_parquet_file_path Is Populated Even Without Datacubes

ids_parquet_file_path and datacubes_parquet_file_path differ in exactly one path segment: type=ids against type=datacubes. The TDP populates datacubes_parquet_file_path for every row, including rows from IDSs that produce no datacubes. In that case, no file exists at the path.

Query Examples

The following examples cover the most common ways to use file_metadata in a query.

1. Filter to an IDS Type and Version

The syntax is identical in Athena and Databricks SQL.

WHERE file_metadata.ids_slug = 'lcuv-empower'
  AND file_metadata.ids_version = 'v17.0.0'

2. Filter to an Organization

WHERE file_metadata.subdomain = 'pharmacorp'
  AND file_metadata.org_slug = 'pharmacorp-uat'

3. Exclude Deletes

WHERE file_metadata.event_type = 'insert'

4. Filter by IDS File Creation Time

Use created_at for readable string ranges, or created_at_timestamp for numeric math.

-- Amazon Athena
WHERE from_unixtime(file_metadata.created_at_timestamp)
      >= timestamp '2025-04-01 00:00:00'

-- Databricks SQL
WHERE timestamp_seconds(file_metadata.created_at_timestamp)
      >= timestamp '2025-04-01 00:00:00'

This filters on when the IDS file was created, not when the row landed in the Lakehouse. For more information, see created_at Isn't a Change Watermark.

5. Use a Processing-Time Watermark

To select rows that a pipeline processed since your last read, filter on task_script_execution_timestamp.

-- Amazon Athena
WHERE from_unixtime(file_metadata.task_script_execution_timestamp)
      >= from_unixtime(:last_run_epoch)

-- Databricks SQL
WHERE timestamp_seconds(file_metadata.task_script_execution_timestamp)
      >= timestamp_seconds(:last_run_epoch)

6. Trace a Row Back to Its Source File

SELECT
  file_metadata.file_id,     -- unique per IDS file version
  file_metadata.file_path,   -- the IDS JSON fileKey
  file_metadata.ids_path     -- stable across file versions
FROM my_nested_table;

7. Deduplicate Across File Versions

file_id changes with every IDS version, but ids_path stays the same for the same logical file. If a query returns more than one row per logical file, keep the row with the most recent processing time. The syntax is identical in Athena and Databricks SQL.

SELECT * FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY file_metadata.ids_path
           ORDER BY file_metadata.task_script_execution_timestamp DESC
         ) AS rn
  FROM my_nested_table
)
WHERE rn = 1;

8. Isolate a Pipeline, Workflow, or Task Script Run

WHERE file_metadata.pipeline_id = 'ea802af3-c605-46e6-bd95-0af5d1ec247f'
   OR file_metadata.workflow_id = '11b50a55-9fa0-4291-8983-29a00c2cac51'
   OR file_metadata.transaction_id = 'a0c87fd7-652d-47b0-a8ef-12b07ec561d0'

Because the TDP generates transaction_id for each ids-to-lakehouse task script execution, it's the most precise way to isolate everything a single run staged.

Related Resources

• Data Lakehouse Architecture
• Create Lakehouse Tables
• Query Lakehouse Tables
• Transform Data by Using Tetraflow Pipelines
• TDP Athena SQL Table Structure

Documentation Feedback

Do you have questions about our documentation or suggestions for how we can improve it? Start a discussion in TetraConnect Hub. For access, see Access the TetraConnect Hub.

📘

NOTE

Feedback isn't part of the official TetraScience product documentation. TetraScience doesn't warrant or make any guarantees about the feedback provided, including its accuracy, relevance, or reliability. All feedback is subject to the terms set forth in the TetraConnect Hub Community Guidelines.