In-Context Learning: Zero-Shot vs. Few-Shot Prompting

One of the most remarkable discoveries in AI is that Large Language Models can learn new tasks on the fly without updating a single weight or running backpropagation.

This phenomenon is called In-Context Learning (ICL): by providing a few demonstrations in the prompt, the model's self-attention layers condition their predictions on the demonstrated pattern.

1. Zero-Shot vs. Few-Shot Defined

flowchart TD
    subgraph ZeroShotPrompt["Zero-Shot: Instruction Only"]
        Z1["Instruction: Convert legacy HiveQL to PySpark"]
        Z2["Input: SELECT date_add('2026-01-01', 5)"]
        Z3["Output: Model guesses without seeing examples"]
        Z1 --> Z2 --> Z3
    end

    subgraph FewShotPrompt["Few-Shot: Instruction + Demonstrations"]
        F1["Instruction: Convert legacy HiveQL to PySpark"]
        F2["Example 1: HiveQL 'nvl(a, b)' --> PySpark 'coalesce(col(a), col(b))'"]
        F3["Example 2: HiveQL 'size(my_arr)' --> PySpark 'size(col(my_arr))'"]
        F4["Target Input: SELECT date_add('2026-01-01', 5)"]
        F5["Output: Follows exact demonstrated style & imports!"]
        F1 --> F2 --> F3 --> F4 --> F5
    end

    style ZeroShotPrompt fill:#1e293b,color:#fff
    style FewShotPrompt fill:#064e3b,color:#fff

Zero-Shot

You state the task and the input, relying purely on the model's pre-trained knowledge:

• Best for: Common, general-knowledge tasks (e.g., standard SQL syntax, English grammar correction).
• Failure point: Fails on domain-specific formatting, company-specific schemas, or unusual edge-case mappings.

Few-Shot

You provide 2 to 5 curated (Input, Output) demonstrations before the final input:

• Best for: Complex formatting, company coding conventions, schema translation, and strict error-handling patterns.
• Accuracy Boost: Adding just 3 high-quality few-shot examples often boosts extraction accuracy from ~65% to >95%!

2. Anatomy of a High-Impact Few-Shot Demonstration

The quality of your few-shot examples matters far more than the quantity:

You are an expert Data Engineer converting Oracle SQL queries into PySpark DataFrame code.

### EXAMPLES ###

Example 1:
Input Query:
SELECT dept_id, AVG(salary) FROM employees WHERE status = 'A' GROUP BY dept_id;
Output PySpark:
df_employees.filter(col("status") == "A").groupBy("dept_id").agg(avg("salary"))

Example 2 (Handling Nulls):
Input Query:
SELECT NVL(commission, 0) FROM sales;
Output PySpark:
df_sales.select(coalesce(col("commission"), lit(0)))

Example 3 (Window Functions):
Input Query:
SELECT emp_id, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY hire_date) FROM staff;
Output PySpark:
window_spec = Window.partitionBy("dept_id").orderBy("hire_date")
df_staff.select(col("emp_id"), row_number().over(window_spec))

### TARGET TASK ###
Input Query:
{target_query}
Output PySpark:

Best Practices for Few-Shot Selection:

1. Consistency: Keep the formatting, capitalization, and punctuation identical across all examples.
2. Diversity: Include an edge case (e.g., null handling) and a complex case (e.g., window functions).
3. Balanced Classes: If doing sentiment or classification, ensure positive and negative examples are equally represented to prevent class bias.

3. Dynamic Few-Shot Selection (RAG-Powered)

If your enterprise has 1,000 legacy stored procedures to migrate to Spark, you cannot fit all 1,000 examples into a single prompt.

The modern architecture uses Dynamic Few-Shot:

1. Store all verified migration pairs in a Vector Database (ChromaDB/Pinecone).
2. When a new SQL query arrives, search the vector DB for the top 3 most semantically similar past queries.
3. Dynamically inject those 3 into the prompt as few-shot demonstrations!

flowchart LR
    IncomingSQL["New Query:<br/>SELECT NVL(tax, 0)..."] --> VectorSearch["Vector Search<br/>(Find 3 Most Similar Queries)"]
    VectorSearch --> BestExamples["Top 3 Matching Examples:<br/>1. NVL query<br/>2. Coalesce query<br/>3. Tax table query"]
    BestExamples --> Prompt["Dynamic Prompt<br/>(Targeted Few-Shot Examples)"]
    Prompt --> LLM["LLM Generates Accurate Code"]

    style IncomingSQL fill:#2563eb,color:#fff
    style VectorSearch fill:#7c3aed,color:#fff
    style BestExamples fill:#059669,color:#fff
    style LLM fill:#d97706,color:#fff

4. Hands-on: Dynamic Few-Shot Selector in Python

import numpy as np

# Repository of verified SQL-to-PySpark migration pairs
MIGRATION_STORE = [
    {
        "id": 1,
        "category": "aggregation",
        "sql": "SELECT dept, SUM(sales) FROM transactions GROUP BY dept",
        "pyspark": "df.groupBy('dept').agg(sum('sales'))"
    },
    {
        "id": 2,
        "category": "window",
        "sql": "SELECT id, DENSE_RANK() OVER (PARTITION BY cat ORDER BY val DESC) FROM items",
        "pyspark": "w = Window.partitionBy('cat').orderBy(col('val').desc())\ndf.select('id', dense_rank().over(w))"
    },
    {
        "id": 3,
        "category": "null_handling",
        "sql": "SELECT COALESCE(phone, 'N/A') FROM customers",
        "pyspark": "df.select(coalesce(col('phone'), lit('N/A')))"
    }
]

def mock_vectorize(text: str):
    """Mock vectorizer based on feature keywords."""
    features = ["group", "window", "rank", "null", "coalesce", "sum", "agg"]
    v = np.zeros(len(features))
    for i, word in enumerate(features):
        if word in text.lower():
            v[i] = 1.0
    norm = np.linalg.norm(v)
    return v / norm if norm > 0 else v

def get_dynamic_few_shot_prompt(target_sql: str, k: int = 1) -> str:
    target_vec = mock_vectorize(target_sql)

    # Rank examples by cosine similarity
    ranked = []
    for item in MIGRATION_STORE:
        ex_vec = mock_vectorize(item["sql"])
        sim = float(np.dot(target_vec, ex_vec))
        ranked.append((sim, item))
    ranked.sort(key=lambda x: x[0], reverse=True)

    top_examples = [item for _, item in ranked[:k]]

    prompt = "Convert SQL to PySpark DataFrame code.\n\n### EXAMPLES ###\n"
    for ex in top_examples:
        prompt += f"SQL: {ex['sql']}\nPySpark:\n{ex['pyspark']}\n\n"
    prompt += f"### TARGET ###\nSQL: {target_sql}\nPySpark:\n"
    return prompt

if __name__ == "__main__":
    query = "SELECT emp_id, RANK() OVER (PARTITION BY region ORDER BY revenue) FROM sales"
    dynamic_prompt = get_dynamic_few_shot_prompt(query, k=1)
    print("Dynamically Selected Few-Shot Prompt:")
    print(dynamic_prompt)

Key Takeaways

• In-Context Learning (ICL) allows LLMs to adapt to new patterns without weight updates.
• Few-shot prompting (2-5 examples) is the single most cost-effective technique to boost code generation fidelity.
• Dynamic few-shot selection via vector databases scales example selection across thousands of enterprise patterns.