Query DataSet Tile

Intro

The Query DataSet tile lets you write a SQL query against a Databricks DataSet as the first step of a Magic ETL DataFlow, in place of the standard Input DataSet tile. It loads only the rows your query returns and can split the result into smaller batches, which improves performance and can prevent timeout and out-of-memory errors when working with large Cloud Integrations DataSets. Although the Input DataSet tile is the most common first step in Magic ETL, other tiles can be used in its place. The SQL tile, for example, lets you write a custom SELECT statement. The Query DataSet tile goes further by also subdividing the result set into smaller loads—useful when a single load would exceed the underlying Cloud Data Warehouse (CDW) resource limits. Although the Query DataSet tile can be used with various data sources, it is strongly recommended for Databricks. Proper use of this tile can resolve errors that large DataSets cause, such as:

Socket timeout errors, which occur when a query against a large DataSet takes too long to return.
Out-of-memory errors, which occur when a single load is too large for the CDW to process, such as exceeding the maximum heap size of 16 GB.

In addition to the custom SELECT statement, the tile exposes two configuration fields:

Query partition key — the column used to split the result set into batches.
# of Loads — the number of batches to process.

Important: The column you choose as the Query partition key must not contain NULL values. Rows whose partition-key value is NULL are ignored. Only choose a column that is guaranteed to be non-null for every row.

Required Grants

You need the same grants required to author Magic ETL DataFlows on Databricks. No additional grants are required for the Query DataSet tile itself.

Access the Query DataSet Tile

On the Magic ETL authoring canvas, the Query DataSet tile is in the left Actions pane under the DataSets section.

Note: The Query DataSet tile is only available to Databricks users.

Use the Query DataSet Tile

To configure the tile:

Drag the Query DataSet tile from the left Actions pane onto the authoring canvas.
Select a Databricks DataSet from the Input DataSet menu.
Write the SELECT statement that identifies the rows you want to load into the DataFlow.
Note: The query must be a single SELECT. Common Table Expressions (CTEs) are not supported.
Configure the Query partition key by selecting the column the tile uses to split the result set into batches.
Important: The partition-key column must not contain NULL values. See Choose a Partition Key for guidance.
Configure # of Loads, the number of batches into which the result set is divided. See Size Your Loads for the recommended ceiling.

Understand How Partitioning Works

The result set from your SELECT statement is partitioned by the distinct values in your Query partition key column and then divided into the number of batches you specify in # of Loads. To avoid bottlenecks, the largest batch must be small enough to fit within your CDW’s resource limits. Batches are not guaranteed to be of equal size by row count or by data volume—the distribution depends on the partition-key column you choose. Example. Suppose the column you choose as Query partition key has 317 distinct values and you set # of Loads to 30. The first 30 distinct values form the first batch, the next 30 form the second batch, and so on—yielding 11 batches total, since 317 ÷ 30 is between 10 and 11. In practice, the number of rows per partition-key value is rarely uniform. Data is often skewed—for example, 50% of all rows might be associated with just 10% of partition-key values—so batches containing those high-row-count values are disproportionately large. Choosing the right column as partition key matters: the more evenly distributed the row count per value, the better the partitioning behaves.

Size Your Loads

For Databricks, choose the Query partition key and set # of Loads so the largest batch is no larger than 4 GB. The following procedure produces a minimum # of Loads that satisfies the 4 GB ceiling for a given partition-key candidate.

Find the total size of the table. Use Databricks’ UI or run:
DESCRIBE DETAIL <catalog>.<schema>.<table>;
Convert sizeInBytes to GB and divide by 4 GB to estimate a preliminary number of divisions:
divisions = (sizeInBytes / 2^30) / 4
This is an intermediate value—it would equal the desired # of Loads only if rows were perfectly evenly distributed across all partition-key values, which is rarely the case.
Get the total row count for the table.
Estimate the row ceiling for a single 4 GB batch:
max_rows = row_count / divisions
Pick a candidate column for the partition key. It must not contain NULL values.

Count the distinct values in the candidate column:

SELECT COUNT(DISTINCT <column>) AS distinct_count
FROM <table>;

Examine the row-count distribution across the candidate column to see how skewed the table is along that column:
SELECT <column>, COUNT(*) AS row_count FROM <table> GROUP BY <column> ORDER BY row_count DESC;

Compute a worst-case running total, the cumulative row count assuming a batch is filled from the largest partition-key values down:

WITH grouped_counts AS (
  SELECT <column>, COUNT(*) AS row_count
  FROM <table>
  GROUP BY <column>
)
SELECT
  <column>,
  row_count,
  SUM(row_count) OVER (
    ORDER BY row_count DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_row_count
FROM grouped_counts
ORDER BY row_count DESC;

For example, suppose a date column is being evaluated as a candidate partition key:

`<column>`	`row_count`	`cumulative_row_count`
2024-10-13	100	100
2024-02-21	80	180
2024-05-17	60	240
2024-08-19	50	290
2024-03-06	30	320
2024-02-10	20	340
2024-11-30	10	350

Determine how many distinct values fit under the max_rows ceiling from step 4:

WITH grouped_counts AS (
  SELECT <column>, COUNT(*) AS row_count
  FROM <table>
  GROUP BY <column>
),
ranked_counts AS (
  SELECT
    <column>,
    row_count,
    SUM(row_count) OVER (
      ORDER BY row_count DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_row_count
  FROM grouped_counts
)
SELECT COUNT(*) AS max_column_values
FROM ranked_counts
WHERE cumulative_row_count < max_rows;

Replace max_rows with the value calculated in step 4.

Compute the minimum # of Loads required to keep the largest batch under 4 GB, rounding up:
# of Loads >= distinct_count / max_column_values

The required # of Loads depends on the column you choose as the partition key. To reduce # of Loads, choose a column whose values are more uniformly distributed across the table.

Choose a Partition Key

A good partition-key column has the following characteristics:

Contains no NULL values. This is required, because rows with NULL partition-key values are skipped.
Has many distinct values, so the result set can be divided into enough batches to keep each one within CDW limits.
Has a relatively even distribution of row counts per distinct value, so batch sizes are predictable.

Highly skewed columns, where a small number of values account for most of the rows, produce very uneven batches and reduce the effectiveness of the tile.

​Intro

​Required Grants

​Access the Query DataSet Tile

​Use the Query DataSet Tile

​Understand How Partitioning Works

​Size Your Loads

​Choose a Partition Key

​Related Articles

Intro

Required Grants

Access the Query DataSet Tile

Use the Query DataSet Tile

Understand How Partitioning Works

Size Your Loads

Choose a Partition Key

Related Articles