Microsoft Fabric Analytics Engineer

Analytics data is sensitive. You manage workspace and item permissions, apply sensitivity labels, and choose the narrowest access that lets each group do their work.

• Workspace roles and item-level sharing
• Granular access in warehouses and lakehouses
• Sensitivity labels and their effects
• Endorsement and certification
• Auditing access and activity
• Avoiding oversharing

You build An access model for a project, with labels applied and tested as three users.

Lakehouses and warehouses store the same data in different ways. You build both, understand Delta tables and the SQL endpoint, and decide which serves a given workload.

• Lakehouse structure: tables and files
• Delta tables and what they provide
• The SQL analytics endpoint
• Warehouse tables and T-SQL
• Cross-database queries and shortcuts
• Choosing between lakehouse and warehouse

You build A lakehouse and a warehouse for the same dataset, with a written reason for each use.

Data has to arrive before it can be modeled. You copy and orchestrate data with pipelines, shape it with dataflows, and choose between them by data volume, skill and refresh needs.

• Data pipelines: activities, triggers and parameters
• The copy activity and connectors
• Dataflows Gen2 and Power Query
• Incremental loading patterns
• Mirroring and shortcuts as alternatives to copying
• Choosing between pipelines, dataflows and notebooks

You build A pipeline and a dataflow that load source data into a lakehouse on a schedule.

Analytics models run best on a star schema. You clean and transform data with SQL, dataflows and notebooks, then shape it into facts and dimensions that reports can query quickly.

• Cleaning: types, duplicates and missing values
• Transforming with T-SQL and notebooks
• Facts, dimensions and grain
• Surrogate keys and slowly changing dimensions
• Date dimensions
• Denormalizing for query speed

You build A star schema built from raw data, with its grain and keys documented.

Slow loads and slow queries often trace to how data is stored. You tune loading, partition and compact tables, and measure the effect of each change on refresh and query time.

• Measuring load time and capacity use
• Small files and table maintenance
• Partitioning and file layout
• Incremental and staged loads
• Parallelism and pipeline design
• Monitoring workloads

You build A load process tuned against a baseline, with timings before and after.

Analysts ask questions in different languages. You query lakehouses and warehouses with SQL, explore time-stamped data with KQL, and profile data to answer business questions.

• SELECT, joins and aggregations over Fabric data
• Window functions for analysis
• KQL basics: where, summarize and project
• Time-series and log queries in KQL
• Profiling and validating data
• Choosing SQL or KQL for a question

You build A set of SQL and KQL queries that answer business questions and check data quality.

A semantic model is the shared meaning of the data. You design models, relationships and measures, and choose between Import, DirectQuery and Direct Lake for each model.

• Tables, relationships and cardinality
• Import, DirectQuery and composite models
• Direct Lake: how it works and its limits
• Fallback behavior and how to avoid it
• Default semantic models and custom models
• Reviewing a model against a star schema

You build A semantic model over a star schema in Direct Lake mode, with its fallback behavior tested.

DAX turns a model into answers. You write measures and calculated columns, understand row and filter context, and use CALCULATE to change what a measure sees.

• Measures compared with calculated columns
• Row context and filter context
• CALCULATE and filter modification
• Iterators and aggregators
• Variables for clarity and speed
• Debugging a wrong number

You build A set of measures with tests that confirm each against known totals.

Business questions are usually about time and comparison. You write period-to-date, year-over-year and rolling measures, build ratios and rankings, and handle blanks and edge cases.

• Date tables and marking them
• Year-to-date, prior period and rolling windows
• Ratios, percentages of total and ranking
• Calculation groups for reuse
• Handling blanks, ties and partial periods
• Testing measures against edge cases

You build A measure library covering year-over-year, rolling and ranked views, with edge case tests.

Different readers should see different rows of the same model. You write row-level security rules, assign them to roles, and secure tables and columns, then test them as each kind of user.

• Static and dynamic row-level security
• Roles and membership
• Object-level security for tables and columns
• Security in Direct Lake and SQL endpoints
• Testing security as another user
• Common mistakes that leak data

You build A model with dynamic row-level security, validated by viewing it as three different users.

Large models need design and care. You configure incremental refresh, tune model size and query speed, and use external tools to find the measures and relationships that cost the most.

• Incremental refresh and its configuration
• Model size, cardinality and compression
• Aggregations and summary tables
• Finding slow queries and measures
• External tools for analysis and optimization
• Refresh scheduling and monitoring

You build A large model with incremental refresh and a performance report that lists the changes made.

Analytics assets deserve the discipline of software. You connect workspaces to Git, move content through deployment pipelines, and manage the changes teams make to shared items.

• Git integration for workspaces
• Branching and conflict resolution for analytics items
• Deployment pipelines and stages
• Deployment rules and parameters
• Promoting and rolling back changes
• Release and ownership practices

You build A workspace connected to Git and promoted through development, test and production stages.

Build one analytics solution from source to report. You prepare the data, build the model and measures, secure it, and ship it through environments, then check the answers against the source.

• Designing from business questions
• Preparing data into a star schema
• Building the semantic model and measures
• Securing and optimizing it
• Deploying and validating the results

You build A deployed analytics solution with data prep, a semantic model, security and a validation report.