pandas

pandas#

Why pandas#

schema_analyzer.py is the one file in this project that ever looks at actual row data rather than a database’s own catalog of tables and columns (that catalog-only work is SQLAlchemy’s job, see SQLAlchemy). When metadata_only is left at its default of False, SchemaAnalyzer._detect_business_rules samples up to 100 rows from each of the first five analyzed tables and asks a per-column question of each sample: does this column look like it holds email addresses, phone numbers, or a bounded numeric range such as a percentage or rating. pandas.read_sql is the function that turns a SQLAlchemy query result into something that already has that per-column shape, typed columns, a .dropna(), a .dtype, a .min()/.max(), instead of a list of raw DB-API row tuples the code would otherwise have to transpose and type-check by hand.

That is the entire role pandas plays here, and it is worth stating plainly, because pandas is also a required base dependency: pyproject.toml pins pandas>=2.1.3 outside [project.optional-dependencies], so it installs on every pip install test-data-workbench, whether or not a user ever runs an analysis that reaches this sampling code path. schema_analyzer.py is the only file in the entire project that imports pandas; nothing in core, the rest of adaptation, api, or cli touches it. Whether that weight is earned belongs in Sharp edges and limits below; this section is only about what pandas is actually asked to do.

The idea underneath#

A pandas DataFrame is, in the library’s own words, “a 2-dimensional labeled data structure with columns of potentially different types. You can think of it like a spreadsheet or SQL table, or a dict of Series objects” (Intro to data structures). Two ideas sit underneath that one sentence, and both predate pandas itself.

The first is columnar storage. A DataFrame is not a list of row-records the way a CSV reader or a list of dicts would represent one; each column is held as its own typed array, built on NumPy’s ndarray, and a DataFrame behaves more like a dict of same-length columns than a list of same-shape rows. That layout favors exactly the kind of question this project asks of it: “what is the minimum of this one column, across every sampled row” touches one contiguous block of memory of a single known type, rather than walking every row and picking one field out of each.

The second idea is vectorization. NumPy’s documentation describes the ndarray as encapsulating “n-dimensional arrays of homogeneous data types, with many operations being performed in compiled code for performance,” and defines vectorization as “the absence of any explicit looping, indexing, etc., in the code” because that looping happens instead “behind the scenes, in optimized, pre-compiled C code” (What is NumPy?). A pandas column, a Series, is backed by exactly this kind of array, so calling .min() or .dropna() on it hands the looping to code compiled once in advance rather than interpreted element by element on every call. The same reasoning is why pandas ships a parallel vectorized string API, Series.str, as a compiled alternative to writing for val in column: re.match(...) by hand in Python.

Neither idea originates with pandas. The DataFrame is pandas’ own counterpart to R’s data.frame, the two-dimensional, column-typed table structure R had already made standard for statistical computing; pandas’ own documentation says as much directly, describing itself as providing “a lot of the data manipulation and analysis functionality that people use R for” and mapping R’s data.frame to pandas’ DataFrame as the equivalent structure (Comparison with R / R libraries). The array underneath it is NumPy’s ndarray, a structure that predates pandas and that pandas builds on rather than reimplements.

How it fits this project#

pandas’ involvement begins and ends inside two methods in src/test_data_workbench/adaptation/schema_analyzer.py: SchemaAnalyzer._detect_business_rules and the _analyze_data_patterns helper it calls. Both run only when analyze_production_schema executes without metadata_only=True (see SQLAlchemy for the full guard). A pandas.DataFrame is built, inspected, and discarded entirely within that one call chain; by the time _detect_business_rules returns, its result is a plain list of BusinessRule dataclass instances (src/test_data_workbench/core/models.py), and no DataFrame or Series survives past that return. Everything downstream, dependency ordering, template rendering, generator code emission, works from those dataclasses and never imports pandas at all.

Reading a sampled query into a DataFrame#

for table in tables[:5]:  # Limit for rapid analysis
    try:
        with engine.connect() as conn:
            query = text(f'SELECT * FROM "{table.name}" LIMIT 100')
            df = pd.read_sql(query, conn)

            if not df.empty:
                rules.extend(self._analyze_data_patterns(table, df))

    except Exception:
        continue

pd.read_sql(query, conn) takes the SQLAlchemy text() query and the open connection, executes it, and returns the result as a DataFrame, one column per selected database column, capped at 100 rows by the query’s own LIMIT 100. The surrounding loop caps the table count too: tables[:5] means at most five DataFrames are built per analysis run, each holding at most 100 rows. df.empty is checked before doing anything further, so a table whose sample comes back with zero rows is skipped rather than handed to _analyze_data_patterns. Any exception during either the connect or the read, a permissions error, a locked table, aborts that one table’s rule detection and moves on to the next; nothing is logged or recorded about the failure, the same bare except Exception pattern this file uses elsewhere for reflection failures (see SQLAlchemy).

Column-by-column pattern detection#

for column in df.columns:
    col_data = df[column].dropna()
    if len(col_data) < 10:
        continue

    # Email pattern detection
    if any(re.match(r'^[\w\.-]+@[\w\.-]+\.\w+$', str(val)) for val in col_data.head()):
        rules.append(BusinessRule.create(
            f"Column {column} contains email addresses",
            "format",
            [column],
            confidence=0.9
        ))

    # Phone pattern detection
    if any(re.match(r'[\+]?[\d\s\-\(\)]{10,}', str(val)) for val in col_data.head()):
        rules.append(BusinessRule.create(
            f"Column {column} contains phone numbers",
            "format",
            [column],
            confidence=0.8
        ))

_analyze_data_patterns iterates df.columns, and for each one takes df[column], a Series, and calls .dropna() before doing anything else. A column with fewer than 10 non-null values remaining (len(col_data) < 10) is skipped outright, no pattern detection is attempted on a thin sample. For both regex checks, the code does not scan col_data in full: col_data.head() returns pandas’ default of the first five non-null values, each converted with str(val) and matched with re.match inside a plain Python any(...) generator expression. A single match among those five values is enough to append a BusinessRule (BusinessRule.create, src/test_data_workbench/core/models.py) claiming the entire column holds email addresses or phone numbers, at a fixed confidence of 0.9 or 0.8. Sample size and match ratio play no part in that confidence number.

Type checks read straight off the DataFrame#

if df[column].dtype in ['int64', 'float64']:
    min_val, max_val = col_data.min(), col_data.max()
    if min_val >= 0 and max_val <= 100:
        rules.append(BusinessRule.create(
            f"Column {column} appears to be percentage/rating (0-100)",
            "range",
            [column],
            confidence=0.7
        ))

df[column].dtype in ['int64', 'float64'] relies on pandas’ dtype objects comparing equal to a plain string on the right-hand side; no casting or parsing happens here, pandas already inferred the column’s dtype when pd.read_sql built the DataFrame from the query result. When the dtype matches, col_data.min() and col_data.max(), called on the Series the loop above already passed through dropna(), are the only two reduction calls this file makes. If the whole observed range sits inside 0 to 100 inclusive, the column is recorded as a likely percentage or rating.

Sharp edges and limits#

The dependency is heavy; the usage is light. pandas>=2.1.3 is a required base dependency, not an extra, and pandas pulls in NumPy as its own required dependency underneath it. Installing this project means installing both, on every machine that runs pip install test-data-workbench, for a CLI tool where a user feels startup latency directly. What that buys, in this codebase, is one pd.read_sql call, a .dropna(), two .head() calls, a .dtype comparison, and a .min()/.max() pair, confined to two methods in one file. That gap between the weight of the dependency and the amount of it actually exercised is real and worth naming plainly: measured against what pandas is built to do, joins, groupby aggregation, reshaping, time series alignment, this project uses almost none of it.

The import cost is paid on every run, not only when the pandas code path executes. import pandas as pd sits at module level, at the top of schema_analyzer.py, next to sqlalchemy. That module loads whenever SchemaAnalyzer is needed, which is on every tdw analyze and tdw deploy invocation, including ones run with --metadata-only, where _detect_business_rules returns immediately without ever calling into pandas. The import happens regardless of that flag; only the work behind it is conditional.

The same job would plainly fit in the standard library. Nothing in _detect_business_rules or _analyze_data_patterns needs a DataFrame’s alignment, joins, or grouping. This is not a hypothetical rewrite: the same file already samples row data without pandas elsewhere. _get_sample_values and _estimate_row_count, two other methods in this same schema_analyzer.py, query row data using plain conn.execute(query) and Python. The exact same approach would work here: transpose the row objects conn.execute(query) already returns into per-column lists, filter None values with a list comprehension in place of dropna(), slice the first five values in place of .head(), check isinstance(val, (int, float)) per sampled value in place of the .dtype comparison, and use the min()/max() builtins in place of the Series methods.

The dataset is always small enough that pandas’ actual advantages never come into play. Every query here is capped with LIMIT 100, and the loop that issues it stops after tables[:5], five tables. Columnar storage and vectorized reductions earn their keep on data too large to iterate row by row in pure Python at acceptable speed; on at most 100 rows, a Python for loop, min(), and max() would not be measurably slower. pandas’ known large-table weaknesses, read_sql materializing an entire result set in memory rather than streaming it, or per-column operations scaling with row count, are consequently untested by this code path. The cap makes them moot here, but it also means the tool never exercises the case pandas is actually built for.

The one place a vectorized pandas operation would be the idiomatic choice, it is not used. pandas ships Series.str, a vectorized string-matching API, for exactly the kind of email- and phone-pattern check _analyze_data_patterns performs. The actual code instead calls col_data.head() to pull out five plain Python values and matches each with re.match inside a Python-level any(...) generator. That is ordinary Python string handling running on a value pulled out of a DataFrame, not vectorization; operating on a Series rather than a list is incidental to how the check actually executes.

The dtype check is an exact string match, not a numeric-type test. df[column].dtype in ['int64', 'float64'] matches only those two specific dtype strings. A column that reflects as 32-bit (int32, float32), pandas’ own nullable integer type (Int64), or a boolean column, is silently excluded from the percentage/rating range check, with no error and nothing in the result noting it was skipped. pandas.api.types.is_numeric_dtype exists for exactly this kind of check and is not used here.

Learn more resources#

Official documentation#

Tutorials and blogs#

Videos#