Example: Add a Dynamics 365 Sales column to a report

Important

Some or all of this functionality is available as part of a preview release. The content and the functionality are subject to change.

This article shows how to add a new column from Dynamics 365 Sales through Bronze, Silver, and Gold to the Fundraising_Intelligence report.

Scenario: Adding msnfp_deceased (Yes/No) field from Dynamics 365 Sales contact table to DimConstituent in the report.

Data flow:

Dynamics 365 Sales contact.msnfp_deceased -> Bronze -> Silver Contact.IsDeceased -> Gold DimConstituent.IsDeceased -> Fundraising_Intelligence report

Table of contents

Before you start: Choose your scenario

Scenario Description What to do
Before go-live New deployment, no production data yet Follow Steps 1-8. No backfill needed.
In production Existing deployment with live data Follow Steps 1-4, then Backfill (reset watermark), then Steps 5-8.

Key difference:

  • Before go-live: The pipeline runs in incremental mode, but on a first run, there's no existing watermark yet. So, it processes all records and populates your new column automatically.

  • In production: The pipeline runs in incremental mode, but an existing watermark limits processing to changed records. Reset the watermark before running the pipeline so it reprocesses existing records with the new column.

Prerequisites

  • Column msnfp_deceased exists in the bronze lakehouse (contact table)
  • Data type: Boolean (Yes/No field, values 0 or 1)

Step 1: Silver schema (Fundraising_SL_CreateSchema)

Find the get_contact_schema() method and add a new StructField:

StructField("IsDeceased", BooleanType(), nullable=True),

Production: Also add the column to the existing table in the silver lakehouse:

spark.sql(f"ALTER TABLE {silver_lakehouse_name}.Contact ADD COLUMN IsDeceased BOOLEAN")

Step 2: Gold schema (Fundraising_GD_CreateSchema)

Find the get_dimconstituent_schema() method and add a new StructField:

StructField("IsDeceased", BooleanType(), nullable=True),

Production: Also, add the column to the existing table in the gold lakehouse:

spark.sql(f"ALTER TABLE {gold_lakehouse_name}.DimConstituent ADD COLUMN IsDeceased BOOLEAN")

Step 3: Bronze→Silver transformation (Fundraising_D365_Transform)

Find the cell "Transform: Contact" and update:

a) Add the column to the transform_contact() function:

col("msnfp_deceased").alias("IsDeceased")

b) Add the source column to the data_sync.sync_table() call:

source_columns=[
    "Id", "firstname", "lastname", "emailaddress1", "birthdate",
    "gendercode", "address1_addressid", "createdon", "modifiedon",
    "msnfp_deceased"  # <-- add this
],

Step 4: Silver→Gold transformation (Fundraising_SL_GD_Enrichment)

Find cell "Build: DimConstituent" and update three places:

a) Add to EnrichDimConstituent() final select:

col("contact.IsDeceased").alias("IsDeceased"),

b) Update generate_merge_sql() - add to both UPDATE and INSERT:

-- In UPDATE SET:
target.IsDeceased = source.IsDeceased,

-- In INSERT columns:
..., IsDeceased

-- In INSERT VALUES:
..., source.IsDeceased

c) Add to contactTable CdfTable columns list:

columns=[
    "ContactId", "FirstName", "LastName", "Email", "EngagementStageId",
    "GenderId", "AddressId", "RegistrationDate", "CreatedDate", "ModifiedDate",
    "SourceId", "SourceSystemId",
    "IsDeceased"  # <-- add this
],

Note

dm_Constituent is a data mart built from DimConstituent via SQL. For basic filtering, you don't need to make any changes. If you need the column in aggregations, update the SQL in cell "Build: dm_Constituent".

Step 5: Run transformations

After completing all code changes (Steps 1-4), run the Fundraising_Orchestration pipeline.

This pipeline executes all notebooks in the correct order:

  1. Schema notebooks (Silver, Gold)
  2. Transformation notebooks (Bronze→Silver, Silver→Gold)

Step 6: Verify gold data

  1. Open Gold Lakehouse (Fundraising_GD) in Fabric.
  2. Find and open the DimConstituent table.
  3. Confirm the IsDeceased column exists and shows True/False values.

Step 7: Sync semantic model schema

The Semantic Model maintains its own metadata about tables and columns. It doesn't automatically detect new columns added to the underlying Gold lakehouse.

  1. Open Semantic Model (Fundraising_Intelligence).
  2. Go to Home > Refresh > Sync schema.
  3. Verify IsDeceased appears under the DimConstituent table.

Without this step, reports can't use the new column because the Semantic Model doesn't know it exists.

Step 8: Report

Use DimConstituent[IsDeceased] in:

  • Filters (for example, exclude deceased constituents)
  • Slicers
  • Visualizations
  • DAX measures

Data type mappings

Follow the same data type mappings used in existing NDS schema notebooks. (Fundraising_SL_CreateSchema, Fundraising_GD_CreateSchema). Look at similar columns for reference.

Backfill existing data (production only)

Note

When to do this step: After completing Steps 1-4 (code changes), before Step 5 (running pipeline).

Why? The pipeline always uses incremental sync:

  • Bronze→Silver: Watermark on SinkModifiedOn stored in Silver.WatermarkState table
  • Silver→Gold: Delta Change Data Feed (tracks Delta table versions)

On a first run, there's no stored watermark yet, so all records are processed. In an existing deployment, the stored watermark means only newly changed records are processed.

If the watermark isn't reset, only newly modified records in an existing deployment get the new column populated.

Important

Without a backfill step, only newly changed records are reprocessed. Existing rows keep their previous values until the source record changes again.

Reset the watermark:

In Fabric > Silver Lakehouse, open a new notebook and run the following code:

DELETE FROM WatermarkState WHERE lower(source_table_name) = lower('contact')

This statement removes the watermark entry, so the next pipeline run treats all records as new and reprocesses them with your new column.

Then continue with Step 5 (run Fundraising_Orchestration pipeline).

Special column types

Lookups (Foreign keys)

For columns that reference other tables (for example, msnfp_receiptoncontactid -> Contact), add them to fk_mappings in sync_table():

data_sync.sync_table(
    ...
    fk_mappings={
        "msnfp_receiptoncontactid": "Contact",
        "msnfp_transaction_receiptonaccountid": "Account"
    }
)

The framework automatically resolves bronze GUID to silver RecordId.

OptionSets (Choice fields)

For choice or picklist columns, complete three steps. For full details, see Example: Add a Dynamics 365 Sales optionset to a report.

1. Sync optionset to dimension table - add to optionsets_to_sync in Fundraising_D365_Transform:

optionsets_to_sync = [
    # (entity_name, optionset_name, TargetTable, TargetPrimaryKey)
    ("contact", "gendercode", "Gender", "GenderId"),
]

2. Add FK mapping in sync_table() - tells framework to resolve the optionset:

data_sync.sync_table(
    ...
    fk_mappings={
        "gendercode": "Gender",  # optionset column -> dimension table
    }
)

3. Use resolved ID in transform function - framework adds _SilverRecordId suffix:

def transform_contact(df: DataFrame) -> DataFrame:
    return df.select(
        col("gendercode_SilverRecordId").alias("GenderId"),  # resolved to dimension FK
        ...
    )

Note

For new dimension tables (not existing ones like Gender), you must also create the schema in Fundraising_SL_CreateSchema first.

See also

  • Last updated on