Analytics Engineer: data models, tests and maintenance

An Analytics Engineer helps make prepared data reusable. When several reports depend on orders, someone needs to look after shared definitions, reliable joins, tests and a manageable way to introduce changes. That is the work I want to explain here. It becomes easier to understand when you follow a model beyond its first successful run and consider who maintains it afterwards.

Job descriptions may use Analytics Engineer or Data Analytics Engineer. I look at the responsibilities first. One company combines this work with analysis and dashboards, while another creates a specialist team. Boundaries between analysts, data engineers and model owners depend on the organisation. A job title alone tells you surprisingly little about what an ordinary Tuesday will involve.

What does an Analytics Engineer deliver?

A useful deliverable is a dataset that another person can find, understand and use for an agreed purpose. Its grain, quality rules and owner are clear. Readers know where it comes from, when it updates and what it cannot answer. Transformation code belongs to that deliverable alongside documentation and the process for maintaining it.

dbt Labs describes analytics engineering through transforming, testing, deploying and documenting data, supported by practices such as version control. That is a useful description of the work, although it comes from a tool provider and does not establish universal job boundaries. See the dbt Labs explanation of analytics engineering.

I would not assume that an Analytics Engineer never builds a warehouse or a dashboard. A small team may put several responsibilities in one role. A larger organisation needs clearer agreements between teams. Data engineers also need business context, and analysts can write strong tests and maintain models. These abilities do not belong exclusively to one title.

To understand a position, ask who owns the shared order table, who approves a definition change and who responds when data arrives late. Those answers reveal the practical scope better than a long technology list. My comparison of Data Analyst, Business Analyst and BI Developer provides another way to examine neighbouring responsibilities without treating titles as rigid borders.

A project example: a shared order model

Imagine a synthetic retail project. Sales wants orders by country, marketing analyses acquisition channels, and customer support examines returns. Each team has been joining order and customer exports independently. Different status filters and missing-value rules have appeared. The project will provide an agreed starting model for these analyses so that repeated preparation becomes easier to inspect and maintain.

I begin by asking which elements are actually shared. Order identifiers and creation dates can belong in the base model. Marketing attribution needs a separate rule, while a return may be a distinct event with its own date. A common table does not need to contain every measure someone might eventually put on a chart. Its scope should remain understandable.

Suppose we agree on one row per order and a separate model for order items. This helps prevent order amounts being repeated when several products are joined. Customer country needs another decision: current country or country at purchase? Both may be useful, but they deserve clearly different names. I would not leave that choice to an accidental join in the next report.

This is a design exercise, not a claim about a completed company implementation. I have not measured a runtime improvement or cost saving here. The purpose is to follow decisions you can investigate in your own environment. That distinction makes it easier to separate useful design reasoning from promises that a tool will automatically organise every dataset.

Agree on what the data means

Before writing transformations, I describe the row, the included records and the intended consumers. The order model needs a key, status rules, an amount unit and a policy for missing information. If orders without recognised customers must remain visible, that belongs in the definition. The decision needs to survive a change in the person writing the SQL.

Current state and event history also need separate treatment. If the source overwrites order status, a later export can change the answer for a previous month. A clever column name cannot reconstruct information that was never retained. We need suitable source history or an explicit agreement that the report shows the state known at refresh time.

A useful definition also limits the model’s use. Store orders may support operational analysis while lacking information needed for financial reporting. Missing returns or tax details matter to the consumer. I would record those limits beside the model before someone gives an amount total a name that implies a broader meaning than the inputs support.

During review, I return to this agreement. A requirement that changes the row grain deserves more attention than a routine column edit. Consumers may already have joins and counts based on the previous assumption. Explain the effect, the migration path and how long the earlier version will remain available. Technical correctness does not remove the need to coordinate a change in meaning.

Divide transformations by responsibility

In our example, the stage closest to the source standardises names, types and basic conventions. Another stage combines records using agreed keys. The consumer-facing model then has a defined grain and description. This separation helps locate a problem without searching through several reports that independently repeat the same preparation step with slightly different assumptions.

I would not create a model for every line of SQL. Separation should improve understanding or reuse. If two stages always change together and have no independent meaning, an extra layer may just make the project harder to read. Model names should explain their purpose without requiring readers to learn the author’s private collection of abbreviations.

Dependencies matter when a source changes too. If the country column disappears, we need to identify affected models and reports. In dbt, declared sources and references help represent these relationships; the source documentation explains the mechanism. A dependency graph still needs meaningful names and maintained descriptions to be useful to the people investigating a change.

If joins are still unfamiliar, practise with a small dataset first. In SQL from question to verified result, I show how adding order items changes an amount total. A model management tool does not remove that issue. You still need to understand what happens to each record before trusting the resulting measure.

Choose tests for specific possible mistakes

For an order model, I check key uniqueness and completeness. I examine allowed statuses and customer relationships, accounting for deliberately permitted exceptions. A business rule may need its own check when basic tests do not cover it. Each test should have a clear answer to two questions: which mistake will it detect, and what does failure require us to do?

dbt includes data tests for uniqueness, missing values, allowed values and relationships. Tests can identify records that violate an assertion, as explained in the data test documentation. Passing checks establish the assertions that were actually written and executed. They cannot confirm every possible property of the model or resolve an incorrect business definition automatically.

A control scenario could contain two orders from the same customer, an order without a customer and an order with a missing amount. Add a duplicate order identifier separately. Before executing the transformation, record the expected output and the expected failure. This gives the check more meaning than simply confirming that the code returned something without stopping.

Decide how the process should respond as well. A duplicate key may block publication of a model defined at order grain. An agreed category of missing information may require a visible note and a separate investigation. Thresholds should follow the use of the data. Copying them from an unrelated project can produce alarms that nobody understands or takes responsibility for resolving.

Understand the scope of a technical contract

A business agreement about a model is broader than a tool’s contract mechanism. A dbt contract can check model shape, including column names and types. Constraint support and enforcement depend on the platform and materialisation. Consult the dbt constraints documentation for the environment you use, rather than assuming every database enforces the same declarations.

A column can have the correct numeric type and still contain amounts in the wrong unit. A valid date may refer to payment when the reader expects order creation. That is why schema checks need definitions and examples beside them. Calling a mechanism a contract does not give it an understanding of every consumer’s intention or the business process behind a field.

Keys deserve particular care. A declaration does not always mean the engine will reject duplicates. Check what the database and configuration actually enforce. If uniqueness is necessary for a reliable join, the process needs a control that detects violations. Otherwise, downstream users may confidently multiply orders while believing an important assumption was already guaranteed upstream.

Freshness and completeness need separate checks

Suppose a report supports a morning meeting. Agree when data should be available, which period it covers and who notices a delay. A successful transformation is insufficient evidence. It may have recalculated an old, incomplete export correctly because the latest source delivery had not yet arrived. The pipeline’s success and the report’s readiness are different things to investigate.

A recent load timestamp also does not demonstrate completeness. One new delivery may have arrived while another region is still missing. Alongside timing, check expected delivery components and agreed counters. The freshness configuration documentation describes thresholds; choose configuration and commands appropriate to the dbt version used in the project.

An alert should identify the affected model, the problem, its impact and the person or team responsible for responding. One clear message about an unavailable source is more helpful than many identical reports from dependent dashboards. Include instructions for retrying, pausing publication and telling consumers that a result is incomplete. Detection needs a practical response path to become useful maintenance.

Review and deployment belong to the work

I prepare a model change on a separate repository branch and describe the problem, resulting behaviour and validation. A reviewer should be able to follow the reasoning without reconstructing every earlier project conversation. Showing one order before and after the change can explain the impact more effectively than listing all the files that were edited.

Before publishing, examine dependent reports. Renaming a column can immediately break a refresh. Changing its meaning while preserving the name is harder to notice because the chart may still render. Both cases need a plan for consumers and a check against the new agreed definition. A successful build does not establish that readers have migrated their interpretation.

Reverting a commit may not restore data that has already been recalculated or overwritten. A recovery plan should explain how to reconstruct the previous result and whether the required sources remain available. You can practise that locally with a copy of the exercise. In a real environment, the procedure depends on tools, permissions and the team’s responsibility for the published dataset.

Assess performance alongside maintenance cost

When a model runs slowly, I begin with measurement and the execution plan for the actual platform. Inspect data scanned, multiplied rows and expensive steps that run repeatedly. I would not promise a reduction from many minutes to seconds without testing comparable workloads. The practical objective is an appropriate cost and response time for the model’s real consumers.

Views, tables and incremental models have different consequences. A stored result may improve reads but requires refreshing. Incremental processing must account for historical corrections, late records and deletions. If the logic considers only new identifiers, an updated status on an older order may never reach the output. Design the update rule before evaluating the saving in processing work.

People’s time is another cost. A model that only its author understands can be expensive to maintain despite executing quickly. Documentation, stable names and small reviewable changes help with this problem. Match the solution to team needs and frequency of use. A single report does not automatically justify the largest architecture you could build around it.

Write documentation around consumer questions

For the order model, start with purpose and grain before describing individual columns. A reader needs to know whether counting rows is meaningful and how the table can be joined. Add an expected relationship and a known one-to-many caveat. This helps someone who understands SQL but has not yet learned the source system or its exceptions.

A column description should add information beyond the name. For a date, explain the event and time zone. For an amount, explain the unit and included components. For a status, describe its interpretation and source. If something remains unresolved, identify the limitation and the definition owner. Calling a field standard will not resolve two teams having different expectations of it.

Include an example question that the model cannot answer. If it contains current customer country, explicitly rule out reconstructing country history from that field. This helps prevent a technically valid report from making an unsupported claim. It also clarifies the next requirement: a new source, a historical model or a narrower interpretation of an existing analysis.

Review the documentation with the code when meaning changes. A separate file updated weeks later quickly loses value. Test the explanation by giving another reader just the description and a small result. Ask them to interpret a missing amount and an unknown country. Their answer can expose a specific passage that needs work before the model is offered to more users.

Try the work before choosing the title

Start with a project you can already calculate in SQL. Extract an order model, describe its grain and specify how a few exceptions should behave. Add version control, tests and instructions for running it. Ask someone else to answer a question using the documentation. Places where they need further explanation become concrete tasks for improving the model and its handover.

Next, simulate a change: a new status, an unmatched relationship or a late correction to an older record. Check whether your controls detect the issue and whether you can explain the effect on the report. This demonstrates maintenance thinking more clearly than a screenshot of a successful schedule. In a portfolio, describe the exercise’s limits and the checks you actually executed.

When reviewing a job description, look for ownership of integrations, models and incident response, as well as how much work involves direct analysis. Ask about code review, test environments and business definition owners. The tool list helps you choose what to learn, but these answers reveal more about daily working practices and the expected level of independence.

This direction may suit someone who enjoys organising recurring solutions, testing assumptions and helping other analysts use data. There is no obligation to pass through a role with this exact title, and learning one tool cannot guarantee employment. A useful next step is a small maintained model whose meaning, behaviour and limitations you can explain clearly to another person.