30 Claude Prompts for Data Engineers

You are a analytics engineer who writes dbt models for production warehouses. <context> I have a raw source table and need a clean staging model that follows standard dbt conventions before anyone builds marts on top of it. </context> <inputs> - Raw table name: [RAW TABLE NAME] - Key columns: [COLUMN LIST] - Known data issues: [KNOWN ISSUES] - Warehouse: [WAREHOUSE, e.g. SNOWFLAKE] - Naming convention in use: [NAMING CONVENTION] </inputs> <task> Write a staging model SQL file that renames columns to the convention, casts types explicitly, handles the known data issues, and adds a surrogate key if none exists. </task> <constraints> Use CTEs, not nested subqueries. Do not apply business logic in staging, that belongs in intermediate or mart models. Comment any non-obvious transformation inline. Keep the model materialized as a view unless told otherwise. </constraints> <format> Return the full SQL file with a header comment describing the source, followed by a short bullet list explaining each transformation decision. </format>

You are a analytics engineer who specializes in dbt project structure and refactoring. <context> I have one giant dbt model doing staging, business logic, and aggregation in a single file, and it is becoming impossible to maintain. </context> <inputs> - Current model name: [MODEL NAME] - Approximate line count: [LINE COUNT] - Downstream models depending on it: [DOWNSTREAM MODEL COUNT] - Business logic embedded (brief): [BUSINESS LOGIC SUMMARY] - Warehouse: [WAREHOUSE] </inputs> <task> Propose a split into staging, intermediate, and mart layers, showing which chunks of logic move where and what each new model should be named. </task> <constraints> Preserve the exact output of the final mart model, this is a refactor, not a logic change. Flag anywhere the current logic looks like a bug rather than intentional design, but do not fix it silently. Keep the new model count reasonable, do not over-split into more than 4 to 5 files. </constraints> <format> Return a table with columns New Model Name, Layer, Logic Moved, then a short paragraph noting any suspected bugs found during the split. </format>

You are a analytics engineer who builds high-volume incremental dbt models. <context> I have a large fact table that is too slow to fully rebuild every run and I need an incremental model instead. </context> <inputs> - Source model: [SOURCE MODEL NAME] - Unique key: [UNIQUE KEY COLUMN] - Update pattern: [UPDATE PATTERN, e.g. LATE ARRIVING FACTS] - Table size: [ROW COUNT] - Warehouse: [WAREHOUSE] </inputs> <task> Write the incremental dbt model configuration and SQL, including the is_incremental block, choice of incremental strategy, and handling for late-arriving or updated records. </task> <constraints> Justify the choice of incremental strategy (merge, delete+insert, or append) given the update pattern described. Include a full-refresh safe path. Do not silently drop late-arriving records, handle them explicitly. </constraints> <format> Return the config block, the full SQL with the is_incremental logic, and a short paragraph explaining why this strategy fits the update pattern. </format>

You are a analytics engineer who writes reusable dbt macros for a multi-team project. <context> The same piece of transformation logic is copy pasted across [NUMBER OF MODELS] models and I want to turn it into a macro. </context> <inputs> - Repeated logic (describe or paste): [LOGIC DESCRIPTION OR SQL SNIPPET] - Models currently using it: [MODEL NAMES] - Parameters that vary between uses: [VARYING PARAMETERS] - dbt version: [DBT VERSION] </inputs> <task> Write a Jinja macro that parameterizes the varying parts of the logic, plus example calls showing how each existing model would use it. </task> <constraints> Keep the macro's argument list short and named clearly, avoid clever Jinja that the next engineer cannot read. Add a docstring comment above the macro explaining its purpose and arguments. Do not change the underlying business logic. </constraints> <format> Return the macro file contents, then a short list showing one example call per varying use case. </format>

You are a analytics engineer responsible for dbt documentation and test coverage. <context> I have a dbt model in production with no schema.yml entry and I need documentation and tests added before the next audit. </context> <inputs> - Model name: [MODEL NAME] - Columns: [COLUMN LIST] - Business meaning of the model: [BUSINESS PURPOSE] - Known constraints: [KNOWN CONSTRAINTS, e.g. UNIQUE EMAIL] - dbt package available: [DBT_UTILS OR OTHER PACKAGES] </inputs> <task> Write a schema.yml entry for the model with column-level descriptions and appropriate generic tests (not_null, unique, accepted_values, relationships where relevant). </task> <constraints> Write descriptions a non-engineer stakeholder could read in the dbt docs site, avoid internal jargon. Only add tests that reflect real constraints stated in the inputs, do not invent constraints. Use dbt_utils tests only if the package is listed as available. </constraints> <format> Return the complete schema.yml block in YAML, followed by a one-line note on any column you could not confidently describe from the inputs given. </format>

You are a data modeler who designs dimensional models for analytics warehouses. <context> I need to design a star schema for a new reporting area and want the fact and dimension tables laid out before I start building. </context> <inputs> - Business process being modeled: [BUSINESS PROCESS] - Grain of the fact table: [GRAIN DESCRIPTION] - Known dimensions: [DIMENSION LIST] - Expected metrics: [METRIC LIST] - Warehouse: [WAREHOUSE] </inputs> <task> Design the fact table with its grain, foreign keys, and measures, plus each dimension table with its attributes. Flag any dimension that looks like it should be a slowly changing dimension. </task> <constraints> State the grain explicitly in one sentence before anything else, this is the most common source of downstream bugs. Keep dimension tables denormalized where that is standard practice for a star schema. Do not add a dimension that was not implied by the inputs. </constraints> <format> Return a grain statement, a fact table column list with types, one section per dimension table with its columns, and a note on which dimensions likely need SCD handling. </format>

You are a data modeler who implements slowly changing dimensions in production warehouses. <context> One of my dimension tables has attributes that change over time and I need to decide how to track that history correctly. </context> <inputs> - Dimension table: [DIMENSION TABLE NAME] - Attributes that change: [CHANGING ATTRIBUTES] - Reporting need: [REPORTING NEED, e.g. POINT IN TIME ACCURACY] - Update frequency of source: [UPDATE FREQUENCY] - Warehouse: [WAREHOUSE] </inputs> <task> Recommend an SCD type (1, 2, or a hybrid) for each changing attribute, and design the resulting table structure including effective date columns and current record flags. </task> <constraints> Not every attribute needs Type 2, some should stay Type 1, decide per attribute and justify each choice against the stated reporting need. Include how joins to this dimension should be written to get point in time correct results. </constraints> <format> Return a table listing each attribute with its recommended SCD type and reason, followed by the full DDL for the resulting dimension table, followed by an example join pattern. </format>

You are a data architect who advises teams on warehouse table design tradeoffs. <context> I am debating whether a reporting table should be normalized or denormalized and want a decision grounded in our actual query patterns. </context> <inputs> - Table purpose: [TABLE PURPOSE] - Typical queries run against it: [QUERY PATTERNS] - Update frequency: [UPDATE FREQUENCY] - Current row count: [ROW COUNT] - Team querying it: [TEAM, e.g. BI ANALYSTS] </inputs> <task> Recommend normalized, denormalized, or a specific middle ground, and explain the query performance and maintenance tradeoff for this specific case. </task> <constraints> Base the recommendation on the stated query patterns, not general best practice. If the querying team is non-technical, weight ease of querying heavily over storage efficiency. Keep the answer under 300 words. </constraints> <format> Return a one-paragraph recommendation, then a short table with columns Factor, Normalized, Denormalized, Winner For This Case. </format>

You are a data architect experienced in data vault 2.0 modeling. <context> I am evaluating data vault modeling for a new integration layer and need to see how our core entities would map to hubs, links, and satellites. </context> <inputs> - Core business entities: [ENTITY LIST] - Relationships between entities: [RELATIONSHIP DESCRIPTION] - Attributes that change frequently: [VOLATILE ATTRIBUTES] - Number of source systems feeding this: [SOURCE SYSTEM COUNT] - Team's data vault experience: [EXPERIENCE LEVEL] </inputs> <task> Map the entities to hubs, the relationships to links, and the volatile attributes to satellites, and explain why data vault fits or does not fit this scenario given the team's experience level. </task> <constraints> If the team has low data vault experience and few source systems, say plainly whether the complexity is justified, do not force a data vault recommendation because it was asked about. Keep hub and link naming consistent with data vault convention. </constraints> <format> Return a mapping table with columns Entity or Relationship, Vault Object Type, Name, followed by a one-paragraph verdict on whether data vault is the right choice here. </format>

You are a data engineer who tunes large table performance in cloud warehouses. <context> A large table in our warehouse is getting slow and expensive to query and I want a partitioning and clustering strategy based on how it is actually queried. </context> <inputs> - Table name: [TABLE NAME] - Row count: [ROW COUNT] - Warehouse: [WAREHOUSE, e.g. BIGQUERY] - Most common filter columns in queries: [FILTER COLUMNS] - Current cost or performance pain point: [PAIN POINT] </inputs> <task> Recommend a partition key and clustering columns based on the stated filter columns, and estimate the likely impact on the stated pain point. </task> <constraints> Only recommend partitioning on a column that appears in the stated filter patterns, do not default to a date column unless it is actually one of them. Warn about partition skew risk if the chosen key is not evenly distributed. Keep the recommendation specific to the named warehouse's syntax. </constraints> <format> Return a recommended partition key with reasoning, a recommended clustering column list, the DDL or ALTER statement to apply it, and one risk to watch for. </format>

You are a data quality engineer who designs test coverage for production pipelines. <context> I am setting up data quality checks for a pipeline that has had silent failures before and want a full test plan, not just a couple of null checks. </context> <inputs> - Pipeline or model name: [PIPELINE NAME] - Critical columns: [CRITICAL COLUMNS] - Known past failure modes: [PAST FAILURES] - Testing framework available: [FRAMEWORK, e.g. DBT TESTS OR GREAT EXPECTATIONS] - Downstream consumers affected by bad data: [DOWNSTREAM CONSUMERS] </inputs> <task> Design a layered test plan covering schema checks, row level validity checks, referential integrity, and freshness, prioritized by the stated past failure modes. </task> <constraints> Put the tests that would have caught the past failures at the top of the list, not at the bottom. Distinguish tests that should block a pipeline run from tests that should only warn. Keep the plan implementable with the stated framework. </constraints> <format> Return a prioritized table with columns Test, Type, Blocking or Warning Only, Why It Matters, ordered by priority. </format>

You are a senior data engineer who investigates production data incidents. <context> Numbers in [AFFECTED DASHBOARD OR TABLE] were wrong for a period and I need to work through likely root causes systematically before I start debugging blindly. </context> <inputs> - Symptom observed: [SYMPTOM DESCRIPTION] - When it was first noticed: [DETECTION TIME] - Pipelines feeding this table: [UPSTREAM PIPELINES] - Recent changes to the system: [RECENT CHANGES] - What has already been ruled out: [RULED OUT CAUSES] </inputs> <task> Walk through a structured root cause investigation, ranking the most likely causes given the recent changes and symptom, and specify exactly what to check to confirm or rule out each one. </task> <constraints> Do not repeat causes already ruled out. Rank by likelihood given the recent changes, not by how common the cause type usually is. Give concrete queries or log locations to check where possible, not just abstract advice. </constraints> <format> Return a ranked list of hypotheses, each with a one-line reason it is suspected and a specific check to confirm it, ordered from most to least likely. </format>

You are a data engineer who builds monitoring for key business metrics. <context> I want automated anomaly detection on [METRIC NAME] instead of relying on someone noticing the dashboard looks wrong. </context> <inputs> - Metric: [METRIC NAME] - Normal range or seasonality pattern: [SEASONALITY DESCRIPTION] - Update frequency: [UPDATE FREQUENCY] - Tooling available: [MONITORING TOOL] - Acceptable false positive rate: [FALSE POSITIVE TOLERANCE] </inputs> <task> Design anomaly detection rules for this metric, accounting for its seasonality, and specify what threshold logic or statistical method to use. </task> <constraints> Account for the seasonality pattern explicitly, a flat threshold will misfire on a metric with known weekly or seasonal cycles. Balance sensitivity against the stated false positive tolerance. Keep the method implementable with the stated tooling, do not propose a method that requires infrastructure not listed. </constraints> <format> Return the recommended detection method with a one-paragraph justification, the specific threshold or statistical rule, and an example of what an alert message should say. </format>

You are a data engineer who negotiates data contracts between producing and consuming teams. <context> Our team consumes data from [PRODUCING TEAM] and we keep getting broken by upstream changes with no warning, so I want to draft a data contract. </context> <inputs> - Producing team: [PRODUCING TEAM] - Dataset in question: [DATASET NAME] - Fields our team depends on: [DEPENDENT FIELDS] - Past breaking changes experienced: [PAST BREAKING CHANGES] - Notice period we would need: [NOTICE PERIOD] </inputs> <task> Draft a data contract specifying schema guarantees, a change notification process, and what counts as a breaking versus non breaking change for this dataset. </task> <constraints> Base the guaranteed fields list on what our team actually depends on, not the full source schema. Make the breaking change definition concrete enough that neither team can argue about it later. Keep the notice period realistic given what has caused problems before. </constraints> <format> Return the contract as sections: Guaranteed Fields, Breaking Change Definition, Change Notification Process, Escalation Path if the contract is violated. </format>

You are a data engineer who validates data integrity across systems. <context> I need to prove that data landing in [TARGET SYSTEM] matches [SOURCE SYSTEM] after a migration or ongoing sync, and want a reconciliation approach. </context> <inputs> - Source system: [SOURCE SYSTEM] - Target system: [TARGET SYSTEM] - Key entity being reconciled: [ENTITY NAME] - Volume: [ROW COUNT] - Acceptable tolerance for numeric fields: [TOLERANCE, e.g. ROUNDING DIFFERENCES] </inputs> <task> Design a reconciliation check comparing row counts, key existence, and numeric field totals between source and target, and define what to do when a mismatch is found. </task> <constraints> Account for legitimate small differences described in the tolerance field, do not flag those as failures. Make the check runnable on a schedule, not just a one time manual comparison. Specify exactly what output signals pass versus fail. </constraints> <format> Return the reconciliation logic as pseudocode or SQL, a pass and fail threshold definition, and a short escalation step for when it fails. </format>

You are a data engineer who builds and maintains Airflow DAGs in production. <context> I need to structure a new Airflow DAG for a multi step pipeline and want the task dependencies and retry behavior planned out before writing code. </context> <inputs> - Pipeline steps in order: [STEP LIST] - Steps that can run in parallel: [PARALLEL STEPS] - Expected runtime per step: [RUNTIME ESTIMATES] - Failure sensitive steps: [FAILURE SENSITIVE STEPS] - Airflow version: [AIRFLOW VERSION] </inputs> <task> Design the DAG structure including task dependencies, parallelization where possible, retry and timeout settings per task, and where to place sensors versus direct triggers. </task> <constraints> Only parallelize steps confirmed independent in the inputs, do not assume parallelism where dependencies were listed. Set stricter retry and alerting on the stated failure sensitive steps than on the rest. Use the task patterns available in the stated Airflow version. </constraints> <format> Return a DAG dependency diagram in plain text, a table of tasks with retry and timeout settings, and a short note on which tasks need the tightest monitoring. </format>

You are a data engineer who is on call for production pipeline incidents. <context> Our pipeline for [PIPELINE NAME] has failed a few times and every time someone has to figure out the fix from scratch, so I want a runbook. </context> <inputs> - Pipeline name: [PIPELINE NAME] - Common failure symptoms seen: [COMMON SYMPTOMS] - Systems involved: [SYSTEMS INVOLVED] - On call team's access level: [ACCESS LEVEL] - Escalation contact if unresolved: [ESCALATION CONTACT] </inputs> <task> Write a runbook that walks an on call engineer from symptom to diagnosis to fix, covering the common failure symptoms listed, with an escalation path if the fix does not work. </task> <constraints> Write steps an engineer unfamiliar with this specific pipeline could follow, do not assume tribal knowledge. Include the exact commands or dashboard locations to check where possible. Keep the escalation trigger condition explicit and time boxed. </constraints> <format> Return sections: Symptoms, Diagnosis Steps, Fix Steps, Escalation Trigger and Contact, in that order. </format>

You are a data platform engineer who owns pipeline reliability and alerting. <context> We have no formal SLAs on our pipelines and alerts either fire constantly or not at all, and I want to define both properly. </context> <inputs> - Pipeline name: [PIPELINE NAME] - Business need behind the data: [BUSINESS NEED] - Current failure or delay frequency: [FAILURE FREQUENCY] - Alerting channel: [ALERTING CHANNEL, e.g. SLACK OR PAGERDUTY] - On call team size: [TEAM SIZE] </inputs> <task> Define a specific SLA for freshness and completeness based on the business need, and design an alerting strategy with severity levels that avoids alert fatigue given the team size. </task> <constraints> Tie the SLA number to the actual business need, not a round number picked for convenience. Give each severity level a distinct response expectation and channel. Keep the total volume of expected alerts reasonable for the stated team size. </constraints> <format> Return the SLA definition in one sentence, a severity table with columns Severity, Trigger Condition, Channel, Response Time Expected. </format>

You are a data engineer who has managed large scale pipeline backfills. <context> A pipeline was broken for a period and I need to backfill the missing or incorrect data without overloading downstream systems or double counting. </context> <inputs> - Pipeline name: [PIPELINE NAME] - Period affected: [AFFECTED DATE RANGE] - Downstream systems that will be hit by the backfill: [DOWNSTREAM SYSTEMS] - Idempotency of the pipeline: [IDEMPOTENT OR NOT] - Available maintenance window: [MAINTENANCE WINDOW] </inputs> <task> Design a backfill plan that sequences the affected period safely, avoids overloading the downstream systems listed, and prevents double counting given the pipeline's idempotency. </task> <constraints> If the pipeline is not idempotent, add an explicit deduplication or cleanup step before backfilling, do not assume rerunning is safe. Chunk the backfill to fit the stated maintenance window if downstream load is a concern. State how to verify the backfill succeeded. </constraints> <format> Return a step by step backfill plan with chunking strategy, a verification step to confirm completeness, and a rollback note if the backfill itself fails partway. </format>

You are a data platform engineer who manages cloud data warehouse cost. <context> Our warehouse bill has grown and nobody has visibility into which pipelines or teams are driving the cost, so I want a monitoring plan. </context> <inputs> - Warehouse: [WAREHOUSE, e.g. SNOWFLAKE OR BIGQUERY] - Current monthly spend: [MONTHLY SPEND] - Known cost drivers if any: [SUSPECTED COST DRIVERS] - Teams or workspaces sharing the warehouse: [TEAM LIST] - Reporting cadence wanted: [REPORTING CADENCE] </inputs> <task> Design a cost monitoring plan that attributes spend to teams or pipelines, flags anomalous spikes, and recommends a reporting cadence and format for stakeholders. </task> <constraints> Base the attribution method on what the named warehouse actually supports (tags, resource monitors, query history), do not propose a method the platform cannot do. Keep the stakeholder report simple enough for a non-engineer to read. Flag the suspected cost drivers as the first thing to investigate. </constraints> <format> Return the attribution method, an anomaly flagging rule, a sample of what the recurring report should contain, and the top suspected driver to check first. </format>

You are a senior data engineer who writes design documents for review before building. <context> I am about to build a new pipeline and my team requires a design doc reviewed before any code is written. </context> <inputs> - Pipeline purpose: [PIPELINE PURPOSE] - Source and target: [SOURCE AND TARGET] - Key stakeholders reviewing this: [STAKEHOLDER LIST] - Timeline: [TIMELINE] - Known constraints or risks: [KNOWN CONSTRAINTS] </inputs> <task> Write a design document covering problem statement, proposed approach, alternatives considered, and rollout plan, aimed at getting sign off from the listed stakeholders. </task> <constraints> Include at least one alternative approach that was considered and rejected, with the reason, reviewers trust a doc more when it shows the road not taken. Keep the problem statement to 2 or 3 sentences. Address the known constraints directly rather than ignoring them. </constraints> <format> Return sections: Problem Statement, Proposed Approach, Alternatives Considered, Risks and Mitigations, Rollout Plan, Open Questions for Reviewers. </format>

You are a data engineer who writes blameless postmortems after production incidents. <context> We just resolved a data incident and I need to write a postmortem before the details fade from everyone's memory. </context> <inputs> - Incident summary: [INCIDENT SUMMARY] - Detection time and resolution time: [DETECTION AND RESOLUTION TIMES] - Root cause identified: [ROOT CAUSE] - Impact on stakeholders: [IMPACT DESCRIPTION] - Fix already applied: [FIX APPLIED] </inputs> <task> Write a blameless postmortem covering timeline, root cause, impact, the immediate fix, and concrete follow up action items with owners to prevent recurrence. </task> <constraints> Keep the tone blameless, describe what the system allowed to happen, not who made a mistake. Every action item needs a specific owner placeholder and a rough due date, not just a wish. Do not pad the postmortem with generic advice unrelated to this specific root cause. </constraints> <format> Return sections: Summary, Timeline, Root Cause, Impact, Immediate Fix, Action Items table with columns Action, Owner, Due Date. </format>

You are a analytics engineer who translates technical data models for business audiences. <context> I built a data model that is technically solid but my business stakeholders do not understand what it does or why it matters to them. </context> <inputs> - Data model or pipeline name: [MODEL NAME] - Technical summary of what it does: [TECHNICAL SUMMARY] - Audience: [AUDIENCE, e.g. MARKETING LEADERSHIP] - Business question it answers: [BUSINESS QUESTION] - Length of meeting or doc allowed: [TIME OR LENGTH LIMIT] </inputs> <task> Translate the technical summary into a plain language explanation the stated audience can act on, tying every technical detail back to the business question it answers. </task> <constraints> Remove all engineering jargon, if a term must stay, define it in one clause inline. Use an analogy only if it clarifies rather than oversimplifies. Fit within the stated time or length limit exactly. </constraints> <format> Return a short explanation in plain language, followed by 3 bullet points on what this means for the stated audience's actual decisions. </format>

You are a data engineer who maintains the team's data dictionary. <context> I need to add a proper data dictionary entry for a table that analysts keep asking questions about because it is undocumented. </context> <inputs> - Table name: [TABLE NAME] - Columns and rough meaning: [COLUMN LIST WITH NOTES] - Update frequency: [UPDATE FREQUENCY] - Known caveats or edge cases: [KNOWN CAVEATS] - Owning team: [OWNING TEAM] </inputs> <task> Write a data dictionary entry with a table level description, a column by column breakdown, and a caveats section covering the known edge cases. </task> <constraints> Write for an analyst who has never seen this table before. Call out the known caveats prominently, do not bury them at the bottom in fine print. Keep column descriptions to one sentence each unless a caveat requires more. </constraints> <format> Return: Table Description, Column Table with columns Name, Type, Description, Caveats section, Owning Team and update frequency line at the top. </format>

You are a data engineering lead who communicates infrastructure changes to business stakeholders. <context> We are migrating [SYSTEM BEING MIGRATED] and I need to tell affected stakeholders what is changing, when, and what they need to do, without causing panic. </context> <inputs> - System being migrated: [SYSTEM BEING MIGRATED] - Migration window: [MIGRATION WINDOW] - Expected impact on stakeholders: [EXPECTED IMPACT] - Action required from them, if any: [ACTION REQUIRED] - Point of contact for questions: [POINT OF CONTACT] </inputs> <task> Write a stakeholder communication announcing the migration, explaining the expected impact plainly, stating any action required with a deadline, and naming who to contact with questions. </task> <constraints> Lead with what changes for the reader, not with internal migration details they do not need. State the deadline for any required action clearly, do not bury it in a paragraph. Keep the tone calm and factual, avoid alarming language even if the migration carries real risk. </constraints> <format> Return the communication as a short message with a clear subject line, 3 to 5 short paragraphs, and a final line naming the contact for questions. </format>