Data engineering
Building data systems: schemas, SQL performance, pipelines, migrations of data.
Download all 99
- Data engineer
Acts as a data engineer who designs for idempotency, backfills and observability, treats schemas as contracts with their consumers, and asks who depends on each table before changing it.
- Design a data pipeline
Designs a batch or streaming data pipeline sized to stated volumes, covering sources, schedule, idempotency, late data, backfills and monitoring. Use before building or replacing a pipeline.
- Design a relational database schema
Designs a relational schema from requirements and access patterns, with keys, constraints, types, indexes and DDL. Use when starting a new service or feature that stores data.
- Design a star schema
Designs a dimensional model from the questions analysts need answered: business processes, grain, facts, dimensions, slowly changing dimension types and DDL. Use when building a warehouse layer.
- Generate realistic seed data
Generates realistic, referentially consistent fixture data for a database schema, with labelled edge cases and no real personal data. Use for local development, demos and integration tests.
- Plan a zero-downtime schema change
Turns current table DDL and a desired change into expand and contract steps with lock-safe SQL, app changes, backfill, verification and rollback. Use before altering a live table.
- Review database indexes against the workload
Reviews a database's indexes against its real query workload to find missing, unused, duplicate and bloated indexes, with DDL and the write cost of each change. Use for periodic index hygiene.
- Review a database migration
Reviews a schema migration for locking risk, table rewrites, unsafe defaults, missing indexes, irreversible steps and deploy-order problems, and returns a safer version. Use before merging.
- Write a data dictionary
Writes a data dictionary for database tables with each column's meaning, units, nullability, allowed values, owner and lineage, and flags every column it cannot infer. Use when documenting a schema.
- Write data-quality checks for a table
Writes data-quality checks for a table (freshness, volume, schema, validity, uniqueness, referential integrity, distribution) with severities, thresholds and owners. Use when a table feeds decisions.
- Write a dbt model
Writes a dbt model from business logic, with declared sources, a stated grain, unique, not_null and relationships tests, column docs and a safe incremental strategy. Use when adding a dbt model.
- Add missing foreign keys to a legacy schema
Adds missing foreign keys to a live database by finding orphan rows, deciding how to fix them, and adding constraints without long locks using NOT VALID then VALIDATE or the engine's online DDL.
- Add read replicas and route reads
Adds database read replicas and routes reads to them safely, handling replication lag, read-your-writes, long queries on replicas, failover and monitoring in the app's data layer.
- Analytics engineer
Acts as an analytics engineer who turns raw tables into tested, documented models analysts trust, with grain first, dimensional modelling, metrics defined once, tests, contracts and clear ownership.
- Automate Postgres backups with point-in-time recovery
Sets up automated Postgres backups with point-in-time recovery using pgBackRest, WAL-G or managed snapshots, with retention, encryption, off-site copies and a scheduled restore test.
- Build an API ingestion job
Builds a job that pulls a paginated third-party API into raw storage with rate limiting, retries, incremental state, schema drift capture, idempotent writes and alerting.
- Build a job queue on Postgres
Builds a job queue in Postgres with FOR UPDATE SKIP LOCKED, leases that expire, retries with backoff, dead jobs, cleanup and tests, and says when to move to a real broker.
- Bulk load data into a database
Loads millions of rows fast with the engine's bulk path (COPY, LOAD DATA or batched inserts), a staging table, deferred indexes, validation and a load that can resume after failure.
- Choose a database for a workload
Recommends a database type and product from access patterns, consistency needs, volume, team skills and operations budget, explaining why the boring default usually wins and what would change it.
- Choose a primary key strategy
Chooses between bigint identity, UUIDv4, UUIDv7, ULID, natural and composite keys per table, weighing index locality, size, exposure in URLs, client-side generation and migration cost.
- Configure SQLite for a production server app
Configures SQLite for a server app with WAL mode, busy timeouts, safe pragmas, a single-writer pattern, backups with Litestream or the backup API, and the limits that mean it is time to move.
- Turn an exploratory notebook into a tested pipeline
Converts an exploratory notebook into a parameterised script or pipeline task with functions, config, logging and a test reproducing its key outputs. Use when a notebook moves to production.
- Create a materialized view for a slow aggregate
Creates a materialized view or summary table for a slow aggregate with a refresh strategy (concurrent, scheduled or incremental), its indexes and an agreed staleness limit.
- Create a sanitized dev database from production
Builds a smaller, masked copy of a production database for development, with a referentially consistent subset, deterministic masking of personal data and a repeatable refresh.
- Data backfill track
Runs a production data backfill in gated steps, from scope and a correctness check to an idempotent batched script, a sample dry run, a throttled tracked run and reconciliation.
- Database administrator
Acts as a production DBA focused on data integrity, backups that restore, safe schema changes, query plans, capacity and least-privilege access. Use for Postgres, MySQL or similar in production.
- Database migration rules
Standing rules for schema migrations an assistant writes, keeping them backward compatible, reversible, lock-aware, batched for data changes and tested on realistic data sizes.
- Debug a failed pipeline run
Finds why a scheduled data pipeline run failed or produced wrong data, using logs, row counts and recent upstream changes, then gives a safe plan to fix, rerun and check downstream impact.
- Decide between JSON and real columns
Decides which fields belong in a JSON or JSONB column and which need real columns, with validation, GIN or expression indexes, query examples and how to promote a JSON field to a column later.
- Design change data capture
Designs log-based change data capture from an operational database to a warehouse, search index or cache, covering snapshot and stream, ordering, deletes, schema changes, outbox and lag.
- Design date and time storage
Decides how a database should store each date and time field - instant, local time with zone, plain date or duration - and plans fixing existing columns with mixed or missing time zones.
- Design a double-entry ledger schema
Designs a double-entry ledger for balances and money movement with immutable entries that always balance, currency and decimal handling, idempotency, balance queries and reconciliation.
- Design a DynamoDB table
Designs a DynamoDB single-table or multi-table model from listed access patterns with partition and sort keys, GSIs, item examples, hot-partition avoidance and cost notes.
- Design a Firestore data model
Designs Firestore collections, documents and subcollections from the app's screens, with denormalised fields, composite indexes, a structure that security rules can check, and read-cost estimates.
- Design a lakehouse table layout
Designs Iceberg, Delta Lake or Hudi table layout from real query patterns, covering partitioning, clustering, file sizes, compaction, snapshot expiry, merge strategy and cost of small files.
- Design medallion data layers
Defines bronze, silver and gold layers (or raw, clean, curated) for a lakehouse or warehouse, with what each layer guarantees, who owns it, retention, access and the rules for promoting data.
- Design a MongoDB schema
Designs a MongoDB document model from the app's access patterns, choosing embed versus reference, bounding arrays, and defining indexes, schema validation and example documents.
- Design an on-device database
Designs a local database for a mobile or desktop app on SQLite, Room, Core Data or similar, with schema, list-screen indexes, migrations that never lose user data, encryption and sync scope.
- Design Redis keys and data structures
Designs Redis keys and data structures for sessions, counters, leaderboards, rate limits, queues or caches, with naming, TTLs, atomic operations, memory estimates and eviction policy.
- Design a save game format
Designs a game save format covering what state to persist, versioning and migration of old saves, atomic writes and checksums against corruption, cloud-save conflicts and per-platform size limits.
- Design a search index
Designs a search index in Elasticsearch, OpenSearch or Postgres full-text, with mappings, analysers, relevance tuning and a reindexing plan. Use when adding search or fixing poor results.
- Design a temporal history table
Designs versioned history for prices, contracts or similar records with valid-from and valid-to rows or system-versioned tables, as-of queries, corrections and constraints against gaps.
- Design a time-series schema
Designs storage for sensor, IoT or metrics data, covering wide versus narrow tables, time partitions, retention, downsampling, late points, cardinality and the queries it must serve.
- Enforce data rules with database constraints
Moves business rules that only live in app validation into NOT NULL, CHECK, unique and partial unique constraints, checking existing rows first and adding them without long locks.
- Export a large dataset from a database
Exports a large table or query result to CSV or Parquet by streaming in key-ordered chunks from a consistent snapshot, with explicit types, checksums and a manifest the receiver can verify.
- Find the worst queries in a database workload
Ranks the queries costing the most from pg_stat_statements, Performance Schema or a slow query log by total time, calls and I/O, and picks the few worth fixing first.
- Fix Postgres table bloat and vacuum
Diagnoses Postgres table and index bloat and lagging autovacuum from stats and settings, finds blockers such as long transactions or replication slots, and gives tuning and safe cleanup.
- Implement an incremental extract
Implements incremental extraction from a database or API with a high-water mark, overlap window, tie-breaker, delete detection and safe replay, when log-based CDC is not available.
- Implement a type 2 slowly changing dimension
Implements a type 2 slowly changing dimension with dbt snapshots or MERGE SQL, with change detection, valid-from and valid-to dates, a current flag, deletes and correct point-in-time joins.
- Implement user data deletion
Implements account and personal-data deletion across a system with a data map, delete versus anonymise choices, backups, logs, audited jobs and processors. Flags legal questions.
- Load files into Snowflake
Loads files from cloud storage into Snowflake with a storage integration, stage, file format, COPY INTO or Snowpipe, error handling, deduplication and a load audit, as code.
- Make a data pipeline idempotent
Fixes an existing pipeline that double-counts or drops data on rerun by keying each run to its data interval and switching appends to partition overwrites or merges, then proves it with a rerun test.
- Map an undocumented legacy database
Reverse-engineers an undocumented database into an entity-relationship diagram with inferred relationships, dead tables, naming quirks, business meaning guesses and open questions.
- Model hierarchical data
Chooses and implements a relational model for trees such as categories, org charts, folders or threaded comments, comparing adjacency list, closure table, materialised path and nested sets.
- Model polymorphic associations
Models a relation that can point at several entity types, such as comments, attachments or likes, comparing polymorphic type-and-id columns, exclusive arcs and join tables, with DDL and queries.
- Move a spreadsheet to a database
Turns a business-critical spreadsheet into a small relational database, finding hidden entities, keys and cleaning rules, with an import script and a simple data entry path for non-developers.
- Plan data archival and purging
Plans archiving or purging old data with per-table retention, partitioning, throttled deletes, verified copies and a restore path. Use when tables grow without bound or retention rules apply.
- Plan database sharding
Decides whether a database really needs sharding, then picks the shard key from access patterns and plans routing, cross-shard queries, rebalancing, global ids and the migration path.
- Plan table partitioning
Decides whether and how to partition a large table, from the key in real queries and range, list or hash choice to partition size, index and constraint effects, upkeep and online migration.
- Prevent double booking and overselling
Stops two requests from claiming the same seat, room, time range or last unit of stock, using exclusion or unique constraints, conditional updates or row locks, proven with a concurrency test.
- Reduce BigQuery costs
Cuts BigQuery spend by finding the queries and tables that cost most, then fixing partitioning, clustering, select-star, repeated scans, materialisation, storage billing and the pricing model.
- Reduce Snowflake costs
Cuts Snowflake spend using metering and query history, with warehouse right-sizing, auto-suspend, workload separation, query fixes, clustering and storage review, and resource monitors.
- Remove duplicate rows from a database table
Finds exact-key duplicate rows in a table, repoints foreign keys to one survivor, deletes the rest in batches and adds the unique constraint that stops them coming back.
- Resolve database deadlocks
Diagnoses deadlocks and lock waits from database logs or lock graphs, names the transactions and lock order involved, and fixes them with consistent ordering, shorter transactions, indexes or retries.
- Review an event schema change
Reviews a change to an Avro, Protobuf or JSON Schema event or message schema for backward and forward compatibility with existing consumers, stored history and replays, and proposes a safe rollout.
- Set up database monitoring and alerts
Sets up dashboards and alerts for Postgres or MySQL covering saturation, slow queries, locks, long transactions, replication lag, disk growth and backup age, with runbook notes.
- Set up a dbt project
Sets up a new dbt project with staging, intermediate and mart layers, naming and folder conventions, sources with freshness, packages, environment targets and slim CI that builds only changed models.
- Size Elasticsearch shards and indices
Plans Elasticsearch or OpenSearch shard counts, replicas, rollover and index lifecycle from data volume, retention and query load, and fixes oversharded or overloaded clusters.
- Tune MySQL configuration
Sets MySQL or MariaDB InnoDB buffer pool, redo log, flush, connection and temp table settings from instance size and workload, with each change's reason, restart needs and how to verify it.
- Tune Postgres configuration
Sets Postgres memory, WAL, checkpoint, autovacuum, planner and connection settings from instance size and workload, with the reason for each change, restart needs and how to verify it.
- Tune a slow Spark job
Diagnoses a slow, failing or expensive Spark job from the Spark UI, query plans and logs, then fixes skew, oversized shuffles, spills, small files and executor sizing one measured change at a time.
- Validate a data migration
Verifies data moved correctly between an old and new store with row counts, per-column aggregates, hashed record diffs, sampled checks and business invariants, ending in pass or fail.
- Write a complex ActiveRecord query
Writes a correct Rails ActiveRecord query with joins, includes, group, subqueries and Arel only where needed, then checks the SQL it generates with to_sql and the query count.
- Write an Airflow DAG
Writes an Apache Airflow DAG with idempotent tasks keyed on the data interval, sensible retries, catchup and backfill behaviour, sensors that do not hog workers, secrets in connections and a DAG test.
- Write an Alembic migration
Writes or fixes an Alembic migration by reviewing autogenerate output, turning drop-and-add into renames, setting server defaults, using batch mode on SQLite and merging branched heads.
- Write a Dagster or Prefect pipeline
Writes a Dagster asset graph or a Prefect flow with partitions or parameters, retries, resources or blocks for secrets, schedules, idempotent outputs and local tests, following the tool's own model.
- Write a data contract
Writes a data contract between the producer and consumers of a table, topic or feed, covering schema, meaning, keys, quality rules, freshness, ownership and change policy as reviewable YAML.
- Write a dbt incremental model
Writes or fixes a dbt incremental model with the right strategy for the warehouse, a unique key, a late-data lookback, schema change handling and a parity check against a full refresh.
- Write a Django data migration
Writes a Django migration that transforms existing rows with RunPython and historical models, in batches, with a reverse function and safe ordering around the schema migrations it depends on.
- Write a complex Django ORM query
Writes a correct Django ORM query with annotate, aggregate, Subquery, OuterRef, F and Q expressions, then prints and checks the SQL it generates and the number of queries it runs.
- Write a Drizzle ORM schema
Writes a Drizzle ORM schema with tables, relations, indexes and constraints for Postgres, MySQL or SQLite, generates and reviews migrations with drizzle-kit, and writes typed queries.
- Write an Entity Framework Core migration
Adds and reviews an EF Core migration from a model change, fixing drop-and-add renames, data loss warnings and seeding, then produces an idempotent SQL script or bundle for deployment.
- Write an Elasticsearch query
Writes an Elasticsearch or OpenSearch query DSL request with filters kept out of scoring, relevance clauses, aggregations and deep pagination, and explains why results rank as they do.
- Write a complex Laravel Eloquent query
Writes a correct Laravel Eloquent query with relationships, whereHas, withCount and withSum, subqueries and eager loading, then checks the SQL with toRawSql and counts the queries run.
- Write a Flyway or Liquibase migration
Writes a Flyway or Liquibase database migration with correct versioning, safe locking on large tables, repeatable scripts for views and procedures, rollback notes and validation in CI.
- Write Go database access with sqlc
Writes Go data access with sqlc and pgx or database/sql, with annotated SQL queries, generated types, transactions, context timeouts, NULL handling and tests against a real database.
- Write GORM models and queries in Go
Writes Go GORM models with associations, indexes and soft deletes, replaces AutoMigrate in production with versioned migrations, and writes queries that avoid zero-value and N+1 traps.
- Write JPA entity mappings
Writes JPA or Hibernate entities with correct associations, lazy fetch types, cascades, equals and hashCode, and an id strategy that avoids the common Hibernate performance and correctness traps.
- Write a MongoDB aggregation pipeline
Writes a MongoDB aggregation pipeline that answers a question, explains each stage, handles missing and array fields, and recommends indexes. Use when a query needs grouping, joins or reshaping.
- Write Mongoose schemas and models
Writes Mongoose schemas and models with validation, indexes, virtuals, middleware, lean queries and TypeScript types, avoiding defaults that skip validation or build indexes in production.
- Write a PostGIS query
Writes PostGIS queries for nearby search, containment and distance with the right geography or geometry type, SRID, units and a GiST index the query can actually use.
- Write a Postgres function or trigger
Writes a PL/pgSQL or SQL function or trigger with the right volatility, security settings, error handling, a migration and tests, and says when the logic belongs in the app instead.
- Write a Prisma schema
Writes or extends a Prisma schema with models, relations, indexes, enums and native types, then runs the right migrate workflow for development and for production deploys.
- Write a PySpark job
Writes a PySpark batch job with explicit input schemas, DataFrame transformations instead of Python UDFs, a deliberate join strategy, partitioned idempotent output and a small local test.
- Write row-level security policies
Writes and tests Postgres row-level security policies for tenant or user isolation, covering roles, USING and WITH CHECK clauses, bypass risks, indexes for policy filters and a test matrix.
- Write SQLAlchemy 2.0 models
Writes SQLAlchemy 2.0 typed models with Mapped columns, relationships, explicit loading strategies, constraints and session handling that avoids lazy-load and detached-instance traps.
- Write a SQL Server stored procedure
Writes a SQL Server stored procedure in T-SQL with typed parameters, set-based logic, TRY/CATCH with XACT_ABORT, transactions, safe dynamic SQL and a test script.
- Write TypeORM entities and queries
Writes TypeORM entities with relations, indexes and cascades, generates and reviews migrations instead of using synchronize, and writes repository or QueryBuilder queries that avoid N+1 loads.
- Write an upsert query
Writes a correct insert-or-update for Postgres, MySQL, SQLite or SQL Server with the unique key it relies on, the engine's exact syntax and how it behaves under concurrent writes.
Not: answering business questions with data (data-analysis domain).