DP-700: how to plan and implement schema mapping for ingestion in Fabric

In this lesson I will teach the skill of planning and implementing schema mapping during data ingestion in Microsoft Fabric. This skill is important for the DP-700 exam because it ensures that incoming data is consistent with analytical models; in practice it prevents compatibility errors, information loss and enables resilient pipelines. A good schema mapping strategy can reduce ingestion rejections by 60–90% in an environment with frequent schema evolution.

What you need to know

Schema mapping is the process of mapping columns, types and data structures from an ingestion source to the target schema used in Fabric tables (for example in Tables within Lakehouse/Delta or in a Synapse SQL pool). The objective is, among other things, to harmonize names, convert types, handle missing/excess columns and ensure that ingestion supports schema evolutions without disrupting downstream consumers.

Simple example: a CSV source has columns (id, name, created_at) where created_at is ISO text; the target requires (Id INT, Nome STRING, CreatedAt DATETIME). The schema mapping defines that id -> Id with conversion to INT, name -> Nome (rename), created_at -> CreatedAt with parsing to DATETIME and rules when the field is empty. In real scenarios you see tables with 20–200 columns; applying explicit mapping for the 10–20 critical columns is usually sufficient to protect essential reports while gradually evolving the rest.

How it works in practice

In Fabric you can apply schema mapping at several points in the pipeline: in the read process (Dataflows, Notebooks, Spark), during writing to Delta/OneLake (using DataFrame schemas or DDL) or in Data Factory/Synapse pipelines. Approaches vary depending on whether you use Spark/Apache/Delta or low-code components like Dataflows. In high-throughput ingestions (e.g. 10k–100k records/minute) you prefer Spark transformations because they are more efficient; for ad-hoc loads and light transformation, Dataflows give visibility and control without code.

Practical key points:

1. Define the target schema explicitly: avoids automatic inference. In Spark/PySpark we define a StructType/DDL; in Dataflows we create the columns with the intended type. In production environments, keeping a versioned schema file (JSON/DDL) facilitates auditing and deployment.
2. Map names and types: rename columns and transform types (e.g.: string -> timestamp). Also document optional and required columns and quantify: e.g. 15 required columns, 25 optional. That inventory helps prioritize validations.
3. Handle unknown columns: choose between ignoring, storing in an attached JSON column, or failing the ingestion. A common practice is to store unmapped columns in an auxiliary JSON field for auditing — reduces failures without losing data.
4. Deal with schema changes: apply schema versioning (e.g. v1, v2), use permissive columns (nullable) and idempotent pipelines. Define SLAs: for example, require read compatibility for 2 weeks before deprecating old fields.

# Exemplo PySpark: definir schema e aplicar transformações antes de escrever para Delta
from pyspark.sql.types import StructType, StructField, IntegerType, StringType, TimestampType
from pyspark.sql.functions import to_timestamp, col, to_json, struct

schema = StructType([
    StructField("id", IntegerType(), nullable=False),
    StructField("Nome", StringType(), nullable=True),
    StructField("CreatedAt", TimestampType(), nullable=True)
])

# leitura com inferência (exemplo) e transformação para o schema alvo
df = spark.read.option("header", "true").csv("/mnt/bronze/myfile.csv")

df_transformed = (
    df.withColumn("id", col("id").cast("int"))
      .withColumnRenamed("name", "Nome")
      .withColumn("CreatedAt", to_timestamp(col("created_at"), "yyyy-MM-dd'T'HH:mm:ss"))
)

# validar: garantir que colunas obrigatórias não são nulas e registar linhas inválidas
invalid = df_transformed.filter(col("id").isNull())
valid = df_transformed.filter(col("id").isNotNull())

# opcional: guardar colunas adicionais num campo JSON para auditoria
known_cols = ["id","Nome","CreatedAt"]
extra = df.drop(*known_cols)
extra_json = df.withColumn("_extra", to_json(struct(*extra.columns)))

# escrever para Delta/OneLake
valid.write.format("delta").mode("append").save("/mnt/lakehouse/mytable")

If you use Dataflows in Fabric you can configure mappings visually: import the source, add transformation steps (Rename, Derived column, Convert data type) and define rules for handling null or invalid values. For critical flows implement automated tests that check samples of 1% to 5% of files daily to detect schema regressions.

Common mistakes

1) Always trusting automatic type inference: this causes differences between runs (e.g. a numeric field with mixed values may be inferred as string). Whenever possible define the schema explicitly and include checks that alert when inference changes.

2) Ignoring additional columns in the source: new columns may appear that contain important information or break downstream operations. Decide a strategy (log, store as JSON, or reject) and apply it consistently. In continuous integration environments, 30–40% of initial pipeline failures stem from unexpected columns.

3) Failing to normalize date/locale formats: dates in different formats lead to invalid parsing. Centralize parsing rules (e.g. UTC vs local time zone) and test with representative samples — test at least 1000 rows per format to reduce false positives.

How to practice

Practice by creating small pipelines in Fabric: use a Lakehouse/Delta or a workspace with Spark, create sources with schema variations (CSV/JSON) and implement mappings in PySpark and in Dataflows. Create evolution scenarios: for example, add 5 new columns in one of the files and observe the pipeline behavior. Measure how many runs fail before and after applying the auxiliary JSON storage strategy.

For exam preparation, use the official Microsoft Practice Assessment and the Microsoft study guide (both free). Those resources help align study with the assessed areas without resorting to prohibited material. Complement with practical labs: implement 3 pipelines with different strategies (fail, ignore, append JSON) and compare mean time to recovery (MTTR) — in typical tests, pipelines with JSON append recover 2–3x faster.

In summary

• Schema mapping ensures compatibility between sources and target schemas, avoiding failures and data loss.
• Always define the target schema explicitly and apply transformations (rename, cast, parsing) before writing to the target.
• Define a policy for unknown columns and for schema changes (versioning, nullable columns, auxiliary storage in JSON).
• Test with varied samples and automate validations (null checks, type checks, counts) to keep pipelines resilient; monitor error rates and MTTR to measure improvements.