Data Layer and ETL

This theme covers everything that brings data to the model: source views or tables, Power Query (M language), native queries, query folding, dataflows, incremental refresh...

Guide≈ 6 min readPartly checkedReading

In brief

This theme covers everything that brings data to the model: source views or tables, Power Query (M language), native queries, query folding, dataflows, incremental refresh, column contracts, replay, and idempotency. It stops where modeling begins (MOD) and where the upstream data pipeline begins (PD matrix).

On this page

1. Why this theme
2. What this theme measures
3. The 5 levels
4. Milestones by level
5. Detailed reading by level
6. Partial achievement and rating
7. Moving to the next level
8. See also

Level: 🟢→🔴 · Theme code: ETL

1. Why this theme#

Transformation logic placed in the report (Power Query) is the most expensive to maintain: invisible to the DBA, untested, recalculated at every refresh, often duplicated from one report to another. The golden rule for mature teams: transform as far upstream as possible (SQL view, datamart table), and only as late as needed for what is report-specific. An M step that breaks query folding shifts a filter from “pushed to the server” to “load everything then filter in memory”: a 2-minute refresh becomes 40.

2. What this theme measures#

Axis: from copy-pasted queries to declarative, folded, incremental, and contract-driven ingestion.
Key indicators: share of queries that fold (M diagnostics), number of sources used without a dedicated view, refresh time, failure rate, successful replay rate.
Units: % of folded queries, minutes, number of incidents/month.

3. The 5 levels#

Level	Short name	Promise	Typical indicator (orders of magnitude)
1	Copy-paste	Data arrives when the author refreshes manually from their workstation.	Source = file or raw table; 0 dedicated view
2	Source views	Each report reads stable views; a source outage is understood within one hour.	> 70% of tables read through views; diagnosis in < 1 h
3	Folded and parameterized	Refresh is predictable and depends on neither a workstation nor one person.	> 80% of folded queries; parameterized sources
4	Contractual and replayable	A source-side column change is detected before reaching the user; replaying a load gives the same result.	0 “silent” broken refreshes; proven idempotent replay
5	Tooled incremental	Volume grows without growing lead time: only the delta is processed, and ingestion self-validates.	Refresh ≈ proportional to delta, not total

4. Milestones by level#

ID	Milestone	Verifiable evidence
BI-ETL-1.1	Sources are identified and accessible to the service (gateway or cloud connection)	Minimal source inventory
BI-ETL-1.2	The report refreshes without intervention from a specific person	Another team member can restart it
BI-ETL-1.3	Connection credentials are not tied to a named individual	Service account or managed identity
BI-ETL-2.1	The report reads dedicated BI SQL views (or published tables), not application tables	`dm.` / `rpt.` schema or equivalent
BI-ETL-2.2	View column names and types match the model (no cascading renaming aliases)	Column-by-column review
BI-ETL-2.3	Each M query has a meaningful name and a single responsibility	Query review
BI-ETL-2.4	A load incident is diagnosed from a log	Refresh history reviewed
BI-ETL-3.1	Query folding is verified (View Native Query / diagnostics) on large tables	Screenshot or documented test
BI-ETL-3.2	Server and database as parameters (dev/test/prod)	M parameters or deployment rules
BI-ETL-3.3	No heavy non-foldable transformation on large volumes; otherwise moved to SQL	List of justified non-foldable steps
BI-ETL-3.4	Incremental refresh enabled on high-volume fact tables	`RangeStart/RangeEnd` policy in place
BI-ETL-3.5	File-based sources (Excel, CSV) are rare, controlled, in managed storage	SharePoint/OneLake path, not local workstation
BI-ETL-4.1	Column contract: expected list (name, type, nullability) versioned	Contract file or test
BI-ETL-4.2	A check compares expected schema to actual schema before publishing	Alert triggered on missing column
BI-ETL-4.3	Replaying a load (same window) neither duplicates nor loses rows	Documented replay test
BI-ETL-4.4	Dimensions with unknown keys have an “unknown” member rather than orphan rows	Key integrity check
BI-ETL-5.1	Tool-managed partitions (XMLA partitions, targeted refresh) by period	Partition-level refresh log
BI-ETL-5.2	Source drift detection: alert before failure	Tracked automatic alert
BI-ETL-5.3	Automatic partial recovery after failure (restart failed partition)	Incident closed without intervention
BI-ETL-5.4	The source → model chain is traced (lineage)	Viewable lineage

5. Detailed reading by level#

🟢 Level 1 — Copy-paste#

Signs: Excel workbooks on a workstation, duplicated queries, personal credentials.
Benefit: immediate startup. Does not guarantee: recovery, freshness, security.
Difficulty: 1/5 · Effort: 1/5.

🟢 Level 2 — Source views#

Signs: reports query SELECT … FROM rpt.vFactVisite instead of application tables; the DBA knows the consumers.
Benefit: isolation between application schema and report; a table change does not impact the report if the view absorbs the change.
Limit: no folding control yet.
Difficulty: 2/5 · Effort: 2/5.

✅ Good practice

Publish “contractual” views: same column names as in the model, explicit types, no SELECT *. The report renames nothing.

🟡 Level 3 — Folded and parameterized#

Signs: filters, joins, and groupings are folded back to SQL; M diagnostics are run for each high-volume table; incremental on date reduces load.
Benefit: short, predictable refreshes; same sources across dev/test/prod.

let
    Source   = Sql.Database(Serveur, Base),
    Faits    = Source{[Schema="rpt", Item="vFactVisite"]}[Data],
    Fenetre  = Table.SelectRows(Faits, each [Date] >= RangeStart and [Date] < RangeEnd)
in
    Fenetre

Limit: incremental window rarely tested in replay.
Difficulty: 3/5 · Effort: 3/5.

⚠️ Pitfall

Symptom: incremental refresh reloads the entire table. Cause: the filter on RangeStart/RangeEnd does not fold (type conversion, non-foldable function). Fix: filter the native column before any processing; verify “View Native Query.”

🟡 Level 4 — Contractual and replayable#

Signs: a test compares expected schema to actual schema; idempotency is demonstrated (replaying window D−3 gives the same total). Orphan keys are handled with an “unknown” member.
Benefit: no more refreshes that succeed while producing incorrect numbers.
Difficulty: 4/5 · Effort: 4/5.

🧪 Example

SFA running example: the field app adds a STATUT_VISITE column. Without a contract, the view propagates it, the model ignores it, nothing breaks. With a contract: the alert flags “new undeclared column,” the team decides, the model version is incremented.

🔴 Level 5 — Tooled incremental#

Signs: partitions driven by API/XMLA, automatic partial recovery, lineage from source to visual, drift monitoring.
Benefit: ingestion cost grows more slowly than volume.
Limit: tooling to maintain; operational complexity (OPS).
Difficulty: 5/5 · Effort: 5/5.

💡 Expert tip

For a large fact table, prefer fixed monthly partitions (history) + one “current” partition refreshed on demand; a model’s native incremental is simpler, but partition refresh via XMLA gives fine-grained replay control.

🤖 AI

AI writes simple M well and suggests cleaning steps; it often breaks folding without saying so and does not know your column conventions. Always verify the native query.

6. Partial achievement and rating#

See the common rule. “A partial 5”: XMLA partitions in place for two out of four facts (BI-ETL-5.1 = 0.5), drift alert present but no automatic recovery (5.2 = 1, 5.3 = 0): level 5 is not achieved, but the average of level 5 milestones (≈ 0.5) gives a score ≈ 4.5 if level 4 is achieved.

7. Moving to the next level#

Possible means (optional):

Transition	Possible levers
1 → 2	Create a BI view schema; service account; remove local workbooks
2 → 3	Folding diagnostics; connection parameters; `RangeStart/RangeEnd`
3 → 4	Version-controlled column contract; schema test in pipeline; replay tests
4 → 5	XMLA partitions; lineage; drift alerts; recovery scripts

Pitfalls:

False positive: “everything folds” but on a view that itself does a full scan of 300M rows. The bottleneck just moved.
Goodhart: targeting “100% folding” leads to excluding useful transformations; judge by refresh time and correctness.
Rewriting in M what should be in the datamart: hidden technical debt (see PD — modeling).

8. See also#

Related themes: MOD · PRF · TST · OPS
Other matrices: PD — Data Sources and Data Contracts · PD — Lead Time and DataOps · PD — Data Quality
Content: SQL Server in SaaS — Overview · OneLake + SaaS Mobile Apps: Challenges and Pitfalls · VCS and SQL/BI Data · CI/CD for Data: Databases, Datamarts, Pipelines, and Reports

✻ Written by Claude Sonnet 5.5