Unstructured Files and Synthetic Data Generation in Snowflake

1.Unstructured data: retrieving files through URLs

Unstructured data does not fit a predefined data model or schema. It is often text-heavy, such as form responses and social media conversations, and it also covers images, video and audio. Industry file types such as VCF (genomics), KDF (semiconductors) and HDF5 (aeronautics) belong here as well. Snowflake can securely access these files in cloud storage (Amazon S3, Google Cloud Storage or Microsoft Azure) and share URLs to them. It can load the URLs and other file metadata into tables, and it can process the files. Both external stages and internal stages support unstructured data.

You do not parse a video into rows. You retrieve it through a URL, and Snowflake offers three kinds. A scoped URL is an encoded URL that gives temporary access to a staged file without granting any privileges on the stage. It is the recommended way for file administrators to give specific roles access, usually through a view that returns scoped URLs. Snowflake records in the query history who used a scoped URL and when. A file URL is permanent and names the database, schema, stage and file path. Custom applications send it in a GET request to the REST API, together with an authorization token. A pre-signed URL is a plain HTTPS link that works without signing in to Snowflake, which suits BI and reporting tools that display file contents.

Sources1

2.Synthetic rows from nothing: GENERATOR, SEQ, UNIFORM and RANDOM

Sometimes the source you need does not exist yet, so you make one. GENERATOR is a system-defined table function for this. It creates rows from a row count (ROWCOUNT), a time budget in seconds (TIMELIMIT), or both. With ROWCOUNT alone you get exactly that many rows. With TIMELIMIT alone, the query generates as many rows as it can in that time, so the count is not fully deterministic. With both, whichever limit is reached first wins. If you give neither, GENERATOR returns 0 rows. GENERATOR only supplies the rows. The functions in the SELECT list decide what each row contains.

SELECT seq4(), uniform(1, 10, RANDOM(12)) FROM TABLE(GENERATOR(ROWCOUNT => 10)) v ORDER BY 1;

SEQ1, SEQ2, SEQ4 and SEQ8 return monotonically increasing integers. The digit is the integer width in bytes, and the sequence wraps around after the largest number that width can hold. The values are unique and increasing, but on large data they can have gaps. When you need a fully ordered sequence with no gaps, use ROW_NUMBER. UNIFORM(min, max, gen) returns a uniformly distributed number between min and max, inclusive. If both bounds are integers, the result is an integer. If either bound is a float, the result is a float. The gen argument is the source of randomness, usually RANDOM. If you pass a constant instead, every row gets the same value. RANDOM returns a pseudo-random 64-bit integer and accepts an optional seed. Its values are not guaranteed to be unique, and rerunning a statement can give different values even with a seed. For unique values, use a sequence.

Sources234

3.Synthetic copies of real tables: GENERATE_SYNTHETIC_DATA

GENERATOR builds data from nothing. The stored procedure SNOWFLAKE.DATA_PRIVACY.GENERATE_SYNTHETIC_DATA starts from real tables instead. For each source table, it creates an output table with the same column names and data types, filled with artificial data that is statistically similar. The output has the same number of rows or fewer. Use it to test or share workloads when the original data is too sensitive. The output keeps the approximate distributions and correlations of the source, but no synthetic row links back to a source row. Synthetic data also appears in the data lineage graph.

Each non-join-key column is handled according to its type. Numbers, Booleans, dates, times and timestamps get new values of the same type, similar to the originals. A categorical string column has fewer unique values than half the row count, and its output reuses actual source values. A non-categorical string column has more unique values than half the row count. Its values are redacted unless you specify an output format with the replace option. To run joins on the synthetic data, mark every join column with join_key. Join key values stay consistent across all tables within one run. To keep them consistent across separate runs, supply a consistency_secret (this works for string columns only). Setting 'similarity_filter': True removes output rows that are too similar to the input. With the filter on, a NULL in any non-string column makes the procedure fail.

CALL SNOWFLAKE.DATA_PRIVACY.GENERATE_SYNTHETIC_DATA({
  'datasets':[
      {
        'input_table': 'CLINICAL_DB.PUBLIC.PATIENTS1',
        'output_table': 'MY_DB.PUBLIC.PATIENTS1',
        'columns': { 'patient_id': {'join_key': TRUE}, 'age':{'join_key': TRUE}}
      },
      {
        'input_table': 'CLINICAL_DB.PUBLIC.PATIENTS2',
        'output_table': 'MY_DB.PUBLIC.PATIENTS2',
        'columns': { 'patient_id': {'join_key': TRUE}, 'age':{'join_key': TRUE}}
      }
    ],
    'replace_output_tables': TRUE
});

The procedure has firm limits. One call accepts up to five inputs, and each must have at least 20 distinct rows, at most 100 columns and at most 14M rows. The inputs can be regular, temporary, dynamic or transient tables, or regular, materialized, secure or secure materialized views. External, Iceberg and hybrid tables, and streams, are not supported. A column of an unsupported data type, such as TIMESTAMP_TZ, returns NULL for every value. The Anaconda terms must also be accepted in the account.

Sources5

4.Semi-structured data: JSON and XML in VARIANT and OBJECT

Snowflake imports semi-structured data from JSON, Avro, ORC, Parquet and XML. For JSON, Avro, ORC and Parquet, each top-level complete object is loaded as a separate row. For XML, each top-level element is a separate row. Such tables usually hold a single VARIANT column. If an individual value needs more than about 128 MB of storage space, you can split the data across several columns instead. XML is queried with XMLGET, which needs an OBJECT (or a VARIANT containing an OBJECT), such as one produced by PARSE_XML or by loading XML data. It does not work directly on a VARCHAR.

Sources67

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.SEQ4() in a GENERATOR query always produces a gap-free sequence, so it can stand in for row numbers.Why is that wrong?

   SEQ functions produce unique, increasing integers, but gaps can appear on large data. Use ROW_NUMBER when you need a sequence with no gaps.

   Covered in Synthetic rows from nothing: GENERATOR, SEQ, UNIFORM and RANDOM

2. 2.RANDOM() is a good way to create unique synthetic IDs.Why is that wrong?

   RANDOM values are not guaranteed to be unique. For unique values the docs recommend a sequence.

   Covered in Synthetic rows from nothing: GENERATOR, SEQ, UNIFORM and RANDOM

3. 3.Join keys marked with join_key stay consistent every time you rerun GENERATE_SYNTHETIC_DATA.Why is that wrong?

   Without a consistency_secret, join keys are consistent only across the tables in a single run.

   Covered in Synthetic copies of real tables: GENERATE_SYNTHETIC_DATA

4. 4.A scoped URL works like a pre-signed link: anyone you pass it to can open the file.Why is that wrong?

   Only the user who generated a scoped URL can use it. Anyone holding a pre-signed URL can use it.

   Covered in Unstructured data: retrieving files through URLs

Practise it for real

Use GENERATOR with the data generation functions to build a small synthetic data set, and confirm how each function behaves.

1. 1.Run: SELECT seq4(), uniform(1, 10, RANDOM(12)) FROM TABLE(GENERATOR(ROWCOUNT => 10)) v ORDER BY 1;

   Why: GENERATOR supplies the rows, and SEQ4 and UNIFORM fill them.

   You should see: 10 rows: a SEQ4 column running from 0 to 9 and integers between 1 and 10 in the second column.

2. 2.Run: SELECT seq4(), uniform(1, 10, 42) FROM TABLE(GENERATOR(ROWCOUNT => 10)) v ORDER BY 1;

   Why: Replacing the RANDOM generator with a constant shows what UNIFORM's third argument does.

   You should see: The UNIFORM column holds the same value on every row.

3. 3.Run: SELECT seq4(), uniform(1, 10, RANDOM(12)) FROM TABLE(GENERATOR()) v ORDER BY 1;

   Why: This checks what GENERATOR does with neither ROWCOUNT nor TIMELIMIT.

   You should see: Zero rows are returned.

4. 4.Run: SELECT ROW_NUMBER() OVER (ORDER BY seq4()) FROM TABLE(generator(rowcount => 10));

   Why: ROW_NUMBER is the recommended way to get a sequence with no gaps.

   You should see: The integers 1 to 10, with no gaps.