Data Transformations

Data Transformations in Analytical Data Sharing

Vendia’s analytical data sharing platform provides powerful, no-code capabilities for transforming and preparing your data before sharing it with partners or consumers. These features help you meet governance requirements, protect sensitive information, and create targeted data products.

Transformation Types

Vendia supports two main types of data transformations:

• Column-Level Transformations: Control data visibility and apply masking to protect sensitive information
• Row-Level Transformations: Filter data to create targeted datasets based on specific conditions

Transformation Workflows

Vendia supports flexible transformation workflows to meet different data sharing needs:

• Transform During Ingestion: Apply filtering and masking as data is ingested from source systems
• Ingest Then Transform: Ingest all data first, then create new filtered/masked tables as needed

Important: Column-level and row-level filtering rules must be configured during data ingestion or table creation and cannot be modified after a table has been created.

Column-Level Transformations

Vendia provides fine-grained control over data visibility and access through column-level filtering rules and permissions. These features help ensure data governance, privacy compliance, and secure data sharing.

Column Masking

Apply dynamic masking to sensitive columns to protect personally identifiable information (PII) or confidential business data.

Setting Up Column Masking

Configure column masking during the data ingestion process or when creating a new Vendia table:

1. When to Configure: During data ingestion or table creation
2. Select Columns: Choose which columns need masking protection
3. Choose Column Action:
   • Allow: All columns are allowed by default (no masking)
   • Exclude: Exclude this column from the table entirely
   • Mask entire value: Replace the entire value with asterisks (*)
   • Mask everything except the last: Replace all but the last specified number of characters with asterisks
   • Replace entire value with: Replace the entire value with a custom provided value

Example Masking Scenarios

-- Original data
email: john.doe@company.com
phone: +1-555-123-4567
customer_id: 12345

-- After applying column actions
email: ****************** (mask entire value)
phone: +1-555-***-**** (mask everything except the last 4 characters)
customer_id: [CUSTOMER_ID] (replace entire value with custom text)
-- Note: Some columns may be excluded entirely and not appear in the result

Column Inclusion and Exclusion Rules

Control which columns are visible in your data tables through inclusion and exclusion actions:

• Allow: Include the column in the table with original values (default behavior)
• Exclude: Remove the column entirely from the table

Column-Level Best Practices

1. Plan Thoroughly: Since column rules cannot be changed after table creation, invest time in planning before ingestion
2. Principle of Least Privilege: Only grant access to columns that consumers actually need
3. Test Before Production: Always validate masking and visibility rules during the ingestion preview phase
4. Documentation: Maintain clear documentation of column access decisions for future reference
5. Compliance Review: Ensure column rules meet regulatory requirements before finalizing table creation

Row-Level Transformations

Vendia allows you to define row-level filtering rules to control which rows of data are shared in your table. These filters help you create targeted datasets by restricting data access based on specific attribute values and conditions.

Setting Up Row-Level Filters

Configure row-level filtering during the data ingestion process or when creating a new Vendia table. Each filter consists of three components:

1. Attribute: Select the column/field you want to filter on
2. Operator: Choose the comparison operator for your filter condition
3. Constraint: Specify the value to compare against

Available Operators

• Equal to: Exact match (=)
• Not equal to: Exclude exact matches (≠)
• Greater than: Values greater than the constraint (>)
• Greater than or equal to: Values greater than or equal to the constraint (≥)
• Less than: Values less than the constraint (<)
• Less than or equal to: Values less than or equal to the constraint (≤)
• Like: Pattern matching with wildcards
• Not like: Exclude pattern matches
• In: Match any value in a list
• Not in: Exclude any value in a list
• Null: Include only null/empty values
• Not null: Exclude null/empty values

Example Row-Level Filters

Basic Filtering Examples

-- Filter for specific marketing opt-in status
WHERE MARKETINGOPTIN_AIRLINE = 'true'

-- Filter for customers in specific regions
WHERE REGION = 'North America'

-- Filter for recent transactions
WHERE ORDER_DATE >= '2024-01-01'

-- Filter for high-value customers
WHERE CUSTOMER_TIER IN ('Premium', 'VIP')

-- Exclude test data
WHERE CUSTOMER_TYPE != 'TEST'

-- Filter for active accounts only
WHERE STATUS IS NOT NULL AND STATUS != 'INACTIVE'

Advanced Filtering Scenarios

-- Combine multiple conditions for targeted customer segments
WHERE REGION = 'Europe' AND CUSTOMER_TIER = 'Premium' AND LAST_ORDER_DATE >= '2024-06-01'

-- Filter for specific product categories with minimum order values
WHERE PRODUCT_CATEGORY LIKE '%Electronics%' AND ORDER_VALUE > 100

-- Exclude internal and test data
WHERE CUSTOMER_TYPE NOT IN ('INTERNAL', 'TEST', 'DEMO')

-- Filter for customers with complete profile information
WHERE EMAIL IS NOT NULL AND PHONE IS NOT NULL AND ADDRESS IS NOT NULL

Row-Level Best Practices

1. Start Simple: Begin with basic single-condition filters before adding complex multi-condition rules
2. Test Thoroughly: Always preview filter results to ensure they produce the expected data subset
3. Document Filter Logic: Maintain clear documentation of filter criteria and business rationale
4. Consider Performance: Complex filters with multiple conditions may impact query performance
5. Plan for Updates: Since filters can’t be changed after table creation, consider future data needs
6. Validate Data Quality: Ensure filter attributes have consistent, reliable data for effective filtering

Common Use Cases

Geographic Data Segmentation

-- North American customers only
WHERE COUNTRY IN ('USA', 'Canada', 'Mexico')

-- European Union compliance filtering
WHERE REGION = 'EU' AND GDPR_CONSENT = 'true'

Customer Segmentation

-- High-value customers
WHERE LIFETIME_VALUE > 10000 AND STATUS = 'ACTIVE'

-- Recent customer acquisition
WHERE SIGNUP_DATE >= '2024-01-01'

Time-Based Filtering

-- Recent transactions only
WHERE TRANSACTION_DATE >= '2024-07-01'

-- Exclude historical test data
WHERE CREATED_DATE >= '2023-01-01' AND ACCOUNT_TYPE != 'TEST'

Data Quality Filtering

-- Complete customer records only
WHERE EMAIL IS NOT NULL AND PHONE IS NOT NULL

-- Exclude system-generated test records
WHERE ACCOUNT_TYPE NOT IN ('TEST', 'SYSTEM', 'DEMO')

Transformation Workflow Steps

Step 1: Plan Before Ingestion

1. Data Classification: Identify sensitive columns (PII, financial data, proprietary information) before ingestion
2. Compliance Requirements: Consider GDPR, CCPA, HIPAA, or industry-specific regulations
3. Consumer Requirements: Define what data consumers need to see before creating the table
4. Filter Criteria: Determine which attributes will be used for row-level filtering

Step 2: Configure During Ingestion/Table Creation

1. Set Up Column Actions: Configure column actions in your data connector
2. Define Filter Conditions: Create rules that specify which rows to include in your table
3. Test Configuration: Preview masked/filtered data to verify actions behave as expected

Step 3: Deploy and Finalize

1. Validate Configuration: Ensure transformations meet compliance requirements
2. Complete Process: Finalize once all column actions and filters are validated
3. Document Decisions: Record transformation decisions for future reference

Example Use Cases

• Create tenant- or customer-specific datasets with custom filters and masking
• Redact sensitive fields before sharing with external partners
• Apply geographic or regulatory compliance filters for region-specific data sharing
• Mask PII while preserving data utility for analytics

Learn More

• Data Joins
• Getting Started with Analytical Data Sharing
• Analytical Data Sharing FAQ
• Contact Vendia Support for specific questions.