Database

Data Retrieval & Transformation

• Type Mapping:
  • Ensure SQL types (e.g., DECIMAL, BIGINT, TIMESTAMP) are correctly mapped to Python/JSON types without precision loss.
  • Convert NULL values to a consistent "missing" representation (e.g., None or NaN).
• Chunked Fetching:
  • For medium-to-large exports, use fetchmany(size) or OFFSET/LIMIT pagination instead of fetching everything into memory at once.
• Aggregations:
  • Prefer performing calculations (SUM, AVG, COUNT) at the database level rather than pulling raw data to the client for processing.

Error Handling & Recovery

• Timeout Management: If a query takes too long, retry with more restrictive filters or optimized joins.
• Syntax Errors: If a query fails, inspect the dialect-specific error message and re-verify the schema (it's often a misspelled column or missing quotes).

Anti-Pattern Prevention (Avoiding "Bad" SQL)

• Index-Friendly Filters: Never wrap indexed columns in functions (e.g., DATE(), UPPER()) within the WHERE clause.
• Join Safety: Always verify join keys. Before joining, check if the key has high cardinality to avoid massive intermediate result sets.
• Memory Safety:
  • Avoid DISTINCT and UNION (which performs de-duplication) on multi-million row sets unless necessary; use UNION ALL if duplicates are acceptable.
  • Avoid ORDER BY on large non-indexed text fields.
• Wildcard Warning: Strictly avoid leading wildcards in LIKE patterns (e.g., %term) on large text columns.
• No Function on Columns: WHERE col = FUNC(val) is good; WHERE FUNC(col) = val is bad.
• Explicit Columns: Only fetch what is necessary.
• Early Filtering: Push WHERE conditions as close to the base tables as possible.
• CTE for Clarity: Use WITH for complex multi-step logic to improve maintainability and optimizer hints.

Best Practices

• Always verify database structure before querying
• Use appropriate sampling techniques for large datasets
• Optimize queries for efficiency based on schema inspection
• Self-review the draft SQL against the "Anti-Pattern Prevention" list.
• Perform a silent mental 'EXPLAIN' on your query. If it smells like a full table scan on a large table, refactor it before outputting