Azure Data Analytics Skills Assessment

A Self-Hosted Integration Runtime is a lightweight agent installed within the on-premises network that establishes a secure, outbound-only connection to Azure Data Factory, allowing pipelines to read from and write to on-premises data sources without requiring the database to be directly exposed to the public internet. Attempting to connect via the public internet with no additional component would either fail or require insecurely opening the database directly to inbound traffic. Azure Bastion is for secure VM remote access, and Azure Front Door is a global content delivery and routing service, neither of which relates to Data Factory's on-premises connectivity model.

An Azure Data Factory pipeline is the top-level logical grouping of activities, such as copy activities, data flows, or stored procedure calls, that together accomplish a specific data movement or transformation task. Pipelines can be triggered on a schedule, in response to an event, or manually, and their execution history is tracked centrally for monitoring and troubleshooting. Understanding pipelines as the primary orchestration unit is foundational to working with Data Factory at all.

Dedicated SQL pools in Synapse are provisioned with a fixed amount of compute capacity, measured in Data Warehouse Units, that you pay for whether or not queries are actively running, similar to a traditional provisioned data warehouse. Serverless SQL pools, by contrast, charge based on the amount of data processed per query, with no infrastructure to provision or pay for when idle. Choosing between the two is a genuine cost and workload-pattern decision, since predictable heavy workloads often favor dedicated pools while ad-hoc, intermittent querying often favors serverless.

Hash distribution on a high-cardinality join column like customer_id spreads rows across compute nodes based on a hash of that column's value, meaning rows that will be joined together on customer_id end up co-located on the same node, minimizing expensive data movement across the network during query execution. Round-robin distribution spreads rows evenly but with no regard for join keys, often causing significant data shuffling during joins. Replicated distribution copies the entire table to every node, which works well for small dimension tables but would be wasteful and slow for a large fact table.

Databricks provides a managed Spark environment (A) that supports both batch and streaming processing, making statement B false since it is not limited to streaming alone. Delta Lake, the storage layer commonly paired with Databricks, adds ACID transaction guarantees, schema enforcement, and time travel capabilities on top of files stored in a data lake (C). Databricks notebooks are notably multi-language, letting a single workspace mix Python, SQL, Scala, and R across different cells (D), which is a major part of its appeal for collaborative data teams.

Z-Ordering is a Delta Lake data layout optimization technique that colocates related information in the same set of files, similar in spirit to clustering in other data warehouse systems, so that queries filtering on the Z-Ordered column can skip scanning files that don't contain relevant data. Running OPTIMIZE with ZORDER BY region would directly address filtered queries on that column performing poorly due to scattered data layout. Reverting to plain Parquet would lose Delta Lake's transactional and performance benefits entirely, increasing driver memory addresses a different class of problem, and disabling the transaction log would break Delta Lake's core ACID guarantees.

Linked Services establishing the actual connections to Blob Storage and Synapse must exist first (2), since datasets reference them. Datasets defining the source and destination schemas are created next (4), followed by building the pipeline itself with a Copy Data activity that references those datasets (5). The pipeline should then be tested in debug mode to catch issues before going live (3), and only after successful testing should it be published to make it active (1). Publishing before testing risks pushing a broken pipeline into a scheduled, production-facing state.

Import mode (1) loads and caches a full copy of the data inside Power BI, offering the fastest report performance since queries run against the local cache (B). DirectQuery mode (2) instead queries the source system live at render time, trading some performance for always-current data (A). A Power BI Dataset (3) is the published, reusable data model underlying one or more reports (C). The Power BI Gateway (4) securely bridges the cloud Power BI service to on-premises data sources without exposing them directly to the internet (D).

Because DirectQuery sends a live query to the source system for essentially every report interaction, multiple concurrent users generate multiple concurrent queries directly against the underlying Synapse pool, and if that pool's compute capacity is undersized for the concurrent query load, performance degrades for everyone. This is a fundamental tradeoff of DirectQuery versus Import mode, where Import mode would instead serve all users from a fast, local in-memory cache regardless of concurrent user count. Power BI does support multiple concurrent users in DirectQuery mode; the bottleneck lies in the underlying data source's capacity to handle concurrent queries, not a Power BI user limit.

Microsoft Purview provides a unified data governance solution that scans and catalogs data assets across an organization's data estate, including Synapse, Data Factory pipelines, and various databases, tracking data lineage and enabling classification of sensitive data. This gives data teams visibility into where sensitive data lives and how it flows through various transformation pipelines, which is essential for compliance and audit requirements. Without a governance tool like Purview, large organizations often lose track of where sensitive data has been copied or transformed across dozens of pipelines and services.

Delta Lake's transaction log tracks every change made to a table, which enables time travel queries that can retrieve the state of a table as of a specific timestamp or version number, all without requiring a separate manual snapshot or backup process to be maintained. This is extremely useful for auditing changes, recovering from an accidental bad write, or reproducing a report exactly as it appeared at an earlier point in time. This capability is one of the most commonly cited advantages of Delta Lake over plain Parquet files sitting in a data lake.

Partitioning data lake files by a commonly filtered dimension like date, organizing them into a folder structure such as year/month/day, lets a serverless SQL pool query with a date range filter skip reading entire folders of irrelevant data rather than scanning every file in the dataset. Converting to CSV would actually increase, not decrease, the data scanned, since CSV is uncompressed and row-based rather than columnar. Serverless SQL pools do not have a DWU setting to increase, since that concept applies to dedicated pools, and adding more columns has no bearing on reducing scanned data for a date-range query.

Row-level security (A) lets administrators restrict which specific rows a given user or role can see within the same table, useful for multi-tenant or regionally segmented data. Managed identities (B) let Data Factory and other Azure services authenticate to each other without any credentials being stored or managed manually. Azure Key Vault integration (D) allows secrets like connection strings and passwords to be securely stored and referenced by Data Factory pipelines without ever appearing in plain text. Storing passwords directly inside a pipeline's JSON definition (C) is a serious anti-pattern and security risk, making that statement false.

400 GB is 400/1000 = 0.4 TB using a standard approximation for this kind of estimate, so multiplying 0.4 TB by $5 per TB gives a query cost of $2.00. This kind of straightforward scanned-data cost calculation is exactly the type of quick math needed to estimate and control serverless query costs before they accumulate into an unexpectedly large bill. Being able to reason about cost per query, not just cost per month, helps teams catch an expensive query pattern before it becomes a systemic cost problem.

Incremental refresh in Power BI configures a dataset to only reload data within a defined recent window, such as the last few days, while leaving already-loaded historical partitions untouched, dramatically reducing the volume of data processed on each scheduled refresh compared to reloading the entire multi-billion-row history every time. Reducing refresh frequency to once a year sacrifices data freshness far more than necessary and doesn't address the underlying processing time problem. Switching to DirectQuery avoids refresh scheduling entirely but introduces live-query performance and concurrency tradeoffs, and deleting historical data destroys analytical value that could instead be preserved through partitioning and incremental refresh.