### Test with Left Join to Original Data

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/compare_all_columns.md

This example demonstrates how to use `compare_all_columns` with `summarize=false` to get detailed comparison results, then joins it back to the original data using a left join. It's useful for investigating specific conflicts by including original columns alongside the comparison results.

```sql
with comparison as (
  {{ 
    audit_helper.compare_all_columns(
      a_relation=ref('stg_orders'),
      b_relation=api.Relation.create(database='prod', schema='analytics', identifier='orders'),
      primary_key='order_id',
      summarize=false
    ) 
  }}
)
select
  o.customer_id,
  o.order_date,
  comparison.*
from comparison
left join {{ ref('stg_orders') }} o using(order_id)
where conflicting_values
```

--------------------------------

### Development Configuration Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

Combine sample_limit and exclude_columns for quick feedback during development cycles.

```sql
sample_limit=10,
exclude_columns=['loaded_at', 'updated_at']
```

--------------------------------

### Compare Queries: Summary Statistics Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/compare_queries.md

This snippet demonstrates how to use the compare_queries macro to get summary statistics about the differences between two SQL queries. It returns counts and percentages of rows that are identical, unique to the first query, or unique to the second query.

```sql
{
 set old_query %}
  select
    id as order_id,
    amount,
    customer_id
  from old_database.old_schema.fct_orders
{% endset %}

{% set new_query %}
  select
    order_id,
    amount,
    customer_id
  from {{ ref('fct_orders') }}
{% endset %}

{{ 
  audit_helper.compare_queries(
    a_query=old_query,
    b_query=new_query,
    summarize=true
  )
}}
```

--------------------------------

### Basic Schema Comparison Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/compare_relation_columns.md

This example demonstrates a basic schema comparison between an old relation fetched using adapter.get_relation and a new relation defined by ref(). It utilizes the compare_relation_columns macro to identify differences.

```sql
{% set old_relation = adapter.get_relation(
  database='legacy_db',
  schema='legacy_schema',
  identifier='customer_orders'
) %}

{% set new_relation = ref('customer_orders') %}

{{ 
  audit_helper.compare_relation_columns(
    a_relation=old_relation,
    b_relation=new_relation
  )
}}
```

--------------------------------

### Production Configuration Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

Use sample_limit=None and an empty exclude_columns list for comprehensive reports in production environments.

```sql
sample_limit=None,
exclude_columns=[]
```

--------------------------------

### Using dbt-audit-helper Macros

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/EXPORTED_FUNCTIONS.md

Example of how to call a macro from the dbt-audit-helper package within your dbt project's macros or tests after installation.

```sql
{{ audit_helper.macro_name(...) }}
```

--------------------------------

### Minimal Macro Configuration

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

Configure macros with essential parameters like datasets and comparison keys. This is the most basic setup for most macros.

```sql
{{ 
  audit_helper.compare_and_classify_query_results(
    a_query=old_query,
    b_query=new_query,
    primary_key_columns=['id'],
    columns=['id', 'name', 'amount']
  )
}}
```

--------------------------------

### Install dbt-audit-helper Package

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

Add the dbt-audit-helper package to your packages.yml file to manage its version. Then run 'dbt deps' to install it.

```yaml
packages:
  - package: dbt-labs/audit_helper
    version: 0.9.1  # use latest version from hub.getdbt.com
```

```bash
dbt deps
```

--------------------------------

### Explicit Column Selection Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/compare_and_classify_relation_rows.md

Use this snippet when you want to specify exactly which columns to compare. Provide a list of column names to the `columns` parameter.

```sql
{% set old_relation = adapter.get_relation(
  database="legacy_db",
  schema="legacy_schema",
  identifier="products"
) %}

{% set new_relation = ref('products') %}

{{ 
  audit_helper.compare_and_classify_relation_rows(
    a_relation=old_relation,
    b_relation=new_relation,
    primary_key_columns=['product_id'],
    columns=['product_id', 'name', 'price', 'category']
  )
}}
```

--------------------------------

### dbt Test Integration Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/DOCUMENTATION_SUMMARY.txt

Shows how to integrate dbt-audit-helper macros within dbt tests for automated data validation.

```sql
--- tests/assert_models_match.sql
{% test assert_models_match(model, compare_model, primary_key) %}

  {{ audit_helper.compare_and_classify_relation_rows(
      a_relation=model,
      b_relation=compare_model,
      primary_key=primary_key
  ) }}

{% endtest %}
```

--------------------------------

### Column List Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/types.md

Provide a Jinja2 list of column names as strings for column list parameters. Column names are case-sensitive and must match exactly.

```plaintext
columns=['order_id', 'customer_id', 'order_date', 'amount']
```

```jinja
{% set columns = dbt_utils.get_filtered_columns_in_relation(my_relation, except=['loaded_at']) %}
```

--------------------------------

### Access Macros with Namespace

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

After installation, access dbt-audit-helper macros using the 'audit_helper' namespace in your SQL queries.

```sql
{{ audit_helper.compare_and_classify_query_results(...) }}
{{ audit_helper.compare_all_columns(...) }}
```

--------------------------------

### dbt-audit-helper Package Installation

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/EXPORTED_FUNCTIONS.md

Instructions for adding the dbt-audit-helper package to your dbt project's packages.yml file. Ensure you use a recent version or later.

```yaml
packages:
  - package: dbt-labs/audit_helper
    version: 0.9.1  # or later
```

--------------------------------

### Basic Query Comparison

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/quick_are_queries_identical.md

Compares two SQL queries to check if they produce identical results based on specified columns. Ensure the 'audit_helper' package is installed and accessible.

```sql
{
  set old_query %}
  select product_id, name, price, category
  from legacy_db.legacy_schema.dim_products
{% endset %}

{% set new_query %}
  select product_id, name, price, category
  from {{ ref('dim_products') }}
{% endset %}

{{ 
  audit_helper.quick_are_queries_identical(
    query_a=old_query,
    query_b=new_query,
    columns=['product_id', 'name', 'price', 'category']
  ) 
}}
```

--------------------------------

### Macro Dispatcher Pattern Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/EXPORTED_FUNCTIONS.md

This snippet demonstrates how dbt's adapter dispatch system is used to call adapter-specific macro implementations. It allows for maintaining a single public API while supporting different database adapters.

```sql
{{ return(adapter.dispatch('compare_and_classify_query_results', 'audit_helper')(...)) }}
```

--------------------------------

### Identifying Union Compatibility Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/compare_relation_columns.md

This example uses compare_relation_columns to identify columns that would prevent a successful UNION operation between two table versions. It filters the results to show only columns where there is a data type mismatch or the column is not present in both relations.

```sql
{% set table_1 = source('raw', 'events_v1') %}
{% set table_2 = source('raw', 'events_v2') %}

{{ audit_helper.compare_relation_columns(
  a_relation=table_1,
  b_relation=table_2
) }}

where not (in_both and has_data_type_match)
```

--------------------------------

### SQL SELECT Query String Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/types.md

Use a SQL SELECT statement as a string for query parameters. This can reference physical tables, views, CTEs, or dbt refs wrapped in a string.

```sql
{% set my_query %}
  select order_id, amount, customer_id
  from {{ ref('orders') }}
{% endset %}
```

--------------------------------

### Compare Queries: Detailed Non-Matching Rows Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/compare_queries.md

This snippet shows how to use the compare_queries macro to retrieve detailed non-matching rows between two SQL queries. It requires a primary key for ordering and allows setting a limit on the number of detailed rows returned.

```sql
{{ 
  audit_helper.compare_queries(
    a_query=old_query,
    b_query=new_query,
    primary_key='order_id',
    summarize=false,
    limit=100
  )
}}
```

--------------------------------

### Primary Key Examples

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/types.md

Specify a single primary key as a SQL expression string or a composite primary key as a list of column name strings. Primary keys must uniquely identify a row and be not null.

```sql
-- Single key
primary_key='order_id'

-- Composite key
primary_key_columns=['customer_id', 'year', 'month']

-- SQL expression
primary_key='concat(customer_id, "-", order_id)'
```

--------------------------------

### Compare All Columns - Summary View

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/compare_all_columns.md

Use this snippet to get a summary of column comparisons, showing counts of perfect matches, nulls, missing keys, and conflicting values. This is the default behavior when summarize=true.

```sql
{
  "page_title": "dbt Audit Helper: Compare All Columns Macro",
  "page_description": "Documentation for the compare_all_columns macro in dbt Audit Helper, which compares all columns between two relations.",
  "page_summary": "The compare_all_columns macro provides a detailed comparison of all columns between two dbt relations, offering both summarized statistics and row-level detail. It is useful for data quality checks and identifying discrepancies between datasets.",
  "codeSnippets": [
    {
      "title": "Compare All Columns - Summary View",
      "description": "Use this snippet to get a summary of column comparisons, showing counts of perfect matches, nulls, missing keys, and conflicting values. This is the default behavior when summarize=true.",
      "language": "sql",
      "codeList": [
        {
          "language": "sql",
          "code": "{% set old_relation = adapter.get_relation(
  database='legacy_db',
  schema='legacy_schema',
  identifier='fct_orders'
) %}

{% set new_relation = ref('fct_orders') %}

{{ 
  audit_helper.compare_all_columns(
    a_relation=old_relation,
    b_relation=new_relation,
    primary_key='order_id'
  )
}}"
        }
      ]
    },
    {
      "title": "Compare All Columns - Detailed View",
      "description": "Use this snippet to get row-level detail for each column comparison when summarize=false. This shows specific match classifications for each primary key.",
      "language": "sql",
      "codeList": [
        {
          "language": "sql",
          "code": "{{ 
  audit_helper.compare_all_columns(
    a_relation=old_relation,
    b_relation=new_relation,
    primary_key='order_id',
    summarize=false
  )
}}"
        }
      ]
    },
    {
      "title": "Compare All Columns - Excluding System Columns",
      "description": "Use this snippet to exclude specific columns, such as system-generated timestamps or metadata, from the comparison. This is useful for focusing on business-relevant data.",
      "language": "sql",
      "codeList": [
        {
          "language": "sql",
          "code": "{% set old_relation = adapter.get_relation(
  database='old_db',
  schema='old_schema',
  identifier='products'
) %}

{% set new_relation = ref('products') %}

{{ 
  audit_helper.compare_all_columns(
    a_relation=old_relation,
    b_relation=new_relation,
    primary_key='product_id',
    exclude_columns=['loaded_at', 'updated_at', '_fivetran_synced']
  )
}}"
        }
      ]
    }
  ]
}

```

--------------------------------

### Automatic Column Discovery Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/quick_are_relations_identical.md

Compares two dbt relations using automatic discovery of intersecting columns. Ensure the relations are accessible and have matching column names.

```sql
{
  "set prod_relation = api.Relation.create(
    database='prod_db',
    schema='analytics',
    identifier='customer_metrics'
  )
}

{% set dev_relation = ref('customer_metrics') %}

{{ 
  audit_helper.quick_are_relations_identical(
    a_relation=prod_relation,
    b_relation=dev_relation,
    columns=None
  )
}}
```

--------------------------------

### dbt Relation Object Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/types.md

Obtain a dbt Relation object using ref(), source(), or adapter.get_relation() for relation parameters. This object represents a dbt model or source table.

```python
{% set my_relation = adapter.get_relation(
  database='my_db',
  schema='my_schema',
  identifier='my_table'
) %}
```

--------------------------------

### Automatic Column Discovery Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/compare_and_classify_relation_rows.md

Use this snippet when you want the macro to automatically identify intersecting columns between the two relations. Ensure `columns` is set to `None`.

```sql
{% set old_relation = adapter.get_relation(
  database="legacy_db",
  schema="legacy_schema",
  identifier="customer_orders"
) %}

{% set new_relation = ref('customer_orders') %}

{{ 
  audit_helper.compare_and_classify_relation_rows(
    a_relation=old_relation,
    b_relation=new_relation,
    primary_key_columns=['order_id'],
    columns=None,
    sample_limit=50
  )
}}
```

--------------------------------

### Compare All Columns - Detailed View

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/compare_all_columns.md

Use this snippet to get row-level detail for each column comparison when summarize=false. This shows specific match classifications for each primary key.

```sql
{{ 
  audit_helper.compare_all_columns(
    a_relation=old_relation,
    b_relation=new_relation,
    primary_key='order_id',
    summarize=false
  )
}}
```

--------------------------------

### Filtered Columns Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/compare_and_classify_relation_rows.md

This example demonstrates how to use `dbt_utils.get_filtered_columns_in_relation` to exclude specific columns before passing them to `compare_and_classify_relation_rows`. This is useful for ignoring metadata columns like timestamps.

```sql
{% set all_columns = dbt_utils.get_filtered_columns_in_relation(
  old_relation, 
  except=['loaded_at', 'updated_at']
) %}

{{ 
  audit_helper.compare_and_classify_relation_rows(
    a_relation=old_relation,
    b_relation=new_relation,
    primary_key_columns=['id'],
    columns=all_columns
  )
}}
```

--------------------------------

### Error Handling on Unsupported Adapters

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/quick_are_queries_identical.md

Illustrates the compiler error message received when attempting to use `quick_are_queries_identical` on an adapter that does not support hash aggregation functions.

```sql
quick_are_queries_identical() is not implemented for adapter '<adapter_name>'
```

--------------------------------

### Macro Invocation Patterns

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/DOCUMENTATION_SUMMARY.txt

Demonstrates how to invoke dbt-audit-helper macros, showing both minimal and enhanced patterns for configuration.

```sql
-- Minimal invocation
{{ dbt_audit_helper.some_macro(...) }}

-- Enhanced invocation with configuration
{{ dbt_audit_helper.some_macro(
    ...,
    config_key = 'value'
) }}
```

--------------------------------

### compare_which_query_columns_differ

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/INDEX.md

Identifies which columns have any differences between queries, providing a boolean flag per column. It serves as a good starting point for investigation.

```APIDOC
## compare_which_query_columns_differ

### Description
Identifies which columns have any differences. Provides a boolean flag per column. Good starting point for investigation.

### Method
SQL Macro

### Endpoint
N/A (dbt macro)

### Parameters
(Details not provided in source)

### Request Example
(Not applicable for dbt macros in this format)

### Response
(Details not provided in source)
```

--------------------------------

### Enable dbt Debug Mode

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

Enable dbt debug mode to get detailed output during macro execution. This is useful for troubleshooting.

```bash
dbt debug
dbt run-operation my_macro --debug
```

--------------------------------

### Event Time Column Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/types.md

Specify the name of a datetime/timestamp column for the event_time parameter. This column is used to filter comparisons to a specific time window.

```plaintext
event_time='order_date'
event_time='created_at'
```

--------------------------------

### Multi-Environment Testing with compare_and_classify_relation_rows

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

Compare relations across different environments (e.g., dev vs. prod) using compare_and_classify_relation_rows. Define relations using api.Relation.create or ref.

```sql
-- Compare dev vs prod
{% set prod_relation = api.Relation.create(
  database='prod_db',
  schema='analytics',
  identifier='my_table'
) %}

{% set dev_relation = ref('my_table') %}

{{ audit_helper.compare_and_classify_relation_rows(
  a_relation=prod_relation,
  b_relation=dev_relation,
  primary_key_columns=['id']
 ) }}
```

--------------------------------

### Large Table Comparison with quick_are_*

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

For tables exceeding 100 million rows, use quick_are_* macros for efficient comparison. Specify the columns to compare.

```sql
{{ audit_helper.quick_are_queries_identical(
  query_a=old_query,
  query_b=new_query,
  columns=['id', 'name', 'amount']
) }}
```

--------------------------------

### Project Structure of dbt-audit-helper

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/INDEX.md

Displays the directory structure of the dbt-audit-helper package, including macro locations and configuration files.

```tree
dbt-audit-helper/
├── macros/
│   ├── compare_and_classify_query_results.sql
│   ├── compare_and_classify_relation_rows.sql
│   ├── quick_are_queries_identical.sql
│   ├── quick_are_relations_identical.sql
│   ├── compare_row_counts.sql
│   ├── compare_column_values.sql
│   ├── compare_which_query_columns_differ.sql
│   ├── compare_which_relation_columns_differ.sql
│   ├── compare_all_columns.sql
│   ├── compare_relation_columns.sql
│   ├── compare_queries.sql (legacy)
│   ├── compare_relations.sql (legacy)
│   └── utils/
│       ├── _classify_audit_row_status.sql
│       ├── _count_num_rows_in_status.sql
│       ├── _ensure_all_pks_are_in_column_set.sql
│       ├── _generate_null_safe_sk.sql
│       ├── _generate_set_results.sql
│       ├── _get_comparison_bounds.sql
│       └── _get_intersecting_columns_from_relations.sql
├── README.md
├── dbt_project.yml
└── packages.yml
```

--------------------------------

### Explicit Column Selection Example

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/quick_are_relations_identical.md

Compares two dbt relations by explicitly specifying the columns to include in the comparison. This is useful when you need to control exactly which fields are checked for identity.

```sql
{
  "set legacy_relation = adapter.get_relation(
    database='legacy_db',
    schema='legacy_schema',
    identifier='orders'
  )
}

{% set new_relation = ref('orders') %}

{{ 
  audit_helper.quick_are_relations_identical(
    a_relation=legacy_relation,
    b_relation=new_relation,
    columns=['order_id', 'customer_id', 'order_date', 'amount']
  )
}}
```

--------------------------------

### Configure Summarize Mode

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

Set the summarize parameter to true for fast summary mode or false for slower, more memory-intensive row-level detail in compare_all_columns.

```sql
-- Summary (fast)
summarize=true
```

```sql
-- Detailed (slower)
summarize=false
```

--------------------------------

### Identify Changed Columns (Step 1 of Investigation)

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/compare_which_relation_columns_differ.md

This is the first step in a common investigation pattern to identify which columns have changed between two relations. It uses `compare_which_relation_columns_differ` to get a summary of differences.

```sql
{{ audit_helper.compare_which_relation_columns_differ(
  a_relation=old_relation,
  b_relation=new_relation,
  primary_key_columns=['id']
) }}
```

--------------------------------

### quick_are_relations_identical

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/INDEX.md

A wrapper for relations that performs a fast hash-based comparison. It auto-discovers columns and offers the same hash-based efficiency as its query counterpart.

```APIDOC
## quick_are_relations_identical

### Description
Wrapper for relations. Auto-discovers columns. Provides the same hash-based efficiency as `quick_are_queries_identical`.

### Method
SQL Macro

### Endpoint
N/A (dbt macro)

### Parameters
(Details not provided in source)

### Request Example
(Not applicable for dbt macros in this format)

### Response
(Details not provided in source)
```

--------------------------------

### Standard dbt Project Configuration

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

Standard dbt project configuration settings that apply to all dbt projects. These include paths for models, tests, and macros, as well as target and clean-up configurations.

```yaml
name: my_project
version: 1.0.0

config-version: 2

profile: my_profile

model-paths: ["models"]
test-paths: ["tests"]

macro-paths: ["macros"]  # if you write your own macros

target-path: "target"
clean-targets:
  - "target"
  - "dbt_packages"
```

--------------------------------

### compare_queries

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/README.md

Compares two SQL queries and provides a summary of row differences. It can output a summary of unique and identical rows or a detailed row-by-row comparison.

```APIDOC
## compare_queries

### Description
Compares two SQL queries and provides a summary of row differences. It can output a summary of unique and identical rows or a detailed row-by-row comparison.

### Method
SQL (dbt Macro)

### Endpoint
N/A (dbt Macro)

### Parameters
#### Arguments
- **a_query** (string) - Required - The first SQL query to compare.
- **b_query** (string) - Required - The second SQL query to compare.
- **primary_key** (string) - Optional - The primary key of the model (or concatenated SQL to create the primary key). Used to sort unmatched results for row-by-row validation.
- **summarize** (boolean) - Optional - Allows you to switch between a summary or detailed view of the compared data. Accepts `true` or `false` values. Defaults to `true`.
- **limit** (integer) - Optional - Allows you to limit the number of rows returned when `summarize = False`. Defaults to `None` (no limit).

### Request Example
```sql
{% set old_query %}
  select
    id as order_id,
    amount,
    customer_id
  from old_database.old_schema.fct_orders
{% endset %}

{% set new_query %}
  select
    order_id,
    amount,
    customer_id
  from {{ ref('fct_orders') }}
{% endset %}

{{ audit_helper.compare_queries(
    a_query = old_query,
    b_query = new_query,
    primary_key = "order_id"
) }}
```

### Response
#### Success Response (200)
- **in_a** (boolean) - Indicates if the row exists in query 'a'.
- **in_b** (boolean) - Indicates if the row exists in query 'b'.
- **count** (integer) - The number of rows matching the in_a and in_b criteria.
- **percent_of_total** (float) - The percentage of total rows that match the in_a and in_b criteria.

#### Response Example (Summary)
```json
{
  "in_a": true,
  "in_b": true,
  "count": 6870,
  "percent_of_total": 99.74
}
```

#### Response Example (Detailed)
```json
{
  "order_id": 1,
  "order_date": "2018-01-01",
  "status": "completed",
  "in_a": true,
  "in_b": false
}
```
```

--------------------------------

### Compare Specific Column Values Between Queries

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/INDEX.md

After identifying differing columns, use this macro to get detailed statistics and compare values for a specific column between two queries. Requires a primary key for row matching.

```sql
-- Step 2: For changed columns, get details
{{ audit_helper.compare_column_values(
  a_query='select * from old_table',
  b_query='select * from new_table',
  primary_key='id',
  column_to_compare='order_date'
 ) }}
```

--------------------------------

### Quickly Compare Relations for Identical Content

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/README.md

A wrapper around `quick_are_queries_identical` that accepts two dbt relations. Relations must have the same columns, but they do not need to be in the same order. Pass `None` for columns to automatically find intersecting columns.

```sql
{% set old_relation = adapter.get_relation(
      database = "old_database",
      schema = "old_schema",
      identifier = "fct_orders"
) -%}

{% set dbt_relation = ref('fct_orders') %}

{{ audit_helper.quick_are_relations_identical(
    a_relation = old_relation,
    b_relation = dbt_relation,
    columns = None
) }}

```

--------------------------------

### Detailed Analysis of a Specific Changed Column

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/compare_which_query_columns_differ.md

After identifying a changed column with `compare_which_query_columns_differ`, use `compare_column_values` to get detailed statistics for that specific column. Ensure you provide the correct queries, primary key, and the column to compare.

```sql
-- Step 2: For each changed column, get detailed statistics
{{ audit_helper.compare_column_values(
  a_query=old_query,
  b_query=new_query,
  primary_key='order_id',
  column_to_compare='order_date'  -- was identified as changed in step 1
) }}
```

--------------------------------

### Print Query Output to Logs with compare_column_values

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/README.md

Use this SQL snippet to store and print the results of the `compare_column_values` macro to your logs. This is useful for debugging or when you don't want to preview the results directly.

```sql
{
set old_query %}
    select * from old_database.old_schema.dim_product
    where is_latest
{% endset %}

{% set new_query %}
    select * from {{ ref('dim_product') }}
{% endset %}

{% set audit_query = audit_helper.compare_column_values(
    a_query = old_query,
    b_query = new_query,
    primary_key = "product_id",
    column_to_compare = "status"
)
%}

{% set audit_results = run_query(audit_query) %}

{% if execute %}
{% do audit_results.print_table() %}
{% endif %}
```

--------------------------------

### Configure dbt Dependencies

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

Ensure dbt-audit-helper and dbt-utils are listed in your packages.yml file with compatible versions.

```yaml
packages:
  - package: dbt-labs/dbt_utils
    version: 0.9.0  -- or latest
  - package: dbt-labs/audit_helper
    version: 0.9.1  -- or latest
```

--------------------------------

### Analyze All Columns with Summary Statistics

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/INDEX.md

Use this macro to perform a comprehensive analysis of all columns between two relations, excluding specified columns and generating summary statistics. It requires a primary key for comparison.

```sql
-- Examine all columns with summary statistics
{{ audit_helper.compare_all_columns(
  a_relation=ref('current_model'),
  b_relation=api.Relation.create(database='prod', schema='analytics', identifier='current_model'),
  primary_key='id',
  exclude_columns=['loaded_at', 'updated_at'],
  summarize=true
 ) }}
```

--------------------------------

### Group and Filter Test Results by Model Attributes

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/README.md

Use a CTE with `compare_all_columns` and a subsequent join to group or filter test results based on attributes present in the model. This example counts conflicting values grouped by a 'status' column.

```sql
with base_test_cte as (
  {{ 
    audit_helper.compare_all_columns(
      a_relation=ref('stg_customers'),
      b_relation=api.Relation.create(database='dbt_db', schema='analytics_prod', identifier='stg_customers'), 
      exclude_columns=['updated_at'], 
      primary_key='id'
    ) 
  }}
  left join {{ ref('stg_customers') }} using(id)
  where conflicting_values
)
select
  status, 
  count(distinct case when conflicting_values then id end) as conflicting_values
from base_test_cte
group by 1
```

--------------------------------

### Row Count Comparison within a dbt Test

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/compare_row_counts.md

Integrates the compare_row_counts macro into a dbt test to assert that the row counts of two relations match. This example uses a window function to compare the current row's count with the previous one.

```sql
-- tests/assert_order_counts_match.sql
{% set old_relation = adapter.get_relation(
  database='prod_db',
  schema='backup',
  identifier='orders'
) %}

{% set new_relation = ref('orders') %}

{{ audit_helper.compare_row_counts(
  a_relation=old_relation,
  b_relation=new_relation
) }}

where total_records != lag(total_records) over (order by relation_name)
```

--------------------------------

### Enhanced Macro Configuration

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

Utilize optional parameters for finer control over macro behavior, such as setting a sample limit or filtering by event time.

```sql
{{ 
  audit_helper.compare_and_classify_query_results(
    a_query=old_query,
    b_query=new_query,
    primary_key_columns=['id'],
    columns=['id', 'name', 'amount'],
    sample_limit=50,  # show 50 samples per status instead of 20
    event_time='created_at'  # filter to specific time window
  )
}}
```

--------------------------------

### Basic Column Comparison with dbt

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/api-reference/compare_column_values.md

Compares the 'price' column between a legacy database and a dbt model using product_id as the primary key. This snippet demonstrates a common use case for identifying data discrepancies.

```sql
{
  set old_query %}
  select 
    product_id,
    name,
    price
  from legacy_db.legacy_schema.dim_products
  where is_active = true
{% endset %}

{% set new_query %}
  select 
    product_id,
    name,
    price
  from {{ ref('dim_products') }}
{% endset %}

{{ 
  audit_helper.compare_column_values(
    a_query=old_query,
    b_query=new_query,
    primary_key='product_id',
    column_to_compare='price'
  )
}}
```

--------------------------------

### Row-by-Row Comparison of Two Queries (Legacy)

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/README.md

The legacy `compare_queries` macro generates SQL for row-by-row comparison of two queries. It's useful for comparing refactored models or migrating from legacy systems, offering flexibility in filtering, renaming, or recasting columns.

```sql
select * from {{ audit_helper.compare_queries( 
    a_query=queries.legacy_users, 
    b_query=queries.refactored_users, 
    a_query_primary_key='user_id', 
    b_query_primary_key='user_id' 
) }}
```

--------------------------------

### quick_are_queries_identical

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/INDEX.md

Performs a fast hash-based comparison for identical result validation. It uses adapter-optimized hashing (e.g., Snowflake, BigQuery) and returns a single boolean result: True if identical.

```APIDOC
## quick_are_queries_identical

### Description
Fast hash-based comparison for identical result validation. Uses adapter-optimized hashing (Snowflake, BigQuery). Returns a single boolean result: True if identical.

### Method
SQL Macro

### Endpoint
N/A (dbt macro)

### Parameters
(Details not provided in source)

### Request Example
(Not applicable for dbt macros in this format)

### Response
(Details not provided in source)
```

--------------------------------

### compare_queries (Legacy)

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/INDEX.md

Legacy macro for row-by-row comparison using set operations (INTERSECT/EXCEPT). Users are advised to use `compare_and_classify_query_results` instead.

```APIDOC
## compare_queries (Legacy)

### Description
Legacy row-by-row comparison using set operations (INTERSECT/EXCEPT). **Use `compare_and_classify_query_results` instead**.

### Method
SQL Macro

### Endpoint
N/A (dbt macro)

### Parameters
(Details not provided in source)

### Request Example
(Not applicable for dbt macros in this format)

### Response
(Details not provided in source)
```

--------------------------------

### Quickly Compare Queries for Identical Content

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/README.md

Compares two queries by hashing all their rows. This is efficient for verifying that a refactor has not introduced changes. Supported on Snowflake and BigQuery. Requires a list of columns to compare.

```sql
{% set old_query %}
    select * from old_database.old_schema.dim_product
{% endset %}

{% set new_query %}
    select * from {{ ref('dim_product') }}
{% endset %}

{{ audit_helper.quick_are_queries_identical(
    query_a = old_query,
    query_b = new_query,
    columns=['order_id', 'amount', 'customer_id']
  )
}

```

--------------------------------

### Compare SQL Query Results with dbt Audit Helper

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/README.md

Use `compare_queries` to compare the results of two SQL queries. It can provide a summary of differences or a detailed row-by-row comparison. Ensure queries return comparable columns.

```sql
{

{% set old_query %}
  select
    id as order_id,
    amount,
    customer_id
  from old_database.old_schema.fct_orders
{% endset %}

{% set new_query %}
  select
    order_id,
    amount,
    customer_id
  from {{ ref('fct_orders') }}
{% endset %}

{{ audit_helper.compare_queries(
    a_query = old_query,
    b_query = new_query,
    primary_key = "order_id"
)
}

}
```

--------------------------------

### Set Sample Limit for Fast Feedback

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

Use sample_limit to control the number of rows returned per status, enabling faster feedback during development.

```sql
-- Fast check
sample_limit=10
```

```sql
-- Comprehensive report
sample_limit=None  -- unlimited
```

--------------------------------

### Compare and Classify Query Results

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/DOCUMENTATION_SUMMARY.txt

Use this macro to compare two queries and classify their rows into identical, added, removed, or modified categories. It requires specifying primary key columns and optionally columns for comparison and an event time.

```sql
select * from {{ dbt_audit_helper.compare_and_classify_query_results(
    a_query = "select id, name, status from {{ ref('a') }} where status = 'active'",
    b_query = "select id, name, status from {{ ref('b') }} where status = 'active'",
    primary_key_columns = ['id'],
    columns = ['name', 'status'],
    event_time = 'updated_at'
) }}

-- Example output:
-- id | name | status | classification
-- ---|------|--------|----------------
-- 1  | A    | active | identical
-- 2  | B    | active | added
-- 3  | C    | active | removed
-- 4  | D    | active | modified
```

--------------------------------

### Basic Test for Row Identicality

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

Store macro output in a variable and assert against it to create a basic test. This test fails if any row is not identical.

```sql
-- tests/assert_orders_unchanged.sql
{% set audit_query %}
  {{ 
    audit_helper.compare_and_classify_query_results(
      a_query='select * from prod_backup.orders',
      b_query='select * from {{ ref("orders") }}',
      primary_key_columns=['order_id'],
      columns=['order_id', 'customer_id', 'amount']
    )
  }}
{% endset %}

{{ audit_query }}
where dbt_audit_row_status != 'identical'
```

--------------------------------

### Enable dbt Test Failure Storage

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

Enable dbt test failure storage using the command line. Results are written to tables defined in your dbt_project.yml.

```bash
dbt test --store-failures
```

--------------------------------

### Compare All Columns

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/EXPORTED_FUNCTIONS.md

Compares all columns between two relations, providing a summary or detailed output. Useful for a comprehensive check of schema and data consistency between tables, with options to exclude specific columns.

```sql
{% macro compare_all_columns(a_relation, b_relation, primary_key, exclude_columns=[], summarize=true) %}
```

--------------------------------

### Conditional Sample Limit from Environment Variable

Source: https://github.com/dbt-labs/dbt-audit-helper/blob/main/_autodocs/configuration.md

Conditionally set the sample_limit macro parameter based on an environment variable. If the variable is not set, a default value of 20 is used.

```sql
{%- if env_var('DBT_AUDIT_HELPER_SAMPLE_LIMIT') -%}
  {% set sample_limit = env_var('DBT_AUDIT_HELPER_SAMPLE_LIMIT') | int %}
{%- else -%}
  {% set sample_limit = 20 %}
{%- endif -%}

{{ audit_helper.compare_and_classify_query_results(
  a_query=old_query,
  b_query=new_query,
  primary_key_columns=['id'],
  columns=['id', 'name'],
  sample_limit=sample_limit
) }}
```