Data Transformation Discovery

The core function is to infer the applied transformations by analyzing input and output data states, rather than reading explicit code. This involves:

• Comparing source and target datasets to deduce calculations, filters, and joins.
• Using techniques like program synthesis or differential analysis to hypothesize the transformation rules.
• The goal is to reconstruct undocumented or black-box business logic, such as a revenue calculation or a customer segmentation rule.

It establishes precise data lineage by mapping how a single column or value is derived through multiple transformation steps. This enables:

• Impact Analysis: Understanding which downstream reports, models, or dashboards are affected by a change to a source column.
• Root Cause Diagnosis: Tracing an anomalous output value back through the transformation chain to its source.
• Provenance Tracking: Documenting the full history of a data element, which is critical for auditability and regulatory compliance in sectors like finance and healthcare.

The process automatically identifies functional dependencies between columns and tables. Key outputs include:

• Column-Level Dependencies: Discovering that output_column_c is calculated from (input_column_a + input_column_b) * constant.
• Table-Level Dependencies: Identifying that a final aggregated table is built from joins across five source staging tables.
• This mapping creates a directed acyclic graph (DAG) of data flow, which is essential for pipeline optimization, change management, and understanding system complexity.

Beyond syntactic changes, it aims to understand the business meaning of transformations. This involves:

• Inferring that a string-cleaning operation standardizes product codes to a canonical format.
• Recognizing that a series of date manipulations calculates a service-level agreement (SLA) compliance metric.
• Discerning that a conditional flag marks records for fraud review. This semantic layer bridges the gap between raw data manipulation and business logic, making pipelines interpretable to non-engineers.

A primary output is the generation of machine-readable and human-understandable documentation of transformation rules. This includes:

• Declarative Code: Producing SQL, dbt YAML, or Dataform definitions that describe the inferred transformations.
• Data Catalog Enrichment: Populating a metadata catalog with discovered lineage, dependencies, and business glossaries.
• This automation is critical for maintaining an accurate single source of truth as pipelines evolve, preventing documentation from becoming stale and unreliable.

By establishing a baseline of transformation logic, the system can monitor for unexpected changes or logic drift. This capability allows for:

• Alerting when a transformation's output statistical profile changes due to an unannounced code update.
• Detecting when a join condition silently fails, leading to missing or duplicated records.
• Identifying when business logic has been inconsistently applied across different pipeline runs or environments (e.g., dev vs. prod). This is a foundational element of data observability.

The automated inference of business logic that creates new columns from existing ones. This includes:

• Mathematical operations: Discovering formulas like total_price = unit_price * quantity or profit_margin = (revenue - cost) / revenue.
• String concatenations: Identifying joins such as full_name = first_name + ' ' + last_name.
• Date arithmetic: Finding rules like age = current_date - birth_date or delivery_date = order_date + 7 days.
• Categorical mappings: Uncovering CASE statements or lookup tables that map codes to descriptive labels.

The discovery of cleansing rules that enforce consistent data representation. Common patterns include:

• Date/time parsing: Inferring the conversion of strings like '2023-12-25', '12/25/2023', or '25-Dec-2023' into a canonical DATE type.
• Numeric normalization: Finding transformations that strip currency symbols ($, €), thousand separators, and convert decimals.
• Phone/ID formatting: Detecting rules that add country codes, enforce dashes, or pad numbers to a fixed length.
• Boolean conversion: Identifying logic that maps 'Y/N', 'True/False', or '1/0' to a standard BOOLEAN type.

The reverse-engineering of WHERE clause predicates and conditional replacements applied to rows or specific values. Examples are:

• Range filters: Discovering rules like WHERE age >= 18 or WHERE transaction_amount > 1000.
• Inclusion/Exclusion lists: Finding filters based on specific values, e.g., WHERE country_code IN ('US', 'CA', 'UK').
• Null handling: Identifying COALESCE functions or default value assignments for missing data.
• Censoring/Redaction: Uncovering rules that replace sensitive values (e.g., credit card middle digits) with placeholders like XXXX.

The inference of summarization logic that reduces data granularity. This encompasses:

• Group-by operations: Discovering key columns used for aggregation, such as customer_id or product_category.
• Aggregate functions: Identifying the use of SUM(), AVG(), COUNT(), MIN(), and MAX() on measure columns.
• Pivot transformations: Detecting logic that transposes rows to columns, often turning unique values in one column into new column headers.
• Window functions: Finding more complex operations like running totals (SUM(...) OVER (ORDER BY date)) or row-numbering for deduplication.

The discovery of relationships and enrichment logic between disparate datasets. This includes:

• Join key detection: Automatically identifying the common columns (e.g., user_id, order_id) used to merge tables.
• Join type inference: Determining if the operation is an INNER JOIN, LEFT JOIN, or FULL OUTER JOIN based on resulting row counts and null patterns.
• Lookup enrichment: Finding rules that append descriptive attributes from a dimension table (e.g., adding product_name and category to a sales fact table using a product_sku key).
• Fuzzy matching logic: Uncovering probabilistic joins using string similarity algorithms (e.g., Levenshtein distance) for name or address matching.

The identification of cryptographic or obfuscation transformations applied to data. Discovered patterns often are:

• One-way hashing: Detecting the use of algorithms like SHA-256 or MD5 to create irreversible digests for identifiers, often for privacy.
• Encoding schemes: Finding transformations like Base64 encoding for binary data or URL encoding for special characters.
• Tokenization: Discovering rules that replace sensitive values (e.g., Social Security Numbers) with non-sensitive surrogate tokens that preserve referential integrity.
• Deterministic encryption: Inferring the use of encryption where the same input always produces the same encrypted output, allowing for joins on encrypted fields.

Data profiling is the foundational, automated process of analyzing a dataset to understand its structure, content, and quality. It involves scanning data to generate descriptive statistics, identify data types, and detect patterns. This creates a quantitative baseline that transformation discovery builds upon to infer how data was changed, not just what it contains.

• Core Activities: Calculating min/max/mean, null counts, value frequency distributions, and uniqueness.
• Output: A statistical summary that reveals data shape, anomalies, and initial quality metrics.

Schema discovery is the automated inference of a dataset's structural metadata. It determines column names, data types (e.g., VARCHAR, INTEGER, DATE), constraints (nullable, unique), and potential primary keys. For transformation discovery, understanding the output schema is the first step in working backward to deduce the transformation rules that created it from a source with a different schema.

• Key Function: Automatically building a CREATE TABLE-like definition from raw data.
• Relation to Transformations: A changed schema (e.g., a column split or merged) is direct evidence of a transformation.

Data lineage is the tracking of data's origin, movement, characteristics, and transformation across its lifecycle. While transformation discovery infers the logic of a single step, lineage maps the entire flow. Lineage tools often use discovered transformations to annotate the nodes in a pipeline graph, answering "where did this data come from and what was done to it?"

• Forward vs. Backward: Forward lineage tracks data to its consumers; backward lineage traces it to its sources.
• Impact Analysis: Critical for understanding the downstream effect of a change in a transformation rule.

Functional dependency discovery is the process of identifying relationships where the value of one set of columns uniquely determines the value of another set (e.g., city → state). This is a core algorithmic component of transformation discovery, as many business rules (like lookups or calculations) create functional dependencies. Discovering these dependencies helps reverse-engineer complex JOIN logic or derived column formulas.

• Example: Discovering that total_price = unit_price * quantity.
• Use Case: Uncovering hidden business logic embedded in calculated fields.

Pattern recognition in data profiling involves identifying recurring formats or structures within data values. This is essential for transformation discovery because transformations often follow predictable patterns: date reformatting, string concatenation, or standardizing phone numbers. By recognizing the output pattern (e.g., (XXX) XXX-XXXX) and comparing it to the source pattern, the transformation rule can be hypothesized.

• Common Patterns: Email addresses, credit card numbers, ISO date formats, custom IDs.
• Techniques: Regular expression matching and probabilistic classifiers.

Metadata extraction is the automated collection of descriptive information about data to populate a data catalog. This goes beyond basic schema to include business glossaries, data owners, and quality scores. Transformation discovery contributes critical operational metadata: the inferred business logic or SQL snippets that define a column. This turns opaque data into a documented, governed asset.

• Enriched Catalogs: Catalogs with discovered transformation rules provide context that dramatically improves data usability and trust.
• Automated Documentation: Reduces the burden of manual pipeline documentation.