You are a senior data engineer.
Produce code or a code scaffold for a CSV-to-MySQL ETL pipeline.
Context:
- Goal or feature: [goal]
- Stack or language: [stack]
- Inputs and outputs: [inputs and outputs]
- Constraints: [constraints]
- Existing code context: [existing context]
- Integration points or surrounding system: [integration points]
- Acceptance criteria or edge cases: [acceptance criteria]
Instructions:
1. Make the key implementation decisions first, then write code that is correct, readable, and idiomatic for the stated stack.
2. Default to realistic production patterns for common tasks instead of toy scaffolds.
3. Include only the structure that materially helps maintainability, testing, or integration.
4. Explicitly address the failure modes that actually break this in production: assuming the CSV schema is stable and silently misaligning columns when a source file adds or reorders fields, no dedup/upsert strategy so re-running the pipeline creates duplicate rows, type coercion errors like a stray 'N/A' in a numeric column failing the entire batch instead of quarantining the bad row, and no transaction or checkpoint boundary so a mid-load failure leaves the table half-loaded.
5. Include validation guidance such as test cases, failure modes, or manual verification steps.
6. Avoid filler placeholders, fake environment variables, and pseudo-code unless the user explicitly wants a sketch.
Output format:
- Implementation approach
- Code
- Validation and test cases (including malformed-row handling)
- Integration notes and assumptions
Missing information policy:
Ask up to 2 concise questions only if the language, framework, integration point, or required behavior is unclear. Otherwise proceed with assumptions.
Quality bar:
- Prefer working simplicity with production realism.
- Avoid placeholder-heavy pseudo-code unless necessary.
- Name the specific failure modes above rather than generic edge-case language.
- Cover meaningful edge cases instead of only the happy path.
02
Design an Ecommerce Database Schema
Design a relational schema for an ecommerce website with products, orders, and customers.
You are a senior data engineering and databases architect creating a plan for a real engineering initiative.
Create a plan for the task below.
Context:
- Goal: [goal]
- Stack or environment: [stack]
- Current system or baseline: [current system]
- Constraints such as security, latency, budget, or staffing: [constraints]
- Scale, complexity, or production expectations: [scale]
- Risks, current issues, or dependencies: [risks]
Instructions:
1. Recommend a practical technical approach and explain why it fits the context.
2. Break the work into sensible phases, components, or rollout stages.
3. Explicitly address the failure modes that actually derail this kind of initiative: normalizing pricing and inventory so tightly that historical order records change retroactively when a product's price is updated later, no order-line snapshotting so an order references the live product row instead of freezing price and name at purchase time, missing constraints for concurrent inventory decrements that allow overselling, and a schema that can't represent ordinary real cases like partial refunds, multiple shipping addresses, or product variants.
4. Include validation, rollback, migration, or monitoring considerations where relevant.
5. Keep the plan realistic for the stated context and avoid architecture theater.
Output format:
- Plan overview
- Recommended approach and tradeoffs
- Implementation phases or rollout path
- Risks, dependencies, and validation checks
- Main assumptions or open questions
Missing information policy:
Ask up to 2 concise questions only if the goal or environment is unclear. Otherwise proceed with assumptions.
Quality bar:
- Make the plan executable.
- Avoid overengineering.
- Name the specific failure modes above rather than generic risk language.
- Show how the plan would survive real deployment pressure.
03
Create a PostgreSQL Indexing Strategy
Create an indexing strategy to speed up queries on large PostgreSQL datasets.
You are a senior data engineering and databases architect creating a plan for a real engineering initiative.
Create a plan for the task below.
Context:
- Goal: [goal]
- Stack or environment: [stack]
- Current system or baseline: [current system]
- Constraints such as security, latency, budget, or staffing: [constraints]
- Scale, complexity, or production expectations: [scale]
- Risks, current issues, or dependencies: [risks]
Instructions:
1. Recommend a practical technical approach and explain why it fits the context.
2. Break the work into sensible phases, components, or rollout stages.
3. Explicitly address the failure modes that actually derail this kind of initiative: indexing every column that appears in a WHERE clause without checking selectivity, which bloats write latency for no read benefit, composite index column order that doesn't match actual query patterns and makes the index unusable, no plan for ongoing index maintenance such as bloat and REINDEX as the table grows, and building the strategy from a slow-query sample that doesn't represent production's real query mix.
4. Include validation, rollback, migration, or monitoring considerations where relevant.
5. Keep the plan realistic for the stated context and avoid architecture theater.
Output format:
- Plan overview
- Recommended approach and tradeoffs
- Implementation phases or rollout path
- Risks, dependencies, and validation checks
- Main assumptions or open questions
Missing information policy:
Ask up to 2 concise questions only if the goal or environment is unclear. Otherwise proceed with assumptions.
Quality bar:
- Make the plan executable.
- Avoid overengineering.
- Name the specific failure modes above rather than generic risk language.
- Show how the plan would survive real deployment pressure.
04
Build a Python Data Pipeline for Multiple APIs
Develop a data pipeline in Python to aggregate and clean data from multiple APIs.
You are a senior data engineering and databases architect creating a plan for a real engineering initiative.
Create a plan for the task below.
Context:
- Goal: [goal]
- Stack or environment: [stack]
- Current system or baseline: [current system]
- Constraints such as security, latency, budget, or staffing: [constraints]
- Scale, complexity, or production expectations: [scale]
- Risks, current issues, or dependencies: [risks]
Instructions:
1. Recommend a practical technical approach and explain why it fits the context.
2. Break the work into sensible phases, components, or rollout stages.
3. Explicitly address the failure modes that actually derail this kind of initiative: no retry or backoff strategy for APIs that rate-limit or fail intermittently, silently dropping or duplicating records, schema drift across source APIs that change response shape without warning, and no idempotency key so re-running the pipeline after a partial failure double-counts data.
4. Include validation, rollback, migration, or monitoring considerations where relevant.
5. Keep the plan realistic for the stated context and avoid architecture theater.
Output format:
- Plan overview
- Recommended approach and tradeoffs
- Implementation phases or rollout path
- Risks, dependencies, and validation checks
- Main assumptions or open questions
Missing information policy:
Ask up to 2 concise questions only if the goal or environment is unclear. Otherwise proceed with assumptions.
Quality bar:
- Make the plan executable.
- Avoid overengineering.
- Name the specific failure modes above rather than generic risk language.
- Show how the plan would survive real deployment pressure.
05
Write a Multi-Table SQL Join and Aggregation Query with Correctness and Performance Notes
Write a multi-table SQL query that joins and aggregates correctly while accounting for duplication risk, grouping logic, and performance-sensitive filters.
You are a senior data engineer.
Produce code or a code scaffold for a multi-table SQL join and aggregation query.
Context:
- Goal or feature: [goal]
- Stack or language: [stack]
- Inputs and outputs: [inputs and outputs]
- Constraints: [constraints]
- Existing code context: [existing context]
- Integration points or surrounding system: [integration points]
- Acceptance criteria or edge cases: [acceptance criteria]
Instructions:
1. Make the key implementation decisions first, then write code that is correct, readable, and idiomatic for the stated stack.
2. Default to realistic production patterns for common tasks instead of toy scaffolds.
3. Include only the structure that materially helps maintainability, testing, or integration.
4. Explicitly address the failure modes that actually break this in production: a join that fans out rows before aggregation and silently inflates SUM or COUNT results, GROUP BY columns that don't match the actual grain of the report and produce misleading rollups, NULLs in join keys silently dropping matching rows instead of surfacing an error, and an unindexed join column that performs fine in dev but times out at production scale.
5. Include validation guidance such as test cases, failure modes, or manual verification steps.
6. Avoid filler placeholders, fake environment variables, and pseudo-code unless the user explicitly wants a sketch.
Output format:
- Implementation approach
- Code
- Validation and test cases (including fan-out and grain checks)
- Integration notes and assumptions
Missing information policy:
Ask up to 2 concise questions only if the language, framework, integration point, or required behavior is unclear. Otherwise proceed with assumptions.
Quality bar:
- Prefer working simplicity with production realism.
- Avoid placeholder-heavy pseudo-code unless necessary.
- Name the specific failure modes above rather than generic edge-case language.
- Cover meaningful edge cases instead of only the happy path.
06
Import Large Datasets into MongoDB Efficiently
Create a process for importing large datasets into MongoDB and optimizing query performance.
You are a senior data engineering and databases architect creating a plan for a real engineering initiative.
Create a plan for the task below.
Context:
- Goal: [goal]
- Stack or environment: [stack]
- Current system or baseline: [current system]
- Constraints such as security, latency, budget, or staffing: [constraints]
- Scale, complexity, or production expectations: [scale]
- Risks, current issues, or dependencies: [risks]
Instructions:
1. Recommend a practical technical approach and explain why it fits the context.
2. Break the work into sensible phases, components, or rollout stages.
3. Explicitly address the failure modes that actually derail this kind of initiative: bulk-inserting without disabling per-document index updates during the load, making it far slower than necessary, no chunking strategy causing memory pressure or timeouts on very large imports, write-concern settings left at defaults that either risk data loss or needlessly slow the import, and no post-import validation pass to catch documents that silently failed schema validation.
4. Include validation, rollback, migration, or monitoring considerations where relevant.
5. Keep the plan realistic for the stated context and avoid architecture theater.
Output format:
- Plan overview
- Recommended approach and tradeoffs
- Implementation phases or rollout path
- Risks, dependencies, and validation checks
- Main assumptions or open questions
Missing information policy:
Ask up to 2 concise questions only if the goal or environment is unclear. Otherwise proceed with assumptions.
Quality bar:
- Make the plan executable.
- Avoid overengineering.
- Name the specific failure modes above rather than generic risk language.
- Show how the plan would survive real deployment pressure.
07
Set Up MySQL Replication for High Availability
Set up MySQL replication to improve availability, fault tolerance, and operational recovery.
You are a senior data engineering and databases architect creating a plan for a real engineering initiative.
Create a plan for the task below.
Context:
- Goal: [goal]
- Stack or environment: [stack]
- Current system or baseline: [current system]
- Constraints such as security, latency, budget, or staffing: [constraints]
- Scale, complexity, or production expectations: [scale]
- Risks, current issues, or dependencies: [risks]
Instructions:
1. Recommend a practical technical approach and explain why it fits the context.
2. Break the work into sensible phases, components, or rollout stages.
3. Explicitly address the failure modes that actually derail this kind of initiative: replication lag that isn't monitored, so failover lands on a replica missing recent writes, no defined behavior for what the application does during a lag spike, split-brain risk from a failover process that doesn't fence off the old primary, and binlog retention too short to let a replica catch up after any real outage.
4. Include validation, rollback, migration, or monitoring considerations where relevant.
5. Keep the plan realistic for the stated context and avoid architecture theater.
Output format:
- Plan overview
- Recommended approach and tradeoffs
- Implementation phases or rollout path
- Risks, dependencies, and validation checks
- Main assumptions or open questions
Missing information policy:
Ask up to 2 concise questions only if the goal or environment is unclear. Otherwise proceed with assumptions.
Quality bar:
- Make the plan executable.
- Avoid overengineering.
- Name the specific failure modes above rather than generic risk language.
- Show how the plan would survive real deployment pressure.
08
Automate MongoDB Backup and Restore with Retention and Recovery Checks
Develop a MongoDB backup and restore workflow that handles schedule, retention, target storage, and basic recovery verification instead of backup-only automation.
You are a senior data engineer.
Produce code or a code scaffold for a MongoDB backup and restore workflow.
Context:
- Goal or feature: [goal]
- Stack or language: [stack]
- Inputs and outputs: [inputs and outputs]
- Constraints: [constraints]
- Existing code context: [existing context]
- Integration points or surrounding system: [integration points]
- Acceptance criteria or edge cases: [acceptance criteria]
Instructions:
1. Make the key implementation decisions first, then write code that is correct, readable, and idiomatic for the stated stack.
2. Default to realistic production patterns for common tasks instead of toy scaffolds.
3. Include only the structure that materially helps maintainability, testing, or integration.
4. Explicitly address the failure modes that actually break this in production: backups taken with no check for point-in-time consistency on a live, actively-written collection, no verification step confirming a restore actually succeeds until the day it's needed in an emergency, a retention policy that silently deletes the one backup old enough to matter for a slow-discovered corruption, and backup-target credentials stored in a way that leaks into logs or version control.
5. Include validation guidance such as test cases, failure modes, or manual verification steps.
6. Avoid filler placeholders, fake environment variables, and pseudo-code unless the user explicitly wants a sketch.
Output format:
- Implementation approach
- Code
- Validation and test cases (including restore verification)
- Integration notes and assumptions
Missing information policy:
Ask up to 2 concise questions only if the language, framework, integration point, or required behavior is unclear. Otherwise proceed with assumptions.
Quality bar:
- Prefer working simplicity with production realism.
- Avoid placeholder-heavy pseudo-code unless necessary.
- Name the specific failure modes above rather than generic edge-case language.
- Cover meaningful edge cases instead of only the happy path.
09
Detect and Handle Missing Values in a Database Table with Explicit Data Quality Rules
Write SQL to detect and handle missing values using explicit data quality rules, distinguishing true nulls, empty placeholders, and remediation-safe updates.
You are a senior data engineer.
Produce code or a code scaffold for SQL for detecting and handling missing values in a database table.
Context:
- Goal or feature: [goal]
- Stack or language: [stack]
- Inputs and outputs: [inputs and outputs]
- Constraints: [constraints]
- Existing code context: [existing context]
- Integration points or surrounding system: [integration points]
- Acceptance criteria or edge cases: [acceptance criteria]
Instructions:
1. Make the key implementation decisions first, then write code that is correct, readable, and idiomatic for the stated stack.
2. Default to realistic production patterns for common tasks instead of toy scaffolds.
3. Include only the structure that materially helps maintainability, testing, or integration.
4. Explicitly address the failure modes that actually break this in production: treating NULL, empty string, and placeholder values like 'N/A' or '-' as the same thing when they carry different meanings, a NOT IN clause silently returning zero rows the moment one comparison value is NULL, missing-value rules applied uniformly across columns that have very different business meaning for 'missing', and no reporting path so detected data-quality issues never reach whoever can fix them upstream.
5. Include validation guidance such as test cases, failure modes, or manual verification steps.
6. Avoid filler placeholders, fake environment variables, and pseudo-code unless the user explicitly wants a sketch.
Output format:
- Implementation approach
- Code
- Validation and test cases (including NULL vs placeholder distinction)
- Integration notes and assumptions
Missing information policy:
Ask up to 2 concise questions only if the language, framework, integration point, or required behavior is unclear. Otherwise proceed with assumptions.
Quality bar:
- Prefer working simplicity with production realism.
- Avoid placeholder-heavy pseudo-code unless necessary.
- Name the specific failure modes above rather than generic edge-case language.
- Cover meaningful edge cases instead of only the happy path.
10
Design a Time-Series Data Storage Solution
Design a database solution for efficiently storing and processing time-series data.
You are a senior data engineering and databases architect creating a plan for a real engineering initiative.
Create a plan for the task below.
Context:
- Goal: [goal]
- Stack or environment: [stack]
- Current system or baseline: [current system]
- Constraints such as security, latency, budget, or staffing: [constraints]
- Scale, complexity, or production expectations: [scale]
- Risks, current issues, or dependencies: [risks]
Instructions:
1. Recommend a practical technical approach and explain why it fits the context.
2. Break the work into sensible phases, components, or rollout stages.
3. Explicitly address the failure modes that actually derail this kind of initiative: choosing a general-purpose relational database for high-cardinality time-series data and hitting write and query performance walls at scale, no downsampling or retention policy so raw data grows unbounded, query patterns that scan across too many tags or dimensions defaulting to full-table scans, and no plan for backfilled or out-of-order data arriving after the fact.
4. Include validation, rollback, migration, or monitoring considerations where relevant.
5. Keep the plan realistic for the stated context and avoid architecture theater.
Output format:
- Plan overview
- Recommended approach and tradeoffs
- Implementation phases or rollout path
- Risks, dependencies, and validation checks
- Main assumptions or open questions
Missing information policy:
Ask up to 2 concise questions only if the goal or environment is unclear. Otherwise proceed with assumptions.
Quality bar:
- Make the plan executable.
- Avoid overengineering.
- Name the specific failure modes above rather than generic risk language.
- Show how the plan would survive real deployment pressure.
11
Manage Schema Migrations with Flyway or Liquibase
Create a process for managing relational database schema migrations.
You are a senior data engineering and databases architect creating a plan for a real engineering initiative.
Create a plan for the task below.
Context:
- Goal: [goal]
- Stack or environment: [stack]
- Current system or baseline: [current system]
- Constraints such as security, latency, budget, or staffing: [constraints]
- Scale, complexity, or production expectations: [scale]
- Risks, current issues, or dependencies: [risks]
Instructions:
1. Recommend a practical technical approach and explain why it fits the context.
2. Break the work into sensible phases, components, or rollout stages.
3. Explicitly address the failure modes that actually derail this kind of initiative: a migration that locks a large table for the duration of an ALTER and causes a production outage, no rollback or down-migration tested before the up-migration ships, migrations applied out of order across environments because they weren't run through the same tool everywhere, and a column-type change that silently truncates or corrupts existing data.
4. Include validation, rollback, migration, or monitoring considerations where relevant.
5. Keep the plan realistic for the stated context and avoid architecture theater.
Output format:
- Plan overview
- Recommended approach and tradeoffs
- Implementation phases or rollout path
- Risks, dependencies, and validation checks
- Main assumptions or open questions
Missing information policy:
Ask up to 2 concise questions only if the goal or environment is unclear. Otherwise proceed with assumptions.
Quality bar:
- Make the plan executable.
- Avoid overengineering.
- Name the specific failure modes above rather than generic risk language.
- Show how the plan would survive real deployment pressure.
12
Parse JSON API Data into a Relational Database with Mapping and Load Validation
Write a Python ingestion script that maps JSON API payloads into a relational schema with normalization, insert safety, and validation around partial or malformed records.
You are a senior data engineer.
Produce code or a code scaffold for a JSON API-to-relational ingestion script.
Context:
- Goal or feature: [goal]
- Stack or language: [stack]
- Inputs and outputs: [inputs and outputs]
- Constraints: [constraints]
- Existing code context: [existing context]
- Integration points or surrounding system: [integration points]
- Acceptance criteria or edge cases: [acceptance criteria]
Instructions:
1. Make the key implementation decisions first, then write code that is correct, readable, and idiomatic for the stated stack.
2. Default to realistic production patterns for common tasks instead of toy scaffolds.
3. Include only the structure that materially helps maintainability, testing, or integration.
4. Explicitly address the failure modes that actually break this in production: nested or array JSON fields flattened inconsistently across records and corrupting relational normalization, a schema assumption baked in that breaks the moment the API adds an optional field, no upsert or idempotency handling so re-running ingestion on the same payload creates duplicates, and a single bad record aborting the entire batch instead of being isolated and logged.
5. Include validation guidance such as test cases, failure modes, or manual verification steps.
6. Avoid filler placeholders, fake environment variables, and pseudo-code unless the user explicitly wants a sketch.
Output format:
- Implementation approach
- Code
- Validation and test cases (including partial-payload isolation)
- Integration notes and assumptions
Missing information policy:
Ask up to 2 concise questions only if the language, framework, integration point, or required behavior is unclear. Otherwise proceed with assumptions.
Quality bar:
- Prefer working simplicity with production realism.
- Avoid placeholder-heavy pseudo-code unless necessary.
- Name the specific failure modes above rather than generic edge-case language.
- Cover meaningful edge cases instead of only the happy path.
13
Monitor Database Performance and Slow Queries
Develop a method for monitoring database performance, slow queries, and index health.
You are a senior data engineering and databases architect creating a plan for a real engineering initiative.
Create a plan for the task below.
Context:
- Goal: [goal]
- Stack or environment: [stack]
- Current system or baseline: [current system]
- Constraints such as security, latency, budget, or staffing: [constraints]
- Scale, complexity, or production expectations: [scale]
- Risks, current issues, or dependencies: [risks]
Instructions:
1. Recommend a practical technical approach and explain why it fits the context.
2. Break the work into sensible phases, components, or rollout stages.
3. Explicitly address the failure modes that actually derail this kind of initiative: monitoring average query latency instead of tail latency (p95/p99), which hides the queries actually causing user-facing pain, alert thresholds copied from a generic template that don't match this database's real baseline, no correlation between slow-query logs and the application code paths that triggered them, and index-health checks treated as a one-time audit instead of an ongoing habit.
4. Include validation, rollback, migration, or monitoring considerations where relevant.
5. Keep the plan realistic for the stated context and avoid architecture theater.
Output format:
- Plan overview
- Recommended approach and tradeoffs
- Implementation phases or rollout path
- Risks, dependencies, and validation checks
- Main assumptions or open questions
Missing information policy:
Ask up to 2 concise questions only if the goal or environment is unclear. Otherwise proceed with assumptions.
Quality bar:
- Make the plan executable.
- Avoid overengineering.
- Name the specific failure modes above rather than generic risk language.
- Show how the plan would survive real deployment pressure.
14
Design a Real-Time Kafka Streaming Pipeline into a Database
Create an approach for real-time data streaming with Kafka and durable storage.
You are a senior data engineering and databases architect creating a plan for a real engineering initiative.
Create a plan for the task below.
Context:
- Goal: [goal]
- Stack or environment: [stack]
- Current system or baseline: [current system]
- Constraints such as security, latency, budget, or staffing: [constraints]
- Scale, complexity, or production expectations: [scale]
- Risks, current issues, or dependencies: [risks]
Instructions:
1. Recommend a practical technical approach and explain why it fits the context.
2. Break the work into sensible phases, components, or rollout stages.
3. Explicitly address the failure modes that actually derail this kind of initiative: no handling for out-of-order or late-arriving events, so downstream aggregates are silently wrong, consumer offset management that isn't idempotent and causes duplicate writes on any restart or rebalance, no dead-letter path for malformed messages so one bad message stalls the whole consumer group, and unversioned schema evolution on the topic that breaks consumers when producers change the payload shape.
4. Include validation, rollback, migration, or monitoring considerations where relevant.
5. Keep the plan realistic for the stated context and avoid architecture theater.
Output format:
- Plan overview
- Recommended approach and tradeoffs
- Implementation phases or rollout path
- Risks, dependencies, and validation checks
- Main assumptions or open questions
Missing information policy:
Ask up to 2 concise questions only if the goal or environment is unclear. Otherwise proceed with assumptions.
Quality bar:
- Make the plan executable.
- Avoid overengineering.
- Name the specific failure modes above rather than generic risk language.
- Show how the plan would survive real deployment pressure.
15
Design a Redshift Data Warehouse Architecture
Design a process for building a data warehouse architecture with AWS Redshift and ETL tools.
You are a senior data engineering and databases architect creating a plan for a real engineering initiative.
Create a plan for the task below.
Context:
- Goal: [goal]
- Stack or environment: [stack]
- Current system or baseline: [current system]
- Constraints such as security, latency, budget, or staffing: [constraints]
- Scale, complexity, or production expectations: [scale]
- Risks, current issues, or dependencies: [risks]
Instructions:
1. Recommend a practical technical approach and explain why it fits the context.
2. Break the work into sensible phases, components, or rollout stages.
3. Explicitly address the failure modes that actually derail this kind of initiative: a distribution or sort key choice that doesn't match actual query patterns and causes expensive redistribution on every join, ETL loads that trickle in with slow row-by-row inserts instead of bulk COPY, no vacuum or analyze maintenance plan so query plans degrade silently as tables grow, and no cost monitoring for a cluster sized for peak load but idle most of the time.
4. Include validation, rollback, migration, or monitoring considerations where relevant.
5. Keep the plan realistic for the stated context and avoid architecture theater.
Output format:
- Plan overview
- Recommended approach and tradeoffs
- Implementation phases or rollout path
- Risks, dependencies, and validation checks
- Main assumptions or open questions
Missing information policy:
Ask up to 2 concise questions only if the goal or environment is unclear. Otherwise proceed with assumptions.
Quality bar:
- Make the plan executable.
- Avoid overengineering.
- Name the specific failure modes above rather than generic risk language.
- Show how the plan would survive real deployment pressure.