Top 100 Data Engineering Interview Questions and Answers

Detailed explanation

Think of the data platform as a supply chain. The data engineer owns the plumbing:

• Ingestion: pulling data from databases, APIs, files and event streams.
• Storage: designing lake and warehouse layouts, file formats and partitioning.
• Processing: batch and streaming jobs that clean, join and reshape data.
• Reliability: orchestration, retries, monitoring, data quality checks and SLAs.
• Platform concerns: security, access control, cost and performance.

A data scientist consumes this output to answer questions with statistics and machine learning. Their success depends on whether the features they need exist, are fresh and are correct, which is why the two roles work closely together.

The analytics engineer is a newer title that grew with tools like dbt. They write SQL transformations, define business logic (what counts as an “active customer”), add tests and documentation, and publish marts that analysts and BI tools query.

In smaller companies one person often covers all three. In larger ones the boundaries are sharper and data engineers lean further toward software and infrastructure work.

Real-world use case

In an e-commerce company, the data engineer streams order events from the application database into the lakehouse. The analytics engineer builds a fct_orders model with a tested revenue definition. The data scientist uses it to train a demand-forecasting model.

Common mistakes and red flags

• Describing the job as “writing SQL queries” with no mention of reliability, scale or ownership.
• Saying data engineers “don’t need to understand the business”.
• Not knowing where their own pipelines’ data ends up or who uses it.

Follow-up questions

• Which part of the role do you enjoy most? — Pick one area and back it with a concrete project.
• How do you work with data scientists? — Agree feature definitions and freshness SLAs up front, and give them production-grade tables, not ad-hoc extracts.

Detailed explanation

Bill Inmon’s classic definition still holds: a warehouse is subject-oriented, integrated, time-variant and non-volatile. In plain terms:

• Subject-oriented: organised around business areas (sales, customers, inventory), not around one application’s screens.
• Integrated: customer IDs, currencies and date formats from different sources are reconciled into one consistent view.
• Time-variant: it keeps history, so you can ask what the numbers looked like last quarter.
• Non-volatile: data is loaded and appended rather than constantly overwritten by users.

An operational database (Postgres behind a checkout service, for example) has different goals. It must handle thousands of concurrent inserts and updates, enforce constraints and respond in milliseconds for single-row operations. It is usually highly normalised to avoid update anomalies and often keeps only current state.

Running heavy analytical queries on the operational database competes with the application for resources and can slow down real customers. That is the main practical reason the two are separated.

Modern cloud warehouses (Snowflake, BigQuery, Redshift, Databricks SQL) separate storage from compute, so analytical scale no longer means buying one huge server.

Real-world use case

A bank’s core system processes card payments in real time. Every night (or continuously, through CDC) those transactions land in the warehouse. There, risk analysts join them with customer, merchant and fraud-label data covering several years.

Common mistakes and red flags

• Saying a warehouse is “just a bigger database”.
• Thinking a read replica of the OLTP database is a warehouse. It removes load but provides no integration or history.
• Not knowing why columnar storage helps analytics.

Follow-up questions

• What is a data mart? — A subject-specific subset of the warehouse built for one team or domain.
• Can a lakehouse replace a warehouse? — Often yes for analytics, if it provides ACID tables, governance and good SQL performance (see Data lake vs data warehouse vs lakehouse).

Detailed explanation

The differences follow from the access pattern.

An OLTP query typically touches one or a few rows by key: “insert this order”, “update this account balance”. So OLTP systems use:

• Row-oriented storage, so a whole record is read or written in one I/O.
• B-tree indexes on primary and foreign keys for fast point lookups.
• Normalised schemas (3NF) so each fact is stored once and updates stay consistent.
• Strong transactional guarantees (ACID, row-level locking or MVCC) because many users write at once.

An OLAP query typically scans millions of rows but only a few columns: “revenue by region by month for three years”. So OLAP systems use:

• Columnar storage, so only the needed columns are read and similar values compress well.
• Partition and file pruning using min/max metadata instead of many secondary indexes.
• Denormalised or dimensional schemas (star schema) to reduce joins.
• Massively parallel processing (MPP) that splits a scan across many nodes.

Some products now blur the line. HTAP (hybrid transactional/analytical processing) databases and features like Snowflake hybrid tables try to serve both. In interviews, still explain the classic split first.

Real-world use case

A ride-hailing app writes each trip to an OLTP database as it happens. Pricing analysts query an OLAP warehouse to compare surge multipliers across cities and weeks without touching the production database.

Common mistakes and red flags

• Claiming OLAP systems “don’t support updates”. They do, but updates are costlier because files are immutable or columnar.
• Suggesting more indexes to speed up warehouse scans.
• Mixing up OLAP the workload with “OLAP cubes” the older technology.

Follow-up questions

• Why is columnar storage better for aggregates? — It reads only the referenced columns and compresses similar values, which cuts I/O dramatically.
• What is MVCC? — Multi-version concurrency control: readers see a consistent snapshot while writers create new row versions, so reads don’t block writes.

Detailed explanation

In a star schema, fct_sales holds foreign keys and numeric measures, and each dimension is one wide table. dim_product contains product name, brand, category and department all in one row. BI tools and analysts find it intuitive, and query engines handle the single-hop joins well.

In a snowflake schema, the dimension is split: dim_product references dim_category, which references dim_department. This removes repeated text (the department name is stored once rather than on every product row) and makes hierarchy changes a one-row update.

On columnar cloud warehouses, the storage argument for snowflaking is weak: repeated strings compress very well. The performance argument usually favours stars. Snowflaking still makes sense when:

• a sub-dimension is large and shared by several dimensions (for example a geography hierarchy used by customer, store and supplier);
• a source system already delivers the hierarchy normalised and you need it for governance;
• you need different SCD behaviour on different levels of a hierarchy.

Example

-- Dialect: ANSI SQL
-- Star: product attributes flattened into one dimension
SELECT d.department_name,
       SUM(f.net_amount) AS revenue
FROM fct_sales f
JOIN dim_product d ON f.product_key = d.product_key
GROUP BY d.department_name;

-- Snowflake: same question needs two extra joins
SELECT dep.department_name,
       SUM(f.net_amount) AS revenue
FROM fct_sales f
JOIN dim_product p     ON f.product_key = p.product_key
JOIN dim_category c    ON p.category_key = c.category_key
JOIN dim_department dep ON c.department_key = dep.department_key
GROUP BY dep.department_name;

Real-world use case

A marketplace keeps a star schema for its sales dashboards. It maintains a separate normalised dim_geography that the customer, seller and warehouse dimensions all reference, so region definitions are managed in one place.

Common mistakes and red flags

• Confusing “snowflake schema” with the Snowflake product.
• Saying snowflake schema is always faster because it is “normalised”.
• Putting measures in dimension tables.

Follow-up questions

• What is a galaxy (fact constellation) schema? — Multiple fact tables sharing conformed dimensions.
• Would you snowflake on Snowflake or BigQuery? — Rarely; compression makes the storage saving small, and extra joins add complexity.

Detailed explanation

Natural keys are tempting because they already exist, but they cause problems in a warehouse:

• They change. People change email addresses. Companies renumber products after a migration.
• They collide across sources. Customer 1001 in the CRM and customer 1001 in the billing system may be different people.
• They cannot represent history. In an SCD Type 2 dimension (see How do you design and load an SCD Type 2 dimension?), one customer has several rows over time, so the natural key is no longer unique. Each version needs its own surrogate key.
• They can be wide or composite, which makes joins slower and fact tables larger.

Surrogate keys solve these by giving each dimension row its own stable identifier. Facts store the surrogate key of the dimension version that was valid when the event happened.

Common ways to generate them:

In modern ELT, hash-based keys (for example an MD5 over the business key columns) are popular because a rerun produces the same key, which helps idempotency (see What is idempotency, and why is it critical in pipelines?). Integer sequences remain common in classic warehouses.

Keep the natural key as a regular column in the dimension. It is needed for lookups during loading and for tracing data back to the source.

Example

-- Dialect: Snowflake SQL
-- Deterministic surrogate key from source system + business key
SELECT
    MD5(source_system || '|' || customer_id) AS customer_key,
    source_system,
    customer_id,      -- natural key kept for lineage
    customer_name,
    email
FROM stg_customers;

Real-world use case

After merging two retail brands, both had a product numbered A-100. Surrogate keys built from brand || '|' || sku kept them distinct in dim_product, and no fact rows were mis-attributed.

Common mistakes and red flags

• Using email or phone number as the primary key of a customer dimension.
• Dropping the natural key from the dimension.
• Hashing columns without a delimiter, so ('ab','c') and ('a','bc') produce the same key.

Follow-up questions

• Why add a delimiter before hashing? — To avoid two different combinations producing the same concatenated string.
• How do you handle facts that arrive before their dimension row? — Insert an “inferred” placeholder dimension row with the natural key and update it when the real row arrives.

Detailed explanation

Dimension attributes such as a customer’s city or a product’s category do change, just not often. The question is whether reports should show facts against the value at the time of the event or the current value. Each SCD type answers that differently, and you can choose per attribute.

• Type 0 (retain original): the value is fixed at first load and never updated. Good for “original signup channel” or “date of birth”.
• Type 1 (overwrite): replace the old value. No history is kept, so past facts now report under the new value. Good for corrections (a typo in a name) or attributes nobody analyses historically.
• Type 2 (add new row): close the current row (set an end date and clear the current flag) and insert a new row with a new surrogate key. Facts loaded earlier still point to the old row, so history is preserved exactly. This is the most common answer for attributes that drive analysis, such as region, segment or price tier.
• Type 3 (add new column): keep current_region and previous_region in the same row. It holds only one prior value, and is useful for “before and after” comparisons such as a sales territory realignment.

Types 4, 5 and 6 are covered in SCD Types 4, 5 and 6 (hybrid): when are they used?. The mechanics of loading Type 2 are in How do you design and load an SCD Type 2 dimension?.

Example

-- Dialect: ANSI SQL
-- Type 1: simple overwrite of a corrected attribute
UPDATE dim_customer
SET customer_name = 'Priya Sharma'
WHERE customer_id = 'C-2041';

Real-world use case

A sales organisation tracks account manager on dim_account as Type 2, so commissions are attributed to whoever owned the account at the time of each deal. It treats typo fixes in the account name as Type 1.

Common mistakes and red flags

• Applying Type 2 to every column, including ones that change constantly. That explodes the row count (see SCD Types 4, 5 and 6 (hybrid): when are they used? for mini-dimensions).
• Saying Type 1 “keeps history in the logs”.
• Not realising one table can mix types across columns.

Follow-up questions

• Which type for a customer’s credit score that changes monthly? — Often a mini-dimension (Type 4) or a periodic snapshot fact, not Type 2 on the main dimension.
• How do facts join to a Type 2 dimension? — Through the surrogate key valid at the event time, looked up by effective date range.

Detailed explanation

Normalisation prevents update anomalies: changing a fact in one place but not another, being unable to insert data without unrelated data, or losing information when a row is deleted.

• 1NF: every column holds one atomic value, and there are no repeating column groups (phone1, phone2, phone3). Each row is uniquely identifiable.
• 2NF: 1NF, plus every non-key column depends on the whole primary key. In order_lines(order_id, product_id, quantity, product_name), product_name depends only on product_id, so it moves to a products table.
• 3NF: 2NF, plus no transitive dependencies: non-key columns must not depend on other non-key columns. In customers(customer_id, zip_code, city), city depends on zip code, so it moves to a zip_codes table.
• BCNF tightens 3NF for rare cases with overlapping candidate keys. It is worth naming, but seldom tested in depth.

When to denormalise

• Analytical workloads: star schemas deliberately flatten dimensions, so scans need fewer joins (see Star schema vs snowflake schema: when would you pick each?).
• Read-heavy serving tables: API or dashboard tables that must answer in milliseconds.
• Columnar engines: redundancy compresses well, so the storage cost is small.
• Pre-joined wide tables for machine-learning features or the One Big Table pattern (see One Big Table (wide denormalised) vs dimensional model).

The trade-off: denormalised tables are faster and simpler to query, but harder to keep consistent. Updates must touch many rows, and the pipeline becomes responsible for consistency instead of the schema.

Example

-- Dialect: ANSI SQL
-- 3NF design for an orders system
CREATE TABLE customers   (customer_id INT PRIMARY KEY, customer_name VARCHAR(100), zip_code VARCHAR(10));
CREATE TABLE zip_codes   (zip_code VARCHAR(10) PRIMARY KEY, city VARCHAR(60), state VARCHAR(30));
CREATE TABLE products    (product_id INT PRIMARY KEY, product_name VARCHAR(100), list_price DECIMAL(10,2));
CREATE TABLE orders      (order_id INT PRIMARY KEY, customer_id INT REFERENCES customers, order_ts TIMESTAMP);
CREATE TABLE order_lines (order_id INT REFERENCES orders, product_id INT REFERENCES products,
                          quantity INT, unit_price DECIMAL(10,2),
                          PRIMARY KEY (order_id, product_id));

unit_price on order_lines is not redundant with list_price. It records the price actually charged, which is a different fact.

Real-world use case

The application database stays in 3NF for transactional safety. The warehouse gold layer denormalises customer, city and state into dim_customer, so a revenue-by-state dashboard needs one join.

Common mistakes and red flags

• Saying normalisation is “bad for performance” without naming the workload.
• Confusing 2NF and 3NF.
• Treating historical prices on order lines as redundancy.

Follow-up questions

• Is a star schema in 3NF? — The fact table usually is; dimensions are deliberately denormalised (often 2NF).
• What anomaly does 3NF prevent? — Updating a city for one zip code but not another row with the same zip code.

Detailed explanation

The three levels move from “what the business means” to “how the database stores it”.

• Conceptual: for business stakeholders. “A Customer places Orders. An Order contains Products.” Boxes and lines, no columns. It answers scope and vocabulary questions: is a “customer” a person or an account?
• Logical: for analysts and engineers. Every entity has attributes, a primary key and relationships with cardinality (one-to-many, many-to-many). It is normalised and platform-independent. Here you decide that Order Line is its own entity and that a Customer can have many Addresses.
• Physical: for the database. Exact table and column names, data types (NUMBER(12,2) vs FLOAT), nullability, surrogate key strategy, indexes or clustering keys, partitioning, and platform features (Snowflake clustering keys, Delta liquid clustering).

In practice, modern teams often skip formal diagrams for the first two levels and capture them as documentation in dbt YAML or a catalog. Interviewers still want to hear that you think at all three levels, especially that you settle definitions before writing DDL.

Example

-- Dialect: Snowflake SQL
-- Physical model detail: types, nullability and a clustering choice
CREATE TABLE fct_order_lines (
    order_line_key  NUMBER(38,0)  NOT NULL,
    order_date      DATE          NOT NULL,
    customer_key    NUMBER(38,0)  NOT NULL,
    product_key     NUMBER(38,0)  NOT NULL,
    quantity        NUMBER(10,0)  NOT NULL,
    net_amount      NUMBER(12,2)  NOT NULL
)
CLUSTER BY (order_date);

Real-world use case

Before migrating a CRM, the team agrees a conceptual model with sales operations: Account, Contact, Opportunity. That surfaces that “customer” means Account to finance but Contact to marketing, before any pipeline is built.

Common mistakes and red flags

• Jumping to DDL without agreeing entity definitions.
• Putting platform-specific details in a logical model.
• Not knowing what cardinality means.

Follow-up questions

• Which tools do you use? — ER diagram tools for design, and dbt docs or a catalog to keep the model documented next to the code.
• How do you keep models in sync with reality? — Generate documentation from the code and enforce contracts (see Model contracts).

Detailed explanation

ETL (Extract → Transform → Load) grew up when warehouse compute was expensive and fixed. Tools like Informatica, SSIS or DataStage pulled data, transformed it on their own servers, and loaded only the cleaned result. Benefits: the warehouse stays small, and sensitive fields can be removed before they ever land. Drawbacks: raw data is often discarded, so reprocessing means re-extracting. Transformations live in a proprietary tool and are harder to version and test.

ELT (Extract → Load → Transform) lands raw data first (often with tools like Fivetran, Airbyte or native connectors), then transforms it inside the platform with SQL, frequently managed by dbt. Benefits: raw history is kept, so logic can be rerun. Transformations are code in Git with tests. The warehouse’s elastic compute does the heavy lifting. Drawbacks: raw sensitive data lands in the platform and needs governance (masking, access control). Messy raw data can tempt teams to skip modelling.

Real pipelines are often hybrids. Light transformation happens in flight, such as PII hashing or format conversion in Spark, and the business modelling happens in-warehouse. Streaming pipelines also transform before landing.

Real-world use case

A healthcare company hashes patient identifiers in a Spark job before landing data (the ETL part, for compliance). All downstream modelling then runs as ELT in the warehouse with dbt.

Common mistakes and red flags

• Saying ELT means “no transformation”.
• Claiming ETL is obsolete. It still fits compliance-driven or edge cases.
• Ignoring the governance burden of landing raw PII under ELT.

Follow-up questions

• What is EtLT? — Extract, a light transform (cleansing, PII masking), load, then the main transform in the warehouse.
• Where does Spark fit? — It can do either: transform before loading to a warehouse, or act as the in-platform engine on a lakehouse.

In depth: ETL vs ELT: what factors decide the choice?

Detailed explanation

Data path

1. Sources: OLTP databases, SaaS APIs, files, event streams, IoT devices.
2. Ingestion: batch extracts, CDC (see What is Change Data Capture? Log-based vs query-based vs trigger-based), streaming consumers, managed connectors. Here you decide full vs incremental load (see Full load vs incremental load).
3. Storage: raw landing zone (object storage or warehouse raw schema), then curated layers. Choices include file and table formats (see Row-oriented vs columnar file formats–Delta Lake vs Apache Iceberg vs Apache Hudi) and partitioning.
4. Transformation: cleaning, deduplication, conforming, modelling and aggregation, in SQL, Spark or Python.
5. Serving: warehouse marts for BI, feature tables for ML, APIs or reverse ETL into operational tools (see What is reverse ETL?).

Operational path

• Orchestration: scheduling, dependencies, retries, backfills (Airflow, Lakeflow Jobs, dbt jobs). See What is Apache Airflow? Explain DAGs, tasks and operators.
• Data quality: checks on freshness, volume, schema, uniqueness and business rules. See How do you build data quality checks into a pipeline?.
• Observability and alerting: run status, duration trends, data anomalies, on-call routing.
• Metadata, catalog and lineage: so people can find data and understand impact. See What is data lineage, and how is it captured?.
• Security and governance: access control, masking, encryption, audit. See What is data governance? Catalog, ownership, stewardship–Encryption at rest and in transit, and key management.
• CI/CD and environments: tests, code review and safe deployment. See What does CI/CD mean for data pipelines?.
• Cost management: tagging, right-sizing, monitoring. See General principles of cloud data cost optimisation.

Example

[Postgres] --CDC--> [Kafka] --> [Object storage: bronze] --Spark/dbt--> [silver] --> [gold marts] --> [BI / ML / Reverse ETL]
                         |                                                  |
                   [Orchestrator: schedules, retries]        [DQ checks, lineage, alerts, access control]

Real-world use case

A fintech’s payments pipeline uses CDC from the ledger database into Kafka, lands it in Delta bronze tables, transforms it in Spark to silver, and builds dbt gold models for finance. Airflow orchestrates the batch steps, and data quality checks block publishing if totals don’t reconcile with the ledger.

Common mistakes and red flags

• Listing only “extract, transform, load” and nothing operational.
• Not mentioning how failures are detected and recovered.
• Forgetting the consumers and their SLAs.

Follow-up questions

• Which component fails most often in your experience? — Usually ingestion, because of upstream changes, credentials and API limits, which is why contracts and alerts matter.
• How do you decide the SLA? — Work back from when consumers need the data, then budget time per stage with buffer for retries.

Detailed explanation

Full load (truncate-and-reload or create-or-replace)

• Simple logic, and every run produces a complete, consistent copy.
• Self-healing: a bug fixed today corrects all history on the next run.
• Cost and time grow with total volume, not change volume.
• Good for small tables (dimensions under a few million rows), reference data, and sources with no change tracking.

Incremental load

• Picks up changes using a high-watermark column such as updated_at (see High-watermark incremental loading and its pitfalls), a CDC log (see What is Change Data Capture? Log-based vs query-based vs trigger-based), file arrival, or a source-provided change feed.
• Writes with append (immutable events) or MERGE/upsert (mutable entities).
• Much cheaper at scale, and often the only way to meet tight SLAs on large tables.
• Risks: missed changes if the watermark is unreliable, deletes not captured, late-arriving records skipped, and drift from the source over time.

A common compromise: incremental daily, with a periodic full refresh (weekly or monthly) or a reconciliation job comparing counts and checksums against the source. Another is a lookback window: reprocess the last N days on each run to catch late updates (see Late-arriving data in incremental models (lookback windows)).

Example

-- Dialect: Snowflake SQL
-- Incremental upsert of changed customers since the last watermark
MERGE INTO dim_customer_current t
USING (
    SELECT * FROM stg_customers
    WHERE updated_at > (SELECT COALESCE(MAX(updated_at), '1900-01-01'::TIMESTAMP_NTZ)
                        FROM dim_customer_current)
) s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET t.customer_name = s.customer_name,
                             t.email         = s.email,
                             t.updated_at    = s.updated_at
WHEN NOT MATCHED THEN INSERT (customer_id, customer_name, email, updated_at)
                      VALUES (s.customer_id, s.customer_name, s.email, s.updated_at);

Real-world use case

A 3-billion-row orders table loads incrementally every hour through CDC. A 20,000-row product catalog is fully reloaded nightly because it is cheap and guarantees deletes are reflected.

Common mistakes and red flags

• Making everything incremental, even tiny tables.
• Not explaining how deletes reach the target.
• Using >= vs > on the watermark without thinking about duplicates or misses at the boundary.

Follow-up questions

• How do you capture hard deletes incrementally? — Log-based CDC, source soft-delete flags, or a periodic key comparison against the source.
• When would you switch an incremental table back to full? — When it is small enough, or when correctness issues outweigh the cost.

In depth: How would you design an incremental load for a large table?

Detailed explanation

Choose streaming when the value of data decays quickly: fraud detection, real-time pricing, operational alerting, user-facing personalisation, IoT monitoring.

Choose batch when decisions are made daily or weekly, sources only deliver in batches anyway, or complex historical joins and reprocessing dominate.

The line is blurring:

• Micro-batching (Spark Structured Streaming default) processes small batches every few seconds.
• Incremental batch (run every 5 minutes, process only new data) often gives “near real-time” at batch simplicity. Spark’s availableNow trigger and warehouse features like Snowflake Dynamic Tables or Lakeflow pipelines fit here.
• Unified engines let the same code run in batch or streaming mode.

A senior answer asks “what latency does the business actually need, and what is it worth?” before choosing. “Real-time” requests often turn out to mean “every 15 minutes”.

Real-world use case

A card issuer scores transactions for fraud in a streaming pipeline within 200 ms. The same events are aggregated nightly in batch for regulatory reporting and model retraining.

Common mistakes and red flags

• Choosing streaming because it sounds modern.
• Ignoring the operational cost of always-on streaming jobs.
• Not knowing that micro-batch is still streaming semantics.

Follow-up questions

• How would you convert a nightly batch to near real-time? — Make it incremental, then schedule it more frequently or run it as a streaming job with an incremental trigger.
• What is the hardest part of streaming? — Correctness under late, duplicated and out-of-order data while keeping state bounded (see Watermarks and late data in Structured Streaming).

Detailed explanation

Data lake. Object storage (S3, ADLS, GCS) holding Parquet, JSON, CSV, images and logs. It is cheap and flexible and suits data science and raw retention. Without discipline, it lacks ACID updates and consistent schemas, has weak access control, and turns into a “data swamp” (see What is a data swamp, and how do you prevent one?). Engines like Spark, Trino and Athena query it.

Data warehouse. A managed analytical database (Snowflake, BigQuery, Redshift, Synapse) with schema-on-write, strong SQL performance, concurrency and built-in security. Historically storage was proprietary and costlier, and unstructured data or ML workloads were awkward.

Lakehouse. Open table formats (Delta Lake, Apache Iceberg, Apache Hudi; see Delta Lake vs Apache Iceberg vs Apache Hudi) add a transaction log over files in the lake. That provides ACID writes, MERGE and deletes, time travel and schema enforcement. A catalog (Unity Catalog, AWS Glue, Polaris/Horizon) adds governance. Multiple engines can read the same tables.

The lines are converging. Snowflake and BigQuery read and write Iceberg tables, and Databricks offers serverless SQL warehouses on Delta. The interview point is the trade-off between openness and flexibility on one side and managed simplicity on the other, not brand loyalty.

Real-world use case

A media company keeps raw video metadata and logs in a lake. It runs its curated analytics as a lakehouse on Iceberg tables, queried by Spark for ML and by a warehouse engine for BI, without copying the data.

Common mistakes and red flags

• Saying “lakehouse = data lake + warehouse side by side”.
• Not mentioning the transaction log or table format as the enabler.
• Claiming warehouses can’t handle semi-structured data.

Follow-up questions

• What makes ACID possible on immutable object storage? — An atomic commit to a transaction log or metadata pointer (see How do open table formats make ACID possible on object storage?).
• When would you still choose a pure warehouse? — A SQL-heavy team, mostly structured data, and a preference for minimal platform operations.

In depth: Data lake vs warehouse vs lakehouse: when would you choose each?

Detailed explanation

• Bronze (raw): append-only copies of source data with ingestion metadata (_ingested_at, source file, batch ID). Minimal transformation, perhaps parsing into a table format. Its purpose is replay, audit and decoupling from source availability.
• Silver (clean / conformed): typed columns, deduplication, standardised formats (dates, currencies, codes), basic quality rules, joined reference data, and often current-state or SCD2 entity tables. This is the “single version of the enterprise data”, reusable by many use cases.
• Gold (curated / business): dimensional models, aggregates, feature tables and KPIs built for specific consumers. Business logic and metric definitions live here.

Why it helps

• Clear contracts per layer, so teams know where to find raw versus trusted data.
• Reprocessing is easy: fix silver logic and rebuild from bronze.
• Access control maps to layers. Few people read bronze PII. Many read gold.

Caveats interviewers like to hear

• It is a naming convention, not a technology. Mapping to raw/staging/marts in dbt is the same idea.
• Don’t add layers just for the pattern’s sake. Some flows need only bronze → gold, and complex ones need intermediate layers.
• Every layer costs storage and compute, so retention and materialisation choices matter.

Example

-- Dialect: Databricks SQL
-- Silver from bronze: type, clean and dedupe
CREATE OR REPLACE TABLE silver.orders AS
SELECT CAST(order_id AS BIGINT)            AS order_id,
       CAST(customer_id AS BIGINT)         AS customer_id,
       TO_TIMESTAMP(order_ts)              AS order_ts,
       UPPER(TRIM(currency))               AS currency,
       CAST(amount AS DECIMAL(12,2))       AS amount
FROM bronze.orders_raw
WHERE order_id IS NOT NULL
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY _ingested_at DESC) = 1;

Real-world use case

A retailer lands POS files into bronze every 15 minutes. Silver standardises store codes and removes duplicate transmissions. Gold publishes fct_sales and agg_store_daily for finance and merchandising dashboards. See Implementing medallion architecture on Databricks (See also Q32) for the Databricks implementation.

Common mistakes and red flags

• Applying business logic in bronze.
• Letting dashboards read silver or bronze directly.
• Treating medallion as a Databricks-only concept.

Follow-up questions

• Where do you handle PII? — Mask or tokenise when moving to silver, and restrict bronze access tightly.
• Do you keep bronze forever? — Keep it as long as replay and compliance need, then apply lifecycle policies (see Storage tiering and lifecycle policies).

Detailed explanation

Two points earn credit in interviews:

1. Quality is relative to use. A 2% null rate in phone_number may be fine for analytics but unacceptable for an SMS campaign. Define thresholds with consumers and record them as SLAs or contracts (see What are data contracts, and how do you enforce them?).
2. Detect at the right place. Validate schema and format at ingestion, keys and integrity at silver, and business reconciliation at gold. Fail fast on critical checks and warn on others.

Example

-- Dialect: ANSI SQL
-- A few dimension checks expressed as one profiling query
SELECT
    COUNT(*)                                                  AS row_count,
    COUNT(*) - COUNT(DISTINCT order_id)                       AS duplicate_order_ids,      -- uniqueness
    SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END)      AS null_customer_ids,         -- completeness
    SUM(CASE WHEN amount < 0 THEN 1 ELSE 0 END)               AS negative_amounts,          -- validity
    MAX(order_ts)                                             AS latest_order_ts            -- timeliness
FROM silver_orders;

Real-world use case

A subscription company’s churn model degraded silently because the cancel_reason field went 40% null after an app release. A completeness check with an alert at 5% would have caught it the same day.

Common mistakes and red flags

• Listing dimensions with no example checks.
• Treating all checks as blocking.
• Ignoring freshness as a quality dimension.

Follow-up questions

• Which dimension is hardest to test? — Accuracy, because it needs an external source of truth or reconciliation.
• How do you set thresholds? — From historical distributions plus consumer requirements, reviewed periodically.

Detailed explanation

• DAG: a collection of tasks and their dependencies, with no cycles. It has a dag_id, a schedule (cron, timedelta, or data-aware via Assets), a start date and default arguments such as retries.
• Task: one unit of work in a DAG. Each scheduled execution creates a task instance with a state (queued, running, success, failed, up_for_retry, skipped).
• Operator: a class that defines what a task does. BashOperator, PythonOperator and the @task decorator (TaskFlow API) are generic. Provider packages add SnowflakeSqlApiOperator, DatabricksRunNowOperator, KubernetesPodOperator and others.
• Sensor: a special operator that waits for a condition, such as a file landing (see Sensors vs deferrable operators).
• DAG run: one execution of the DAG for a given logical date or data interval (see Airflow catchup, backfill, start_date and logical date).

Good practice

• Airflow is an orchestrator, not a processing engine. Push heavy work to Snowflake, Databricks, Spark or Kubernetes pods.
• Keep top-level DAG code light. The scheduler parses DAG files frequently, so avoid database calls at import time.
• Make tasks idempotent and atomic (see How do you make Airflow tasks idempotent and safe to retry?).

Example

# Runtime: Python 3.11, Apache Airflow 2.x/3.x (TaskFlow API)
from datetime import datetime, timedelta
from airflow.decorators import dag, task

@dag(
    dag_id="daily_orders",
    schedule="0 2 * * *",
    start_date=datetime(2026, 1, 1),
    catchup=False,
    default_args={"retries": 2, "retry_delay": timedelta(minutes=5)},
)
def daily_orders():
    @task
    def extract(ds: str | None = None) -> str:
        # ds is the logical date string injected by Airflow, e.g. '2026-03-01'
        return f"s3://landing/orders/{ds}/"

    @task
    def load(path: str) -> None:
        print(f"Loading files from {path}")

    load(extract())

daily_orders()

In Airflow 3 the same decorators are also available from the airflow.sdk namespace (the Task SDK).

Real-world use case

A nightly DAG triggers an ingestion job, waits for it, runs dbt build on the affected models, then refreshes a BI extract. Airflow handles retries and alerts, while Snowflake does all the computation.

Common mistakes and red flags

• Processing large data inside the Airflow worker with pandas.
• Putting expensive code at DAG file top level.
• Calling Airflow a “streaming tool”.

Follow-up questions

• What is the TaskFlow API? — A decorator-based way to write tasks as Python functions, with return values passed automatically through XCom.
• How do you pass data between tasks? — Pass references (paths, table names) through XCom, not the data itself (see What are XComs, and what are their limits?).

Detailed explanation

Imagine orders(order_id, customer_id, amount, status, notes) with 1 billion rows, and a query SELECT SUM(amount) WHERE status = 'paid'.

• Row format: every row’s bytes (including large notes) must be read to extract amount and status.
• Columnar format: only the amount and status column chunks are read. Min/max statistics let the reader skip chunks entirely. Similar values sit together, so encodings such as dictionary, run-length and bit-packing shrink them dramatically.

Format notes

• CSV: human-readable, no types, ambiguous quoting and escaping, no nested data. Use only at boundaries.
• JSON: nested and self-describing, but verbose and slow to parse at scale.
• Avro: compact binary row format with an embedded schema and strong schema-evolution rules. Common for Kafka and CDC.
• Parquet / ORC: the analytics standards (see Parquet internals: row groups, column chunks, pages and statistics, Parquet vs ORC vs Avro).

A typical lakehouse flow: land as JSON or Avro, convert to Parquet-based table formats (Delta/Iceberg) in bronze or silver.

Example

# Runtime: Python 3.11 with pyarrow
import pyarrow as pa
import pyarrow.parquet as pq

table = pa.table({
    "order_id": [1, 2, 3],
    "status": ["paid", "paid", "refunded"],
    "amount": [120.50, 75.00, 30.25],
})
pq.write_table(table, "orders.parquet", compression="zstd")

# Read only the columns the query needs
subset = pq.read_table("orders.parquet", columns=["status", "amount"])
print(subset.num_rows, subset.column_names)

Real-world use case

Converting 4 TB of daily JSON logs to Parquet cut storage to about 600 GB, and typical Athena queries scanned a fraction of the bytes, which directly cut per-TB-scanned costs.

Common mistakes and red flags

• Storing analytical tables as CSV in the lake.
• Saying Avro is columnar.
• Ignoring that columnar formats are poor for single-row lookups.

Follow-up questions

• Why is Avro common in Kafka? — Compact binary, record-oriented and with well-defined schema evolution via a registry.
• Can you update a row in Parquet? — Not in place; you rewrite files, or use a table format with deletion vectors.

Detailed explanation

How to choose

• Analytical storage: Parquet by default. Its ecosystem breadth and table-format support make it the safe choice.
• ORC: fine if your stack is Hive or Trino-centric and already standardised on it. Some ACID Hive tables require ORC.
• Avro: event streams and CDC payloads, where each message is written and read as a whole and schemas evolve frequently. Convert to Parquet on landing for analytics.

Real-world use case

A fintech publishes transaction events to Kafka as Avro with a schema registry. A streaming job writes them into Iceberg tables (Parquet data files) for analytics and Avro manifest files for metadata, so each format is used where it fits.

Common mistakes and red flags

• Claiming ORC is always faster or always slower; it depends on engine and data.
• Using Avro as the long-term analytics format.
• Not knowing Delta Lake data files are Parquet.

Follow-up questions

• How does Avro schema evolution work? — The reader schema is resolved against the writer schema; new fields need defaults for backward compatibility.
• What about Arrow? — Arrow is an in-memory columnar format for fast processing and interchange, not a storage format (though Feather/Arrow IPC files exist).

Detailed explanation

Failures happen between “work done” and “success recorded”: a timeout after the insert committed, a network blip, a worker killed. The orchestrator retries. If the step is not idempotent, data is duplicated or double-counted.

Techniques

1. Deterministic scope: each run processes a fixed input slice (a date, a file set, an offset range), not “whatever is new right now”.
2. Replace, don’t append: overwrite the target partition (INSERT OVERWRITE, Delta replaceWhere, delete-then-insert in one transaction).
3. Upsert on a natural key: MERGE so reprocessing the same record updates rather than duplicates (see MERGE for upserts).
4. Dedupe on unique event IDs for append-only data.
5. Atomic publish: write to staging, then swap or commit (see Write-Audit-Publish and blue-green deployments for data).
6. Idempotency keys for side effects: an API call with a request ID, so the receiver ignores repeats.
7. Deterministic surrogate keys (hashes) instead of sequences, so reruns generate the same keys (see Surrogate keys vs natural keys).

Example

-- Dialect: Spark SQL (Delta Lake)
-- Rerunning this for the same date always yields the same partition contents
INSERT OVERWRITE gold.daily_revenue
PARTITION (revenue_date = '2026-03-01')
SELECT store_id, SUM(amount) AS revenue
FROM silver.orders
WHERE order_date = '2026-03-01'
GROUP BY store_id;

Real-world use case

A retry after a warehouse timeout doubled a day’s revenue in a dashboard. Rewriting the load as a partition overwrite meant later retries and backfills could never double-count.

Common mistakes and red flags

• Confusing idempotent with “it ran successfully”.
• Relying on “we never retry” as a strategy.
• Using now() inside transformations, so reruns give different results.

Follow-up questions

• Is MERGE always idempotent? — Only if the source is deduplicated and the update logic doesn’t accumulate (SET total = total + s.amount is not idempotent).
• How do you make streaming idempotent? — Checkpointed offsets plus idempotent or transactional sinks (see Checkpoints and exactly-once in Structured Streaming).

Detailed explanation

In data platforms

• Snowflake: a bigger warehouse size (XS → XL) is scaling up for one complex query. A multi-cluster warehouse is scaling out for concurrency (see Scale up vs scale out: multi-cluster warehouses and scaling policies).
• Spark / Databricks: more workers is scaling out. Bigger instance types with more memory per executor is scaling up, which helps when tasks spill or hit OOM (see How do you size executors (cores and memory)?).
• Kafka: more partitions and brokers is scaling out throughput.
• Databases: read replicas and sharding scale out. A bigger instance scales up.

Not everything scales out well. A skewed key or a step that needs global ordering still lands on one node, so scaling out doesn’t help until skew is fixed (see How do you detect data skew?).

Real-world use case

A dashboard queue built up at 9 AM when 200 analysts logged in. Scaling up the Snowflake warehouse didn’t help. Enabling multi-cluster scale-out (1–4 clusters) removed queuing at the same per-query speed.

Common mistakes and red flags

• Treating “add more nodes” as the answer to every slowdown.
• Not distinguishing concurrency problems from single-query performance problems.
• Ignoring skew when proposing scale-out.

Follow-up questions

• What is elastic scaling? — Automatically adding or removing capacity based on load (autoscaling clusters, multi-cluster warehouses).
• Which is cheaper? — Depends; doubling a Snowflake warehouse doubles credits per hour but can halve runtime, so cost may stay similar.

Detailed explanation

• Broker: a Kafka server that stores partitions and serves reads and writes. A cluster has several brokers. Modern Kafka (3.3+ production-ready, and the only mode in 4.0) uses KRaft for metadata instead of ZooKeeper.
• Topic: a named stream of records, such as orders. Configured with partition count, replication factor and retention.
• Partition: the unit of parallelism and ordering. Records are ordered within a partition, not across the topic. A record’s partition is chosen by its key’s hash (same key, same partition) or by the producer’s partitioner when there is no key.
• Offset: a monotonically increasing ID per record within a partition. Consumers track their position by committing offsets, stored in the internal __consumer_offsets topic.
• Replication: each partition has one leader and N−1 followers on other brokers. The in-sync replicas (ISR) are the followers that are fully caught up. With acks=all and min.insync.replicas=2, a write is acknowledged only once it is safely on at least two replicas.
• Retention: records stay for a configured time or size (for example 7 days) whether or not they’ve been consumed. Compacted topics keep the latest value per key instead (see Kafka retention vs log compaction).
• Producers and consumers: producers batch and compress records. Consumers in a consumer group share partitions (see Kafka consumer groups and rebalancing).

Example

# Runtime: Python 3.11 with confluent-kafka
from confluent_kafka import Producer

producer = Producer({
    "bootstrap.servers": "broker1:9092",
    "acks": "all",                 # wait for all in-sync replicas
    "enable.idempotence": True,    # no duplicates from producer retries
})

def on_delivery(err, msg):
    if err is not None:
        print(f"Delivery failed: {err}")

producer.produce("orders", key="order-1001", value=b'{"amount": 49.99}', callback=on_delivery)
producer.flush()

Real-world use case

An orders topic with 12 partitions and replication factor 3 lets up to 12 consumers process in parallel, and survives a broker failure without data loss. Keying by order_id keeps each order’s events in order.

Common mistakes and red flags

• Saying Kafka guarantees ordering across a whole topic.
• Thinking messages are deleted once consumed.
• Not knowing what ISR or acks=all means.

Follow-up questions

• How many partitions should a topic have? — Enough for peak consumer parallelism and throughput, with headroom; increasing later changes key mapping.
• What replaced ZooKeeper? — KRaft (Kafka Raft) metadata quorum; Kafka 4.0 removed ZooKeeper support.

Detailed explanation

A typical AWS reference pipeline: DMS or MSK feeds raw data into S3 (bronze), Glue or EMR Spark transforms it into Iceberg or Parquet (silver/gold) registered in the Glue Catalog, Athena and Redshift serve SQL, and MWAA orchestrates.

Real-world use case

A logistics company lands IoT telemetry via Kinesis and Firehose into S3, runs hourly Glue jobs to curate Iceberg tables, and lets analysts query with Athena while finance uses Redshift. Lake Formation controls who can see driver PII.

Common mistakes and red flags

• Confusing Glue the catalog with Glue the ETL service.
• Not knowing Athena charges by data scanned, so formats and partitioning matter.
• Listing services with no explanation of how they connect.

Follow-up questions

• Glue vs EMR? — Glue for serverless, simpler jobs; EMR for heavy, tunable, long-running or non-Spark workloads.
• How do you reduce Athena cost? — Columnar formats, partitioning or clustering, compression, and selecting only needed columns.

Detailed explanation

Direction of travel: Microsoft positions Fabric as the strategic analytics platform. Many Synapse capabilities have Fabric equivalents, and new investment focuses there. Existing Synapse and ADF estates remain common in interviews, so knowing both is valuable.

A typical Azure reference pipeline: ADF (or Fabric Data Factory) copies data from on-prem and SaaS sources into ADLS Gen2 bronze, Azure Databricks or Fabric Spark builds Delta silver and gold tables, a SQL warehouse layer serves BI, and Power BI sits on top. Identity is handled through Entra ID with managed identities, so pipelines need no stored passwords. Secrets that must exist live in Key Vault. Purview scans the estate for classification and lineage. Private endpoints keep traffic off the public internet, which regulated customers usually require.

Real-world use case

An insurer uses ADF to copy on-prem SQL Server tables into ADLS Gen2 nightly, Azure Databricks to build Delta silver and gold tables, and Power BI for reporting. Purview tracks lineage from source to report.

Common mistakes and red flags

• Confusing ADLS Gen1 (retired) with Gen2.
• Calling ADF a transformation engine for heavy logic. It orchestrates, and its data flows run on Spark under the hood.
• Not knowing Fabric exists.

Follow-up questions

• What is OneLake? — Fabric’s single, tenant-wide logical lake built on ADLS, storing tables in Delta format.
• How do you run on-prem extracts with ADF? — With a self-hosted integration runtime installed inside the network.

Detailed explanation

Notable BigQuery points for interviews

• Separation of storage and compute. Slots are the compute units.
• Partitioned tables (by date or integer range) plus clustering (up to four columns) to cut bytes scanned.
• On-demand pricing charges per bytes processed, so SELECT * on wide tables is costly.

Real-world use case

A media company sends app events via Pub/Sub with a BigQuery subscription straight into a raw table. Dataflow handles complex streaming enrichment, and Composer runs nightly dbt models in BigQuery.

Common mistakes and red flags

• Thinking BigQuery has indexes for performance (clustering and partitioning do that job).
• Writing SELECT * in BigQuery cost discussions.
• Not knowing Dataflow means Apache Beam.

Follow-up questions

• Dataflow vs Dataproc? — Dataflow for serverless Beam pipelines, especially streaming; Dataproc for existing Spark code and more control.
• How do you limit BigQuery cost? — Partition filters (optionally required), clustering, selecting columns, and slot reservations or quotas.

Detailed explanation

Why lakes use object storage

• Cheap, durable (designed for 11 nines of durability on S3), and elastic.
• Compute and storage scale independently.
• Every engine can read it.

Constraints to design around

• No atomic rename of directories, and listing can be slow. Table formats solve commit semantics with metadata (see How do open table formats make ACID possible on object storage?).
• Per-request costs and latency, which make small files expensive (see What is the small-files problem, and how do you fix it?).
• Strong read-after-write consistency now on S3 (since 2020), GCS and ADLS.

Real-world use case

A Spark cluster stores tables in S3 (object), uses local NVMe or EBS for shuffle spill (block), and mounts EFS (file) only for a legacy model-scoring library that expects a shared directory.

Common mistakes and red flags

• Treating S3 like a file system with cheap renames.
• Putting a transactional database on object storage.
• Not knowing why ADLS Gen2’s hierarchical namespace matters (atomic directory operations).

Follow-up questions

• What is the hierarchical namespace in ADLS Gen2? — It adds real directories with atomic rename and ACLs on top of Blob storage.
• Why is listing slow? — Object stores are flat key spaces; listing a prefix with millions of keys requires many paginated API calls.

Detailed explanation

Core building blocks

• Ownership: every dataset has an accountable owner (business) and a technical owner (engineering team).
• Stewardship: data stewards maintain definitions, resolve quality issues and approve access.
• Catalog: searchable metadata such as descriptions, schemas, owners, tags, popularity and lineage. Tools: Unity Catalog, Snowflake Horizon, Microsoft Purview, Google Dataplex, Collibra, Alation, Atlan, DataHub, OpenMetadata.
• Business glossary: agreed definitions (“active customer”, “net revenue”) linked to tables and metrics.
• Classification and tagging: PII, PHI, confidential and public tags. These drive masking and access policies (see How do you protect PII: masking, tokenisation, hashing, encryption, Tag-based masking and sensitive data classification).
• Access control: RBAC or ABAC policies, least privilege, access reviews (see RBAC vs ABAC).
• Quality standards: SLAs and checks (see What are the dimensions of data quality?–What are data contracts, and how do you enforce them?).
• Lineage: source-to-report traceability (see What is data lineage, and how is it captured?).
• Retention and lifecycle: legal holds, deletion schedules, privacy requests (see How does GDPR “right to erasure” affect lake and warehouse design?).
• Auditing: who accessed what and when.

Data engineer’s role

• Build governance into pipelines: register tables in the catalog, apply tags at creation, enforce masking by policy, emit lineage, and automate access requests through code (IaC, grants in version control).
• Make the governed path the easiest path, so people don’t bypass it.

Real-world use case

A healthcare company tags PHI columns at ingestion. Masking policies keyed on the tag automatically hide those values from non-clinical roles in every downstream table, and Purview lineage proves to auditors which reports use PHI.

Common mistakes and red flags

• Treating governance as just “a catalog tool”.
• Governance with no owners or enforcement.
• Seeing governance purely as a blocker rather than an enabler of trust and self-service.

Follow-up questions

• How do you keep catalog descriptions current? — Generate them from code (dbt docs, YAML) and make documentation part of code review.
• What is a data owner vs a data steward? — The owner is accountable for the data and decisions about it; the steward manages its definitions and quality day to day.

Detailed explanation

In transit

• TLS (1.2+) on all connections: JDBC/ODBC drivers, APIs, Kafka (SSL listeners), storage endpoints.
• Enforce TLS-only bucket policies, and private networking (PrivateLink, private endpoints) to keep traffic off the public internet.

At rest

• Cloud object storage and managed warehouses encrypt by default (AES-256 is typical).
• Options: provider-managed keys (default), customer-managed keys (CMK) in the provider’s KMS, or customer-supplied or external keys for strict regimes.
• Envelope encryption: each data object is encrypted with a data encryption key (DEK), and the DEK is encrypted with a key encryption key (KEK) stored in KMS. Rotating the KEK doesn’t require re-encrypting all data.
• Platform examples: Snowflake encrypts all data with a hierarchical key model and offers Tri-Secret Secure (customer-managed key combined with Snowflake’s key) on Business Critical edition. Databricks supports customer-managed keys for managed services and workspace storage.

Key management practices

• Separation of duties: key administrators are not data administrators.
• Automatic rotation (for example yearly for KMS keys), audit logs of key usage, and least-privilege key policies.
• Revoking a CMK can make data unreadable, which is a deliberate “kill switch” but also a risk if done by mistake.

Column-level encryption adds protection beyond disk encryption, because disk encryption doesn’t stop a user with table access from reading values (see How do you protect PII: masking, tokenisation, hashing, encryption).

Real-world use case

A bank requires CMKs for its lakehouse storage, with keys in its own KMS account, so it can revoke access to all data instantly in a security incident. TLS plus private endpoints secure all warehouse connections.

Common mistakes and red flags

• Thinking at-rest encryption protects against authorised users misusing data.
• Hardcoding encryption keys in code.
• Not knowing what envelope encryption is.

Follow-up questions

• What does key rotation do to existing data? — With envelope encryption, old DEKs are re-wrapped or remain decryptable through retained key versions; data isn’t necessarily rewritten.
• Is S3 encrypted by default? — Yes; since January 2023, new objects get server-side encryption (SSE-S3) by default.

Detailed explanation

Hierarchy of good practice

1. No secret at all: use workload identity. For example an EC2 or EKS IAM role reading S3, Azure managed identity, GCP service accounts, Snowflake storage integrations, or OIDC federation from CI (GitHub Actions to cloud roles).
2. Short-lived tokens issued by an identity provider (OAuth, STS).
3. Managed secrets with rotation for anything static (API keys, database passwords, Snowflake key-pair private keys).

Operational rules

• Restrict who can read each secret, and audit reads.
• Rotate regularly and after staff changes, and automate rotation where the provider supports it.
• Redact in logs. Many platforms (Databricks, Airflow) mask known secret values in output, but don’t rely on that alone.
• Separate secrets per environment (dev, test, prod).
• Use pre-commit hooks and secret scanners (for example gitleaks, GitHub secret scanning) to block accidental commits.
• If a secret leaks: revoke it immediately, rotate it, check access logs, and scrub history.
• Keep secrets out of job parameters and notebook widgets, which are often visible in run histories and UIs.

Example

# Runtime: Python 3.11 with boto3 — fetch a secret at runtime instead of hardcoding
import json
import boto3

def get_db_credentials(secret_name: str, region: str = "us-east-1") -> dict:
    client = boto3.client("secretsmanager", region_name=region)
    response = client.get_secret_value(SecretId=secret_name)
    return json.loads(response["SecretString"])

creds = get_db_credentials("prod/orders-db/readonly")
# use creds["username"], creds["password"] to connect; never print or log them

Real-world use case

A leaked Snowflake password in a public notebook prompted a move to key-pair authentication for service users. Private keys now live in Secrets Manager, CI uses OIDC, and network policies restrict service users to known IP ranges.

Common mistakes and red flags

• Storing passwords in Airflow Variables in plain text, or in .env files committed to Git.
• Sharing one service credential across all environments.
• Printing connection strings in logs for debugging.

Follow-up questions

• How does Airflow handle secrets? — Connections and variables can be served from a secrets backend (AWS, GCP, Azure, Vault) instead of the metadata DB.
• What is OIDC federation in CI? — The CI job gets a short-lived token from the cloud provider by proving its identity, with no stored keys.

Detailed explanation

What goes in version control

• Transformation code (SQL, dbt, PySpark, Python), orchestration definitions (DAGs, job YAML), infrastructure (Terraform; see Infrastructure as Code (Terraform) for data platforms), and access grants and policies.

Typical CI stages (on every pull request)

1. Static checks: formatting and linting (SQLFluff, ruff), type checks, secret scanning.
2. Unit tests: pure-function and transformation tests on small fixtures (pytest, dbt unit tests).
3. Build and integration tests: run changed models plus their downstream models in an isolated CI schema, often with dbt slim CI (see Slim CI with state:modified and --defer) or zero-copy clones (see Zero-copy cloning).
4. Data diff: compare outputs between production and the branch (row counts, key metrics).
5. Review and approval.

Typical CD stages

• Merge to main deploys to dev or test automatically, then to production with an approval gate or automatically after tests.
• Use deployment tools such as Declarative Automation Bundles for Databricks (see Declarative Automation Bundles (formerly Databricks Asset Bundles) for CI/CD), Snowflake CLI or schemachange for Snowflake, dbt platform jobs, and Terraform plan/apply.
• Strategies for safe releases: blue-green or Write-Audit-Publish (see Write-Audit-Publish and blue-green deployments for data), feature flags, and backward-compatible schema changes.

What’s different from app CI/CD

• Data has state: you can’t just redeploy a table, so migrations and backfills must be planned.
• Testing needs representative data, which is costly and sensitive. Use samples, clones or synthetic data.
• Deploys can trigger expensive rebuilds, so selective builds matter.

Example

# GitHub Actions: minimal dbt CI on pull request
name: dbt-ci
on: [pull_request]
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.11"
      - run: pip install dbt-snowflake sqlfluff
      - run: sqlfluff lint models/
      - run: dbt deps
      - run: dbt build --select state:modified+ --defer --state ./prod-artifacts --target ci
        env:
          SNOWFLAKE_PRIVATE_KEY: ${{ secrets.SNOWFLAKE_CI_PRIVATE_KEY }}

Real-world use case

A team that used to deploy SQL by copying it into the warehouse UI moved to Git plus dbt CI. Broken references and failing tests are now caught in the pull request, and production incidents from deploys dropped markedly.

Common mistakes and red flags

• “We test in production.”
• CI that only checks syntax, not data.
• Manual production changes outside version control.

Follow-up questions

• How do you handle a breaking schema change in CD? — Make it backward compatible (add then remove), coordinate with consumers, and run the migration as a planned step.
• How do you keep CI cheap? — Build only modified models, defer unchanged ones to production, use small warehouses and auto-drop CI schemas.

In depth: What does CI/CD look like for data pipelines?

Detailed explanation

Good practices for data repos

• Small pull requests: one model or feature each.
• Branch protection on main: required CI checks and at least one reviewer.
• Conventional commit messages or pull-request templates that cover what changed, impact and how it was tested.
• Promote by deploying the same commit to each environment (see Dev/test/prod environments and promoting data code) rather than merging between environment branches.
• Tag releases if consumers need version references (for example dbt packages).
• Keep secrets and data out of the repo, and use .gitignore for target/, logs/ and local profiles.

Example

# Typical short-lived feature flow
git switch -c feature/add-refund-model
# ... edit models/marts/fct_refunds.sql and tests ...
git add models/marts/fct_refunds.sql models/marts/_marts.yml
git commit -m "feat(marts): add fct_refunds with grain tests"
git push -u origin feature/add-refund-model
# open PR -> CI runs -> review -> squash merge to main -> CD deploys

Real-world use case

A team with dev, qa and prod branches spent days each sprint resolving merge conflicts and cherry-picking hotfixes. Switching to trunk-based development, with environment promotion by pipeline, cut release effort to minutes.

Common mistakes and red flags

• Long-lived branches that diverge for weeks.
• Committing directly to main without review.
• Environment branches that drift from each other.

Follow-up questions

• How do you ship unfinished work? — Keep it disabled with feature flags or config (for example dbt enabled: false), or behind a non-public model access level.
• Squash vs merge commits? — Squash keeps main history readable; either is fine if consistent.

Detailed explanation

Inmon (Corporate Information Factory). Source data is integrated into a central enterprise data warehouse in third normal form. That layer is the single, consistent version of the truth. Departmental data marts, usually dimensional, are built downstream for reporting. The approach is strong on integration and consistency, but it needs substantial upfront modelling, and the business waits longer for its first reports.

Kimball (Dimensional Bus Architecture). You pick one business process (orders, shipments, payments) and build a fact table at a declared grain, surrounded by dimension tables. Each new process adds another star. Shared dimensions such as dim_customer and dim_date are conformed: built once and reused, so metrics from different stars can be compared. The bus matrix (processes × dimensions) is the planning tool. You get faster delivery and business-friendly models, but you need discipline to keep dimensions conformed.

Most modern stacks are a hybrid. A raw or staging layer lands source data, an integration layer (sometimes Data Vault, see Data Vault 2.0: hubs, links and satellites) cleans and conforms it, and a Kimball-style presentation layer serves BI. The medallion architecture (bronze/silver/gold) maps to the same idea.

Real-world use case

A retailer starts with a Kimball sales star to unblock finance reporting in six weeks. It then adds inventory and returns stars that reuse dim_product and dim_store, so margin-after-returns can be calculated across processes.

Common mistakes and red flags

• Saying “Kimball means no normalisation anywhere”. Staging layers are often normalised.
• Not mentioning conformed dimensions when describing Kimball.
• Treating the choice as all-or-nothing instead of describing a layered hybrid.

Follow-up questions

• What is a bus matrix? — A grid of business processes against the dimensions they share. It guides which dimensions must be conformed.
• Which do you use today? — Usually a layered hybrid: integrated silver layer, dimensional gold layer.

Detailed explanation

Transactional facts are the most common. Each row is an atomic event at a fixed moment: a payment, a click, an order line. Rows are inserted and rarely updated. They are the most granular and flexible type, because any time-based rollup can be computed from them.

Periodic snapshot facts capture a measurement at a regular cadence, whether or not anything happened. Examples: end-of-day inventory per product per warehouse, or month-end account balance. They answer “what was the level?” questions cheaply. Rebuilding a balance from millions of transactions every time is expensive. Their measures are often semi-additive: you can sum across warehouses but not across days (see What are additive, semi-additive and non-additive measures?).

Accumulating snapshot facts model a process with a defined start and end and several milestones, such as order placed → paid → shipped → delivered. There is one row per order, with a date key per milestone and lag measures like days_to_ship. The row is updated as each milestone happens, which makes this the only fact type that is routinely updated in place.

Example

-- Dialect: ANSI SQL
-- Accumulating snapshot: one row per order, milestone dates filled in over time
CREATE TABLE fct_order_fulfilment (
    order_key          BIGINT       NOT NULL,
    customer_key       BIGINT       NOT NULL,
    placed_date_key    INT          NOT NULL,
    paid_date_key      INT,
    shipped_date_key   INT,
    delivered_date_key INT,
    days_placed_to_ship INT,
    days_ship_to_deliver INT,
    order_amount       DECIMAL(12,2) NOT NULL
);

Real-world use case

A logistics team uses an accumulating snapshot to track where shipments stall. Finance uses a periodic snapshot of accounts receivable each month-end. Both are derived from the same transactional event tables.

Common mistakes and red flags

• Summing a balance from a periodic snapshot across days.
• Building an accumulating snapshot for a process with no clear end.
• Not knowing that accumulating snapshots require updates or MERGE, which affects load design.

Follow-up questions

• How do you load an accumulating snapshot? — MERGE on the process key, filling milestone columns as events arrive.
• Can you derive a periodic snapshot from transactions? — Yes, with a running sum per entity over a date spine, but storing it saves repeated compute.

Detailed explanation

Classifying measures correctly prevents some of the most common BI bugs.

• Additive: quantity, revenue, cost. Summing them by day, product, region or any combination gives a correct answer. Transactional fact tables mostly hold additive measures.
• Semi-additive: stock on hand, account balance, headcount. Summing across warehouses for one day is valid. Summing across days is not: three days of a 100-unit balance is not 300 units. Over time you use the last value in the period (closing balance), an average or a minimum or maximum instead.
• Non-additive: margin percentage, unit price, conversion rate, COUNT(DISTINCT customer_id). Adding two daily conversion rates gives nonsense. Two days’ distinct customers can overlap, so their sum overcounts.

The safe pattern for non-additive measures is to store the additive components and compute the ratio at query time. Store revenue and cost, then compute (revenue - cost) / revenue after aggregating. Never average pre-computed percentages unless the weights are equal.

Example

-- Dialect: ANSI SQL
-- Wrong: averaging daily margin percentages weights small days the same as big ones
SELECT AVG(margin_pct) AS margin_pct_wrong
FROM daily_store_margin;

-- Right: aggregate components first, then divide
SELECT (SUM(revenue) - SUM(cost)) / NULLIF(SUM(revenue), 0) AS margin_pct
FROM daily_store_margin;

Sample: Day 1 revenue 100, cost 50 (50%); Day 2 revenue 1,000, cost 900 (10%). Wrong answer = 30%. Correct answer = (1,100 − 950) / 1,100 = 13.6%.

Real-world use case

A subscription business reports monthly active users. Data engineers store the per-user activity facts and compute distinct counts per period. Summing daily active users into a monthly figure would inflate it.

Common mistakes and red flags

• Summing balances or inventory over time.
• Pre-computing ratios in the fact table and letting BI tools sum or average them.
• Not mentioning NULLIF or divide-by-zero handling when computing ratios.

Follow-up questions

• How do semantic layers handle this? — Metrics are defined as ratios of aggregated components, so tools compute them correctly at any grain (see What is a semantic/metrics layer?).
• How do you combine distinct counts across partitions cheaply? — Use mergeable sketches such as HyperLogLog (see Approximate aggregation (APPROX_COUNT_DISTINCT, HyperLogLog)).

Detailed explanation

Imagine the sales team and the support team each build their own customer table. Sales calls a customer “active” if they ordered in 90 days. Support uses “has an open contract”. The surrogate keys differ too. Now nobody can reliably answer “revenue per support ticket by customer segment”, because the two stars cannot be joined on a common customer.

A conformed dimension fixes this. dim_customer is built once, owned by one team, and reused by fct_orders, fct_tickets and fct_subscriptions. Conformance can be:

• Identical: the same physical table referenced by every fact.
• Shrunken (rolled-up): a subset of rows or a higher-grain version, such as dim_month derived from dim_date, with attribute names and values kept consistent.

The bus matrix lists business processes as rows and dimensions as columns, with a tick where they intersect. It shows you:

• which dimensions are reused most and should be built and conformed first;
• the integration scope before any tables exist;
• where drill-across reports will be possible.

Example

-- Dialect: ANSI SQL
-- Drill across two facts using the conformed customer dimension
WITH rev AS (
    SELECT customer_key, SUM(net_amount) AS revenue
    FROM fct_orders GROUP BY customer_key
),
tix AS (
    SELECT customer_key, COUNT(*) AS ticket_count
    FROM fct_tickets GROUP BY customer_key
)
SELECT c.segment,
       SUM(rev.revenue)                        AS revenue,
       SUM(COALESCE(tix.ticket_count, 0))      AS tickets
FROM dim_customer c
LEFT JOIN rev ON rev.customer_key = c.customer_key
LEFT JOIN tix ON tix.customer_key = c.customer_key
GROUP BY c.segment;

Aggregating each fact separately before joining avoids the fan-out that a direct fact-to-fact join causes (see Diagnose a join that multiplies rows (fan-out)).

Real-world use case

A telecom company conforms dim_customer, dim_date and dim_plan across billing, network usage and churn facts. Leadership can then see revenue, usage and churn risk per plan in one report.

Common mistakes and red flags

• Joining two fact tables directly instead of through conformed dimensions.
• Letting each team build its own copy of a core dimension.
• Not knowing what a shrunken dimension is.

Follow-up questions

• Who should own a conformed dimension? — A single team or data product owner with change control, because many facts depend on it.
• How do you conform IDs from different source systems? — Build a mapping or master-data step that assigns one surrogate key per real-world entity.

Detailed explanation

Kimball’s four-step design process is: choose the business process, declare the grain, identify the dimensions, identify the facts. The grain comes second for a reason. It decides which dimensions are valid and which measures can live in the table.

A good grain statement is specific and testable:

• Vague: “sales data”.
• Precise: “one row per product scanned on a receipt at a store register”.

Once declared:

• Dimensions must have exactly one value per row at that grain. A receipt line has one product, one store, one date and one cashier. “Promotion” may need a bridge if a line can have several.
• Measures must be true at that grain. Line quantity and line amount fit. Receipt-level tax or order-level shipping cost does not, unless you allocate it down to lines.
• The primary key is usually the combination of dimension keys plus a degenerate identifier (receipt number and line number). You test that combination for uniqueness.

Prefer the most atomic grain the source supports. Atomic data can always be rolled up. Aggregated data cannot be broken back down, and new questions (“which basket combinations sell together?”) will need the detail.

Mixed grains are the classic failure: order headers and order lines in the same table, with header-level freight repeated on each line. Summing freight then overcounts it by the number of lines.

Example

-- Dialect: ANSI SQL
-- Test that the declared grain (order_id, line_number) is actually unique
SELECT order_id, line_number, COUNT(*) AS row_count
FROM fct_order_lines
GROUP BY order_id, line_number
HAVING COUNT(*) > 1;
-- Any rows returned mean the grain is violated (duplicates or mixed grain)

Real-world use case

An airline declares its booking fact as “one row per passenger per flight segment”. Fare per segment is a valid measure. Baggage fees, charged per booking, go to a separate booking-level fact so they are not repeated across segments.

Common mistakes and red flags

• Picking dimensions first and discovering the grain later.
• Storing header-level measures on line-level rows.
• Not having a uniqueness test on the grain columns.

Follow-up questions

• What if the business only needs daily totals? — Still keep an atomic fact if it is affordable, and build the daily aggregate on top of it.
• How do you handle header-level charges? — Allocate them to lines with a documented rule, or keep a separate fact at header grain.

Detailed explanation

Table design

• customer_key: surrogate key, unique per version.
• customer_id: natural key.
• Tracked attributes, for example city and segment.
• valid_from / valid_to: the effective range. Use a far-future date such as 9999-12-31 for the open row rather than NULL, so range joins are simpler.
• is_current: a convenience flag for “latest version” queries.
• Optional row_hash: a hash of the tracked attributes for cheap change detection.

Load logic (per batch)

1. Stage the incoming snapshot or the changed rows and compute row_hash.
2. Expire: for natural keys where the hash differs from the current row, set valid_to to the change timestamp and is_current = FALSE.
3. Insert: add a row for each new natural key and each changed key, with valid_from set to the change timestamp, the open-ended valid_to and is_current = TRUE.
4. Run steps 2 and 3 in one transaction (or one MERGE) so readers never see a key with zero or two current rows.

Decisions to make explicitly

• Effective timestamp source: use the source’s change time when available. The load time is a fallback and distorts history when loads are late.
• Deletes: close the row, and optionally insert a tombstone version with is_deleted = TRUE.
• Late or out-of-order changes: a change older than the current version means splitting an existing range. This is rarer and needs a reprocessing path (see SCD Type 2 on a 500-million-row customer dimension).

Example

-- Dialect: Snowflake SQL
-- Step 1: expire current rows whose tracked attributes changed
UPDATE dim_customer d
SET valid_to = s.changed_at,
    is_current = FALSE
FROM stg_customer_changes s
WHERE d.customer_id = s.customer_id
  AND d.is_current = TRUE
  AND d.row_hash <> s.row_hash;

-- Step 2: insert new versions (changed keys) and brand-new keys
INSERT INTO dim_customer
    (customer_key, customer_id, city, segment, row_hash, valid_from, valid_to, is_current)
SELECT MD5(s.customer_id || '|' || TO_VARCHAR(s.changed_at)),
       s.customer_id, s.city, s.segment, s.row_hash,
       s.changed_at, '9999-12-31'::TIMESTAMP_NTZ, TRUE
FROM stg_customer_changes s
LEFT JOIN dim_customer d
       ON d.customer_id = s.customer_id
      AND d.is_current = TRUE
WHERE d.customer_id IS NULL;   -- no current row: new key, or just expired in step 1

Wrap both statements in BEGIN ... COMMIT so they apply atomically.

Sample: C-1 is current in Pune. A change arrives moving it to Mumbai at 2026-03-01. After the load there are two rows: Pune (valid_to = 2026-03-01, is_current = FALSE) and Mumbai (valid_from = 2026-03-01, is_current = TRUE).

Real-world use case

An insurer keeps policyholder risk tier as SCD2. Claims facts look up the tier valid on the claim date, so loss ratios by tier reflect the tier customers actually had at the time.

Common mistakes and red flags

• Running expire and insert as separate, non-atomic jobs.
• Using load time as valid_from when the source provides a change timestamp.
• Creating a new version when nothing tracked changed (no hash comparison).
• Overlapping or gapped validity ranges for the same natural key.

Follow-up questions

• How do you test an SCD2 table? — Exactly one current row per natural key, no overlapping ranges, and valid_from < valid_to.
• How does dbt do this? — Snapshots implement the same pattern (see Snapshots: timestamp vs check strategy (See also Q12)). On Databricks, AUTO CDC can maintain it (see AUTO CDC (formerly APPLY CHANGES) for SCD Type 1/2 in pipelines).

Detailed explanation

Junk dimension. Transactions often carry yes/no flags and small codes: is_gift, payment_type, is_expedited. Adding each as a fact column clutters the fact table. Creating a dimension for each is overkill. A junk dimension holds every valid combination (2 × 4 × 2 = 16 rows here), and the fact stores one order_flags_key.

Degenerate dimension. Order number, invoice number or ticket ID identify a group of fact rows, but every attribute about them already lives in other dimensions. Keeping the number on the fact lets you group lines into orders and trace back to the source, with no join needed.

Role-playing dimension. An order fact may have order_date_key, ship_date_key and delivery_date_key, all referencing dim_date. You do not copy the table three times. You join it three times with aliases, or expose role-specific views (dim_ship_date) so BI users see clear column names.

Example

-- Dialect: ANSI SQL
-- Role-playing date dimension joined twice with aliases
SELECT od.calendar_month AS order_month,
       sd.calendar_month AS ship_month,
       COUNT(DISTINCT f.order_number) AS orders   -- order_number is degenerate
FROM fct_order_lines f
JOIN dim_date od ON f.order_date_key = od.date_key
JOIN dim_date sd ON f.ship_date_key  = sd.date_key
GROUP BY od.calendar_month, sd.calendar_month;

Real-world use case

A food-delivery company has 12 boolean flags per order (contactless, scheduled, promo applied and so on). A junk dimension of the observed combinations, a few hundred rows, replaced 12 columns on a billion-row fact, and BI filters stayed simple.

Common mistakes and red flags

• Building a dimension table for order number that just repeats the ID.
• Physically duplicating the date dimension for every role.
• Generating every theoretical flag combination when only a fraction ever occur. Either approach can work, but explain the choice.

Follow-up questions

• How do you populate a junk dimension? — Pre-generate all combinations, or insert new combinations as they first appear during loads.
• Do degenerate dimensions break star-schema rules? — No; Kimball explicitly allows them.

Detailed explanation

Not every business process produces a number. Two common patterns:

1. Event-tracking factless facts record occurrences. Examples: student attendance (student, class, date), website logins, or a patient visit with no billed amount. The question is “how many?”, answered with COUNT(*).

2. Coverage factless facts record what could have happened, so you can find what didn’t. Example: a promotion coverage table listing every product × store × day under promotion. Comparing it with the sales fact shows promoted products that sold nothing, which you cannot see from sales data alone, because missing sales leave no rows.

Some designers add a constant event_count = 1 column so BI tools have something to sum. That is a convenience; the table is still factless in spirit.

Design notes

• Declare the grain as carefully as for any fact (“one row per student per class session attended”), and test the key combination for uniqueness.
• Coverage tables can be generated from planning data (promotion calendars, class schedules) rather than from transactions, which is exactly why they reveal non-events.
• Keep coverage tables scoped. Generating every product × store × day combination “just in case” creates billions of rows nobody queries.

Example

-- Dialect: ANSI SQL
-- Promoted products that had zero sales: coverage fact anti-joined to sales fact
SELECT pc.date_key, pc.store_key, pc.product_key
FROM fct_promo_coverage pc
LEFT JOIN fct_sales s
       ON s.date_key    = pc.date_key
      AND s.store_key   = pc.store_key
      AND s.product_key = pc.product_key
WHERE s.product_key IS NULL;

Real-world use case

A university uses an enrolment factless fact (student × course × term) to count enrolments by department. It also uses a course-offering coverage fact to find courses that were offered but had no enrolments.

Common mistakes and red flags

• Saying “a fact table without facts is a dimension”.
• Trying to find non-events (zero sales) from the sales fact alone.
• Not knowing the coverage pattern.

Follow-up questions

• How big can coverage tables get? — Very large (products × stores × days), so restrict them to the relevant scope, for example only promoted items.
• How do you measure attendance rate? — Divide attendance-event counts by a coverage count of scheduled sessions.

Detailed explanation

Columnar warehouses read only the columns a query touches and compress repeated values well, so wide tables with hundreds of columns are practical. That made OBT popular for BI extracts, ML feature tables and self-service analytics.

A pragmatic pattern: keep the dimensional model as the source of truth and generate OBTs from it for specific consumers (a dashboard, a feature store). The OBT is then a derived, disposable artefact. Business rules live in the dimensions, so consistency is preserved.

Watch the grain. An OBT is still at a single grain (for example order line). Adding customer-level aggregates like “lifetime value” is fine if they are documented as repeated per row and not meant to be summed.

Example

-- Dialect: Snowflake SQL
-- OBT built from the dimensional model, not from raw sources
CREATE OR REPLACE TABLE obt_order_lines AS
SELECT f.order_number, f.line_number, f.quantity, f.net_amount,
       d.calendar_date, d.fiscal_quarter,
       c.customer_id, c.segment, c.country,
       p.sku, p.brand, p.category
FROM fct_order_lines f
JOIN dim_date d     ON f.order_date_key = d.date_key
JOIN dim_customer c ON f.customer_key   = c.customer_key
JOIN dim_product p  ON f.product_key    = p.product_key;

Real-world use case

A marketing team uses a self-service BI tool with weak join support. Data engineering publishes a nightly obt_campaign_performance, derived from conformed dimensions, so the team can filter and pivot freely without misjoining tables.

Common mistakes and red flags

• Building OBTs directly from raw data with business logic duplicated in each one.
• Summing repeated customer-level metrics in an order-line OBT.
• Claiming OBT eliminates the need for modelling.

Follow-up questions

• How do you keep OBTs in sync with dimension changes? — Rebuild them from the dimensional layer on each run, or incrementally on changed keys.
• Is OBT good for ML? — Yes, as a feature table, but point-in-time correctness matters (see Build point-in-time-correct training data for an ML model).

Detailed explanation

Event data is high-volume, append-only, often late or duplicated, and its schema evolves as product teams add properties.

Raw layer (bronze)

• Store each event as received: event_id, event_name, event_ts (client time), received_ts (server or ingestion time), user_id, anonymous_id, session_id, properties (JSON/VARIANT), context (device, app version).
• Never mutate it. It is your replay source.

Clean layer (silver)

• Deduplicate on event_id. Clients retry, so duplicates are normal (see Delete duplicates but keep one row).
• Type and flatten the important properties into columns, for example order_id and amount for checkout_completed. Keep less important properties in the semi-structured column.
• Identity stitching: map anonymous_id to user_id after login, usually through an identity mapping table.
• Split very different event types into their own tables, or use one narrow event table with a typed column per common property. Both work. Choose based on how consumers query.

Derived layer (gold)

• Sessions: group events per user with an inactivity gap (see Sessionise events with a 30-minute inactivity gap).
• Funnels and conversion facts, and daily user activity snapshots for DAU/MAU.

Physical design

• Partition or cluster by event date. High-cardinality IDs are poor partition keys (see How do you choose a partition key, and what is over-partitioning?).
• Use event time for business logic and ingestion time for incremental loading (see Event time vs processing time vs ingestion time).
• Govern schema with a tracking plan or data contract (see What are data contracts, and how do you enforce them?).

Example

-- Dialect: Snowflake SQL
-- Silver: dedupe and type the checkout events from the raw VARIANT payload
CREATE OR REPLACE TABLE slv_checkout_completed AS
SELECT event_id,
       user_id,
       event_ts,
       properties:order_id::STRING        AS order_id,
       properties:amount::NUMBER(12,2)    AS amount,
       properties:currency::STRING        AS currency
FROM raw_events
WHERE event_name = 'checkout_completed'
QUALIFY ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_ts) = 1;

Real-world use case

A mobile app sends 300 event types. The team types the 25 that drive KPIs into silver tables and keeps the rest queryable through the raw VARIANT column. That kept pipeline maintenance small while still supporting ad-hoc analysis.

Common mistakes and red flags

• Partitioning by user_id.
• Using ingestion time for business metrics such as daily revenue.
• No deduplication, assuming the SDK sends each event exactly once.

Follow-up questions

• How do you handle a renamed property? — Map old and new names in silver with COALESCE, and enforce the tracking plan going forward.
• How do you deal with client clock skew? — Keep both client and server timestamps, and use server time for ordering when client time is clearly invalid.

Detailed explanation

How it works

1. Read the stored watermark, for example from a control table.
2. Extract WHERE updated_at > :last_watermark (and optionally <= :current_upper_bound).
3. Load the batch into the target.
4. Only after a successful load, save the new watermark as the max value actually extracted.

Pitfalls and fixes

• Late commits: a transaction stamps updated_at = 10:00:00 but commits at 10:05. If your extract ran at 10:02 and saved 10:01 as the watermark, that row is skipped forever. Fix: subtract a safety overlap (re-read the last few minutes) and dedupe on load, or switch to log-based CDC.
• Columns not updated by every change: some application paths or bulk scripts update rows without touching updated_at. Fix: database triggers, CDC, or periodic reconciliation.
• Deletes: a deleted row has no updated_at to see. Fix: soft deletes or CDC.
• Clock skew and time zones: mixed server clocks or naive timestamps break ordering. Fix: use database-generated UTC timestamps.
• Equal timestamps at the boundary: with >, rows sharing the boundary timestamp that commit after the extract are lost. With >= you get duplicates. Fix: use >= plus idempotent MERGE or dedupe.
• Saving the watermark before the load succeeds: a failure then skips data. Fix: commit the watermark in the same transaction as the load, or only after it.
• Using now() as the new watermark instead of the max extracted value, which skips rows written during extraction.

Example

-- Dialect: Snowflake SQL
-- Extract with a 10-minute overlap; the target MERGE is idempotent so re-reads are safe
SET last_wm = (SELECT last_watermark FROM etl_control WHERE table_name = 'orders');

CREATE OR REPLACE TEMPORARY TABLE orders_batch AS
SELECT *
FROM src_orders
WHERE updated_at >= DATEADD(minute, -10, $last_wm::TIMESTAMP_NTZ);

-- ... MERGE orders_batch into target on order_id ...

UPDATE etl_control
SET last_watermark = (SELECT MAX(updated_at) FROM orders_batch)
WHERE table_name = 'orders'
  AND EXISTS (SELECT 1 FROM orders_batch);

Real-world use case

A team noticed about 0.1% of orders missing in the warehouse. The cause was long-running checkout transactions committing minutes after their updated_at. A 15-minute overlap with an idempotent MERGE closed the gap. The longer-term fix was moving to log-based CDC.

Common mistakes and red flags

• Assuming updated_at is always reliable.
• Updating the watermark before confirming the load.
• No reconciliation to detect silent misses (see An incremental load silently missed records).

Follow-up questions

• Can an auto-increment ID be a watermark? — For insert-only tables, yes, but IDs can commit out of order too, and it never captures updates.
• How do you detect missed rows? — Daily count and checksum comparisons between source and target per partition.

Detailed explanation

The three can differ by seconds or by days. A phone that was offline on a flight uploads yesterday’s events this morning.

• Event time: stamped by the producer. It is the truth for “how many orders happened on Monday?” It can arrive late and out of order, and client clocks can be wrong.
• Ingestion time: stamped by the broker or landing system (Kafka record timestamp in LogAppendTime mode, or a _loaded_at column). Roughly monotonic, so it is ideal for incremental pickup (“give me everything that landed since my last run”).
• Processing time: the clock of the processing engine. Deterministic results are impossible with it because rerunning gives different times. It is useful only for operational metrics and timeouts.

Why it matters

• Windowed aggregations by processing time change when you replay or when the system lags. By event time they are reproducible.
• Event-time processing needs a rule for how long to wait for late data. In streaming this is the watermark (see Watermarks and late data in Structured Streaming). In batch it is a lookback window (see Late-arriving data in incremental models (lookback windows)).
• Partitioning raw data by ingestion date and modelling by event date is a common, robust combination.

Example

-- Dialect: Snowflake SQL
-- Incremental pickup by ingestion time; aggregate by event time with a 3-day lookback
SELECT DATE_TRUNC('day', event_ts) AS event_date,
       COUNT(*)                    AS orders
FROM raw_order_events
WHERE _loaded_at > (SELECT last_loaded_at FROM etl_control WHERE table_name = 'order_events')
   OR DATE_TRUNC('day', event_ts) >= DATEADD(day, -3, CURRENT_DATE)
GROUP BY 1;

In practice you recompute only the affected event dates and MERGE them into the aggregate.

Real-world use case

A delivery app’s “orders per hour” dashboard used processing time and showed spikes every time the pipeline caught up after an outage. Switching to event time with a two-hour late-data allowance removed the false spikes.

Common mistakes and red flags

• Reporting business KPIs by load date.
• Assuming event time is always trustworthy, with no handling of future or very old timestamps.
• Not knowing what a watermark is.

Follow-up questions

• How do you handle events with timestamps in the future? — Cap or flag them, fall back to server time, and monitor the rate.
• What does Kafka store as the timestamp? — Either CreateTime (producer-set) or LogAppendTime (broker-set), depending on topic configuration.

Detailed explanation

Lakes make it easy to store anything, and that is the problem. Symptoms of a swamp:

• Hundreds of folders like /data/tmp_final_v2/ with no owner.
• The same dataset copied five times with slightly different logic.
• Unknown schemas and file formats, so every consumer reverse-engineers them.
• Stale data nobody deletes, with storage cost growing every month.
• PII sitting in places nobody knows about.

Prevention

1. Catalog everything. Register every dataset as a table in a catalog (Unity Catalog, Glue, Iceberg REST catalog) instead of reading raw paths. Add descriptions, owners and tags.
2. Standard layering and naming. Bronze/silver/gold (see What is the medallion (bronze/silver/gold) architecture?) with predictable paths and names.
3. Ownership. Every dataset has a team and a contact. Unowned data gets flagged and eventually archived.
4. Quality gates on promotion between layers (see How do you build data quality checks into a pipeline?).
5. Access control and classification. Tag PII, restrict raw zones, audit access.
6. Lifecycle and retention. Automatically tier or delete temp and old data (see Storage tiering and lifecycle policies).
7. Lineage, so consumers can see where data came from and producers can see who uses it (see What is data lineage, and how is it captured?).
8. Self-service with guardrails. Templates and platform tooling make the right way the easy way.

Real-world use case

A company found 40% of its S3 lake had no reads in 12 months. Tagging owners, archiving unread prefixes to cold storage and forcing new datasets through catalog registration cut storage cost and made discovery possible.

Common mistakes and red flags

• Blaming the technology rather than the missing process.
• Proposing a catalog without ownership or enforcement.
• Ignoring cost as a swamp symptom.

Follow-up questions

• How do you find unused data? — Storage access logs or catalog and query history showing tables with no reads in N days.
• How do you enforce registration? — Only let pipelines write through governed tables, and block direct writes to raw paths with IAM.

Detailed explanation

Where to check

• Ingestion / bronze: file arrived, row count in expected range, schema matches the contract, required fields parseable.
• Silver: primary key uniqueness, not-null on required columns, accepted values, referential integrity, deduplication verified.
• Gold: business rules (revenue ≥ 0, order total = sum of lines), reconciliation against source totals, and metric anomaly detection (today vs the last 4 same weekdays).

How to act on failures

• Blocking (error): stop the pipeline before publishing. Consumers keep seeing yesterday’s correct data rather than today’s wrong data.
• Warning: publish but alert, for issues like a slight increase in nulls on a non-critical field.
• Row-level quarantine: send invalid rows to a quarantine table with a reason code and publish the rest (see How do you handle bad records: quarantine tables and dead-letter queues). Use this when partial data is better than none.

Implementation options

• dbt tests (generic, singular, unit; see The four built-in generic data tests–Unit tests).
• Great Expectations, Soda or similar frameworks.
• Platform-native features: Lakeflow pipeline expectations (see Pipeline expectations for data quality), Snowflake data metric functions.
• Custom SQL assertions in the orchestrator.

Publishing pattern: build into a staging table, run checks, then atomically swap or publish (Write-Audit-Publish; see Write-Audit-Publish and blue-green deployments for data).

Example

-- Dialect: Snowflake SQL
-- Blocking check: fail the task if today's load reconciles poorly with the source
SELECT CASE
         WHEN ABS(t.total - s.total) / NULLIF(s.total, 0) > 0.001
         THEN TO_NUMBER('reconciliation_failed')   -- deliberate runtime error to fail the step
         ELSE 1
       END AS check_passed
FROM (SELECT SUM(amount) AS total FROM gold.fct_payments WHERE payment_date = CURRENT_DATE - 1) t,
     (SELECT SUM(amount) AS total FROM src_ledger_daily  WHERE ledger_date  = CURRENT_DATE - 1) s;

Most teams express this as a dbt test or orchestrator check rather than a forced cast error. The point is that the step fails loudly.

Real-world use case

A payments team added a gold reconciliation test against the ledger. In its first month it blocked two bad publishes caused by duplicated CDC events, and finance never saw wrong numbers.

Common mistakes and red flags

• Running tests after the data is already exposed to dashboards.
• Making every check blocking, which causes alert fatigue and constant outages.
• Alerts without an owner or runbook.

Follow-up questions

• How do you avoid alert fatigue? — Tier checks by criticality, deduplicate alerts, and tune thresholds from history.
• What do you do when a blocking check fails at 3 AM? — Follow the runbook: assess impact, fix or quarantine, rerun, and communicate (see The nightly pipeline failed at 3 AM; executive dashboards refresh at 7 AM).

In depth: What are the most important data-quality checks in production?

Detailed explanation

Tests (“known unknowns”)

• Written per table by engineers, versioned with the code, and run as part of the pipeline.
• Deterministic pass or fail, usually blocking.
• Limited to what someone anticipated.

Observability (“unknown unknowns”)

• Often described in five pillars: freshness, volume, schema, distribution, lineage.
• Uses metadata and statistical baselines, for example “this table normally gets 1.2M rows on Mondays; today 300k”.
• Detects anomalies across all tables without hand-written rules, and uses lineage to show impact and root cause.
• Includes pipeline signals: run durations, failures, retries and cost trends.

A mature setup combines them. Tests guard critical contracts. Observability watches everything else. Incidents found by observability become new tests.

Example

-- Dialect: Snowflake SQL
-- Simple volume anomaly: compare today's row count to the trailing 4-week same-weekday average
WITH daily AS (
    SELECT DATE(_loaded_at) AS load_date, COUNT(*) AS row_count
    FROM bronze_orders
    WHERE _loaded_at >= DATEADD(day, -35, CURRENT_DATE)
    GROUP BY 1
)
SELECT d.load_date, d.row_count,
       AVG(h.row_count) AS baseline,
       d.row_count / NULLIF(AVG(h.row_count), 0) AS ratio
FROM daily d
JOIN daily h
  ON DAYOFWEEK(h.load_date) = DAYOFWEEK(d.load_date)
 AND h.load_date < d.load_date
 AND h.load_date >= DATEADD(day, -28, d.load_date)
WHERE d.load_date = CURRENT_DATE
GROUP BY d.load_date, d.row_count;

Real-world use case

All tests passed, but an upstream change halved the volume of a marketing events table. A volume monitor flagged it within an hour, lineage showed three affected dashboards, and the team notified stakeholders before the Monday business review.

Common mistakes and red flags

• Thinking observability tools replace tests.
• Monitoring pipeline success only, not the data itself.
• No link from alerts to owners and downstream impact.

Follow-up questions

• What are the five pillars? — Freshness, volume, schema, distribution and lineage.
• How do you reduce false positives? — Seasonality-aware baselines, minimum thresholds, and feedback loops to tune monitors.

Detailed explanation

Bad records include unparseable JSON, missing required fields, invalid types, unknown reference codes and business-rule violations. You have three choices:

1. Fail the pipeline. Right for systemic problems (the schema changed) or critical data where partial output is worse than none.
2. Drop silently. Almost never acceptable, because you lose data and visibility.
3. Quarantine / dead-letter. The default for isolated row-level issues.

Good quarantine design

• Keep the raw payload exactly as received, so you can reprocess it.
• Add error_code, error_message, pipeline_name, source_file or offset, and quarantined_at.
• Keep a threshold: if quarantined rows exceed, say, 1% of the batch, fail the pipeline, because something systemic is wrong.
• Alert on volume and report per error type.
• Provide a replay path that reinserts corrected rows idempotently.

In streaming (Kafka), a dead-letter topic receives messages that fail deserialisation or processing after N retries, so a “poison pill” message doesn’t block the partition. Make sure each failure type has an owner. Vendor-format issues go back to the vendor, and code bugs go to the pipeline team.

Example

# Runtime: PySpark 3.5
from pyspark.sql import functions as F

# raw_df columns: value STRING (JSON), _source_file STRING
schema = "order_id STRING, amount DECIMAL(12,2), currency STRING"
parsed = raw_df.withColumn("data", F.from_json("value", schema))

is_valid = (F.col("data.order_id").isNotNull()
            & F.col("data.amount").isNotNull()
            & F.col("data.currency").isin("USD", "EUR", "INR"))

good = parsed.filter(is_valid).select("data.*")
bad = (parsed.filter(~is_valid)
       .select(F.col("value").alias("raw_payload"),
               "_source_file",
               F.lit("VALIDATION_FAILED").alias("error_code"),
               F.current_timestamp().alias("quarantined_at")))

good.write.mode("append").saveAsTable("silver.orders")
bad.write.mode("append").saveAsTable("quarantine.orders")

from_json returns NULL fields for malformed JSON in the default permissive mode, so those rows also fail is_valid and land in quarantine.

Real-world use case

A vendor occasionally sends rows with an unknown currency code. They go to quarantine, the vendor-management team receives a daily report, and corrected rows are replayed weekly. Meanwhile 99.9% of the data publishes on time.

Common mistakes and red flags

• Quarantining without ever looking at the table.
• Storing only parsed fields, not the raw payload.
• No upper threshold, so a broken feed quietly quarantines everything.

Follow-up questions

• How do you avoid poison-pill messages blocking Kafka consumers? — Bounded retries, then publish to a dead-letter topic and commit the offset.
• How do you reprocess quarantined rows? — Fix the cause, then replay from quarantine through the same validation path with idempotent writes.

Detailed explanation

Airflow 3 changes worth knowing (released 2025)

• Task execution goes through the Task Execution API / Task SDK, so workers no longer need direct metadata-DB access. This improves security and enables remote execution.
• DAG versioning: the UI shows which DAG version each run used.
• “Datasets” were renamed Assets, and asset-driven scheduling was expanded.
• A rewritten React UI, and removal of some deprecated features (for example the execution_date context key in favour of logical_date).

Scaling and reliability

• Run multiple schedulers (supported since 2.0) for high availability.
• The metadata DB is a common bottleneck: keep it healthy, clean old records, and avoid heavy XComs.
• Scale workers horizontally (Celery) or per task (Kubernetes).

Example

           +-------------+        +----------------+
DAG files->| DAG processor|------>| Metadata DB    |<------+
           +-------------+        +----------------+       |
                                     ^      ^              |
                     +---------------+      |              |
                     |                      |              |
               +-----------+   queue   +---------+   +-----------+
               | Scheduler |---------->| Executor|-->|  Workers  |
               +-----------+           +---------+   +-----------+
                                                         |
                          +--------------+   Task SDK/API |
                          | API server/UI|<---------------+
                          +--------------+

Real-world use case

A team running about 2,000 DAGs saw tasks sitting in “scheduled” state for minutes. The metadata DB was overloaded by large XComs and years of task-instance history. Pruning history with airflow db clean and moving payloads to object storage fixed scheduling latency.

Common mistakes and red flags

• Thinking the web server runs tasks.
• Not knowing where state is stored.
• Ignoring the metadata DB as a scaling limit.

Follow-up questions

• What happens if the scheduler dies? — Running tasks continue on workers; with multiple schedulers another takes over, otherwise no new tasks are scheduled until it restarts.
• What are managed Airflow options? — Amazon MWAA, Google Cloud Composer, Astronomer, and Azure-hosted options.

Detailed explanation

• A task pushes an XCom explicitly (ti.xcom_push) or implicitly by returning a value from a TaskFlow @task or a PythonOperator callable.
• A downstream task pulls it (ti.xcom_pull(task_ids=...)) or receives it as a function argument in TaskFlow.
• Values are serialised and stored with dag_id, task_id, run_id and key.

Limits and good practice

• Size: the default backend stores values in the metadata DB, and practical size limits depend on the database (on the order of KB to a few GB depending on the DB). Treat anything beyond small payloads as wrong.
• Pass references, not data: write the DataFrame to S3 or a table, then pass the path or table name.
• Custom XCom backends: store values in object storage and keep only a reference in the DB. Airflow provides an object-storage XCom backend in its common IO provider.
• Serialisation: values must be serialisable. TaskFlow handles common types.
• Coupling: heavy XCom use can hide data dependencies that should instead be modelled as Assets (data-aware scheduling).

Example

# Runtime: Python 3.11, Apache Airflow 2.x/3.x (TaskFlow)
from airflow.decorators import task

@task
def stage_orders(ds: str | None = None) -> dict:
    path = f"s3://staging/orders/{ds}/part-0000.parquet"
    # ... write data to path ...
    return {"path": path, "row_count": 125_000}   # small metadata only

@task
def validate(meta: dict) -> None:
    if meta["row_count"] == 0:
        raise ValueError(f"No rows staged at {meta['path']}")

validate(stage_orders())

Real-world use case

A DAG returned a 200 MB pandas DataFrame from a task. The metadata DB grew rapidly and the scheduler slowed for every DAG. Writing the data to Parquet in S3 and returning only the path fixed it.

Common mistakes and red flags

• Returning whole DataFrames from tasks.
• Not knowing XComs live in the metadata DB by default.
• Using XCom to coordinate between different DAGs instead of Assets or triggers.

Follow-up questions

• How do you share data across DAGs? — Use Assets (data-aware scheduling) or TriggerDagRunOperator with a config payload.
• Are XComs cleaned up? — They’re cleared on task retry/clear; old records should be pruned with airflow db clean.

Detailed explanation

Data intervals and logical date

• With schedule="@daily", the run whose logical date is 2026-03-01 has the data interval [2026-03-01 00:00, 2026-03-02 00:00) and starts after the interval ends, at about 2026-03-02 00:00.
• Templates such as {{ ds }}, {{ data_interval_start }} and {{ data_interval_end }} let tasks process exactly that slice. That is what makes runs reproducible and backfills correct.
• execution_date was the old name. It was renamed logical_date in 2.2 and removed as a context key in Airflow 3.

start_date

• A static date in the past, never datetime.now(), which shifts on every parse.
• The first run covers the interval starting at start_date.

catchup

• catchup=True: when a DAG is unpaused or deployed with an old start_date, Airflow schedules every missing interval, which can create hundreds of runs.
• catchup=False: only the most recent interval runs. In Airflow 3 the default for catchup changed to False.

Backfill

• Explicit reprocessing of a date range: airflow dags backfill in 2.x, and backfills managed by the scheduler (triggerable from UI or API) in Airflow 3.
• Control parallelism with max_active_runs so backfills don’t overwhelm downstream systems.
• Only safe if tasks are idempotent and parameterised by the interval (see How do you make Airflow tasks idempotent and safe to retry?).

Example

# Runtime: Python 3.11, Apache Airflow 2.x/3.x
from datetime import datetime
from airflow.decorators import dag, task

@dag(schedule="@daily", start_date=datetime(2026, 1, 1), catchup=False, max_active_runs=3)
def orders_by_interval():
    @task
    def load_interval(data_interval_start=None, data_interval_end=None):
        # Process exactly this interval -> safe to rerun or backfill
        sql = f"""
            DELETE FROM fct_orders
             WHERE order_ts >= '{data_interval_start}' AND order_ts < '{data_interval_end}';
            INSERT INTO fct_orders
            SELECT * FROM stg_orders
             WHERE order_ts >= '{data_interval_start}' AND order_ts < '{data_interval_end}';
        """
        print(sql)  # in practice, execute via a hook with bound parameters
    load_interval()

orders_by_interval()

Real-world use case

An engineer deployed a DAG with start_date two years back and catchup=True. Around 700 runs started at once and saturated the warehouse. Setting catchup=False and doing a controlled backfill with max_active_runs=4 fixed it.

Common mistakes and red flags

• Expecting the 1 March daily run to start at midnight on 1 March.
• Using datetime.now() as start_date.
• Processing “yesterday” via CURRENT_DATE - 1 instead of the interval, which breaks backfills.

Follow-up questions

• How do you rerun a single day? — Clear that DAG run’s tasks in the UI or CLI; idempotent tasks will recompute it.
• What is depends_on_past? — A task only runs if its previous run’s instance succeeded, which enforces sequential processing.

In depth: What is the difference between catchup and backfill in Airflow 3?

Detailed explanation

Airflow will rerun tasks: automatic retries, manual clears and backfills. If a task appends rows, each rerun duplicates data. See What is idempotency, and why is it critical in pipelines? for the general principle. Here are the Airflow-specific techniques.

1. Use the interval, not the clock. Read data_interval_start and data_interval_end (or ds). Never datetime.now() or CURRENT_DATE for business logic.
2. Deterministic outputs. Write to .../dt={{ ds }}/ and overwrite that partition, or DELETE the interval then INSERT in one transaction, or MERGE on a natural key.
3. Atomic writes. Write to a temp location or staging table, then rename, swap or commit. A half-failed attempt then leaves nothing visible.
4. Side effects: emails, API posts and file drops to partners need idempotency keys (for example dag_id + run_id) or a “sent” log checked before sending.
5. Retries config: set retries, retry_delay and retry_exponential_backoff. Use execution_timeout so hung tasks fail instead of blocking.
6. Small tasks. One task, one responsibility, so a retry repeats only the failed step.
7. No local state. Workers are ephemeral, so pass references through XCom and storage, not local files.

Example

# Runtime: Python 3.11, Apache Airflow 2.x/3.x + Snowflake provider
from datetime import timedelta
from airflow.providers.common.sql.operators.sql import SQLExecuteQueryOperator

load_daily = SQLExecuteQueryOperator(
    task_id="load_daily_sales",
    conn_id="snowflake_default",
    sql="""
        BEGIN;
        DELETE FROM fct_daily_sales WHERE sales_date = '{{ ds }}';
        INSERT INTO fct_daily_sales
        SELECT sales_date, store_id, SUM(amount)
        FROM stg_sales WHERE sales_date = '{{ ds }}'
        GROUP BY sales_date, store_id;
        COMMIT;
    """,
    retries=3,
    retry_delay=timedelta(minutes=5),
    retry_exponential_backoff=True,
    execution_timeout=timedelta(hours=1),
)

{{ ds }} is rendered by Airflow from the run’s logical date, not supplied by users, so this templating is not an injection risk here. For user-supplied values, use bound parameters.

Real-world use case

A task appended daily revenue. When the warehouse timed out after the insert but before the task reported success, Airflow retried and revenue doubled for that day. Changing to delete-and-insert in a transaction made retries harmless.

Common mistakes and red flags

• INSERT without a matching delete or MERGE.
• Using retries to mask non-idempotent tasks.
• Sending notifications on every retry.

Follow-up questions

• How do you test idempotency? — Run the task twice for the same interval and assert the target is identical (row counts, checksums).
• What about streaming? — Use checkpoints plus idempotent sinks or MERGE (see Checkpoints and exactly-once in Structured Streaming).

Detailed explanation

How I’d choose

• Platform scope: if nearly everything runs in one platform (all Databricks or all Snowflake), its native scheduler is often enough and cheaper to operate.
• Cross-system workflows (SaaS APIs, multiple warehouses, ML, reverse ETL) favour a general orchestrator.
• Team skills and existing investment: Airflow knowledge is widespread.
• Asset-centric needs (lineage, partition-aware backfills): Dagster is compelling.
• Operational appetite: managed services (MWAA, Composer, Astronomer, Dagster+, Prefect Cloud) reduce operations.

Real-world use case

A team with all transformations in Databricks moved simple daily pipelines from Airflow to Lakeflow Jobs to cut infrastructure. It kept Airflow for cross-platform workflows that touch Salesforce, Snowflake and an internal API.

Common mistakes and red flags

• Picking a tool on hype without considering team skills and operations.
• Saying one tool is objectively “best”.
• Running two general orchestrators with overlapping scope and no clear rule.

Follow-up questions

• What does “asset-centric” buy you? — The orchestrator knows which tables exist and how fresh they are, so it can materialise only stale assets and show lineage.
• Can dbt be the orchestrator? — dbt orders its own models; you still need a scheduler for runs, ingestion and non-dbt steps.

Detailed explanation

Log-based CDC is preferred for serious workloads because it:

• captures every change, including deletes and intermediate updates, in commit order;
• adds almost no query load to the source;
• provides before and after images and transaction metadata.

It requires database configuration (for example wal_level=logical and a replication slot in PostgreSQL, or row-based binlog in MySQL). It also requires monitoring, because an unconsumed replication slot makes the WAL grow until the disk fills.

Typical tools

• Debezium (open source, via Kafka Connect; see Design a log-based CDC pipeline with Debezium and Kafka).
• AWS DMS, Azure Data Factory/SQL Server CDC, Google Datastream, Oracle GoldenGate.
• Managed ELT connectors (Fivetran, Airbyte), Snowflake Openflow, Databricks Lakeflow Connect.

CDC event shape: usually an op field (c = create, u = update, d = delete, r = snapshot read), before and after images, a source position (LSN or binlog offset) and a commit timestamp. Downstream, you apply events in order with MERGE, or append them to a history table and derive current state (see How do you handle deletes in CDC: soft vs hard deletes, CDC from a PostgreSQL OLTP database into the warehouse).

Initial snapshot: CDC tools first take a consistent snapshot of the existing table, then stream changes from the snapshot’s log position. That avoids gaps or double-counting.

Real-world use case

An e-commerce team replaced a nightly full dump of a 500 GB orders database with Debezium CDC. Source load dropped, deletes finally reached the warehouse, and freshness improved from 24 hours to under 5 minutes.

Common mistakes and red flags

• Calling updated_at polling “CDC” without stating its limits.
• Forgetting the initial snapshot.
• Not monitoring replication slot lag on PostgreSQL.

Follow-up questions

• How do you keep ordering? — Key the Kafka topic by primary key so changes per row stay in one partition, and apply them in log-position order.
• What happens on schema change in the source? — Log-based tools emit schema change events; downstream must evolve or a contract must block it (see Upstream added and renamed columns without notice (schema drift)).

Detailed explanation

Detecting deletes

• Log-based CDC emits explicit delete events (op = 'd') with the before image.
• Query-based extraction cannot see deletes. Options: the source uses soft deletes itself, periodic full key comparison (anti-join source keys against target keys), or a source-side delete log table.

Applying deletes

Recommendations

• Keep raw change events in bronze (append-only), so nothing is lost even if silver hard-deletes.
• Expose views or gold models that filter is_deleted = FALSE, so consumers don’t need to remember.
• Privacy deletions (GDPR) must be physical across all layers, including history and time-travel retention (see How does GDPR “right to erasure” affect lake and warehouse design?, Fulfil a GDPR deletion request across lake, warehouse and backups (See also Q85)). Soft delete is not enough there.
• Handle delete-then-reinsert of the same key: the later insert must win by log position.

Example

-- Dialect: Snowflake SQL
-- Soft-delete pattern from a batch of CDC events (latest event per key already selected)
MERGE INTO silver_customers t
USING cdc_customers_latest s
   ON t.customer_id = s.customer_id
WHEN MATCHED AND s.op = 'd' THEN UPDATE SET
     t.is_deleted = TRUE,
     t.deleted_at = s.commit_ts
WHEN MATCHED AND s.op <> 'd' THEN UPDATE SET
     t.full_name  = s.full_name,
     t.email      = s.email,
     t.is_deleted = FALSE,
     t.deleted_at = NULL
WHEN NOT MATCHED AND s.op <> 'd' THEN INSERT
     (customer_id, full_name, email, is_deleted, deleted_at)
     VALUES (s.customer_id, s.full_name, s.email, FALSE, NULL);

Real-world use case

A SaaS company soft-deletes cancelled accounts in silver, so churn analysis can still count them. A separate GDPR workflow physically purges personal fields for erasure requests across bronze, silver and gold.

Common mistakes and red flags

• Assuming incremental timestamp loads capture deletes.
• Soft-deleting but letting dashboards count deleted rows.
• Treating a soft delete as satisfying GDPR erasure.

Follow-up questions

• How do you find deletes without CDC? — Compare the full key set from the source with the target periodically and mark the missing keys.
• What about deletes in fact tables? — Usually soft-delete or reverse with negative adjustments, depending on accounting rules.

Detailed explanation

Folder-style partitioning (/table/event_date=2026-03-01/) lets engines skip whole directories when a query filters on the partition column.

Good partition key criteria

1. Commonly filtered: most queries include it (WHERE event_date BETWEEN ...).
2. Reasonable cardinality: hundreds to low thousands of partitions in total, not millions.
3. Healthy partition size: a common guideline is at least about 1 GB per partition, with files of roughly 128 MB–1 GB. Databricks guidance is to avoid partitioning tables under about 1 TB at all.
4. Even distribution: avoid keys where one value holds most of the data.
5. Stable: the value doesn’t change for a record (an update would move it between partitions).
6. Aligned with lifecycle: date partitions make retention deletes and backfills simple.

Over-partitioning symptoms

• Millions of small files (see What is the small-files problem, and how do you fix it?).
• Slow job planning and metastore or listing calls.
• Writers creating one file per partition per task, which explodes file counts.

Alternatives to more partitioning

• Partition coarsely by date, then cluster or sort within it on the second filter column (customer, region).
• Use modern layout features: Delta Liquid Clustering (see Liquid Clustering vs partitioning vs Z-ORDER), Iceberg hidden partitioning with transforms like bucket(16, customer_id), and Snowflake micro-partitions with clustering keys (see Clustering keys, clustering depth and Automatic Clustering). Snowflake has no user-defined folder partitions.

Example

-- Dialect: Spark SQL (Delta Lake)
-- Coarse date partition; cluster within files by customer via OPTIMIZE ZORDER
CREATE TABLE silver.events (
    event_id    STRING,
    customer_id BIGINT,
    event_type  STRING,
    event_ts    TIMESTAMP,
    event_date  DATE
)
USING DELTA
PARTITIONED BY (event_date);

OPTIMIZE silver.events
WHERE event_date >= current_date() - INTERVAL 7 DAYS
ZORDER BY (customer_id);

For new Databricks tables, Liquid Clustering (CLUSTER BY) is usually preferred over partitioning plus Z-ORDER (see Liquid Clustering vs partitioning vs Z-ORDER).

Real-world use case

A team partitioned a 50 GB table by customer_id (2 million values). It produced 2 million folders and queries took minutes just to plan. Repartitioning by month and clustering by customer cut query times to seconds.

Common mistakes and red flags

• Partitioning by a high-cardinality ID.
• Partitioning small tables at all.
• Choosing a column queries rarely filter on.

Follow-up questions

• What happens if queries don’t filter on the partition column? — No pruning; full scan, and you’ve paid the small-file cost for nothing.
• Partition by event date or ingestion date? — Event date for query pruning; ingestion date if late data would otherwise rewrite old partitions, often both with clustering.

Detailed explanation

Why small files hurt

• Per-file overhead: opening each file, reading its footer, and an object-storage request per file. Ten thousand 1 MB files are far slower to read than ten 1 GB files holding the same data.
• Metadata pressure: listing operations, Hive metastore partitions and table-format manifests or logs all grow with file count.
• Poor compression and skipping: small row groups compress worse and carry weaker statistics.
• Task scheduling: engines may launch one task per file, so the cluster spends its time scheduling.

Common causes

• Streaming or micro-batch writes every few seconds.
• Over-partitioning (see How do you choose a partition key, and what is over-partitioning?): every write task emits a file into every partition it touches.
• High-parallelism writes (spark.sql.shuffle.partitions = 2000 writing a 1 GB result).
• Frequent small MERGE or UPDATE operations that rewrite files.

Fixes

1. Write-time sizing: coalesce or repartition by partition columns before writing (see repartition vs coalesce). Use optimized writes and auto compaction on Databricks (see OPTIMIZE, optimized writes and auto compaction). Let AQE coalesce shuffle partitions (see Adaptive Query Execution: what does it do?).
2. Compaction jobs: Delta OPTIMIZE, Iceberg rewrite_data_files, Hudi clustering and compaction, or scheduled rewrite jobs for plain Parquet.
3. Batch longer in streaming: a longer trigger interval, or availableNow runs, where latency allows.
4. Fix the partition design.
5. Clean up old file versions after compaction (Delta VACUUM, Iceberg expire_snapshots and remove_orphan_files).

Example

# Runtime: PySpark 3.5
from pyspark.sql import functions as F

daily = spark.read.table("bronze.events").filter(F.col("event_date") == "2026-03-01")

# Write ~8 files instead of hundreds: size the output to the data (~8 GB -> ~1 GB/file)
(daily.repartition(8)
      .write.mode("overwrite")
      .option("replaceWhere", "event_date = '2026-03-01'")
      .format("delta")
      .saveAsTable("silver.events"))

Real-world use case

A streaming job writing every 10 seconds produced 8,640 files per day per partition. Queries that should take seconds took minutes. Nightly OPTIMIZE on the previous day’s partitions, plus a 1-minute trigger, reduced the file count by over 99%.

Common mistakes and red flags

• Fixing only reads (caching) without addressing how files are written.
• Running compaction but never vacuuming old files, so storage grows.
• Coalescing to 1 file for huge outputs, which creates a single-task bottleneck.

Follow-up questions

• How do you measure it? — Look at file counts and average file size per partition (DESCRIBE DETAIL on Delta, Iceberg files metadata table).
• Does Snowflake have this problem? — Not for native tables, which Snowflake manages internally; it does affect loading many tiny staged files (see File sizing and format best practices for bulk loading).

Detailed explanation

Splittability

• Inside Parquet and ORC, compression applies per page or stream, so the files stay splittable whatever the codec.
• A whole-file compressed CSV or JSON (.csv.gz) must be read by a single task from the start. A single 20 GB .gz file gives you no parallelism. Bzip2 is splittable but slow. The fix is many moderately sized compressed files, or conversion to Parquet.

Choosing

• Analytics storage: ZSTD (a low level such as 1–3) or Snappy. Benchmark on your data. ZSTD often shrinks files 10–30% versus Snappy with little read penalty.
• Landing text files from vendors: gzip is fine if files are reasonably sized.
• Kafka: LZ4 or ZSTD at producer level for throughput.
• Remember that compute cost (CPU) and storage or scan cost trade off. On pay-per-scan engines, better ratios save money directly.

Example

# Runtime: PySpark 3.5
spark.conf.set("spark.sql.parquet.compression.codec", "zstd")

(spark.read.table("bronze.clicks")
      .write.mode("overwrite")
      .parquet("s3://lake/silver/clicks_zstd/"))

Real-world use case

Switching a 200 TB Parquet lake from Snappy to ZSTD reduced storage by roughly a fifth after rewrite. Query times were essentially unchanged because the workloads were I/O-bound rather than CPU-bound.

Common mistakes and red flags

• Receiving one huge .gz file and wondering why Spark uses one core.
• Assuming the highest compression level is always best.
• Thinking compression makes Parquet unsplittable.

Follow-up questions

• What does Snowflake use? — Snowflake compresses its internal storage automatically; for staged files you declare the codec in the file format (or AUTO).
• How do you pick a ZSTD level? — Benchmark: low levels for write-heavy pipelines, higher for cold archival data.

Detailed explanation

• C (consistency): linearizability. All clients see the same, most recent value.
• A (availability): every non-failing node answers every request.
• P (partition tolerance): the system keeps working despite lost messages between nodes.

In real distributed systems, partitions will happen, so P is not optional. The real choice during a partition is CP (refuse or delay some requests to stay consistent) or AP (answer with possibly stale data and reconcile later).

PACELC: if Partition, choose A or C; Else (normal operation), choose Latency or Consistency. Synchronous cross-region replication gives consistency but adds latency. Asynchronous replication is fast but can serve stale reads.

Why data engineers care

• Replicas and CDC sources can lag, so reading a replica may give stale data.
• Cross-region warehouse replication is asynchronous, so the DR copy lags.
• Exactly-once and idempotency designs (see What is idempotency, and why is it critical in pipelines?, At-most-once vs at-least-once vs exactly-once delivery) exist because networks fail between systems.

Real-world use case

A team read from a lagging MySQL replica for its hourly extract and missed recent orders. Knowing replicas are asynchronous (latency over consistency), they added a lag check and a lookback window to the extract.

Common mistakes and red flags

• Saying “pick any two of three”.
• Confusing CAP consistency with ACID consistency.
• Claiming a system is “CA” in a distributed setting.

Follow-up questions

• Is S3 strongly consistent? — Yes; S3 has provided strong read-after-write consistency for all operations since December 2020.
• What is eventual consistency? — If writes stop, all replicas eventually converge to the same value.

Detailed explanation

Assignment rules

• 12 partitions and 4 consumers: 3 partitions each.
• 12 partitions and 15 consumers: 3 consumers sit idle.
• Different groups each get the full stream independently. That is how one topic serves many applications.

Offsets

• Consumers commit offsets per partition, automatically (enable.auto.commit) or manually after processing.
• Committing after processing gives at-least-once. Committing before gives at-most-once (see At-most-once vs at-least-once vs exactly-once delivery).
• auto.offset.reset (earliest / latest) applies only when the group has no committed offset.

Rebalancing

• Triggered by a consumer joining or leaving, a missed heartbeat (session.timeout.ms), exceeding max.poll.interval.ms between polls, or topic partition changes.
• Eager rebalancing stops all consumers and reassigns everything, a “stop the world” pause.
• Cooperative / incremental rebalancing (the CooperativeStickyAssignor) moves only the partitions that need to move, so other consumers keep working.
• Static membership (group.instance.id) avoids rebalances on quick restarts, such as rolling deploys.
• Kafka 4.0 made the new consumer group protocol (KIP-848, with broker-side assignment) generally available, which reduces rebalance disruption further.

Common rebalance trouble: slow processing per poll exceeds max.poll.interval.ms, so the consumer is kicked out, partitions move, work is redone, and the cycle repeats.

Example

# Runtime: Python 3.11 with confluent-kafka — commit only after successful processing
from confluent_kafka import Consumer

consumer = Consumer({
    "bootstrap.servers": "broker1:9092",
    "group.id": "orders-loader",
    "enable.auto.commit": False,
    "auto.offset.reset": "earliest",
    "partition.assignment.strategy": "cooperative-sticky",
})
consumer.subscribe(["orders"])

try:
    while True:
        msg = consumer.poll(1.0)
        if msg is None or msg.error():
            continue
        process(msg.value())                      # user-defined, should be idempotent
        consumer.commit(message=msg, asynchronous=False)
finally:
    consumer.close()

Real-world use case

A consumer called a slow enrichment API per message and kept exceeding max.poll.interval.ms. That triggered rebalance storms and duplicate processing. Batching API calls, lowering max.poll.records and switching to cooperative rebalancing stabilised the group.

Common mistakes and red flags

• Adding consumers beyond the partition count and expecting more throughput.
• Using auto-commit with long processing, which risks data loss on crashes.
• Not knowing what triggers a rebalance.

Follow-up questions

• How do you measure consumer lag? — Log-end offset minus committed offset per partition (tools: kafka-consumer-groups, Burrow, cloud metrics).
• How do you reprocess from a point in time? — Reset the group’s offsets to a timestamp (--reset-offsets --to-datetime) while consumers are stopped.

In depth: Explain Kafka partitions and consumer groups.

Detailed explanation

What is guaranteed

• Within a partition, offsets are sequential and consumers see records in offset order.
• Records with the same key go to the same partition, provided the partition count doesn’t change.

What can break ordering

1. Multiple partitions. Events for one entity spread across partitions if they’re unkeyed or keyed inconsistently.
2. Producer retries with multiple in-flight requests. Without idempotence, a failed batch retried after a later batch succeeded can land out of order. Fix: enable.idempotence=true (the default in modern clients). It preserves order with up to 5 in-flight requests per connection.
3. Increasing partition count. Key-to-partition mapping changes, so new events for a key may go to a different partition than old ones.
4. Consumer-side parallelism. A consumer that processes records from one partition with a thread pool can reorder them. Process per key sequentially, or use key-aware parallel consumers.
5. Multiple producers for the same key without coordination, which can interleave by time of arrival.

Design guidance

• Choose the key by the entity whose order matters (order_id, account_id).
• Even then, downstream should carry a sequence number or source log position and apply the latest by sequence, not by arrival (see Design a log-based CDC pipeline with Debezium and Kafka). That makes processing robust to replays and merges.
• If you truly need a total order, a single partition gives it, but caps throughput at one consumer.

Example

# Runtime: Python 3.11 with confluent-kafka
from confluent_kafka import Producer

producer = Producer({
    "bootstrap.servers": "broker1:9092",
    "enable.idempotence": True,          # safe retries preserve per-partition order
    "acks": "all",
})
# Same key -> same partition -> ordered for this account
for event in ({"type": "debit", "amt": 50}, {"type": "credit", "amt": 20}):
    producer.produce("account-events", key="acct-778", value=str(event).encode())
producer.flush()

Real-world use case

Account balance updates were computed wrongly because debit and credit events for the same account were unkeyed and landed in different partitions. Keying by account_id and applying events by sequence number fixed the balances.

Common mistakes and red flags

• Claiming topic-level ordering.
• Forgetting that adding partitions changes key placement.
• Parallelising within a partition without preserving key order.

Follow-up questions

• How do you handle ordering across topics? — You can’t rely on it; join on keys and event timestamps or sequence numbers with watermarks.
• What about hot keys? — One key’s partition becomes a bottleneck; consider sub-keys if strict ordering isn’t required for all events (see A hot partition in a Kafka topic).

Detailed explanation

How it works

1. A producer serialiser registers or looks up the schema under a subject (by default <topic>-value and <topic>-key).
2. The registry returns an ID, and the message is written as a magic byte, the schema ID and the binary payload.
3. Consumers fetch the schema by ID (cached) and deserialise.

Compatibility modes (examples use Avro rules)

Why it matters for data engineering

• It blocks breaking changes at produce time instead of breaking pipelines at 3 AM (see What are data contracts, and how do you enforce them?).
• It gives a self-documenting catalogue of event schemas.
• It enables automatic schema evolution in sinks (for example, a new optional field flowing into a lakehouse table).

Implementations: Confluent Schema Registry, AWS Glue Schema Registry, Azure Schema Registry (Event Hubs), Apicurio, Redpanda’s built-in registry.

Example

{
  "type": "record",
  "name": "OrderPlaced",
  "namespace": "com.shop.events",
  "fields": [
    {"name": "order_id", "type": "string"},
    {"name": "amount", "type": {"type": "bytes", "logicalType": "decimal", "precision": 12, "scale": 2}},
    {"name": "channel", "type": ["null", "string"], "default": null}
  ]
}

Adding channel as an optional field with a default is a backward-compatible change.

Real-world use case

A producer team tried to rename amount to total_amount. With BACKWARD compatibility, the registry rejected the new schema at deploy time. They added total_amount as a new optional field instead and deprecated amount over a release cycle.

Common mistakes and red flags

• Setting compatibility to NONE to “unblock” a deploy.
• Adding required fields without defaults.
• Not knowing which side must upgrade first for each mode.

Follow-up questions

• Avro vs Protobuf in a registry? — Both work; Protobuf is popular with gRPC-heavy services, Avro with data platforms; compatibility rules differ slightly.
• How do lakehouse sinks use it? — Connectors read the schema to create and evolve target table columns automatically.

Detailed explanation

How to choose

• Multi-cloud or portability, rich connector ecosystem, exactly-once Kafka Streams: Kafka, usually managed.
• All-in on one cloud with modest needs and no operations team: the native service.
• Global fan-out with many independent subscribers and no partition management: Pub/Sub.
• Azure shop with Kafka clients: Event Hubs’ Kafka endpoint, so existing clients connect with configuration changes.

Retention limits, per-shard or per-partition throughput quotas and pricing details change over time. Check current provider limits before quoting numbers.

Real-world use case

A retailer on AWS chose Kinesis with Firehose for clickstream landing (minimal operations). A separate CDC platform that needed Debezium and Kafka Connect used Amazon MSK.

Common mistakes and red flags

• Claiming Pub/Sub orders all messages by default.
• Ignoring operational cost when recommending self-managed Kafka.
• Assuming the APIs are interchangeable.

Follow-up questions

• What is Kinesis Data Firehose (now Amazon Data Firehose)? — A managed delivery service that batches streams into S3, Redshift, Snowflake and others with no consumer code.
• When is a queue (SQS) better than a log? — For task distribution where each message is processed once and replay isn’t needed.

Detailed explanation

Considerations

• Utilisation: a cluster busy 20 hours a day may be cheaper provisioned, especially with reserved or committed pricing. Ten jobs a day of 5 minutes each favour serverless.
• Latency requirements: serverless removes cluster start-up, which matters for interactive and short jobs.
• Governance and networking: some serverless options restrict private networking or custom libraries. Check before committing.
• Cost visibility: serverless can surprise you. Set budgets, quotas and alerts (see General principles of cloud data cost optimisation).
• Many platforms are hybrid: Snowflake warehouses are provisioned, but suspend when idle and bill per second after the first 60 seconds. Snowpipe and serverless tasks are serverless.

Real-world use case

A team moved 300 small daily Databricks jobs from classic job clusters, each waiting about 5 minutes to start, to serverless jobs. Total runtime fell and cost was roughly flat. Their 18-hour-a-day streaming job stayed on provisioned compute because that was cheaper.

Common mistakes and red flags

• “Serverless is always cheaper.”
• Ignoring start-up latency when choosing provisioned compute for short jobs.
• No cost guardrails on serverless.

Follow-up questions

• How do you compare costs? — Model expected usage hours and data volumes per option, then validate with a trial run and billing data.
• What is a warm pool? — Pre-started instances (for example Databricks instance pools) that cut cluster start-up time.

Detailed explanation

Data Catalog

• Stores table definitions: location, format, schema, partitions. It also supports Iceberg, Delta and Hudi table metadata.
• Shared across AWS analytics services, so one definition serves Athena and EMR.
• Lake Formation layers fine-grained permissions on top of it.

Crawlers

• Point at an S3 path or JDBC source, infer schema with classifiers, and detect new partitions.
• Useful for discovery and evolving raw zones.
• Pitfalls: unexpected schema inference (mixed types in JSON), crawlers creating many tables when folder layouts are inconsistent, and cost if they run too often. For well-known tables, define the schema explicitly (DDL or IaC) and add partitions programmatically or through partition projection.

Glue jobs

• Spark-based ETL with DPUs (data processing units) as the billing unit, plus auto scaling and Flex execution for cheaper non-urgent jobs.
• DynamicFrames: Glue’s schema-flexible wrapper around DataFrames, with transforms like ResolveChoice. Many teams just use Spark DataFrames.
• Job bookmarks track processed data (files or JDBC keys) to support incremental runs.
• Glue Data Quality offers rule-based checks (DQDL).
• Glue Studio for visual authoring. Interactive sessions for notebooks.

Example

# Runtime: AWS Glue 4.0/5.0 job (PySpark 3.x) — read catalog table, write Parquet partitioned by date
import sys
from awsglue.context import GlueContext
from awsglue.job import Job
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from pyspark.sql import functions as F

args = getResolvedOptions(sys.argv, ["JOB_NAME"])
glue_ctx = GlueContext(SparkContext.getOrCreate())
job = Job(glue_ctx)
job.init(args["JOB_NAME"], args)

orders = glue_ctx.create_dynamic_frame.from_catalog(
    database="bronze", table_name="orders_raw",
    transformation_ctx="orders_src")          # transformation_ctx enables bookmarks

df = orders.toDF().withColumn("order_date", F.to_date("order_ts"))
df.write.mode("append").partitionBy("order_date").parquet("s3://lake/silver/orders/")

job.commit()                                   # persists bookmark state

Real-world use case

A team replaced hourly crawlers on a 10,000-partition table, which cost money and sometimes changed column types, with partition projection in Athena and a fixed DDL. That cut cost and eliminated surprise schema changes.

Common mistakes and red flags

• Running crawlers on every load for stable, known schemas.
• Forgetting job.commit(), so bookmarks never advance.
• Not knowing what a DPU is.

Follow-up questions

• What is partition projection? — Athena computes partition locations from configured patterns instead of reading them from the catalog, which suits highly partitioned tables.
• Glue vs Databricks on AWS? — Glue is simpler and serverless for moderate ETL; Databricks offers a fuller lakehouse platform with richer tooling.

Detailed explanation

RBAC

• Example: role ANALYST_FINANCE has SELECT on schema finance_gold, and Priya is granted that role.
• Hierarchies let roles inherit other roles (Snowflake role hierarchy; see Snowflake RBAC: system roles and role hierarchy).
• Pros: clear, auditable, supported everywhere.
• Cons: role explosion. Combining regions × departments × sensitivity levels can create hundreds of roles.

ABAC

• A policy evaluates attributes at query time. For example: “users can see rows where row.region is in user.allowed_regions”, or “columns tagged PII are masked unless the user is in group pii_readers”.
• Implemented with tag-based policies (Unity Catalog ABAC policies, Snowflake tag-based masking, AWS Lake Formation LF-tags), row access policies and masking functions that read session or user attributes.
• Pros: few policies cover many tables, and new tables are protected automatically once tagged.
• Cons: harder to debug (“why can’t I see this row?”), and it depends on correct, complete tagging.

Most platforms use RBAC for object access plus ABAC-style policies for row and column security.

Example

-- Dialect: Snowflake SQL
-- RBAC: object-level grant
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.finance_gold TO ROLE analyst_finance;

-- ABAC-style: mask any column tagged PII unless the role is allowed
CREATE OR REPLACE MASKING POLICY mask_pii_string AS (val STRING) RETURNS STRING ->
  CASE WHEN IS_ROLE_IN_SESSION('PII_READER') THEN val ELSE '***MASKED***' END;

ALTER TAG governance.tags.pii SET MASKING POLICY mask_pii_string;

Real-world use case

A global company had 400 regional roles to restrict sales data. Replacing them with one row access policy driven by a user-to-region mapping table cut the number of roles sharply and made onboarding a single mapping-table insert.

Common mistakes and red flags

• Granting privileges directly to users.
• Proposing ABAC without a reliable tagging process.
• Not knowing that both models usually coexist.

Follow-up questions

• What is least privilege? — Grant only the minimum access required, for the minimum time, reviewed regularly.
• How do you audit access? — Platform access-history views (Snowflake ACCOUNT_USAGE.ACCESS_HISTORY, Unity Catalog audit system tables).

Detailed explanation

Principles

1. Data minimisation: don’t ingest PII you don’t need.
2. Classify early: tag PII at ingestion, using automated classification plus review.
3. Separate identity from behaviour: keep a restricted identity table keyed by a pseudonymous ID, and keep analytics tables free of direct identifiers.
4. Use keyed hashes, not plain hashes. Unsalted MD5 or SHA of an email or phone number can be reversed by brute force or lookup tables, because the input space is small.
5. Protect non-prod: never copy raw production PII to dev. Use masked or synthetic data.
6. Plan for deletion (GDPR/CCPA). Pseudonymisation does not remove the obligation (see How does GDPR “right to erasure” affect lake and warehouse design?).

Example

-- Dialect: Snowflake SQL
-- Pseudonymous, joinable ID with a secret key (keep the key in a secured secret/UDF, not in code)
SELECT
    SHA2(CONCAT(email_normalized, '|', $pii_hash_pepper), 256) AS customer_pid,
    signup_date,
    country
FROM (
    SELECT LOWER(TRIM(email)) AS email_normalized, signup_date, country
    FROM raw.customers
);

In production, put the pepper in a secured function or secret with restricted access rather than a session variable, so analysts can’t read it.

Real-world use case

A marketplace replaced emails in its event tables with an HMAC-based customer pseudonym. Only the identity service can map the pseudonym back to an email. Analysts join across datasets freely, and a breach of the analytics tables would not expose emails.

Common mistakes and red flags

• Calling an unsalted hash of an email “anonymised”.
• Masking in the BI tool only, leaving raw tables open.
• Copying production PII into development environments.

Follow-up questions

• Is pseudonymised data still personal data under GDPR? — Yes; only truly anonymised data falls outside it.
• Tokenisation vs encryption? — Tokens have no mathematical relationship to the value (lookup via vault); encryption is reversible with the key.

Detailed explanation

Levels

• Table-level: raw.orders → silver.orders → gold.fct_revenue → Revenue dashboard.
• Column-level: gold.fct_revenue.net_amount is derived from silver.orders.amount - silver.orders.discount.

Capture methods

Uses

• Impact analysis: “If I drop this column, which dashboards break?”
• Root cause: “This KPI is wrong. Which upstream table changed?”
• Compliance: show where PII flows, and prove report numbers trace to approved sources.
• Cost and cleanup: find unused tables with no downstream consumers.

Real-world use case

Before changing the definition of customer_status, a team checked column-level lineage and found 14 downstream models and 6 dashboards. They coordinated the change with owners instead of discovering breakages after deploy.

Common mistakes and red flags

• Relying on hand-drawn diagrams.
• Having lineage only inside one tool, so cross-system flows are invisible.
• Not mentioning column-level lineage for impact analysis.

Follow-up questions

• What is OpenLineage? — An open specification for emitting lineage events (job, run, input and output datasets) from data tools.
• How does lineage help incident response? — It shows downstream blast radius for notification, and upstream candidates for root cause.

Detailed explanation

Notes

• Unit tests catch logic bugs (date boundaries, null handling, currency rounding) before data ever runs. They are the most under-used layer in data teams.
• Data tests catch data problems that the code can’t anticipate (duplicates from upstream).
• Keep test data small, versioned and representative, including edge cases: nulls, duplicates, late records, time-zone boundaries.
• Make transforms testable: pure functions that take DataFrames and return DataFrames, with no I/O inside.

Example

# Runtime: Python 3.11, pytest, PySpark 3.5 — unit test a pure transformation function
import pytest
from pyspark.sql import SparkSession, functions as F

def add_net_amount(df):
    return df.withColumn("net_amount", F.col("gross_amount") - F.coalesce(F.col("discount"), F.lit(0)))

@pytest.fixture(scope="session")
def spark():
    return SparkSession.builder.master("local[1]").appName("tests").getOrCreate()

def test_add_net_amount_handles_null_discount(spark):
    df = spark.createDataFrame([(100.0, 10.0), (50.0, None)], "gross_amount double, discount double")
    result = [r.net_amount for r in add_net_amount(df).collect()]
    assert result == [90.0, 50.0]

Real-world use case

A currency-conversion bug, where the wrong rate date was used at month boundaries, kept reaching production because only data tests existed. A dozen unit tests with boundary-date fixtures caught the next regression in CI.

Common mistakes and red flags

• Only having data tests in production.
• Unit tests that need a full warehouse to run.
• No edge cases in fixtures.

Follow-up questions

• How do you unit test SQL? — dbt unit tests with mocked inputs, or run SQL on a local engine (DuckDB) against fixtures.
• How do you test idempotency? — Run the job twice for the same input and assert identical output.

Detailed explanation

Isolation options

Configuration, not code changes

• Environment-specific values (database names, warehouse sizes, secret names, schedules) live in config: dbt targets, bundle targets, Terraform workspaces or variables, environment variables.
• The same artefact runs everywhere, so “it worked in test” means something.

Data for non-prod

• Zero-copy clones (Snowflake CLONE, Delta shallow clone) of production for realistic testing, with masking policies still applied (see Zero-copy cloning, Shallow clone vs deep clone).
• Sampling (latest N days) to cut cost.
• Synthetic or masked data where PII rules forbid production copies.
• Defer to production for unchanged upstream models in dbt CI (see Slim CI with state:modified and --defer).

Promotion flow

1. Feature branch, developing in a personal dev schema.
2. Pull request: CI builds in an ephemeral CI schema.
3. Merge: automatic deploy to test or staging, with integration and data tests.
4. Approval: deploy to prod, plus post-deploy smoke checks.

Real-world use case

A team used a single “shared dev” schema where developers overwrote each other’s tables. Moving to per-developer schemas, plus CI schemas per pull request that are dropped on merge, ended the conflicts.

Common mistakes and red flags

• Hardcoding PROD database names in SQL.
• Copying full production data, PII included, to dev.
• Making hotfixes directly in prod, outside Git.

Follow-up questions

• How do you avoid stale clones? — Re-clone on a schedule or per CI run; clones are cheap until data diverges.
• Do you need a staging environment if you have good CI? — For complex, multi-system releases, yes; for simple dbt changes, CI plus prod defer is often enough.

Detailed explanation

Why it matters

• Reproducibility: dev, test and prod are created from the same code with different variables.
• Review: terraform plan shows exactly what will change before it happens.
• Drift detection: manual console changes show up as differences in the next plan.
• Disaster recovery: rebuild infrastructure from code.
• Access governance: roles and grants defined in code, so access changes are reviewed pull requests.

Key Terraform concepts

• Providers (aws, azurerm, google, the Snowflake provider snowflakedb/snowflake, databricks).
• Resources and data sources. Modules for reusable patterns (for example “a team’s schema + roles + warehouse”).
• State: a mapping of code to real resources, stored remotely (S3 + DynamoDB lock, Terraform Cloud, Azure Storage) with locking. It may contain secrets, so restrict access.
• Workspaces or separate state per environment.

What not to manage with Terraform

• Fast-changing data objects such as individual tables built by dbt or pipelines. Let the transformation tool own those.
• Very high-churn grants on thousands of objects may be better handled with future grants or a dedicated access tool.

Alternatives and companions: OpenTofu (open-source fork), Pulumi (general-purpose languages), AWS CDK, Bicep, Declarative Automation Bundles for Databricks job and pipeline resources, and Snowflake’s declarative tooling.

Example

# Terraform (HCL) — Snowflake warehouse and role for a team
# Resource and argument names follow the snowflakedb/snowflake provider; check your provider version's docs.
resource "snowflake_warehouse" "marketing_wh" {
  name           = "MARKETING_WH"
  warehouse_size = "XSMALL"
  auto_suspend   = 60
  auto_resume    = true
}

resource "snowflake_account_role" "marketing_analyst" {
  name = "MARKETING_ANALYST"
}

resource "snowflake_grant_privileges_to_account_role" "wh_usage" {
  account_role_name = snowflake_account_role.marketing_analyst.name
  privileges        = ["USAGE"]
  on_account_object {
    object_type = "WAREHOUSE"
    object_name = snowflake_warehouse.marketing_wh.name
  }
}

Real-world use case

A platform team manages 40 Snowflake warehouses, roles and grants in Terraform. A new team gets its full environment from a module call in one pull request, and quarterly access reviews read straight from the code.

Common mistakes and red flags

• Storing state locally or in Git.
• Mixing console changes with Terraform, causing drift.
• Managing every dbt table in Terraform.

Follow-up questions

• How do you import existing resources? — terraform import or import blocks, then write matching configuration.
• How do you protect production? — Separate state and credentials per environment, required plan review, and prevent_destroy on critical resources.

Detailed explanation

1. Visibility

• Tag or label compute (warehouses, clusters, jobs) by team, project and environment.
• Use platform usage data (Snowflake ACCOUNT_USAGE, Databricks system.billing, cloud cost explorers) for per-pipeline cost.
• Set budgets, resource monitors and anomaly alerts (see Cost control: resource monitors and budgets, Monitoring spend with system.billing tables).

2. Compute

• Auto-suspend and terminate: idle warehouses and clusters are the most common waste.
• Right-size: start small and scale up only if a bigger size improves runtime proportionally.
• Use job compute, not interactive compute, for production (see All-purpose vs job compute vs SQL warehouse vs serverless).
• Spot or preemptible instances for fault-tolerant batch (see Spot/preemptible and reserved capacity for data workloads).
• Schedule smartly: consolidate small jobs, and avoid running every 5 minutes when hourly meets the SLA.

3. Work reduction

• Incremental instead of full refresh (see Full load vs incremental load).
• Prune effectively: partitioning or clustering aligned with filters, and selecting needed columns only.
• Fix the expensive queries: the top 10 queries usually dominate the spend.
• Avoid recomputation: materialise expensive shared intermediates, and use result caching where it applies.
• Stop unused pipelines and tables: check lineage and query history for zero-consumer outputs.

4. Storage

• Lifecycle rules to cold tiers, and expiry of temp or staging data (see Storage tiering and lifecycle policies).
• Shorter Time Travel or retention on large, reproducible tables, and transient tables for staging (see Permanent vs transient vs temporary tables).
• Compaction and VACUUM to remove old file versions.

5. Culture

• Show teams their own spend. Include cost in design reviews and pull requests for large jobs.

Real-world use case

A monthly review showed three nightly full-refresh models made up 40% of warehouse credits. Converting them to incremental with a 3-day lookback cut those costs by about 80%, with no change in output.

Common mistakes and red flags

• Optimising tiny costs while ignoring the top consumers.
• Cutting cost by breaking SLAs with no agreement from stakeholders.
• No tagging, so nobody can see who spends what.

Follow-up questions

• Where do you start? — Rank spend by warehouse, cluster, job and query, and attack the top items first.
• How do you prevent regressions? — Budgets and alerts per team, plus cost checks in CI for heavy models.

Detailed explanation

Typical classes

Trade-offs

• Cheaper classes have minimum storage durations, retrieval fees and sometimes retrieval delays (hours for deep archive).
• Don’t tier files that are still referenced by active table versions and queried. Moving Delta or Iceberg data files to archive classes can break queries.
• Many small objects in IA or archive classes can cost more because of per-object overheads and minimum object sizes.

Policy patterns for a lake

• landing/ and tmp/: expire after 7–30 days.
• bronze/ raw files: Standard for 30–90 days, then IA, then archive or expiry based on compliance needs.
• silver/ and gold/ table data: keep hot. Manage old versions with table-format maintenance (VACUUM, expire snapshots) rather than raw lifecycle rules.
• Versioned buckets: add rules for noncurrent versions, otherwise deleted data keeps costing money.
• Clean up incomplete multipart uploads.

Example

{
  "Rules": [
    {
      "ID": "tier-bronze-raw",
      "Filter": { "Prefix": "bronze/raw/" },
      "Status": "Enabled",
      "Transitions": [
        { "Days": 90,  "StorageClass": "STANDARD_IA" },
        { "Days": 365, "StorageClass": "GLACIER" }
      ],
      "NoncurrentVersionExpiration": { "NoncurrentDays": 30 },
      "AbortIncompleteMultipartUpload": { "DaysAfterInitiation": 7 }
    },
    {
      "ID": "expire-tmp",
      "Filter": { "Prefix": "tmp/" },
      "Status": "Enabled",
      "Expiration": { "Days": 7 }
    }
  ]
}