Metadata export

Organizations need to export metadata from Atlan to support AI applications, internal data marketplaces, and governance workflows across source systems. Use Lakehouse as a single place to query enriched metadata and feed it into downstream tools and applications.

Before you begin

Before you run these queries, make sure:

Your Lakehouse setup is complete and Lakehouse is enabled for your organization. If setup isn't complete, see Get started with Lakehouse.
The Gold layer is set up for your SQL engine. See Set up gold layer in Snowflake. For Databricks (Databricks setup guide) or BigQuery (BigQuery setup guide) setups, contact your Atlan Customer Success team.

You can run these queries directly on the ASSETS and README tables (and related metadata tables) in your Lakehouse to export metadata for AI assistants, data marketplaces, and reverse sync workflows.

Retrieve asset metadata

Use this query to retrieve assets from Atlan with enrichment metadata that you can export to external tools. It's particularly useful for:

Finding assets with good documentation. Update the WHERE clause to filter assets that match your use case. Several examples are shown in the section below.
Powering data discovery, quality, or governance dashboards. Update the <database> placeholder to match your environment.
Identifying certified and actively used assets.

USE <database>;

SELECT
    A.ASSET_NAME,                 -- Human-readable name of the asset
    A.GUID,                       -- Unique identifier for the asset
    A.ASSET_QUALIFIED_NAME,       -- Fully qualified name (e.g., db.schema.table)
    A.ASSET_TYPE,                -- Type of asset: Table, Column, View, etc.
    A.DESCRIPTION,               -- Asset-level documentation
    A.STATUS,                    -- Lifecycle status: ACTIVE, DELETED
    A.CERTIFICATE_STATUS,        -- Certification state: VERIFIED, DRAFT, DEPRECATED
    A.OWNER_USERS,               -- Array of asset owners
    A.TAGS,                      -- Array of tags applied to the asset
    R.DESCRIPTION AS README_TEXT,-- Rich documentation from README (if present)
    A.POPULARITY_SCORE,          -- Usage-based popularity metric
    A.HAS_LINEAGE                -- Boolean indicating if lineage exists
FROM
  ASSETS A
LEFT JOIN
  README R
  ON A.README_GUID = R.GUID     -- Join README content to the asset
WHERE
  A.ASSET_TYPE IN ('Table', 'Column', 'View')     -- Focus on core data assets
  AND A.CONNECTOR_NAME IN ('snowflake', 'redshift', 'bigquery') -- Cloud warehouses
  AND R.DESCRIPTION IS NOT NULL  -- Only return assets with README documentation
LIMIT 10;                        -- Sample output for inspection

Sample output

ASSET_NAME	ASSET_TYPE	DESCRIPTION	STATUS	CERTIFICATE_STATUS	OWNER_USERS	TAGS	HAS_LINEAGE
instacart_orders	Table	Transaction log with order timing and reorder patterns	ACTIVE	VERIFIED	andrew	Attribute, Finance, Cost_center, Confidential	TRUE
dwh_dmatrizcampana	Table	Central source of truth for date, holiday, and fiscal periods	ACTIVE	VERIFIED		Attribute, Customer Service, Confidential	FALSE

Retrieve asset enrichment with custom metadata

Snowflake
Databricks
BigQuery

USE ATLAN_GOLD;
-- ============================================
-- ASSET ENRICHMENT WITH CUSTOM METADATA SCORES
-- ============================================

WITH CM_DETAILS AS (
    SELECT 
        CM.ASSET_GUID,
        
        -- Create JSON object using LISTAGG and PARSE_JSON
        PARSE_JSON(
            '{' || 
            LISTAGG(
                '"' || CM.ATTRIBUTE_NAME || '":"' || 
                REPLACE(COALESCE(CM.ATTRIBUTE_VALUE, ''), '"', '\\"') || '"',
                ','
            ) WITHIN GROUP (ORDER BY CM.ATTRIBUTE_NAME)
            || '}'
        ) AS CM_ATTRIBUTES_JSON
        
    FROM CUSTOM_METADATA CM
    WHERE 
        CM.STATUS = 'ACTIVE'
        AND CM.CUSTOM_METADATA_NAME = 'AI Readiness'  -- 🔧 CUSTOMIZE
        AND CM.ATTRIBUTE_VALUE IS NOT NULL
    GROUP BY CM.ASSET_GUID
)

SELECT
    A.ASSET_NAME,
    A.GUID,
    A.ASSET_QUALIFIED_NAME,
    A.ASSET_TYPE,
    A.DESCRIPTION,
    R.DESCRIPTION AS README_TEXT,
    A.STATUS,
    A.CERTIFICATE_STATUS,
    A.OWNER_USERS,
    A.TAGS,
    A.POPULARITY_SCORE,
    A.HAS_LINEAGE,
    
    -- All Custom Metadata attributes as JSON
    CMS.CM_ATTRIBUTES_JSON,


FROM ASSETS A
LEFT JOIN README R ON A.README_GUID = R.GUID
LEFT JOIN CM_DETAILS CMS ON A.GUID = CMS.ASSET_GUID

WHERE 
    A.ASSET_TYPE IN ('Table', 'Column', 'View')
    AND A.CONNECTOR_NAME IN ('snowflake', 'redshift', 'bigquery')
    AND A.STATUS = 'ACTIVE'
    AND CMS.CM_ATTRIBUTES_JSON IS NOT NULL
    AND A.OWNER_USERS IS NOT NULL

LIMIT 5;

Replace the owner value, such as [email protected], to match your environment.

USE atlan_gold;    
-- ============================================
-- ASSET ENRICHMENT WITH CUSTOM METADATA SCORES
-- ============================================

WITH CM_JSON AS (
    SELECT 
        CM.ASSET_GUID,
        
        -- Store as parallel arrays (simpler, more reliable)
        collect_list(CM.ATTRIBUTE_NAME) AS SCORE_ATTRIBUTE_NAMES,
        collect_list(CM.ATTRIBUTE_VALUE) AS SCORE_ATTRIBUTE_VALUES,
        
        -- Also create JSON string for easy extraction
        concat(
            '{',
            concat_ws(
                ',',
                collect_list(
                    concat(
                        '"', CM.ATTRIBUTE_NAME, '":"', 
                        regexp_replace(coalesce(CM.ATTRIBUTE_VALUE, ''), '"', '\\\\"'), '"'
                    )
                )
            ),
            '}'
        ) AS CM_ATTRIBUTES_JSON
        
    FROM CUSTOM_METADATA CM
    WHERE 
        CM.STATUS = 'ACTIVE'
        AND CM.CUSTOM_METADATA_NAME = 'AI Readiness'  -- 🔧 CUSTOMIZE: Your CM set name
        AND CM.ATTRIBUTE_VALUE IS NOT NULL
    GROUP BY CM.ASSET_GUID
)

SELECT
    -- ========================================
    -- ASSET IDENTIFICATION
    -- ========================================
    A.ASSET_NAME,
    A.GUID,
    A.ASSET_QUALIFIED_NAME,
    A.ASSET_TYPE,
    
    -- ========================================
    -- DOCUMENTATION
    -- ========================================
    A.DESCRIPTION,
    R.DESCRIPTION AS README_TEXT,
    
    -- ========================================
    -- GOVERNANCE METADATA
    -- ========================================
    A.STATUS,
    A.CERTIFICATE_STATUS,
    A.OWNER_USERS,
    A.TAGS,
    
    -- ========================================
    -- USAGE METRICS
    -- ========================================
    A.POPULARITY_SCORE,
    A.HAS_LINEAGE,
    
    -- ========================================
    -- CUSTOM METADATA (Multiple Formats)
    -- ========================================
    -- Option 1: JSON string for extraction
    CMS.CM_ATTRIBUTES_JSON

FROM ASSETS A

LEFT JOIN README R
    ON A.README_GUID = R.GUID

LEFT JOIN CM_JSON CMS
    ON A.GUID = CMS.ASSET_GUID

WHERE 
    A.CONNECTOR_NAME IN ('snowflake', 'redshift', 'bigquery', 'oracle')
    AND A.STATUS = 'ACTIVE'
    AND CMS.CM_ATTRIBUTES_JSON IS NOT NULL
    AND A.OWNER_USERS IS NOT NULL
    AND size(A.OWNER_USERS) > 0

LIMIT 10;

SET @@dataset_id = 'ATLAN_GOLD';

-- ============================================
-- ASSET ENRICHMENT WITH CUSTOM METADATA SCORES
-- ============================================

WITH CM_JSON AS (
    SELECT 
        CM.ASSET_GUID,
        
        -- Construct JSON object as string
        PARSE_JSON(
            CONCAT(
                '{',
                STRING_AGG(
                    CONCAT(
                        '"', CM.ATTRIBUTE_NAME, '":"', 
                        IFNULL(CM.ATTRIBUTE_VALUE, ''), '"'
                    ),
                    ','
                    ORDER BY CM.ATTRIBUTE_NAME
                ),
                '}'
            )
        ) AS CM_ATTRIBUTES_JSON
        
    FROM CUSTOM_METADATA CM
    WHERE 
        CM.STATUS = 'ACTIVE'
        AND CM.CUSTOM_METADATA_NAME = 'AI Readiness'  -- 🔧 CUSTOMIZE
    GROUP BY CM.ASSET_GUID
)

SELECT
    A.ASSET_NAME,
    A.GUID,
    A.ASSET_QUALIFIED_NAME,
    A.ASSET_TYPE,
    A.DESCRIPTION,
    R.DESCRIPTION AS README_TEXT,
    A.STATUS,
    A.CERTIFICATE_STATUS,
    A.OWNER_USERS,
    A.TAGS,
    A.POPULARITY_SCORE,
    A.HAS_LINEAGE,
    
    CMS.CM_ATTRIBUTES_JSON,
    
    -- Extract specific attributes
    JSON_EXTRACT_SCALAR(CMS.CM_ATTRIBUTES_JSON, '$.data_quality_score') AS data_quality_score,
    JSON_EXTRACT_SCALAR(CMS.CM_ATTRIBUTES_JSON, '$.ai_readiness_score') AS ai_readiness_score

FROM ASSETS A
LEFT JOIN README R ON A.README_GUID = R.GUID
LEFT JOIN CM_JSON CMS ON A.GUID = CMS.ASSET_GUID

WHERE 
    A.CONNECTOR_NAME IN ('snowflake', 'redshift', 'bigquery', 'oracle')
    AND A.STATUS = 'ACTIVE'
    AND CMS.CM_ATTRIBUTES_JSON IS NOT NULL
    AND A.OWNER_USERS IS NOT NULL
    AND ARRAY_LENGTH(A.OWNER_USERS) > 0

LIMIT 10;

Sample output

ASSET_NAME	ASSET_TYPE	DESCRIPTION	STATUS	CERTIFICATE_STATUS	OWNER_USERS	HAS_LINEAGE	CM_ATTRIBUTES_JSON
ACCOUNTS	View		ACTIVE			FALSE	pii_risk_level_for_ai: medium
POSITIONS	Table	Provides details of job roles or positions	ACTIVE	VERIFIED	ravi	FALSE	ai_certification_status: In Review, pii_risk_level_for_ai: medium
average_sale_price_per_model_per_year_apac	Table		ACTIVE	VERIFIED	christopher.perro, esthephany.ayala	TRUE	pii_risk_level_for_ai: medium
INSTACART_ALL_ORDER_PRODUCTS_USER	Table	Comprehensive record of customer orders	ACTIVE	VERIFIED	bryan	TRUE	representativeness_checked: True
CLIENT_BRANCH_LOAN_CLEANSED	Table	Information on individual loans and branch revenue	ACTIVE	VERIFIED	sean.uptigrove	TRUE	pii_risk_level_for_ai: high, representativeness_checked: True

Before you begin​

Retrieve asset metadata​

Retrieve asset enrichment with custom metadata​

Before you begin

Retrieve asset metadata

Retrieve asset enrichment with custom metadata