Healthcare Eligibility ETL Quality Gate

Build a T-SQL quality gate that validates and loads eligibility data.

Introduction

30 Second Summary

One impossible date in an eligibility file can block every valid record behind it. A strong interview answer shows how you protect good data without losing evidence of what went wrong.

In this project, you will build a healthcare eligibility quality gate with T-SQL inside a SQL database in Microsoft Fabric. Your ETL flow will standardize valid records while preserving each rejected row with a reason.

What You'll Build

You'll run a nine-row eligibility batch in your browser and see every source row resolve into an accepted record or an explained rejection before a zero-difference report proves the batch reconciles.

By the end of this project, you'll have:

  • An inspectable raw landing zone that preserves all 9 source rows so you can identify spaces, lowercase statuses, an invalid date, a missing employer code, and a duplicate record.
  • A resilient quality gate that loads 4 standardized records while routing 5 malformed or duplicate records to named rejection reasons.
  • A reconciliation report showing a 0 difference plus an employer-status summary you can explain during an interview.
  • Secret Mission: Turn the quality checks into a reusable stored procedure with an optional employer filter.

Are there any prerequisites?

You need basic SELECT knowledge plus a Microsoft account with access to an existing Fabric capacity. An eligible Fabric trial also works, but availability depends on your tenant.

Before We Start

You are about to build a healthcare eligibility ETL quality gate that protects warehouse tables from malformed source data. This skill matters in interviews because it proves you can preserve source evidence while isolating bad records from trustworthy reporting data.

Set Up Fabric and the ETL Schema

Your healthcare eligibility ETL quality gate needs a real database where raw evidence stays separate from reporting data. Microsoft Fabric gives you that environment through a browser.

On your Mac, the Fabric web editor provides direct T-SQL practice without requiring Windows-only development tools. This step creates the database boundaries that protect every later load.

In this step, get ready to:
  • Access a dedicated Trial workspace or an existing-capacity workspace.
  • Create the HealthcareETL database.
  • Create two schemas with six empty tables.
Access Microsoft Fabric

Fabric access determines where your database can run. You need a workspace backed by an eligible trial or an existing Fabric capacity.

Why use Fabric on a Mac?

The browser-based editor keeps your setup on macOS. It also gives you direct practice with the T-SQL used throughout this project.

SQL Server Integration Services development uses Windows-focused Visual Studio tooling. Fabric lets you practise the same source, transformation, destination, validation, and error-routing model in your browser.

  • Sign in to Microsoft Fabric with your Microsoft account in your browser.
  • Open Account manager to determine which workspace option is available.

✔️ I have an existing Fabric capacity

Your account can use a workspace that already has Fabric capacity. The workspace role must let you create a SQL database.

  • Select a workspace assigned to an existing Fabric capacity.
  • Confirm that your workspace role is Admin or Member.

Your selected workspace is ready to hold the project database.

ⓧ I need to start a trial

Starting a trial can feel like a billing decision. An eligible Microsoft Fabric trial gives you free access for 60 days.

  • Select Start trial in Account manager.
  • Select Start trial in the trial prompt.
  • Select Activate when Fabric asks you to activate the trial.
  • Create a dedicated workspace for this project.
  • Assign the workspace to the Trial workspace type.
  • Select the dedicated workspace.

The dedicated workspace now keeps this interview project separate from your other Fabric items.

ⓧ Start trial is unavailable

Trial availability depends on tenant settings, license state, regional availability, and available trial capacity. Do not purchase a Fabric capacity unless you intend to accept its cost.

  • Ask your tenant administrator for access to a workspace on an existing Fabric capacity.
  • Confirm that the workspace grants you the Admin or Member role.
  • Select the workspace after access is granted.

Still blocked from Fabric?

Your Microsoft account may belong to a tenant that disables trials or item creation. An administrator can confirm whether an existing Fabric capacity is available.

Help me diagnose my Fabric access options.

That is the access hurdle cleared. Your selected workspace can now host the database for the quality gate.

Create the HealthcareETL database

A SQL database in Microsoft Fabric stores the staging and warehouse objects used by this project. The database opens directly in the web editor after creation.

  • Select Databases in your Fabric workspace.
  • Select SQL database under New.
  • Enter HealthcareETL as the database name.
  • Select Create.

Fabric opens HealthcareETL in the T-SQL web editor. You can see its database objects in Explorer.

Database creation unavailable?

Confirm that the selected workspace is assigned to Fabric capacity. Check that your workspace role is Admin or Member.

Help me fix Fabric database creation access.

Create the ETL schemas and tables

A schema groups related database objects under a shared name. The stg schema holds incoming and validated data. The dw schema holds reporting tables.

Each CREATE SCHEMA statement must run as a separate batch. You will use one query tab for each schema before creating the tables.

  • Select New SQL query in the HealthcareETL editor.
  • Create the staging schema by adding this statement to the first query tab:
CREATE SCHEMA stg;

What does this statement do?

The statement creates the stg namespace. Its tables preserve source values and hold validation outcomes before warehouse loading.

  • Select Run.
  • Refresh Explorer.

You will see the stg schema listed under HealthcareETL.

Staging schema missing?

Confirm that the query ran against HealthcareETL. Refresh Explorer after the query finishes.

Help me troubleshoot the missing staging schema.

  • Select New SQL query to create a separate batch.
  • Create the warehouse schema by adding this statement to the new query tab:
CREATE SCHEMA dw;

What does this statement do?

The statement creates the dw namespace. Its dimension and fact tables contain standardized records that passed the quality gate.

  • Select Run.
  • Refresh Explorer.

You will now see both stg and dw under the database.

Warehouse schema missing?

Check that the warehouse statement ran in its own query tab. A separate batch keeps the schema creation statement isolated.

Help me troubleshoot the missing warehouse schema.

The two boundaries are ready. Next, each table definition adds one visible piece of the ETL structure.

  • Select New SQL query for the first section of 02_create_tables.sql.
  • Create the raw landing table by adding this definition:
CREATE TABLE stg.EligibilityRaw
(
    source_row_id INT NOT NULL PRIMARY KEY,
    member_external_id VARCHAR(20) NULL,
    employer_code VARCHAR(10) NULL,
    first_name VARCHAR(50) NULL,
    last_name VARCHAR(50) NULL,
    coverage_start_text VARCHAR(20) NULL,
    coverage_end_text VARCHAR(20) NULL,
    status_text VARCHAR(20) NULL,
    plan_code VARCHAR(10) NULL
);

What does this table protect?

  • The text columns preserve incoming values before any conversion or standardization occurs.
  • The primary key makes every source row traceable through source_row_id.
  • Select Run.
  • Refresh Explorer.

You will see stg.EligibilityRaw under the staging schema.

Raw table missing?

Confirm that stg exists before running the table definition. Check that the query is connected to HealthcareETL.

Help me troubleshoot EligibilityRaw creation.

  • Select New SQL query.
  • Create the standardized staging table by adding this definition:
CREATE TABLE stg.EligibilityValidated
(
    source_row_id INT NOT NULL PRIMARY KEY,
    member_external_id VARCHAR(20) NULL,
    employer_code VARCHAR(10) NULL,
    first_name VARCHAR(50) NULL,
    last_name VARCHAR(50) NULL,
    coverage_start DATE NULL,
    coverage_end DATE NULL,
    status VARCHAR(20) NULL,
    plan_code VARCHAR(10) NULL,
    reject_reason VARCHAR(100) NULL
);

What does validation add?

  • The date columns store values only after successful conversion to the DATE type.
  • The reject_reason column records why a row cannot continue to the warehouse.
  • Select Run.
  • Refresh Explorer.

You will see stg.EligibilityValidated beside the raw table.

Validated table missing?

Check the commas between each column definition. Confirm that the final column is followed by the closing parenthesis.

Help me troubleshoot EligibilityValidated creation.

  • Select New SQL query.
  • Create the rejected-row table by adding this definition:
CREATE TABLE stg.EligibilityReject
(
    source_row_id INT NOT NULL PRIMARY KEY,
    member_external_id VARCHAR(20) NULL,
    employer_code VARCHAR(10) NULL,
    first_name VARCHAR(50) NULL,
    last_name VARCHAR(50) NULL,
    coverage_start_text VARCHAR(20) NULL,
    coverage_end_text VARCHAR(20) NULL,
    status_text VARCHAR(20) NULL,
    plan_code VARCHAR(10) NULL,
    reject_reason VARCHAR(100) NOT NULL
);

Why preserve rejected values?

  • The table keeps the original text values so a failed row remains auditable.
  • The required reject_reason prevents an invalid row from being stored without an explanation.
  • Select Run.
  • Refresh Explorer.

The stg schema now contains all three staging tables.

Reject table missing?

Confirm that the table name is stg.EligibilityReject. Check that reject_reason appears before the closing parenthesis.

Help me troubleshoot EligibilityReject creation.

  • Select New SQL query.
  • Create the employer dimension by adding this definition:
CREATE TABLE dw.DimEmployer
(
    employer_key INT IDENTITY(1, 1) NOT NULL,
    employer_code VARCHAR(10) NOT NULL,
    CONSTRAINT PK_DimEmployer PRIMARY KEY (employer_key),
    CONSTRAINT UQ_DimEmployer_Code UNIQUE (employer_code)
);

How does the employer dimension stay consistent?

  • The identity column generates a warehouse key for each employer.
  • The unique constraint prevents the same employer_code from appearing twice.
  • Select Run.
  • Refresh Explorer.

You will see dw.DimEmployer under the warehouse schema.

Employer dimension missing?

Confirm that the dw schema exists. Check that both named constraints appear inside the table definition.

Help me troubleshoot DimEmployer creation.

  • Select New SQL query.
  • Create the member dimension by adding this definition:
CREATE TABLE dw.DimMember
(
    member_key INT IDENTITY(1, 1) NOT NULL,
    member_external_id VARCHAR(20) NOT NULL,
    employer_key INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    CONSTRAINT PK_DimMember PRIMARY KEY (member_key),
    CONSTRAINT UQ_DimMember_ExternalId UNIQUE (member_external_id),
    CONSTRAINT FK_DimMember_Employer
        FOREIGN KEY (employer_key)
        REFERENCES dw.DimEmployer (employer_key)
);

How are members connected to employers?

  • The unique constraint gives each external member identifier one dimension record.
  • The foreign key requires every employer_key to reference an existing employer.
  • Select Run.
  • Refresh Explorer.

You will see dw.DimMember beside the employer dimension.

Member dimension missing?

Create dw.DimEmployer first because the member foreign key references it. Confirm that both tables use employer_key.

Help me troubleshoot DimMember creation.

  • Select New SQL query.
  • Create the eligibility fact table by adding this definition:
CREATE TABLE dw.FactEligibility
(
    eligibility_key INT IDENTITY(1, 1) NOT NULL,
    source_row_id INT NOT NULL,
    member_key INT NOT NULL,
    plan_code VARCHAR(10) NOT NULL,
    coverage_start DATE NOT NULL,
    coverage_end DATE NULL,
    status VARCHAR(20) NOT NULL,
    CONSTRAINT PK_FactEligibility PRIMARY KEY (eligibility_key),
    CONSTRAINT UQ_FactEligibility_SourceRow UNIQUE (source_row_id),
    CONSTRAINT CK_FactEligibility_Status
        CHECK (status IN ('ACTIVE', 'TERMINATED')),
    CONSTRAINT FK_FactEligibility_Member
        FOREIGN KEY (member_key)
        REFERENCES dw.DimMember (member_key)
);

How does the fact table enforce quality?

  • The source-row constraint prevents one accepted source record from loading twice.
  • The CK_FactEligibility_Status constraint accepts only ACTIVE or TERMINATED.
  • The member foreign key connects each eligibility record to a valid member dimension row.
  • Select Run.
  • Refresh Explorer.

The dw schema now contains both dimensions and the eligibility fact table.

Fact table missing?

Create dw.DimMember before the fact table. Check that the status constraint includes both allowed uppercase values.

Help me troubleshoot FactEligibility creation.

✔️ Awesome, I've got everything!

All three scripts are complete. Keep the schema statements in separate query tabs because each one runs as its own batch.

ⓧ I'd like to double check the full code

Compare your first schema query with 00_create_stg_schema.sql.

CREATE SCHEMA stg;

Staging schema reference

This full-file reference contains only the separate batch that creates stg.

Compare your second schema query with 01_create_dw_schema.sql.

CREATE SCHEMA dw;

Warehouse schema reference

This full-file reference contains only the separate batch that creates dw.

Compare the table definitions with the complete 02_create_tables.sql reference.

CREATE TABLE stg.EligibilityRaw
(
    source_row_id INT NOT NULL PRIMARY KEY,
    member_external_id VARCHAR(20) NULL,
    employer_code VARCHAR(10) NULL,
    first_name VARCHAR(50) NULL,
    last_name VARCHAR(50) NULL,
    coverage_start_text VARCHAR(20) NULL,
    coverage_end_text VARCHAR(20) NULL,
    status_text VARCHAR(20) NULL,
    plan_code VARCHAR(10) NULL
);

CREATE TABLE stg.EligibilityValidated
(
    source_row_id INT NOT NULL PRIMARY KEY,
    member_external_id VARCHAR(20) NULL,
    employer_code VARCHAR(10) NULL,
    first_name VARCHAR(50) NULL,
    last_name VARCHAR(50) NULL,
    coverage_start DATE NULL,
    coverage_end DATE NULL,
    status VARCHAR(20) NULL,
    plan_code VARCHAR(10) NULL,
    reject_reason VARCHAR(100) NULL
);

CREATE TABLE stg.EligibilityReject
(
    source_row_id INT NOT NULL PRIMARY KEY,
    member_external_id VARCHAR(20) NULL,
    employer_code VARCHAR(10) NULL,
    first_name VARCHAR(50) NULL,
    last_name VARCHAR(50) NULL,
    coverage_start_text VARCHAR(20) NULL,
    coverage_end_text VARCHAR(20) NULL,
    status_text VARCHAR(20) NULL,
    plan_code VARCHAR(10) NULL,
    reject_reason VARCHAR(100) NOT NULL
);

CREATE TABLE dw.DimEmployer
(
    employer_key INT IDENTITY(1, 1) NOT NULL,
    employer_code VARCHAR(10) NOT NULL,
    CONSTRAINT PK_DimEmployer PRIMARY KEY (employer_key),
    CONSTRAINT UQ_DimEmployer_Code UNIQUE (employer_code)
);

CREATE TABLE dw.DimMember
(
    member_key INT IDENTITY(1, 1) NOT NULL,
    member_external_id VARCHAR(20) NOT NULL,
    employer_key INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    CONSTRAINT PK_DimMember PRIMARY KEY (member_key),
    CONSTRAINT UQ_DimMember_ExternalId UNIQUE (member_external_id),
    CONSTRAINT FK_DimMember_Employer
        FOREIGN KEY (employer_key)
        REFERENCES dw.DimEmployer (employer_key)
);

CREATE TABLE dw.FactEligibility
(
    eligibility_key INT IDENTITY(1, 1) NOT NULL,
    source_row_id INT NOT NULL,
    member_key INT NOT NULL,
    plan_code VARCHAR(10) NOT NULL,
    coverage_start DATE NOT NULL,
    coverage_end DATE NULL,
    status VARCHAR(20) NOT NULL,
    CONSTRAINT PK_FactEligibility PRIMARY KEY (eligibility_key),
    CONSTRAINT UQ_FactEligibility_SourceRow UNIQUE (source_row_id),
    CONSTRAINT CK_FactEligibility_Status
        CHECK (status IN ('ACTIVE', 'TERMINATED')),
    CONSTRAINT FK_FactEligibility_Member
        FOREIGN KEY (member_key)
        REFERENCES dw.DimMember (member_key)
);

Table script reference

The full script defines the staging tables first. It then defines the warehouse tables in dependency order.

Before you perform the final check, what should Explorer show under each schema?

  • Refresh Explorer.
  • Expand the stg schema.

You will see EligibilityRaw, EligibilityValidated, and EligibilityReject.

  • Expand the dw schema.

You will see DimEmployer, DimMember, and FactEligibility.

  • Use Explorer to preview each of the six tables.

Every preview returns zero rows. Your keys and constraints are ready to protect the data that arrives next.

Your Fabric database now has clear staging and warehouse boundaries. Next, you will land the synthetic eligibility batch without changing the source evidence.

Land the Raw Eligibility Batch

Your schemas are ready inside the HealthcareETL SQL database in Microsoft Fabric. Six empty tables now define where raw data and warehouse records belong.

A raw landing zone preserves every value that arrived from the source before cleaning begins. This gives your quality gate an audit trail when malformed records need investigation.

In this step, get ready to:
  • Load nine synthetic eligibility records into the raw staging table.
  • Inspect the original values for data-quality risks.
Load the synthetic source rows

ETL staging creates a boundary between incoming data and warehouse tables. This T-SQL script resets the fixture before inserting nine source records.

  • Return to the T-SQL web editor from the previous step.
  • Select New SQL query in the editor toolbar.
  • Build the loading section of 03_load_raw.sql by copying this code into the new query tab:
DELETE FROM stg.EligibilityRaw;

INSERT INTO stg.EligibilityRaw
(
    source_row_id,
    member_external_id,
    employer_code,
    first_name,
    last_name,
    coverage_start_text,
    coverage_end_text,
    status_text,
    plan_code
)
VALUES
    (1, 'M100', 'ACME', 'Avery', 'Cole', '2026-01-01', '', 'active', 'GOLD'),
    (2, ' M101 ', ' acme ', ' Blake ', ' Diaz ', ' 2026-02-01 ', '2026-12-31', ' Active ', ' silver '),
    (3, 'M102', 'NOVA', 'Casey', 'Evans', '2026-02-30', '', 'active', 'GOLD'),
    (4, 'M103', '', 'Devon', 'Ford', '2026-03-01', '', 'active', 'GOLD'),
    (5, 'M104', 'NOVA', 'Emery', 'Gray', '2026-03-01', '', 'pending', 'BRONZE'),
    (6, 'M105', 'NOVA', 'Finley', 'Hall', '2026-04-01', '2026-03-31', 'terminated', 'GOLD'),
    (7, 'm100', 'acme', 'Avery', 'Cole', '2026-01-01', '', 'ACTIVE', 'gold'),
    (8, 'M106', 'ACME', 'Gray', 'Irwin', '2026-05-01', '2026-09-30', 'terminated', 'SILVER'),
    (9, 'M107', 'NOVA', 'Harper', 'Jones', '2026-06-01', '', 'active', 'BRONZE');

What does this loading section do?

  • The DELETE statement clears any earlier fixture rows so each run begins from a known state.
  • The multirow INSERT loads all nine source records as one batch.
  • The text columns preserve spaces and inconsistent letter case exactly as supplied.
  • The date fields remain text so the impossible date can land without stopping the insert.
  • Select the DELETE statement plus the complete INSERT statement.
  • Select Run in the query toolbar.

The Messages area should report that both statements completed successfully.

Good progress. The raw table now holds the source batch exactly as it arrived.

Did the insert fail?

  • Confirm that the query tab is connected to HealthcareETL.
  • Select the DELETE statement before rerunning the complete insert.
  • Check that every row ends with a comma except row 9.

Help me fix the raw eligibility batch insert.

Inspect the raw batch

The landing table becomes useful when you can compare its rows with the source fixture. A stable row order makes each data-quality issue easy to locate.

  • Add the inspection section below the insert in 03_load_raw.sql by copying this query:
SELECT
    source_row_id,
    member_external_id,
    employer_code,
    first_name,
    last_name,
    coverage_start_text,
    coverage_end_text,
    status_text,
    plan_code
FROM stg.EligibilityRaw
ORDER BY source_row_id;

What does this inspection query do?

  • The SELECT returns every source field without transforming its value.
  • The ORDER BY keeps the rows in source ID order for repeatable inspection.

Before you run the check, consider which source values you expect to stand out as unsafe.

  • Clear any selected text in the query tab.
  • Select Run to execute the complete 03_load_raw.sql script.

The Results tab shows nine rows ordered from source row 1 through source row 9.

That is the landing zone working. Every source row is visible before any cleaning begins.

  • Inspect row 2 for surrounding spaces across several text fields.
  • Compare the letter case used across rows 1, 2, and 7.
  • Locate the impossible date 2026-02-30 on row 3.
  • Locate the blank employer_code on row 4.
  • Locate the unsupported pending status on row 5.
  • Compare the coverage dates on row 6 to spot the reversed date range.
  • Compare row 7 with row 1 to spot equivalent eligibility data after text normalization.

Why preserve the messy values?

Raw-data preservation keeps evidence of what the source delivered. A rejected record can always be traced back to its original values.

This boundary also keeps cleaning rules away from the source record. Your later checks can explain every change.

✔️ Awesome, I've got everything!

Your three schema scripts remain unchanged. Your 03_load_raw.sql script now resets the raw table, inserts nine rows, and returns them in source order.

ⓧ I'd like to double check the full code

These are the four cumulative scripts at this checkpoint.

CREATE SCHEMA stg;

The staging schema script remains unchanged.

CREATE SCHEMA dw;

The warehouse schema script remains unchanged.

CREATE TABLE stg.EligibilityRaw
(
    source_row_id INT NOT NULL PRIMARY KEY,
    member_external_id VARCHAR(20) NULL,
    employer_code VARCHAR(10) NULL,
    first_name VARCHAR(50) NULL,
    last_name VARCHAR(50) NULL,
    coverage_start_text VARCHAR(20) NULL,
    coverage_end_text VARCHAR(20) NULL,
    status_text VARCHAR(20) NULL,
    plan_code VARCHAR(10) NULL
);

CREATE TABLE stg.EligibilityValidated
(
    source_row_id INT NOT NULL PRIMARY KEY,
    member_external_id VARCHAR(20) NULL,
    employer_code VARCHAR(10) NULL,
    first_name VARCHAR(50) NULL,
    last_name VARCHAR(50) NULL,
    coverage_start DATE NULL,
    coverage_end DATE NULL,
    status VARCHAR(20) NULL,
    plan_code VARCHAR(10) NULL,
    reject_reason VARCHAR(100) NULL
);

CREATE TABLE stg.EligibilityReject
(
    source_row_id INT NOT NULL PRIMARY KEY,
    member_external_id VARCHAR(20) NULL,
    employer_code VARCHAR(10) NULL,
    first_name VARCHAR(50) NULL,
    last_name VARCHAR(50) NULL,
    coverage_start_text VARCHAR(20) NULL,
    coverage_end_text VARCHAR(20) NULL,
    status_text VARCHAR(20) NULL,
    plan_code VARCHAR(10) NULL,
    reject_reason VARCHAR(100) NOT NULL
);

CREATE TABLE dw.DimEmployer
(
    employer_key INT IDENTITY(1, 1) NOT NULL,
    employer_code VARCHAR(10) NOT NULL,
    CONSTRAINT PK_DimEmployer PRIMARY KEY (employer_key),
    CONSTRAINT UQ_DimEmployer_Code UNIQUE (employer_code)
);

CREATE TABLE dw.DimMember
(
    member_key INT IDENTITY(1, 1) NOT NULL,
    member_external_id VARCHAR(20) NOT NULL,
    employer_key INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    CONSTRAINT PK_DimMember PRIMARY KEY (member_key),
    CONSTRAINT UQ_DimMember_ExternalId UNIQUE (member_external_id),
    CONSTRAINT FK_DimMember_Employer
        FOREIGN KEY (employer_key)
        REFERENCES dw.DimEmployer (employer_key)
);

CREATE TABLE dw.FactEligibility
(
    eligibility_key INT IDENTITY(1, 1) NOT NULL,
    source_row_id INT NOT NULL,
    member_key INT NOT NULL,
    plan_code VARCHAR(10) NOT NULL,
    coverage_start DATE NOT NULL,
    coverage_end DATE NULL,
    status VARCHAR(20) NOT NULL,
    CONSTRAINT PK_FactEligibility PRIMARY KEY (eligibility_key),
    CONSTRAINT UQ_FactEligibility_SourceRow UNIQUE (source_row_id),
    CONSTRAINT CK_FactEligibility_Status
        CHECK (status IN ('ACTIVE', 'TERMINATED')),
    CONSTRAINT FK_FactEligibility_Member
        FOREIGN KEY (member_key)
        REFERENCES dw.DimMember (member_key)
);

The six table definitions remain unchanged.

DELETE FROM stg.EligibilityRaw;

INSERT INTO stg.EligibilityRaw
(
    source_row_id,
    member_external_id,
    employer_code,
    first_name,
    last_name,
    coverage_start_text,
    coverage_end_text,
    status_text,
    plan_code
)
VALUES
    (1, 'M100', 'ACME', 'Avery', 'Cole', '2026-01-01', '', 'active', 'GOLD'),
    (2, ' M101 ', ' acme ', ' Blake ', ' Diaz ', ' 2026-02-01 ', '2026-12-31', ' Active ', ' silver '),
    (3, 'M102', 'NOVA', 'Casey', 'Evans', '2026-02-30', '', 'active', 'GOLD'),
    (4, 'M103', '', 'Devon', 'Ford', '2026-03-01', '', 'active', 'GOLD'),
    (5, 'M104', 'NOVA', 'Emery', 'Gray', '2026-03-01', '', 'pending', 'BRONZE'),
    (6, 'M105', 'NOVA', 'Finley', 'Hall', '2026-04-01', '2026-03-31', 'terminated', 'GOLD'),
    (7, 'm100', 'acme', 'Avery', 'Cole', '2026-01-01', '', 'ACTIVE', 'gold'),
    (8, 'M106', 'ACME', 'Gray', 'Irwin', '2026-05-01', '2026-09-30', 'terminated', 'SILVER'),
    (9, 'M107', 'NOVA', 'Harper', 'Jones', '2026-06-01', '', 'active', 'BRONZE');

SELECT
    source_row_id,
    member_external_id,
    employer_code,
    first_name,
    last_name,
    coverage_start_text,
    coverage_end_text,
    status_text,
    plan_code
FROM stg.EligibilityRaw
ORDER BY source_row_id;

The completed loading script preserves the batch before returning all nine rows for inspection.

Your raw batch is preserved with every data-quality problem still visible. Next, you will test what happens when a direct date conversion meets the malformed value on row 3.

Break the Naive Direct Load

Your nine-row eligibility batch is now preserved in stg.EligibilityRaw inside Microsoft Fabric. That untouched landing zone gives you a safe place to test a risky shortcut without changing the source rows.

A direct CAST looks like the fastest route from date text to a typed warehouse column. One malformed date can stop the entire batch before any valid records reach the warehouse.

In this step, get ready to:
  • Test a direct date conversion against the raw eligibility batch.
  • Trace the conversion failure to source row 3.
  • Explain why the malformed row must be preserved for rejection handling.
Run the naive date conversion

A T-SQL conversion asks the database to turn source text into a typed value. A direct conversion makes the whole statement depend on every source value being valid.

  • Select New SQL query in the toolbar to keep this test separate from the raw-load script.
  • Paste the query below into the new query tab.
SELECT
    source_row_id,
    member_external_id,
    CAST(TRIM(coverage_start_text) AS DATE) AS coverage_start
FROM stg.EligibilityRaw;

What Does This Query Test?

  • The source_row_id and member_external_id columns identify the source record being converted.
  • TRIM(coverage_start_text) removes surrounding spaces before conversion.
  • CAST(... AS DATE) requires the date text to be convertible. One invalid value stops the statement.

Before you run the query, consider whether it returns all nine rows or stops at the invalid value.

  • Select Run in the query toolbar.
  • Select the Messages tab below the editor.

The Results tab does not return the full batch. The Messages tab reports a conversion failure.

This Failure Is Intentional

The statement stops when CAST reaches invalid date text. The valid source records never reach the result set through this direct conversion.

This is the designed failure. Your source table stays unchanged because the query only reads data.

🙋‍♀️ Did the Query Return Rows?

  • Compare your query with the snippet above.
  • Confirm the conversion uses CAST(TRIM(coverage_start_text) AS DATE).
  • Confirm the source table is stg.EligibilityRaw.

Help me diagnose why the naive conversion did not fail.

Trace the failing source row

Raw-data preservation lets you connect a failed transformation to the exact value that arrived. The original row becomes evidence for investigation instead of disappearing inside a stopped batch.

  • Switch back to the 03_load_raw.sql query tab from earlier.
  • Select the Results tab.
  • Locate the record whose source_row_id is 3.
  • Read its coverage_start_text value.

You'll see 2026-02-30 on row 3. February has no thirtieth day, so that text cannot become a valid date.

  • Explain aloud why row 3 belongs in a rejected-row path.

Why Preserve Rejected Rows?

Preserving the original value gives an ETL support team evidence of what arrived. The source row can be investigated without guessing what a cleaning rule changed.

Redirecting the malformed row lets valid records keep moving. A rejection reason gives the bad row a clear path to correction.

Before the final run, predict whether inspecting the raw row changed the failure or changed any source data.

  • Switch back to the one-off conversion query tab.
  • Select Run again.
  • Select the Messages tab.

You'll see the same conversion failure instead of the full batch. The one-off query leaves stg.EligibilityRaw unchanged because it only reads data.

That risky shortcut is now exposed. Next, you'll replace the stopped batch with resilient validation.

Validate, Reject, and Load

Your unchanged source batch is still available in Microsoft Fabric. The failed CAST exposed the risk of sending dirty values straight into typed warehouse columns.

This step builds an ETL quality gate around that raw batch. TRY_CONVERT keeps the flow running by returning NULL for malformed values. A searched CASE assigns the first applicable rejection reason.

In this step, get ready to:
  • Standardize all nine source rows while preserving the raw batch.
  • Route five invalid or duplicate rows into the rejection table.
  • Load four accepted records into the dimension and fact tables.
Standardize and classify every source row

The validated staging table creates a typed version of each raw row. Standardization removes edge spaces from text. It also converts identifiers and statuses to a consistent case.

  • Click New SQL query in the SQL query editor toolbar.
  • Use the new query tab for 04_transform_load.sql.
  • Clear the target tables by running this first section:
DELETE FROM dw.FactEligibility;
DELETE FROM dw.DimMember;
DELETE FROM dw.DimEmployer;
DELETE FROM stg.EligibilityReject;
DELETE FROM stg.EligibilityValidated;

Why clear the target tables first?

  • The deletion order starts with the fact table because it references the member dimension.
  • The member dimension is cleared before the employer dimension because each member references an employer.
  • The raw table is absent from these statements. All nine original source rows remain unchanged.

Good start. The target tables are ready for a clean rerun while the original batch remains available in stg.EligibilityRaw.

  • Paste the first half of the validated staging insert below the deletion statements:
INSERT INTO stg.EligibilityValidated
(
    source_row_id,
    member_external_id,
    employer_code,
    first_name,
    last_name,
    coverage_start,
    coverage_end,
    status,
    plan_code,
    reject_reason
)
SELECT
    r.source_row_id,
    UPPER(TRIM(r.member_external_id)),
    UPPER(TRIM(r.employer_code)),
    TRIM(r.first_name),
    TRIM(r.last_name),
    TRY_CONVERT(DATE, TRIM(r.coverage_start_text), 23),
    TRY_CONVERT(DATE, NULLIF(TRIM(r.coverage_end_text), ''), 23),
    UPPER(TRIM(r.status_text)),
    UPPER(TRIM(r.plan_code)),
    CASE

How does standardization work?

  • TRIM removes spaces from both ends of incoming text.
  • UPPER gives identifiers and coded values one consistent format.
  • NULLIF changes an empty coverage end value into NULL.
  • The date style 23 handles the source format used by valid dates in this batch.
  • Complete the validated staging insert by pasting this continuation directly below CASE:
        WHEN NULLIF(TRIM(r.member_external_id), '') IS NULL
            THEN 'Missing member ID'
        WHEN NULLIF(TRIM(r.employer_code), '') IS NULL
            THEN 'Missing employer code'
        WHEN TRY_CONVERT(DATE, TRIM(r.coverage_start_text), 23) IS NULL
            THEN 'Invalid coverage start date'
        WHEN NULLIF(TRIM(r.coverage_end_text), '') IS NOT NULL
             AND TRY_CONVERT(DATE, TRIM(r.coverage_end_text), 23) IS NULL
            THEN 'Invalid coverage end date'
        WHEN TRY_CONVERT(DATE, NULLIF(TRIM(r.coverage_end_text), ''), 23)
             < TRY_CONVERT(DATE, TRIM(r.coverage_start_text), 23)
            THEN 'Coverage end precedes start'
        WHEN UPPER(TRIM(r.status_text)) NOT IN ('ACTIVE', 'TERMINATED')
            THEN 'Unrecognized status'
        WHEN EXISTS
        (
            SELECT 1
            FROM stg.EligibilityRaw AS earlier
            WHERE earlier.source_row_id < r.source_row_id
              AND UPPER(TRIM(earlier.member_external_id)) = UPPER(TRIM(r.member_external_id))
              AND UPPER(TRIM(earlier.plan_code)) = UPPER(TRIM(r.plan_code))
              AND TRIM(earlier.coverage_start_text) = TRIM(r.coverage_start_text)
        )
            THEN 'Duplicate eligibility record'
        ELSE NULL
    END
FROM stg.EligibilityRaw AS r;

How are rejection reasons assigned?

  • The searched CASE checks each quality rule in order. The first matching rule supplies the rejection reason.
  • TRY_CONVERT changes malformed dates into NULL. This makes the invalid row classifiable without stopping the batch.
  • The EXISTS check compares each row with lower source_row_id values. The later copy becomes the duplicate.

Before you run the completed insert, which rows do you expect to receive a rejection reason?

  • Click Run in the query editor toolbar.
  • Refresh Explorer.
  • Select stg.EligibilityValidated in Explorer to preview its rows.

You should see nine validated rows. Source row 2 now contains M101, ACME, ACTIVE, and SILVER without surrounding spaces.

Source row 3 has a NULL coverage start. Its rejection reason is Invalid coverage start date.

Validated rows missing or unchanged?

Confirm that both halves of the insert sit directly after the deletion statements in the same query tab. Check that the continuation begins below CASE.

Refresh Explorer after the query finishes. The table preview can otherwise show an older result.

Help me troubleshoot the validated staging insert

Route rejected rows with their originals

A useful rejection table keeps the exact values that arrived from the source. Support teams can then compare the original text with the reason assigned during validation.

  • Append the rejection insert below the validated staging insert in 04_transform_load.sql:
INSERT INTO stg.EligibilityReject
(
    source_row_id,
    member_external_id,
    employer_code,
    first_name,
    last_name,
    coverage_start_text,
    coverage_end_text,
    status_text,
    plan_code,
    reject_reason
)
SELECT
    r.source_row_id,
    r.member_external_id,
    r.employer_code,
    r.first_name,
    r.last_name,
    r.coverage_start_text,
    r.coverage_end_text,
    r.status_text,
    r.plan_code,
    v.reject_reason
FROM stg.EligibilityRaw AS r
INNER JOIN stg.EligibilityValidated AS v
    ON v.source_row_id = r.source_row_id
WHERE v.reject_reason IS NOT NULL;

What does this insert preserve?

  • The join matches every raw row with its validated result through source_row_id.
  • The selected source columns come from stg.EligibilityRaw. Spaces and lowercase values remain visible for investigation.
  • The filter keeps only rows whose validated copy contains a rejection reason.
  • Click Run to rerun the script from the beginning.
  • Refresh Explorer.
  • Select stg.EligibilityReject in Explorer to preview its rows.

You should see five rejected rows. Their reasons cover the invalid start date, missing employer, unsupported status, reversed date range, and duplicate eligibility record.

That is the quality gate doing its job. Every failed record retains the original source values needed to explain the rejection.

Seeing the wrong rejection count?

Confirm that the rejection filter reads WHERE v.reject_reason IS NOT NULL. Check that the join uses source_row_id on both tables.

Make sure the deletion statements remain at the top of the script. Without the cleanup section, a rerun can conflict with existing primary keys.

Help me fix the rejected-row load

Load accepted rows into the warehouse

Accepted records now move into dimension tables for employers and members. The eligibility details then enter a fact table connected through generated keys.

  • Append the employer and member dimension inserts below the rejection insert:
INSERT INTO dw.DimEmployer (employer_code)
SELECT DISTINCT employer_code
FROM stg.EligibilityValidated
WHERE reject_reason IS NULL;

INSERT INTO dw.DimMember
(
    member_external_id,
    employer_key,
    first_name,
    last_name
)
SELECT DISTINCT
    v.member_external_id,
    e.employer_key,
    v.first_name,
    v.last_name
FROM stg.EligibilityValidated AS v
INNER JOIN dw.DimEmployer AS e
    ON e.employer_code = v.employer_code
WHERE v.reject_reason IS NULL;

Why load the dimensions in this order?

  • DimEmployer receives each accepted employer code once through DISTINCT.
  • DimMember joins to the employer dimension to obtain the generated employer_key.
  • Both inserts require reject_reason IS NULL. Rejected rows stay outside the warehouse model.
  • Click Run to rebuild the populated sections.
  • Select dw.DimEmployer in Explorer to preview its rows.
  • Select dw.DimMember in Explorer to preview its rows.

You should see two employers in dw.DimEmployer. You should see four accepted members in dw.DimMember.

The accepted business entities are now keyed and ready. The final insert connects each accepted eligibility record to its member.

  • Append the fact insert and the two row-count queries below the dimension inserts:
INSERT INTO dw.FactEligibility
(
    source_row_id,
    member_key,
    plan_code,
    coverage_start,
    coverage_end,
    status
)
SELECT
    v.source_row_id,
    m.member_key,
    v.plan_code,
    v.coverage_start,
    v.coverage_end,
    v.status
FROM stg.EligibilityValidated AS v
INNER JOIN dw.DimMember AS m
    ON m.member_external_id = v.member_external_id
WHERE v.reject_reason IS NULL;

SELECT COUNT(*) AS accepted_rows
FROM dw.FactEligibility;

SELECT COUNT(*) AS rejected_rows
FROM stg.EligibilityReject;

How does the final load work?

  • The fact insert joins accepted staging rows to dw.DimMember through the standardized member identifier.
  • The generated member_key becomes the relationship between the fact row and its member.
  • The final two queries expose separate accepted and rejected totals in the Results area.

✔️ Awesome, I've got everything!

Your 04_transform_load.sql query now clears the targets, validates the raw rows, routes rejections, loads the warehouse tables, and reports both counts.

ⓧ I'd like to double check the full code

Your complete 04_transform_load.sql query should match this reference exactly.

DELETE FROM dw.FactEligibility;
DELETE FROM dw.DimMember;
DELETE FROM dw.DimEmployer;
DELETE FROM stg.EligibilityReject;
DELETE FROM stg.EligibilityValidated;

INSERT INTO stg.EligibilityValidated
(
    source_row_id,
    member_external_id,
    employer_code,
    first_name,
    last_name,
    coverage_start,
    coverage_end,
    status,
    plan_code,
    reject_reason
)
SELECT
    r.source_row_id,
    UPPER(TRIM(r.member_external_id)),
    UPPER(TRIM(r.employer_code)),
    TRIM(r.first_name),
    TRIM(r.last_name),
    TRY_CONVERT(DATE, TRIM(r.coverage_start_text), 23),
    TRY_CONVERT(DATE, NULLIF(TRIM(r.coverage_end_text), ''), 23),
    UPPER(TRIM(r.status_text)),
    UPPER(TRIM(r.plan_code)),
    CASE
        WHEN NULLIF(TRIM(r.member_external_id), '') IS NULL
            THEN 'Missing member ID'
        WHEN NULLIF(TRIM(r.employer_code), '') IS NULL
            THEN 'Missing employer code'
        WHEN TRY_CONVERT(DATE, TRIM(r.coverage_start_text), 23) IS NULL
            THEN 'Invalid coverage start date'
        WHEN NULLIF(TRIM(r.coverage_end_text), '') IS NOT NULL
             AND TRY_CONVERT(DATE, TRIM(r.coverage_end_text), 23) IS NULL
            THEN 'Invalid coverage end date'
        WHEN TRY_CONVERT(DATE, NULLIF(TRIM(r.coverage_end_text), ''), 23)
             < TRY_CONVERT(DATE, TRIM(r.coverage_start_text), 23)
            THEN 'Coverage end precedes start'
        WHEN UPPER(TRIM(r.status_text)) NOT IN ('ACTIVE', 'TERMINATED')
            THEN 'Unrecognized status'
        WHEN EXISTS
        (
            SELECT 1
            FROM stg.EligibilityRaw AS earlier
            WHERE earlier.source_row_id < r.source_row_id
              AND UPPER(TRIM(earlier.member_external_id)) = UPPER(TRIM(r.member_external_id))
              AND UPPER(TRIM(earlier.plan_code)) = UPPER(TRIM(r.plan_code))
              AND TRIM(earlier.coverage_start_text) = TRIM(r.coverage_start_text)
        )
            THEN 'Duplicate eligibility record'
        ELSE NULL
    END
FROM stg.EligibilityRaw AS r;

INSERT INTO stg.EligibilityReject
(
    source_row_id,
    member_external_id,
    employer_code,
    first_name,
    last_name,
    coverage_start_text,
    coverage_end_text,
    status_text,
    plan_code,
    reject_reason
)
SELECT
    r.source_row_id,
    r.member_external_id,
    r.employer_code,
    r.first_name,
    r.last_name,
    r.coverage_start_text,
    r.coverage_end_text,
    r.status_text,
    r.plan_code,
    v.reject_reason
FROM stg.EligibilityRaw AS r
INNER JOIN stg.EligibilityValidated AS v
    ON v.source_row_id = r.source_row_id
WHERE v.reject_reason IS NOT NULL;

INSERT INTO dw.DimEmployer (employer_code)
SELECT DISTINCT employer_code
FROM stg.EligibilityValidated
WHERE reject_reason IS NULL;

INSERT INTO dw.DimMember
(
    member_external_id,
    employer_key,
    first_name,
    last_name
)
SELECT DISTINCT
    v.member_external_id,
    e.employer_key,
    v.first_name,
    v.last_name
FROM stg.EligibilityValidated AS v
INNER JOIN dw.DimEmployer AS e
    ON e.employer_code = v.employer_code
WHERE v.reject_reason IS NULL;

INSERT INTO dw.FactEligibility
(
    source_row_id,
    member_key,
    plan_code,
    coverage_start,
    coverage_end,
    status
)
SELECT
    v.source_row_id,
    m.member_key,
    v.plan_code,
    v.coverage_start,
    v.coverage_end,
    v.status
FROM stg.EligibilityValidated AS v
INNER JOIN dw.DimMember AS m
    ON m.member_external_id = v.member_external_id
WHERE v.reject_reason IS NULL;

SELECT COUNT(*) AS accepted_rows
FROM dw.FactEligibility;

SELECT COUNT(*) AS rejected_rows
FROM stg.EligibilityReject;

Before the final run, what accepted and rejected totals do you expect from the nine-row source batch?

  • Click Run to execute the complete 04_transform_load.sql query.
  • Use the results dropdown to view each result set.
  • Select dw.FactEligibility in Explorer to preview the accepted records.

The first result set should show accepted_rows as 4. The second result set should show rejected_rows as 5.

You should see four rows in dw.FactEligibility for source rows 1, 2, 8, and 9.

Final counts do not show four and five?

Confirm that every warehouse insert filters on reject_reason IS NULL. Confirm that the rejection insert filters on reject_reason IS NOT NULL.

Check the Messages area for the statement that stopped. Compare the nearby code with the full script above.

Help me diagnose the final ETL counts

Your quality gate now preserves all nine source rows, redirects five failures, and loads four accepted records. Next up, you will reconcile those totals and turn the warehouse rows into a business summary.

Prove the Numbers Reconcile

Your Microsoft Fabric load now protects the warehouse by routing five bad records away from four accepted records. This step proves that every one of the nine source rows still has an outcome.

A reconciliation check provides the control evidence that makes an ETL result trustworthy. A T-SQL report will summarize the rejected rows. It will also summarize the accepted records by employer and status.

In this step, get ready to:
  • Calculate control totals that expose missing source rows.
  • Summarize each rejection reason with a row count.
  • Join the fact table to both dimensions for an employer-status report.
Calculate the control totals

A control total compares the rows that arrived with the rows that reached an accepted or rejected outcome. The reconciliation difference must be zero for every source row to be accounted for.

  • Select New SQL query in the query editor.
  • Name the query 05_validation_report.sql.
  • Add the control-total query below:
SELECT
    (SELECT COUNT(*) FROM stg.EligibilityRaw) AS source_rows,
    (SELECT COUNT(*) FROM dw.FactEligibility) AS accepted_rows,
    (SELECT COUNT(*) FROM stg.EligibilityReject) AS rejected_rows,
    (SELECT COUNT(*) FROM stg.EligibilityRaw)
      - (SELECT COUNT(*) FROM dw.FactEligibility)
      - (SELECT COUNT(*) FROM stg.EligibilityReject) AS reconciliation_difference;

What does this query prove?

  • The three COUNT(*) subqueries calculate the source total plus both possible outcomes.
  • The final expression subtracts accepted rows plus rejected rows from the source total.
  • A zero difference proves that no source row disappeared between landing and loading.

Before you run this check, do you expect any source row to remain unaccounted for?

  • Select the control-total statement in 05_validation_report.sql.
  • Select Run in the query toolbar.

In Results, you will see source_rows as 9. You will see accepted_rows as 4.

You will see rejected_rows as 5. You will see reconciliation_difference as 0.

That closes the audit loop. All nine source rows have a visible destination.

Seeing a nonzero difference?

  • Return to 04_transform_load.sql if the accepted or rejected totals differ from the previous load results.
  • Confirm that stg.EligibilityRaw still contains all nine source rows.
  • Check that the control-total query references dw.FactEligibility for accepted rows.

Help me trace a reconciliation mismatch.

Summarize every rejection reason

Control totals prove that every row has an outcome. A grouped rejection summary explains why each failed record was redirected.

  • Add the rejection-summary query below the control-total statement in 05_validation_report.sql:
SELECT
    reject_reason,
    COUNT(*) AS rejected_rows
FROM stg.EligibilityReject
GROUP BY reject_reason
ORDER BY reject_reason;

How does the summary work?

  • The GROUP BY reject_reason clause creates one group for each recorded failure reason.
  • The COUNT(*) expression shows how many rows followed each rejection path.
  • The ORDER BY reject_reason clause keeps the output stable for review.
  • Select the rejection-summary statement in 05_validation_report.sql.
  • Select Run in the query toolbar.

You will see five rows in Results. Each row will show rejected_rows as 1.

The rows name Coverage end precedes start, Duplicate eligibility record, Invalid coverage start date, Missing employer code, and Unrecognized status.

Missing a rejection reason?

  • Confirm that the query reads from stg.EligibilityReject.
  • Compare the current reject_reason values with the five reasons loaded in the previous step.
  • Check that GROUP BY reject_reason appears before the ordering clause.

Help me diagnose my rejection summary.

Build the employer-status report

The fact table stores accepted eligibility events with member keys. The dimension tables supply the member and employer context needed for business reporting.

  • Add the employer-status query below the rejection summary in 05_validation_report.sql:
SELECT
    e.employer_code,
    f.status,
    COUNT(*) AS eligibility_rows
FROM dw.FactEligibility AS f
INNER JOIN dw.DimMember AS m
    ON m.member_key = f.member_key
INNER JOIN dw.DimEmployer AS e
    ON e.employer_key = m.employer_key
GROUP BY
    e.employer_code,
    f.status
ORDER BY
    e.employer_code,
    f.status;

How do the joins support reporting?

  • The alias f represents the four accepted eligibility records.
  • The first join matches f.member_key with m.member_key to identify each member.
  • The second join matches m.employer_key with e.employer_key to identify each employer.
  • The grouped count produces employer-status totals that tools such as Power BI can visualize.
  • Select the employer-status statement in 05_validation_report.sql.
  • Select Run in the query toolbar.

You will see three rows in Results. The ACME employer has 2 active eligibility rows.

The ACME employer also has 1 terminated row. The NOVA employer has 1 active row.

Report rows missing or duplicated?

  • Confirm that the first join uses member_key on both sides.
  • Confirm that the second join uses employer_key on both sides.
  • Check that both e.employer_code and f.status appear in the GROUP BY clause.

Help me troubleshoot the employer-status joins.

  • Practice explaining the join path from f.member_key to m.member_key.
  • Practice explaining how m.employer_key connects each member to an employer.
  • Practice explaining why grouped dimension values make the result useful for reporting.

✔️ Awesome, I've got everything!

Your 05_validation_report.sql query now contains all three statements in the correct order.

ⓧ I'd like to double check the full code

Compare your completed 05_validation_report.sql query with this full version.

SELECT
    (SELECT COUNT(*) FROM stg.EligibilityRaw) AS source_rows,
    (SELECT COUNT(*) FROM dw.FactEligibility) AS accepted_rows,
    (SELECT COUNT(*) FROM stg.EligibilityReject) AS rejected_rows,
    (SELECT COUNT(*) FROM stg.EligibilityRaw)
      - (SELECT COUNT(*) FROM dw.FactEligibility)
      - (SELECT COUNT(*) FROM stg.EligibilityReject) AS reconciliation_difference;

SELECT
    reject_reason,
    COUNT(*) AS rejected_rows
FROM stg.EligibilityReject
GROUP BY reject_reason
ORDER BY reject_reason;

SELECT
    e.employer_code,
    f.status,
    COUNT(*) AS eligibility_rows
FROM dw.FactEligibility AS f
INNER JOIN dw.DimMember AS m
    ON m.member_key = f.member_key
INNER JOIN dw.DimEmployer AS e
    ON e.employer_key = m.employer_key
GROUP BY
    e.employer_code,
    f.status
ORDER BY
    e.employer_code,
    f.status;

Before you run the complete report, do you expect all three result sets to remain consistent when they execute together?

  • Clear any highlighted code in the 05_validation_report.sql query tab.
  • Select Run in the query toolbar.

You will see three result sets available in Results.

  • Use the results dropdown to select the first result set.

You will see 9 source rows plus 4 accepted rows. You will also see 5 rejected rows plus a 0 reconciliation difference.

  • Use the results dropdown to select the second result set.

You will see five specific rejection reasons. Each reason will have a count of 1.

  • Use the results dropdown to select the final result set.

You will see the three employer-status groups from the four accepted records. Together, these result sets prove that every source row is explained.

You have built the evidence behind a trustworthy load. Your quality gate now accounts for every source row and produces a business-ready summary.

Secret mission

Turn the Checks into a Stored Procedure

Your validation queries prove that every eligibility row is accounted for. Package those checks into one reusable stored procedure that can report across all employers or focus the business summary on a single employer.

Clean Up Your Resources

Clean Up Your Resources

Your project remains at $0 while you use an eligible Microsoft Fabric trial during its 60-day lifetime. Decide whether to keep the resources available, pause your work, or remove the dedicated workspace entirely.

Cost warning

The Microsoft Fabric trial gives free access for 60 days. The trial continues to age while your project sits unused.

  • Choose a paid Fabric capacity only if you intend to accept its cost.

Resources you used:

  • A dedicated Microsoft Fabric workspace, if you created one for this project.
  • A SQL database in Microsoft Fabric named HealthcareETL.
  • The stg schema with three tables containing the synthetic staging data.
  • The dw schema with three tables containing the accepted dimensional data.
  • The dbo.usp_EligibilityQualityReport stored procedure.
  • Eight saved T-SQL query scripts in the Fabric query editor.

Keep everything running

No action needed. Choose this if you want to keep the quality gate ready for your interview demonstration.

  • Leave the dedicated workspace available with HealthcareETL intact.
  • Open Account manager to check the remaining trial days.

Pause - I'll come back to this later

Pausing here means leaving the Fabric items untouched while you stop running queries. The trial still follows its 60-day lifetime.

  • Stop running queries in the T-SQL web editor.
  • Leave HealthcareETL untouched.
  • Open Account manager before your next session to check the remaining trial days.

Why Doesn't the Trial Pause?

The documented capacity pause feature requires a paid F SKU capacity.

This trial workflow has no database-level pause. Waiting still uses the trial's 60-day lifetime.

Delete - I don't want to use this again

Removing the workspace is permanent. The next checks protect your project evidence before that irreversible step.

Check the Workspace First

Workspace removal deletes every item it contains. This option applies only to a dedicated project workspace where you are an administrator.

  • Return to the Keep or Pause option if the workspace contains unrelated items.
  • Copy each saved T-SQL query into a local .sql file outside Microsoft Fabric.
  • Confirm that any screenshots you want to keep are stored outside the workspace.
  • Switch back to the dedicated project workspace from the HealthcareETL editor.
  • Open Workspace settings.
  • Select Other.
  • Select Remove this workspace.
  • Check your workspaces to confirm that the dedicated project workspace no longer appears.

That's the cleanup complete. HealthcareETL, stg, dw, all six tables, dbo.usp_EligibilityQualityReport, and the saved T-SQL queries are now removed with the workspace.

Nice Work!

Nice Work!

You did it! You built a healthcare eligibility ETL quality gate with T-SQL in Microsoft Fabric.

You've learned how to:

  • Protect source fidelity with an ETL staging boundary that preserves all nine original records before any quality rule runs.
  • Replace a batch-stopping conversion with resilient validation for clean values. Route five malformed or duplicate records to explicit rejection reasons.
  • Load employer and member dimensions plus an eligibility fact table from four accepted records. Prove that all nine source rows reconcile with a difference of zero through control totals and reporting results.
  • Secret Mission: Turn the quality report into a reusable stored procedure. Use its optional employer filter to return only ACME in the final business summary.

Ready to quiz yourself?