Enterprise Data Architect

Architecture styles, table formats, compatibility, governance, privacy, security and lineage across AWS, Azure, Google Cloud, Snowflake and Databricks, and how to implement lakes, warehouses, lakehouses and data mesh and modernise legacy systems.

1. What a Data Architect Does

A data architect designs how an organisation's data is collected, stored, connected, protected and used, so that many teams can build on it safely. Data engineers build pipelines; the architect decides the rules and the structure those pipelines fit into.

This card continues the online retailer from the Data Modelling and dbt cards. The sales mart works. But the company has grown to 40 pipelines and three teams, it now holds customer data from India and Europe, and an auditor has asked a simple question: "Who can see customer emails, and where does that data go?" Nobody can answer it quickly. Answering it properly is the architect's job, and each section below solves part of it.

Thinking across the whole enterprise

An enterprise data architect is not tied to one product. A large company usually runs several platforms at once: an ERP on Oracle or SAP, a CRM in the cloud, a warehouse on Snowflake or BigQuery, a lakehouse on Databricks, Kafka for events and Power BI or Tableau for reporting. The architect's job is to make them work together under one set of rules, and to choose new technology for good reasons rather than habit.

Frameworks worth knowing

You rarely follow a framework rigidly, but interviewers expect you to know them, and they give a shared vocabulary with enterprise architects and auditors.

2. The Platform at a Glance

An architect first draws the whole platform, because every later decision has to fit into it. The layers in the middle carry data; the band underneath applies to all of them, which is why governance and security cannot be added to one pipeline at a time.

 SOURCES        Orders DB   CRM    ERP    Clickstream (Kafka)   Partner APIs
                    │        │      │          │                    │
 INGESTION      CDC, batch connectors, streaming (Kafka, Kinesis, Event Hubs)
                    │
 STORAGE        Bronze (raw)  ─►  Silver (clean)  ─►  Gold (business)
                Open table formats on object storage, or a cloud warehouse
                    │
 PROCESSING     Spark, SQL warehouses, dbt, stream processors
                    │
 SERVING        BI tools, ML models, data sharing, APIs, reverse ETL
 ───────────────────────────────────────────────────────────────────────
 ACROSS ALL     Governance │ Catalog │ Security │ Lineage │ Quality │ Cost

The data movement patterns behind this picture, such as Lambda, Kappa and medallion, are covered in the Data Architecture Patterns card.

The same layers on every platform

The layers stay the same whichever vendor you choose; only the product names change. This map is the most useful single table for an architect working across clouds.

Databricks and Snowflake run on all three clouds, and Kafka (or Confluent), Airflow, dbt and Tableau run almost anywhere. These cross-cloud tools are often chosen precisely to avoid being tied to one cloud.

Cloud product names change often. For example, Google renamed Dataplex to Knowledge Catalog in April 2026, and AWS has moved DataZone into SageMaker. Check current names when writing a design document.

The decisions an architect records

Big choices should be written down with their reasons, as an architecture decision record (ADR). When someone asks in two years why the company chose Delta over Iceberg, the answer is in the record, not in a person's memory.

ADR-007: Use Unity Catalog as the single governance layer

Status:   Accepted (2026-03-14)
Context:  Three teams manage access separately; an audit could not
          list who can read customer emails.
Decision: Register every table in Unity Catalog. Grant access to
          groups, never users. Tag personal data with governed tags.
Options:  Per-workspace Hive metastore (rejected: no central audit),
          external catalog only (rejected: no enforcement).
Impact:   All new tables must be Unity Catalog managed tables.
          Existing Hive tables are migrated by June.

Non-functional requirements

Most architecture is driven by requirements that are not features. Agree on these numbers early, because they decide cost and design.

3. Architecture Styles

Before choosing products, an architect chooses a style: where data is stored, who owns it, and how it is accessed. Most enterprises combine several.

Operational and analytical data

Keeping them separate protects the order system from heavy reports. Data moves from operational to analytical systems through the integration patterns in the next section.

Open table formats: the key to avoiding lock-in

Delta Lake, Apache Iceberg and Apache Hudi add transactions, schema evolution and time travel to Parquet files in your own storage. Because the format is open, several engines can read the same data: Spark, Trino, Snowflake, BigQuery, Athena, Fabric and Databricks all read Iceberg or Delta to some degree. Catalogs that speak the Iceberg REST protocol, such as Apache Polaris, Unity Catalog and Snowflake's catalog, let engines share one table definition. For an architect, this separates the choice of storage from the choice of engine. The next section explains which format to choose.

Compare the formats in the Delta Lake, Apache Iceberg and Apache Hudi cards. Interoperability features change quickly, so check current support for each engine before committing.

4. Medallion Architecture and Data Layers

The medallion architecture organises a lake or lakehouse into layers of increasing quality: bronze (raw), silver (cleaned and conformed) and gold (business-ready). Databricks popularised the name, but the idea is older and platform-neutral: warehouses have long used staging, integration and mart layers, and Snowflake, Fabric, BigQuery and AWS lakes use the same layering under other names.

It is a design pattern, not a product. Each layer has a clear contract: what it contains, who writes it, who may read it, and what quality it guarantees. That contract is what makes data traceable, replayable and trustworthy.

SOURCES  ->  LANDING  ->  BRONZE  ->  SILVER  ->  GOLD  ->  SERVING (BI, APIs, ML, AI)

         SOURCES          LANDING          BRONZE           SILVER           GOLD
         databases, SaaS, files exactly    raw history,     cleaned, typed,  business models,
         devices, events, as delivered,    append-only,     deduplicated,    KPIs, star
         files, APIs      short-lived      + load metadata  conformed keys   schemas

quality  unknown          unknown          as the source    validated        certified
users    source teams     ingestion jobs   engineers        engineers, DS    analysts, BI

Every layer at a glance

Why each layer matters

What each layer does, in detail

Tools used in each layer

The same layers on the cloud platforms

Product names move quickly: Databricks renamed Delta Live Tables to Lakeflow Declarative Pipelines and Workflows to Lakeflow Jobs in 2025. Check the current name before an interview, but explain the role of the tool, which does not change.

Tested: a medallion pipeline end to end

We dropped two JSON files into a landing folder. The second file contained a re-sent duplicate, an amount of "abc", a truncated line, and a later update to order 1. Then we built each layer with PySpark and Delta Lake:

from pyspark.sql import functions as F, Window

# BRONZE: read everything as strings, keep bad lines, add load metadata
bronze = (spark.read
    .schema("order_id STRING, customer STRING, amount STRING, "
            "status STRING, updated_at STRING, _corrupt_record STRING")
    .option("columnNameOfCorruptRecord", "_corrupt_record")
    .option("recursiveFileLookup", "true")  # landing/orders/2026-11-07/*.json
    .json("/lake/landing/orders")
    .withColumn("_source_file", F.col("_metadata.file_name"))
    .withColumn("_ingested_at", F.current_timestamp()))
bronze.write.format("delta").mode("append").save("/lake/bronze/orders")

# SILVER: type, validate, quarantine, keep the latest version per key
br = spark.read.format("delta").load("/lake/bronze/orders")
typed = (br.filter("_corrupt_record IS NULL")
    .withColumn("order_id", F.col("order_id").cast("int"))
    .withColumn("amount", F.expr("try_cast(amount AS DECIMAL(10,2))"))
    .withColumn("updated_at", F.to_timestamp("updated_at")))

bad = typed.filter("amount IS NULL OR amount < 0")
bad.write.format("delta").mode("append").save("/lake/quarantine/orders")

w = Window.partitionBy("order_id").orderBy(F.col("updated_at").desc())
silver = (typed.filter("amount IS NOT NULL AND amount >= 0")
    .withColumn("rn", F.row_number().over(w)).filter("rn = 1")
    .drop("rn", "_corrupt_record"))
silver.write.format("delta").mode("overwrite").save("/lake/silver/orders")

# GOLD: answer a business question
gold = (silver.groupBy("customer")
    .agg(F.count("*").alias("orders"), F.sum("amount").alias("revenue")))
gold.write.format("delta").mode("overwrite").save("/lake/gold/customer_revenue")

A gotcha we hit: without recursiveFileLookup, reading /lake/landing/orders returned 0 rows with no error, because the files were in a date subfolder that was not named key=value. Spark only discovers subfolders as partitions in that form. Always check row counts after a landing read.

On Databricks: declarative pipelines with expectations

On Databricks the same flow is usually written as a Lakeflow Declarative Pipeline. Auto Loader picks up only new files, and expectations record, drop or fail on bad rows, with the results shown in the pipeline's event log:

import dlt  # also: from pyspark import pipelines as dp
from pyspark.sql import functions as F

@dlt.table(comment="Raw orders, exactly as landed")
def bronze_orders():
    return (spark.readStream.format("cloudFiles")  # Auto Loader
        .option("cloudFiles.format", "json")
        .option("cloudFiles.schemaLocation", "/Volumes/prod/landing/_schemas/orders")
        .option("cloudFiles.schemaEvolutionMode", "rescue")  # unknown fields -> _rescued_data
        .load("/Volumes/prod/landing/orders/")
        .select("*", F.col("_metadata.file_path").alias("_source_file"),
                F.current_timestamp().alias("_ingested_at")))

@dlt.table(comment="Clean orders")
@dlt.expect_or_drop("valid_amount", "amount IS NOT NULL AND amount >= 0")
@dlt.expect("has_customer", "customer IS NOT NULL")  # record only
def silver_orders_clean():
    return (dlt.read_stream("bronze_orders")
        .withColumn("amount", F.expr("try_cast(amount AS DECIMAL(10,2))")))

dlt.create_streaming_table("silver_orders")
# create_auto_cdc_flow was called apply_changes before 2025
dlt.create_auto_cdc_flow(target="silver_orders", source="silver_orders_clean",
                  keys=["order_id"], sequence_by=F.col("updated_at"),
                  stored_as_scd_type=1)

@dlt.table(comment="Revenue per customer")
def gold_customer_revenue():
    return (dlt.read("silver_orders").groupBy("customer")
        .agg(F.count("*").alias("orders"), F.sum("amount").alias("revenue")))

On Snowflake: stages, Snowpipe and Dynamic Tables

-- landing: an external stage over cloud storage
CREATE STAGE landing.orders_stage URL = 's3://acme-landing/orders/'
  STORAGE_INTEGRATION = s3_int FILE_FORMAT = (TYPE = JSON);

-- bronze: Snowpipe loads each new file as it arrives
CREATE TABLE bronze.orders (raw VARIANT, _source_file STRING, _ingested_at TIMESTAMP_LTZ);
CREATE PIPE bronze.orders_pipe AUTO_INGEST = TRUE AS
  COPY INTO bronze.orders
  FROM (SELECT $1, METADATA$FILENAME, CURRENT_TIMESTAMP() FROM @landing.orders_stage);

-- silver: a Dynamic Table keeps itself up to date
CREATE DYNAMIC TABLE silver.orders TARGET_LAG = '15 minutes' WAREHOUSE = transform_wh AS
SELECT raw:order_id::INT AS order_id, raw:customer::STRING AS customer,
       TRY_TO_DECIMAL(raw:amount::STRING, 10, 2) AS amount,
       raw:updated_at::TIMESTAMP_NTZ AS updated_at
FROM bronze.orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1;

-- gold
CREATE DYNAMIC TABLE gold.customer_revenue TARGET_LAG = '1 hour' WAREHOUSE = transform_wh AS
SELECT customer, COUNT(*) AS orders, SUM(amount) AS revenue FROM silver.orders GROUP BY customer;

How to lay out the layers

Always separate environments (dev, test, prod) first and layers second. Grant users read access to gold by default, silver on request, and bronze to engineers only.

Variations you will meet

Common mistakes

5. Edge, Ingestion and the Landing Zone

Before the medallion layers begin, data has to travel from where it is produced, the edge, into the platform. This is where most real-world failures start: missing files, duplicates, half-written files, schema surprises, firewalls, and sources that cannot take extra load.

What the edge layer is

The edge is everything outside the central platform that produces or first collects data: factory sensors and gateways, shop tills, mobile apps, branch servers, on-premise databases, partner SFTP servers and SaaS applications. In IoT architectures the edge also has its own compute, an edge gateway, which does useful work before sending anything:

• Buffering when the network drops, so no readings are lost (store and forward)
• Filtering and aggregating, for example sending one-minute averages instead of every 10 ms reading
• Protocol translation from OPC UA, Modbus or MQTT to HTTPS or Kafka
• Compression and encryption before data crosses the internet
• Local decisions such as stopping a machine, without waiting for the cloud

Two meanings of "landing zone"

Both matter in an interview. Cloud landing zones are built with AWS Control Tower, the Azure landing zone accelerators (and Cloud Adoption Framework data landing zones), or the Google Cloud landing zone blueprints, usually as Terraform. Build it before any data arrives, because moving networks and permissions later is painful.

How data lands: the ingestion patterns

Connecting the edge to the cloud

How data should arrive in the landing zone

1. Agree an ingestion contract per source. Owner, format, schema, delivery frequency, file naming, expected volume, classification and retention. No contract, no onboarding.

2. Use a predictable path. landing/<source>/<dataset>/<yyyy-mm-dd>/<file>_<timestamp>.json, so files are easy to find, replay and expire.

3. Make "complete" explicit. Write to a temporary name and rename, or send a _SUCCESS marker or manifest listing files, row counts and checksums. Never process a file still being written.

4. Make it write-once. Ingestion roles may add files but not change or delete them; object lock or immutability policies where audit requires it.

5. Validate on arrival. Checksum, row count against the manifest, file format, malware scan for external files. Move failures to a rejected folder and alert the source owner.

6. Load into bronze incrementally and idempotently. Auto Loader, Snowpipe or COPY INTO track which files were already loaded, so a re-run never loads a file twice.

7. Expire the landing zone. Once a file is safely in bronze, lifecycle rules delete or archive it after days or weeks. Landing is a buffer, not a store.

Tested: what happens when a load runs twice

Jobs get re-run after failures, so loading from landing into bronze must be idempotent: a re-run must not load the same file again. We put two files of 5 rows each in a landing folder and compared a plain batch load with a streaming load that keeps a checkpoint:

# plain batch: no memory of what was loaded
spark.read.schema(schema).json("/lake/landing/orders")      .write.format("delta").mode("append").save("/lake/bronze/orders")

# incremental: the checkpoint records which files were processed
(spark.readStream.schema(schema).json("/lake/landing/orders")
     .writeStream.format("delta")
     .option("checkpointLocation", "/lake/_checkpoints/bronze_orders")
     .trigger(availableNow=True)  # process what is there, then stop
     .start("/lake/bronze/orders"))

Managed tools give the same guarantee: Auto Loader (a streaming read with file tracking that scales to millions of files), Databricks and Snowflake COPY INTO, which skip files they have already loaded unless forced, and Snowpipe, which keeps load history per pipe:

COPY INTO prod_bronze.shop.orders
FROM '/Volumes/prod/landing/shop/orders/'
FILEFORMAT = JSON
FORMAT_OPTIONS ('rescuedDataColumn' = '_rescued_data')
COPY_OPTIONS ('mergeSchema' = 'true');     -- 'force' = 'true' would reload everything

Typical enterprise flow, end to end

 Factory sensors -> IoT Edge gateway -(MQTT/HTTPS)-> IoT Hub -> Event Hubs --+
 Oracle ERP ---- CDC (GoldenGate / Debezium) -----------------> Kafka ------+-> bronze (streaming)
 Salesforce ---- Fivetran / Lakeflow Connect ---------------------------------> bronze
 Partner CSVs -- SFTP (Transfer Family) -> s3://landing/partner/ -- Auto Loader -> bronze
                                              |
                       checksum, manifest, schema check, lifecycle expiry
 bronze -> silver (quality, dedupe, conform) -> gold (marts, KPIs) -> Power BI / APIs / ML

6. Choosing an Open Table Format

An open table format turns a folder of Parquet files into a real table: transactions, schema evolution, time travel and efficient updates, while the data stays in your own storage. The choice matters because it decides which engines can read and write your data for years. There is no single winner; there is a right answer for each situation.

Which one, in which scenario

Which engines must WRITE the tables?
  ├─ Mostly Databricks or Fabric ............... Delta (+ UniForm for readers)
  ├─ Many engines, or Snowflake / BigQuery ..... Iceberg
  └─ Flink-heavy streaming ..................... Paimon or Hudi
Then check the workload:
  ├─ Update-heavy CDC with tight latency ....... consider Hudi or MoR
  └─ Mostly append and large scans ............. any; follow the engines

You do not always have to choose just one

Format versions are a compatibility decision

Formats keep adding features that older readers cannot understand. Delta tracks this with reader and writer protocol features; Iceberg with format versions. Iceberg v3, which adds deletion vectors, row lineage and the VARIANT type, became generally available in Snowflake in May 2026 and was in public preview on Databricks in April 2026. A v3 table is unreadable by engines that only support v2, so upgrade a format version only after every reader supports it.

Details of each format are in the Delta Lake, Apache Iceberg and Apache Hudi cards. Feature support moves quickly; check each engine's documentation for the exact version you run.

7. Data Integration Patterns

Moving data between systems is where most enterprise complexity lives. The right pattern depends on how fresh the data must be and how much load the source can take.

Tools by pattern

How the retailer combines them

• Orders database to bronze: CDC, because orders change and finance needs them within minutes
• Website clicks: event streaming through Kafka
• Supplier prices from a partner: an API, pulled hourly
• CRM: batch ELT through a managed connector each night
• Customer churn scores back to the CRM: reverse ETL

CDC captures deletes, which matters for privacy: when a customer is deleted at the source, the delete flows to the analytics copy. Plain batch snapshots miss deletes unless you compare full extracts.

8. Compatibility and Schema Evolution

Data outlives the code that writes it. A schema change that is harmless for the producer can break ten consumers, so an architect defines which changes are allowed, how they are checked, and how breaking changes are rolled out.

The four kinds of compatibility

Schema registries add transitive variants: the new schema must be compatible with all earlier versions, not only the previous one. This matters when consumers can replay old data, such as a Kafka topic kept for months.

Tested: which changes break readers

We wrote Avro records with one schema and read them with another, in both directions:

The pattern to remember: defaults make changes safe, renames are almost never free, and an alias only helps the new reader. That is why many teams default to full compatibility for shared topics: add optional fields with defaults, and never rename or retype in place.

Schema registries

A registry stores every version of a schema and rejects incompatible versions when the producer registers them, so a breaking change fails at deploy time rather than in a consumer at 3 a.m. Protobuf follows similar rules through field numbers: never reuse or renumber a field.

Tables evolve differently

Tested in the other cards: on Delta Lake 3.2, a rename and a drop both failed until column mapping was enabled, and changing INT to BIGINT failed. Appending an extra column to plain Parquet succeeded, but the new column was invisible when reading the folder.

Rolling out a breaking change: expand and contract

1. Expand. Add the new column or field alongside the old one, for example email_address next to email.

2. Dual-write. Producers fill both, so old and new consumers keep working.

3. Migrate consumers. Lineage lists every consumer; each moves to the new field, tracked to completion.

4. Deprecate. Announce a removal date in the catalog and data contract.

5. Contract. Remove the old field once nothing reads it, which lineage and query logs can prove.

Other compatibility an architect manages

9. Reliability and Consistency Patterns

Pipelines fail: networks drop, jobs are retried, data arrives late or twice. Reliable architecture does not try to prevent every failure; it makes failure safe to repeat.

Write-audit-publish on different platforms

Consistency across systems

A single Delta, Iceberg or Snowflake table gives ACID transactions. Across systems there is no shared transaction: a row can be in the orders database but not yet in the lakehouse. Architects design for eventual consistency and make the delay explicit, with a freshness SLA per dataset.

• CAP: during a network partition a distributed system must choose between consistency and availability; most data platforms choose availability and catch up later
• Transactional outbox: an application writes the business change and an event row in the same database transaction, and a relay publishes the event, so the two cannot disagree (see the Data Architecture Patterns card)
• Reconciliation: compare counts and totals between source and target daily; it is the safety net for every pattern above

The idempotency patterns are tested in the Data Modelling and dbt cards, where MERGE reruns created no duplicates while plain append did.

10. Scalability, Performance and Workload Isolation

Modern platforms separate storage from compute: data sits once in cheap object storage or a warehouse's storage layer, and any number of compute clusters read it. This is what lets an architect scale and isolate workloads independently.

Isolation by workload

                 one copy of the data
                          │
   ┌──────────────┬───────┴──────┬──────────────┐
   │ ETL compute   │ BI warehouse │ Data science  │ Ad hoc SQL
   │ scheduled,    │ always on,   │ GPU or large  │ small, auto-
   │ job clusters  │ auto-scaling │ memory        │ suspends
   └──────────────┴──────────────┴──────────────┘
     each billed and tagged to its own team

Multi-tenancy

Capacity planning

Estimate from data volume, growth rate, concurrency and freshness: for example, "5 TB growing 60% a year, 200 dashboard users at 9 a.m., hourly refresh". Load-test with realistic data before go-live, because skew, small files and concurrency show up only at real scale. Then set budgets and alerts, since elastic platforms scale costs as easily as performance.

11. Event-Driven Architecture

Instead of systems asking each other for data, an event-driven architecture has systems publish facts, such as OrderPlaced, to a durable log that many consumers read independently.

Design decisions

• Choose the key carefully: ordering is only guaranteed within a partition, so key by the entity whose order matters, such as order_id
• Govern schemas: every topic has a registered schema and a compatibility mode
• Avoid personal data in topics where possible: messages are hard to delete from a retained log, so send IDs and keep details in a governed table
• Plan replay: retention must cover the longest backfill you may need
• Land events in the lakehouse: bronze tables keep the full history for analytics, beyond the topic's retention

12. Enterprise Modelling and the Semantic Layer

Physical models were covered in the Data Modelling card. At enterprise level the architect also standardises meaning, so that "revenue" or "active customer" is the same number in every tool.

Semantic layer options

A semantic layer also matters for AI: natural-language tools such as chat-with-data assistants answer far more reliably when metrics and relationships are defined explicitly rather than guessed from table names.

Standards worth fixing early

• Naming: snake_case, consistent prefixes such as dim_ and fact_, no abbreviations outside an agreed glossary
• Keys: surrogate keys in dimensions, business keys kept for tracing
• Time: UTC storage, an explicit time zone column where local time matters
• Money: DECIMAL with currency code; never mix currencies in one column
• Nulls and unknowns: a defined unknown member rather than silent NULLs

13. Challenges in Data Modelling

Drawing a star schema is easy. The hard part is everything that happens around it: unclear requirements, messy sources, changing history, data that arrives late and tables that grow to billions of rows. Interviewers ask about these challenges because they show whether you have modelled real data or only read about it.

The challenges at a glance

Grain: the challenge behind most wrong numbers

The grain is what one row means. If a table mixes order headers and order lines, or a join quietly changes one row per order into one row per order line, every sum after that is wrong. Most "the dashboard is wrong" incidents trace back to grain.

• Declare the grain in writing for every fact table and put it in the table comment
• Enforce it with a uniqueness test on the grain columns, such as dbt unique on order_line_id
• Never store measures of two different grains in one fact; build two facts and combine them as the next section shows

History: joining to the right version

With SCD Type 2 a customer has one row per version. Joining facts to it on the business key alone matches every version, which duplicates rows. Join on the version valid on the event date, or store the version's surrogate key in the fact at load time.

-- dim_customer_scd2: C1 lived in Pune until 2026-06-30, then Mumbai
SELECT o.order_id, d.customer_sk, d.city, o.amount
FROM orders o
JOIN dim_customer_scd2 d
  ON  o.customer_id = d.customer_id
  AND o.order_date BETWEEN d.valid_from AND d.valid_to;

Tested on PySpark 3.5. A date range join cannot use a plain equi-join alone, so on very large tables resolve the surrogate key once at load time and store it in the fact; queries then join on a simple key.

Late-arriving dimensions: inferred members

An order for customer C9 arrives before the CRM sends C9. An inner join drops the order; a left join leaves a NULL key. The standard fix is an inferred member: insert a placeholder row for C9 now, key the fact to it, and overwrite the placeholder's attributes when the real row arrives. The fact never needs to be touched again.

INSERT INTO dim_customer
SELECT (SELECT MAX(customer_sk) FROM dim_customer)
         + ROW_NUMBER() OVER (ORDER BY o.customer_id) AS customer_sk,
       o.customer_id, 'Unknown (pending)' AS name, true AS is_inferred
FROM (SELECT DISTINCT customer_id FROM staged_orders) o
LEFT ANTI JOIN dim_customer d ON o.customer_id = d.customer_id;

Tested: C9 got key 3 with is_inferred = true and both orders loaded. In production, use an identity column or a hash key instead of MAX + ROW_NUMBER, which is not safe when two loads run at once.

Many-to-many: bridge tables

A joint bank account has two owners. Join the balance fact to customers directly and the balance is counted twice. A bridge table (account_id, customer_id, weight) sits between them; weights such as 0.5 and 0.5 allocate the measure so totals still add up, and queries that need "accounts per customer" can ignore the weight.

A checklist before you call a model done

• Grain written down and tested for uniqueness
• Every dimension conformed or deliberately local
• SCD type chosen per attribute, not per table
• Unknown and inferred members defined; no NULL foreign keys
• Late facts and deletes handled, with a reprocessing window
• Metrics defined once; totals reconciled to the source system
• PII tagged; partitioning and clustering chosen for the main queries

Interview-ready summary

14. Joining Two Fact Tables

A classic interview question: "What happens when you join two fact tables, given that facts are huge?" The short answer is that you usually get wrong numbers and a very expensive query at the same time. The good answer explains why, and what to do instead.

Why fact-to-fact joins go wrong

Two fact tables usually share only dimensions (customer, product, date), not a one-to-one key. Joining on a shared dimension is a many-to-many join: every sales row for a customer pairs with every return row for that customer. Kimball calls the result a fan trap (or chasm trap): rows multiply and measures are counted many times.

fact_sales (grain: order line)          fact_returns (grain: return line)
customer  line  sales                   customer  return  amount
C1        L1    100                     C1        R1      40
C1        L2     50                     C1        R2      30
C1        L3     30
C2        L4     80

JOIN ON customer_id  ->  C1: 3 sales rows x 2 return rows = 6 rows
                         C2: no returns, so an inner join drops C2 entirely

SELECT s.customer_id,
       COUNT(*)             AS joined_rows,
       SUM(s.sales_amount)  AS total_sales,
       SUM(r.return_amount) AS total_returns
FROM fact_sales s
JOIN fact_returns r ON s.customer_id = r.customer_id
GROUP BY s.customer_id;

Tested on PySpark 3.5. Each sales row is repeated once per matching return row and each return row once per sales row. No error is raised: the query looks fine and the numbers are silently wrong.

What happens to the engine at huge volume

The output size of a many-to-many join is the sum over every key of (rows on the left) × (rows on the right). With billions of rows that number becomes astronomical, and the engine has to do all the work below before it can return anything:

A tested example at small scale: 300,000 sales rows and 30,000 return rows over 1,000 customers.

Scale the same shape to 3 billion sales rows and the naive join would produce trillions of rows. That is why the query either runs for hours or fails, and why the answer would be wrong even if it finished.

Estimate the damage before you join

Counting the rows per key on each side tells you how big a join will be, using only cheap aggregations:

SELECT SUM(s.n * r.n) AS rows_the_join_will_produce,
       MAX(s.n * r.n) AS rows_from_worst_key
FROM (SELECT customer_id, COUNT(*) AS n FROM fact_sales   GROUP BY customer_id) s
JOIN (SELECT customer_id, COUNT(*) AS n FROM fact_returns GROUP BY customer_id) r
  ON s.customer_id = r.customer_id;

Tested: it predicted 9,000,000 rows and a worst key of 9,000, matching the real join exactly. If the result is far above the larger input, the join is many-to-many.

The right patterns

1. Drill-across: aggregate first, then join

Bring each fact to the same grain on conformed dimensions, then join the small results. This is Kimball's drill-across, and it is what BI tools such as Power BI and Looker do behind the scenes when one report uses two facts through shared dimensions.

WITH s AS (SELECT customer_id, SUM(sales_amount) AS total_sales
           FROM fact_sales GROUP BY customer_id),
     r AS (SELECT customer_id, SUM(return_amount) AS total_returns
           FROM fact_returns GROUP BY customer_id)
SELECT COALESCE(s.customer_id, r.customer_id) AS customer_id,
       COALESCE(s.total_sales, 0)             AS total_sales,
       COALESCE(r.total_returns, 0)           AS total_returns
FROM s FULL OUTER JOIN r ON s.customer_id = r.customer_id;

Result: C1 180 and 70, C2 80 and 0. Use a full outer join so customers that appear in only one fact are kept. The aggregations are cheap because engines aggregate partially before the shuffle, and the join is now one-to-one on far fewer rows.

2. Union, then aggregate (no join at all)

SELECT customer_id,
       SUM(sales_amount)  AS total_sales,
       SUM(return_amount) AS total_returns
FROM (
  SELECT customer_id, sales_amount, 0 AS return_amount FROM fact_sales
  UNION ALL
  SELECT customer_id, 0, return_amount FROM fact_returns
) t
GROUP BY customer_id;

Same correct result. Stacking the facts and grouping once avoids the join and its skew entirely, and scales well because a group-by can combine rows partially before the shuffle, unlike a join.

3. Join at the true shared grain

Sometimes two facts really are related row to row: a return points to the order line it reverses. Then join on that key (often a degenerate dimension such as order_line_id), after aggregating the side that can have several rows per key:

SELECT s.order_line_id, s.sales_amount,
       COALESCE(r.returned, 0)                  AS returned,
       s.sales_amount - COALESCE(r.returned, 0) AS net_amount
FROM fact_sales s
LEFT JOIN (SELECT order_line_id, SUM(return_amount) AS returned
           FROM fact_returns GROUP BY order_line_id) r
  ON s.order_line_id = r.order_line_id;

Tested: L1 100 − 40 = 60, L3 30 − 30 = 0, L2 and L4 unchanged. The join is now one-to-one, so no row multiplies.

4. Model it once: consolidated and accumulating facts

When you really must join two large facts

# Salting: spread a hot key across 16 tasks
from pyspark.sql import functions as F
SALT = 16
fact = big_fact.withColumn("salt", (F.rand() * SALT).cast("int"))
other = small_side.crossJoin(spark.range(SALT).withColumnRenamed("id", "salt"))
joined = fact.join(other, ["customer_id", "salt"])

Tested: a key holding 150,000 of 200,000 rows was split into 16 pieces of about 9,300 rows each, and the salted join returned exactly the same count and sum as the plain join. Salt the big side randomly and copy the other side once per salt value; it only makes sense when the other side is small enough to copy.

== physical plan of the naive join, with broadcast disabled ==
SortMergeJoin [customer_id], [customer_id], Inner
:- Sort [customer_id ASC]
:  +- Exchange hashpartitioning(customer_id, 8)     <- shuffle of fact 1
+- Sort [customer_id ASC]
   +- Exchange hashpartitioning(customer_id, 8)     <- shuffle of fact 2

Simplified from the tested plan. Two Exchange nodes mean both facts cross the network; that is the cost you pay before a single row is matched.

Common mistakes

Interview-ready summary

15. Data Governance

Data governance is the set of roles, rules and processes that decide how data is owned, defined, protected and used. Tools enforce it, but governance starts with people: if nobody owns the customer table, no tool can decide who should see it.

The three parts of governance

Roles

Operating models

Our retailer chooses the federated model. A small central team defines classification levels, naming rules and security standards. The Sales, CRM and Finance domains each own their data and apply those rules.

Policies a company needs

• Data classification policy: the sensitivity levels and how to label data
• Access policy: who approves access, and how often it is reviewed
• Retention policy: how long each kind of data is kept
• Data quality policy: required checks and who fixes failures
• Acceptable use policy: what data may be used for, including AI

Many companies follow DAMA-DMBOK, the Data Management Body of Knowledge, which puts data governance at the centre of ten other knowledge areas, including architecture, modelling, security, quality and metadata.

Governance tools

A common pattern is two layers: the platform's own catalog, such as Unity Catalog, enforces rules where the data lives, and an enterprise catalog, such as Collibra or Purview, describes data across every system for business users.

16. Metadata and Catalogs

Governance needs metadata, meaning data about data. Without it, nobody can find a table, trust a definition or tell which column holds an email address.

How Unity Catalog organises data

Metastore (one per region)
  └─ Catalog        prod_sales            often one per domain or environment
       └─ Schema    gold
            ├─ Table     fact_order_line
            ├─ View      v_customer_masked
            ├─ Volume    raw_files           non-table files
            ├─ Function  mask_email          UDFs, masks, filters
            └─ Model     churn_model         ML models

Full name: catalog.schema.object -> prod_sales.gold.fact_order_line

Each object can carry the metadata above: a comment for the business definition, an owner, and tags for classification.

COMMENT ON TABLE prod_sales.gold.dim_customer IS
  'One row per version of a customer (SCD2). Owner: CRM team.';

ALTER TABLE prod_sales.gold.dim_customer OWNER TO `crm-owners`;

ALTER TABLE prod_sales.gold.dim_customer
  ALTER COLUMN email SET TAGS ('pii' = 'email');

A business glossary

A glossary defines business terms once, such as Active Customer: placed an order in the last 90 days, and links each term to the tables and columns that implement it. This is what stops three dashboards from showing three different numbers for the same thing.

Catalogs on other platforms

The architectural decision is which catalog is the source of truth for each kind of metadata. A common answer: each platform's own catalog enforces access, and one enterprise catalog collects metadata, glossary and lineage from all of them.

17. Data Classification

Before deciding how to protect data, you must know how sensitive it is. Classification gives every dataset, and ideally every column, a label that the rest of the platform can act on.

Types of sensitive data

Classification only helps if it drives action. In Unity Catalog, a governed tag is a tag from an approved list, so it cannot be misspelled, and access policies can use it directly, as the security section shows. Databricks documents a safe default: tag every new object as unverified, block access until it is reviewed, then apply the right tags.

Finding sensitive data automatically

In a large estate you cannot tag every column by hand. Discovery tools scan data and suggest classifications, which stewards then confirm.

18. Data Privacy

Privacy is about using personal data fairly and lawfully: collecting only what you need, using it only for the stated purpose, and respecting people's rights. Security is about protecting data from unauthorised access. A perfectly secure system can still break privacy law, for example by keeping data for too long or using it for something nobody agreed to.

Privacy principles

Laws and standards an architect meets

This is a summary for architecture planning, not legal advice. Confirm exact obligations and dates with your legal team.

Privacy techniques

Tested: hashing is not anonymisation

A common mistake is to hash emails with SHA-256 and call the data anonymous. We hashed two emails, then hashed a list of guessed emails and joined the two:

from pyspark.sql import functions as F

hashed = customers.select(F.sha2("email", 256).alias("email_hash"), "spend")

guesses = candidate_emails.select(
    "email", F.sha2("email", 256).alias("email_hash"))

hashed.join(guesses, "email_hash").select("email", "spend").show()
# Both customers were re-identified, with their spend.

Anyone with a list of likely emails can reverse a plain hash. A keyed hash, mixing in a secret held in a secret store, stopped the attack in our test: 0 matches. The same email still produced the same value, so joins kept working.

key = dbutils.secrets.get("privacy", "hash_key")  # never in code
pseudo = F.sha2(F.concat(F.lit(key), F.col("email")), 256)

Under laws such as GDPR, keyed hashes are still personal data, called pseudonymised data, because whoever holds the key can link them back. They lower risk; they do not remove legal duties. A dedicated keyed function such as HMAC is the stronger form of this idea.

Tested: deleting a person from a Delta table

Erasure requests are harder in a lakehouse than they look, because Delta keeps old versions for time travel. We deleted customer C200 and checked what remained:

So erasure is a process, not a single statement: delete from every table that holds the person, including bronze and any copies, then let VACUUM remove the old files after the retention period. Lineage tells you which tables to check.

DELETE FROM prod_sales.silver.customer WHERE customer_id = 'C200';
DELETE FROM prod_sales.gold.dim_customer WHERE customer_id = 'C200';
-- after the retention period (default 7 days), old files are removed
VACUUM prod_sales.silver.customer;
VACUUM prod_sales.gold.dim_customer;

The default retention exists to protect running readers and time travel; do not set it to zero in production. Check your retention settings against the deadline the law gives you. Raw files in bronze, exports and backups also need a deletion plan.

Erasure on other platforms

Every modern platform keeps deleted data for a while to protect against mistakes, so the Delta lesson applies everywhere: a DELETE is only the first step, and the erasure is complete once the recovery copies expire.

Document these windows in your privacy notice and erasure process, because "deleted" usually means "deleted from live use now, and from recovery copies within a known period".

Data residency and cross-border transfers

Many laws care about where personal data is stored and processed. GDPR restricts transfers outside the EU unless safeguards are in place, and India's DPDP Act lets the government restrict transfers to certain countries. For an architect this becomes concrete: choose cloud regions deliberately, keep EU customers' personal data in an EU region, and make sure backups, logs, support access and analytics copies follow the same rule. Aggregated or anonymised data can usually move more freely.

19. Data Security and How to Implement It

Security protects data from unauthorised access, change or loss. No single control is enough, so architects use defence in depth: several layers, so that one mistake does not expose everything.

Implementation steps on any platform

Whatever the vendor, security is implemented in the same order. The Databricks example below and the other platforms after it all follow these steps.

This approach is often called zero trust: never trust a request because of where it comes from, verify every identity, and give the least access needed.

Example: implementing it on Databricks

Here is how the retailer answers the auditor's question, layer by layer.

1. Connect identity. Sync users and groups from the identity provider, such as Microsoft Entra ID or Okta, with SCIM. Create groups by role, for example analysts, crm-owners and data-engineers. Production jobs run as service principals, not as people.

2. Grant to groups, with least privilege. Each group gets only what its job needs. Because grants go to groups, moving a person between teams changes their access automatically.

GRANT USE CATALOG ON CATALOG prod_sales TO `analysts`;
GRANT USE SCHEMA  ON SCHEMA  prod_sales.gold TO `analysts`;
GRANT SELECT      ON SCHEMA  prod_sales.gold TO `analysts`;

GRANT ALL PRIVILEGES ON SCHEMA prod_sales.silver TO `data-engineers`;

SHOW GRANTS ON TABLE prod_sales.gold.dim_customer;

3. Protect sensitive columns. Analysts need customer counts by city, but not emails. A column mask hides the value for everyone outside an approved group, while the table stays the same.

CREATE FUNCTION prod_sales.security.mask_email(email STRING)
RETURNS STRING
RETURN CASE
  WHEN is_account_group_member('crm-owners') THEN email
  ELSE concat(left(email, 1), '***@', substring_index(email, '@', -1))
END;

ALTER TABLE prod_sales.gold.dim_customer
  ALTER COLUMN email SET MASK prod_sales.security.mask_email;

The masking expression itself was tested in Spark: asha@example.com became a***@example.com, and a card number became ************1111.

4. Limit rows where needed. Regional teams should see only their region's orders. A row filter applies that automatically to every query.

CREATE FUNCTION prod_sales.security.region_filter(region STRING)
RETURNS BOOLEAN
RETURN is_account_group_member('sales-all-regions')
    OR (region = 'IN' AND is_account_group_member('sales-india'))
    OR (region = 'EU' AND is_account_group_member('sales-europe'));

ALTER TABLE prod_sales.gold.fact_sales
  SET ROW FILTER prod_sales.security.region_filter ON (region);

5. Scale it with tags and ABAC. Writing a mask for each table does not scale to hundreds of tables. Attribute-based access control applies one policy to every column carrying a tag, including tables created later. Databricks now recommends ABAC for this, and it became generally available in April 2026.

-- tag the column once
ALTER TABLE prod_sales.gold.dim_customer
  ALTER COLUMN email SET TAGS ('pii' = 'email');

-- one policy covers every email column in the schema
CREATE POLICY mask_emails
ON SCHEMA prod_sales.gold
COLUMN MASK prod_sales.security.mask_email
TO `account users`
FOR TABLES
MATCH COLUMNS has_tag_value('pii', 'email') AS email_col
ON COLUMN email_col;

ABAC needs Databricks Runtime 16.4 or above, or serverless compute, and some parts are still in Beta. Row filters and column masks have limits: for example, they cannot be applied to views, and time travel and clones are not supported on tables that use them.

6. Encrypt and isolate. Data is encrypted at rest and in transit. For stricter control, use customer-managed keys in the cloud key service, so that revoking the key makes the data unreadable. Use private connectivity so traffic does not cross the public internet.

7. Keep secrets out of code. Store passwords and keys in a secret scope, or in a cloud vault behind it, and read them at runtime.

password = dbutils.secrets.get(scope="etl", key="crm_db_password")

8. Audit and review. Every access is logged. Query the audit system table to answer the auditor directly, and review grants every quarter.

-- Who read the customer table in the last 30 days?
SELECT event_time, user_identity.email, action_name
FROM system.access.audit
WHERE event_date >= current_date() - INTERVAL 30 DAYS
  AND request_params.full_name_arg = 'prod_sales.gold.dim_customer'
ORDER BY event_time DESC;

The Databricks grants, masks, filters, policies and audit queries above follow the Databricks documentation; they need a Unity Catalog workspace and were not run here. Check audit column names in your workspace before relying on the query.

The same controls on other platforms

Here is the email mask and the regional row filter from the Databricks example, on the other major platforms. The pattern is the same everywhere: a policy object, attached to a column or table, deciding per user at query time.

Snowflake uses masking policies and row access policies, both requiring Enterprise Edition or higher. A masking policy can also be attached to a tag, so every tagged column is masked.

CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('CRM_OWNER') THEN val
    ELSE '*********'
  END;

ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY email_mask;

CREATE OR REPLACE ROW ACCESS POLICY region_policy
AS (region VARCHAR) RETURNS BOOLEAN ->
  CURRENT_ROLE() = 'SALES_ALL'
  OR (region = 'IN' AND CURRENT_ROLE() = 'SALES_INDIA');

ALTER TABLE sales ADD ROW ACCESS POLICY region_policy ON (region);

Google BigQuery uses row access policies that grant rows to principals, and policy tags for column-level security and data masking. Data masking is applied on top of row-level security.

CREATE ROW ACCESS POLICY india_rows
ON retail.sales
GRANT TO ("group:sales-india@example.com")
FILTER USING (region = "IN");

CREATE ROW ACCESS POLICY all_rows
ON retail.sales
GRANT TO ("group:sales-all@example.com")
FILTER USING (TRUE);

In BigQuery, creating the first row access policy hides all rows from anyone not granted, which is why the second policy gives full access back to the right group.

AWS Lake Formation controls access to data in S3 through the Glue Data Catalog. Data filters provide column, row and cell-level security when granting SELECT, and are enforced in Athena, Redshift Spectrum, EMR and Glue. LF-Tags give tag-based access control: a user whose granted tags match a resource's tags can access it.

Microsoft offers controls in several places. Fabric's OneLake security defines roles with row-level rules, written as SQL predicates, and column-level rules on Delta tables. Azure SQL and SQL Server provide dynamic data masking and row-level security:

-- SQL Server / Azure SQL: mask a column
ALTER TABLE dbo.Customers
  ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

-- Row-level security: a predicate function plus a security policy
CREATE FUNCTION security.fn_region(@region NVARCHAR(10))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS allowed
WHERE IS_MEMBER('sales-all') = 1
   OR (@region = 'IN' AND IS_MEMBER('sales-india') = 1);

CREATE SECURITY POLICY security.RegionFilter
  ADD FILTER PREDICATE security.fn_region(Region) ON dbo.Sales
  WITH (STATE = ON);

PostgreSQL has row-level security built in:

ALTER TABLE sales ENABLE ROW LEVEL SECURITY;

CREATE POLICY region_access ON sales
  USING (region = current_setting('app.region'));

These examples follow each vendor's documentation and were not run here. Masking in SQL Server is a display-level control: users with ad hoc query access can sometimes infer values, so combine it with permissions.

Cross-platform security comparison

When data lives on several platforms, the risk is inconsistent rules: an email masked in Snowflake but visible in a copy on S3. Mitigations are to tag sensitive data consistently everywhere, generate policies from one definition (in code, or with a cross-platform tool such as Immuta or Privacera), and use lineage to find unprotected copies.

20. Data Lineage

Data lineage shows where data comes from, how it is transformed and where it goes. It answers the second half of the auditor's question: where does customer email data end up?

Levels of lineage

How lineage is captured

Example: lineage in Databricks

Unity Catalog captures lineage automatically for queries run on Databricks through Spark DataFrames or Databricks SQL. Here is what happens when the retailer's daily job runs:

1. The job runs as usual. Nothing in the code mentions lineage.

spark.sql("""
  CREATE OR REPLACE TABLE prod_sales.gold.rpt_daily_sales AS
  SELECT f.order_date, c.city, SUM(f.net_amount) AS revenue
  FROM prod_sales.gold.fact_order_line f
  JOIN prod_sales.gold.dim_customer c ON f.customer_sk = c.customer_sk
  GROUP BY f.order_date, c.city
""")

2. Unity Catalog records the links. From the query plan it learns that rpt_daily_sales reads fact_order_line and dim_customer, that revenue comes from net_amount, and which job or notebook ran it.

3. View it in Catalog Explorer. Open the table, choose the Lineage tab, then See Lineage Graph. Click a column to see its upstream and downstream columns, and filter for the jobs and dashboards that use the table.

4. Query it with SQL. Lineage is also in system tables, so it can drive reports and automation.

-- What reads dim_customer?
SELECT DISTINCT target_table_full_name, entity_type
FROM system.access.table_lineage
WHERE source_table_full_name = 'prod_sales.gold.dim_customer'
  AND event_date >= current_date() - INTERVAL 90 DAYS;

-- Where does the email column flow?
SELECT DISTINCT target_table_full_name, target_column_name
FROM system.access.column_lineage
WHERE source_table_full_name = 'prod_sales.silver.customer'
  AND source_column_name = 'email';

To get full lineage, register tables in Unity Catalog and refer to them by name, not by path, and avoid RDDs. These rules are worth writing into your coding standards.

Example: lineage outside Databricks with OpenLineage

OpenLineage is an open standard for lineage events. A Spark listener sends an event when a job starts and finishes, listing the datasets it read and wrote. Marquez is its reference server, and DataHub, Purview and others can receive the same events.

spark = (SparkSession.builder
  .config("spark.jars.packages",
          "io.openlineage:openlineage-spark_2.12:1.40.1")
  .config("spark.extraListeners",
          "io.openlineage.spark.agent.OpenLineageSparkListener")
  .config("spark.openlineage.transport.type", "http")
  .config("spark.openlineage.transport.url", "http://marquez:5000")
  .config("spark.openlineage.namespace", "retail")
  .getOrCreate())

We ran this on local Spark 3.5, sending events to a file instead of a server. The result was a useful lesson:

So lineage coverage depends on the engine version, the command and the table format, and no tool catches everything. Test lineage capture for your real job types before promising an auditor complete coverage. We saw the same on Databricks' list of limitations above.

Lineage on other platforms

In Snowflake, column lineage is recorded in the objects_modified field of ACCESS_HISTORY, and can be flattened into source-to-target pairs:

SELECT ah.query_start_time,
       om.value:"objectName"::STRING    AS target_table,
       col.value:"columnName"::STRING   AS target_column,
       src.value:"objectName"::STRING   AS source_table,
       src.value:"columnName"::STRING   AS source_column
FROM snowflake.account_usage.access_history ah,
     LATERAL FLATTEN(ah.objects_modified) om,
     LATERAL FLATTEN(om.value:"columns") col,
     LATERAL FLATTEN(col.value:"directSources") src
WHERE ah.query_start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP());

The Snowflake query follows the structure in Snowflake's documentation and was not run here. Retention differs widely between platforms, from 30 days to over a year, so copy lineage into your own store if audits need longer history.

Other lineage tools

21. Building Enterprise Lineage Across Systems

Unity Catalog covers what runs on Databricks. A real company also has source databases, APIs, Kafka, other Spark clusters and Airflow, so the architect has to join them into one picture. These seven steps build on each other.

 Source DBs    APIs      Kafka       Spark jobs      Airflow DAGs
     │          │          │              │                │
 scanners    manual    schema reg.   OpenLineage    OpenLineage
 (JDBC)      / API     + topics      listener       provider
     └──────────┴──────────┴──────┬───────┴────────────────┘
                                  ▼
                  CENTRAL METADATA PLATFORM
              (Collibra, DataHub, Atlas, Purview)
       technical metadata + glossary + owners + lineage graph
                                  │
              impact analysis • visualisation • audit

1. Collect metadata from every source. Each kind of system needs a different collector:

2. Capture all three levels. Job-level lineage says which run produced the data. Dataset-level lineage links tables. Column-level lineage links fields, which is what you need to trace personal data and judge the impact of a column change.

3. Use OpenLineage where it fits. Choose the open standard for anything you control, such as Spark and Airflow, so that you are not tied to one vendor. Use native connectors where they are richer, such as Unity Catalog inside Databricks. Keep dataset names consistent: OpenLineage identifies a dataset by namespace and name, so the same table must get the same name from every tool, or the graph splits.

4. Store it centrally. Send everything to one metadata platform. Collibra is strongest for business governance and stewardship workflows; DataHub is open source with strong lineage and many connectors; Apache Atlas suits Hadoop-based estates. Purview is the natural choice in Microsoft-heavy companies.

5. Connect technical and business metadata. Link each table and column to a glossary term and an owner. Then the lineage graph can say "the Revenue KPI comes from CRM and Orders data, owned by the Sales domain", not only "table A feeds table B".

6. Build impact analysis. Walk the graph upstream to find causes and downstream to find what breaks. Run it automatically: when a pull request changes a column, list the affected tables, dashboards and owners, and notify them before merging.

7. Provide visualisation and audit. Users explore the graph in the catalog. Auditors get evidence: lineage for personal data, access logs, and the history of who changed what. Keep lineage history for as long as your audit period requires.

The hard part is rarely the tools. It is coverage, naming and ownership, so measure them: the percentage of tables with an owner, and the percentage of pipelines sending lineage.

Lineage interview questions with answers

Q1. What is the difference between data lineage and data provenance?
Lineage is the map of how data flows: which sources, jobs and transformations lead to a dataset, and where it goes next. Provenance is the origin and history of specific data: where a record came from, when, from which source version, and every change made to it, often used as evidence. Lineage says "revenue comes from orders through this job"; provenance says "this row came from file orders_0612.csv, loaded at 06:02 by run 8841, and was corrected on 14 June". Delta table history and audit logs provide provenance; lineage graphs provide lineage.

Q2. What is column-level lineage?
It links each column to the source columns and transformation that produce it. For example, rpt_daily_sales.revenue comes from SUM(fact_order_line.net_amount), which comes from orders.quantity × orders.unit_price. It is needed to trace personal data such as email through every copy, to judge the impact of changing one column, and to explain a metric. Tools build it from the query plan or by parsing SQL. It breaks down inside UDFs and path-based tables, which hide the column mapping.

Q3. How do you capture lineage across 100 Spark pipelines?
Never instrument each pipeline by hand; capture lineage at the platform level so new pipelines are covered automatically. On Databricks, Unity Catalog does this, provided every pipeline uses Unity Catalog tables by name, avoids RDDs, and runs on supported compute. Elsewhere, add the OpenLineage listener once, in the cluster defaults, an init script or a shared job template, with one namespace convention. Then enforce it: a CI check rejects path-based writes and missing table registration, and a coverage report lists pipelines that sent no lineage in the last week. Finally, test each job type: in our test, OpenLineage recorded a Delta write's inputs but not its output.

Q4. How do you capture lineage from Kafka to Delta Lake?
A Structured Streaming job reads the topic and writes a bronze Delta table. On Databricks, Unity Catalog records the Delta tables, but a Kafka topic is not a Unity Catalog object, so register it as external metadata (system type Kafka, entity type Topic) and link it to the bronze table with the External Lineage API, including column mappings. That external lineage shows in Catalog Explorer, but not in the lineage system tables. With OpenLineage, the Spark listener reports the Kafka topic as an input dataset, provided your version supports the source. Also collect topic schemas from the schema registry so the columns can be matched.

Q5. How do you identify downstream impact when a source column changes?
Use column-level lineage to walk downstream from the column, then expand to the jobs, dashboards and owners attached to each result. On Databricks, query system.access.column_lineage repeatedly, starting from the changed column, and join to table_lineage for jobs and dashboards. In dbt, dbt ls --select model+ lists downstream models. Automate it in CI so a pull request that drops or renames a column shows the impact list and notifies owners, and use data contracts so breaking changes need agreement first.

# Walk downstream from one column, level by level
frontier = {("prod_sales.silver.customer", "email")}
seen = set()
while frontier:
    pairs = " OR ".join(
        f"(source_table_full_name = '{t}' AND source_column_name = '{c}')"
        for t, c in frontier)
    rows = spark.sql(f"""
        SELECT DISTINCT target_table_full_name AS t, target_column_name AS c
        FROM system.access.column_lineage
        WHERE ({pairs}) AND target_table_full_name IS NOT NULL""").collect()
    frontier = {(r.t, r.c) for r in rows} - seen
    seen |= frontier
print(sorted(seen))   # every downstream column that carries email

Q6. What is the difference between technical lineage and business lineage?
Technical lineage is system-level detail: tables, columns, jobs and SQL, for engineers. Business lineage is a simplified view in business terms: "the Monthly Revenue KPI is calculated from paid orders in the Order system, owned by Finance", for analysts, auditors and managers. Business lineage is built by linking technical assets to glossary terms and owners, and hiding intermediate steps.

Q7. How does Unity Catalog capture lineage?
Automatically, at runtime. When a query runs on Databricks compute through Spark DataFrames or Databricks SQL, Unity Catalog records which tables and columns were read and written, and which notebook, job, pipeline or dashboard ran it. It aggregates this across all workspaces on the metastore. You see it in Catalog Explorer and query it in system.access.table_lineage and column_lineage, kept 395 days by default. Tables must be registered in Unity Catalog. RDDs, global temp views, renamed objects and path-based column lineage are not captured, and external systems are added through external metadata.

Q8. What is OpenLineage, and how does it integrate with Airflow?
OpenLineage is an open standard for lineage events. Each event describes a run of a job with its input and output datasets, plus extra details called facets, such as schemas or column lineage. Airflow 2.7 and later integrate through the apache-airflow-providers-openlineage package. A listener fires when tasks start, complete or fail, and gets lineage from the operator in this order: a custom extractor, the operator's own OpenLineage methods, hook-level lineage, then manual inlets and outlets. You configure a transport, such as HTTP to Marquez or DataHub, or the console for testing. Where the operator supports it, Airflow passes parent run details to Spark, so a task links to the Spark job it launched.

Q9. How would you integrate Collibra with Databricks?
Collibra connects to Unity Catalog through an Edge site or a Collibra Cloud site. You create a Databricks Workspace connection, add the Technical Lineage for Databricks Unity Catalog capability, and synchronise manually or on a schedule, then review the ingestion report. It brings in databases, schemas, tables and columns with their lineage, with volumes and notebooks in preview, but not workflows. Use a service principal with only USE CATALOG, USE SCHEMA and SELECT; Collibra recommends a dedicated catalog of lineage views rather than direct access to query history tables. Then link the ingested assets to Collibra's glossary terms, owners and policies, so that business users see governed data with lineage.

Q10. How would you maintain lineage across multiple cloud platforms?
Choose one central metadata platform, such as DataHub, Collibra or Purview, and one standard, OpenLineage, for custom pipelines. Harvest native lineage from each platform: Unity Catalog per metastore, which means per region, AWS Glue, and Microsoft Fabric or Purview. Use a single naming convention for datasets, because the graph only joins up when the same dataset has the same identity everywhere. Register shared data, such as Delta Sharing recipients or cross-cloud copies, as explicit links. Run the collectors on a schedule, measure coverage per platform, and keep ownership and glossary terms in the central catalog, so the business view stays the same whichever cloud the data is in.

22. Data Quality

Governed, secure, traceable data is still useless if it is wrong. Data quality is measured on a few standard dimensions:

Where to check, and with what

-- Lakeflow Declarative Pipelines: rules live with the table
CREATE OR REFRESH STREAMING TABLE silver_orders (
  CONSTRAINT valid_amount EXPECT (amount > 0) ON VIOLATION DROP ROW,
  CONSTRAINT has_customer EXPECT (customer_id IS NOT NULL)
    ON VIOLATION FAIL UPDATE
) AS SELECT * FROM STREAM(bronze_orders);

Data contracts

A data contract is an agreement between the producer and consumers of a dataset: its schema, meaning, quality rules, freshness and owner. Breaking changes need notice. The dbt card shows one in practice: a model contract stops a build when a column's type changes.

Master data management

Customers often exist in several systems under different IDs, such as the CRM, the website and the loyalty app. Master data management (MDM) matches those records and creates one trusted golden record per customer, product or supplier. Tools include Informatica MDM, Reltio and Profisee, or matching rules built in Spark.

23. Data Quality KPIs at Enterprise Level

Checks catch bad rows; KPIs tell leadership whether data quality is improving across the whole company. A good enterprise scorecard measures three things: the data itself, the reliability of delivery, and how well the organisation responds when something breaks.

1. KPIs on the data (quality dimensions)

2. KPIs on delivery (reliability)

3. KPIs on the programme (governance and impact)

Rolling checks up into a score

Store every check result in one table, then roll it up by domain, table and dimension. Weight rules by severity so one failed critical rule counts more than many failed optional ones.

SELECT domain,
       ROUND(100 * SUM(rows_passed) / SUM(rows_checked), 2) AS row_pass_pct,
       ROUND(100 * SUM(w * rows_passed / rows_checked) / SUM(w), 2) AS dq_score
FROM (SELECT *, CASE severity WHEN 'critical' THEN 4 WHEN 'high' THEN 3
                              WHEN 'medium' THEN 2 ELSE 1 END AS w
      FROM dq_check_results WHERE run_date = '2026-11-07') t
GROUP BY domain;

SELECT COUNT(*) AS incidents,
       ROUND(AVG((unix_timestamp(detected_at, 'yyyy-MM-dd HH:mm')
                - unix_timestamp(started_at,  'yyyy-MM-dd HH:mm')) / 3600), 2) AS mttd_hours,
       ROUND(AVG((unix_timestamp(resolved_at, 'yyyy-MM-dd HH:mm')
                - unix_timestamp(detected_at, 'yyyy-MM-dd HH:mm')) / 3600), 2) AS mttr_hours,
       ROUND(SUM((unix_timestamp(resolved_at, 'yyyy-MM-dd HH:mm')
                - unix_timestamp(started_at,  'yyyy-MM-dd HH:mm')) / 3600), 2) AS data_downtime_hours,
       ROUND(100 * AVG(CASE WHEN found_by = 'consumer' THEN 1 ELSE 0 END), 0) AS pct_found_by_consumers
FROM dq_incidents;

Tested on PySpark 3.5 with two incidents: MTTD 1.75 h, MTTR 3 h, data downtime 9.5 h, 50% found by consumers. A freshness query over the same sample gave 50% SLA attainment for gold.fact_sales (one late load out of two).

Making KPIs work in practice

• Start with critical data elements: the 50 to 200 fields behind revenue, regulatory and customer reporting, not every column
• Set targets per tier: gold and regulatory data strict, exploratory data relaxed
• Publish one scorecard per domain, with an owner for each red number
• Show trends, not snapshots: a score moving from 92% to 97% tells a better story than 97% alone
• Collect results automatically from Lakeflow expectations, dbt tests, Great Expectations, Soda or Lakehouse Monitoring into one results table
• Tie KPIs to business impact in the language of the business: "the finance close was late twice because of data", not "null rate 0.4%"

Interview-ready summary

24. Master Data Management (MDM)

Master data is the small set of core business entities that many systems share: customers, products, suppliers, employees, locations and accounts. Master data management (MDM) is the discipline, process and tooling that creates one trusted version of each of those entities, the golden record, and keeps every system linked to it.

The problem it solves: the same customer exists in the CRM as "Asha Rao", in the web shop as "asha rao" with another city, and in the loyalty app as "A. Rao" with only a phone number. Without MDM, reports count three customers, marketing sends three emails and compliance cannot answer "what do we hold about this person?"

Three kinds of data

MDM vs a regular database vs a data warehouse

The key point: a database stores data for one application and a warehouse analyses data from many, but neither decides which record is the real customer. MDM is the system that decides, and both of the others then use its answer. In the warehouse, the MDM master ID becomes the business key of the conformed dim_customer.

  CRM        Web shop     Loyalty app      ERP
   |            |             |             |
   +------------+------+------+-------------+
                       v
              MDM HUB  (match -> merge -> survivorship -> steward review)
              golden records + cross-reference + hierarchies
                       |
         +-------------+--------------+
         v                            v
  back to source apps         data warehouse / lakehouse
  (corrected address,         dim_customer keyed by master_id,
   master ID stamped)         so every fact joins to one customer

How MDM works: the core steps

A tested match, merge and survivorship example

Five customer records from three systems. Step 1 standardises the values and gives each source a trust rank:

CREATE OR REPLACE TEMP VIEW std AS
SELECT *,
       NULLIF(LOWER(TRIM(email)), '')                         AS email_n,
       RIGHT(REGEXP_REPLACE(phone, '[^0-9]', ''), 10)         AS phone_n,
       CASE source WHEN 'CRM' THEN 1 WHEN 'LOY' THEN 2 ELSE 3 END AS trust
FROM customer_sources;

Steps 2 and 3 match on email, then on phone, so records linked through either one end up under the same master ID:

CREATE OR REPLACE TEMP VIEW matched AS
WITH pass1 AS (
  SELECT *, MIN(source_id) OVER (PARTITION BY COALESCE(email_n, source_id)) AS g1
  FROM std),
pass2 AS (
  SELECT *, MIN(g1) OVER (PARTITION BY COALESCE(phone_n, source_id)) AS g2
  FROM pass1)
SELECT *, MIN(g2) OVER (PARTITION BY g1) AS master_id
FROM pass2;

Step 4 builds the golden record with survivorship rules, one per attribute:

SELECT master_id,
       MIN_BY(name, trust)                                                AS name,
       MAX_BY(email_n, CASE WHEN email_n IS NOT NULL THEN updated_at END) AS email,
       MIN_BY(phone_n, CASE WHEN phone_n IS NOT NULL THEN trust END)      AS phone,
       MAX_BY(city, updated_at)                                           AS city,
       COUNT(*)                                                           AS source_records
FROM matched
GROUP BY master_id;

Step 5 sends uncertain pairs to a data steward instead of merging them automatically:

SELECT a.master_id AS record_a, b.master_id AS record_b, a.name AS name_a, b.name AS name_b,
       LEVENSHTEIN(LOWER(a.name), LOWER(b.name)) AS name_distance
FROM matched a JOIN matched b
  ON a.master_id < b.master_id AND a.city = b.city
WHERE LEVENSHTEIN(LOWER(a.name), LOWER(b.name)) <= 2;

Tested on PySpark 3.5: five source records became three golden records, and one pair (CRM-2 and WEB-9, "Ravi Kumar" vs "Ravi Kumarr", distance 1) went to the review queue. The two-pass match is a teaching simplification: real MDM tools and libraries such as Splink or Zingg use blocking, weighted probabilistic scores and graph connected components, so chains of links of any length are merged.

The cross-reference table

MDM never throws the source IDs away. A cross-reference (xref) table maps every source record to its master ID, which is what lets the warehouse, the apps and auditors trace any golden record back to where it came from:

MDM implementation styles

Tools

Common challenges

Types of MDM

"Types of MDM" can mean four different things, so a strong answer names the angle first. The two most often asked are by domain and by usage.

Interview-ready summary

Follow-up interview questions

Interviewers rarely stop at "what is MDM?". These are the follow-ups that usually come next, with answers at the depth a senior or architect role expects.

Q1. What is the difference between MDM and data governance?

Data governance is the framework for all data: policies, standards, roles (owners, stewards), decision rights and quality rules. MDM is one programme that applies that framework to master data, with its own processes and tools.

They depend on each other: governance decides who owns customer data, which attributes are critical and which source is trusted; MDM enforces those decisions in its match and survivorship rules. MDM without governance becomes an expensive duplicate-matching tool that nobody trusts.

Q2. What is a golden record?

The golden record (also "best version of truth") is the single, most accurate version of an entity, built by matching records from several sources and choosing the best value for each attribute. It usually does not equal any one source record: in the example above, Asha's golden record takes the name from the CRM, the email from the web shop and the latest city.

• A stable master ID that never changes, even when sources do
• Best attribute values, each with lineage: which source supplied it and when
• Links to every contributing source record (the cross-reference)
• Status and history: active, merged, retired; versions over time
• Relationships and hierarchies: household, parent company, product family

Q3. What is the difference between MDM and a Customer 360 platform?

MDM answers "who is this customer?": a governed, high-precision identity with core attributes. A Customer 360 answers "everything about this customer": the identity plus orders, payments, service cases, marketing interactions, web behaviour and scores, usually for front-office users.

Best practice: build the Customer 360 on top of MDM. The master ID is the spine that every transaction and interaction is attached to. A 360 without MDM shows three half-profiles of the same person.

Q4. What are matching and survivorship rules?

Matching rules decide whether two records are the same entity. Survivorship rules decide which value wins for each attribute once they are matched.

Scores fall into three bands: auto-merge above an upper threshold, steward review in the grey zone, no match below. Blocking compares only records sharing a cheap key, such as postcode or the first letters of a surname, to avoid comparing every record with every other.

Two golden rules: never let a NULL overwrite a real value, and define survivorship per attribute, not per record.

Q5. What is the difference between registry and consolidation MDM?

Both are read-only towards the sources. If sources must also be corrected, move to coexistence; if all creation must be controlled, move to centralised. Many companies start with registry or consolidation and evolve.

Q6. How do you handle conflicting customer records from different systems?

• Standardise first: many "conflicts" are formatting, such as "+91 98450 12345" vs "98450-12345"
• Agree source trust per attribute with the data owners: KYC system for legal name and date of birth, billing for address, CRM for contact preferences
• Apply survivorship rules, recording which source won each attribute and why
• Route true conflicts to stewards: two trusted sources with different dates of birth should not be auto-resolved
• Keep everything: losing values stay in history and the cross-reference, so decisions can be audited and reversed
• Support unmerge: a wrong merge must be undoable, with facts re-pointed to the right master IDs
• Fix at source: in coexistence style, send corrections back so the conflict does not return tomorrow
• Measure: duplicate rate, false-merge rate, steward backlog and time to resolve

Q7. How do you maintain historical changes in master data?

Master data needs three kinds of history: attribute history (Asha moved from Pune to Mumbai), identity history (records merged or unmerged) and an audit trail (who changed what, and which steward approved it).

Attribute history is usually kept as SCD Type 2 on the golden record. This Delta Lake MERGE closes the old version and inserts the new one in one atomic step:

MERGE INTO golden_customer_hist t
USING (
  -- every incoming record, keyed so it meets its current row
  SELECT u.master_id AS merge_key, u.* FROM golden_updates u
  UNION ALL
  -- changed records again with a NULL key, so they fall through to INSERT
  SELECT NULL AS merge_key, u.*
  FROM golden_updates u
  JOIN golden_customer_hist h
    ON h.master_id = u.master_id AND h.is_current
   AND h.attr_hash <> sha2(concat_ws('|', u.name, u.email, u.city), 256)
) s
ON t.master_id = s.merge_key AND t.is_current
WHEN MATCHED AND t.attr_hash <> sha2(concat_ws('|', s.name, s.email, s.city), 256) THEN
  UPDATE SET valid_to = CAST(s.changed_at AS TIMESTAMP), is_current = false
WHEN NOT MATCHED THEN
  INSERT (master_id, name, email, city, attr_hash, valid_from, valid_to, is_current)
  VALUES (s.master_id, s.name, s.email, s.city,
          sha2(concat_ws('|', s.name, s.email, s.city), 256),
          CAST(s.changed_at AS TIMESTAMP), NULL, true);

Tested on PySpark 3.5 with Delta Lake 3.2: 1 row updated, 2 inserted. Re-running the same batch changed nothing, because the attribute hash detects that nothing changed. Asking for CRM-1's city on 2026-05-01 returned Pune.

• Identity history: keep the cross-reference with valid_from and valid_to too, so a merge or unmerge on a date can be replayed and old reports reproduced
• Audit trail: an append-only change log of every edit, merge and steward decision with user and reason
• Bitemporal history for regulated industries: valid time (when it was true in the real world) and system time (when we recorded it), so you can answer "what did we know on 31 March?"
• Do not rely on Delta time travel for business history: VACUUM and retention settings remove old versions

Q8. What is the difference between MDM and a data warehouse?

A data warehouse integrates large volumes of transactional history from many systems for analysis. It is one-way and read-only towards the sources, and it inherits whatever duplicates arrive. MDM manages a small volume of core entities to resolve and govern identity: it matches, merges and applies survivorship, involves human stewards, and can push corrections back to operational systems in real time.

They are complementary, not alternatives: MDM produces the master ID and golden attributes; the warehouse uses them as the business key and attributes of conformed dimensions such as dim_customer, then adds the facts. The full comparison is in the table at the top of this section.

Q9. How do you integrate MDM with Kafka and a lakehouse?

SOURCES                     KAFKA                        MDM HUB
CRM, core banking,   CDC   crm.customer.cdc      --->   match, merge,
web, loyalty   ----------> web.customer.cdc             survivorship,
(Debezium, GoldenGate)     loyalty.customer.cdc         stewardship
                                                             |
                           mdm.customer.golden   <-----------+  compacted topic,
                           mdm.customer.xref     <-----------+  key = master_id
                              |            |
               +--------------+            +--------------------+
               v                                                v
     operational apps update                LAKEHOUSE (Structured Streaming)
     their local copies                     bronze: raw golden and xref events
                                            silver: golden_customer_hist (SCD2 MERGE)
                                                    xref_hist
                                            gold:   dim_customer, Customer 360

• Inbound: change data capture (Debezium, Oracle GoldenGate, Qlik) streams every source change into one topic per source, so MDM sees changes in seconds instead of nightly files
• Outbound: the hub publishes golden-record events to a log-compacted topic keyed by master_id, so the topic always holds the latest state of every customer and new consumers can rebuild from it. Merges and unmerges go to a separate xref topic
• Ordering: keying by master_id puts all events for one customer in one partition, which keeps them in order
• Contracts: Avro or Protobuf schemas in a schema registry, with compatibility rules, so producers cannot break consumers
• Lakehouse: Spark Structured Streaming reads the topics into bronze, then each micro-batch runs the SCD2 MERGE above in foreachBatch; the checkpoint plus an idempotent MERGE gives exactly-once results in Delta
• Merges in facts: store source IDs in facts and resolve master IDs through the cross-reference when building gold, so a later merge or unmerge re-points history without rewriting fact tables

def upsert_golden(batch_df, batch_id):
    batch_df.createOrReplaceTempView("golden_updates")
    batch_df.sparkSession.sql(HIST_MERGE_SQL)  # the SCD2 MERGE shown above

(spark.readStream.format("kafka")
   .option("kafka.bootstrap.servers", "broker:9092")
   .option("subscribe", "mdm.customer.golden")
   .load()
   .select(F.from_json(F.col("value").cast("string"), golden_schema).alias("e"))
   .select("e.*")
   .writeStream
   .foreachBatch(upsert_golden)
   .option("checkpointLocation", "/chk/golden_customer_hist")
   .start())

The MERGE inside foreachBatch is the one tested above. The Kafka reader follows the standard Spark Structured Streaming API but was not run here, as it needs a broker. If one batch can hold several events for the same master_id, keep only the latest per key before the MERGE.

Q10. How would you implement MDM for customer and counterparty data in a global bank?

Start with why the bank needs it: regulation and risk. Credit exposure must be aggregated per counterparty and per group (BCBS 239, large-exposure rules), KYC and anti-money-laundering rules require knowing the customer and its beneficial owners, and sanctions screening needs one reliable identity. A false merge in a bank is not a marketing nuisance: it can misstate risk or leak one client's data to another.

Roadmap: (1) legal-entity counterparty master for risk, with the highest regulatory value; (2) hierarchies and beneficial ownership; (3) retail customer master region by region; (4) real-time operational sync and Customer 360. Each phase ships golden records that a regulator-facing report actually uses.

25. Observability and Operations

A platform in production needs the same discipline as any software system. Data observability watches the data itself, not just whether jobs succeeded, because a job can succeed while writing wrong data.

SLIs, SLOs and SLAs

Tools

Operating model

• Everything as code: infrastructure with Terraform, Bicep or CloudFormation; jobs with Asset Bundles, dbt or similar; permissions as code where possible
• Environments: dev, test and prod separated by account, workspace or catalog, with production data access restricted
• CI/CD: every change reviewed and tested before deployment, including data tests on representative data
• Runbooks and on-call: a documented response for each critical pipeline, and blameless reviews after incidents
• Change management: data contracts and lineage-based impact checks before breaking changes

26. Resilience, Lifecycle and Cost

Disaster recovery

Recovery is designed from the two numbers agreed earlier. RPO, the recovery point objective, is how much data you can afford to lose. RTO, the recovery time objective, is how long you can be down.

-- A full, independent copy of a Delta table, for backup or another region
CREATE OR REPLACE TABLE dr_sales.gold.fact_order_line
DEEP CLONE prod_sales.gold.fact_order_line;

Delta time travel helps with mistakes such as a bad overwrite, but it is not a backup: the data is in the same storage, and VACUUM removes old versions. Also store infrastructure and jobs as code, so the platform itself can be rebuilt.

Data lifecycle and retention

Cost as an architecture concern

• Tag compute and tables by team and project, so every cost has an owner
• Report spend from billing data, for example the Databricks system.billing.usage table
• Use serverless or autoscaling for uneven workloads, and job compute rather than all-purpose clusters for scheduled work
• Set budgets and alerts, and remove tables that lineage shows nothing reads
• Review the biggest jobs every month: a few usually cause most of the bill

Sharing data

Instead of copying data to partners, Delta Sharing, an open protocol, gives them read access to live tables, with the same governance and audit. Data mesh takes the idea further inside a company: each domain publishes data products, which are documented, owned and quality-checked datasets that others can use. The Data Architecture Patterns card covers data mesh in more detail.

27. Data Architecture for AI

AI raises the stakes for everything above. A model trained on personal data, or a chatbot that retrieves documents a user should not see, turns a governance gap into a visible incident.

Governance rules for AI data

• Permission-aware retrieval: a retrieval-augmented (RAG) assistant must only return documents the asking user may read; filter by the user's permissions at query time, not when building the index
• Redact before embedding: remove or mask personal data before documents become embeddings, since embeddings are hard to delete selectively
• Lineage from data to model: record which data version trained each model, so you can retrain after an erasure request or a quality issue
• Purpose and consent: data collected for orders may not be lawful to use for training without a proper basis
• Regulation: the EU AI Act sets duties by risk level, phased in from 2025, so classify AI use cases as well as data

28. Choosing Technology

How to evaluate a platform

Score options against agreed criteria rather than preferences. Weight the criteria first, with stakeholders, then score each option, and record the result in an ADR.

Build, buy or assemble

29. Implementing an Enterprise Data Lake

A data lake is cheap object storage plus the rules that stop it becoming a data swamp: files nobody can find, trust or delete. The storage is the easy part; the conventions are the architecture.

Zones

Storage layout

Separate by environment and zone at the bucket or container level, because that is where cloud permissions, encryption keys and lifecycle rules apply. Use a fixed path convention below that.

s3://acme-lake-prod-raw/       one bucket per environment and zone
  crm/customers/ingest_date=2026-11-07/part-0000.parquet
  orders_db/orders/ingest_date=2026-11-07/...
s3://acme-lake-prod-curated/
  sales/orders/                  table format folder (Delta or Iceberg)
s3://acme-lake-prod-gold/
  sales/fact_order_line/

pattern: <env>-<zone> / <source or domain> / <dataset> / <partition>

Implementation steps

1. Set up the landing zone. Cloud accounts or subscriptions per environment, network isolation, encryption with managed keys, central logging. Do this before any data arrives.

2. Create zones with their own permissions. Ingestion roles can write raw; only pipelines write curated and gold; analysts read gold. Deny public access everywhere.

3. Register everything in a catalog. Glue Data Catalog, Unity Catalog, Purview or Knowledge Catalog. A file that is not in the catalog does not exist for consumers.

4. Standardise ingestion. One framework, with a metadata contract per source: owner, classification, schema, frequency, retention. Reject sources without an owner.

5. Choose formats. Keep raw in the delivered format or Parquet; use an open table format from curated onwards, so you get updates, deletes and schema evolution.

6. Apply lifecycle rules. Landing files expire in days, sandbox in weeks, raw moves to cheaper storage classes after months, archive follows legal retention.

7. Add quality and lineage. Checks between zones; lineage captured automatically where possible.

8. Publish and measure. Track the share of datasets with an owner, a description and a classification. Falling numbers are the first sign of a swamp.

30. Implementing an Enterprise Data Warehouse

A warehouse gives the business one trusted, modelled version of its data, optimised for SQL and BI. Three classic design approaches still shape how it is built:

 Sources ─► Staging ─► Integration layer ─► Data marts ─► Semantic layer ─► BI
            (as is)    (3NF or Data Vault)   (star schemas)  (metrics)

The bus matrix

Kimball's bus matrix is the planning tool: business processes as rows, shared dimensions as columns. Each tick is a fact table that must use that conformed dimension, and the matrix becomes the delivery roadmap.

Implementation steps

1. Start from business questions. Interview stakeholders, list the key metrics and decisions, and build the bus matrix.

2. Choose the core approach and platform. Kimball for speed, Data Vault when sources change often and audit matters; then a platform from the evaluation criteria in this card.

3. Build conformed dimensions first. Date, customer and product, with surrogate keys and SCD rules agreed per attribute.

4. Deliver one process at a time. Typically the highest-value row of the bus matrix, end to end, including tests and a dashboard.

5. Load incrementally. CDC or watermarks into staging, MERGE into the core, idempotent at every step.

6. Manage workloads. Separate compute for loading, BI and ad hoc queries; materialised views for hot aggregates.

7. Secure and govern. Role-based access, masking of personal data, documented metrics in the semantic layer.

The Data Warehouse card covers star and snowflake schemas, SCDs and normal forms; the Data Modelling card builds a full mart with DDL and PySpark.

31. Implementing a Lakehouse on Delta Lake

A lakehouse combines the lake's cheap open storage with the warehouse's reliability, using an open table format. Here is an enterprise implementation on Delta Lake with Unity Catalog, followed by the Iceberg equivalent.

 Ingestion           Storage and catalog            Consumption
 ─────────           ───────────────────            ───────────
 CDC, Auto Loader ─► prod catalog               ─►  SQL warehouses, BI
 Kafka streams       ├─ bronze  raw, append-only    ML, feature store
 Batch connectors    ├─ silver  cleaned            Delta Sharing
                     └─ gold    marts, products    Semantic layer, AI
                     Unity Catalog: access, lineage, audit, tags

Step 1: lay out catalogs and schemas

Decide early how catalogs map to the organisation, because permissions and isolation follow this structure. Two common designs: one catalog per environment with schemas per layer, or one catalog per domain per environment.

CREATE CATALOG IF NOT EXISTS prod_sales
  MANAGED LOCATION 's3://acme-lakehouse-prod/sales';

CREATE SCHEMA IF NOT EXISTS prod_sales.bronze;
CREATE SCHEMA IF NOT EXISTS prod_sales.silver;
CREATE SCHEMA IF NOT EXISTS prod_sales.gold;

GRANT USE CATALOG ON CATALOG prod_sales TO `sales-analysts`;
GRANT USE SCHEMA, SELECT ON SCHEMA prod_sales.gold TO `sales-analysts`;

Step 2: set table standards

Write the defaults once and apply them to every table through templates or code review.

CREATE TABLE prod_sales.silver.orders (
  order_id     STRING NOT NULL COMMENT 'Business key from the order system',
  customer_id  STRING,
  order_ts     TIMESTAMP COMMENT 'UTC',
  amount       DECIMAL(18,2),
  _ingested_at TIMESTAMP COMMENT 'Load time, for audit'
)
CLUSTER BY (order_ts)
COMMENT 'One row per order. Owner: sales-domain'
TBLPROPERTIES (
  'delta.enableChangeDataFeed'      = 'true',
  'delta.deletedFileRetentionDuration' = 'interval 7 days',
  'delta.logRetentionDuration'      = 'interval 30 days'
);

Step 3: ingestion and processing

• Bronze: Auto Loader for files, Lakeflow Connect or CDC tools for databases, Structured Streaming for Kafka; append-only
• Silver: deduplicate, type and conform; MERGE for idempotency
• Gold: dimensional models or data products, built with SQL, dbt or Lakeflow Declarative Pipelines
• Orchestrate with Lakeflow Jobs or Airflow; deploy with Asset Bundles through CI/CD

Step 4: operate it

• Maintenance: enable predictive optimization, or schedule OPTIMIZE, VACUUM and ANALYZE
• Quality: expectations, dbt tests or monitoring on gold tables
• Cost: tagged compute per team, serverless where usage is uneven
• Security: groups, tag-based masking, audit reviews

The same lakehouse on Iceberg

32. Implementing a Data Mesh

Data mesh is mostly an organisational change, not a product you install. It answers a scaling problem: one central data team becomes a bottleneck when hundreds of sources and consumers depend on it.

Is it right for you?

A data product specification

Every product is described in a standard, machine-readable file, so the platform can validate it, publish it to the catalog and enforce its policies.

name: sales.orders_daily
domain: sales
owner: sales-data-team@acme.example
description: Daily orders by store and product, net of returns
output_ports:
  - type: table
    location: prod_sales.gold.orders_daily
  - type: share
    name: orders_daily_partners
schema_contract: contracts/orders_daily.yaml  # columns, types, compatibility
classification: internal
slo:
  freshness: "07:00 daily"
  completeness: ">= 99.9% of stores"
version: 2.1.0

Implementation steps

1. Pick two or three domains. Choose domains with strong teams and real consumers, not the hardest problem first.

2. Build the self-serve platform. A product template that creates storage, a catalog or schema, CI/CD, quality checks and access groups from the specification above.

3. Define global standards. Naming, classification tags, security policies, interoperability formats and SLO reporting, enforced by the platform rather than by review meetings.

4. Publish products to a marketplace. A catalog where consumers find products, read contracts and request access.

5. Share between domains without copying. Use the platform's sharing features, so domains consume each other's products in place.

6. Measure and expand. Track product usage, SLO compliance and time-to-publish; add domains as the platform matures.

Common failures: renaming central teams as "domains" without giving them ownership, building no platform so each domain reinvents pipelines, and skipping governance so products are incompatible. Many companies end up with a hybrid: a central platform and core data, with domains owning their products.

33. Modernising Legacy Systems

Most enterprises do not start from scratch. They have a warehouse appliance, a Hadoop cluster, decades of ETL jobs and perhaps a mainframe. Modernisation moves these to a cloud platform without breaking the business while it happens, which is why it is a programme of waves, not a single project.

Why modernise

• Hardware or licence end of life, and rising support costs
• Fixed capacity that cannot scale for month-end or AI workloads
• Skills scarcity for old technologies
• No support for streaming, unstructured data or machine learning
• Governance gaps: no lineage, coarse access control

Typical legacy estates and where they go

Migration tools change quickly: Databricks made the BladeBridge conversion technology free as Lakebridge in 2025, and Snowflake now centres on SnowConvert AI. Always run a proof of concept on your own code, because conversion rates vary widely by source.

Modernisation patterns

The six Rs of migration

A modernisation programme, phase by phase

1. Discover. Inventory every table, job, report and user. Usage logs and lineage show what is really used; it is common to find a large share of reports and tables nobody reads, which can be retired instead of migrated.

2. Assess and plan. Score each workload by business value and migration complexity, group them into waves by domain, and build the business case on total cost, including running both systems in parallel.

3. Build the foundation. Landing zone, network, identity, catalog, security policies, CI/CD and the ingestion framework, before the first wave.

4. Migrate a pilot wave. One domain end to end: bulk-load history, keep it current with CDC, convert and test the code, and rebuild its reports.

5. Validate by reconciliation. Prove the new platform gives the same answers as the old one, at several levels (below).

6. Run in parallel, then cut over. Both systems run side by side for an agreed period; consumers switch domain by domain once results match.

7. Decommission. Switch off legacy jobs and hardware wave by wave. Savings only appear when the old system is actually turned off.

Tested: converting legacy Parquet tables in place

Hadoop estates often hold Hive tables already stored as Parquet. Delta can adopt them without rewriting any data, which makes migrating very large tables fast and cheap:

CONVERT TO DELTA parquet.`s3://legacy/warehouse/sales`
  PARTITIONED BY (region_id INT);

Iceberg offers similar procedures, such as migrate and snapshot, which add Iceberg metadata over existing files. Test them on a copy first: an in-place conversion changes how the folder must be written from then on.

Tested: reconciling source and target

Reconciliation proves the migration is correct. We copied a table, then deliberately deleted one row and changed one amount in the target. A layered check caught both:

from pyspark.sql import functions as F

def profile(df, key, cols):
    row_hash = F.xxhash64(*[F.col(c) for c in cols])
    return df.agg(F.count("*").alias("rows"),
                  F.countDistinct(key).alias("keys"),
                  F.sum("amount").alias("sum_amount"),
                  F.sum(row_hash).alias("hash_sum")).first()

s = profile(source, "order_id", ["order_id", "region_id", "amount"])
t = profile(target, "order_id", ["order_id", "region_id", "amount"])
for k in ("rows", "keys", "sum_amount", "hash_sum"):
    print(k, "match" if s[k] == t[k] else f"DIFF {s[k]} vs {t[k]}")

missing = source.join(target, "order_id", "left_anti")
changed = source.exceptAll(target).join(target.select("order_id"), "order_id")

Use the cheap aggregate checks on every table and every load; run the row-level comparison when aggregates disagree, or on a sample. Finish with report-level checks: the top business reports must show the same numbers on both platforms.

What usually goes wrong

34. Putting It Together: The Retailer's Plan

Back to the auditor's question. Here is how the architect turns this card into a plan, in an order where each phase makes the next one possible.

After phase 5, the auditor's question has a one-query answer: the grants and policies show who can see the email column unmasked, and lineage shows every table it reaches, on every platform. That is the measure of a good data architecture: hard questions get quick, provable answers.

35. Common Mistakes

36. Common Interview Questions