i

Description

Écluse de canal vue de l'amont : l'eau passe par étapes, de bief en bief, sous le contrôle de portes.

Source

Poliphilo · original page

Licence

CC0 Public domain

Original photo, unretouched. The background is a version of it stylised by the site.

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...

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. 1. Why this theme
2. 2. What this theme measures
3. 3. The 5 levels
4. 4. Milestones by level
5. 5. Detailed reading by level
   1. 🟢 Level 1 — Copy-paste
   2. 🟢 Level 2 — Source views
   3. 🟡 Level 3 — Folded and parameterized
   4. 🟡 Level 4 — Contractual and replayable
   5. 🔴 Level 5 — Tooled incremental
6. 6. Partial achievement and rating
7. 7. Moving to the next level
8. 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#

4. Milestones by level#

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.

🟡 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.

🟡 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.

🔴 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.

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):

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