Data Profiling Taking Too Long or Failing

Data profiling for large tables takes an extremely long time, causes ingestion timeouts, or fails with out-of-memory errors. This commonly occurs when profiling is enabled on large tables without row sampling or when profiling all columns including high-cardinality fields.

Common Error Messages:

• Profiling timeout exceeded
• OutOfMemoryError during profiling
• Query exceeded maximum execution time
• Connection lost during profiling

You Might Be Asking:

• How do I speed up data profiling?
• Can I profile only specific tables or columns?
• What's the difference between table-level and column-level profiling?

1. Configure profiling with sampling:

Example for Snowflake. This can be adapted for other data sources/connectors.

source:
  type: snowflake
  config:
    profiling:
      enabled: true

      # Use sampling for large tables
      profile_table_size_limit: 1000000  # Only profile tables < 1M rows
      profile_table_row_limit: 50000     # Sample max 50k rows

      # Table-level profiling only (faster)
      profile_table_level_only: true

      # Limit columns to profile
      max_number_of_fields_to_profile: 20

2. Additional Options For Improving Performance:

Use turn_off_expensive_profiling_metrics: true in your profile configuration when you want to speed up profiling and reduce resource usage, especially for large tables. This setting disables profiling for quantiles, distinct value frequencies, histograms, and sample values, and limits the number of fields profiled to 10, making profiling much faster and less costly. Enable this option if profiling jobs are taking too long or consuming too many resources, or if you do not need detailed field-level statistics.

3. Profile specific tables or schemas only:

source:
  config:
    profiling:
      enabled: true
      # Only profile analytics schema
      allow_deny_patterns:
        allow: ["analytics\..*"]
        deny: [".*_staging$", ".*_temp$"]

4. Use column-level profiling selectively:

source:
  config:
    profiling:
      enabled: true
      profile_table_level_only: false

      # Profile specific column types
      include_field_null_count: true
      include_field_min_value: true
      include_field_max_value: true
      include_field_mean_value: true

      # Skip expensive operations
      include_field_distinct_count: false  # Expensive on large tables
      include_field_histogram: false
      include_field_sample_values: true
      max_number_of_fields_to_profile: 10

5. Run profiling separately from metadata ingestion:

# metadata-only-recipe.yml
source:
  type: snowflake
  config:
    profiling:
      enabled: false

---
# profiling-only-recipe.yml  
source:
  type: snowflake
  config:
    profiling:
      enabled: true
      profile_table_level_only: true
      # Profile incrementally
      schema_pattern:
        allow: ["analytics"]

6. Adjust timeout settings:

source:
  config:
    profiling:
      enabled: true
      query_timeout: 300  # 5 minutes per query

    # Connection timeout
    options:
      connect_timeout: 60
      read_timeout: 300

For production deployments, consider running profiling on a separate schedule (e.g., weekly) from metadata ingestion (e.g., daily). Table-level profiling provides row counts and size without scanning columns. If you need additional help tuning your available resources, please feel free to log a ticket with DataHub Support.

• Profiling Guide
• Performance Best Practices