Query Aware Generation

Query-Aware Generation

Query-aware generation helps you create synthetic data that contains the values, partition dates, and join paths your SQL queries expect.

Use it when you want to test SQL logic, ETL pipelines, partition pruning, joins, aggregations, or performance behavior without using production data.

All query-aware options are optional. Existing generation behavior is unchanged unless you provide required_values, partition_by, target_selectivity, ensure_join_coverage, or query_profile.

Why this exists

Random synthetic data is often not enough for query testing.

A production query may expect values like:

WHERE business_date BETWEEN '2026-01-01' AND '2026-01-31'
  AND region = 'SOUTH'
  AND product_type IN ('CHECKING', 'SAVINGS')
  AND member_status = 'ACTIVE'

If generated data does not contain those values, the query may return no rows. That makes the test less useful.

Query-aware generation lets you tell Great Generator which values and partitions must exist.

Single-table example

from great_generator import generate_from_schema

schema = """
member_id string,
business_date date,
region string,
product_type string,
member_status string,
interaction_count int,
balance double
"""

df = generate_from_schema(
    schema=schema,
    rows=1_000_000,
    required_values={
        "region": ["SOUTH"],
        "product_type": ["CHECKING", "SAVINGS"],
        "member_status": ["ACTIVE"],
    },
    partition_by={
        "column": "business_date",
        "values": ["2026-01-01", "2026-01-02", "2026-01-03"],
        "distribution": "balanced",
    },
    seed=42,
)

Required values

Use required_values to make sure specific values appear in generated data.

required_values={
    "region": ["SOUTH"],
    "product_type": ["CHECKING", "SAVINGS"],
}

This does not mean the column will contain only those values. Other generated values may also appear.

Partition-aware generation

Use partition_by to generate data for specific partition values.

partition_by={
    "column": "business_date",
    "values": ["2026-01-01", "2026-01-02", "2026-01-03"],
    "distribution": "balanced",
}

Balanced distribution creates equal or near-equal row counts across the listed partition values.

Custom partition counts

Use custom counts when you need exact row counts per partition.

partition_by={
    "column": "business_date",
    "counts": {
        "2026-01-01": 100000,
        "2026-01-02": 100000,
        "2026-01-03": 95000,
    },
}

If rows is also provided, the sum of custom counts should match rows. If it does not, Great Generator raises a clear validation error.

Target selectivity

Use target_selectivity to request approximate ratios for required values.

target_selectivity={
    "region": {
        "SOUTH": 0.25,
    },
    "product_type": {
        "CHECKING": 0.30,
        "SAVINGS": 0.20,
    },
}

This asks the generator to create about 25% of rows with region = "SOUTH", about 30% with product_type = "CHECKING", and about 20% with product_type = "SAVINGS".

Selectivity is approximate unless exact-count behavior is explicitly supported.

Relational example

from great_generator import generate_relational

tables = {
    "dim_member": {
        "schema": "member_id int primary key, region string, member_status string",
        "rows": 100_000,
    },
    "dim_product": {
        "schema": "product_id int primary key, product_type string",
        "rows": 1_000,
    },
    "fact_interaction": {
        "schema": (
            "interaction_id int primary key, "
            "member_id int references dim_member.member_id, "
            "product_id int references dim_product.product_id, "
            "business_date date, interaction_count int"
        ),
        "rows": 5_000_000,
    },
}

data = generate_relational(
    tables=tables,
    required_values={
        "dim_member.region": ["SOUTH"],
        "dim_product.product_type": ["CHECKING", "SAVINGS"],
        "dim_member.member_status": ["ACTIVE"],
    },
    partition_by={
        "table": "fact_interaction",
        "column": "business_date",
        "values": ["2026-01-01", "2026-01-02", "2026-01-03"],
        "distribution": "balanced",
    },
    ensure_join_coverage=True,
    seed=42,
)

Join coverage

Use ensure_join_coverage=True when required values are on dimension tables and you need matching fact rows.

For example, if you require:

required_values={
    "dim_member.region": ["SOUTH"],
    "dim_product.product_type": ["CHECKING", "SAVINGS"],
}

Then ensure_join_coverage=True tells Great Generator to create fact rows that join to members in the SOUTH region and products with CHECKING or SAVINGS.

Coverage validation

Use the coverage report to confirm the generated data contains the requested values and partitions.

from great_generator import validate_query_coverage

report = validate_query_coverage(
    data=df,
    required_values=required_values,
    partition_by=partition_by,
    target_selectivity=target_selectivity,
)

A report can include:

required_values_status
partition_coverage_status
partition_counts
selectivity_actuals
selectivity_targets
join_coverage_status
warnings

Query profile

For simple use cases, pass required_values, partition_by, and target_selectivity directly.

For reusable configs, use query_profile.

query_profile={
    "required_values": {
        "region": ["SOUTH"],
        "product_type": ["CHECKING", "SAVINGS"],
    },
    "partition_by": {
        "column": "business_date",
        "values": ["2026-01-01", "2026-01-02", "2026-01-03"],
        "distribution": "balanced",
    },
    "target_selectivity": {
        "region": {
            "SOUTH": 0.25,
        },
    },
}

If both direct arguments and query_profile are provided, direct arguments override query_profile.

What this does not guarantee

Query-aware generation helps synthetic data match expected query values, partition dates, and join paths.

It does not guarantee identical production performance because file layout, table statistics, clustering, caching, concurrency, warehouse size, and query engine configuration also affect runtime.

Roadmap

Future improvements may include:

• Inferring query profiles from SQL text
• Weighted partition distribution
• Business-day-only partition generation
• Month-end skew generation
• More advanced selectivity controls
• Multi-table selectivity validation
• Query coverage reports exported as JSON or HTML