/verify-data-in-db Verify my Oracle table has 2 records after pipeline execution

/verify-data-in-db Export data from Snowflake table and compare with expected output

/verify-data-in-db Create full verification test for PostgreSQL pipeline results

/verify-data-in-db Compare actual vs expected CSV, excluding timestamp columns

Capture And Verify Number of records From DB Table
...    ${table_name}
...    ${schema_name}
...    ${order_by_column}
...    ${expected_record_count}

Export DB Table Data To CSV
...    ${table_name}
...    ${order_by_column}
...    ${output_file_path}

[Template]    Compare CSV Files With Exclusions Template
${actual_file_path}    ${expected_file_path}    ${ignore_order}    ${show_details}    ${expected_status}    @{excluded_columns}

*** Settings ***
Documentation    Verifies data in database table after pipeline execution
Resource         snaplogic_common_robot/snaplogic_apis_keywords/snaplogic_keywords.resource
Resource         ../../resources/common/sql_table_operations.resource
Resource         ../../resources/common/files.resource
Library          Collections

*** Variables ***

---

*** Settings ***
Documentation    Verifies data in Oracle database table after pipeline execution
...              This test suite validates that the pipeline correctly inserted data
...              by checking record counts and comparing with expected output
Resource         snaplogic_common_robot/snaplogic_apis_keywords/snaplogic_keywords.resource
Resource         ../../resources/common/sql_table_operations.resource
Resource         ../../resources/common/files.resource
Resource         ../../../resources/common/database.resource
Library          Collections
Library          DatabaseLibrary
Library          oracledb

Suite Setup      Connect To Oracle And Initialize
Suite Teardown   Disconnect From Database

*** Variables ***

Capture And Verify Number of records From DB Table
...    ${task_params_set}[table_name]
...    ${task_params_set}[schema_name]
...    ${order_by_column}
...    ${expected_record_count}

Export DB Table Data To CSV
...    ${task_params_set}[table_name]
...    ${order_by_column}
...    ${actual_output_file}

# file1_path    file2_path    ignore_order    show_details    expected_status    exclude_columns
${actual_output_file}    ${expected_output_file}    ${FALSE}    ${TRUE}    IDENTICAL    @{excluded_columns_for_comparison}

undefined

undefined

project-root/
├── test/
│   └── suite/
│       ├── pipeline_tests/
│       │   └── oracle/
│       │       ├── oracle_verify_data.robot                ← Test case file
│       │       └── VERIFY_DATA_README.md                   ← This file
│       └── test_data/
│           └── actual_expected_data/
│               ├── actual_output/
│               │   └── oracle/
│               │       └── oracle_actual_output.csv        ← Exported actual data
│               └── expected_output/
│                   └── oracle/
│                       └── expected_output_file1.csv       ← Expected baseline
└── .env                                                    ← Environment configuration

@{excluded_columns_for_comparison}
...    event_timestamp
...    unique_event_id
...    created_date

undefined

undefined

*** Settings ***
Documentation    Verifies data in Snowflake table after pipeline execution
Resource         snaplogic_common_robot/snaplogic_apis_keywords/snaplogic_keywords.resource
Resource         ../../../resources/snowflake/snowflake_keywords_databaselib.resource
Resource         ../../resources/common/sql_table_operations.resource
Resource         ../../resources/common/files.resource
Library          Collections

Suite Setup      Connect To Snowflake Via DatabaseLibrary    keypair
Suite Teardown   Disconnect From Snowflake

*** Variables ***
${table_name}                   DEMO.TEST_SNAP4
${schema_name}                  DEMO
${order_by_column}              RECORD_METADATA
${expected_record_count}        2

${actual_output_file}           ${CURDIR}/../../test_data/actual_expected_data/actual_output/snowflake/actual_output.csv
${expected_output_file}         ${CURDIR}/../../test_data/actual_expected_data/expected_output/snowflake/expected_output.csv

@{excluded_columns_for_comparison}
...    SnowflakeConnectorPushTime
...    unique_event_id
...    event_timestamp

*** Test Cases ***
Verify Data In Snowflake Table
    [Documentation]    Verifies data integrity in Snowflake table.
    [Tags]    snowflake    verification
    Capture And Verify Number of records From DB Table
    ...    ${table_name}
    ...    ${schema_name}
    ...    ${order_by_column}
    ...    ${expected_record_count}

Export Snowflake Data To CSV
    [Documentation]    Exports Snowflake table data to CSV.
    [Tags]    snowflake    export
    Export DB Table Data To CSV
    ...    ${table_name}
    ...    ${order_by_column}
    ...    ${actual_output_file}

Compare Actual vs Expected
    [Documentation]    Compares actual vs expected CSV output.
    [Tags]    snowflake    comparison
    [Template]    Compare CSV Files With Exclusions Template
    ${actual_output_file}    ${expected_output_file}    ${FALSE}    ${TRUE}    IDENTICAL    @{excluded_columns_for_comparison}

*** Settings ***
Documentation    Verifies data in PostgreSQL table after pipeline execution
Resource         snaplogic_common_robot/snaplogic_apis_keywords/snaplogic_keywords.resource
Resource         ../../resources/common/sql_table_operations.resource
Resource         ../../resources/common/files.resource
Library          Collections
Library          DatabaseLibrary

Suite Setup      Connect To PostgreSQL Database
Suite Teardown   Disconnect From Database

*** Variables ***
${table_name}                   public.test_table
${schema_name}                  public
${order_by_column}              id
${expected_record_count}        5

${actual_output_file}           ${CURDIR}/../../test_data/actual_expected_data/actual_output/postgres/actual_output.csv
${expected_output_file}         ${CURDIR}/../../test_data/actual_expected_data/expected_output/postgres/expected_output.csv

*** Test Cases ***
Verify Data In PostgreSQL Table
    [Documentation]    Verifies data integrity in PostgreSQL table.
    [Tags]    postgres    verification
    Capture And Verify Number of records From DB Table
    ...    ${table_name}
    ...    ${schema_name}
    ...    ${order_by_column}
    ...    ${expected_record_count}

*** Variables ***
@{excluded_columns_for_comparison}
...    event_timestamp                    # Timestamp when event was created
...    unique_event_id                    # Auto-generated unique ID
...    SnowflakeConnectorPushTime         # Snowflake connector timestamp
...    created_date                       # Record creation date
...    modified_date                      # Record modification date
...    /MARKETING-NOTIFICATIONS/CONTENT   # Dynamic notification content

┌─────────────────────────┐
│  1. Suite Setup         │
│  (Connect to Database)  │
└───────────┬─────────────┘
            │
            ▼
┌─────────────────────────┐
│  2. (Previous Steps)    │
│  - Create Account       │
│  - Import Pipeline      │
│  - Execute Task         │
└───────────┬─────────────┘
            │
            ▼
┌─────────────────────────┐
│  3. Verify Record Count │  ◄── VERIFY COUNT
│  (Expected: 2 records)  │
└───────────┬─────────────┘
            │
            ▼
┌─────────────────────────┐
│  4. Export Data to CSV  │  ◄── EXPORT DATA
│  (actual_output.csv)    │
└───────────┬─────────────┘
            │
            ▼
┌─────────────────────────┐
│  5. Compare CSV Files   │  ◄── COMPARE OUTPUT
│  (Actual vs Expected)   │
└───────────┬─────────────┘
            │
            ▼
┌─────────────────────────┐
│  6. Suite Teardown      │
│  (Disconnect Database)  │
└─────────────────────────┘

undefined

project-root/
├── test/
│   └── suite/
│       ├── pipeline_tests/
│       │   └── [type]/
│       │       ├── [type]_verify_data.robot                ← Test case file
│       │       └── VERIFY_DATA_README.md                   ← This file
│       └── test_data/
│           └── actual_expected_data/
│               ├── actual_output/
│               │   └── [type]/
│               │       └── actual_output.csv               ← Exported actual data
│               └── expected_output/
│                   └── [type]/
│                       └── expected_output.csv             ← Expected baseline
└── .env                                                    ← Environment configuration

make robot-run-all-tests TAGS="[type] AND verification" PROJECT_SPACE_SETUP=True

---