Profiler Workflow

Configure and deploy a Profiler Agent to extract table and column metrics and run data quality tests.

Tip: For Datalake profiling, NaN (Not a Number) values are dropped from the DataFrame using dropna() to allow metric computation. Null values are retained so that null-count metrics remain accurate.

Configure a Profiler Agent

Complete metadata ingestion before configuring and deploying a Profiler Workflow. The workflow populates the Table Profile tab with table and column metrics and runs any data quality tests configured on the table. To deploy a Profiler Agent from the Collate UI, follow these steps:

1. Add a Profiler Agent

In the left navigation bar, click Connections and select the desired connection.
On the connection details page, click the Agents tab.
In the Add Agent dropdown field, click Profiler.

2. Configure the Profiler Agent

In the Configure Ingestion step, enter a unique descriptive Name and set up the following:

Agent Setup

Spark Engine: The engine used to run profiling jobs. Select an engine that executes profiling queries from the following:

Native Engine: Collate’s built-in query runner. Use for most workloads where the database handles query execution without performance issues.
Spark Engine: Offloads computation to an Apache Spark cluster. Use for large-scale tables where native queries are too slow or resource-intensive.

For setup instructions and guidance on when to use Spark Engine, see Spark Engine Overview.

Filter Patterns

Filter patterns control which databases, schemas, and tables the profiler targets. Each pattern field accepts a regex expression with separate include and exclude lists.

Database filter pattern (Optional): Regex to include or exclude databases by name.
Schema filter pattern (Optional): Regex to include or exclude schemas by name.
Table filter pattern (Optional): Regex to include or exclude tables by name.
Classification filter pattern (Optional): Regex to target tables by classification, tag, tier, or glossary term. The profiler computes metrics only for tables whose classification matches the pattern.
Use FQN For Filtering: When enabled, regex patterns apply to the fully qualified name (for example, service_name.db_name.schema_name.table_name) instead of the raw asset name. Enable this when different schemas share the same table name across multiple databases.

Scope & Behaviour

Control which entity types the profiler includes and how it logs activity.

Enable Debug Log: Enables debug-level logging for the profiler run. Check logs in the Agents tab of the service.

Advanced Configuration

Control sampling strategy, thread usage, metric selection, and system statistics behavior.

Sample Config Type: Controls how the profiler selects rows to scan. Select Dynamic (default) or Static. When Dynamic is selected:
- Smart Sampling: When enabled (default), automatically scales the sample percentage based on table size. The profiler samples 100% of tables with up to 100,000 rows, 50% up to 1 million, 10% up to 10 million, 5% up to 100 million, 1% up to 1 billion, and 0.1% above 1 billion rows.
Dynamic sampling reads the table’s row count once per run to choose the sample size. Most databases return it from system catalogs or table statistics instead of scanning the table, so the value can be an estimate. For example, Postgres uses pg_class.reltuples. When no statistic is available, the profiler runs a COUNT(*). It always runs a COUNT(*) for tables with a partition filter. When Static is selected:
- Profile Sample: A fixed sample size applied to every profiler run, either as a percentage of rows or an absolute row count. The value must be at least 1.
- Profile Sample Type: Select Percentage to sample by a fraction of the table, or Row Count to sample by an absolute number of rows.
- Sampling Method Type: The SQL sampling strategy used when scanning a percentage sample. It applies to Snowflake and Timescale only. Other databases, including Postgres, ignore it. Select BERNOULLI to sample each row independently at the given probability, which is more statistically uniform but slower. Select SYSTEM to sample entire disk pages at the given probability, which is faster but less statistically uniform.
With Static sampling, how the sample is read depends on the database:
- BigQuery, Snowflake, Timescale, SQL Server, Azure SQL, and Microsoft Fabric use TABLESAMPLE. Synapse uses it when the warehouse supports it. BigQuery uses it only for percentage samples on tables, and a row count sample still uses a LIMIT. SQL Server and Azure SQL don’t use it for views.
- Postgres and other databases scan the table for a percentage sample. They assign each row a random number and keep the rows within the percentage. A row count sample uses a LIMIT.
- Static sampling doesn’t look up the row count to choose the sample size. The exception is a row count sample with Randomized Sample enabled, which runs a COUNT(*) first.
Important: Profile Sample is not supported for Druid. Druid does not support RANDOM sampling functions. Use the partitioning or sample query option to limit data scanned.
Thread Count (Optional): The number of threads used for parallel metric computation. Set to 1 for Snowflake due to a known issue with snowflake-connector-python. Defaults to the system value.
Timeout in Seconds (Optional): How long a profiling job waits before skipping to the next table. The profiler waits for the current query to finish before timing out. Set a query or connection timeout on your database engine if jobs risk hanging. Defaults to 12 hours (43,200 seconds).
Metrics: The set of metrics the profiler computes. All metrics are selected by default. Deselect specific metrics to reduce profiling time or cost.
Include Views: When enabled, the profiler computes metrics for view entity types. This may reduce profiler performance.
Compute Table Metrics: When enabled (default), the profiler computes table-level metrics such as row count and column count.
Compute Column Metrics: When enabled (default), the profiler computes column-level metrics such as null count, distinct count, and mean.
Use System Table Statistics: When enabled, the profiler reads table metrics from system catalog tables instead of scanning data. Faster than full scans, but requires up-to-date statistics on the database — for example, run ANALYZE on PostgreSQL before profiling. Metrics unavailable in system tables fall back to the default scan method.
Randomized Sample: When enabled, the profiler randomizes the row order in the sample. Disable for deterministic sampling.

3. Schedule and Deploy

Click Next to open the scheduling step.

Select how the profiler runs:
- Schedule: Set a recurring schedule to run the profiler automatically at a fixed interval.
- On Demand: Run the profiler manually whenever needed.
Click Deploy.

The Profiler Agent is created and listed under the Agents tab of the service.

Adjust Profiler Settings

After deploying the Profiler Agent, fine-tune profiler behavior at the table or platform level.

Table-Level Settings

To configure the table-level settings, follow these steps:

In the table’s details page, navigate to the Data Observability tab and click the Settings icon.
In the Settings panel, configure the following details:
- Profile Sample Type: The method used to determine how much data the profiler scans for this table.
  - Percentage: Use the slider to set a value between 1% and 100%.
  - Row Count: Enter a fixed number of rows to scan, for example 1,000 or 2,000. The value must be at least 1.
  The Settings panel doesn’t accept a profile sample of 0.
  Important: Profile Sample is not supported for Druid. Use the partitioning or custom query option to limit data scanned.
- Sample Data Count: The number of rows to collect as sample data for this table. Defaults to 50.
- Enable Column Profile: Control which columns the profiler includes for this table.
  - Exclude: Select columns to skip during profiling.
  - Include: Select specific columns to profile. When columns are selected here, the profiler runs only on those columns. All columns are included by default.
- Enable Partition: When enabled, the profiler fetches data based on a partition configuration. If Profile Sample Type is also set, it applies to the partitioned data, not the full table.
  - Column Name: The column used as the partition field.
  - Interval Type:
    - TIME-UNIT: A business timestamp or date column, for example order date or sign-up datetime.
    - INGESTION-TIME: A process timestamp indicating when data was ingested into the table.
    - COLUMN-VALUE: A categorical value representing a chunk of data, for example Product Type A, B, or C.
    - INTEGER-RANGE: A range of integers used as the partition, for example Customer ID between 1 and 10.
  After selecting an Interval Type, configure the following: For TIME-UNIT or INGESTION-TIME:
  - Interval: The interval value, for example 1 or 2.
  - Interval Unit: HOUR, DAY, MONTH, or YEAR.
  Note: For BigQuery tables partitioned on a timestamp or datetime column, month and year intervals are not supported. Set Interval Unit to DAY or HOUR. For COLUMN-VALUE:
  - Value: A list of values for the partitioning logic.
  For INTEGER-RANGE:
  - Start Range: The start of the range (inclusive).
  - End Range: The end of the range (inclusive).

Platform-Level Settings

To configure platform-level settings, follow these steps:

In the left navigation bar, click the Profile icon and navigate to Settings > Preferences > Profiler Configuration.
On the Profiler Configuration page, configure the following sections: Metric Configuration Each row controls which metrics the profiler computes for a specific data type. Click + Add New Field to add a configuration for an additional data type.
- Data Type: The column data type to configure, for example DATE, BIGINT, or VARCHAR.
- Metric Type: The specific metrics to compute for the data type. When no metrics are selected, the profiler computes all available metrics. When metrics are selected, only those metrics are computed.
- Disable: Toggle on to skip all metric computation for that data type. The profiler ignores any selected metrics when this is enabled.
Sample Data Ingestion Configuration Controls whether Collate stores or reads sample data during profiling. These settings override source-level configuration.
- Enable Storing of Sample Data: When enabled (default), Collate stores sample data collected during profiling runs.
- Enable Reading of Sample Data: When enabled (default), Collate reads sample data during profiling. The data is temporarily loaded into Collate’s infrastructure for processing but not stored. When using the Hybrid Runner, this processing happens within your own environment and sample data does not leave your infrastructure. If reading is disabled but storing is enabled, reading is automatically enabled.

YAML Configuration

The profiler workflow can also be triggered using a YAML configuration file via the Airflow SDK or the CLI. The serviceConnection config is connector-specific. See the connectors section for details. The sourceConfig for the profiler is similar across all connectors. To run the profiler from a YAML file, follow these steps:

Create a YAML configuration file with the following structure: The sourceConfig block supports these parameters:

computeTableMetrics: Turn off to skip table-level metric computation.
computeColumnMetrics: Turn off to skip column-level metric computation. Turn both this and computeTableMetrics off when you only want to ingest sample data.
profileSampleConfig: How much data the profiler and tests run on. sampleConfigType is either DYNAMIC (default, resolves the sample size at runtime from the table’s row count) or STATIC (a fixed size you set yourself); config holds the settings for the type you chose.
threadCount: Number of threads used for metric computation.
timeoutSeconds: How long a profiling job waits before skipping to the next table.
databaseFilterPattern: Regex to include or exclude databases by name.
schemaFilterPattern: Regex to include or exclude schemas by name.
tableFilterPattern: Regex to include or exclude tables by name.

The processor block uses type: orm-profiler. Use tableConfig to set profile sample, column filters, and partition config at the table level. The sink block must use type: metadata-rest to send results to Collate. The workflowConfig block requires an openMetadataServerConfig with the host and auth provider for your Collate installation.

source:
  type: <connectorType (e.g. bigquery, redshift, etc.)>
  serviceName: <serviceName (same as metadata ingestion service name)>
  sourceConfig:
    config:
      type: Profiler
      computeTableMetrics: true
      computeColumnMetrics: true
# profileSampleConfig:
#   sampleConfigType: DYNAMIC
#   config:
#     smartSampling: true
# threadCount: 5
# timeoutSeconds: 43200
# databaseFilterPattern:
      #   includes:
      #     - database1
      #     - database2
      #   excludes:
      #     - database3
      #     - database4
# schemaFilterPattern:
      #   includes:
      #     - schema1
      #     - schema2
      #   excludes:
      #     - schema3
      #     - schema4
# tableFilterPattern:
      #   includes:
      #     - table1
      #     - table2
      #   excludes:
      #     - table3
      #     - table4
processor:
  type: orm-profiler
  config: {}  # Remove braces if adding properties
    # tableConfig:
    #   - fullyQualifiedName: <table fqn>
    #     profileSampleConfig:
    #       sampleConfigType: STATIC  # omit entirely to inherit dynamic sampling
    #       config:
    #         profileSample: <number between 0 and 99>
    #     columnConfig:
    #       excludeColumns:
    #         - <column name>
    #       includeColumns:
    #         - columnName: <column name>
    #         - metrics:
    #           - MEAN
    #           - MEDIAN
    #           - ...
    #     partitionConfig:
    #       enablePartitioning: <set to true to use partitioning>
    #       partitionColumnName: <partition column name>
    #       partitionIntervalType: <TIME-UNIT, INTEGER-RANGE, INGESTION-TIME, COLUMN-VALUE>
    #       ----'TIME-UNIT' or 'INGESTION-TIME'-------
    #       partitionInterval: <partition interval>
    #       partitionIntervalUnit: <YEAR, MONTH, DAY, HOUR>
    #       ------------'INTEGER-RANGE'---------------
    #       partitionIntegerRangeStart: <integer>
    #       partitionIntegerRangeEnd: <integer>
    #       -----------'COLUMN-VALUE'----------------
    #       partitionValues:
    #         - <value>
    #         - <value>
sink:
  type: metadata-rest
  config: {}
workflowConfig:
  # loggerLevel: DEBUG  # DEBUG, INFO, WARN or ERROR
  openMetadataServerConfig:
    hostPort: <Collate host and port>
    authProvider: <Collate auth provider>

Run the profiler using the CLI:
metadata profile -c <path-to-yaml>
Use profile instead of ingest to trigger the Profiler workflow.

Profiler Best Practices

The profiler runs queries against your database, which can incur costs on engines like Google BigQuery and Snowflake. Execution time varies based on engine performance, table size, and column count. Keep these practices in mind when configuring profiler workflows.

Profile only what you need: Profiling every table in your data platform is rarely necessary. Focus on tables that are actively used by analysts, data scientists, or downstream pipelines. Use database, schema, and table filter patterns to narrow down which tables the profiler targets.
Use sampling and partitioning: Set a sample percentage or row count to limit how much data the profiler scans per table. For partitioned tables, configure a partition filter so the profiler only scans the relevant partition. Both approaches reduce query cost and execution time.
Exclude unnecessary columns and metrics: By default, the profiler computes all metrics for all columns. Use the column profile settings to exclude columns such as id fields, or limit the metric set to only what’s needed. This reduces computation time and cost.
Distribute load across multiple workflows: For large numbers of tables, set up multiple Profiler Agents with different filter patterns to distribute the load. Monitor instance CPU and memory when running several workflows simultaneously to avoid resource contention.

Data Quality & Observability

Profiler Workflow | Profiling Workflow

Profiler Workflow

Configure a Profiler Agent

1. Add a Profiler Agent

2. Configure the Profiler Agent

Agent Setup

Filter Patterns

Scope & Behaviour

Advanced Configuration

3. Schedule and Deploy

Adjust Profiler Settings

Table-Level Settings

Platform-Level Settings

YAML Configuration

Profiler Best Practices

​Profiler Workflow

​Configure a Profiler Agent

​1. Add a Profiler Agent

​2. Configure the Profiler Agent

​Agent Setup

​Filter Patterns

​Scope & Behaviour

​Advanced Configuration

​3. Schedule and Deploy

​Adjust Profiler Settings

​Table-Level Settings

​Platform-Level Settings

​YAML Configuration

​Profiler Best Practices

Profiler Workflow

Configure a Profiler Agent

1. Add a Profiler Agent

2. Configure the Profiler Agent

Agent Setup

Filter Patterns

Scope & Behaviour

Advanced Configuration

3. Schedule and Deploy

Adjust Profiler Settings

Table-Level Settings

Platform-Level Settings

YAML Configuration

Profiler Best Practices