Microsoft Certified: Fabric Analytics Engineer Associate flashcards

The Copy Data activity in a Data Pipeline supports efficient incremental loading of files from sources like ADLS Gen2. This is achieved by configuring the 'File filter' option with 'Last modified date' and dynamically setting a 'Start time (UTC)' to process only new or updated files since the last run.

• Filters files based on their last modification timestamp.
• Uses 'Start time (UTC)' to define the incremental window.
• Prevents reprocessing of unchanged files.
• Efficient for ingesting new or updated data in file-based sources.

Memory trick: Copy Data filters files by their last modified time.

The process of efficiently adding only new or changed data from files to an existing destination table using Dataflows Gen2, typically leveraging Delta Lake capabilities.

• Achieved by writing to a Delta table.
• Uses 'Append' write mode for new records.
• Avoids reprocessing or duplicating historical data.

Memory trick: Append to Delta is like adding new pages to an existing, organized book.

Data Pipelines' Copy Data activity in Microsoft Fabric enables scheduled, scalable data movement between various sources and destinations, including on-premises databases.

• Supports a wide range of connectors including on-premises via gateway.
• Ideal for scheduled batch data ingestion.
• Provides monitoring and retry capabilities.

Memory trick: Connect, Schedule, Move: The Fabric pipeline for data flow.

A visual data transformation tool integrated into Dataflows Gen2 and other Microsoft products, enabling users to clean, shape, and combine data.

• Uses a M language behind the scenes.
• Provides a wide array of transformation functions.
• Records steps for reproducibility.

Memory trick: Power Query: The artist's studio for data shaping.

Settings that control how a Spark DataFrame writes data to a destination when the table/path already exists.

• append: Adds new data to existing.
• overwrite: Replaces existing data.
• ignore: Does nothing if exists.
• errorifexists: Fails if exists.

Memory trick: Append mode adds to the story, never erasing history.

A real-time analytics capability in Microsoft Fabric for ingesting, transforming, and routing streaming data from various sources.

• Low-latency processing.
• Supports Event Hubs, Kafka, custom apps.
• Integrates with other Fabric items like Lakehouse.

Memory trick: Eventstream: The river for your real-time data.

The 'Expand Column' transformation in Power Query flattens structured data (lists, records, tables) within a column into new rows or columns.

• Used for nested JSON or structured data.
• Creates new rows for each item in a nested list/array.
• Crucial for transforming hierarchical data into tabular format.

Memory trick: Reshape data like clay, molding it to your will.

A setting in Power Query's CSV source connector that specifies the character used to separate fields (columns) within a CSV file.

• Defaults to comma (`,`).
• Must be configured for non-standard delimiters (e.g., semicolon, tab).
• Incorrect setting leads to data appearing in a single column.

Memory trick: The delimiter is the 'road sign' telling Power Query where columns divide.

A Microsoft Fabric component for orchestrating and automating data movement and transformation activities.

• Supports large-scale data transfer.
• Offers various data source and sink connectors.
• Can be scheduled and monitored.

Memory trick: A pipeline moves mountains of data, steadily and surely.

A Power Query transformation that combines two queries (tables) into a single query based on matching values in specified columns, similar to a SQL JOIN.

• Used for horizontal combination of data.
• Supports various join kinds (inner, left outer, right outer, full outer).
• Essential for denormalization and bringing related data together.

Memory trick: Merge is like two rivers joining, combining their waters into one flow.

PySpark's `functions` module provides efficient methods like `trim`, `regexp_replace`, and `lower` for cleaning string columns in DataFrames.

• Use `F.trim()` for leading/trailing whitespace.
• Use `F.regexp_replace()` for complex pattern-based cleaning (e.g., multiple spaces, special characters).
• Operations are vectorized for performance on large datasets.

Memory trick: Clean strings like a chef preps ingredients: trim, replace, standardize.

The practice of securely storing and accessing sensitive authentication information, such as SSH private keys, for external SFTP connections in Microsoft Fabric Data Pipelines.

• Azure Key Vault is the recommended secure store.
• Linked services reference Key Vault secrets.
• Prevents hardcoding or exposing credentials.

Memory trick: Key Vault is the vault for your SSH key, unlocking secure data transfer.

Data Pipelines in Microsoft Fabric provide orchestration capabilities, including scheduling and monitoring, for ingesting data from various sources, particularly on-premises databases via an On-premises data gateway, using activities like 'Copy Data'.

• Orchestrates data movement and transformation.
• Supports scheduling and monitoring of activities.
• Uses Copy Data activity for efficient data transfer.
• Connects to on-premises sources via On-premises data gateway.

Memory trick: On-premises data moves through a pipeline to the Lakehouse.

Using PySpark DataFrame operations to enforce data types and handle missing values during data transformation.

• `.withColumn()` for type casting and new column creation.
• `.na.drop()` for removing rows with nulls.
• `.na.fill()` for imputing nulls.

Memory trick: With Spark, we polish columns and drop dirty rows.

A software agent that connects Microsoft cloud services with on-premises data sources, enabling secure data transfer.

• Acts as a bridge.
• Encrypts data in transit.
• Required for Fabric to access private networks.

Memory trick: The gateway is the secure bridge to your on-premises data.

A real-time data streaming and processing platform in Microsoft Fabric that enables ingestion, transformation, and routing of high-volume event data with low latency.

• Designed for real-time analytics scenarios.
• Supports various event sources and destinations.
• Integrated with other Fabric components (KQL, Lakehouse).

Memory trick: Eventstream is like a mighty river, carrying your real-time data swiftly to its destination.

Leveraging PySpark in Spark notebooks for highly scalable and customizable Extract, Transform, Load (ETL) operations, including complex parsing and external library integration.

• Scales to petabytes of data.
• Supports Python, Scala, R, SQL.
• Ideal for machine learning and complex analytical transformations.

Memory trick: PySpark's code unleashes powerful insights from massive data.

Eventstream in Microsoft Fabric is a fully managed, real-time data streaming platform designed to ingest, process, and route continuous streams of data from various sources, including IoT devices, for immediate analytical consumption.

• Dedicated to real-time data streaming.
• Ingests continuous streams (e.g., IoT, logs).
• Provides initial processing and routing capabilities.
• Outputs to Lakehouse, KQL Database, or other destinations.

Memory trick: Eventstream catches real-time waves for analytics.

Combining Data Pipelines for orchestration and scheduling with Dataflows Gen2 for low-code data ingestion and transformation.

• Pipelines manage flow, Dataflows handle ETL.
• Enables continuous, event-driven processing.
• Leverages low-code for transformations while ensuring robust orchestration.

Memory trick: The pipeline guides the dataflow, transforming it without a single line of code.

Interactive development environments within Microsoft Fabric for data processing using Apache Spark with languages like PySpark, Scala, or C#.

• Offers high flexibility for complex transformations.
• Scalable for large datasets.
• Supports various data formats and operations.

Memory trick: Spark code sculpts raw data into analyzed perfection.

For highly performant and scalable complex transformations on large datasets in Spark, prioritizing built-in Spark SQL functions and DataFrame API operations is crucial. These leverage Spark's Catalyst optimizer and distributed execution model for efficiency.

• Built-in functions are highly optimized.
• DataFrame API allows declarative transformations.
• Leverages Spark's Catalyst optimizer for execution plans.
• Executed in a distributed manner for scalability.

Memory trick: Spark's DataFrame API powers optimal transformations.

Settings within the Copy Data activity that define how errors are handled during data transfer, ensuring resilience and data integrity.

• Can skip incompatible rows.
• Can log inconsistent data.
• Prevents pipeline failure due to minor data issues.

Memory trick: Fault tolerance is the pipeline's safety net.

A low-code data integration tool in Microsoft Fabric for ingesting, transforming, and preparing data from various sources.

• Uses Power Query for transformations.
• Supports a wide range of data sources.
• Can output directly to Lakehouse tables.

Memory trick: Flowing data, visually transformed, ready for the Lakehouse.

A feature in Power Query that simplifies combining multiple files with the same schema from a folder into a single dataset.

• Automates the creation of a transformation function.
• Applies transformations to all files consistently.
• Efficient for folder-based data sources.

Memory trick: Folder of files becomes one table with a single click.

Microsoft Fabric Eventstream is a real-time analytics component for ingesting, transforming, and routing high-volume streaming data with low latency.

• Designed for real-time data ingestion (e.g., IoT, logs).
• Supports various streaming sources and destinations.
• Enables real-time analytics and anomaly detection.

Memory trick: Data flows into Fabric, either a calm lake or a rushing stream.

A built-in mechanism in Microsoft Fabric Data Pipelines (and Azure Data Factory) to automatically re-execute failed activities a specified number of times with a defined delay between attempts.

• Configurable per activity or pipeline.
• Includes 'Retry count' and 'Retry interval'.
• Essential for fault tolerance and robust data ingestion.

Memory trick: Don't just fail; retry with a policy, like a persistent mail carrier.

Dataflows Gen2 leverage Power Query to connect to and ingest data from various sources, including web APIs and semi-structured formats like JSON, enabling visual transformations.

• Uses Power Query Editor for visual ETL.
• Supports a wide range of connectors, including Web API.
• Ideal for ingesting semi-structured data and applying basic to intermediate transformations.
• Output can be loaded directly into a Lakehouse or Data Warehouse.

Memory trick: Web API data flows visually to the Lakehouse.

The 'Wildcard file path' setting in a Data Pipeline's Copy Data activity allows dynamic selection of source files based on patterns, often incorporating dynamic expressions.

• Enables filtering files by name, date, or other patterns.
• Supports system variables like 'utcNow()' for dynamic dates.
• Efficient for ingesting a subset of files from a folder.

Memory trick: Pipeline flows, date-filtered, like a river finding its daily path.

Spark Notebooks in Microsoft Fabric, utilizing PySpark or Scala, are the preferred tool for performing complex, large-scale ETL (Extract, Transform, Load) operations on multi-terabyte datasets, offering distributed processing, custom logic, and high performance.

• Handles multi-terabyte datasets efficiently.
• Supports custom code (PySpark, Scala).
• Leverages distributed computing for performance.
• Ideal for complex cleansing, enrichment, and aggregation.

Memory trick: Spark handles complex ETL at scale.

A Spark SQL hint that forces the optimizer to use a broadcast hash join by broadcasting the specified table to all executor nodes.

• Ideal for joining a small table with a large table.
• Avoids data shuffling for the larger table.
• Can significantly improve join performance if the broadcasted table fits in executor memory.

Memory trick: Broadcast the small message to all for a fast meeting.

Context transition in DAX converts row context into filter context during calculations, often implicitly by `CALCULATE` or explicitly by iterating functions.

• Crucial for complex filtering logic.
• Implicitly occurs with `CALCULATE`.
• Allows filters derived from one period/set to be applied to another.

Memory trick: Filter context shifts and turns, previous month's products, current month learns.

A calculation that computes the running total of a numeric column within a defined window (partition and order) in Spark SQL.

• Uses the `SUM()` aggregate function with an `OVER()` clause.
• `PARTITION BY` defines the groups for which the sum restarts.
• `ORDER BY` defines the sequence in which the sum accumulates within each group.

Memory trick: Windows frame how you view the data.

A PySpark window function clause that defines a window frame based on a range of values relative to the current row, commonly used for time-based windows when `orderBy` is on a numeric or timestamp column.

• Requires `orderBy` on a numeric or timestamp column.
• Offsets specify values relative to the current row (e.g., seconds for timestamp).
• Used for fixed-duration rolling windows, like 'last 30 minutes'.

Memory trick: Range by time, not just by row count.

Inactive relationships in a semantic model allow a single dimension table to relate to multiple date columns in fact tables, activated as needed by DAX functions.

• Used when a dimension relates to multiple columns in one or more tables.
• Activated using `USERELATIONSHIP()` in DAX.
• Prevents ambiguity in filter propagation.

Memory trick: Dates flow, one active, others wait, DAX then chooses to activate.

The Power Query Editor in Microsoft Fabric is a visual tool used for connecting to diverse data sources, performing data cleansing, transformation, and shaping operations before loading data into a semantic model.

• Supports hundreds of data connectors.
• Uses the M language for transformations.
• Essential for data preparation in semantic models.

Memory trick: Connect, transform, load – Power Query's the data's road.

A visual display of the distribution of a dataset through its quartiles, median, and potential outliers, often used for comparing distributions across different groups.

• Shows five-number summary: minimum, first quartile (Q1), median (Q2), third quartile (Q3), maximum.
• Whiskers extend to the lowest/highest data points within 1.5 times the interquartile range (IQR).
• Points beyond the whiskers are considered outliers.

Memory trick: Boxes show the spread, whiskers show the reach.

Referential integrity in a semantic model ensures that every foreign key value in a child (many) table has a matching primary key value in the parent (one) table.

• Crucial for accurate relationship behavior.
• Assuming it can optimize query performance.
• Disabling it is necessary when orphaned rows exist to prevent errors.

Memory trick: Cardinality counts, integrity checks, orphaned rows need careful effects.

Combining Object-Level Security (OLS) and Row-Level Security (RLS) provides comprehensive data access control by restricting both entire objects (tables/columns) and specific data rows based on user roles.

• OLS hides sensitive objects (tables, columns, measures).
• RLS filters sensitive data at the row level.
• Together they offer robust, granular security.

Memory trick: Objects hide, rows filter, together they secure, no data can slip or deter.

Microsoft Fabric allows setting individual refresh schedules for tables within a semantic model, enabling different data freshness requirements for different data sources or components.

• Optimizes refresh cycles and resource consumption.
• Ensures specific tables are refreshed at their required frequency.
• Maintains a single, cohesive semantic model.

Memory trick: Each table has its own clock, sales tick fast, inventory stock.

This policy within incremental refresh allows recent data partitions to be imported for performance, while older, less-frequently accessed partitions remain in DirectQuery mode, reducing refresh times and memory usage.

• Optimizes refresh by only importing new data.
• Reduces memory consumption by keeping historical data in DirectQuery.
• Ensures all historical data is available for compliance without full import.

Memory trick: Recent data imports, old data waits, DirectQuery for history, no data fates.

The 'Allow workspace admins to assign users to workspace roles' tenant setting enables workspace administrators to manage user permissions within their own workspaces.

• Delegates user management to workspace admins.
• Does not require tenant-level admin privileges.
• Enhances decentralized governance and reduces IT overhead.

Memory trick: Tenant settings delegate power, like passing a baton to workspace admins.

A data ingestion strategy that only processes and loads new or changed data from the source system since the last successful load.

• Optimizes performance by reducing data volume.
• Reduces resource consumption (compute, storage, network).
• Requires a mechanism to identify new/changed records (e.g., timestamp, version, change tracking).

Memory trick: Only new data, like a detective, finds what's changed.