Artefact Software Engineer Interview Guide

The 60-second brief

Candidates describe Artefact as a data consulting and AI firm whose engineers work on client projects for large organisations. The Software Engineer role is described as a mix of software development and data engineering: building data products and machine learning pipelines, designing data architectures, deploying applications that process large datasets, working on cloud infrastructure, and writing custom software around a client's business problem. In practice that means the code you write sits next to data scientists, consultants and product managers, and the output is software a client will run, not an internal tool only your own team sees.

This guide is for Software Engineer candidates preparing for Artefact. It covers the five rounds candidates report (HR Screening Call, Live Coding Challenge, SQL Assessment, Data Architecture Case, Behavioral Interview), the Python, SQL, data modeling and ETL topics reported for them, and a behavioral section built around client-facing and deadline-driven stories. The role is described as Python-first with SQL and schema design, and with cloud platforms, Airflow and dbt listed as nice-to-have, so the plan spends most of its days on those.

Candidates report five rounds over roughly 4-6 weeks: an HR Screening Call, a Live Coding Challenge in Python, a SQL Assessment, a Data Architecture Case, and a Behavioral Interview with an engineering manager or team lead.

Focus areas

• Python
• SQL
• Data Architecture

Overview

The reported loop moves from a conversation about your background into three technical stages and ends with a behavioral interview. The technical stages follow the job description: Python algorithms (tree traversal, recursion versus iteration, complexity), SQL (joins, CTEs, window functions) and data design (OLTP versus OLAP schemas, star and snowflake models, ETL or ELT pipelines). Candidates describe the process as practical, with real-world coding, SQL and design scenarios rather than abstract puzzles.

Because the role mixes software engineering with data work, a strong preparation covers both halves with equal weight. On the software side, be able to write a tree longest-path function recursively, rewrite it iteratively with an explicit stack, state the time and space cost of each, and say what happens on an empty tree, a single node and a very deep chain. On the data side, be able to design a reporting schema for a retail or e-commerce scenario, justify a star or snowflake layout, write a ranking query with a window function, and outline how raw data lands in a warehouse and how you would check its quality.

The role description also stresses client engagements, so the last stage and many of your technical explanations should show how you communicate. Candidates report being asked to explain architectures to non-technical people, to describe delivering under a tight deadline with unclear requirements, to handle disagreement over architecture, and to say why you want to work at Artefact. Prepare those stories with the same discipline as the code: a specific project, your own decision, and a result you can describe honestly.

Candidates also report that some questions can fall outside the job posting. You cannot prepare content for that, but you can prepare a method: ask clarifying questions, state assumptions, break the problem into parts you do know, and reason from first principles while talking through it.

The Artefact interview process

The five rounds below are what candidates report, in the order they report them. The descriptions say what is reported and how to approach each stage.

HR Screening Call

Candidates report an initial call that covers your background, your career expectations and how you fit the company's structure. No technical content is reported for this stage. Treat it as the first test of your story: a short account of your experience, why a data consulting setting appeals to you, and what you are looking for next. Have practical details ready (availability, location, what you want from the role) so the call moves quickly, and ask what the remaining rounds cover so you can plan the weeks ahead.

What to demonstrate

• How your background lines up with a Python, SQL and data-focused engineering role
• Your career expectations and what you want from the next role
• How clearly you can describe your own experience

How to prepare

• Write a short walk-through of your CV that ends on why a consulting-style, client-facing engineering role fits you, and rehearse it aloud.
• List the projects where you wrote Python, SQL or data pipeline code so you can name them quickly when asked about your background.
• Prepare questions about the remaining four rounds and the format of each, and note the answers so you can target your practice.

Live Coding Challenge

Candidates report a live Python session where you solve problems in real time. Tree traversal for the longest path in a hierarchical dataset is the example reported for it, together with rewriting a recursive solution iteratively. Recursion versus iteration, DFS and BFS, and Big O analysis are the listed topics. Candidates are advised to think aloud and to practise without an IDE. Approach it by clarifying the input, writing the simplest correct version, then testing edge cases yourself before being asked.

What to demonstrate

• Working Python code produced live, since the format requires it
• Recursion and iteration, including converting one to the other
• Tree and graph traversal with DFS and BFS
• Your ability to state time and space complexity as you go

How to prepare

• Write the tree longest-path function recursively, then iteratively with an explicit stack, in a plain editor with no autocomplete, and run both on the same test trees.
• Practise stating complexity for both versions, including the extra stack space and what the CPython recursion limit does to a very deep chain.
• Practise narrating while you type: restate the problem, name your data structure, run a small example by hand, then list edge cases.

SQL Assessment

Candidates report a dedicated session on SQL skills. The reported SQL topics are joins, subqueries, common table expressions and window functions, with rolling customer retention from raw transaction logs given as an example scenario, and a query for top-performing products per category among the reported questions. Approach each problem by naming the grain of the table, deciding what one output row represents, and building the query in CTE steps you can explain one by one.

What to demonstrate

• Fluency with joins, subqueries and CTEs
• Use of window functions such as ranking within a partition
• Reasoning about query correctness on raw transactional data

How to prepare

• Write the top products per category query with a ranking window function, then rewrite it to handle ties differently and say how the output changes.
• Build a rolling retention query on a small transactions table: first-purchase cohort, activity by period, and the share still active.
• Check every query for one-to-many joins that inflate sums, and for NULLs in partition and order columns.

Data Architecture Case

Candidates report a business case that tests data architecture. The reported content is data modeling for transactional (OLTP) and analytical (OLAP) uses, star and snowflake designs, ETL or ELT pipeline structure, ingestion and cloud warehousing, with partitioning, indexing and slowly changing dimensions listed as less common. A retail warehouse tracking inventory and sales across regions is the example scenario. Approach it as a client conversation: ask about the reporting questions first, state the grain of your central table, then walk from source to warehouse.

What to demonstrate

• Schema design for reporting and analytics from a business scenario
• Choosing between star and snowflake models and justifying the choice
• Structuring an ETL or ELT pipeline and its quality checks

How to prepare

• Design a retail schema end to end: a sales fact table, dimension tables, and the three reports the business would run, then say where you denormalise and why.
• Outline a pipeline from raw files to warehouse tables: landing area, parsing, validation, load, and what happens to rows that fail.
• Prepare a short explanation of slowly changing dimensions, partitioning and indexing in terms of the queries they speed up and what they cost on write.

Behavioral Interview

Candidates report a final interview with an engineering manager or team lead covering cultural fit and behavioral topics. Reported areas are stakeholder management, adaptability and collaboration, including code reviews and mentoring junior developers. Candidates also report that technical questions slightly outside the job posting can come up, so keep a calm method ready. Approach it with specific stories where you made the decisions, and be ready to explain a technical idea to a non-technical listener.

What to demonstrate

• Communication with technical and non-technical people
• Adaptability under ambiguous requirements and changing priorities
• Collaboration within a team, including reviews and disagreements

How to prepare

• Write four stories in situation, decision, result form: a deadline with unclear requirements, learning a tool quickly, an architecture disagreement, and a client or stakeholder who wanted something conflicting.
• Practise explaining a pipeline or algorithm you built in plain language, with an analogy and no jargon.
• Write a specific answer to why Artefact, tied to the kind of client data work you have done or want to do.

Where candidates lose points

Writing a recursive longest-path function that returns the height of the tree, or only measures paths through the root

Track the best path seen at every node, which is the sum of the left and right depths there, and return only the node's own height to its parent. Test a tree whose longest path sits inside a subtree away from the root.

Having only a recursive solution and no answer when asked to make it iterative or to handle a very deep tree

Practise the explicit-stack version until you can write it without reference, including a post-order pattern. State that deep chains hit CPython's recursion limit, and give the space cost for both versions.

Using a ranking window function without deciding how ties are treated, or filtering on the rank in the same SELECT

Compare ROW_NUMBER, RANK and DENSE_RANK on a tied example before the interview. Aggregate and rank in a CTE, then filter the rank in an outer query, and apply the time period filter before ranking.

Jumping into tables for the data architecture case without establishing what reports the business needs or the grain of the fact table

Start by asking which questions the client wants answered and how fresh the data must be. State the grain of the central table, list dimensions, then choose star or snowflake and say what query it makes cheaper.

Giving behavioral answers that describe what the team did, with no personal decision, no client angle and no cost

For each story, write one sentence that begins with I decided, name the trade-off, and give the result. Include one story with a non-technical stakeholder, since the role is described as client-facing.

Questions & practice

Write a recursive function to traverse a tree and calculate the longest path between any two nodes.

Medium · Python Coding & Algorithms

Approach

1. Clarify the shape first: binary or n-ary tree, and whether length counts edges or nodes. The longest path between any two nodes is the tree's diameter, and it does not have to pass through the root.
2. Use one post-order DFS. The helper returns the depth of a node (None returns 0, a leaf returns 1). At each node, left_depth + right_depth is the longest path through that node in edges. Keep the maximum in a nonlocal variable and return 1 + max(left, right) to the parent.
3. For an n-ary tree, track only the two largest child depths at each node, so each node still costs O(number of children).
4. Complexity: O(n) time because each node is visited once, and O(h) call-stack space. The naive approach calls height() separately at every node, which costs O(n^2) on a skewed tree. Say that before giving the single-pass version.
5. Edge cases: an empty tree returns 0 and a single node returns 0 edges. A chain deeper than CPython's default recursion limit of 1000 raises RecursionError, which leads into the iterative version.

Follow-up

• Can you return the nodes on the longest path as well as its length?
• How does the solution change if edges carry non-negative weights?
• If the tree arrives as an edge list instead of node objects, can you solve it with two BFS passes?

Implement the same tree traversal and longest-path calculation using an iterative approach in Python.

Medium · Python Coding & Algorithms

Approach

1. Replace the call stack with an explicit post-order traversal. Push (node, False). When an unvisited entry is popped, push (node, True) and then its children. When a visited entry is popped, its children's depths are already computed, so combine them.
2. A simpler variant: collect nodes in preorder with a stack, then process the list in reverse. Every child comes after its parent in preorder, so in the reversed list each child is handled before its parent.
3. Store depths in a dict keyed by the node. Default Python objects hash by identity. At each node, set l = depth.get(left, 0), r = depth.get(right, 0), best = max(best, l + r) and depth[node] = 1 + max(l, r).
4. Complexity: O(n) time, plus O(n) heap memory for the depth map (and for the reversed list in the preorder variant). Depth is now limited by RAM rather than by the recursion limit.
5. Test it against the recursive version on random trees, an empty tree, a single node, and a degenerate chain of 100,000 nodes that the recursive version cannot handle.

Follow-up

• Can you cut the extra memory to O(h) by storing depth on the node or carrying it on the stack?
• Why not just call sys.setrecursionlimit and keep the recursive version?
• How would you do this with a BFS order for an n-ary tree stored as a parent array?

Explain the time and space complexity of your recursive versus iterative solutions.

Medium · Python Coding & Algorithms

Approach

1. Time is O(n) for both versions, because each node is pushed or called once and does constant work. Contrast this with recomputing height() at every node: O(n^2) on a skewed tree, O(n log n) on a balanced one.
2. Recursive space is O(h) stack frames: O(log n) on a balanced tree and O(n) on a chain. CPython does not eliminate tail calls, and the default limit is 1000 frames (sys.getrecursionlimit), after which RecursionError is raised.
3. Iterative space is an O(h) explicit stack for the visited-flag version, plus O(n) for the depth map unless depth is stored on the nodes. The preorder-then-reverse version holds all n nodes in a list. All of this lives on the heap, so it scales with available memory.
4. Constant factors: a Python function call costs more than a list append/pop, but the iterative version allocates tuples and does dict lookups. I would measure both with timeit rather than assume the iterative one is faster.

Follow-up

• What is the peak memory for a complete binary tree versus a linked-list shaped tree?
• What can go wrong if you raise the recursion limit to one million?
• Can you do the traversal with less than O(h) extra space, and what does that cost?

Describe how you would optimize a Python script that is experiencing memory bottlenecks during large-scale dat

Medium · Python Coding & Algorithms

Describe how you would optimize a Python script that is experiencing memory bottlenecks during large-scale data processing.

Approach

1. Measure before changing anything. Use tracemalloc snapshots and snapshot.compare_to(..., 'lineno') to find the lines that allocate, memory_profiler for a line-by-line view, and resource.getrusage(...).ru_maxrss for peak RSS (kilobytes on Linux, bytes on macOS).
2. Stream instead of materialising. Iterate files line by line, use generators instead of building lists, and use pandas read_csv(chunksize=...), which yields DataFrames one chunk at a time. Aggregate per chunk, combine the partial results, and write output incrementally.
3. Shrink the representation. Pass usecols, set explicit dtypes (int32/float32, 'category' for low-cardinality strings), use numpy arrays instead of lists of Python ints, add slots to classes with millions of instances, and read only the needed columns from Parquet.
4. Find references that keep memory alive: unbounded caches such as lru_cache(maxsize=None), lists that grow across loop iterations, globals and closures, and intermediate DataFrame copies. Drop them with del, and call gc.collect() only when reference cycles are involved.
5. Move the work elsewhere when that is cheaper: push joins and aggregations into the database or warehouse, or use out-of-core tools (DuckDB, Polars lazy/streaming, Dask). The trade-off is that chunking makes global operations such as sort, distinct and median harder.

Follow-up

• How do you compute an exact median or distinct count when you can only hold one chunk at a time?
• Why can forking worker processes with multiprocessing still blow up memory even though fork is copy-on-write?
• How would you show that your change fixed the problem and did not just move it?

Write a SQL query utilizing window functions to find the top-performing products per category over a specific

Medium · SQL & Data Architecture

Write a SQL query utilizing window functions to find the top-performing products per category over a specific time period.

Approach

1. Define 'top-performing' before writing anything: revenue as SUM(quantity * unit_price) net of refunds, or units sold. Confirm N and whether ties count.
2. Aggregate first in a CTE: join order_items to orders and products, filter on a half-open date range (order_ts >= :start AND order_ts < :end_plus_one_day, so timestamps on the last day are included), then GROUP BY category_id, product_id.
3. Rank in a second step with DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC) and keep rank <= N in the outer query. A window function cannot go in WHERE; BigQuery, Snowflake and Databricks support QUALIFY instead.
4. Pick the ranking function on purpose: ROW_NUMBER returns exactly N rows but breaks ties arbitrarily (add product_id as a tiebreaker), RANK leaves gaps after ties, and DENSE_RANK keeps every product tied at the cutoff.
5. Check join fan-out: joining payments or refunds at a different grain before the SUM inflates revenue, so pre-aggregate them per order line. If products with zero sales must appear, drive the query from products with a LEFT JOIN.

Follow-up

• How would you add each product's share of its category's revenue in the same query?
• How does the query change to return the top products for each month of the year?
• How would you compute a rolling 7-day revenue per product, and what goes wrong if some days have no rows?

Denormalise tenant onto revisions and backfill it live

Hard · denormalisation · online migration · backfill

resource_revision (revision_id, resource_id, version, actor_user_id, change_kind, patch, request_id, created_at) has 400M rows and no tenant column; tenant_id lives only on resource. Two reads need it: a tenant-scoped audit feed ordered by created_at DESC, and an offboarding purge. Both join back to resource today. Justify adding tenant_id to resource_revision against those two reads, name the anomaly the copy introduces and the constraint that prevents it, then give the ordered migration for a live table taking 1.2k writes/second — the lock each step takes, how the backfill is batched, and where each step stops being reversible. PostgreSQL 16.

Approach

1. Justify from the access path rather than from taste. Without the column, the audit feed either scans resource_revision by created_at and discards other tenants' rows, or resolves the tenant's resource_ids first and probes with them — both proportional to the tenant's whole history rather than to one page. With (tenant_id, created_at DESC, revision_id DESC) it is a seek that stops at 50 rows, and the purge becomes a ranged delete instead of a join.
2. Name the cost exactly: a second copy of a fact can disagree with the first. Make the disagreement unwritable rather than documented — add UNIQUE (resource_id, tenant_id) on resource so it can serve as a foreign-key target, then FOREIGN KEY (resource_id, tenant_id) REFERENCES resource (resource_id, tenant_id) on the revision table. A revision can then only ever carry its parent's tenant.
3. Step one, expand: ALTER TABLE resource_revision ADD COLUMN tenant_id BIGINT NULL, with no default, so it is a catalogue change and no rewrite. It still needs ACCESS EXCLUSIVE for an instant, and that instant queues behind the longest open transaction on the table while every later query queues behind it — set lock_timeout to 2s and retry rather than wait.
4. Step two, dual-write: deploy the writer that populates tenant_id on every new revision while reads still use the join. Reversible by redeploying the previous build, because nothing reads the column yet.

Follow-up

• The backfill is half finished and a rollback is required. What state is the table in, and what does the previous build do with a half-populated column?
• How do you verify the backfill actually finished, given rows are still being inserted while it runs?

Given a business scenario (e.g., e-commerce transactions), design a relational database schema that supports f

Medium · SQL & Data Architecture

Given a business scenario (e.g., e-commerce transactions), design a relational database schema that supports fast reporting and analytics.

Approach

1. Start from the reporting questions (revenue by day, product, region and channel; returns; repeat customers), the data volume, and how fresh the data must be. Then keep the transactional model separate from the reporting model.
2. Transactional side in 3NF: customers, products, categories, orders, order_items(order_id, product_id, quantity, unit_price at time of sale), payments and refunds, with primary and foreign keys. Use NUMERIC/DECIMAL for money, never float.
3. Reporting side as a star: state the grain first (one row per order line), then fact_order_line with quantity, gross and net amount, discount and tax, plus surrogate keys to dim_date, dim_product, dim_customer and dim_channel.
4. For fast reads: partition the fact table by order date, cluster or index on the product and customer keys, and pre-aggregate daily summary tables or materialized views for dashboards. Use SCD type 2 on dim_customer so region history stays correct.
5. Edge cases: returns and partial refunds (a returns fact or negative lines), multiple currencies (store the original amount, the converted amount and the rate date), guest checkouts (an 'unknown' dimension row), and late-arriving orders.

Follow-up

• A product moves to a new category. Should past revenue report under the old category or the new one, and how does the schema support each answer?
• How would you load the fact table incrementally without double-counting orders that get updated?
• Which indexes or partitions would you drop if write volume became the bottleneck?

How would you structure an ETL pipeline to ingest raw, unstructured data and load it into a structured cloud d

Medium · SQL & Data Architecture

How would you structure an ETL pipeline to ingest raw, unstructured data and load it into a structured cloud data warehouse?

Approach

1. Clarify the sources and their shape (JSON events, CSV exports, logs, PDFs or free text), the volume, whether data arrives in batches or as a stream, and how fresh the warehouse tables must be.
2. Layer it: a raw landing zone in object storage (immutable, partitioned by source and ingest date), then staging that parses records into semi-structured columns, then typed and cleaned models, then reporting marts. Keeping raw data lets me replay after a parsing bug.
3. ETL versus ELT: load raw data and transform in SQL inside the warehouse (for example dbt models with tests) when warehouse compute is cheap. Transform before loading when PII must be masked or when truly unstructured input needs Python extraction, such as text from PDFs.
4. Reliability: orchestrate with an Airflow DAG and make tasks idempotent (overwrite a date partition, or MERGE on a business key) so retries and backfills never duplicate rows. Load incrementally from a watermark, validate against a schema, and route rejected records to a quarantine table instead of dropping them.
5. Operate it: alert on freshness and on row-count anomalies, accept new fields in the raw layer but map them explicitly downstream, and partition or cluster warehouse tables by event date so queries scan less data.

Follow-up

• A source changes a field from integer to string without warning. What breaks, and where should the pipeline catch it?
• How would you reprocess the last three months after finding a bug in a transformation?
• How do you avoid duplicates if the source can deliver the same record more than once?

Explain the differences between star and snowflake schemas, and describe when you would choose one over the ot

Medium · SQL & Data Architecture

Explain the differences between star and snowflake schemas, and describe when you would choose one over the other for a client project.

Approach

1. Star schema: a central fact table at a declared grain, with each dimension as one denormalised table (dim_product carries the subcategory and category names). A query needs one join per dimension.
2. Snowflake schema: dimensions are normalised into hierarchies (product, then subcategory, then category). That means less redundancy and one place to update an attribute, but more joins and harder SQL for analysts and BI tools. The fact table is the same in both designs.
3. Default to a star for reporting on columnar warehouses. Storage is cheap, compression absorbs repeated strings, and self-service users write simpler queries.
4. Choose snowflaking when a dimension is very large and its hierarchy changes often, or when a sub-dimension such as geography is shared by several dimensions (customer and store) and must stay consistent.
5. For a client project, decide from who will query the model, what the BI tool's semantic layer expects, and how history is kept. SCD type 2 on a denormalised dimension repeats the hierarchy columns in every version row. Note that the term is unrelated to the Snowflake product.

Follow-up

• How would you model a many-to-many relationship, such as a product that belongs to several categories?
• Where does SCD type 2 history live in each design, and what does it cost at query time?
• When would you skip both and serve one wide denormalised table?

How do you ensure data quality and consistency when merging datasets from multiple disparate sources?

Medium · SQL & Data Architecture

Approach

1. Profile each source before merging: row counts, null rates, key uniqueness, value distributions, formats and update frequency. Write down the canonical schema and a field-by-field mapping from every source.
2. Standardise before matching: trim and case-fold strings, normalise emails and phone numbers, convert timestamps to UTC, convert amounts to one currency with a dated rate, and map codes to one reference list.
3. Resolve entities in tiers: deterministic matches on reliable keys first, then fuzzy matching (Jaro-Winkler or Levenshtein on names and addresses) with blocking on, for example, postcode, so the comparison does not grow as O(n^2).
4. Set survivorship rules when sources disagree (source priority, most recently updated, most complete) and keep lineage columns (source_system, source_id, loaded_at) so every merged value can be traced back.
5. Enforce quality with automated tests (unique, not null, referential integrity, accepted values) and reconcile counts and sums against each source. Quarantine failing rows and report them rather than silently dropping them.

Follow-up

• Two sources give different addresses for the same customer. Which wins, and how do you record the decision?
• How would you measure the precision and recall of your fuzzy matching?
• How do you handle a record that arrives a week late and changes an already-merged entity?

How do you explain complex technical architectures or algorithms to non-technical stakeholders or clients?

Medium · Behavioral & Consulting Mindset

Approach

1. Start from the decision the listener owns (budget, launch date, risk), not from the components. For example: 'I explain the pipeline as three boxes: where the data comes from, where it is cleaned, and the dashboard you open each morning.'
2. Use one simple diagram and add depth in layers. Name a technology only when the listener needs it for a decision, and use an analogy for one concept at a time without stretching it.
3. Translate trade-offs into the listener's terms: hourly versus daily freshness as a monthly cost difference, or a schema change as the number of reports that would need rework.
4. Check understanding by asking the listener to restate the choice or say what they would decide, then adjust the level of detail. Follow up with a short written summary of the options and the recommendation.
5. For a story: name a specific architecture or algorithm you explained, who the audience was, what you left out on purpose, and the decision they made as a result. Opening with jargon or tool names is the mistake that undermines the answer.

Follow-up

• Explain a star schema to a marketing director in two sentences.
• A client asks for real-time dashboards. How do you explain the cost and the alternatives?
• Tell me about a time an explanation did not land. What did you change?

Read latency spikes on a sixty-second sawtooth

Medium · cache stampede · single-flight · ttl

The cached listing read path serves about 14k reads/second at an 85% hit rate. p99 sits at 35 ms for 57 seconds, jumps to 900 ms for 3, and repeats. During each spike the primary shows several hundred identical listing queries starting within the same millisecond, all carrying one large tenant's id. Cache entries use a 60-second TTL. Give the mechanism, the ordered checks, the fix, and the correctness hazard your fix must not introduce.

Approach

1. Match the period to a configured number before theorising about load. A spike every 60 seconds against a 60-second TTL is an entry expiring, and you confirm it by correlating spike timestamps with the entry's write time rather than with the traffic curve. If the period had matched a cron or a GC interval instead, this is a different investigation.
2. Establish the concurrency of the miss. Several hundred identical queries in one millisecond means the miss path has no coalescing: every request that arrives between expiry and repopulation recomputes. The herd size is that key's arrival rate times its recompute time, so at 1.2k reads/second for the hot key and a 250 ms recompute you expect about 300 concurrent misses, which matches what is observed.
3. Add single-flight on the miss path so one caller per key recomputes under a short-lived lock while the rest wait for its result. Prefer stale-while-revalidate where the read tolerates it: return the expired value immediately and refresh asynchronously, which removes the latency spike rather than serialising it into a queue of waiters.
4. De-synchronise the keys. Write TTLs with jitter, for example 60 seconds plus or minus 10%, so a deploy or a mass invalidation does not align every key on the same second and turn a per-key herd into a fleet-wide one.

Follow-up

• The same sawtooth appears on a key that is invalidated on write rather than expired. Is that the same bug?
• How does your answer change if the recompute takes 4 seconds instead of 250 ms?

Suggested practice checklist

The plan follows the reported order of the loop: Python trees first, then memory and SQL, then data architecture and pipelines, and behavioral stories last. Each day produces something you can check, and the final day rehearses the full sequence.

Day 1 — Map the five rounds and set a Python baseline

• Write the five reported rounds in order, and next to each list the topics reported for it (tree traversal, window functions, star and snowflake schemas, ETL, stakeholder stories).
• Without an IDE or notes, write the longest-path function for a binary tree recursively, run it on three trees, and note every bug you hit.

Deliverable: A one-page round map with your weakest topic marked, plus a working recursive longest-path function and a list of the bugs you found.

Day 2 — Iterative traversal, complexity and edge cases

• Rewrite the longest-path function iteratively with an explicit stack and compare outputs with the recursive version on random trees.
• Write the time and space complexity for both versions, including the stack depth on a skewed tree and the effect of the recursion limit.
• Add asserts for an empty tree, a single node, a two-node chain, a skewed chain and a tree whose longest path avoids the root.

Deliverable: An iterative function that matches the recursive one, a written complexity comparison, and a test file covering the five edge cases.

Day 3 — Python memory and large-scale data processing

• Take a script that loads a large CSV fully into a list or DataFrame and rewrite it to stream with a generator or chunked reading, then compare peak memory with tracemalloc.
• Write a short ordered checklist for a memory-bound script: measure first, then reduce data types, process in chunks, avoid copies and use generators.

Deliverable: A before-and-after memory measurement for one script and a written optimisation checklist you can say aloud.

Day 4 — SQL: window functions, CTEs and retention

• Write the top-products-per-category query for a date range with a ranking window function inside a CTE, and test it on tied revenue values.
• Build a monthly retention query on a small transactions table using a first-purchase cohort and a join back to later activity.
• Check each query for join fan-out by comparing row counts before and after each join.

Deliverable: Two tested SQL queries (top products per category, rolling retention) with notes on tie handling and join fan-out.

Day 5 — Schema design: reporting schemas and star versus snowflake

• Design a retail or e-commerce schema for fast reporting: define the grain of the fact table, the dimensions and three reports it must support.
• Write a comparison of star and snowflake versions of that schema, covering join count, storage, update effort and how readable the queries are.

Deliverable: A schema diagram with the grain stated, a star versus snowflake comparison table, and notes from the denormalisation practice exercise.

Day 6 — ETL pipelines and data quality

• Outline a pipeline that ingests raw unstructured files into a cloud warehouse: landing area, parsing, validation, load, and handling of rows that fail.
• Write a list of data quality checks for merging two sources with different keys: duplicate keys, mismatched formats, missing values, referential integrity and row-count reconciliation.
• Explain your pipeline aloud as you would to a client, then note where the explanation lost you.

Deliverable: A pipeline diagram with failure handling, a data quality checklist for merging sources, and a note on the weakest part of your spoken explanation.

Day 7 — Behavioral stories and a full run-through

• Write four stories in situation, decision, result form for the deadline, new-tool, architecture-disagreement and why-Artefact prompts.
• Practise explaining the longest-path algorithm or your ETL pipeline to a non-technical listener using an analogy, and record yourself.
• Run through the five rounds in order: a short background pitch, a coding warm-up, one SQL query, one schema sketch and one story.

Deliverable: Four written stories, one recorded plain-language explanation, and a list of three things to fix before the loop.

Consulting-style behavioral preparation

Candidates report the final round as a conversation with an engineering manager or team lead about communication, adaptability and teamwork, and the role is described as client-facing with data scientists, consultants and product managers. Prepare stories where you owned the decision and can state the trade-off and the result. Include one story with a non-technical audience.

1. How do you explain complex technical architectures or algorithms to non-technical stakeholders or clients?
2. Why do you want to work at Artefact, and how would your engineering skills contribute to its data consulting work?
3. Describe a time you worked with a client or stakeholder whose technical requirements conflicted, and how you reached an agreement.

How do you handle edge cases, such as empty trees or single-node structures, in your Python code?

Medium · Python Coding & Algorithms

Approach

1. Treat it as an engineering question, not a story: list the inputs that break tree code before you write any. An empty tree (root is None), a single node, a long chain with one child per node, a node with many children, and repeated or invalid links if the input is really a graph.
2. Fix a convention out loud before coding. For the longest path between two nodes, say whether you count edges or nodes, so a single node returns 0 edges (or 1 node) and an empty tree returns 0 (or -1 for height). Mismatched conventions are the usual source of off-by-one bugs.
3. Handle the base case first with an early return, such as if root is None: return 0, rather than wrapping the traversal in try/except. In the iterative version, check the empty case before pushing onto the stack so you never pop from an empty list.
4. Name the depth risk: CPython's default recursion limit is about 1000 frames, so a chain-shaped tree with tens of thousands of nodes raises RecursionError in the recursive version. The fix is the iterative traversal with an explicit stack, not raising the limit and hoping the C stack survives.
5. Show the check: write tiny asserts for None, one node, a two-node chain, a balanced tree and a skewed chain, and say which one would have caught your last bug. Mention that a path may avoid the root, so test a tree where the longest path sits entirely in a subtree.

Follow-up

• How would your code change if the input could contain a cycle, so it is a graph and not a tree?
• What would you do if the tree has a hundred thousand nodes in a single chain?
• How would you test this function if you could not see the input data?

Tell me about a time you had to deliver a technical solution under a tight deadline with ambiguous requirement

Medium · Behavioral & Consulting Mindset

Tell me about a time you had to deliver a technical solution under a tight deadline with ambiguous requirements.

Approach

1. Pick a project where you owned the delivery decisions. State the deadline and what was unclear in a sentence or two (who the user was, what the data looked like, which metric mattered), then spend most of the answer on what you did about the ambiguity.
2. Show the concrete steps: you wrote down your assumptions, confirmed the riskiest ones with the person who could answer, and agreed on a smallest useful slice to deliver first. Say what you explicitly left out and who agreed to that.
3. Describe the trade-off you made under the deadline, such as a simpler schema, a manual step you automated later, or fewer tests on a low-risk path, and how you contained the risk (a clear note of known gaps, a quick check on the main path).
4. Close with the result using only numbers you can stand behind: what shipped, what the stakeholder did with it, what changed after, and one thing you would do differently. A story with no cost or mistake sounds rehearsed.
5. The mistake that sinks this answer is blaming the unclear requirements or listing hard work and long hours with no decisions. Focus on how you reduced the uncertainty and kept the client informed.

Follow-up

• What did you decide to leave out, and who agreed to that?
• Which of your assumptions turned out wrong, and what did it cost?
• How did you keep the stakeholder informed while the requirements were changing?

Describe a situation where you had to learn a new tool, language, or framework quickly to complete a project.

Medium · Behavioral & Consulting Mindset

Approach

1. Choose a real case where the learning had a deliverable: a pipeline in a tool you had not used, a new language for a service, or a cloud service you needed for a client. Name the tool, why the project needed it and what you had to ship.
2. Describe your method, which is the part being tested: you read the official documentation for the core concepts, built the smallest working example, compared it with something you already knew, and asked a colleague one precise question rather than ten vague ones. Mention how you checked your understanding, such as a test or a review.
3. Say what you got wrong first. A real misunderstanding, for example about how the tool handles retries, types or ordering, and how you found it, makes the story credible and shows you learn from errors.
4. Give the result and the follow-through: what shipped, whether it held up, and what you left behind (a short README, a reusable template, a review of someone else's first use).
5. Do not claim you became an expert, and do not pick a trivial tool. If you have used Airflow, dbt or a cloud platform, those fit this role well, but only use them if the experience is genuine.

Follow-up

• What was the first thing you misunderstood about it?
• How did you know your first implementation was correct?
• How did you decide when you knew enough to start building?

How do you handle disagreements within an engineering team regarding architectural choices?

Medium · Behavioral & Consulting Mindset

Approach

1. Pick a real architectural disagreement with a technical subject, such as a normalised versus denormalised schema, batch versus streaming ingestion, or a managed service versus self-built component. Present both positions fairly in a sentence each, so the listener sees you understood the other side.
2. Explain how you turned opinion into evidence: agree on the criteria first (cost, query speed, complexity, how long it takes to build), then build a small prototype, run a benchmark, or write a one-page comparison of the options.
3. State who made the final call and how you handled it. If the team chose the other option, say you committed to it and helped it succeed. If yours was chosen, say what you did to address the concerns of the person who disagreed.
4. Give the outcome, including what you learned. A good ending is a decision that was revisited later when facts changed, or a design record the team kept.
5. The mistake to avoid is a story where you were simply right and the other person was stubborn. It signals low collaboration, which matters for a role that works with data scientists, consultants and clients.

Follow-up

• What if the decision turned out to be wrong a few months later?
• How do you handle it when your manager decides against your recommendation?
• What would you do if a client wanted a design you thought was a poor choice?

Why do you want to work at Artefact, and how do you see your engineering skills contributing to our data consu

Medium · Behavioral & Consulting Mindset

Why do you want to work at Artefact, and how do you see your engineering skills contributing to our data consulting missions?

Approach

1. Build the answer in three parts: what draws you to data consulting and AI work, how your own experience fits what the engineer role is described to involve (Python services, SQL and schema design, data pipelines, working with data scientists and clients), and what you want to learn there.
2. Make it specific to you. Name one project of yours that matches the work, for instance a pipeline you built or a schema you redesigned, and say what you did on it. A project-level detail separates your answer from a general statement of admiration.
3. Link your skills to the consulting setting without claiming facts about the firm you cannot verify. Read the company's public material before the interview and refer only to things you have actually read, such as a case study or a post.
4. Address the client-facing element directly: say what you have done when you needed to explain technical work to non-technical people, since the role is described as working closely with consultants, product managers and data scientists.
5. Avoid flattery, stock phrases about being passionate, and pay as the main reason. Finish with what you hope to be doing a year in, tied to the skills you would build.

Follow-up

• Why consulting rather than a product company?
• Which of your projects is closest to client-facing work, and what did you do on it?
• How would you handle a client request that conflicted with your technical judgement?