# What is DataPancake?
{% embed url="" %}
DataPancake is a complete solution for semi-structured data. You can now Pancake your data™ - Flatten, normalize, enrich, and secure complex JSON/XML with a Cortex AI Data Dictionary Builder and a Cortex Analyst Semantic Model Generator - all securely inside of Snowflake.
***
### DataPancake's Core Features
🔍 Schema Discovery
* Recursively scans 100% of your semi-structured data (JSON, XML, Avro, Parquet, and ORC) to discover all attributes including nested arrays, objects, and every polymorphic version of each attribute.
* Detects all 7 polymorphic data type variations (4 primitives, 2 types of arrays, and objects).
* Identifies escaped JSON within string fields.
* Scanning and discovery benefits from Snowflake's vertical scaling.
* Infers Snowflake destination data types including correct datetime formats for accurate type conversion.
🛠️ Pipeline Designer
* Enables users to customize how each pipeline SQL DDL will be generated.
* Configure foreign key relationships for nested arrays.
* Apply column-level transformation logic during the materialization process.
* Create virtual attributes for derived fields or semantic model metrics and filters.
* Configure row access and column masking policies integration.
* Configure semantic layer of views including additional column-level transformations.
✨ SQL Code Generation
* Generates SQL DDL code needed to create relational dynamic tables and policy-infused views in Snowflake based on your configured attribute metadata.
* Code-generated Snowflake Dynamic Table SQL DDL using DataPancake ITDCs (Immutable Typed Derived Columns) to create technical-debt-free pipelines.
* Reflects configured transformations, foreign keys, and virtual attributes allowing for post-normalized table joins.
* Code-generated views selecting data from normalized dynamic tables that incorporate row-access and column-masking security policies and additional column-level transformations.
* Code-generated streams, tasks, and tables to track dynamic table metadata including insert and last updated datetime.
🚨 Schema Drift Monitoring
* Continuously monitors and alerts you when your semi-structured data source schema changes.
* Detects schema drift in semi-structured data sources like JSON and XML.
* Flags changes in data types, structure, and new attributes.
* Alerts users to configure newly discovered attributes and regenerate pipeline code.
* Optionally generates updated pipeline SQL DDL upon schema change detection.
📚 Data Dictionary Builder
* Creates a comprehensive data dictionary that includes definitions, synonyms, and sample values for every attribute with integration to the Semantic Model Generator for Cortex Analyst.
* Uses your preferred LLM to generate definitions, synonyms, and sample values.
* Extends DataPancake’s system prompt with your own custom context for greater clarity and improved responses.
* Generates descriptions for the data source, nested arrays, and attributes.
🧠 Cortex Analyst Semantic Model Generator
* Generates Cortex Analyst–ready YAML files that define your complete semantic model.
* Automatically includes relationship metadata based on selected columns.
* Integrated with the Pipeline Designer and Data Dictionary Builder.
* Configures custom metrics, facts, and filters through virtual attributes, then adds verified queries and custom instructions.
# Understanding the Challenges of Semi-Structured Data
DataPancake is designed to convert semi-structured hierarchical data into relational data using a deep recursive scanning process, user configuration, and then the generating of SQL statements to create extracted and flattened out Dynamic Tables. Also known as, Pancaking your semi-structured data.
The following sections will give you a brief overview of the problems DataPancake is designed to solve, as well as information about the problems themselves to help you understand them in more depth.
Here are some of the problems that DataPancake directly addresses:
* Polymorphic Data (attributes using multiple data types)
* Stringified, Embedded or Escaped JSON Data (JSON Blob inside a string attribute)
* Schema Discovery (Deeply nested objects and arrays)
* Parsing & Querying Semi-Structured Data
* Schema Evolution/Drift
***
### 🔀 **Polymorphic Data**
**Polymorphic data** refers to attributes in a semi-structured document that have different structures which could consist of different attributes from one document to another or identical attributes that have different data types from one document to another. This can occur when documents are receiving raw data from multiple sources, or changes are made to the underlying schema and historical data is not updated. It can easily result in queries which miss large portions of raw data or impact downstream features and analytics.
The following are examples of polymorphic attributes found in a single JSON document. They are intended to provide a basic understanding of polymorphism in data, but are by no means exhaustive:
**Example #1:**
> *property\_type (string)*\
> *address (string)*
```
{
"_id": "re-8594abcd-3217-4889-ef01-23b45cd6789f",
"property_id": "PR-5689",
"property_type": "Mixed-use",
"address": "123 Main St"
}
```
> *property\_type (object)*\
> *address (object array)*
```
{
"_id": "re-8594efgh-3217-4889-ef01-23b45cd6789f",
"property_id": "PR-5690",
"property_type": {"usage":"Commercial", "sq_ft":20000},
"address": [{
"street": "1234 High Tower Rd",
"city": "Skyline",
"state": "NY",
"zip_code": "10101"
}]
}
```
**Example #2:**
> *amenities (array that contains both primitive and object elements)*
```
"amenities": [
"Gym",
"Rooftop Deck",
{
"name": "Conference Rooms",
"availability": "Reservable",
"capacity": 20
}
]
```
Issues like the examples above are very common with a flexible format like JSON. DataPancake is designed to help you avoid the issues which result from that flexibility, resulting in poor data quality.
***
### 🧶 **Embedded/Stringified/Escaped JSON**
JSON data can be stored as an escaped string inside an attribute with a string data type. This type of JSON is especially difficult to parse because the schema may be unknown and have its own level of depth and polymorphic state. There is no limit to the level of recursive escaped JSON contained inside a single string attribute.
**Example #1:**
```
{
"customerData": {
"customers": [
{
"customerID": "C011",
"loyaltyProgram": {
"enrollmentDate": "{\"datetime_iso\": \"2021-06-01 00:00:00\"}",
"membershipID": "M011",
"membershipLevel": "Gold",
"points": 5000,
"rewards": "[{\"rewardID\": \"R017\", \"description\": \"10% off next purchase\", \"expirationDate\": \"{\\"datetime_iso\\": \\"2023-12-31 23:59:59\\"}\"},
{\"rewardID\": \"R018\", \"description\": \"Free shipping\", \"expirationDate\": \"{\\"datetime_us\\": \\"12/31/2023 11:59 PM\\"}\"}
]"
}
```
***
### 🔍 **Schema Discovery**
When parsing JSON data, it is frequently difficult to know with certainty that the entire schema has been fully documented. Schema-less structures can change at any time, and hierarchical structures like JSON have no limit to the depth of nested objects and arrays. The same things which make JSON a desirable storage format also create challenges for organizations hoping to leverage that data for applications and analytics.
***
### 🧮 **Parsing and Querying Semi-Structured Data**
Snowflake SQL provides excellent support for parsing and processing data, but complex JSON structures require equally complex queries if users want to unpack, unnest, flatten, and relate the data found in them. That level of data complexity and SQL can be difficult to test and troubleshoot. If attributes are not notated precisely, you will receive a null value, and can easily exclude data from your result set if you are not familiar enough with the underlying data to know the value should not be null.
***
### 🌪️ **Schema Evolution (or Schema Drift)**
The structure or schema of JSON can change easily and often accidentally, making it difficult to track and process the changes to upstream data sources. Schema drift can create sudden problems as downstream pipelines are impacted or even broken. This causes delays to reporting, analytics, and even product rollouts when features depend on data from the JSON source.
Applications that are built on document databases which use Snowflake as their analytics platform and repository, suffer as a result of these uncommunicated and often overlooked schema changes. Worse, if the schema has not changed but data has inadvertently become polymorphic due to data migration errors, these types of adverse impacts can occur without something as obvious as a broken dashboard to call attention to the issue.
# Install & Setup
This guide will walk you through installing the free version of DataPancake, upgrading it to the paid version, and completing necessary admin setup steps.
{% hint style="info" %}
**Account Admin** privileges are required for this process.
{% endhint %}
{% stepper %}
{% step %}
### Install & Upgrade DataPancake
1. [Install DataPancake](/guides/miscellaneous-guides/how-to-install-datapancake) from the Snowflake marketplace
2. [Upgrade DataPancake from the free trial](/guides/miscellaneous-guides/how-to-upgrade-datapancake-from-the-free-trial) to use all of its features\
*Note: While DataPancake is free to install, core features such as pipeline designing and generating code can only be done from the upgraded (purchased) version.*
{% endstep %}
{% step %}
### Setup DataPancake
1. Build a script to [grant account level privileges to DataPancake](/guides/how-to-guides-script-builder/admin-guides/how-to-grant-account-level-privileges-to-datapancake).
2. [Configure available warehouses for DataPancake](/guides/how-to-guides-script-builder/admin-guides/how-to-configure-available-warehouses-in-datapancake)
3. [Grant role access as needed to DataPancake](/guides/how-to-guides-script-builder/admin-guides/how-to-manage-access-to-datapancake)
{% endstep %}
{% endstepper %}
{% hint style="success" %}
DataPancake is now ready to use! Check out the "[Data Source Quick Start](/getting-started/data-source-quick-start)" to walk through creating your first simple data source.
{% endhint %}
# Data Source Quick Start
This quick start will help you implement your first DataPancake pipeline for semi-structured data.
{% hint style="info" %}
**Prerequisite:** Complete the [Install & Setup](/getting-started/install-and-setup) process
{% endhint %}
{% stepper %}
{% step %}
### Create, Scan, & Generate Code for your First Data Source
1. [Load your data into Snowflake](https://docs.snowflake.com/en/guides-overview-loading-data) or access your data via [iceberg tables](https://docs.snowflake.com/en/user-guide/tables-iceberg).
2. Use the [single data source script builder](/guides/how-to-guides-script-builder/data-source-guides/how-to-create-a-single-datapancake-data-source-script-builder) to create and scan your first data source.
3. Monitor the progress of the scan on the "Scans in Process" page. The scanning process will often take several minutes depending on the size of your data and the warehouse used.
{% hint style="success" %}
DataPancake recursively scans your *entire* data source unless you specifically limit the amount of data scanned.
{% endhint %}
{% endstep %}
{% step %}
### Deploy Dynamic Tables and Views (if generated)
1. Navigate to the "Generated SQL" page and select your data source.
2. Copy the generated SQL DDL.
3. In a new Snowflake worksheet or workspace, paste and execute all of the statements to deploy your dynamic tables and views (if generated).
{% hint style="success" %}
You're now ready to start selecting data from your deployed objects!
{% endhint %}
{% endstep %}
{% endstepper %}
## Next Steps
Schema Shaping
Pipline Design
Scan Configuration
Code Generation & Deployment Options
Data Dictionary Builder
Semantic Model Generator
JSON Tutorial
# JSON Tutorial
This detailed tutorial will walk you through creating and configuring a JSON DataPancake pipeline using an example dataset.
{% hint style="info" %}
**How to use this tutorial:**
Reference the screenshots and code blocks for the input values you should use as you follow along.
You can open the linked guides in new tabs to easily return to this tutorial for the next steps in the process.
{% endhint %}
{% stepper %}
{% step %}
### **Deploy Data Objects & Load Data**
Create the table and [load the data](https://docs.snowflake.com/en/guides-overview-loading-data) which can be downloaded here:
{% file src="/files/lRp1oSE5rVD9kay13sYp" %}
Additionally, copy and deploy the [UDFs and security policies](/guides/miscellaneous-guides/json-tutorial-udfs-and-security-policies).
Note: The Enterprise version of DataPancake is required to use the security policies used in this tutorial.
{% endstep %}
{% step %}
### **Create the Data Source**
Use the [Script Builder to generate the initial source](/guides/how-to-guides-script-builder/data-source-guides/how-to-create-a-single-datapancake-data-source-script-builder).
Make sure to check "Start Scan", this will discover the raw schema and attributes.
{% endstep %}
{% step %}
### **Finalize the Schema & Rescan**
[Add schema transformations](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-modify-the-schema).
[Re-scan the data source](/guides/how-to-guides-ui/how-to-scan-the-data-source) with **reset attributes = true**.
Note: After manual metadata edits, reset attributes is no longer available.
{% endstep %}
{% step %}
### **Define Virtual Attributes**
[Create the new virtual attributes](/guides/how-to-guides-ui/pipeline-designer/how-to-create-virtual-attributes)
This is where you can add surrogate primary keys, create calculated fields, and add ordering attributes for dedupe logic.
{% endstep %}
{% step %}
### **Configure Column Materialization**
[Add column materialization rules](/guides/how-to-guides-ui/pipeline-designer/how-to-add-column-materialization-rules) in the dynamic table layer and the secure view layer.
{% endstep %}
{% step %}
### **Apply Column-Level Schema Transformations**
[Apply column-level schema transformations](/guides/how-to-guides-ui/pipeline-designer/how-to-apply-column-level-schema-transformations).
You can also normalize data types and formats, and perform schema consolidation at the field level.
{% endstep %}
{% step %}
### **Merge Polymorphic Versions**
[Merge string, float, and integer variants](/guides/how-to-guides-ui/pipeline-designer/how-to-merge-polymorphic-attribute-versions) into unified attributes.
{% endstep %}
{% step %}
### **Add Aliases**
[Add user-friendly alias names for columns and arrays](/guides/how-to-guides-ui/pipeline-designer/how-to-add-aliases) where needed.
{% endstep %}
{% step %}
### **Configure Array Relationships \[Coming Soon]**
Define parent/child relationships
Note: this is required for semantic model generation.
{% endstep %}
{% step %}
### **Add Security Policies**
[Configure security policies](/guides/how-to-guides-ui/pipeline-designer/how-to-configure-security-policies) such as row-level access rules, and attribute-level security tags.
{% endstep %}
{% step %}
### **Configure Foreign Keys**
Add relationships between flattened entities by [configuring foreign keys](/guides/how-to-guides-ui/pipeline-designer/how-to-configure-foreign-keys).
{% endstep %}
{% step %}
### **Apply Final Metadata Modifications \[Coming Soon]**
{% endstep %}
{% step %}
### **Generate Code**
[Generate the dynamic SQL statements](/guides/how-to-guides-ui/pipeline-designer/how-to-generate-dynamic-sql-statement-code) using your latest configurations.
{% endstep %}
{% step %}
### **Deploy and Validate**
Review and [deploy the generated code](/guides/how-to-guides-ui/how-to-deploy-and-validate-generated-code).
Make sure to perform data quality checks and validate security enforcement.
{% endstep %}
{% endstepper %}
### Pipeline Maintenance
Repeat steps 3 - 13 as many times as needed.
# How to Guides (UI)
# Creating & Configuring Data Sources
# How to Create a Data Source for Data Streamed from Kafka
Create a data source for data streamed from Kafka in DataPancake.
### 1. Grant DataPancake access to the database, schema, and table
Open a new Snowflake SQL file and run the necessary statements for the database, schema, and table which contain the Kafka data. For example:
GRANT USAGE ON DATABASE DB_NAME TO APPLICATION DATAPANCAKE;
GRANT USAGE ON SCHEMA DB_NAME.SCHEMA_NAME TO APPLICATION DATAPANCAKE;
GRANT REFERENCES, SELECT ON TABLE DB_NAME.SCHEMA_NAME.TABLE_NAME TO APPLICATION DATAPANCAKE;
{% hint style="info" %}
**Imported databases (e.g. Confluent)**: If your Kafka data lives in an imported database, run:
`GRANT IMPORTED PRIVILEGES ON DATABASE DB_NAME TO APPLICATION DATAPANCAKE;`
Use `GRANT IMPORTED PRIVILEGES` on the imported database instead of granting individual privileges on database, schema, and table.
Need help? Read more about [Snowflake shared databases](https://docs.snowflake.com/en/user-guide/data-sharing-gs)
{% endhint %}
### 2. In DataPancake, navigate to the Data Sources page
### 3. Enter a name for the new data source
### 4. Select the data source type
### 5. (Optional) Enter data source tags
These are simply used for filtering / searching. For example: `dev`, `prod`, `api`, `csv`
### 6. Select "Kafka" for the source stream platform
### 7. (Optional) Deduplicate Messages
If checked, two dynamic tables will be produced for the root attributes in the semi-structured data source. The first dynamic table will be used to flatten the root attributes. The second dynamic table will be used to filter the flattened rows using a window function (configured in the next step) to produce the most recent message for each primary key.
### 8. Enter the Deduplication SQL Expression
{% hint style="info" %}
### Required only if "Deduplicate Messages" is toggled on
{% endhint %}
The sql expression used to deduplicate the source stream messages based on a primary key and sort order to produce the most recent message. This value is required if the SQL Code Generation feature is selected and the Deduplicate Messages option is enabled.
### 9. Select the Object Type
### 10. Select the Column Data Type
### 12. Select the Format Type
### 13. Select the Database
### 14. Select the Schema
### 15. Select the Object Name
### 16. Enter the Column Name
This column name can be a sql expression that refers to a specific path of the semi-structured data source. See the example below.
### 17. Click 'Enable Feature Selection'
### 18. Select "SQL Code Generation"
### 19. Select the Output Object Type
### 20. Configure additional settings as needed
[How to configure materialization object & dynamic table settings](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-output-object-and-dynamic-table-settings)\
[How to configure the secure view](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-the-secure-semantic-view-layer)\
How to configure schema consolidation (WIP)\
[How to configure schema filters](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-schema-filters)
### 21. Save the data source
The save button is near the bottom of the page.
### 22. Verify the save completed successfully.
# How to Configure Materialization & Dynamic Table Settings
Configure Output Object Settings, Dynamic Table Settings, and Dynamic Table Metadata Settings for a data source.
## Output Object Settings
These settings are used to configure the type of output objects generated, whether to use case sensitivity in the code generation process, whether to include stream message metadata (if applicable), and the deployment location.
### 1. Ensure the Output Object Type is set to "Dynamic Table"
If this is for a one-time materilization process, you can choose "Table" instead.
### 2. Enter the Root Output Object Name
Required only if you plan to generate dynamic tables for this data source.
This name will be used as the root dynamic table name and as a prefix for all other nested Dynamic Tables created for array attributes.
You do not need to include an underscore at the end of this name. An underscore will be added automatically for all nested Dynamic Tables.
### 3. (Optional) Enter the Root Output Object Select Prefix Expression
This is a SQL expression added to the beginning of the select clause of the root dynamic table.
This expression is used to include one or more columns (not the source column to be flattened) from the source database object by adding the expression to the beginning of the root level dynamic table select clause.
For multiple columns use a comma separated list. Alias all columns in this list using the 'as' keyword.
### 4. (Optional) Enter the name of the database the Dynamic Tables will be deployed to.
If left blank, dynamic tables will be deployed to the same database the source database object resides in.
### 5. (Optional) Enter the name of the schema the Dynamic Tables will be deployed to.
If left blank, dynamic tables will be deployed to the same schema the source database object resides in.
### 6. (Optional) Enable "Include Stream Message Metadata"
If enabled, the core Kafka message metadata will be extracted and included in the root and all nested dynamic table select clauses.
### 7. (Optional) Enable "Use Case Sensitivity"
If enabled, all output column names will be enclosed with double quotes allowing for the accomodation of special characters in the attribute names.
***
## Dynamic Table Settings
Dynamic tables are a new type of object in Snowflake that can be used to automatically materialize data based on a schedule similar to how a materialization table, stream (on the source table), and a task with a cron schedule (used to merge CDC events from the stream into the materialized table) would work. For more information see
### 1. Enter the Dynamic Table Warehouse Name
The name of warehouse that will be assigned to the dynamic tables which is a part of the CREATE dynamic table statement. This is the warehouse used to manage all of the data refreshes once the dynamic tables are deployed.
### 2. Enter the Dynamic Table Target Lag Unit Type
The frequency interval for the dynamic table refresh. This value will be included in the CREATE dynamic table statement.
### 3. Enter the Dynamic Table Target Lag Quantity
The frequency interval quantity for the dynamic table refresh. This value will be included in the CREATE dynamic table statement. The minimum is one minute.
### 4. (Optional) Enter the Root Dynamic Table Optional Parameters
This value will be included in the CREATE dynamic table statement. See for available options.
### 5. (Optional) Enter the Nested Dynamic Table Optional Parameters
Optional parameters for the flattened dynamic tables created from nested arrays. This value will be included in the CREATE dynamic table statement. See for available options.
***
## Dynamic Table Metadata Settings
Metadata tables are used to track changes to dynamic table rows by storing the primary key, a unique UUID, the created at, last action (INSERT or UPDATE), and last updated date for each row in the root dynamic table.
### 1. (Optional) Toggle the Create Metadata for Dynamic Table Inserts
If enabled, SQL DDL code will be generated to create a new table to store the metadata for each row in the root dynamic table.
### 2. (Optional) Toggle the Create Metadata for Dynamic Table Updates
If enabled, the metadata will be created for row updates in additon to inserts.
### 3. (Optional) Toggle the Create Metadata for Dynamic Table Deletes
If enabled, the metadata will be created for row deletes.
### 4. (Optional) Enter the Task Deployment Database Name
The name of the database the metadata table sync task will be deployed to.
{% hint style="info" %}
If any of the 3 ‘Create Metadata’ toggles are toggled on and this is left blank, it will assume the same database name as the source table.
{% endhint %}
### 5. (Optional) Enter the Task Deployment Schema Name
The name of the schema the metadata table sync task will be deployed to.
{% hint style="info" %}
If any of the 3 ‘Create Metadata’ toggles are toggled on and this is left blank, it will assume the same schema name as the source table.
{% endhint %}
### 6. Enter the Metadata Table Task Warehouse Name
{% hint style="warning" %}
If any of the 3 ‘Create Metadata’ toggles are enabled, this is **required**.
{% endhint %}
The name of warehouse that will be included in the CREATE Task statement used to create metadata based on changes to the root Dynamic Table.
### 7. Enter the Metadata Table Task Cron Schedule
{% hint style="warning" %}
If any of the 3 ‘Create Metadata’ toggles are enabled, this is **required**.
{% endhint %}
The metadata table task cron schedule used to schedule the metadata updates. The schedule should coincide with the target lag of the dynamic table to be as frequent or more frequent. Examples:
Hourly at 30 minutes after the hour
```python
30 * * * *
```
Daily at 3:00 am
```python
0 3 * * *
```
Weekly at 3:00 am every Monday
```python
0 3 * * MON
```
Monthly at 3:00 am on the 1st of each month
```python
0 3 1 * *
```
### 8. Enter the Metadata Table Task Schedule Time Zone
{% hint style="warning" %}
If any of the 3 ‘Create Metadata’ toggles are enabled, this is **required**.
{% endhint %}
A cron timezone is required in addition to the cron schedule.
***
## Next Steps
See [How to Configure the Secure Semantic View Layer](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-the-secure-semantic-view-layer)
# How to Configure the Secure View Layer
Configure the Semantic Layer Settings for a data source.
If configured, the secure semantic view layer and security policy settings will be used to generate CREATE VIEW statements to create a semantic layer that selects data from the generated dynamic tables. One view will be created for each dynamic table.
You can learn more about Snowflake's security policy capabilities here and .
### 1. Enter the Object Type
### 2. Enter the Root Level Name
The name used for the root view for the semantic layer. This value will be used as a prefix for all nested array views.
This value is required if you choose a type of semantic layer object for this data source.
You do not need to include an underscore at the end of this name. An underscore will be added automatically for all nested output objects.
### 3. (Optional) Enter the Semantic Layer Deployment Database Name
The name of the database the semantic layer objects will be deployed to.
Explore [deployment options](/core-concepts/reference-architecture/generated-sql-ddl-deployment-options) to learn more.
### 4. (Optional) Enter the Semantic Layer Deployment Schema Name
The name of the schema the semantic layer objects will be deployed to.
Explore [deployment options](/core-concepts/reference-architecture/generated-sql-ddl-deployment-options) to learn more.
### 5. (Optional) Enter the Primary Row Access Policy Name
The name of the row access policy used for the root level semantic layer object.
### 6. (Optional) Enter the Optional Parameters
Optional parameters for the root level semantic layer object. This value will be included in the CREATE statement.
Visit to learn more.
# How to Configure Schema Filters
Configure Schema Filters for a data source.
Schema filters are used to exclude parts of the schema from the code generation process.
If filtered, attributes discovered will have a record status of 'inactive' and can be changed back to active at any time. Additionally, the record status for an attribute can be modified in the Pipeline Designer.
{% hint style="warning" %}
You must create a data source *before* you can add Schema Filters.
{% endhint %}
### 1. Enter the name for a new schema filter
### 2. Enter the filter RegEx for a new schema filter
This regular expression is used to search the discovered paths so DataPancake can then set the attribute record status to 'inactive'.
{% hint style="info" %}
This can be done *before* or *after* schema discovery. If done after, rescanning is *not* required.
{% endhint %}
### 3. Click "Save Schema Filters"
# How to Configure a Data Source for a Single Semi-Structured Data Column in a Table or View
Configure a table or view data source for semi-structured data.
### 1. Navigate to the Data Sources page
### 2. Enter the Data Source name
### 3. Select Semi-Structured for the Data Source Type
### 4. Enter any data source tags
These are DataPancake tags for DataPancake data sources, *not* Snowflake tags.
### 5. Select the Object type (Table, View, or Materialized View)
{% hint style="info" %}
See the documentation for [External Tables](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-an-external-table-data-source-json-only) or [Iceberg Tables](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-an-iceberg-data-source-json-only) for creating additional types of data sources.
{% endhint %}
### 6. Select the Column Data Type
### 7. Select the Format Type
### 8. Select the Database
### 9. Select the Schema
### 10. Select the Object Name
### 11. Enter the Column Name
Use uppercase, otherwise use "double quotes" to encapsulate the column name if it is case sensitive.
### 12. (Optional) Enter Sample Schema Data
### 13. Ensure all required fields are completed
The "Required Fields" section in the bottom of the left-hand sidebar will show exactly which fields must still be completed.
If code generation feature is enabled, see [How to Configure the Secure Semantic View Layer](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-the-secure-semantic-view-layer).
### 14. Save the data source (unless the code generation feature has been selected)
If the code generation feature is enabled, you must complete the [Output Object & Dynamic Table Settings](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-output-object-and-dynamic-table-settings) before you can save.
***
## Next Steps
[How to Modify Enabled Features for a Data Source](/guides/how-to-guides-ui/modify-enabled-features-for-a-data-source)
[How to Configure the Secure Semantic View Layer](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-the-secure-semantic-view-layer)
Pipeline Designer (WIP)
Data Dictionary Builder (WIP)
Semantic Model Generator (WIP)
# How to Configure a Data Source for a Structured Table or View
Configure a table or view data source for structured data.
### 1. Navigate to the Data Sources page
### 2. Enter the data source name.
### 3. Select "Structured" for the Data Source Type
### 4. Enter any data source tags
These are DataPancake tags for DataPancake data sources, *not* Snowflake tags.
### 5. Select the Object type (Table, View, or Materialized View)
{% hint style="info" %}
See the documentation for [External Tables](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-an-external-table-data-source-json-only) or [Iceberg Tables](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-an-iceberg-data-source-json-only) for creating additional types of data sources.
{% endhint %}
### 6. Select the Database
### 7. Select the Schema
### 8. Select the Object Name
### 9. Ensure all required fields are completed
The "Required Fields" section in the bottom of the left-hand sidebar will show exactly which fields must still be completed.
If code generation feature is enabled, see [How to Configure the Secure Semantic View Layer](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-the-secure-semantic-view-layer).
### 10. Save the Data Source
***
## Next Steps
[How to Modify Enabled Features for a Data Source](/guides/how-to-guides-ui/modify-enabled-features-for-a-data-source)
[How to Configure the Secure Semantic View Layer](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-the-secure-semantic-view-layer)
Pipeline Designer (WIP)
Data Dictionary Builder (WIP)
Semantic Model Generator (WIP)
# How to Configure an External Table Data Source (JSON Only)
Configure an External Table data source.
### 1. Ensure Semi-Structured is selected for the Data Source Type
### 2. Select External Table for the Object Type
### 3. Select the Column Data Type
### 4. Select JSON for the Format Type
### 4. Click "DEMO\_DATA"
5\. Click "SAMPLE\_DATA"
6\. Click here.
7\. Click the "Column Name\*" field.
### Ensure all required fields are completed
The "Required Fields" section in the bottom of the left-hand sidebar will show exactly which fields must still be completed.
If code generation feature is enabled, see [How to Configure the Secure Semantic View Layer](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-the-secure-semantic-view-layer).
### 10. Save the Data Source
***
## Next Steps
[How to Modify Enabled Features for a Data Source](/guides/how-to-guides-ui/modify-enabled-features-for-a-data-source)
[How to Configure the Secure Semantic View Layer](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-the-secure-semantic-view-layer)
Pipeline Designer (WIP)
Data Dictionary Builder (WIP)
Semantic Model Generator (WIP)
# How to Configure an Iceberg Data Source (JSON Only)
Configure an Iceberg data source.
### 1. Ensure Semi-Structured is selected for the Data Source Type
### 2. Select the Object Type of 'Iceberg Table'
### 3. Select "String" for the Column Data Type
{% hint style="info" %}
Only String Data Types are supported for Iceberg Tables
{% endhint %}
### 4. Select "JSON" for the Format Type
### 5. Select the Database
### 6. Select the Schema
### 7. Select the Object Name
### 8. Enter the Column Name
### 9. Ensure all required fields are completed
The "Required Fields" section in the bottom of the left-hand sidebar will show exactly which fields must still be completed.
If code generation feature is enabled, see [How to Configure the Secure Semantic View Layer](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-the-secure-semantic-view-layer).
### 10. Save the Data Source
***
## Next Steps
[How to Modify Enabled Features for a Data Source](/guides/how-to-guides-ui/modify-enabled-features-for-a-data-source)
[How to Configure the Secure Semantic View Layer](/guides/how-to-guides-ui/creating-and-configuring-data-sources/how-to-configure-the-secure-semantic-view-layer)
Pipeline Designer (WIP)
Data Dictionary Builder (WIP)
Semantic Model Generator (WIP)
# How to Manage a Scan Configuration
Create or modify a scan configuration for a data source.
Configure Required Scan Configuration Settings
### 1. Navigate to the Scan Configurations Page
### 2. Select a data source
### 3. Enter the Configuration Name
### 4. Select the Virtual Warehouse
### 5. Ensure the Warehouse is Connected
### 6. (Optional) Configure additional settings
[Base Configuration Settings](#optional-base-configuration-settings)
[Scan Schedule Settings](#optional-scan-schedule-settings)
[Data Source Settings](#optional-data-source-settings)
[Vertical Scale Settings](#optional-vertical-scale-settings)
[Source Stream Settings](#optional-source-stream-settings)
### 7. Save the scan configuration
***
Optional Base Configuration Settings
### Set the Attribute Create Type
The default Discover create type uses scanned data to create attribute metadata.
The Schema create type uses the Data Source Object Schema Sample data to create the attribute metadata.
### Update the Scan Configuration Status
### Enable Auto Code Generate
If enabled, DataPancake will automatically generate dynamic table SQL code if the data source's schema or polymorphic state changes.
***
(Optional) Scan Schedule Settings
### 1. Enable a Scan Schedule
### 2. Enter a Cron Schedule
A cron schedule is required if you enable scheduling. See the examples below for valid cron schedules:
Hourly at 30 minutes after the hour
```python
30 * * * *
```
Daily at 3:00 am
```python
0 3 * * *
```
Weekly at 3:00 am every Monday
```python
0 3 * * MON
```
Monthly at 3:00 am on the 1st of each month
```python
0 3 1 * *
```
### 3. Select a Cron Time Zone
A cron timezone is required in addition to the cron schedule if you enable scheduling.
***
(Optional) Data Source Settings
### Enter a Record Limit
### Enter a Where Clause
The where clause can only be used with a single procedure call.
***
(Optional) Vertical Scale Settings
### Modify the Number of Threads
The number of threads will default to the maximum number of threads available to the virtual warehouse chosen.
### Modify the Number of Procedure Calls
The number of scan procedure calls required to process the entire dataset.
Use multiple procedure calls when a single call cannot be completed in under sixty minutes which is the default timeout for the Snowpark Python Sandbox.
The where clause parameter is not available if the number of procedure calls is greater than 1.
### Modify the Record Count Per Procedure Call
The record count per procedure is required when the number of procedure calls is greater than 1.
The record count chosen needs to be large enough to process all the rows in the data source but not too large as to create procedure calls with no rows to process based on the criteria you have provided and the number of calls entered.
Ex. 2,000,000 rows can successfully be divided into two calls with 1m records per call. But it cannot be divided into 2 calls with 500,000 rows per call or 3 calls with 2.5m rows per call.
***
(Optional) Source Stream Settings
### Modify the Last Scanned Timestamp
The last timestamp scanned. This value will be used as part of the where clause when scanning data from this data source. To scan the entire datasource remove the timestamp if one exists.
# Page
## Configuring DATAPANCAKE Settings in Snowflake
[**Made by Ben Wolff with Scribe**](https://scribehow.com/shared/Configuring_DATAPANCAKE_Settings_in_Snowflake__LxS3wN8cQt27WKSfur30Fw)
1\. Navigate to
2\. Click here.
3\. Click "udi\_json"
4\. Click the "Configuration Name\*" field.
5\. Type "Full Scan Multi Call"
6\. Click here.
7\. Click "DATAPANCAKE\_MEDIUM\_01"
8\. Click the "Number of Procedure Calls\*" field.
9\. Click the "Record Count Per Procedure Call\*" field.
10\. Type "2500000"
11\. Click the "Order By\*\*" field.
12\. Press \[\[cmd]] + \[\[v]]
13\. Click "Save"
14\. Switch to tab Pancake Regression Test Plan Template - Google Sheets"
15\. Click "json\_data:"\_id""
16\. Switch to tab DATAPANCAKE\_HOME - DATAPANCAKE - Snowflake"
17\. Click the "Configuration Name\*" field.
18\. Type "Partial Scan Where Clause 1"
19\. Click the "Configuration Name\*" field.
20\. Click here.
21\. Click "DATAPANCAKE\_MEDIUM\_01"
22\. Click the "Where Clause" field.
23\. Press \[\[cmd]] + \[\[v]]
24\. Click the "Where Clause" field.
25\. Click "Save"
26\. Click the "Configuration Name\*" field.
27\. Type "Partial Scan Where Clause 2"
28\. Click the "Configuration Name\*" field.
29\. Click here.
30\. Click here.
31\. Click "DATAPANCAKE\_MEDIUM\_01"
32\. Click the "Record Limit" field.
33\. Type "1000"
34\. Click the "Record Limit" field.
35\. Click the "Where Clause" field.
36\. Press \[\[cmd]] + \[\[v]]
37\. Click the "Where Clause" field.
38\. Click "Save"
39\. Click the "Configuration Name\*" field.
40\. Type "Partial Scan Order By Limit"
41\. Click the "Configuration Name\*" field.
42\. Click the "Record Limit" field.
43\. Click the "Order By\*\*" field.
44\. Press \[\[cmd]] + \[\[v]]
45\. Click the "Order By\*\*" field.
46\. Click "Save"
47\. Click the "Configuration Name\*" field.
48\. Click here.
49\. Click the "Cron Schedule\*\*" field.
50\. Press \[\[cmd]] + \[\[v]]
51\. Click the "Cron Schedule\*\*" field.
52\. Click here.
53\. Click "America/New\_York"
54\. Click "Save"
55\. Click here.
56\. Click the "Record Limit" field.
57\. Type "150"
58\. Click the "Record Limit" field.
[**Made with Scribe**](https://scribehow.com/shared/Configuring_DATAPANCAKE_Settings_in_Snowflake__LxS3wN8cQt27WKSfur30Fw)
# How to Configure Schema Consolidation
Configure Schema Consolidation for a data source.
Schema consolidations are used to reduce schemas into a smaller schema using regex expressions.
This is useful when you have multiple schemas that contain similar data and you want to simplify your data model.
For example, if you have a schema for each region, you could consolidate them into a single schema that contains all the data for all regions. This would make it easier to query the data and create reports.
### 1. Enter the Name for the new Conslidation
For example: `X|Gfs|Shipment`
### 2. Enter the Search Regex for the new Conslidation
For example: `X|Gfs|Shipment|`
### 3. Enter the Search Regex for the Conslidation
For example: `|\d+`
### 4. Save the new Schema Consolidation
[**Made with Scribe**](https://scribehow.com/shared/Consolidate_Schema_in_Snowflake_and_Google_Sheets__fjtDvspLT5qMkedYfJolfw)
# How to Configure Baseline Scan Settings
Configure Baseline Scan Settings for a data source.
### (Optional) Reset the Baseline Scan Settings from the Data Sources page
Reset the scan default settings to 0. This allow the next scan to recreate the base values used to determine estimated time and completion progress for future scan.
# How to Modify the Schema
### 1. Navigate to the Data Sources page
### 2. Select the data source
### 3. Scroll down to the Schema Transformation section
Schema transformations are used to modify the source schema during the materialization process.
### 4. Click the + symbol in the bottom row to add a new schema transformation
To delete a row move your mouse to the leftmost column in the table to reveal a checkbox, check the box, and press the delete key.
### 5. Add the required schema transformation properties
For this guide we will add four transforms. See the table below the screenshots for exact values
| Name | Transformation Type | Search Attribute Path | Search Expression | Replace Expression | String List Type | String Delimiter Value |
| ------------ | -------------------------- | --------------------- | ------------------------------------------------------------------------------------------------------------- | ------------------ | ---------------- | ---------------------- |
| transform\_1 | consolidate schema | | Y\|Cre\|Shipment\| | \|\d+ | | |
| transform\_2 | transform string to array | special\_tags\_pipe | | | primitive | \| |
| transform\_3 | transform string to array | batch\_tags\_pipe | | | primitive | \| |
| transform\_4 | transform objects to array | operation\_logs | ^op\_\[a-z]+-\[0-9a-fA-F]{8}-\[0-9a-fA-F]{4}-\[1-5]\[0-9a-fA-F]{3}-\[89abAB]\[0-9a-fA-F]{3}-\[0-9a-fA-F]{12}$ | codeId | | |
### 6. Save the Schema Transformations
### 7. Ensure the transformations/consolidations updated successfully
# Modify Enabled Features for a Data Source
Enable or disable the Pipeline Designer, SQL Code Generation, Security Policy Integration, Data Dictionary Builder, and Semantic Model Generator as needed for each data source.
### 1. Navigate to the "Data Sources" page
### 2. Select your data source
### 3. Click "Enable Feature Selection"
{% hint style="success" %}
**DataPancake has a value-based pricing model for each feature selected:**
**Pay per attribute, per month used.**
All selected services will generate a unique billing event for the number of attributes contained in the object connected to this data source for each month used.
You are not charged for months where DataPancake is not utilized.
Visit our [pricing page](https://www.datapancake.com/pricing) for more details and our pricing calculator.
{% endhint %}
### 4. Check the features you want to enable or disable
Some features depend on other features and will be automatically selected if required.
### 5. Click the "Save Data Source" button
You will need to scroll down near the bottom of the page to find the save button.
### 6. Verify the save completed successfully.
# How to Scan the Data Source
### 1. Navigate to the Scan Data page
### 2. Select the data source
### 3. Select the data source scan configuration
### 4. (Optional) Click "Reset Attributes"
This will delete all existing attributes and re-discover all attributes from the data source.
{% hint style="success" %}
Use this feature after adding schema transformations to apply the changes to the data source discovery
{% endhint %}
### 5. Start the Scan
### 6. Ensure the scan starts successfully
# Pipeline Designer
# How to Create Virtual Attributes
### Select your data source on the Pipeline Designer page
### Open the Virtual Attributes section
### Enter the name of the new virtual attribute
{% hint style="info" %}
By default, you will be in "Create New Attribute" mode. After you finish creating a virtual attribute, you can select an existing virtual attribute to edit in that top dropdown to make any updates as needed.
{% endhint %}
### Add the description of the virtual attribute
This description will be used in the semantic model for the attribute.
### Select the source data type
### Select the Snowflake data type
### (Optional) Select the W Question Category
You can select one of the W (What, Where, When, Who, Why) Question categories. This value will be used to determine which section of the semantic model to add the attribute to.
### (Optional) Select the Object Array
The object array the virtual attribute will be inserted into. If left blank the virtual attribute will be inserted at the root level.
### Enter the SQL Expression
This SQL expression will be used for the Dynamic Table column definition.
### Create the virtual attribute
### Ensure the attribute was created successfully
### Add additional virtual attributes as needed
For the JSON Tutorial, add the customer\_email\_domain virtual attribute:
# How to Add Column Materialization Rules
### Select your data source on the Pipeline Designer page
### Navigate to the Transformation/Security tab
### Search for the desired attribute in the editable grid
### Scroll horizontally to view more columns
### Update the materialization transformation type to SQL Expression
### Enter the materialization transformation expression
For the JSON Tutorial, please \[deploy the database tutorial objects], then for the expression use:
```
udf_std_part_number({attribute_name})
```
### Enter the materialization transformation description
### Click save attributes to apply changes
# How to Apply Column-Level Schema Transformations
### Locate the attribute to update
### Add a schema consolidation search value
You may need to horizontally scroll in the editable grid.
For the JSON Tutorial, use:
```
Y|Cre|Shipment
```
### Add a schema consolidation SQL expression
For the JSON tutorial, use:
```
CONCAT('|',REGEXP_SUBSTR(OBJECT_KEYS(VARIANT_COL)::STRING, 'Y\|Cre\|Shipment\|(\d+)', 1, 1, 'e', 1))
```
### Add more schema consolidation search and expression values
For the JSON tutorial, apply the same values as above to the `Y|Cre|Shipment.shipment_status` attribute.
### Click save to apply changes
# How to Merge Polymorphic Attribute Versions
This feature allows you to automatically merge all numeric attributes into either the float or string polymorphic version of the attribute by creating a sql expression and an alias.
{% hint style="success" %}
This is useful when you have multiple numeric attributes that represent the same data but in different data types (e.g., str, int, and float).
{% endhint %}
### On the Pipeline Designer, select your data source and click on the Transformation/Security tab
### Open the merge polymorphic versions section
{% hint style="info" %}
**Note:** This operation sets `INCLUDE_IN_SEMANTIC_LAYER` to **TRUE** for the polymorphic version (string or float) that is being merged into, and to **FALSE** for all other polymorphic versions.
{% endhint %}
### Select string in the dropdown to merge numeric attributes into string polymorphic versions
### Click "Merge Attributes"
### Select float in the dropdown to merge numeric attributes into the float polymorphic version
### Click "Merge Attributes"
# How to Add Aliases
### Open the Transformation/Security tab in the Pipeline Designer page
### Find the View Alias column and add alias names for the attributes
For the JSON Tutorial, add the following view alias to the `Y|Cre|Shipment.last_update` attribute:
```
shipment_last_update
```
Additionally, add the following view alias to the `Y|Cre|Shipment.shipment_status`attribute:
```
shipment_last_status
```
### Save the Attributes
### Scroll to the Arrays section
### Enter dynamic table alias names for the desired array attributes
For the JSON tutorial, find the operation\_logs attribute and use this dynamic table alias name:
```
process_logs
```
Additionally, find the process\_steps attribute and use this dynamic table alias name:
```
process_steps
```
### Save the Arrays
### Ensure the data source arrays saved
# How to Configure Array Relationships \[Coming Soon]
### \[Coming Soon]
# How to Configure Security Policies
### Open the Transformation/Security tab in the Pipeline Designer
### Find the Attribute you want to update
### Scroll horizontally and check the "Add to Row Access Policy" to add the attribute to the row access policy
### Set the value for the Masking Policy Name
For the JSON Tutorial, set customer\_email to:
```
mp_customer_email
```
and set customer\_phone to:
```
mp_customer_phone
```
### Save the attributes
# How to Configure Foreign Keys
### Scroll to the Array Foreign Keys section in the Pipeline Designer
### Select an attribute to create foreign key(s)
### Enter a parent attribute name
### (Optional) Enter a foreign key column alias
### (Optional) Add the foreign key to the row access policy
### Save the foreign key(s)
### Extra Steps for the JSON Tutorial
If you are following the JSON Tutorial, create the following foreign keys. It may help to use the [bulk update feature for foreign keys](/guides/how-to-guides-bulk-upsert-via-csv/usage/array-foreign-key-bulk-update).
ARRAY_NAME
PARENT_ATTRIBUTE_NAME
batch_labels_array_primitive
plant_code_work_order_id_pk
batch_tags_pipe_array_primitive
plant_code_work_order_id_pk
measurement_metric_value_array_primitive
plant_code_work_order_id_pk
operation_logs_array_object
plant_code_work_order_id_pk
process_steps_array_object
plant_code_work_order_id_pk
product_specifications_array_primitive
plant_code_work_order_id_pk
quality_notes_array_primitive
plant_code_work_order_id_pk
shipments_array_object
plant_code_work_order_id_pk
special_tags_pipe_array_primitive
plant_code_work_order_id_pk
suppliers_array_object
plant_code_work_order_id_pk
flags_array_primitive
plant_code_work_order_id_pk
nested_array_primitive
plant_code_work_order_id_pk
text_array_primitive
plant_code_work_order_id_pk
equipment_array_object
process_step_id_str
items_array_primitive
shipment_id_str
batch_labels_array_primitive
plant_code_work_order_id_pk
measurement_metric_value_array_primitive
plant_code_work_order_id_pk
product_specifications_array_primitive
plant_code_work_order_id_pk
quality_notes_array_primitive
plant_code_work_order_id_pk
flags_array_primitive
plant_code_work_order_id_pk
text_array_primitive
plant_code_work_order_id_pk
instruments_array_object
equipment_id_str
configuration_notes_array_primitive
equipment_id_str
calibration_config_array_primitive
instrument_id_str
measurements_array_object
instrument_id_str
qc_results_array_object
measurement_id_str
defect_codes_array_primitive
qc_id_str
details_array_primitive
qc_id_str
# How to Generate Dynamic SQL Statement Code
### Open the Pipeline Designer and select your datasource
### Click the "Generate Data Source SQL" button in the sidebar
### Wait for the SQL to be generated
This process can take a few seconds to a few minutes depending on the complexity of your data source.
### Ensure the SQL statements generate successfully
# Data Dictionary Builder
# How to Generate a Data Source Description
### Navigate to the Data Dictionary Builder
### Select your data source
### Select a Cortex AI model
See the [model availability by cloud provider](https://docs.snowflake.com/user-guide/snowflake-cortex/aisql#availability) for more information.
### (Optional) Modify the Data Source Description Word Limit
The maximum number of words to be generated by Cortex AI for the data source description.
### (Optional) Modify the Array Description Word Limit
The maximum number of words to be generated by Cortex AI for the array description.
### (Optional) Modify the Attribute Description Word Limit
The maximum number of words to be generated by Cortex AI for the attribute description.
### Open the Data Source Description editor
### (Optional) Add a custom AI Prompt
This prompt will be added to the DataPancake system prompt.
### Generate a description with Cortex AI
### Review & modify the generated description
{% hint style="warning" %}
Please be aware of using special markdown characters such as ' or " in the AI prompt or description as they may interfere with the final prompt.
{% endhint %}
### Save the description
### Verify the description saved successfully
# How to Generate Array Descriptions & Synonyms
### In the Data Dictionary Builder's "Array Descriptions" section, select an array to edit
### Open the description & synonym editor
### (Optional) Add a custom AI Prompt
This prompt will be added to the DataPancake system prompt.
### Open the description & synonym editor
### Generate the description with Cortex AI
### Review and modify the generated description
{% hint style="warning" %}
Please be aware of using special markdown characters such as ' or " in the AI prompt or description as they may interfere with the final prompt.
{% endhint %}
### Generate synonyms with Cortex AI
### Review generated synonyms and modify as needed
{% hint style="warning" %}
Please be aware of using special markdown characters such as ' or " in the AI prompt or description as they may interfere with the final prompt.
{% endhint %}
### Save array description and synonyms
### Verify array description and synonyms saved successfully
# How to Generate Attribute Descriptions, Synonyms, & Sample Values
### In the Data Dictionary Builder, select an array to edit attributes for
### Scroll to the Attribute Descriptions & Synonyms section
These attributes are based on the selected array
### Select an attribute
### Open the attribute description, synonyms, and sample data editor
### (Optional) Add a custom AI prompt
This prompt will be added to the DataPancake system prompt
### Generate the description with Cortex AI
### Review the generated description and modify as needed
{% hint style="warning" %}
Please be aware of using special markdown characters such as ' or " in the AI prompt or description as they may interfere with the final prompt.
{% endhint %}
### Generate synonyms with Cortex AI
### Review generated synonyms and modify as needed
{% hint style="warning" %}
Please be aware of using special markdown characters such as ' or " in the AI prompt or description as they may interfere with the final prompt.
{% endhint %}
### Generate sample values
### Review generated sample values and modify as needed
{% hint style="warning" %}
Please be aware of using special markdown characters such as ' or " in the AI prompt or description as they may interfere with the final prompt.
{% endhint %}
### Save the description, synonyms, and sample values
### Verify the new values saved successfully
# How to Deploy & Validate Generated Code
{% hint style="info" %}
Please ensure you have [generated the latest SQL code](/guides/how-to-guides-ui/pipeline-designer/how-to-generate-dynamic-sql-statement-code).
{% endhint %}
### Open the Data Source SQL page and select your data source
### Click the copy button for the generated code block
### Open Workspaces in Snowflake
### In a new SQL file, paste the copied SQL statements
Review the statements carefully before deploying
### Select the desired database and schema to deploy objects to
### Run the statements and ensure they complete successfully
# Data Dictionary
# Semantic Model Generator
## Creating a Semantic Model in Snowflake
1\. Click "Semantic Model Generator"
2\. Click here.
3\. Click "customer\_data"
4\. Click here.
5\. Click here.
6\. Click here.
7\. Click here.
8\. Double-click here.
9\. Click here.
10\. Click here.
11\. Click here.
12\. Click here.
13\. Click here.
14\. Click here.
15\. Click here.
16\. Click here.
17\. Click here.
18\. Click "Virtual Attributes"
19\. Click the "Attribute Name" field.
20\. Type "example"
21\. Click the "Description" field.
22\. Type "an example virtual attribute"
23\. Click "str"
24\. Click "int"
25\. Click "NUMBER"
26\. Click "DECIMAL"
27\. Click here.
28\. Click "filters"
29\. Click "Root level (no array)"
30\. Click "customerData.customers"
31\. Click the "SQL Expression\*" field.
32\. Type "''"
33\. Click "Create"
34\. Click "Create"
35\. Click the "Semantic Model Instructions" field.
36\. Type "Additional Instructions go here."
37\. Click "Save Instructions"
38\. Click "Generate YAML Code"
39\. Click "itemized purchases"
40\. Click this icon.
[**Made with Scribe**](https://scribehow.com/shared/Creating_a_Semantic_Model_in_Snowflake__dqN-mugtTgul1lSf3oqMFg)
# How to Guides (Script Builder)
# Admin Guides
# How to Grant Account Level Privileges to DataPancake
Build a script to grant DataPancake the necessary account-level and Cortex AI privileges required for scanning and schema discovery the Cortex AI assisted data dictionary builder.
### 1. Navigate to the admin page
### 2. (Optional) Change the app database name
The default is DATAPANCAKE
### 3. Enter the role name used to install data pancake
### 4. Enter a role name with privileges to create a warehouse
### 6. Enter the warehouse name used to execute the generated script
### 7. Ensure the "Initialize Application Database Privileges" checkbox is checked
If you are ever re-running this script, you can uncheck this box to skip this step.
### 8. Scroll to the bottom and click "Generate Script"
### 9. Copy the script
### 10. Open a new Snowflake SQL file and paste the script
Ensure you have a role and warehouse selected to execute the SQL file
### 11. Review & Execute the script
Use the blue dropdown arrow at the top right and click "Run All"
### 12. Ensure successful execution by examining the call result column in the result set
# How to Configure Available Warehouses in DataPancake
Build a script to configure virtual warehouse access for DataPancake by granting usage on existing warehouses or provisioning new, optimized warehouses for scanning and discovery workloads.
### 1. Navigate to the "Admin" script builder
If you are already on the Admin script builder page, navigate to the Getting Started page and then the Admin script builder to reset the admin form UI.
### 2. Enter the role name used to install DataPancake
### 3. Enter a role name with privileges to create a warehouse
### 4. Enter the warehouse name used to execute the generated script
### 5. Determine which warehouse sizes to include in the script
### 6. Set the warehouse statuses
**Create New** will create a new warehouse and add it to DataPancake
**Use Existing** will add an existing warehouse to DataPancake
### 7. Enter warehouse names
Enter warehouse names for existing warehouses or enter a custom name if you are creating a new warehouse
### 8. (Optional) Modify the Auto-Suspend time
### 9. Click "Generate Script"
### 10. Copy the script
### 11. Open a new Snowflake SQL file and paste in the script
### 12. Review & Execute the script
Use the blue dropdown arrow at the top right and click "Run All"
### 13. Ensure successful execution by examining the call result column in the result set
### Troubleshooting
The query may take several minutes to finish.
If Snowflake fails to provision any of the larger warehouses, simply refresh the page and re-execute the required statements.
# How to Manage Access to DataPancake
To allow additional users to access DataPancake, you can grant the APP\_PUBLIC application role to any existing system or custom role in your Snowflake account.
### 1. Navigate to the "Admin" script builder
If you are already on the Admin script builder page, navigate to the Getting Started page and then the Admin script builder to reset the admin form UI.
### 2. (Optional) Change the App Database Name
The default is DATAPANCAKE
### 3. Enter the role name used to install DataPancake
### 4. Enter a role name with privileges to create a warehouse
### 5. Enter the warehouse name used to execute the generated script
### 6. Scroll to the Access Management section and enter the role names
### 7. Click "Generate Script"
### 8. Copy the generated script
### 9. Open a new Snowflake SQL file and paste in the script
### 10. Review & Execute the script
Use the blue dropdown arrow at the top right and click "Run All"
### 11. Ensure successful execution by examining the call result column in the result set
# Data Source Guides
# How to Create a Single DataPancake Data Source (Script Builder)
Build a script to create and initiate a scan for a single data source in DataPancake and optionally generate SQL code.
{% hint style="warning" %}
Please ensure the [Admin Guides](/guides/how-to-guides-script-builder/admin-guides) have been completed before creating your first data source.
{% endhint %}
{% hint style="info" %}
This script builder is not available for the following scenarios:
* Data sources with a pre-configured schema
* Data sources that contain stream data such as Kafka
See [Creating & Configuring Data Sources](/guides/how-to-guides-ui/creating-and-configuring-data-sources) guides for more details.
{% endhint %}
### 1. Navigate to the "Data Source" script builder
### 2. Enter the custom data source name
### 3. (Optional) Change the app database name
The default is DATAPANCAKE
### 4. Enter the role used to install DataPancake
### 5. Enter the role which has the select privilege for the database object to be scanned
### 6. Select the Snowflake Database Object Type
### 7. Select the Column Data Type (Semi-Structured Only)
### 8. Select the Data Source Type
### 9. (Semi-Structured Only) Select the Data Format Type
### 10. Select the Attribute Create Type
"Discover" means DataPancake will discover the *all* attributes for you during the scan. "Schema" means you will provide a schema for all known attributes, on top of which DataPancake can find additional discovered attributes.
### 11. Enter the Snowflake database name that contains the data
{% hint style="info" %}
If the name is case sensitive, put the name in double quotes such as `"EXAMPLE_NAME"`
{% endhint %}
### 12. (Optional) Indicate if the database is shared
### 13. Enter the name of the schema which contains the database object to be scanned
{% hint style="info" %}
If the name is case sensitive, put the name in double quotes such as `"EXAMPLE_NAME"`
{% endhint %}
### 14. Enter the name of the database object to be scanned
{% hint style="info" %}
If the name is case sensitive, put the name in double quotes such as `"EXAMPLE_NAME"`
{% endhint %}
### 15. (Semi-Structured Only) Enter the name of the column to be scanned
{% hint style="info" %}
If the name is case sensitive, put the name in double quotes such as `"EXAMPLE_NAME"`
{% endhint %}
### 16. Select the warehouse to be used by DataPancake to scan
### 17. (Optional) Prevent the scan from starting automatically
If you switch this off, you will need to manually trigger the data source scan from the "Scan Data" page.
### 18. (Optional) Enter a record limit for the initial scan
If you want to scan *all* records, set the limit to 0
### 19. (Optional) Enable Additional Paid Features
{% hint style="info" %}
Explore to learn more about DataPancake's transparent, attribute-based pricing model.
{% endhint %}
### 20. (Optional) Disable Using Case Sensitivity
{% hint style="warning" %}
The "Generate Code After Scan" will automatically create the output SQL statements based on the discovered schema at the completion of the scan.
SQL Code Generation is a paid feature available once you purchase the application or as part of an Enterprise license. Code Generation in the Trial version is not enabled.
If you choose not to generate code, skip to the "Generate Script" step below.
{% endhint %}
### 21. (Optional) Enable generating SQL code after the scan completes
With the SQL Code Generation feature enabled, you can also generate SQL code on scanned data sources from the "Generated SQL" page as needed.
### 22. Enter the Materialized Object Type
The type of materialized object to create.
### 23. Enter the user defined name for the root dynamic table, such as 'dt\_\'
The name used for the materialized object's root name and as a prefix for all nested materialized objects in the code generation process.
### 24. Enter the name of the warehouse used to refresh dynamic tables
This warehouse name is included as part of the create dynamic SQL table statements.
{% hint style="info" %}
**"Why is this not a dropdown with options like the scan data warehouse one above?"**
The previous dropdown is the warehouse used to scan data, which *must* be connected to DataPancake.
The warehouse here is used to refresh dynamic tables and can be any warehouse, including ones not connected to DataPancake.
{% endhint %}
### 25. Select the Semantic Layer - Object Type
The type of semantic layer object to create.
### 26. Enter the user defined name for the root level view, such as 'vw\_\'
### 27. Click "Generate Script"
### 28. Copy the script
### 29. Open a new Snowflake SQL file and paste the script
### 30. Scroll Down to Review the Optional Parameters as Part of the Code Generation
### 31. Execute the Script
Use the blue dropdown arrow at the top right and click "Run All"
### 32. Ensure successful execution by examining the call result column in the result set
If you mistyped any of the database connection information, the resulting error message will show you what was incorrect.
If the warehouse used has not been previously added to DataPancake, then the scan will not be initiated.
If the scan is initiated, the result message will show that the scan has started.
## 33. View the scan status
On the "Scans In Process" page you can view the progress of the scan. Click the "Refresh" button to update view the latest status. When the scan is finished, you are ready to start designing your pipeline.
# Alert Guides
# How to Create Schema Drift Alerts (Script Builder)
Build a script to create schema drift email alerts for all connected data sources in DataPancake.
{% hint style="warning" %}
Please ensure the [Admin Guides](/guides/how-to-guides-script-builder/admin-guides) have been completed and at least one [data source](broken://pages/qx1Zmem0EUKGzG3xdJpY) is connected before creating schema drift alerts.
{% endhint %}
### 1. Navigate to the "Alert" script builder
### 2. (Optional) Change the app database name
The default is DATAPANCAKE
### 3. Enter the role used to install DataPancake
### 4. Enter the name of the Snowflake database used to store the alert
{% hint style="info" %}
If the name is case sensitive, put the name in double quotes such as `"EXAMPLE_NAME"`
{% endhint %}
### 5. Enter the name of the schema used to store the alert
{% hint style="info" %}
If the name is case sensitive, put the name in double quotes such as `"EXAMPLE_NAME"`
{% endhint %}
### 6. Enter the name of the Warehouse used to store the alert
### 7. Select the Interval Type
Choose the interval type for data to be analyzed and determine if an alert should be sent.
### 8. Enter the Interval Quantity
Choose the interval quantity for data to be analyzed and determine if an alert should be sent.
### 9. Enter the comma separated email addresses that should receive the alerts
{% hint style="info" %}
The email addresses must be verified by Snowflake to receive emails. Email verification can be initiated through the user's "My Profile" settings in Snowflake. Click 'Resend verification email' if needed.
{% endhint %}
### 10. (Option 1) Click "Generate Alert Script (Free)"
The free version will send an email notification informing of a schema change to one or more data sources including the name of the data source only.
### 11. (Option 2) Click "Generate Alert Script (Paid)"
The paid version will send an email notification informing of a schema change to one or more data sources including the details of the schema changes.
### 12. Copy the script
### 13. Open a new Snowflake SQL file and paste the script
### 14. Review & Execute the script
Use the blue dropdown arrow at the top right and click "Run All"
### 15. Ensure successful execution by examining the call result
### 16. Alert Management
SQL statements have been provided to:
* Manually execute the alert
* Start and stop the alert scheduling.
* View alert execution history
# How to Guides (Bulk Upsert via CSV)
# Database Object Initialization
# Core Attributes - Initialization
Create a new stage and upload the stored procedure to enable bulk updates for core attribute metadata.
### Create a new stage for datapancake files (or use an existing stage)
```sql
CREATE STAGE ..DATAPANCAKE_FILES;
```
For more details on creating a stage, see
### Copy the Procedure DDL
```sql
CREATE OR REPLACE PROCEDURE ..sp_update_datapancake_attribute_metadata_core(
FILE_PATH string,
ARCHIVE_PATH string,
USER_NAME string
)
RETURNS string
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python','orjson')
HANDLER = 'update_datapancake_attribute_metadata'
EXECUTE AS CALLER
AS
$$
import snowflake.snowpark as snowpark
import datetime
import traceback
import orjson
def update_datapancake_attribute_metadata(session: snowpark.Session, FILE_PATH: str, ARCHIVE_PATH: str, USER_NAME: str):
try:
# Initialize the output variable
attribute_data = ""
# Step 1: Read the CSV file from the specified Snowflake stage
# Set skip_header=0 so we can manually extract and process the first row as the header
df = session.read.options({"field_delimiter": ",", "skip_header": 0}).csv(FILE_PATH)
# Step 2: Collect all rows from the file
rows = df.collect()
if not rows:
return "No rows found."
# Step 3: Extract column names from the first row (header)
# Convert column names to uppercase and strip whitespace
header_row = rows[0]
header_names = [str(getattr(header_row, f"c{i+1}")).strip().upper() for i in range(len(header_row))]
# Step 4: Extract all remaining data rows
data_rows = rows[1:]
if not data_rows:
return "Only header row found."
# Step 5: Build a list of records (dicts) using the extracted headers
records = []
for row in data_rows:
record = {}
for i in range(len(header_names)):
value = getattr(row, f"c{i+1}")
record[header_names[i]] = str(value).strip() if value not in [None, ""] else None
records.append(record)
# Step 6: Serialize the records list as JSON string using orjson
# Default=str handles values like datetime or decimal types
attribute_data = orjson.dumps(records,default=str).decode("utf-8")
# Step 7: Call the DataPancake procedure to update the attribute metadata
result = session.call("DATAPANCAKE.CORE.UPDATE_ATTRIBUTE_METADATA_CORE",USER_NAME, attribute_data, FILE_PATH)
if result == "Success":
#Step 8: Optional: Archive the input file to a new location
if ARCHIVE_PATH is not None and len(ARCHIVE_PATH) > 0 and ARCHIVE_PATH != FILE_PATH:
session.sql(f"COPY FILES INTO '{ARCHIVE_PATH}' FROM '{FILE_PATH}'").collect()
#Step 9: Optional - Remove the source file from the stage
session.sql(f"REMOVE '{FILE_PATH}'").collect()
#Step 10: Return message showing the successful result
return f"The DataPancake attribute metadata has been successfully updated and the file has been archived. Processed {len(records)} rows with columns: {', '.join(header_names)}"
else:
#Step 10: Return the error message
return f"Error: {result} and: {rows[1]}"
except Exception as e:
return f"Error: {str(e)} with a trace of: {traceback.format_exc()}"
$$;
```
### Paste the DDL and modify the database and schema name
{% hint style="info" %}
If you changed the name during install, modify DataPancake application database name.
{% endhint %}
{% hint style="info" %}
Update the DataPancake database application name if you changed the name during install. TODO add circle
{% endhint %}
### Execute the create stored procedure statement
### Verify the stored procedure was created successfully
## Next Steps
[Attribute Metadata Bulk Updates (Core) - Execute](/guides/how-to-guides-bulk-upsert-via-csv/usage/core-attributes-bulk-update)
# Core + Security Attributes - Initialization
Create a new stage and upload the stored procedure to enable bulk updates for both core and security attribute metadata.
### Create a new stage for datapancake files (or use an existing stage)
```sql
CREATE STAGE ..DATAPANCAKE_FILES;
```
For more details on creating a stage, see
### Copy the Procedure DDL
```sql
CREATE OR REPLACE PROCEDURE ..sp_update_datapancake_attribute_metadata_security(
FILE_PATH string,
ARCHIVE_PATH string,
USER_NAME string
)
RETURNS string
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python','orjson')
HANDLER = 'update_datapancake_attribute_metadata'
EXECUTE AS CALLER
AS
$$
import snowflake.snowpark as snowpark
import datetime
import traceback
import orjson
def update_datapancake_attribute_metadata(session: snowpark.Session, FILE_PATH: str, ARCHIVE_PATH: str, USER_NAME: str):
try:
# Initialize the output variable
attribute_data = ""
# Step 1: Read the CSV file from the specified Snowflake stage
# Set skip_header=0 so we can manually extract and process the first row as the header
df = session.read.options({"field_delimiter": ",", "skip_header": 0}).csv(FILE_PATH)
# Step 2: Collect all rows from the file
rows = df.collect()
if not rows:
return "No rows found."
# Step 3: Extract column names from the first row (header)
# Convert column names to uppercase and strip whitespace
header_row = rows[0]
header_names = [str(getattr(header_row, f"c{i+1}")).strip().upper() for i in range(len(header_row))]
# Step 4: Extract all remaining data rows
data_rows = rows[1:]
if not data_rows:
return "Only header row found."
# Step 5: Build a list of records (dicts) using the extracted headers
records = []
for row in data_rows:
record = {}
for i in range(len(header_names)):
value = getattr(row, f"c{i+1}")
record[header_names[i]] = str(value).strip() if value not in [None, ""] else None
records.append(record)
# Step 6: Serialize the records list as JSON string using orjson
# Default=str handles values like datetime or decimal types
attribute_data = orjson.dumps(records,default=str).decode("utf-8")
# Step 7: Call the DataPancake procedure to update the attribute metadata
result = session.call("DATAPANCAKE.CORE.UPDATE_ATTRIBUTE_METADATA_SECURITY",USER_NAME, attribute_data, FILE_PATH)
if result == "Success":
#Step 8: Optional: Archive the input file to a new location
if ARCHIVE_PATH is not None and len(ARCHIVE_PATH) > 0 and ARCHIVE_PATH != FILE_PATH:
session.sql(f"COPY FILES INTO '{ARCHIVE_PATH}' FROM '{FILE_PATH}'").collect()
#Step 9: Optional - Remove the source file from the stage
session.sql(f"REMOVE '{FILE_PATH}'").collect()
#Step 10: Return message showing the successful result
return f"The DataPancake attribute metadata has been successfully updated and the file has been archived. Processed {len(records)} rows with columns: {', '.join(header_names)}"
else:
#Step 10: Return the error message
return f"Error: {result} and: {rows[1]}"
except Exception as e:
return f"Error: {str(e)} with a trace of: {traceback.format_exc()}"
$$;
```
### Paste the DDL and modify the database and schema name
{% hint style="info" %}
If you changed the name during install, modify DataPancake application database name.
{% endhint %}
### Execute the create stored procedure statement
### Verify the stored procedure was created successfully
## Next Steps
[Attribute Metadata Bulk Updates (Security) - Execute](/guides/how-to-guides-bulk-upsert-via-csv/usage/core-+-security-attributes-bulk-update)
# Core + Data Dictionary Attributes - Initialization
Select a stage and upload the stored procedure to enable bulk updates for both core and data dictionary attribute metadata or to generate descriptions, synonyms, and sample values via Cortex AI.
### (Optional) Create a new stage for datapancake files (or use an existing stage)
```sql
CREATE STAGE ..DATAPANCAKE_FILES;
```
For more details on creating a stage, see
### Copy the Procedure DDL
```sql
CREATE OR REPLACE PROCEDURE ..sp_update_datapancake_attribute_metadata_dictionary(
FILE_PATH string,
ARCHIVE_PATH string,
USER_NAME string
)
RETURNS string
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python','orjson')
HANDLER = 'update_datapancake_attribute_metadata'
EXECUTE AS CALLER
AS
$$
import snowflake.snowpark as snowpark
import datetime
import traceback
import orjson
def update_datapancake_attribute_metadata(session: snowpark.Session, FILE_PATH: str, ARCHIVE_PATH: str, USER_NAME: str):
try:
# Initialize the output variable
attribute_data = ""
# Step 1: Read the CSV file from the specified Snowflake stage
# Set skip_header=0 so we can manually extract and process the first row as the header
df = session.read.options({"field_delimiter": ",", "skip_header": 0}).csv(FILE_PATH)
# Step 2: Collect all rows from the file
rows = df.collect()
if not rows:
return "No rows found."
# Step 3: Extract column names from the first row (header)
# Convert column names to uppercase and strip whitespace
header_row = rows[0]
header_names = [str(getattr(header_row, f"c{i+1}")).strip().upper() for i in range(len(header_row))]
# Step 4: Extract all remaining data rows
data_rows = rows[1:]
if not data_rows:
return "Only header row found."
# Step 5: Build a list of records (dicts) using the extracted headers
records = []
for row in data_rows:
record = {}
for i in range(len(header_names)):
value = getattr(row, f"c{i+1}")
record[header_names[i]] = str(value).strip() if value not in [None, ""] else None
records.append(record)
# Step 6: Serialize the records list as JSON string using orjson
# Default=str handles values like datetime or decimal types
attribute_data = orjson.dumps(records,default=str).decode("utf-8")
# Step 7: Call the DataPancake procedure to update the attribute metadata
result = session.call("DATAPANCAKE.CORE.UPDATE_ATTRIBUTE_METADATA_DICTIONARY",USER_NAME, attribute_data, FILE_PATH)
if result == "Success":
#Step 8: Optional: Archive the input file to a new location
if ARCHIVE_PATH is not None and len(ARCHIVE_PATH) > 0 and ARCHIVE_PATH != FILE_PATH:
session.sql(f"COPY FILES INTO '{ARCHIVE_PATH}' FROM '{FILE_PATH}'").collect()
#Step 9: Optional - Remove the source file from the stage
session.sql(f"REMOVE '{FILE_PATH}'").collect()
#Step 10: Return message showing the successful result
return f"The DataPancake attribute metadata has been successfully updated and the file has been archived. Processed {len(records)} rows with columns: {', '.join(header_names)}"
else:
#Step 10: Return the error message
return f"Error: {result} and: {rows[1]}"
except Exception as e:
return f"Error: {str(e)} with a trace of: {traceback.format_exc()}"
$$;
```
### Paste the DDL and modify the database and schema name
{% hint style="info" %}
If you changed the name during install, modify DataPancake application database name.
{% endhint %}
### Execute the create stored procedure statement
### Verify the stored procedure was created successfully
## Next Steps
[Attribute Metadata Bulk Updates (Data Dictionary) - Execute](/guides/how-to-guides-bulk-upsert-via-csv/usage/core-+-data-dictionary-attributes-bulk-update)
# Datasource Metadata - Initialization
Select a stage and upload the stored procedure to enable bulk updates for datasource metadata.
### 1. (Optional) Create a new stage for datapancake files (or use an existing stage)
```sql
CREATE STAGE ..DATAPANCAKE_FILES;
```
For more details on creating a stage, see
### 2. Copy the Datasource Metadata Procedure DDL
```sql
CREATE OR REPLACE PROCEDURE ..sp_update_datapancake_datasource_metadata(
FILE_PATH string,
ARCHIVE_PATH string,
USER_NAME string
)
RETURNS string
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python','orjson')
HANDLER = 'update_datapancake_datasource_metadata'
EXECUTE AS CALLER
AS
$$
import snowflake.snowpark as snowpark
import datetime
import traceback
import orjson
def update_datapancake_datasource_metadata(session: snowpark.Session, FILE_PATH: str, ARCHIVE_PATH: str, USER_NAME: str):
try:
# Initialize the output variable
datasource_data = ""
# Step 1: Read the CSV file from the specified Snowflake stage
# Set skip_header=0 so we can manually extract and process the first row as the header
df = session.read.options({"field_delimiter": ",", "skip_header": 0}).csv(FILE_PATH)
# Step 2: Collect all rows from the file
rows = df.collect()
if not rows:
return "No rows found."
# Step 3: Extract column names from the first row (header)
# Convert column names to uppercase and strip whitespace
header_row = rows[0]
header_names = [str(getattr(header_row, f"c{i+1}")).strip().upper() for i in range(len(header_row))]
# Step 4: Extract all remaining data rows
data_rows = rows[1:]
if not data_rows:
return "Only header row found."
# Step 5: Build a list of records (dicts) using the extracted headers
records = []
for row in data_rows:
record = {}
for i in range(len(header_names)):
value = getattr(row, f"c{i+1}")
record[header_names[i]] = str(value).strip() if value not in [None, ""] else None
records.append(record)
# Step 6: Serialize the records list as JSON string using orjson
# Default=str handles values like datetime or decimal types
datasource_data = orjson.dumps(records,default=str).decode("utf-8")
# Step 7: Call the DataPancake procedure to update the datasource metadata
result = session.call("DATAPANCAKE.CORE.UPDATE_DATAPANCAKE_DATASOURCE_METADATA_CORE",USER_NAME, datasource_data, FILE_PATH)
if result.startswith("Successfully updated"):
#Step 8: Optional: Archive the input file to a new location
if ARCHIVE_PATH is not None and len(ARCHIVE_PATH) > 0 and ARCHIVE_PATH != FILE_PATH:
session.sql(f"COPY FILES INTO '{ARCHIVE_PATH}' FROM '{FILE_PATH}'").collect()
#Step 9: Optional - Remove the source file from the stage
session.sql(f"REMOVE '{FILE_PATH}'").collect()
#Step 10: Return message showing the successful result
return f"The DataPancake datasource metadata has been successfully updated and the file has been archived. Processed {len(records)} rows with columns: {', '.join(header_names)}"
else:
#Step 10: Return the error message
return f"Error: {result}"
except Exception as e:
return f"Error: {str(e)} with a trace of: {traceback.format_exc()}"
$$;
```
### Paste the DDL and modify the database and schema name
{% hint style="info" %}
If you changed the name during install, modify DataPancake application database name.
{% endhint %}
### Execute the create stored procedure statement
### Verify the stored procedure was created successfully
# Array Foreign Key - Initialization
Select a stage and upload the stored procedure to enable bulk updates for array foreign key metadata metadata.
### (Optional) Create a new stage for datapancake files (or use an existing stage)
```sql
CREATE STAGE ..DATAPANCAKE_FILES;
```
For more details on creating a stage, see
### Copy the Array Foreign Key Procedure DDL
```sql
CREATE OR REPLACE PROCEDURE ..sp_update_datapancake_array_foreign_key_metadata_core(
FILE_PATH string,
ARCHIVE_PATH string,
USER_NAME string
)
RETURNS string
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python','orjson')
HANDLER = 'update_datapancake_array_foreign_key_metadata'
EXECUTE AS CALLER
AS
$$
import snowflake.snowpark as snowpark
import datetime
import traceback
import orjson
def update_datapancake_array_foreign_key_metadata(session: snowpark.Session, FILE_PATH: str, ARCHIVE_PATH: str, USER_NAME: str):
try:
# Initialize the output variable
foreign_key_data = ""
# Step 1: Read the CSV file from the specified Snowflake stage
# Set skip_header=0 so we can manually extract and process the first row as the header
df = session.read.options({"FIELD_DELIMITER": ",","TRIM_SPACE":True,"SKIP_HEADER": 0, "FIELD_OPTIONALLY_ENCLOSED_BY":'"'}).csv(FILE_PATH)
# Step 2: Collect all rows from the file
rows = df.collect()
if not rows:
return "No rows found."
# Step 3: Extract column names from the first row (header)
# Convert column names to uppercase and strip whitespace
header_row = rows[0]
header_names = [str(getattr(header_row, f"c{i+1}")).strip().upper() for i in range(len(header_row))]
# Step 4: Extract all remaining data rows
data_rows = rows[1:]
if not data_rows:
return "Only header row found."
# Step 5: Build a list of records (dicts) using the extracted headers
records = []
for row in data_rows:
record = {}
for i in range(len(header_names)):
value = getattr(row, f"c{i+1}")
record[header_names[i]] = str(value) if value not in [None, ""] else None
records.append(record)
# Step 6: Serialize the records list as JSON string using orjson
# Default=str handles values like datetime or decimal types
foreign_key_data = orjson.dumps(records,default=str).decode("utf-8")
# Step 7: Call the DataPancake procedure to update the array foreign key metadata
result = session.call("datapancake.core.update_array_foreign_key_metadata_core",USER_NAME, foreign_key_data, FILE_PATH)
# Use substring matching to be more flexible
if "Success" in str(result):
#Step 8: Optional: Archive the input file to a new location
if ARCHIVE_PATH is not None and len(ARCHIVE_PATH) > 0 and ARCHIVE_PATH != FILE_PATH:
session.sql(f"COPY FILES INTO '{ARCHIVE_PATH}' FROM '{FILE_PATH}'").collect()
#Step 9: Optional - Remove the source file from the stage
session.sql(f"REMOVE '{FILE_PATH}'").collect()
#Step 10: Return message showing the successful result
return f"The DataPancake array foreign key metadata has been successfully updated and the file has been archived. Processed {len(records)} rows with columns: {', '.join(header_names)}"
else:
#Step 10: Return the error message
return f"Error: {result} and: {rows[1]}"
except Exception as e:
return f"Error: {str(e)} with a trace of: {traceback.format_exc()}"
$$;
```
### Paste the DDL and modify the database and schema name
{% hint style="info" %}
If you changed the name during install, modify DataPancake application database name.
{% endhint %}
### Execute the create stored procedure statement
### Verify the stored procedure was created successfully
# Schema Transformation / Consolidation - Intialization
Select a stage and upload the stored procedure to enable bulk updates for schema transformation metadata.
### (Optional) Create a new stage for datapancake files (or use an existing stage)
```sql
CREATE STAGE ..DATAPANCAKE_FILES;
```
For more details on creating a stage, see
### Copy the Schema Transformation Procedure DDL
```sql
CREATE OR REPLACE PROCEDURE ..sp_update_datapancake_datasource_schema_transformation_metadata_core(
FILE_PATH string,
ARCHIVE_PATH string,
USER_NAME string
)
RETURNS string
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python','orjson')
HANDLER = 'update_datapancake_datasource_schema_transformation_metadata'
EXECUTE AS CALLER
AS
$$
import snowflake.snowpark as snowpark
import datetime
import traceback
import orjson
def update_datapancake_datasource_schema_transformation_metadata(session: snowpark.Session, FILE_PATH: str, ARCHIVE_PATH: str, USER_NAME: str):
try:
# Initialize the output variable
transformation_data = ""
# Step 1: Read the CSV file from the specified Snowflake stage
# Set skip_header=0 so we can manually extract and process the first row as the header
df = session.read.options({"FIELD_DELIMITER": ",","TRIM_SPACE":True,"SKIP_HEADER": 0, "FIELD_OPTIONALLY_ENCLOSED_BY":'"'}).csv(FILE_PATH)
# Step 2: Collect all rows from the file
rows = df.collect()
if not rows:
return "No rows found."
# Step 3: Extract column names from the first row (header)
# Convert column names to uppercase and strip whitespace
header_row = rows[0]
header_names = [str(getattr(header_row, f"c{i+1}")).strip().upper() for i in range(len(header_row))]
# Step 4: Extract all remaining data rows
data_rows = rows[1:]
if not data_rows:
return "Only header row found."
# Step 5: Build a list of records (dicts) using the extracted headers
records = []
for row in data_rows:
record = {}
for i in range(len(header_names)):
value = getattr(row, f"c{i+1}")
record[header_names[i]] = str(value) if value not in [None, ""] else None
records.append(record)
# Step 6: Serialize the records list as JSON string using orjson
# Default=str handles values like datetime or decimal types
transformation_data = orjson.dumps(records,default=str).decode("utf-8")
# Step 7: Call the DataPancake procedure to update the schema transformation metadata
result = session.call("DATAPANCAKE.CORE.UPDATE_DATASOURCE_SCHEMA_TRANSFORMATION_CORE",USER_NAME, transformation_data, FILE_PATH)
if result.startswith("Success"):
#Step 8: Optional: Archive the input file to a new location
if ARCHIVE_PATH is not None and len(ARCHIVE_PATH) > 0 and ARCHIVE_PATH != FILE_PATH:
session.sql(f"COPY FILES INTO '{ARCHIVE_PATH}' FROM '{FILE_PATH}'").collect()
#Step 9: Optional - Remove the source file from the stage
session.sql(f"REMOVE '{FILE_PATH}'").collect()
#Step 10: Return message showing the successful result
return f"The DataPancake schema transformation metadata has been successfully updated and the file has been archived. Processed {len(records)} rows with columns: {', '.join(header_names)}"
else:
#Step 10: Return the error message
return f"Error: {result}"
except Exception as e:
return f"Error: {str(e)} with a trace of: {traceback.format_exc()}"
$$;
```
### Paste the DDL and modify the database and schema name
{% hint style="info" %}
If you changed the name during install, modify DataPancake application database name.
{% endhint %}
### Execute the create stored procedure statement
### Verify the stored procedure was created successfully
# Data Source + CodeGen + Data Dictionary
# Scan Configuration
# Virtual Attributes
# Semantic Model
# Usage
# Core Attributes - Bulk Update
Select and export the attribute metadata to a .csv file, edit in a spreadsheet like Google Sheets or Excel, then perform a bulk update for core attribute metadata in DataPancake.
{% hint style="info" %}
### Ensure you have completed [Attribute Metadata Bulk Updates (Core) - Setup](/guides/how-to-guides-bulk-upsert-via-csv/database-object-initialization/core-attributes-initialization)
{% endhint %}
### Open a new SQL file Snowflake workspace
### Run a select statement for datapancake.core.vw\_datasource\_all\_attributes
The minimum field requirements to select are `attribute_id, polymorphic_version_id` - for example:
```sql
select ATTRIBUTE_ID, POLYMORPHIC_VERSION_ID
from datapancake.core.vw_datasource_attribute_security
where version_status = 'active';
```
#### Select statement for updating attributes where the source data type is an array
select
-- VIEW ONLY COLUMNS
DATASOURCE_ID,
ATTRIBUTE_ID,
POLYMORPHIC_VERSION_ID,
DATASOURCE_NAME,
DATASOURCE_TAG,
VERSION_NUMBER,
VERSION_STATUS_DATE,
PARENT_OBJECT,
PARENT_ARRAY,
ATTRIBUTE_PATH,
ATTRIBUTE_NAME,
ATTRIBUTE_LEVEL,
ATTRIBUTE_ORDER,
SOURCE_DATA_TYPE,
POLYMORPHIC_ATTRIBUTE_NAME,
ARRAY_TYPE,
ARRAY_PRIMITIVE_TYPE,
-- EDITABLE COLUMNS (Array-specific)
INCLUDE_ARRAY_IN_CODE_GEN, --TRUE, FALSE
ARRAY_RELATIONSHIP_NAME,
ARRAY_RELATIONSHIP_DESCRIPTION,
ARRAY_RELATIONSHIP_TYPE, --many_to_one, one_to_one
ARRAY_RELATIONSHIP_JOIN_TYPE, --left_outer, inner
SEMANTIC_LAYER_ALIAS_NAME,
FLATTEN_ARRAY, --TRUE, FALSE
-- AUDIT COLUMNS
ATTRIBUTE_LAST_UPDATED_DATETIME,
ATTRIBUTE_LAST_UPDATED_BY
from datapancake.core.vw_datasource_all_attributes
where version_status = 'active'
and SOURCE_DATA_TYPE = 'array'
order by attribute_path;
#### Select statement for updating attributes where source data types are primitive and object
```sql
select
-- VIEW ONLY COLUMNS
DATASOURCE_ID,
ATTRIBUTE_ID,
POLYMORPHIC_VERSION_ID,
DATASOURCE_NAME,
DATASOURCE_TAG,
VERSION_NUMBER,
VERSION_STATUS_DATE,
PARENT_OBJECT,
PARENT_ARRAY,
ATTRIBUTE_PATH,
ATTRIBUTE_NAME,
ATTRIBUTE_LEVEL,
ATTRIBUTE_ORDER,
SOURCE_DATA_TYPE,
POLYMORPHIC_ATTRIBUTE_NAME,
SAMPLE_VALUE,
HAS_EMBEDDED_CONTENT,
DATA_PLATFORM_DATA_TYPE,
-- EDITABLE COLUMNS (Non-array attributes)
ATTRIBUTE_RECORD_STATUS,
INCLUDE_COLUMN_IN_SECURE_VIEW,
NUMERIC_PRECISION,
NUMERIC_SCALE,
USE_DATETIME_FORMAT,
DATETIME_FORMAT,
ATTRIBUTE_RECORD_STATUS,
IS_UNIQUE,
IS_PRIMARY_KEY,
CONTAINS_ENUM_VALUES,
RAW_TRANSFORMATION_TYPE,
RAW_TRANSFORMATION_EXPRESSION,
RAW_TRANSFORMATION_EXPRESSION_COMMENT,
RAW_NULL_VALUE_EXPRESSION,
SEMANTIC_LAYER_TRANSFORMATION_EXPRESSION,
SEMANTIC_LAYER_TRANSFORMATION_EXPRESSION_COMMENT,
RAW_ALIAS_NAME,
SEMANTIC_LAYER_ALIAS_NAME,
CONSOLIDATION_REGEX_SEARCH,
CONSOLIDATION_SQL_EXPRESSION,
-- AUDIT COLUMNS
ATTRIBUTE_LAST_UPDATED_DATETIME,
ATTRIBUTE_LAST_UPDATED_BY
from datapancake.core.vw_datasource_all_attributes
where version_status = 'active'
and SOURCE_DATA_TYPE != 'array'
order by attribute_path;
```
{% hint style="info" %}
You can choose which editable fields you wish to include in your select statement.
{% endhint %}
### Verify the query successfully returns a list of attribute metadata
### Download the results as a .csv file
### Open the .csv file in either Excel or Google Sheets
### Edit any editable columns as needed
### Save (Excel) or download (Google Sheets) the file
### Load the .csv into your stage
### Run the bulk update stored procedure
{% hint style="info" %}
Modify the \ with the same database and schema you uploaded the stored procedure to.
{% endhint %}
When the stored procedure completes, the attribute metadata file will be moved to the archive folder.
```sql
call ..sp_update_datapancake_attribute_metadata_core(
'@/.csv',
'@//',
''
);
```
### Verify the integration completed successfully by running the following query
```sql
select * from datapancake.core.vw_integration_process
where user_name = 'BWOLFF'
order by integration_process_date desc;
```
# Core + Security Attributes - Bulk Update
Select and export the attribute metadata to a .csv file, edit in a spreadsheet like Google Sheets or Excel, then perform a bulk update for core and security attribute metadata in DataPancake.
{% hint style="info" %}
### Ensure you have completed [Attribute Metadata Bulk Updates (Security) - Setup](/guides/how-to-guides-bulk-upsert-via-csv/database-object-initialization/core-+-security-attributes-initialization)
{% endhint %}
### Open a new SQL file Snowflake workspace
### Run a select statement for DATAPANCAKE.CORE.VW\_DATASOURCE\_ATTRIBUTE\_SECURITY
The minimum field requirements to select are `attribute_id, polymorphic_version_id` - for example:
```sql
select ATTRIBUTE_ID, POLYMORPHIC_VERSION_ID
from datapancake.core.vw_datasource_attribute_security
where version_status = 'active';
```
#### Select statement for security updates where source data type is an array
```sql
select
-- VIEW ONLY COLUMNS
DATASOURCE_ID,
ATTRIBUTE_ID,
POLYMORPHIC_VERSION_ID,
DATASOURCE_NAME,
DATASOURCE_TAG,
VERSION_NUMBER,
VERSION_STATUS_DATE,
PARENT_OBJECT,
PARENT_ARRAY,
ATTRIBUTE_PATH,
ATTRIBUTE_NAME,
ATTRIBUTE_LEVEL,
ATTRIBUTE_ORDER,
SOURCE_DATA_TYPE,
POLYMORPHIC_ATTRIBUTE_NAME,
ARRAY_TYPE,
ARRAY_PRIMITIVE_TYPE,
-- SECURITY EDITABLE COLUMNS (Array-specific)
INCLUDE_ARRAY_IN_CODE_GEN,
ARRAY_RELATIONSHIP_NAME,
ARRAY_RELATIONSHIP_DESCRIPTION,
ARRAY_RELATIONSHIP_TYPE,
ARRAY_RELATIONSHIP_JOIN_TYPE,
SEMANTIC_LAYER_ALIAS_NAME,
FLATTEN_ARRAY,
ARRAY_ROW_ACCESS_POLICY_NAME,
-- AUDIT COLUMNS
ATTRIBUTE_LAST_UPDATED_DATETIME,
ATTRIBUTE_LAST_UPDATED_BY
from datapancake.core.vw_datasource_attribute_security
where version_status = 'active'
and SOURCE_DATA_TYPE = 'array'
order by attribute_path;
```
#### Select statement for updating attributes where source data types are primitive and object
{% hint style="info" %}
You can choose which editable fields you wish to include in your select statement.
{% endhint %}
### Verify the query successfully returns a list of attribute metadata
### Download the results as a .csv file
### Open the .csv file in either Excel or Google Sheets
### Edit any editable columns as needed
### Save (Excel) or download (Google Sheets) the file
### Load the .csv into your stage
### Run the bulk update stored procedure
{% hint style="info" %}
Modify the \ with same database and schema you uploaded the stored procedure to.
{% endhint %}
When the stored procedure completes, the attribute metadata file will be moved to the archive folder.
```sql
call ..sp_update_datapancake_attribute_metadata_security(
'@/.csv',
'@//',
''
);
```
### Verify the integration completed successfully by running the following query
```sql
select * from datapancake.core.vw_integration_process
where user_name = 'BWOLFF'
order by integration_process_date desc;
```
# Core + Data Dictionary Attributes - Bulk Update
Export the attribute metadata to a .csv file, edit in a spreadsheet like Google Sheets or Excel, then use to perform a bulk update on core & data dictionary attribute metadata in DataPancake.
{% hint style="info" %}
### Ensure you have completed [Attribute Metadata Bulk Updates (Data Dictionary) - Setup](/guides/how-to-guides-bulk-upsert-via-csv/database-object-initialization/core-+-data-dictionary-attributes-initialization)
{% endhint %}
### Open a new SQL file Snowflake workspace
### Run a select statement for DATAPANCAKE.CORE.VW\_DATASOURCE\_ATTRIBUTE\_DATA\_DICTIONARY
The minimum field requirements to select are `attribute_id, polymorphic_version_id` - for example:
```sql
select attribute_id, polymorphic_version_id
from datapancake.core.vw_datasource_attribute_data_dictionary
where version_status = 'active';
```
#### Select statement for data dictionary updates where source data type is an array
```sql
select
-- VIEW ONLY COLUMNS
DATASOURCE_ID,
ATTRIBUTE_ID,
POLYMORPHIC_VERSION_ID,
DATASOURCE_NAME,
DATASOURCE_TAG,
VERSION_NUMBER,
VERSION_STATUS_DATE,
PARENT_OBJECT,
PARENT_ARRAY,
ATTRIBUTE_PATH,
ATTRIBUTE_NAME,
ATTRIBUTE_LEVEL,
ATTRIBUTE_ORDER,
SOURCE_DATA_TYPE,
POLYMORPHIC_ATTRIBUTE_NAME,
ARRAY_TYPE,
ARRAY_PRIMITIVE_TYPE,
-- EDITABLE COLUMNS (Array-specific)
INCLUDE_ARRAY_IN_CODE_GEN,
ARRAY_RELATIONSHIP_NAME,
ARRAY_RELATIONSHIP_DESCRIPTION,
ARRAY_RELATIONSHIP_TYPE,
ARRAY_RELATIONSHIP_JOIN_TYPE,
SEMANTIC_LAYER_ALIAS_NAME,
ARRAY_REQUIRE_FLATTEN, // TODO: Update
ATTRIBUTE_VERSION_DESCRIPTION_PROMPT,
ATTRIBUTE_VERSION_DESCRIPTION,
ATTRIBUTE_VERSION_SYNONYMS,
-- DERIVED COLUMNS FOR BULK CORTEX AI GENERATION
GENERATE_DESCRIPTION,
GENERATE_SYNONYMS,
GENERATE_SAMPLE_VALUES,
DESCRIPTION_WORD_LIMIT,
SYNONYM_LIMIT,
SAMPLE_VALUE_LIMIT,
MODEL_NAME,
-- AUDIT COLUMNS
ATTRIBUTE_LAST_UPDATED_DATETIME,
ATTRIBUTE_LAST_UPDATED_BY
from datapancake.core.vw_datasource_attribute_data_dictionary
where version_status = 'active'
and SOURCE_DATA_TYPE = 'array'
order by attribute_path;
```
#### Select statement for updating attributes where source data types are primitive and object
```sql
select
-- VIEW ONLY COLUMNS
DATASOURCE_ID,
ATTRIBUTE_ID,
POLYMORPHIC_VERSION_ID,
DATASOURCE_NAME,
DATASOURCE_TAG,
VERSION_NUMBER,
VERSION_STATUS_DATE,
PARENT_OBJECT,
PARENT_ARRAY,
ATTRIBUTE_PATH,
ATTRIBUTE_NAME,
ATTRIBUTE_LEVEL,
ATTRIBUTE_ORDER,
SOURCE_DATA_TYPE,
POLYMORPHIC_ATTRIBUTE_NAME,
SAMPLE_VALUE,
HAS_EMBEDDED_CONTENT,
DATA_PLATFORM_DATA_TYPE,
-- EDITABLE COLUMNS (Non-array attributes)
ATTRIBUTE_RECORD_STATUS,
INCLUDE_COLUMN_IN_SECURE_VIEW,
NUMERIC_PRECISION,
NUMERIC_SCALE,
USE_DATETIME_FORMAT,
DATETIME_FORMAT,
ATTRIBUTE_RECORD_STATUS,
IS_UNIQUE,
IS_PRIMARY_KEY,
CONTAINS_ENUM_VALUES,
RAW_TRANSFORMATION_TYPE,
RAW_TRANSFORMATION_EXPRESSION,
RAW_TRANSFORMATION_EXPRESSION_COMMENT,
RAW_NULL_VALUE_EXPRESSION,
SEMANTIC_LAYER_TRANSFORMATION_EXPRESSION,
SEMANTIC_LAYER_TRANSFORMATION_EXPRESSION_COMMENT,
RAW_ALIAS_NAME,
SEMANTIC_LAYER_ALIAS_NAME,
CONSOLIDATION_REGEX_SEARCH,
CONSOLIDATION_SQL_EXPRESSION,
ATTRIBUTE_VERSION_DESCRIPTION_PROMPT,
ATTRIBUTE_VERSION_DESCRIPTION,
ATTRIBUTE_VERSION_SYNONYMS,
ATTRIBUTE_VERSION_SAMPLE_VALUES,
-- DERIVED COLUMNS FOR BULK CORTEX AI GENERATION
FALSE AS GENERATE_DESCRIPTION,
FALSE AS GENERATE_SYNONYMS,
FALSE AS GENERATE_SAMPLE_VALUES,
100 AS DESCRIPTION_WORD_LIMIT,
5 AS SYNONYM_LIMIT,
3 AS SAMPLE_VALUE_LIMIT,
'' AS MODEL_NAME,
-- AUDIT COLUMNS
ATTRIBUTE_LAST_UPDATED_DATETIME,
ATTRIBUTE_LAST_UPDATED_BY
from datapancake.core.vw_datasource_attribute_data_dictionary
where version_status = 'active'
and SOURCE_DATA_TYPE != 'array'
order by attribute_path;
```
{% hint style="info" %}
You can choose which editable fields you wish to include in your select statement.
{% endhint %}
### Verify the query successfully returns a list of attribute metadata
### Download the results as a .csv file
### Open the .csv file in either Excel or Google Sheets
### Edit any editable columns as needed
{% hint style="success" %}
**Bulk Cortex AI Data Dictionary Generation**
Use **TRUE** for these attributes and DataPancake will generate new data dictionary values with the following:
`GENERATE_DESCRIPTION`, `GENERATE_SYNONYMS`, and `GENERATE_SAMPLE_VALUES` \
\
You can also modify the `DESCRIPTION_WORD_LIMIT`, `SYNONYM_LIMIT`, `SAMPLE_VALUE_LIMIT`, `MODEL_NAME`, and `PROMPT` columns to further improve the LLM values returned.
{% endhint %}
### Save (Excel) or download (Google Sheets) the file
### Load the .csv into your stage
### Run the bulk update stored procedure
{% hint style="info" %}
Modify the \ with same database and schema you uploaded the stored procedure to.
{% endhint %}
When the stored procedure completes, the attribute metadata file will be moved to the archive folder.
### Verify the integration completed successfully by running the following query
```sql
select * from datapancake.core.vw_integration_process
where user_name = 'BWOLFF'
order by integration_process_date desc;
```
# Datasources - Bulk Update
Select and export the datasource metadata to a .csv file, edit in a spreadsheet like Google Sheets or Excel, then perform a bulk update in DataPancake.
{% hint style="info" %}
### Ensure you have completed [Initialization Guide](/guides/how-to-guides-bulk-upsert-via-csv/database-object-initialization/datasource-metadata-initialization)
{% endhint %}
### Open a new SQL file Snowflake workspace
### Run a select statement for DATAPANCAKE.CORE.VW\_DATASOURCES
```sql
SELECT
-- VIEW ONLY COLUMNS
DATASOURCE_ID,
DATASOURCE_TYPE,
DATA_FORMAT,
DATASOURCE_OBJECT_TYPE,
DATASOURCE_OBJECT_ATTRIBUTE_DATA_TYPE,
DATASOURCE_DATABASE,
DATASOURCE_SCHEMA,
DATASOURCE_OBJECT_NAME,
DATASOURCE_OBJECT_ATTRIBUTE_NAME,
LAST_SCAN,
ATTRIBUTE_COUNT,
ATTRIBUTE_MAX_LEVEL,
OBJECT_COUNT,
ARRAY_COUNT,
POLYMORPHIC_ATTRIBUTE_COUNT,
EMBEDDED_CONTENT_ATTRIBUTE_COUNT,
COMPLEXITY_SCORE,
DATASOURCE_STATUS,
CONNECTION_STATUS,
VIRTUAL_ATTRIBUTE_COUNT,
SCHEMA_ATTRIBUTE_COUNT,
DISCOVERED_ATTRIBUTE_COUNT,
-- PRODUCT TIER FLAGS
PRODUCT_TIER_ATTRIBUTE_METADATA_MANAGEMENT,
PRODUCT_TIER_SQL_CODE_GENERATION,
PRODUCT_TIER_DATA_DICTIONARY_BUILDER,
PRODUCT_TIER_SEMANTIC_MODEL_CODE_GENERATION,
PRODUCT_TIER_SECURITY_POLICY_INTEGRATION,
-- EDITABLE COLUMNS FOR BULK UPDATE
DATASOURCE_NAME,
DATASOURCE_TAG,
-- OUTPUT CONFIGURATION FIELDS
OUTPUT_OBJECT_TYPE,
OUTPUT_OBJECT_PREFIX_NAME,
OUTPUT_OBJECT_COMPUTE_RESOURCE_NAME,
OUTPUT_OBJECT_INTERVAL_QUANTITY,
OUTPUT_OBJECT_INTERVAL_UNIT_TYPE,
OUTPUT_OBJECT_OPTIONAL_PARAMETERS,
OUTPUT_OBJECT_OPTIONAL_PARAMETERS_ARRAY,
OUTPUT_OBJECT_DEDUPLICATE,
OUTPUT_OBJECT_SECURITY_ROW_LEVEL_POLICY_NAME_SEMANTIC_LAYER,
OUTPUT_OBJECT_DATABASE_NAME,
OUTPUT_OBJECT_SCHEMA_NAME,
OUTPUT_OBJECT_ROOT_SELECT_PREFIX,
OUTPUT_OBJECT_CASE_SENSITIVE,
-- SCAN CONFIGURATION FIELDS
BASE_SCAN_COMPUTE_RESOURCE_THREAD_COUNT,
BASE_SCAN_RECORDS_PER_MINUTE,
-- METADATA TABLE CONFIGURATION FIELDS
OUTPUT_OBJECT_CREATE_METADATA_TABLE,
OUTPUT_OBJECT_METADATA_TABLE_ALLOW_UPDATE,
OUTPUT_OBJECT_METADATA_TABLE_ALLOW_DELETE,
OUTPUT_OBJECT_METADATA_TABLE_COMPUTE_RESOURCE_NAME,
OUTPUT_OBJECT_METADATA_TABLE_CRON_SCHEDULE,
OUTPUT_OBJECT_METADATA_TABLE_CRON_TIMEZONE,
OUTPUT_OBJECT_METADATA_TABLE_DATABASE_NAME,
OUTPUT_OBJECT_METADATA_TABLE_SCHEMA_NAME,
-- DATA DICTIONARY
DATASOURCE_DESCRIPTION_PROMPT,
DATASOURCE_DESCRIPTION,
-- DERIVED COLUMNS FOR BULK UPDATE
FALSE AS GENERATE_CODE,
FALSE AS GENERATE_DATASOURCE_DESCRIPTION,
'mixtral-8x7b' AS MODEL_NAME,
250 AS WORD_LIMIT,
FROM DATAPANCAKE.CORE.VW_DATASOURCES
ORDER BY DATASOURCE_NAME;
```
{% hint style="info" %}
You can choose which editable fields you wish to include in your select statement.
{% endhint %}
### Verify the query successfully returns a list of attribute metadata
### Download the results as a .csv file
### Open the .csv file in either Excel or Google Sheets
### Edit any editable columns as needed
{% hint style="success" %}
**Bulk Cortex AI Data Source Description Generation**
Use **TRUE** for `GENERATE_CODE` and DataPancake will automatically generate code for the datasource.
Use `GENERATE_DATASOURCE_DESCRIPTION` and DataPancake will automatically generate a description from the datasource\
\
You can also modify the `MODEL_NAME`, and `WORD_LIMIT`columns to further improve the LLM values returned.
{% endhint %}
### Save (Excel) or download (Google Sheets) the file
### Load the .csv into your stage
### Run the bulk update stored procedure
{% hint style="info" %}
Modify the \ with same database and schema you uploaded the stored procedure to.
{% endhint %}
When the stored procedure completes, the attribute metadata file will be moved to the archive folder.
```sql
call ..sp_update_datapancake_datasource_metadata_core(
'@/.csv',
'@//',
''
);
```
### Verify the integration completed successfully by running the following query
```sql
select * from datapancake.core.vw_integration_process
where user_name = 'BWOLFF'
order by integration_process_date desc;
```
# Array Foreign Key - Bulk Update
Select and export the array foreign key metadata to a .csv file, edit in a spreadsheet like Google Sheets or Excel, then perform a bulk update in DataPancake.
***
## Array Foreign Key - Bulk Update
{% hint style="info" %}
#### Ensure you have completed the [Array Foreign Key - Initialization](app://-/database-object-initialization/array-foreign-key-initialization) guide.
{% endhint %}
This guide explains how to export foreign key metadata, edit the metadata in a spreadsheet, and apply the changes through a CSV bulk update.
The examples use the sample **Real Estate** data source. The exact data source ID and available child tables may vary by environment.
#### Open a new SQL worksheet in Snowflake
Open Snowsight and create a new SQL worksheet.
Select a role, warehouse, database, and schema with access to the DataPancake application and the Snowflake stage used for bulk-update files.
#### Export the foreign key metadata
Run the following query to retrieve foreign key metadata from `DATAPANCAKE.CORE.VW_DATASOURCE_FOREIGN_KEYS`.
```sql
SELECT
-- REQUIRED COLUMNS FOR BULK UPDATE
FOREIGN_KEY_ID, -- Unique ID for upsert operations - use {new_id} for new records
ATTRIBUTE_ID_CHILD_TABLE,
-- VIEW ONLY COLUMNS
DATASOURCE_ID,
DATASOURCE_NAME,
PARENT_TABLE_WITHOUT_PREFIX,
CHILD_TABLE_WITHOUT_PREFIX,
CHILD_TABLE_PATH,
CHILD_TABLE_LEVEL,
-- EDITABLE COLUMNS FOR BULK UPDATE
PARENT_ATTRIBUTE_NAME,
CHILD_ATTRIBUTE_NAME,
INCLUDE_COLUMN_IN_ROW_ACCESS_POLICY,
ROW_ACCESS_POLICY_COLUMN_ORDER
FROM datapancake.core.vw_datasource_foreign_keys
--where datasource_id = ''
ORDER BY DATASOURCE_NAME, CHILD_TABLE_LEVEL, CHILD_TABLE_WITHOUT_PREFIX, PARENT_ATTRIBUTE_NAME;
```
{% hint style="info" %}
Uncomment the `WHERE` clause and enter the target data source ID to limit the export to a single data source.\
\
Filtering the export reduces the possibility of changing foreign keys for an unrelated data source.
{% endhint %}
For the Real Estate demonstration, filter the query to the sample Real Estate data source in your environment.
#### Run the query and review the results
Execute the query.
Confirm that the results contain the Real Estate data source and its child tables.
Use the following columns to identify each child table:
* `CHILD_TABLE_WITHOUT_PREFIX`
* `CHILD_TABLE_PATH`
* `CHILD_TABLE_LEVEL`
* `ATTRIBUTE_ID_CHILD_TABLE`
#### Download the results as a CSV file
Download the query results as a `.csv` file.
#### Open the CSV file
Open the downloaded file in Excel, Google Sheets, or another spreadsheet editor that preserves CSV formatting.
Confirm that the header row contains all exported columns.
#### Review the CSV columns
| Column | Usage |
| ------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------- |
| `FOREIGN_KEY_ID` | Preserve the existing identifier when updating a record. Enter `{new_id}` when creating a new foreign key. |
| `ATTRIBUTE_ID_CHILD_TABLE` | Required identifier for the child table attribute. Preserve the exported value. |
| `DATASOURCE_ID` | View-only data source identifier. Do not edit. |
| `DATASOURCE_NAME` | View-only data source name. Do not edit. |
| `PARENT_TABLE_WITHOUT_PREFIX` | View-only parent table name. Do not edit. |
| `CHILD_TABLE_WITHOUT_PREFIX` | View-only child table name. Do not edit. |
| `CHILD_TABLE_PATH` | View-only path that identifies the child table within the source structure. Do not edit. |
| `CHILD_TABLE_LEVEL` | View-only nesting level of the child table. Do not edit. |
| `PARENT_ATTRIBUTE_NAME` | Parent attribute whose value should be added to the child table. |
| `CHILD_ATTRIBUTE_NAME` | Name of the foreign key column created in the child table. |
| `INCLUDE_COLUMN_IN_ROW_ACCESS_POLICY` | Enter `TRUE` or `FALSE` to control whether the generated child-table column is included in the row access policy. |
| `ROW_ACCESS_POLICY_COLUMN_ORDER` | Enter the column's position in the row access policy when applicable. Leave blank when the column is not included in the policy. |
{% hint style="info" %}
Do not change `ATTRIBUTE_ID_CHILD_TABLE` or any of the view-only columns.\
\
When updating an existing foreign key, preserve its current `FOREIGN_KEY_ID`. Use `{new_id}` only when creating a new foreign key record.
{% endhint %}
#### Add `property_id_pk` to selected Real Estate child tables
For this demonstration, select a few of the Real Estate child tables that should receive the parent property's identifier.
For each selected child table:
1. Enter `{new_id}` in `FOREIGN_KEY_ID`.
2. Preserve the exported `ATTRIBUTE_ID_CHILD_TABLE`.
3. Enter `property_id_pk` in `PARENT_ATTRIBUTE_NAME`.
4. Enter `property_id_pk` in `CHILD_ATTRIBUTE_NAME`.
5. Enter `FALSE` in `INCLUDE_COLUMN_IN_ROW_ACCESS_POLICY`.
6. Leave `ROW_ACCESS_POLICY_COLUMN_ORDER` blank.
Example:
| FOREIGN\_KEY\_ID | ATTRIBUTE\_ID\_CHILD\_TABLE | CHILD\_TABLE\_WITHOUT\_PREFIX | PARENT\_ATTRIBUTE\_NAME | CHILD\_ATTRIBUTE\_NAME | INCLUDE\_COLUMN\_IN\_ROW\_ACCESS\_POLICY | ROW\_ACCESS\_POLICY\_COLUMN\_ORDER |
| ---------------- | --------------------------- | ----------------------------- | ----------------------- | ---------------------- | ---------------------------------------- | ---------------------------------- |
| `{new_id}` | Preserve exported value | First selected child table | `property_id_pk` | `property_id_pk` | `FALSE` |
|
| `{new_id}` | Preserve exported value | Second selected child table | `property_id_pk` | `property_id_pk` | `FALSE` |
|
| `{new_id}` | Preserve exported value | Third selected child table | `property_id_pk` | `property_id_pk` | `FALSE` |
|
{% hint style="info" %}
The table names shown in the CSV depend on the current Real Estate data source schema. Select child tables that do not already contain the intended `property_id_pk` foreign key relationship.
{% endhint %}
#### Save the CSV file
Save or download the edited spreadsheet as a CSV file.
Use a descriptive filename, such as:
```
real_estate_foreign_keys_YYYYMMDD.csv
```
{% hint style="info" %}
Confirm that the saved filename contains exactly one `.csv` extension.
The stage path used in the stored procedure call must exactly match the uploaded filename.
{% endhint %}
#### Upload the CSV to a Snowflake stage
In Snowflake, navigate to the database, schema, and stage used for DataPancake bulk-update files.
Select **+ Files** or the equivalent upload action and upload the edited CSV.
#### Verify the staged filename
Confirm that the uploaded CSV appears in the stage.
Note the exact stage path and filename. You will use both values in the stored procedure call.
#### Run the bulk update stored procedure
Run the same array foreign key bulk-update procedure used for existing foreign key files.
Replace the placeholders with values for your environment.
```sql
call ..sp_update_datapancake_array_foreign_key_metadata_core(
'@/.csv',
'@//',
''
);
```
Example structure:
```sql
call MY_DATABASE.MY_SCHEMA.sp_update_datapancake_array_foreign_key_metadata_core(
'@MY_DATABASE.MY_SCHEMA.DATAPANCAKE_FILES/real_estate_foreign_keys_YYYYMMDD.csv',
'@MY_DATABASE.MY_SCHEMA.DATAPANCAKE_FILES/ARCHIVED_FILES/',
'MY_USERNAME'
);
```
{% hint style="warning" %}
The stage path and filename must exactly match the uploaded file.
Check the following values before running the procedure:
* Database
* Schema
* Stage
* Optional folder
* Filename
* File extension
* Snowflake username
{% endhint %}
#### Review the returned processing message
Execute the stored procedure call.
The procedure returns a processing message after it evaluates the uploaded file. Review the complete returned message and confirm that the file was processed successfully.
The screenshot for this step should capture the full message returned by the current version of the stored procedure.
{% hint style="success" %}
The returned processing message is the primary confirmation that the file was accepted and processed. Review the entire message before continuing to verification.
{% endhint %}
#### Confirm that the file was archived
After a successful update, confirm that the processed CSV was moved to the configured archive folder.
The file should:
* Appear in the archive folder.
* No longer appear in its original stage location.
#### Verify the new foreign keys
Rerun the export query using the same Real Estate data source filter.
```sql
SELECT
-- REQUIRED COLUMNS FOR BULK UPDATE
FOREIGN_KEY_ID,
ATTRIBUTE_ID_CHILD_TABLE,
-- VIEW ONLY COLUMNS
DATASOURCE_ID,
DATASOURCE_NAME,
PARENT_TABLE_WITHOUT_PREFIX,
CHILD_TABLE_WITHOUT_PREFIX,
CHILD_TABLE_PATH,
CHILD_TABLE_LEVEL,
-- EDITABLE COLUMNS FOR BULK UPDATE
PARENT_ATTRIBUTE_NAME,
CHILD_ATTRIBUTE_NAME,
INCLUDE_COLUMN_IN_ROW_ACCESS_POLICY,
ROW_ACCESS_POLICY_COLUMN_ORDER
FROM datapancake.core.vw_datasource_foreign_keys
WHERE datasource_id = ''
ORDER BY DATASOURCE_NAME, CHILD_TABLE_LEVEL, CHILD_TABLE_WITHOUT_PREFIX, PARENT_ATTRIBUTE_NAME;
```
For each child table updated in the CSV, confirm that:
* `FOREIGN_KEY_ID` now contains a generated identifier instead of `{new_id}`.
* `ATTRIBUTE_ID_CHILD_TABLE` identifies the expected child table attribute.
* `PARENT_ATTRIBUTE_NAME` is `property_id_pk`.
* `CHILD_ATTRIBUTE_NAME` is `property_id_pk`.
* `INCLUDE_COLUMN_IN_ROW_ACCESS_POLICY` is `FALSE`.
* The correct child table is shown in `CHILD_TABLE_WITHOUT_PREFIX` and `CHILD_TABLE_PATH`.
#### Optional: Review the integration process
You can also review the DataPancake integration process log.
Replace `` with the username passed to the bulk-update procedure.
```
SELECT *
FROM datapancake.core.vw_integration_process
WHERE user_name = ''
ORDER BY integration_process_date DESC;
```
Review the latest entry associated with the uploaded CSV.
***
#### Troubleshooting
**The staged file could not be found**
Confirm that:
* The file was uploaded successfully.
* The stage path is correct.
* The filename in the procedure call exactly matches the staged filename.
* The filename does not contain a duplicated `.csv` extension.
* The active Snowflake role has access to the stage.
You can list the staged files to confirm the exact filename:
```
LIST @..;
```
**A new foreign key was not created**
Confirm that:
* `FOREIGN_KEY_ID` contains exactly `{new_id}`.
* `ATTRIBUTE_ID_CHILD_TABLE` contains the original exported identifier.
* `PARENT_ATTRIBUTE_NAME` contains `property_id_pk`.
* `CHILD_ATTRIBUTE_NAME` contains `property_id_pk`.
* The required column headers were not renamed.
* The intended child table row was edited.
**An existing foreign key was duplicated**
Use `{new_id}` only when creating a new foreign key.
When updating an existing relationship, preserve the generated value already present in `FOREIGN_KEY_ID`.
**The wrong child table was updated**
Use the following view-only fields together to identify the intended table before editing:
* `CHILD_TABLE_WITHOUT_PREFIX`
* `CHILD_TABLE_PATH`
* `CHILD_TABLE_LEVEL`
* `ATTRIBUTE_ID_CHILD_TABLE`
Do not rely on the child table name alone when similarly named tables exist at different nesting levels.
**The row access policy configuration was not applied**
Confirm that:
* `INCLUDE_COLUMN_IN_ROW_ACCESS_POLICY` contains `TRUE` or `FALSE`.
* `ROW_ACCESS_POLICY_COLUMN_ORDER` is populated when required.
* The data source has the required security policy features enabled.
* The configured policy supports the selected child-table column.
# Schema Transformation / Consolidation - Bulk Update
Select and export the scema transformation metadata to a .csv file, edit in a spreadsheet like Google Sheets or Excel, then perform a bulk update in DataPancake.
{% hint style="info" %}
### Ensure you have completed [Initialization Guide](/guides/how-to-guides-bulk-upsert-via-csv/database-object-initialization/schema-transformation-consolidation-intialization)
{% endhint %}
### Open a new SQL file Snowflake workspace
### Run a select statement for DATAPANCAKE.CORE.VW\_DATASOURCE\_SCHEMA\_CONSOLIDATION
```sql
SELECT
-- REQUIRED COLUMNS FOR BULK UPDATE
TRANSFORMATION_ID, -- Unique ID for upsert operations - use {new_id} for new records
DATASOURCE_ID,
-- VIEW ONLY COLUMNS
DATASOURCE_NAME,
DATASOURCE_TAG,
-- EDITABLE COLUMNS FOR BULK UPDATE
TRANSFORMATION_NAME,
TRANSFORMATION_TYPE,
SCHEMA_ATTRIBUTE_PATH_SEARCH,
SCHEMA_REGULAR_EXPRESSION_SEARCH,
SCHEMA_REGULAR_EXPRESSION_REPLACE,
STRING_DELIMITER_VALUE,
STRING_KEY_VALUE_SEPARATOR,
STRING_WRAPPER_VALUE,
STRING_FIELD_WIDTHS
FROM datapancake.core.vw_datasource_schema_transformation
ORDER BY DATASOURCE_NAME, TRANSFORMATION_NAME;
```
{% hint style="info" %}
You can choose which editable fields you wish to include in your select statement.
{% endhint %}
### Verify the query successfully returns a list of attribute metadata
### Download the results as a .csv file
### Open the .csv file in either Excel or Google Sheets
### Edit any editable columns as needed
### Save (Excel) or download (Google Sheets) the file
### Load the .csv into your stage
### Run the bulk update stored procedure
{% hint style="info" %}
Modify the \ with same database and schema you uploaded the stored procedure to.
{% endhint %}
When the stored procedure completes, the attribute metadata file will be moved to the archive folder.
```sql
call ..sp_update_datapancake_datasource_schema_transformation_metadata_core(
'@/.csv',
'@//',
''
);
```
### Verify the integration completed successfully by running the following query
```sql
select * from datapancake.core.vw_integration_process
where user_name = 'BWOLFF'
order by integration_process_date desc;
```
# temp
# Miscellaneous Guides
# How to Install DataPancake
Purchase and Install the DataPancake Snowflake Native App into an existing Snowflake account.
### Navigate to the Snowflake Marketplace
### Search for "DataPancake"
### Click the DataPancake listing
### Click "See Pricing"
### Explore the pricing model, then click "Try Now"
To learn more about standard DataPancake pricing, visit the DataPancake [pricing page](https://www.datapancake.com/pricing).
{% hint style="info" %}
You may be eligible to use the MarketPlace Capacity Drawdown to pay for DataPancake. Contact your Snowflake account executive to sign a Marketplace Capacity Drawdown agreement.
{% endhint %}
### (Optional) Modify the name of the application
The default is DataPancake
### Click "Start Trial"
### After 5-7 minutes, DataPancake will be installed in your environment and ready to use
Navigate to the Catalog and select Apps, then you can "Browse all apps". The installed version of DataPancake will be available to launch from here when it is ready.
Make sure to refresh your browser after \~7 minutes to see if the application is installed.
You may also need to refresh DataPancake itself after opening for the first time. You will also need to select a warehouse when DataPancake is opened if you didn't already.
# How to Upgrade DataPancake from the Free Trial
Upgrade DataPancake from a free trial to use all of its features.
### From DataPancake Click the "Go to Listing" button
Alternatively, you can go to directly to the listing from the Snowflake Marketplace.
### Click "See Pricing"
### Review the pricing model and click buy
{% hint style="success" %}
**You are not charged until you enable and use paid features for a specific data source.**
See the [pricing page](https://www.datapancake.com/pricing) and the [guide on enabling features](/guides/how-to-guides-ui/modify-enabled-features-for-a-data-source) as needed.
{% endhint %}
{% hint style="success" %}
**The scanning/discovering, schema summary information, and summary schema drift alert are always free.**
{% endhint %}
### Verify the Purchase was Successful
If you have any issues, please [contact us](https://www.datapancake.com/contact-us) for more help.
# JSON Tutorial UDFs & Security Policies
Copy and deploy these in a workspace for the JSON Tutorial.
```sql
CREATE OR REPLACE ROLE PLANT_ACCESS_NA;
CREATE OR REPLACE ROLE PLANT_ACCESS_EU;
CREATE OR REPLACE TABLE DEMO_PHARMA_MANUFACTURING_PLANT_ACCESS (
role_name STRING,
plant_code STRING);
-- Example grants
INSERT INTO DEMO_PHARMA_MANUFACTURING_PLANT_ACCESS(role_name, plant_code) VALUES
('PLANT_ACCESS_NA','PLT-AZ01'),
('PLANT_ACCESS_NA','PLT-NJ02'),
('PLANT_ACCESS_EU','PLT-IE03');
-- Row access policy using role() to look up entitlements
CREATE OR REPLACE ROW ACCESS POLICY rap_by_plant
AS (plant_code STRING) RETURNS BOOLEAN ->
'ACCOUNTADMIN' = CURRENT_ROLE()
OR
EXISTS (
SELECT 1
FROM DEMO_PHARMA_MANUFACTURING_PLANT_ACCESS pa
WHERE pa.plant_code = plant_code
AND UPPER(pa.role_name) IN (
SELECT UPPER(value::string)
FROM TABLE(
FLATTEN(INPUT => PARSE_JSON(CURRENT_AVAILABLE_ROLES()))
)
)
);
CREATE OR REPLACE FUNCTION mask_phone(raw STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
CASE
WHEN raw IS NULL THEN NULL
WHEN LENGTH(raw) <= 4 THEN '****'
ELSE CONCAT(REPEAT('*', GREATEST(LENGTH(raw)-4,0)), RIGHT(raw, 4))
END
$$;
-- Policy for phone
CREATE OR REPLACE MASKING POLICY mp_customer_phone AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN') THEN val
ELSE mask_phone(val)
END;
-- Policy for email
CREATE OR REPLACE MASKING POLICY mp_customer_email AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN') THEN val
ELSE REGEXP_REPLACE(val, '(^.).+(@.+$)', '\\1***\\2') -- a***@domain
END;
-------Data Enrichment
-- UDF: standardize part number to uppercase and zero-pad the middle block
CREATE OR REPLACE FUNCTION udf_std_part_number(pn STRING)
RETURNS STRING
LANGUAGE SQL
IMMUTABLE
AS
$$
CASE
WHEN pn IS NULL THEN NULL
WHEN REGEXP_LIKE(pn, '^PN-(\d+)-([A-Z])$', 'i') THEN
UPPER('PN-' || LPAD(REGEXP_SUBSTR(pn, 'PN-(\d+)-', 1, 1, 'i', 1), 4, '0') || '-' ||
REGEXP_SUBSTR(pn, '-([A-Z])$', 1, 1, 'i', 1))
ELSE UPPER(pn)
END
$$;
CREATE OR REPLACE FUNCTION udf_email_domain(email STRING)
RETURNS STRING
LANGUAGE SQL
IMMUTABLE
AS
$$
CASE
WHEN email IS NULL THEN NULL
-- trim whitespace and lowercase
WHEN REGEXP_LIKE(LOWER(TRIM(email)), '^[^@]+@[^@]+$') THEN
LOWER(SPLIT_PART(TRIM(email), '@', 2))
ELSE NULL
END
$$;
```
# FAQ
Frequently Asked Questions
## FAQ
#### 1. How do we ensure data quality when loading files?
If you use the **DataPancake XML Chunker** to load data, it reports a **hash value** you can check against a hash of the raw file.
* A provided **stored procedure** can scan dynamic table columns for fields that are 100% null, which indicates potential issues.
* DataPancake supports **custom null expressions** to make quality checks easier.
* For deeper checks, you can:
* Count XML elements in the raw file and compare them to rows in the final materialized tables.
* Validate transformation expressions (e.g., formulas or UDFs) to avoid incorrect results.
***
#### 2. Can we add metadata columns (e.g., filename, run date) to dynamic tables?
Yes. You can add **virtual attributes** at the root or within any nested array. These can be populated using any valid SQL expression, including values from other source columns.
***
#### 3. How does the automated XML file ingestion process work?
We provide SQL code to automate the pipeline:
* A task checks for new files in ADLS.
* When a new file arrives, the **XML Chunker UDTF** brings the data into the **raw staging table** using a **variant column**.
***
#### 4. Can we clean up dynamic tables with aliases, PKs, and FKs?
Yes. You can alias:
* Raw dynamic table names
* Dynamic column names
* Primary/foreign key column names
* View names and view column names
This allows you to simplify naming and hide unnecessary array data when creating views.
# Warehouses
Snowflake virtual warehouses configured for DataPancake scanning operations, with specific requirements for Snowpark-Optimized warehouses and concurrency settings.
### Overview
Warehouses in DataPancake refer to Snowflake virtual warehouses that you make available to the application as compute resources for scanning operations. Each scan configuration must be assigned to a specific warehouse, and each warehouse can only support one scan at a time.
**DataPancake-Specific Constraints:**
* **One scan per warehouse** - Each warehouse processes one scan at a time; multiple warehouses required for concurrent scanning
* **Snowpark-Optimized required** - DataPancake uses Snowpark Python procedures; standard warehouses are not recommended
* **MAX\_CONCURRENCY\_LEVEL = 1** - Required for all Snowpark-Optimized warehouses to prevent memory contention
* **60-minute timeout** - Snowflake native app limitation; scans exceeding 60 minutes are automatically terminated
***
### Warehouse Configuration
#### Supported Warehouse Sizes
DataPancake supports Snowpark-Optimized warehouses. Thread count defaults are determined by `AVAILABLE_CORES` from the `compute_resource_size` table:
* **X-Small**: 8 threads
* **Small**: 16 threads
* **Medium Snowpark Optimized**: 32 threads (recommended for most use cases)
* **Large Snowpark Optimized**: 64 threads
* **X-Large Snowpark Optimized**: 128 threads
* **2X-Large Snowpark Optimized**: 256 threads
* **3X-Large through 6X-Large**: 512-4096 threads
**Size Recommendations:**
* **Medium Snowpark-Optimized** - Best balance of performance and cost; achieves approximately 1,000,000 records per minute; recommended starting point
* **Multiple Medium Warehouses** - More cost-effective than larger warehouses; enables concurrent scanning across multiple data sources
* **Large and 2X-Large** - Use for exceptionally large or complex data sources; diminishing returns compared to medium warehouses
* **Small** - Suitable for testing and small data sources; may struggle with complex or large data sources
#### Required Settings
**Warehouse Type:**
* **Snowpark-Optimized** - Required for DataPancake's Snowpark-based scanning
* **Standard warehouses** - Not recommended; may experience performance limitations
**MAX\_CONCURRENCY\_LEVEL = 1:**
* Required for all Snowpark-Optimized warehouses
* Prevents memory contention during Python procedure execution
* Prevents scan failures due to memory constraints
***
### Performance Characteristics
#### Scan Speed Benchmarks
**Medium Snowpark-Optimized Warehouse:**
* Approximately 1,000,000 records per minute
* Performance varies based on:
* Data source complexity (nested structures, array depth)
* Number of polymorphic variations
* Embedded JSON content
* Data source type (Internal Tables vs. External Tables)
**External Tables:**
* Slightly slower than internal tables
* Network latency and external storage access add overhead
* Still achieve high throughput on medium warehouses
**Scaling Considerations:**
* Diminishing returns as warehouse size increases
* 2X-Large warehouses don't provide significantly better performance than Medium
* Multiple medium warehouses provide better overall throughput than single large warehouses
* Cost-effectiveness favors multiple medium warehouses over fewer large ones
#### Memory Constraints
**Real Memory Limitations:**
* Python procedures have significant memory requirements
* Complex data sources with deep nesting consume more memory
* Large arrays and polymorphic variations increase memory usage
* Warehouse size directly impacts available memory
**Vertical Scaling:**
* DataPancake uses vertical scaling across threads within a warehouse
* Efficient job handling maximizes warehouse utilization
* Memory constraints are the primary limiting factor, not CPU
#### Timeout Constraints
**60-Minute Runtime Limit:**
* Snowpark procedures in Snowflake native apps have a 60-minute runtime limit
* Scans exceeding 60 minutes are automatically terminated by Snowflake
* This is a Snowflake organizational-level limitation, not a DataPancake limitation
**Handling Large Data Sources:**
* Break large scans into multiple procedure calls using Scan Configuration settings
* Set appropriate record limits per procedure call to stay within 60-minute limit
* Monitor scan duration and adjust batch sizes accordingly
***
### Naming Conventions
**Recommended Pattern:** `pancake__`
**Examples:**
* `pancake_medium_snowpark_optimized_1`
* `pancake_medium_snowpark_optimized_2`
* `pancake_large_optimized_1`
**Notes:**
* Use descriptive names indicating size and type
* Include sequential numbers for multiple warehouses of same size
* Avoid special characters that might cause issues in SQL
***
### Adding Warehouses
#### During Initial Setup
Warehouses must be created immediately following the granting of global permissions. The setup script (available via the Readme button) includes warehouse creation statements.
**Setup Script Example:**
```sql
CREATE OR REPLACE WAREHOUSE PANCAKE_MEDIUM_OPTIMIZED_01 WITH
WAREHOUSE_SIZE = 'Medium'
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
MAX_CONCURRENCY_LEVEL = 1;
```
**Setup Process:**
1. Grant global permissions to DataPancake application
2. Create warehouses using `CREATE OR REPLACE WAREHOUSE` statements
3. Warehouses are automatically registered during setup script execution via `ADD_WAREHOUSE` procedure calls
**Required Settings:**
* `WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'`
* `MAX_CONCURRENCY_LEVEL = 1`
***
#### After Initial Setup
**Post-Setup Process (3 steps, in order):**
1. **Create the Warehouse in Snowflake:**
```sql
CREATE OR REPLACE WAREHOUSE PANCAKE_LARGE_OPTIMIZED_03 WITH
WAREHOUSE_SIZE = 'Large'
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
MAX_CONCURRENCY_LEVEL = 1;
```
2. **Grant Usage to DataPancake:**
```sql
GRANT USAGE on WAREHOUSE PANCAKE_LARGE_OPTIMIZED_03 to APPLICATION PANCAKE;
```
3. **Register Warehouse in DataPancake:**
```sql
CALL PANCAKE.CORE.ADD_WAREHOUSE('PANCAKE_LARGE_OPTIMIZED_03', 'LARGE');
```
**Notes:**
* The `ADD_WAREHOUSE` procedure uses a LIKE match on warehouse size name (partial matches work, e.g., 'LARGE' matches 'Large Snowpark Optimized')
* Warehouse name matching is case-insensitive
* Duplicate warehouse names are rejected
***
#### Via DataPancake UI
**UI Process (still requires SQL for step 2):**
1. Create warehouse in Snowsight (Admin → Warehouses)
2. Grant usage via SQL: `GRANT USAGE on WAREHOUSE to APPLICATION PANCAKE;`
3. Register in DataPancake: Navigate to "Manage Warehouses", enter warehouse name (case-insensitive), select size, click "Save"
**Notes:**
* Warehouse must exist in Snowflake before registration
* Warehouse name matching in UI is case-insensitive
***
### Common Scenarios
#### Single Data Source, Regular Scanning
* Use a single medium Snowpark-Optimized warehouse
* Set scan configuration with record limit of 0 (full scan)
* Monitor scan duration to ensure it stays under 60 minutes
#### Multiple Large Data Sources
* Provision multiple medium Snowpark-Optimized warehouses
* Assign each data source to a dedicated warehouse
* Use consistent naming convention
#### Very Large Data Source Exceeding 60 Minutes
* Use multiple procedure calls in Scan Configuration
* Set appropriate record count per procedure call
* Use a medium or large warehouse depending on complexity
#### Development and Testing
* Create small Snowpark-Optimized warehouses for testing
* Use separate warehouses from production to avoid resource contention
***
### Summary
**Key Points:**
* **One scan per warehouse** - Each warehouse processes one scan at a time; multiple warehouses required for concurrent operations
* **Medium Snowpark-Optimized recommended** - Best balance of performance and cost (32 threads, \~1M records/minute)
* **MAX\_CONCURRENCY\_LEVEL = 1** - Required for all Snowpark-Optimized warehouses
* **60-minute timeout** - Snowflake native app limitation; use multiple procedure calls for large data sources
* **Thread counts** - Determined by warehouse size (X-Small: 8, Small: 16, Medium: 32, Large: 64, 2X-Large: 256, etc.)
For Snowflake warehouse documentation, see [Snowflake Virtual Warehouses](https://docs.snowflake.com/en/user-guide/warehouses-overview).
# Data Sources
Introduction to data sources and navigation hub for all data source documentation.
### Overview
Data sources connect to Snowflake database objects and define what data to scan, how to process it, and what materialized objects to generate. Data sources are the entry point for all DataPancake operations—from schema discovery through code generation and materialization.
**Key Concepts:**
1. **Source Object Connection** - Links to a specific Snowflake database object and column
2. **Data Type Classification** - Semi-Structured (VARIANT/String columns) or Structured (relational)
3. **Product Tier Management** - Enable specific features per data source
4. **Materialization Configuration** - Define output objects (dynamic tables, views, etc.)
5. **Schema Transformation** - Apply consolidations and filters during processing
6. **Baseline Performance** - Track scan performance metrics for estimation
***
### What You'll Learn
This Data Sources section covers:
* [**Data Source Types**](/core-concepts/data-sources/data-source-types) - Semi-structured and structured data sources, format types, and column data types
* [**Adding Data Sources**](/core-concepts/data-sources/adding-data-sources) - Adding via application interface or SQL file, privileges, and connection validation
* [**Basic Configuration Settings**](/core-concepts/data-sources/basic-configuration-settings) - Name, type, status, tags, and connection status
* [**Product Tiers & Features**](/core-concepts/data-sources/product-tiers-and-features) - Feature tiers from Schema Discovery to Semantic Model Generator
* [**Source Object Settings**](/core-concepts/data-sources/source-object-settings) - Object selection, column configuration, schema samples, and platform settings
* [**Materialization Settings**](/core-concepts/data-sources/materialization-settings) - Output object types, naming, deployment location, and configuration
* [**Dynamic Table Settings**](/core-concepts/data-sources/dynamic-table-settings) - Warehouse assignment, target lag, refresh modes, and metadata tables
* [**Secure View Settings**](/core-concepts/data-sources/secure-view-settings) - View types, semantic layer, and row access policy integration
* [**Schema Transformations**](/core-concepts/data-sources/schema-transformations) - Consolidation rules, transformation types, and data type transformations
* [**Schema Filters**](/core-concepts/data-sources/schema-filters) - Filter configuration, regular expressions, and attribute exclusion
* [**Baseline Scan Settings**](/core-concepts/data-sources/baseline-scan-settings) - Performance calibration and scan estimation
***
### Quick Reference
**Essential Settings:**
* **Data Source Name** - Unique identifier (case-insensitive uniqueness check)
* **Data Source Type** - Semi-Structured or Structured
* **Source Object** - Database, schema, object, and column reference
* **Status** - Active, Inactive, or Deleted
**For Semi-Structured Data Sources:**
* **Format Type** - JSON, Avro, Parquet, ORC, or XML
* **Column Data Type** - VARIANT or String
* **Column Name** - Name of the VARIANT/String column (can include parsing expressions for String type)
**Product Tiers:**
* **Schema Discovery** - Always enabled (free tier)
* **Pipeline Designer** - Foundation for all paid features (auto-enabled with others)
* **SQL Code Generation** - Enables materialization code generation (semi-structured only)
* **Additional Features** - Data Dictionary Builder, Security Policy Integration, Semantic Model Generator
**Materialization (Semi-Structured Only):**
* **Output Object Type** - Dynamic Table or Table
* **Root Table Name** - Prefix for all generated objects (required when SQL Code Generation enabled)
* **Deployment Location** - Database and schema for output objects (defaults to source location if not specified)
* **Dynamic Table Settings** - Warehouse, target lag, optional parameters (required for Dynamic Table type)
***
### Data Source Lifecycle
1. **Creation** - Add data source via application interface or SQL file (`core.add_datasource_with_scan`)
2. **Configuration** - Set product tiers, materialization settings, and transformations
3. **Initial Scan** - Perform quick scan (default 150 records) to discover schema (optional)
4. **Full Scan** - Execute comprehensive scan using scan configurations
5. **Code Generation** - Generate SQL code for materialized objects (requires SQL Code Generation feature)
6. **Deployment** - Execute generated SQL to create output objects
7. **Maintenance** - Update settings, add transformations, monitor performance
**For detailed information on scanning data sources, see the** [**Scan Configurations**](/core-concepts/scan-configurations) **documentation. For information on generated attributes, see the** [**Attribute Metadata**](/core-concepts/attribute-metadata) **documentation.**
# Adding Data Sources
Methods and requirements for adding data sources to DataPancake.
## Overview
Data sources can be added to DataPancake through two methods: the application interface or a SQL file. Both methods require appropriate Snowflake privileges to be granted first.
***
## Prerequisites
### Required Privileges
Before adding a data source, you must grant DataPancake the necessary privileges to access the source object.
For Standard Databases:
```sql
GRANT USAGE ON DATABASE TO APPLICATION PANCAKE;
GRANT USAGE ON SCHEMA . TO APPLICATION PANCAKE;
GRANT REFERENCES, SELECT ON .. TO APPLICATION PANCAKE;
```
For Shared Databases:
```sql
GRANT USAGE ON DATABASE TO APPLICATION PANCAKE;
GRANT USAGE ON SCHEMA . TO APPLICATION PANCAKE;
GRANT IMPORTED PRIVILEGES ON DATABASE TO APPLICATION PANCAKE;
```
Object Types:
* `TABLE` for tables
* `VIEW` for views
* `MATERIALIZED VIEW` for materialized views
* `EXTERNAL TABLE` for external tables
* `DYNAMIC TABLE` for dynamic tables
* `ICEBERG TABLE` for Iceberg tables
### Privilege Verification
The system validates privileges when:
* Creating a new data source
* Saving data source settings
* Attempting to scan the data source
If privileges are missing, you'll receive an error message with the exact SQL statements needed to grant access.
***
## Adding from Application Interface
The application interface provides a guided experience for adding data sources with validation and error handling.
{% stepper %}
{% step %}
#### Navigate to Data Sources
* Open the DataPancake application
* Navigate to "Data Source Management" → "Data Sources"
{% endstep %}
{% step %}
#### Select "New" Action
* Use the sidebar action selector
* Choose "New" to create a new data source
{% endstep %}
{% step %}
#### Configure Basic Settings
* Enter Data Source Name (required, unique)
* Select Data Source Type: Semi-Structured or Structured
* Set Status: Active, Inactive, or Deleted
{% endstep %}
{% step %}
#### Configure Source Object (Semi-Structured)
* Select Object Type (Table, View, External Table, etc.)
* Select Format Type (JSON, Avro, Parquet, ORC, XML)
* Select Column Data Type (VARIANT or String)
* Select Database
* Select Schema
* Select Object Name
* Enter Column Name (VARIANT/String column name)
{% endstep %}
{% step %}
#### Configure Source Object (Structured)
* Select Object Type
* Select Database
* Select Schema
* Select Object Name
{% endstep %}
{% step %}
#### Optional Configuration
* Add Data Source Tags
* Configure Product Tiers/Features
* Add Schema Sample (for Semi-Structured)
* Configure Materialization Settings (if SQL Code Generation enabled)
{% endstep %}
{% step %}
#### Save Data Source
* Click "Save Data Source" button
* System validates connection and required fields
* Success message confirms data source creation
{% endstep %}
{% endstepper %}
### Validation
The application validates:
* **Required Fields** - All required fields must be provided
* **Unique Name** - Data source name must be unique
* **Connection** - System attempts to connect to source object
* **Privileges** - Verifies required privileges are granted
### Error Handling
Connection Failures:
* System displays specific error message
* Provides SQL statements to grant required privileges
* Allows correction and retry
Validation Errors:
* Missing required fields are highlighted
* Error messages explain what's needed
* Sidebar shows list of missing fields
Duplicate Names:
* Error message indicates duplicate name
* Must choose a different name
***
## Adding via SQL file
SQL file addition enables programmatic data source creation using stored procedures. This method is ideal for bulk operations, automation, and integration with other systems.
### Stored Procedure: core.add\_datasource\_with\_scan
The primary stored procedure for adding data sources with optional initial scan.
Signature:
```sql
CALL core.add_datasource_with_scan(
DATASOURCE_NAME string,
DATASOURCE_TYPE string,
DATASOURCE_FORMAT string,
ATTRIBUTE_CREATE_TYPE string,
DATASOURCE_SCHEMA_SAMPLE string,
SOURCE_DATA_PLATFORM string,
OBJECT_TYPE string,
OBJECT_DATABASE_NAME string,
OBJECT_SCHEMA_NAME string,
OBJECT_NAME string,
OBJECT_COLUMN_DATA_TYPE string,
OBJECT_COLUMN_NAME string,
INITIATE_SCAN boolean,
SCAN_WAREHOUSE_NAME string,
SCAN_RECORD_LIMIT int,
BASE_SCAN_WAREHOUSE_THREAD_COUNT int,
BASE_SCAN_RECORDS_PER_MINUTE int,
DATASOURCE_TAG string
)
```
### Parameters
Required Parameters:
* `DATASOURCE_NAME` - Unique name for the data source
* `DATASOURCE_TYPE` - `'Semi-Structured'` or `'Structured'`
* `DATASOURCE_FORMAT` - Format type: `'JSON'`, `'Avro'`, `'Parquet'`, `'ORC'`, or `'XML'` (Semi-Structured only)
* `ATTRIBUTE_CREATE_TYPE` - `'Discover'` (scan data) or `'Schema'` (use schema sample)
* `OBJECT_TYPE` - `'Table'`, `'View'`, `'External Table'`, `'Dynamic Table'`, `'Materialized View'`, or `'Iceberg Table'`
* `OBJECT_DATABASE_NAME` - Database name containing the source object
* `OBJECT_SCHEMA_NAME` - Schema name containing the source object
* `OBJECT_NAME` - Name of the source object
* `OBJECT_COLUMN_NAME` - Column name (required for Semi-Structured)
* `OBJECT_COLUMN_DATA_TYPE` - `'Variant'` or `'String'` (required for Semi-Structured)
Optional Parameters:
* `DATASOURCE_SCHEMA_SAMPLE` - JSON/XML schema sample (for Schema attribute creation type)
* `SOURCE_DATA_PLATFORM` - `'Kafka'` or `'DynamoDB'` (for streaming platforms)
* `INITIATE_SCAN` - `TRUE` to perform initial scan, `FALSE` to skip
* `SCAN_WAREHOUSE_NAME` - Warehouse name for initial scan
* `SCAN_RECORD_LIMIT` - Number of records to scan (0 = unlimited, default 150 for quick scan)
* `BASE_SCAN_WAREHOUSE_THREAD_COUNT` - Thread count for baseline (default 8)
* `BASE_SCAN_RECORDS_PER_MINUTE` - Records per minute for baseline (default 10000)
* `DATASOURCE_TAG` - Optional tags for organization
### Example: Basic Semi-Structured Data Source
```sql
DECLARE
datasource_name VARCHAR DEFAULT 'PRODUCTION.MEDICAL_DEVICE.UDI_JSON';
object_type VARCHAR DEFAULT 'Table';
database_name VARCHAR DEFAULT 'PRODUCTION';
schema_name VARCHAR DEFAULT 'MEDICAL_DEVICE';
object_name VARCHAR DEFAULT 'UDI_JSON';
column_name VARCHAR DEFAULT 'JSON_DATA';
datasource_tag VARCHAR DEFAULT '';
warehouse_name VARCHAR DEFAULT 'PANCAKE_X_SMALL_01';
record_limit INT DEFAULT 150;
initiate_scan BOOLEAN DEFAULT TRUE;
call_result VARCHAR;
BEGIN
CALL PANCAKE.core.add_datasource_with_scan(
:datasource_name,
'Semi-Structured',
'JSON',
'Discover',
'',
'',
:object_type,
:database_name,
:schema_name,
:object_name,
'Variant',
:column_name,
:initiate_scan,
:warehouse_name,
:record_limit,
8,
10000,
:datasource_tag
) INTO :call_result;
RETURN :call_result;
END;
```
### Example: Full Scan on Creation
```sql
DECLARE
datasource_name VARCHAR DEFAULT 'PRODUCTION.MEDICAL_DEVICE.UDI_JSON';
warehouse_name VARCHAR DEFAULT 'PANCAKE_MEDIUM_OPTIMIZED_01';
record_limit INT DEFAULT 0; -- 0 = unlimited
call_result VARCHAR;
BEGIN
CALL PANCAKE.core.add_datasource_with_scan(
:datasource_name,
'Semi-Structured',
'JSON',
'Discover',
'',
'',
'Table',
'PRODUCTION',
'MEDICAL_DEVICE',
'UDI_JSON',
'Variant',
'JSON_DATA',
TRUE,
:warehouse_name,
:record_limit,
8,
10000,
''
) INTO :call_result;
RETURN :call_result;
END;
```
### Example: Using Schema Sample
```sql
DECLARE
schema_sample VARCHAR DEFAULT '{"id": "string", "name": "string", "items": [{"item_id": "string"}]}';
call_result VARCHAR;
BEGIN
CALL PANCAKE.core.add_datasource_with_scan(
'PRODUCTION.MEDICAL_DEVICE.UDI_JSON',
'Semi-Structured',
'JSON',
'Schema', -- Use schema sample
:schema_sample,
'',
'Table',
'PRODUCTION',
'MEDICAL_DEVICE',
'UDI_JSON',
'Variant',
'JSON_DATA',
FALSE, -- Don't initiate scan
'',
0,
8,
10000,
''
) INTO :call_result;
RETURN :call_result;
END;
```
### Example: Structured Data Source
```sql
DECLARE
call_result VARCHAR;
BEGIN
CALL PANCAKE.core.add_datasource_with_scan(
'PRODUCTION.CUSTOMER_DATA.ORDERS_TABLE',
'Structured',
'', -- No format for structured
'Discover',
'',
'',
'Table',
'PRODUCTION',
'CUSTOMER_DATA',
'ORDERS_TABLE',
'', -- No column for structured
'', -- No column name for structured
TRUE,
'PANCAKE_X_SMALL_01',
150,
8,
10000,
''
) INTO :call_result;
RETURN :call_result;
END;
```
### Return Values
The stored procedure returns a string indicating success or failure:
* Success: Returns success message
* Error: Returns error message with details
* Connection Failed: Returns connection error with privilege grant statements
* Duplicate Name: Returns error indicating duplicate data source name
### Bulk Data Source Creation
You can use SQL scripts to find and create data sources in bulk:
```sql
-- Example: Find all tables with VARIANT columns and generate add statements
SELECT
'CALL PANCAKE.core.add_datasource_with_scan(' ||
'''' || table_catalog || '.' || table_schema || '.' || table_name || ''',' ||
'''Semi-Structured'',' ||
'''JSON'',' ||
'''Discover'',' ||
'''',' ||
'''',' ||
'''Table'',' ||
'''' || table_catalog || ''',' ||
'''' || table_schema || ''',' ||
'''' || table_name || ''',' ||
'''Variant'',' ||
'''' || column_name || ''',' ||
'TRUE,' ||
'''PANCAKE_X_SMALL_01'',' ||
'150,' ||
'8,' ||
'10000,' ||
''''');' AS add_statement
FROM information_schema.columns
WHERE data_type = 'VARIANT'
AND table_schema NOT LIKE 'PANCAKE%';
```
***
## Initial Quick Scan
When adding a data source, you can optionally perform an initial "quick scan" to discover the schema immediately.
### Quick Scan Configuration
Default Settings:
* Record Limit: 150 records
* Warehouse: PANCAKE\_X\_SMALL\_01 (or specified warehouse)
* Attribute Create Type: Discover (scans actual data)
### Quick Scan Benefits
* Immediate Schema Discovery — See schema structure right away
* Fast Onboarding — Quick validation of data source setup
* Cost Effective — Small record limit keeps costs low
* Baseline Setup — Establishes initial attribute metadata
### When to Skip Initial Scan
Skip the initial scan when:
* You have a schema sample and want to use Schema attribute creation type
* You want to configure materialization settings first
* You're creating data sources in bulk and will scan later
* You want to review configuration before scanning
### Full Scan During Creation
To perform a full scan when creating a data source:
* Set `SCAN_RECORD_LIMIT` to `0` (unlimited) or a number greater than total records
* Use a Medium or larger warehouse (e.g., `PANCAKE_MEDIUM_OPTIMIZED_01`)
* Ensure warehouse has sufficient resources for the data size
Note: Full scans during creation may take significant time and cost. Consider using quick scan first, then performing full scans via scan configurations.
***
***
## Troubleshooting
Connection Failed
* Verify privileges are granted correctly
* Check object exists and is accessible
* For shared databases, use `GRANT IMPORTED PRIVILEGES`
* Verify database and schema names are correct
Duplicate Name
* Choose a different, unique name
* Check existing data sources for similar names
* Use more specific naming with database/schema context
Invalid Format
* Verify format type matches actual data format
* Check column data type (VARIANT vs String)
* For String columns, ensure parsing expression is correct
Scan Failures
* Verify warehouse exists and is accessible
* Check warehouse size is appropriate for data volume
* Review scan error messages for specific issues
* Start with smaller record limits
***
## Summary
Methods:
* Application Interface (guided experience)
* SQL file (programmatic/bulk)
Requirements:
* Appropriate Snowflake privileges
* Valid source object
* Unique data source name
Initial Scan:
* Optional quick scan (150 records default)
* Can skip and scan later
* Can perform full scan on creation
**Note:** Start with quick scan (default 150 records), then configure and perform full scans via scan configurations.
For information on data source types, see [Data Source Types](/core-concepts/data-sources/data-source-types). For basic configuration, see [Basic Configuration Settings](/core-concepts/data-sources/basic-configuration-settings).
# Baseline Scan Settings
Performance calibration metrics for estimating scan duration and progress.
## Overview
Baseline Scan Settings store performance metrics from previous scans to estimate future scan duration and track progress. These settings are automatically populated after the first successful scan with at least 10,000 records using an X-Small warehouse (or specified warehouse), and are used by scan configurations to calculate estimated completion times and progress percentages.
***
## Base Records Processed Per Minute
The number of records processed per minute during the baseline scan.
### Purpose
* **Performance Metric** - Measures scanning throughput
* **Estimation** - Used to estimate future scan durations
* **Progress Tracking** - Calculates scan progress percentage
* **Calibration** - Baseline for performance comparisons
### How It's Set
**Automatic Population:**
* Set after first successful scan with 10,000+ records
* Calculated from actual scan performance
* Uses X-Small warehouse as baseline
* Stored in data source configuration
**Calculation:**
* Total records scanned / scan duration in minutes
* Example: 10,000 records in 2 minutes = 5,000 records/minute
### Usage
**Scan Estimation:**
* Used by scan configurations to estimate duration
* Formula: `estimated_minutes = record_limit / records_per_minute`
* Provides users with expected scan time
**Progress Tracking:**
* Calculates percentage complete during scans
* Formula: `progress = (records_processed / total_records) * 100`
* Updates in real-time during scan execution
***
## Base Thread Count
The number of threads used during the baseline scan.
### Purpose
* **Resource Metric** - Records compute resources used
* **Calibration** - Baseline for thread count comparisons
* **Estimation** - Used in performance calculations
* **Reference** - Helps configure future scans
### How It's Set
**Automatic Population:**
* Set after first successful scan with 10,000+ records
* Uses actual thread count from baseline scan
* Typically 8 threads for X-Small warehouse
* Stored in data source configuration
**Default Value:**
* Defaults to 8 threads if not set
* Can be manually adjusted if needed
* Should match warehouse thread capacity
### Usage
**Performance Reference:**
* Reference for configuring scan thread counts
* Helps determine optimal thread configuration
* Used in performance estimation calculations
***
## Setting Baseline Values
### Automatic Setting
**Requirements:**
* Scan with at least 10,000 records
* Use X-Small warehouse (or specified warehouse)
* Successful scan completion
* System automatically calculates and stores values
{% stepper %}
{% step %}
#### Perform a baseline scan
Perform a scan with 10,000+ records using the specified warehouse.
{% endstep %}
{% step %}
#### System calculates records per minute
The system computes records per minute from the scan duration and total records scanned.
{% endstep %}
{% step %}
#### System records thread count
The system records the actual thread count used during the baseline scan.
{% endstep %}
{% step %}
#### Values stored in data source configuration
Calculated values (records/minute and thread count) are saved to the data source configuration and become available for future scan estimations.
{% endstep %}
{% endstepper %}
### Manual Setting
**When to Manually Set:**
* Baseline values are inaccurate
* Performance has changed significantly
* Want to use different baseline
* Testing different configurations
**How to Set:**
* Edit data source configuration
* Enter values in Baseline Scan Settings
* Save data source
* Values used for future estimations
***
## Resetting Baseline Settings
### Reset to Zero
**Purpose:**
* Clear existing baseline values
* Force recalculation on next scan
* Reset to default values
* Start fresh baseline calibration
**When to Reset:**
* Baseline values are inaccurate
* Performance characteristics changed
* Warehouse configuration changed
* Want to recalibrate
**How to Reset:**
* Click "Reset Baseline Scan Settings" button
* Values set to 0
* Next scan with 10,000+ records will set new baseline
* System recalculates from new scan
### Default Values
**When Baseline is Zero:**
* System uses default values for estimation
* Default: 10,000 records/minute
* Default: 8 threads
* Estimates may be less accurate
**After Reset:**
* Next substantial scan sets new baseline
* System uses actual performance metrics
* More accurate estimations going forward
***
## Impact on Scan Configurations
### Estimation Accuracy
**With Baseline:**
* Estimates based on actual performance
* More accurate duration predictions
* Better progress tracking
* Calibrated to your data and warehouse
**Without Baseline:**
* Uses default values
* Less accurate estimations
* May over/under estimate duration
* Generic performance assumptions
### Progress Tracking
**With Baseline:**
* Real-time progress based on actual throughput
* Accurate percentage calculations
* Better user experience
* Reliable progress indicators
**Without Baseline:**
* Progress based on defaults
* May be less accurate
* Still functional but less precise
***
***
## Troubleshooting
Inaccurate Estimates — Symptoms & Solutions
**Symptoms:**
* Scan duration estimates are way off
* Progress tracking seems incorrect
* Baseline values seem wrong
**Solutions:**
* Reset baseline settings
* Perform new baseline scan
* Verify warehouse configuration
* Check data complexity hasn't changed
Missing Baseline — Symptoms & Solutions
**Symptoms:**
* Baseline values are 0
* Estimates use defaults
* Less accurate predictions
**Solutions:**
* Perform scan with 10,000+ records
* Let system set baseline automatically
* Verify scan completed successfully
* Check baseline values were set
Performance Changes — Symptoms & Solutions
**Symptoms:**
* Baseline no longer accurate
* Recent scans much faster/slower
* Estimates consistently wrong
**Solutions:**
* Reset baseline settings
* Perform new baseline scan
* Review warehouse configuration
* Check for data structure changes
***
## Summary
**Core Settings:**
* Base Records Processed Per Minute (auto-set or manual)
* Base Thread Count (auto-set or manual)
**Automatic Setting:**
* Set after scan with 10,000+ records
* Uses actual scan performance
* Calibrated to your data and warehouse
**Usage:**
* Scan duration estimation
* Progress tracking
* Performance reference
**Note:** Let system set baseline automatically after first substantial scan (10,000+ records). Reset only when baseline is clearly inaccurate.
For scan configuration, see [Scan Configurations documentation](/core-concepts/scan-configurations). For data source overview, see [Overview](/core-concepts/data-sources).
# Basic Configuration Settings
Core settings for data source identification, status, and metadata.
## Overview
Basic configuration settings define the fundamental properties of a data source: its name, type, operational status, and organizational tags. These settings are required for all data sources and determine how the data source appears and functions within DataPancake.
***
## Data Source Name
A unique, descriptive identifier for the data source within your DataPancake instance.
### Requirements
* Required Field — Must be provided to save the data source
* Unique — Cannot duplicate an existing data source name (case-insensitive)
* Descriptive — Should clearly identify the data source's purpose and content
**Examples:**
* `PRODUCTION.MEDICAL_DEVICE.UDI_JSON`
* `ANALYTICS.CUSTOMER_EVENTS`
* `STAGING.API_RESPONSES_JSON`
* `WAREHOUSE.ORDERS_TABLE`
**Validation:**
* Checks for uniqueness (case-insensitive comparison)
* Returns error if duplicate name exists
* Name cannot be empty or whitespace only
***
## Data Source Type
Classification of the data source as either Semi-Structured or Structured.
### Options
Semi-Structured (Recommended for JSON/VARIANT data)
* For data stored in VARIANT columns
* Enables full schema discovery and materialization
* Supports nested objects and arrays
* Requires format type and column specification
* Enables dynamic table generation
Structured
* For standard relational database objects
* Basic schema discovery only
* No materialization features
* No format or column specification needed
* Limited to flat relational structures
### Selection Impact
The data source type determines:
* Available Configuration Options — Semi-structured sources have more settings
* Materialization Capabilities — Only semi-structured sources can generate dynamic tables
* Schema Discovery Depth — Semi-structured sources discover nested structures
* Required Fields — Semi-structured requires format type and column name
Best Practice: Choose Semi-Structured for VARIANT column data to enable full DataPancake capabilities.
For detailed information on data source types, see [Data Source Types](broken://pages/3a32caa968b71644f58268e6aebb34e7ba155ae3).
***
## Status
Operational state of the data source within DataPancake.
### Options
Active (Default)
* Data source is operational and available for scanning
* Appears in data source lists and dropdowns
* Can be used in scan configurations
* Scans can be executed against this data source
* Use for all production and active development data sources
Inactive
* Data source is preserved but not available for scanning
* Hidden from most data source selection lists
* Configuration is retained for future use
* Useful for temporarily disabling data sources
* Can be reactivated by changing status back to Active
Deleted
* Data source is marked for deletion
* Configuration may be retained for audit purposes
* Typically hidden from user interfaces
* Use when data source is no longer needed
* May be permanently removed in future cleanup operations
**Impact on Operations:**
* Only Active data sources appear in scan configuration dropdowns
* Only Active data sources can be scanned
* Inactive/Deleted data sources retain their configuration and scan history
* Status changes are logged for audit purposes
***
## Data Source Tags
Optional metadata tags for organizing and categorizing data sources.
### Purpose
Tags provide flexible organization and filtering capabilities:
* Categorization — Group related data sources
* Environment Identification — Tag as PROD, DEV, TEST, etc.
* Department/Team — Identify data source ownership
* Data Classification — Mark sensitive or public data
* Custom Organization — Any organizational scheme
### Usage
* Optional Field — Not required to save data source
* Free Text — Any text value accepted
* No Validation — System does not enforce tag format
* Search/Filter — Can be used for filtering in views and reports
**Examples:**
* `PROD, CUSTOMER_DATA, PII`
* `DEV; TEST_DATA; API_RESPONSES`
* `ANALYTICS_TEAM, PUBLIC, EVENTS`
***
## Connection Status
Read-only indicator showing whether DataPancake can successfully connect to the source object.
### Status Values
Connected
* DataPancake has successfully validated connection to source object
* Required privileges are granted
* Data source is ready for scanning
* Connection validated on save or periodic checks
Connection Failed
* DataPancake cannot access the source object
* Missing required privileges
* Object may not exist or be inaccessible
* Error message provides specific failure reason
### Connection Validation
Connection status is validated when:
* Data source is first created
* Data source settings are saved
* Periodic background validation (if enabled)
Troubleshooting Connection Failures
When connection fails, the system provides specific error messages and SQL statements to resolve privilege issues.
For Standard Databases:
```sql
GRANT USAGE ON DATABASE TO APPLICATION PANCAKE;
GRANT USAGE ON SCHEMA . TO APPLICATION PANCAKE;
GRANT REFERENCES, SELECT ON .. TO APPLICATION PANCAKE;
```
For Shared Databases:
```sql
GRANT USAGE ON DATABASE TO APPLICATION PANCAKE;
GRANT USAGE ON SCHEMA . TO APPLICATION PANCAKE;
GRANT IMPORTED PRIVILEGES ON DATABASE TO APPLICATION PANCAKE;
```
Common Issues:
* Missing USAGE privilege on database or schema
* Missing SELECT privilege on object
* Object does not exist or was renamed
* Object is in a shared database requiring IMPORTED PRIVILEGES
For detailed information on privileges, see [Adding Data Sources](broken://pages/6c9e24784a258c34149469b6ee344df363a19923).
***
## Field Requirements Summary
### Required Fields
All data sources require:
* Data Source Name — Unique identifier
* Data Source Type — Semi-Structured or Structured
* Status — Active, Inactive, or Deleted
* Object Type — Table, View, External Table, etc.
* Database — Source database name
* Schema — Source schema name
* Object Name — Source object name
### Additional Required for Semi-Structured
* Format Type — JSON, Avro, Parquet, ORC, or XML
* Column Data Type — VARIANT or String
* Column Name — Name of VARIANT/String column
### Optional Fields
* Data Source Tags — Organizational metadata
* Schema Sample — Sample JSON/XML for faster discovery (semi-structured)
***
For information on adding data sources, see [Adding Data Sources](/core-concepts/data-sources/adding-data-sources). For product tier configuration, see [Product Tiers & Features](/core-concepts/data-sources/product-tiers-and-features).
# Data Source Types
Classification and format configuration for data sources in DataPancake.
## Overview
DataPancake supports two primary data source types: **Semi-Structured** and **Structured**. The data source type determines what configuration options are available, how data is processed, and what materialization capabilities can be used. Semi-structured data sources require additional format and column type specifications, while structured data sources connect directly to relational database objects.
***
## Semi-Structured Data Sources
Semi-structured data sources connect to Snowflake objects containing JSON, Avro, Parquet, ORC, or XML data stored in VARIANT or String columns. These data sources enable DataPancake's full schema discovery and materialization capabilities.
### Supported Object Types
Semi-structured data sources can be created from:
* **Dynamic Tables** - Snowflake dynamic tables containing VARIANT columns only
* **External Tables** - External tables pointing to cloud storage (S3, Azure Blob, etc.). JSON format only.
* **Iceberg Tables** - Iceberg tables with VARIANT or STRING columns. JSON format only. STRING type is primarily for Iceberg use cases and supports JSON only (not XML).
* **Materialized Views** - Materialized views containing VARIANT columns
* **Tables** - Standard Snowflake tables with VARIANT columns
* **Views** - Views that expose VARIANT columns
### Format Types
* **JSON**
* **Avro**
* **Parquet**
* **ORC**
* **XML**
### Column Data Types
* **VARIANT** - Native Snowflake semi-structured data type
* **String** - Text column containing JSON strings. For Iceberg tables, STRING type supports JSON only (not XML).
***
## Structured Data Sources
Structured data sources connect to standard relational database objects (tables, views, materialized views) without requiring format or column type specifications. These data sources provide basic schema discovery capabilities.
### Supported Object Types
* **Materialized Views** - Materialized views
* **Tables** - Standard Snowflake tables
* **Views** - Standard views
### Limitations
Structured data sources only support:
* **Data Dictionary Builder**
* **Semantic Model Generator**
These are the only two supported features for structured data sources.
***
For information on adding data sources, see [Adding Data Sources](broken://pages/6c9e24784a258c34149469b6ee344df363a19923). For source object configuration, see [Source Object Settings](broken://pages/60884f750752dbb870333b2321e5c820a4665a9d).
# Dynamic Table Settings
Configuration for Snowflake Dynamic Tables generated from semi-structured data sources.
## Overview
Dynamic Table Settings configure the refresh behavior, warehouse assignment, and optional parameters for Dynamic Tables generated by DataPancake.
**Feature Requirements:**
* Table Type must be set to "Dynamic Table"
* SQL Code Generation feature must be enabled
***
## Dynamic Table Warehouse Name
The Snowflake warehouse assigned to the Dynamic Table for refresh operations.
**Purpose:**
* Warehouse used to execute Dynamic Table refreshes
* Determines compute resources for refresh
* Warehouse size affects refresh cost and performance
**Requirements:**
* Required for Dynamic Table type
* Warehouse must exist before code generation
* User must have USAGE privilege on warehouse
**Note:** This is the warehouse assigned to the Dynamic Table (in the CREATE DYNAMIC TABLE statement), not the warehouse used to execute the CREATE statement.
***
## Target Lag Configuration
The maximum acceptable delay between source data changes and Dynamic Table updates.
**Target Lag Unit Type:**
* seconds — For near-real-time updates
* minutes — For frequent updates (most common)
* hours — For periodic updates
* days — For daily or less frequent updates
**Target Lag Quantity:**
* Number of time units for target lag (must be greater than 0)
* Combined with unit type defines refresh schedule
**Examples:**
* `1 minute` — Updates within 1 minute of source changes
* `5 minutes` — Updates within 5 minutes
* `1 hour` — Updates within 1 hour
* `1 day` — Daily updates
**How Target Lag Works:**
* Snowflake automatically refreshes Dynamic Table when target lag is exceeded
* Refresh frequency depends on source data change rate
* More frequent changes trigger more frequent refreshes
***
## Optional Parameters
Additional parameters included in the CREATE DYNAMIC TABLE statement.
**Root Dynamic Table Optional Parameters:**
* Parameters for the root-level Dynamic Table
* Included directly in CREATE DYNAMIC TABLE statement
**Nested Dynamic Table Optional Parameters:**
* Parameters for nested Dynamic Tables created from array attributes
* Applied to all nested/flattened Dynamic Tables
**Common Parameters:**
* `REFRESH_MODE=INCREMENTAL` — Only refresh changed data (default, recommended)
* `REFRESH_MODE=FULL` — Refresh all data each time
**Parameter Format:**
* Key-value pairs separated by commas
* Format: `KEY1=VALUE1, KEY2=VALUE2`
* Example: `REFRESH_MODE=INCREMENTAL, INITIALIZE=ON_CREATE`
**Documentation:**
* See [Snowflake Dynamic Table documentation](https://docs.snowflake.com/en/sql-reference/sql/create-dynamic-table) for all available parameters
***
## Metadata Table Configuration
Optional configuration for creating metadata tables to track Dynamic Table changes.
**Create Metadata for Dynamic Table Inserts:**
* Toggle: Creates a metadata table tracking inserts to root Dynamic Table
* Stores primary key, UUID, created timestamp, last updated timestamp
* Enables change tracking and auditing
* Metadata table and sync task created automatically when enabled
**Create Metadata for Dynamic Table Updates:**
* Toggle: Extends metadata tracking to include updates
* Tracks when rows are updated in Dynamic Table
* Requires Create Metadata for Inserts to be enabled
**Create Metadata for Dynamic Table Deletes:**
* Toggle: Extends metadata tracking to include deletes
* Tracks when rows are deleted from Dynamic Table
* Requires Create Metadata for Inserts to be enabled
### Metadata Table Task Configuration
**Required when metadata tracking enabled:**
* Metadata Table Task Warehouse Name - Warehouse for executing metadata sync task (can be different from Dynamic Table warehouse)
* Metadata Table Task Cron Schedule - Cron expression for scheduling metadata updates
* Metadata Table Task Schedule Time Zone - Timezone for cron schedule
**Optional:**
* Task Deployment Database - Database where metadata sync task will be deployed (defaults to Dynamic Table deployment database)
* Task Deployment Schema - Schema where metadata sync task will be deployed
**Cron Schedule Examples:**
* `30 * * * *` — Hourly at 30 minutes past hour
* `0 3 * * *` — Daily at 3:00 AM
* `0 3 * * MON` — Weekly on Monday at 3:00 AM
* `0 3 1 * *` — Monthly on 1st at 3:00 AM
**Available Timezones:**
* `America/Los_Angeles`, `America/New_York`, `America/Chicago`, `America/Denver`, `Etc/UTC` (recommended), and others
**Use Cases:**
* Change tracking and audit trails
* Data quality monitoring and lineage
* Integration with downstream systems for change detection
***
## Configuration Requirements
**Required Settings for Dynamic Tables:**
* Dynamic Table Warehouse Name
* Dynamic Table Target Lag Unit Type
* Dynamic Table Target Lag Quantity
**Required Settings for Metadata Tables (if enabled):**
* Metadata Table Task Warehouse Name
* Metadata Table Task Cron Schedule
* Metadata Table Task Schedule Time Zone
**Optional Settings for Metadata Tables:**
* Task Deployment Database (defaults to Dynamic Table deployment database)
* Task Deployment Schema
**Validation:** The system validates warehouse exists and is accessible, target lag quantity is greater than 0, cron schedule is valid format, timezone is valid, and all required fields are provided when metadata enabled.
***
***
## Summary
**Core Settings (required):**
* Warehouse Name
* Target Lag Unit Type
* Target Lag Quantity
**Optional Parameters:**
* Root Dynamic Table parameters
* Nested Dynamic Table parameters
**Metadata Configuration (optional):**
* Enable metadata tracking (Create Metadata for Inserts/Updates/Deletes)
* Configure metadata task (warehouse, cron schedule, timezone)
For materialization settings, see [Materialization Settings](/core-concepts/data-sources/materialization-settings). For secure view configuration, see [Secure View Settings](/core-concepts/data-sources/secure-view-settings).
# Materialization Settings
Configuration for generating materialized objects (Dynamic Tables or Tables) from semi-structured data sources.
## Overview
Materialization Settings configure how DataPancake generates SQL code to create materialized objects from your semi-structured data. These settings control the output object type, naming, deployment location, case sensitivity, and stream metadata inclusion.
**Feature Requirements:**
* Semi-structured data sources only
* SQL Code Generation feature must be enabled
***
## Table Type
The type of materialized object to generate.
**Options:**
* **Dynamic Table** (Recommended) - Automatically refreshed based on target lag, requires Dynamic Table Settings configuration
* **Table** - Static tables requiring manual refresh, simpler configuration
**Selection:**
* Use Dynamic Table for continuously updated data requiring automatic refresh
* Use Table for static or rarely updated data requiring manual control
***
## Root Table Name
The base name used for all generated materialized objects.
**Purpose:**
* Name for the root-level materialized object
* Prefix for all nested array objects (system automatically adds underscores for nested objects)
**Requirements:**
* Required when SQL Code Generation is enabled
* Should be unique within deployment database/schema
* Must be valid Snowflake identifier
**Naming Pattern:**
* Root Object: Uses the exact name provided (e.g., `CUSTOMER_EVENTS`)
* Nested Array Objects: Uses prefix with underscore and array attribute name (e.g., `CUSTOMER_EVENTS_ITEMS` for items array)
***
## Deployment Location
The database and schema where materialized objects will be created.
**Table Deployment Database:**
* Optional field (if empty, uses source database)
* Should exist before code generation
**Table Deployment Schema:**
* Optional field (if empty, uses source schema)
* Should exist before code generation
**Note:** If deployment database/schema are not specified, objects are created in the same database/schema as the source object.
***
## Case Sensitivity
Controls whether object names preserve case and allow special characters.
**Toggle:** Use Case Sensitivity
**When Enabled:**
* All output object names enclosed in double quotes
* Preserves exact case of names
* Allows special characters in names
* Example: `"CustomerEvents"`, `"customer_events"`
**When Disabled:**
* Object names not quoted
* Snowflake converts to uppercase
* No special characters allowed
* Example: `CUSTOMER_EVENTS` (always uppercase)
***
## Include Stream Message Metadata
Controls whether streaming platform message metadata is included in the root dynamic table.
**When Enabled:**
* Includes metadata fields: `message_key`, `message_offset`, `message_timestamp`, and platform-specific metadata fields
* Useful for tracking message order, deduplication, and auditing
**When Disabled:**
* No message metadata included
* Only data payload attributes in dynamic table
**Requirements:**
* Source Platform/Application must be configured (Kafka, DynamoDB, etc.)
* Only applies to streaming platform data sources
* Metadata fields automatically added to root table
For information on deduplication, see [Source Object Settings](/core-concepts/data-sources/source-object-settings).
***
## Configuration Requirements
**Minimum Requirements (when SQL Code Generation enabled):**
* Table Type (Dynamic Table or Table)
* Root Table Name
**For Dynamic Tables (additionally required):**
* Dynamic Table Warehouse Name
* Dynamic Table Target Lag Unit Type
* Dynamic Table Target Lag Quantity
**For Metadata Tables (if enabled):**
* Metadata Table Task Warehouse Name
* Metadata Table Task Cron Schedule
* Metadata Table Task Schedule Time Zone
**Optional Settings:**
* Table Deployment Database (defaults to source database)
* Table Deployment Schema (defaults to source schema)
* Case Sensitivity (defaults to disabled)
* Include Stream Message Metadata (defaults to disabled)
***
***
## Summary
**Core Settings:**
* Table Type (Dynamic Table or Table)
* Root Table Name (required)
* Deployment Location (optional, defaults to source location)
**Advanced Settings:**
* Case Sensitivity (optional, defaults to disabled)
* Include Stream Message Metadata (optional, streaming only, defaults to disabled)
**Requirements:**
* SQL Code Generation feature must be enabled (semi-structured only)
* Additional Dynamic Table settings required for Dynamic Tables
For detailed Dynamic Table configuration, see [Dynamic Table Settings](/core-concepts/data-sources/dynamic-table-settings). For secure view configuration, see [Secure View Settings](/core-concepts/data-sources/secure-view-settings).
# Product Tiers & Features
Feature enablement and product tier configuration for data sources.
## Overview
DataPancake offers multiple product tiers that enable different capabilities for each data source. Schema Discovery is always available at no cost, while other features can be enabled per data source. Features have dependencies—some features automatically enable others.
***
## Schema Discovery (Always Enabled)
**Status:** Always enabled, no cost (cannot be disabled)
**Capabilities:**
* Schema Summary - Overview of discovered attributes and data types
* Data Source Overview - Summary information on the Data Source Overview screen
* Basic Attribute Discovery - Identification of attributes and their types
* Scan History - Record of scans performed on the data source
**Limitations:**
* No detailed attribute metadata management
* No SQL code generation
* No materialization capabilities
* No advanced features
***
## Pipeline Designer
**Status:** Paid feature, can be enabled per data source
**Dependencies:** None (but required for other features)
**Capabilities:**
* Attribute Metadata Management - Detailed attribute information and configuration
* Polymorphic Version Tracking - Management of multiple data type versions for attributes
* Attribute Relationships - Configuration of relationships between attributes
* Schema Evolution Monitoring - Tracking changes in data structure over time
**Auto-Enabled With:**
* SQL Code Generation
* Data Dictionary Builder
* Semantic Model Generator
* Security Policy Integration
***
## SQL Code Generation
**Status:** Paid feature, can be enabled per data source (semi-structured only)
**Dependencies:** Pipeline Designer (auto-enabled)
**Capabilities:**
* Dynamic Table Generation - Generate CREATE DYNAMIC TABLE statements
* Table Generation - Generate CREATE TABLE statements
* Nested Array Flattening - Automatic handling of nested arrays
* Materialization Code - Complete SQL code for materialized objects
* Code Customization - Configure output object names, deployment locations, and settings
**Auto-Enabled With:**
* Semantic Model Generator
* Security Policy Integration
**Configuration Requirements:** When SQL Code Generation is enabled, you must configure:
* Materialization Settings - Output object type, root table name, deployment location
* Dynamic Table Settings - Warehouse, target lag, optional parameters (if using Dynamic Tables)
* Source Object Settings - Complete source object configuration
**For detailed information on materialization, see** [**Materialization Settings**](/core-concepts/data-sources/materialization-settings)**.**
***
## Data Dictionary Builder
**Status:** Paid feature, can be enabled per data source
**Dependencies:** Pipeline Designer (auto-enabled)
**Capabilities:**
* Data Dictionary Management - Create and maintain data dictionaries
* Attribute Documentation - Document attributes with descriptions and metadata
* Schema Documentation - Comprehensive schema documentation
* Metadata Management - Rich metadata for attributes and data sources
***
## Security Policy Integration
**Status:** Paid feature, can be enabled per data source
**Dependencies:** Pipeline Designer, SQL Code Generation (both auto-enabled)
**Capabilities:**
* Row Access Policy Integration - Include row-level security in generated code
* Column Masking Policy Integration - Include column masking in generated code
* Secure View Generation - Generate secure views with security policies
* Policy Configuration - Configure which attributes use which security policies
**For detailed information on secure views, see** [**Secure View Settings**](/core-concepts/data-sources/secure-view-settings)**.**
***
## Semantic Model Generator
**Status:** Paid feature, can be enabled per data source
**Dependencies:** Pipeline Designer, SQL Code Generation, Data Dictionary Builder (all auto-enabled)
**Capabilities:**
* Semantic Layer Generation - Create views or materialized views for semantic layer
* Cortex Analyst Integration - Generate semantic models for Cortex Analyst
* Relationship Configuration - Define relationships for semantic models
* Query Generation - Generate verified queries for semantic models
**Configuration Requirements:** When Semantic Model Generator is enabled, you can configure:
* View Type - View or Materialized View
* Root View Name - Name for the root semantic layer view
* Deployment Location - Database and schema for semantic layer objects
* Row Access Policy - Security policy for semantic layer views
**For detailed information on secure views, see** [**Secure View Settings**](/core-concepts/data-sources/secure-view-settings)**.**
***
## Feature Enablement
**Enabling Features:** Features are enabled per data source through the Data Source management interface:
1. Navigate to the Data Source management page
2. Select or create a data source
3. Click the "Enable Feature Selection" button
4. Check the boxes for desired features
5. Save the data source
**Feature Dependencies:** The system automatically handles feature dependencies:
* Enabling SQL Code Generation automatically enables Pipeline Designer
* Enabling Semantic Model Generator automatically enables Pipeline Designer, SQL Code Generation, and Data Dictionary Builder
* Enabling Security Policy Integration automatically enables Pipeline Designer and SQL Code Generation
**Feature Status:**
* **Active** - Feature is enabled and available
* **Inactive** - Feature is disabled (default for paid features)
**Billing:** For organizations using billing events (Public Marketplace listing), each enabled feature generates billing events based on the number of attributes in the data source. Schema Discovery is always free. For subscription listings, features are available based on your subscription tier.
***
## Feature Combinations
**Basic Materialization:**
* Pipeline Designer (auto-enabled)
* SQL Code Generation
**Complete Solution:**
* Pipeline Designer (auto-enabled)
* SQL Code Generation
* Data Dictionary Builder
* Security Policy Integration
* Semantic Model Generator
**Documentation Only:**
* Pipeline Designer
* Data Dictionary Builder
***
## Feature Management
**Viewing Enabled Features:** Enabled features are displayed in the Data Source management interface with checkboxes indicating their status.
**Changing Features:** Features can be enabled or disabled at any time:
1. Edit the data source
2. Click "Enable Feature Selection"
3. Check or uncheck features as needed
4. Save changes
**Note:** Disabling a feature may affect dependent features. The system will warn you if disabling a feature will also disable dependent features.
**Feature Impact:**
* **Enabling Features:** May require additional configuration (e.g., SQL Code Generation requires materialization settings), may enable dependent features automatically, may generate billing events (for billing event organizations)
* **Disabling Features:** May disable dependent features, may remove generated code or configurations, does not delete scan history or discovered attributes
***
## Summary
**Free Tier:**
* Schema Discovery (always enabled)
**Paid Features:**
* Pipeline Designer (foundation feature, auto-enabled with others)
* SQL Code Generation (materialization, semi-structured only)
* Data Dictionary Builder (documentation)
* Security Policy Integration (security)
* Semantic Model Generator (semantic layer)
**Dependencies:**
* Most features require Pipeline Designer (auto-enabled)
* Advanced features require SQL Code Generation (auto-enabled)
* Semantic Model Generator requires multiple features (all auto-enabled)
**For information on basic configuration, see** [**Basic Configuration Settings**](/core-concepts/data-sources/basic-configuration-settings)**. For materialization configuration, see** [**Materialization Settings**](/core-concepts/data-sources/materialization-settings)**.**
# Schema Filters
Configuration for excluding parts of the schema from code generation and attribute discovery.
## Overview
Schema Filters allow you to exclude specific attributes or attribute patterns from the code generation process using regular expressions. Filtered attributes are discovered during scanning but marked as `inactive`, preventing them from being included in generated SQL code. Filters can be reactivated at any time by removing the filter or changing attribute status.
***
## Filter Configuration
### Filter Name
**Purpose:** Descriptive name for the filter (required field for all filters).
### Filter Regular Expression
**Purpose:** Regular expression pattern matching attribute paths to exclude (required field for all filters).
**Pattern Matching:**
* Matches against complete attribute paths (e.g., `customer.metadata.debug`)
* Uses standard regular expression syntax
* Case-sensitive by default
* Can match partial paths or full paths
**Examples:**
* `^customer\.internal_id$` - Excludes `customer.internal_id` only
* `^metadata\..*` - Excludes all `metadata.*` attributes
* `.*\.debug$` - Excludes any attribute ending with `.debug`
* `.*_temp$` - Excludes attributes ending with `_temp`
* `^temp\..*` - Excludes all attributes under `temp.*`
* `.*\[0\]\.internal.*` - Excludes internal attributes in first array element
**Note:** Use multiple filters for different patterns—each filter can target different attribute groups.
***
## How Filters Work
### During Scanning
* Filters do not prevent attribute discovery—all attributes are still discovered during scanning
* Attributes matching filter patterns are marked as `inactive`
* Attributes not matching filters remain `active` (or keep existing status)
* Status can be changed manually after scanning
### During Code Generation
* Only `active` attributes included in generated SQL
* `inactive` attributes excluded from code generation
* Filtered attributes not in Dynamic Tables, semantic layer views, or generated SQL code
### Reactivation
**Removing Filters:**
* Delete filter from configuration
* Run scan to update attribute statuses
* Previously filtered attributes become active
**Manual Reactivation:**
* Change attribute status to `active` in Attributes management
* Attribute included in future code generation
* No need to remove filter if manually reactivated
***
## Filter Use Cases
**Exclude Internal Fields:**
* `^.*\._internal.*$` - Exclude internal fields
* `^metadata\..*` - Exclude metadata fields
* `.*\.system_.*` - Exclude system fields
**Exclude Debug/Temporary Fields:**
* `.*_debug$` - Exclude debug fields
* `.*_temp$` - Exclude temporary fields
* `^temp\..*` - Exclude temp namespace
**Exclude Sensitive Data:**
* `.*password.*` - Exclude password fields
* `.*ssn.*` - Exclude SSN fields
* `.*pii\..*` - Exclude PII namespace
**Exclude Large/Unused Attributes:**
* `.*\.binary_data$` - Exclude binary data
* `.*\.large_text$` - Exclude large text fields
* `^unused\..*` - Exclude unused namespace
**Exclude Specific Paths:**
* `^customer\.address\.coordinates$` - Exclude specific nested attribute
* `.*\[.*\]\.internal.*` - Exclude internal fields in arrays
* `^root\.metadata\..*` - Exclude metadata under root
***
## Managing Filters
**Adding Filters:**
* Navigate to data source management
* Scroll to Schema Filters section
* Click `+` symbol to add new row
* Enter Filter Name (required)
* Enter Filter Regular Expression (required)
* Click "Save Schema Filters"
**Editing Filters:**
* Find filter in grid
* Edit Filter Name or Regular Expression directly
* Changes saved when "Save Schema Filters" clicked
* System validates regular expression syntax
**Deleting Filters:**
* Move mouse to leftmost column
* Checkbox appears for row selection
* Check box for filter to delete
* Press Delete key
* Click "Save Schema Filters" to confirm deletion
**Filter Validation:**
* System validates regex syntax on save
* Invalid regex patterns cause save to fail
* Error message indicates regex issue
***
## Filter vs. Transformation
**When to Use Filters:**
* You want to exclude attributes completely
* Attributes are not needed in materialized objects
* You want to reduce object complexity
* Attributes should remain in source only
**When to Use Transformations:**
* You want to modify attribute names
* You need to change data types
* You want to restructure schema
* You need to normalize data
**Order of Application:**
1. Transformations applied first (consolidation, type changes)
2. Filters applied after transformations
3. Final schema used for code generation
***
## Summary
**Core Configuration:**
* Filter Name (required)
* Filter Regular Expression (required)
**Functionality:**
* Excludes attributes from code generation
* Marks matching attributes as inactive
* Can be reactivated by removing filter or changing attribute status
**Use Cases:**
* Exclude internal/debug fields
* Remove sensitive data
* Optimize object size
* Fine-grained attribute control
**Important:** Filters are applied after transformations. Order matters when combining filters and transformations.
For schema transformations, see [Schema Transformations](/core-concepts/data-sources/schema-transformations). For attribute management, see the Attributes documentation.
# Schema Transformations
Configuration for transforming source schema during materialization process.
## Overview
Schema Transformations (also called Schema Consolidations) allow you to modify the source schema during the materialization process. Transformations are applied during scanning and code generation, affecting how attributes are discovered and how SQL code is generated.
***
## Transformation Types
The type of transformation to apply to the schema.
### Consolidate Schema
**Purpose:** Consolidate multiple attribute name variations into a single name using regular expressions.
**Configuration:**
* **Search Expression** - Regular expression to find attribute names
* **Replace Expression** - Replacement pattern for matched names
* **Attribute Path Search** - Optional path filter to limit transformation scope
### Convert Object to Array
**Purpose:** Convert object attributes to array attributes (transform single objects into arrays of objects).
**Configuration:**
* **Source Data Type** - `object` (required)
* **Transform Data Type** - `array` (result)
* **Attribute Path Search** - Path to object attribute to transform
### Transform Objects to Array
**Purpose:** Transform multiple object attributes into a single array (consolidate related objects into array structure).
**Configuration:**
* **Source Data Type** - `object`
* **Transform Data Type** - `array`
* **Attribute Path Search** - Path pattern to match objects
### Transform String to Array
**Purpose:** Convert string attributes containing delimited values into arrays.
**Configuration:**
* **Source Data Type** - `str` (required)
* **Transform Data Type** - `array` (result)
* **String List Type** - Parsing method (primitive, key\_value, fixed\_width)
* Additional parameters based on String List Type
***
## String List Types
When transforming strings to arrays, specify how the string should be parsed.
### Primitive
**Purpose:** Simple delimited list of primitive values.
**Configuration:**
* **Delimiter Value** - Character(s) separating values (e.g., `,`, `|`, `;`)
**Example:**
* Input: `"apple,banana,cherry"`, Delimiter: `,`, Output: `["apple", "banana", "cherry"]`
### Key Value
**Purpose:** String containing key-value pairs parsed into array of objects.
**Configuration:**
* **Key Value Separator** - Separator between key and value (e.g., `=`, `:`)
* **Delimiter Value** - Separator between pairs (e.g., `,`, `;`)
**Example:**
* Input: `"name=John,age=30,city=NYC"`, Key Value Separator: `=`, Delimiter: `,`, Output: `[{"name": "John"}, {"age": "30"}, {"city": "NYC"}]`
### Fixed Width
**Purpose:** String with fixed-width fields parsed based on character positions.
**Configuration:**
* **Field Widths** - Comma-separated list of field widths (e.g., `10,5,15`)
**Example:**
* Input: `"John 30NYC "`, Field Widths: `10,5,15`, Output: Parsed into array based on positions
***
## Regular Expression Patterns
Regular expressions are used for schema consolidation and attribute path filtering.
### Search Expression
**Purpose:** Regular expression pattern to match attribute names (used in Consolidate Schema transformations).
**Examples:**
* `^customer_?id$` - Matches `customer_id`, `customerid`
* `.*[Tt]imestamp.*` - Matches any attribute with "timestamp" or "Timestamp"
* `^[a-z]+_[a-z]+$` - Matches snake\_case attributes
### Replace Expression
**Purpose:** Replacement pattern for matched attribute names (used with Search Expression in Consolidate Schema).
**Examples:**
* Search: `^customer_?id$`, Replace: `customer_id`
* Search: `.*[Tt]imestamp.*`, Replace: `timestamp`
* Search: `(.*)_(.*)`, Replace: `$1_$2` (standardize format)
### Attribute Path Search
**Purpose:** Optional path filter to limit transformation scope (applies transformation only to matching attribute paths).
**Examples:**
* `customer.*` - Apply to all customer.\* attributes
* `.*\.items\[.*\]` - Apply to items array elements
* `root\.metadata\..*` - Apply to metadata.\* attributes
***
## Transformation Configuration
### Consolidation Name
**Purpose:** Descriptive name for the transformation (required field for all transformations).
### Source Data Type
**Purpose:** Original data type before transformation (required for type transformations).
**Options:** `str`, `int`, `float`, `bool`, `object`, `array`
**Usage:**
* Required for Convert Object to Array
* Required for Transform Objects to Array
* Required for Transform String to Array
### Transform Data Type
**Purpose:** Target data type after transformation (required for type transformations).
**Options:** `str`, `int`, `float`, `bool`, `object`, `array`
**Usage:** Specifies result type for transformations (must be compatible with transformation type).
### Additional Parameters
**Wrapper Value:** Characters wrapping values in strings (used for string parsing).
**Delimiter Value:** Character(s) separating values (used in Primitive and Key Value string parsing). Common: `,`, `|`, `;`, `\t`
**Key Value Separator:** Separator between keys and values (used in Key Value string parsing). Common: `=`, `:`, `->`
**Field Widths:** Comma-separated list of field widths (used in Fixed Width string parsing). Example: `10,5,15` for three fields
***
## Managing Transformations
**Adding Transformations:**
* Navigate to data source management
* Scroll to Schema Transformation section
* Click `+` symbol to add new row
* Configure transformation settings
* Click "Save Schema Transformations"
**Editing Transformations:**
* Find transformation in grid
* Edit fields directly in grid
* Changes are saved when "Save Schema Transformations" clicked
* System validates changes before saving
**Deleting Transformations:**
* Move mouse to leftmost column
* Checkbox appears for row selection
* Check box for transformation to delete
* Press Delete key
* Click "Save Schema Transformations" to confirm
{% hint style="warning" %}
Important: Transformations are applied in the order they appear in the grid. Order matters when multiple transformations affect the same attributes.
{% endhint %}
***
## Summary
**Transformation Types:**
* Consolidate Schema (name consolidation)
* Convert Object to Array
* Transform Objects to Array
* Transform String to Array
**String List Types:**
* Primitive (simple delimited)
* Key Value (key-value pairs)
* Fixed Width (position-based)
**Configuration:**
* Regular expression patterns
* Data type specifications
* Parsing parameters
* Attribute path filters
**Important:** Transformations are applied in the order they appear in the grid. Order matters when multiple transformations affect the same attributes.
For schema filtering, see [Schema Filters](/core-concepts/data-sources/schema-filters). For source object configuration, see [Source Object Settings](/core-concepts/data-sources/source-object-settings).
# Secure View Settings
Configuration for generating secure views (secure view layer) from materialized Dynamic Tables.
## Overview
Secure View Settings configure the generation of secure views or materialized views that provide a secure view layer on top of materialized Dynamic Tables. These views can incorporate Row Access Policies for security and are used for secure view layer access and Cortex Analyst integration.
**Feature Requirements:**
* Semantic Model Generator feature must be enabled (automatically enables Pipeline Designer, SQL Code Generation, and Data Dictionary Builder)
* SQL Code Generation automatically enabled with Semantic Model Generator
## View Type
The type of view object to generate for the secure view layer.
**Options:**
* **View** (Recommended) - Standard Snowflake views (virtual objects, no storage, always reflect current Dynamic Table data)
* **Materialized View** - Pre-computed views stored as tables (faster query performance, requires refresh, additional storage cost)
## Root View Name
The base name for the root-level secure view.
**Purpose:**
* Name for the root-level view
* Prefix for all nested array views (system automatically adds underscores for nested views)
**Requirements:**
* Required when View Type is selected
* Should be unique within deployment database/schema
* Must be valid Snowflake identifier
**Naming Pattern:**
* Root View: Uses the exact name provided (e.g., `CUSTOMER_EVENTS_VIEW`)
* Nested Array Views: Uses prefix with underscore and array attribute name (e.g., `CUSTOMER_EVENTS_VIEW_ITEMS` for items array)
## Deployment Location
The database and schema where secure views will be created.
**Secure View Deployment Database Name:**
* Optional field (if empty, uses Dynamic Table database)
* Should exist before code generation
**Secure View Deployment Schema Name:**
* Optional field (if empty, uses Dynamic Table schema)
* Should exist before code generation
**Note:** Deployment location can be different from Dynamic Table deployment location to separate materialized objects from the secure view layer.
## Row Access Policy
Configuration for Row-Level Security (RLS) on secure views.
**Primary Row Access Policy Name:**
* Name of the Row Access Policy applied to root secure view
* Enables row-level security based on user context
* Policy is applied in CREATE VIEW statement
**Requirements:**
* Policy must exist before code generation (or be created separately)
* Policy must be compatible with view structure
* Policy should reference attributes available in view
For more information: See [Snowflake Row Access Policies documentation](https://docs.snowflake.com/en/user-guide/security-row-intro)
## Optional Parameters
Additional parameters included in CREATE VIEW or CREATE MATERIALIZED VIEW statement. Allows fine-grained control over view behavior.
**Format:**
* Key-value pairs or SQL syntax
* Depends on view type
* See Snowflake documentation for available parameters
**Example:**
```
COMMENT='Customer Events Secure View'
```
## Configuration Requirements
**When View Type is Selected:**
* Root View Name is required
* Deployment location optional (defaults to Dynamic Table location)
* Row Access Policy optional but recommended for security
## Use Cases
**Secure View Layer:**
* Provide business-friendly view of data
* Abstract underlying Dynamic Table complexity
* Enable self-service analytics
* Standardize data access patterns
**Security Enforcement:**
* Apply Row Access Policies for data security
* Control data access by user/role
* Meet compliance requirements
**Cortex Analyst Integration:**
* Provide secure view layer for Cortex Analyst
* Enable natural language queries
* Support AI-powered analytics
## Summary
**Core Settings:**
* View Type (View or Materialized View)
* Root View Name (required when type selected)
* Deployment Location (optional, defaults to Dynamic Table location)
**Security Settings:**
* Primary Row Access Policy Name (optional but recommended)
**Advanced Settings:**
* Optional Parameters (optional)
**Requirements:**
* Semantic Model Generator feature must be enabled (automatically enables Pipeline Designer, SQL Code Generation, and Data Dictionary Builder)
For materialization settings, see [Materialization Settings](/core-concepts/data-sources/materialization-settings). For Dynamic Table configuration, see [Dynamic Table Settings](/core-concepts/data-sources/dynamic-table-settings).
# Source Object Settings
Configuration for the Snowflake database object that serves as the data source.
## Overview
Source Object Settings define the connection to the actual Snowflake object containing your data. These settings specify the database, schema, object, and column (for semi-structured data) that DataPancake will scan and process. Additional settings configure platform-specific options for streaming data sources.
***
## Object Type Selection
The type of Snowflake object that contains your data. DataPancake uses this to construct proper SQL queries and grant statements.
### Supported Object Types
**For Semi-Structured Data Sources:**
* **Dynamic Table** - VARIANT columns only
* **External Table** - JSON format only
* **Iceberg Table** - VARIANT or STRING columns, JSON format only (STRING type supports JSON only, not XML)
* **Materialized View** - VARIANT columns
* **Table** - VARIANT columns
* **View** - VARIANT columns
**For Structured Data Sources:**
* **Materialized View**
* **Table**
* **View**
***
## Database, Schema, and Object Selection
The three-part identifier for locating your source object in Snowflake.
### Database Selection
* Dropdown populated from databases accessible to your user (requires USAGE privilege)
* Case-sensitive names are automatically quoted
* Selection required before schema selection
### Schema Selection
* Dropdown populated after database selection
* Shows schemas in selected database (requires USAGE privilege)
* Refreshes when database changes
* Case-sensitive names are automatically quoted
### Object Name Selection
* Dropdown populated after schema selection
* Shows objects of selected type in selected schema
* Only objects matching selected Object Type are shown
* Refreshes when schema or object type changes
* Requires SELECT privilege on object (for semi-structured) or appropriate privileges
* Case-sensitive names are automatically quoted
***
## Column Configuration (Semi-Structured Only)
For semi-structured data sources, you must specify the column containing the VARIANT or String data.
### Column Name
Requirements:
* Column must exist in the source object
* Column must be of type VARIANT or String (VARCHAR/TEXT)
* Required field for semi-structured data sources
Input:
* Text field for column name
* Case-sensitive if column name is quoted in Snowflake
* Can include parsing expressions for String columns
Examples:
* Simple column: `JSON_DATA`
* Quoted column: `"JsonData"`
* Parsing expression: `parse_json(RECORD_CONTENT:"fullDocument")`
### Column Data Type
* **VARIANT** (Recommended)
* Native Snowflake semi-structured type
* Automatically parses JSON, Avro, Parquet, ORC, XML
* Best performance
* No parsing expressions needed
* **String**
* Text column containing JSON/XML strings
* Requires parsing functions in Column Name field
* Useful for streaming platforms
* For Iceberg tables: STRING type supports JSON only (not XML)
**Parsing Expressions (String columns only):**
Enter parsing expressions in the Column Name field when using String column type. Example for Kafka/MongoDB:
```sql
parse_json(RECORD_CONTENT:"fullDocument")
```
***
## Format Type (Semi-Structured Only)
The data format stored in the VARIANT or String column. Format type must match actual data format—incorrect format selection may cause scan failures.
### Supported Formats
* **JSON** - Standard JSON format
* **Avro** - Apache Avro binary format
* **Parquet** - Columnar storage format
* **ORC** - Optimized Row Columnar format
* **XML** - XML document format
**Format Restrictions:**
* External Tables: JSON format only
* Iceberg Tables: JSON format only (STRING type supports JSON only, not XML)
***
## Schema Sample (Semi-Structured Only)
Optional JSON or XML document representing the complete schema of your data source.
**Purpose:**
* Use "Schema" attribute create type in scan configurations (skips data scanning)
* Faster initial setup for prototyping and testing
* Can be updated when full scans are performed
**Requirements:**
* Valid JSON or XML document
* Should represent complete schema structure with all attributes and nested structures
* Can be a single representative record
**Usage:** When Attribute Create Type is set to "Schema" in scan configurations, the system uses the schema sample instead of scanning data.
***
## Platform/Application Settings (Semi-Structured Only)
Configuration for data sources from streaming platforms or document databases.
### Source Platform/Application
**Supported Platforms:**
* Kafka — Apache Kafka streaming platform
* DynamoDB — Amazon DynamoDB document database
* Other — Custom platforms can be configured
**Purpose:**
* Identifies data source platform for specialized handling
* Enables platform-specific features (e.g., stream message metadata inclusion)
**Selection:**
* Dropdown of available platforms
* Optional field (can be left blank)
* Only relevant for streaming/document database sources
### Data Deduplication
**Toggle:** Data Deduplication (By Primary Key)
**When Enabled:**
* Creates two dynamic tables:
1. First table: Flattens root attributes
2. Second table: Filters to most recent record per primary key
**Requirements:**
* SQL Code Generation feature must be enabled
* Deduplication SQL Expression must be provided
**Use Cases:**
* Streaming data sources with duplicate records
* Need to maintain only latest record per key
### Deduplication SQL Expression
**Format:**
* QUALIFY clause with ROW\_NUMBER() window function
* PARTITION BY primary key
* ORDER BY sort field (typically timestamp or offset)
**Example — Kafka (with Stream Metadata):**
```sql
QUALIFY ROW_NUMBER() OVER (PARTITION BY message_key ORDER BY message_offset DESC) = 1
```
**Example — Timestamp-based:**
```sql
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY event_timestamp DESC) = 1
```
**Requirements:**
* Required when Data Deduplication is enabled
* Must use valid Snowflake SQL syntax
* Should reference attributes available in flattened data
***
## Source Object Validation
The system validates source object connection when creating new data sources, saving settings, or before initiating scans.
**Validation Checks:**
* Object exists and is accessible
* Required privileges are granted
* Column exists (for semi-structured)
* Object type matches selection
**Error Handling:**
* Connection Failed: System displays specific error message and provides SQL statements to grant privileges
* Object Not Found: Error indicates object doesn't exist—verify database, schema, and object names
* Column Not Found: Error indicates column doesn't exist—verify column name spelling and data type
***
## Summary
**Required Settings:**
* Object Type
* Database
* Schema
* Object Name
* Column Name (semi-structured)
* Format Type (semi-structured)
* Column Data Type (semi-structured)
**Optional Settings:**
* Schema Sample
* Source Platform/Application
* Data Deduplication
* Deduplication SQL Expression
For information on data source types, see [Data Source Types](/core-concepts/data-sources/data-source-types). For materialization settings, see [Materialization Settings](/core-concepts/data-sources/materialization-settings).
# Scan Configurations
Introduction to scan configurations - learn and control how DataPancake scans your data.
## Overview
Scan configurations control how DataPancake scans data sources. Each configuration defines compute resources, scanning strategy, scheduling, and attribute discovery method.
## Configuration Sections
* [**Basic Configuration Settings**](/core-concepts/scan-configurations/basic-configuration-settings) - `CONFIGURATION_NAME`, `ATTRIBUTE_CREATE_TYPE`, `RECORD_STATUS`, `CODE_GENERATE_ON_VERSION_CHANGE`
* [**Scheduling Settings**](/core-concepts/scan-configurations/scheduling-settings) - `MONITOR_ENABLED`, `MONITOR_CRON_SCHEDULE`, `MONITOR_CRON_TIMEZONE`
* [**Data Source & Warehouse Settings**](/core-concepts/scan-configurations/data-source-and-warehouse-settings) - `SCAN_RECORD_LIMIT`, `SCAN_WHERE_CLAUSE`, warehouse selection
* [**Vertical Scale Settings**](/core-concepts/scan-configurations/vertical-scale-settings) - Thread count, `PROCEDURE_INSTANCE_COUNT`, `PROCEDURE_INSTANCE_ROW_COUNT`, `SCAN_ORDER_BY`, `THREAD_PROCESS_RECORD_COUNT` (semi-structured only)
* [**Source Stream Settings**](/core-concepts/scan-configurations/source-stream-settings) - `SOURCE_STREAM_LAST_SCANNED_TIMESTAMP` for incremental scanning
* [**Common Patterns & Best Practices**](/core-concepts/scan-configurations/common-patterns-and-best-practices) - Configuration patterns and optimization
## Quick Reference
**Essential:**
* `CONFIGURATION_NAME` - Unique identifier
* `ATTRIBUTE_CREATE_TYPE` - `'Discover'` (production) or `'Schema'` (prototyping)
* Virtual Warehouse - Compute resource
* `SCAN_RECORD_LIMIT` - Number of records (0 = unlimited)
**Advanced:**
* Number of Threads - Parallel processing (defaults to warehouse max, semi-structured only)
* `PROCEDURE_INSTANCE_COUNT` - Split scans across multiple calls (60-minute timeout)
* `MONITOR_CRON_SCHEDULE` - Automated scanning with cron expressions
See [Scan Processing](/core-concepts/scan-processing) for scanning details. See [Warehouses](/core-concepts/warehouses) for warehouse selection.
# Basic Configuration Settings
Core settings for naming, attribute discovery method, status, and auto code generation.
## Configuration Name (`CONFIGURATION_NAME`)
Unique, descriptive name for the scan configuration. Use names that indicate purpose (e.g., "Quick Onboarding Scan", "Daily Full Scan", "Incremental Updates").
***
## Attribute Create Type (`ATTRIBUTE_CREATE_TYPE`)
Determines how attributes are discovered and created during scanning.
**Values:**
* `'Discover'` - Uses actual scanned data to create attribute metadata. Discovers all attributes, creates all 7 polymorphic versions proactively, activates versions based on actual data types found. Recommended for production.
* `'Schema'` - Uses Data Source Object Schema Sample to create attribute metadata. Faster for rapid prototyping without scanning full datasets. Best for quick onboarding and testing.
***
## Status (`RECORD_STATUS`)
Controls whether scan configuration is active and available for use.
**Values:**
* `'Active'` - Configuration available for scanning
* `'Inactive'` - Configuration disabled but preserved
* `'Deleted'` - Configuration marked for deletion
***
## Auto Code Generate (`CODE_GENERATE_ON_VERSION_CHANGE`)
Automatically generates Dynamic Table SQL code when data source schema or polymorphic state changes after a scan.
**When enabled:**
* SQL code automatically generated after scans that detect schema changes
* New polymorphic versions trigger code regeneration
* Attribute additions or changes update generated SQL
**When disabled:**
* Code generation must be triggered manually
* Useful for reviewing changes before generating code
# Scheduling Settings
Configure automated scanning with cron schedules and time zones.
## Enable Schedule (`MONITOR_ENABLED`)
Toggles automated scanning based on cron schedule. When enabled, scans run automatically at specified times.
**Use cases:** Regular full scans (daily, weekly), incremental update scans, schema monitoring and drift detection, automated pipeline updates.
***
## Cron Schedule (`MONITOR_CRON_SCHEDULE`)
Cron expression defining when scans run automatically. Required when `MONITOR_ENABLED = TRUE`.
**Cron format:** `minute hour day-of-month month day-of-week`
**Examples:**
```
30 * * * * # Hourly at 30 minutes after the hour
0 3 * * * # Daily at 3:00 AM
0 3 * * MON # Weekly at 3:00 AM every Monday
0 3 1 * * # Monthly at 3:00 AM on the 1st
```
**Note:** Schedule during low-usage periods. Avoid overlapping scans on the same warehouse.
***
## Cron Time Zone (`MONITOR_CRON_TIMEZONE`)
Time zone for cron schedule. Required when `MONITOR_ENABLED = TRUE`.
**Available time zones:** `Etc/UTC`, `America/Los_Angeles`, `America/Phoenix`, `America/Denver`, `America/Boise`, `America/Mexico_City`, `America/Indiana/Knox`, `America/Chicago`, `America/Toronto`, `America/Detroit`, `America/New_York`
**Note:** Use organization's primary time zone or UTC for consistency across regions.
# Data Source & Warehouse Settings
Configure record limits, WHERE clauses, and warehouse selection for scan operations.
## Data Source Settings
**Record Limit (`SCAN_RECORD_LIMIT`)**
* Limits number of records scanned. Set to `0` for unlimited (full scan).
* Use cases: Quick scans (e.g., 10,000 for rapid discovery), full scans (`0`), testing (small limits), sampling
* Monitor scan duration to ensure it stays under 60 minutes
***
**Where Clause (`SCAN_WHERE_CLAUSE`)**
* SQL WHERE clause to filter records during scanning
* **Constraint:** Only available when `PROCEDURE_INSTANCE_COUNT = 1` (cannot use with multiple procedure calls)
* Use cases: Incremental scanning (timestamp filters), partitioned scanning, data filtering
**Examples:**
```sql
WHERE created_date >= CURRENT_DATE - 7
WHERE partition_key = '2024-01'
WHERE status = 'active'
```
**Note:** Combine with `SOURCE_STREAM_LAST_SCANNED_TIMESTAMP` for delta scans. Test WHERE clauses before enabling scheduled scans.
***
## Warehouse Settings
**Virtual Warehouse**
* Snowflake virtual warehouse assigned to this scan configuration
* Each configuration must be assigned to exactly one warehouse
* Only warehouses registered in DataPancake are available
* Warehouse must be connected and active
* Each warehouse can only run one scan at a time
* Warehouse size affects scan performance
**Warehouse status indicators:**
* Connected - Available and ready
* Not Connected - Connection issue (cannot save configuration)
* Inactive - Disabled (warning shown)
* Deleted - Marked for deletion (cannot save configuration)
# Vertical Scale Settings
Control thread count, procedure calls, and memory management for optimal scan performance.
Vertical scale settings control how DataPancake utilizes compute resources within a single warehouse. These settings are only available for semi-structured data sources.
***
## Number of Threads
Number of parallel threads used during scanning. Defaults to maximum threads available for chosen warehouse size.
**Default behavior:**
* Automatically set to warehouse maximum
* Small: 1-2 threads, Medium: 4-8 threads, Large: 8-16 threads, 2X-Large: 16+ threads
**Manual override:**
* Can be reduced for memory-constrained scenarios
* Lower thread count = less memory usage, slower processing
* Higher thread count = more memory usage, faster processing (up to warehouse max)
**Note:** Use default (maximum) for most scenarios. Reduce only if experiencing memory errors.
***
## Number of Procedure Calls (`PROCEDURE_INSTANCE_COUNT`)
Total number of procedure calls required to process entire dataset. Use multiple calls when a single call cannot complete within 60 minutes.
**When to use multiple calls:**
* Data source takes longer than 60 minutes to scan
* Very large datasets (millions of records)
* Complex nested structures requiring more processing time
**Configuration:**
* Set to `1` for single procedure call (default)
* Set to `2` or more to split scan across multiple calls
* Each call must complete within 60-minute timeout
* Calls execute sequentially via Snowflake Tasks
**Important constraints:**
* **WHERE clause not available** - Cannot use `SCAN_WHERE_CLAUSE` when `PROCEDURE_INSTANCE_COUNT > 1`
* **ORDER BY required** - Must specify `SCAN_ORDER_BY` when `PROCEDURE_INSTANCE_COUNT > 1`
* **Record count required** - Must set `PROCEDURE_INSTANCE_ROW_COUNT` when `PROCEDURE_INSTANCE_COUNT > 1`
**Note:** Start with single call (`1`) and monitor scan duration. Use multiple calls only if scans exceed 60 minutes.
***
## Record Count Per Procedure Call (`PROCEDURE_INSTANCE_ROW_COUNT`)
Number of records processed per procedure call. Required when `PROCEDURE_INSTANCE_COUNT > 1`.
**Calculation:**
* Total records ÷ Number of procedure calls = Records per call
* Must be large enough to process all rows
* Cannot be so large that some calls have zero rows
**Example:**
* Dataset: 2,000,000 records, 2 procedure calls
* Valid: 1,000,000 records per call (2,000,000 ÷ 2 = 1,000,000)
* Invalid: 500,000 records per call (would only process 1,000,000 total)
* Invalid: 2,500,000 records per call (exceeds total, would leave empty calls)
**Note:** Calculate: Total records ÷ Number of calls. Add small buffer (5-10%) to account for data growth.
***
## Order By (`SCAN_ORDER_BY`)
One or more attributes used to order the data source.
**When required:**
* `PROCEDURE_INSTANCE_COUNT > 1` - Ensures consistent record partitioning across calls, prevents duplicate or missed records
**When optional:**
* `PROCEDURE_INSTANCE_COUNT = 1` and `SCAN_RECORD_LIMIT > 0` - Can improve performance for limited scans
**When not required:**
* `PROCEDURE_INSTANCE_COUNT = 1` and `SCAN_RECORD_LIMIT = 0` - Full table scan in single call
**Format examples:**
* Single attribute: `json_data:"_id"` or `customer_id`
* Multiple attributes: `customer_id, json_data:"_id"`
**Note:** Use unique or nearly-unique attributes. Prefer indexed columns for performance.
***
## Record Count Per Thread Worker Process (`THREAD_PROCESS_RECORD_COUNT`)
Number of records passed to each thread worker process during a scan procedure call. Controls how much data is loaded into memory at one time.
**Memory calculation:**
```
Memory Used = Avg document size (bytes) × 2 × Thread Count × Record Count Per Thread
```
**Default behavior:**
* Automatically set based on warehouse size and data complexity
* Typically defaults to 1,000 records per thread
* Can be adjusted if memory errors occur
**Optimization:**
* Reduce if experiencing memory errors
* Increase if memory allows and you want faster processing
* Balance between memory usage and processing efficiency
**Note:** Start with default value. Reduce if memory errors occur during scanning.
# Source Stream Settings
Configure incremental scanning with timestamp tracking for efficient delta updates.
## Last Scanned Timestamp (`SOURCE_STREAM_LAST_SCANNED_TIMESTAMP`)
Last timestamp scanned from this data source. Used as part of `SCAN_WHERE_CLAUSE` for incremental scanning.
**How it works:**
* Stored after each successful scan
* Automatically updated when scans complete
* Used to filter records in subsequent scans
* Enables delta/incremental scanning
**Incremental scanning:**
* Set `SCAN_WHERE_CLAUSE` to include timestamp filter (e.g., `WHERE created_date > ''`)
* Scans only new or changed records
* Faster than full scans
* Schema discovery still runs for new data
**Resetting:**
* Remove timestamp from configuration to scan entire data source
* Useful for full re-scanning after schema changes
* Can be cleared manually in configuration
# Common Patterns & Best Practices
Pre-configured patterns and optimization recommendations for common scan scenarios.
## Common Configuration Patterns
**Quick Onboarding Scan:**
* `ATTRIBUTE_CREATE_TYPE`: `'Schema'` (if schema sample available) or `'Discover'`
* `SCAN_RECORD_LIMIT`: `10,000` (or small sample)
* `PROCEDURE_INSTANCE_COUNT`: `1`
* Number of Threads: Default (maximum)
* `CODE_GENERATE_ON_VERSION_CHANGE`: `FALSE` (review first)
* `MONITOR_ENABLED`: `FALSE`
* Use case: Initial data source setup, testing, quick schema validation
**Full Production Scan:**
* `ATTRIBUTE_CREATE_TYPE`: `'Discover'`
* `SCAN_RECORD_LIMIT`: `0` (unlimited)
* `PROCEDURE_INSTANCE_COUNT`: `1` (or multiple if needed)
* Number of Threads: Default (maximum)
* `CODE_GENERATE_ON_VERSION_CHANGE`: `TRUE`
* `MONITOR_ENABLED`: `TRUE` (daily/weekly as needed)
* Use case: Production data sources, complete attribute discovery, automated pipeline updates
**Incremental Scan:**
* `ATTRIBUTE_CREATE_TYPE`: `'Discover'`
* `SCAN_RECORD_LIMIT`: `0` (unlimited)
* `SCAN_WHERE_CLAUSE`: `created_date > ''`
* `SOURCE_STREAM_LAST_SCANNED_TIMESTAMP`: Auto-updated after scans
* `PROCEDURE_INSTANCE_COUNT`: `1`
* Number of Threads: Default (maximum)
* `CODE_GENERATE_ON_VERSION_CHANGE`: `TRUE`
* `MONITOR_ENABLED`: `TRUE` (hourly/daily)
* Use case: Regularly updated data sources, delta scanning workflows, schema monitoring
**Large Dataset Scan:**
* `ATTRIBUTE_CREATE_TYPE`: `'Discover'`
* `SCAN_RECORD_LIMIT`: `0` (unlimited)
* `PROCEDURE_INSTANCE_COUNT`: `4` (or as needed)
* `PROCEDURE_INSTANCE_ROW_COUNT`: `1,000,000` (or calculated)
* `SCAN_ORDER_BY`: `unique_id` (or appropriate attribute)
* Number of Threads: Default (maximum)
* `CODE_GENERATE_ON_VERSION_CHANGE`: `TRUE`
* `MONITOR_ENABLED`: `TRUE` (as needed)
* Use case: Data sources exceeding 60-minute scan time, very large datasets (millions+ records), complex nested structures
**Memory-Constrained Scan:**
* `ATTRIBUTE_CREATE_TYPE`: `'Discover'`
* `SCAN_RECORD_LIMIT`: `0` (unlimited) or limited
* `PROCEDURE_INSTANCE_COUNT`: `1` or multiple
* Number of Threads: Reduced (e.g., 2-4 instead of max)
* `THREAD_PROCESS_RECORD_COUNT`: Reduced
* `CODE_GENERATE_ON_VERSION_CHANGE`: `TRUE`
* `MONITOR_ENABLED`: `FALSE` or `TRUE`
* Use case: Memory errors during scanning, very complex nested structures, large arrays and polymorphic variations, smaller warehouse sizes
***
## Best Practices
**Configuration management:**
* Use descriptive, consistent names (e.g., `customer_events_daily_full`)
* Create separate configurations for different use cases (quick scan, full scan, incremental scan)
* Keep only active configurations visible; use Inactive status for configurations you may reuse
**Performance optimization:**
* Use medium warehouses for most use cases; reserve larger warehouses for complex data sources
* Use default (maximum) threads unless memory constrained
* Start with single procedure call (`PROCEDURE_INSTANCE_COUNT = 1`); use multiple calls only if exceeding 60 minutes
**Scheduling strategy:**
* Schedule during low-usage periods
* Avoid overlapping scans on same warehouse
* Use `SCAN_WHERE_CLAUSE` with timestamp filters for incremental scanning
* Leverage `SOURCE_STREAM_LAST_SCANNED_TIMESTAMP`; clear timestamp for full re-scans
**Error prevention:**
* Monitor scan duration; use multiple procedure calls if needed
* Start with default settings; reduce thread count or `THREAD_PROCESS_RECORD_COUNT` if memory errors occur
* Validate `SCAN_WHERE_CLAUSE` and test `SCAN_ORDER_BY` attributes before use
* Ensure `PROCEDURE_INSTANCE_ROW_COUNT` is accurate when using multiple procedure calls
# Attribute Metadata
## Overview
Attributes are discovered during scanning and serve as the metadata foundation for generating Dynamic Tables and Views. DataPancake discovers every attribute path at any nesting depth, including:
* All nested structures (any depth)
* Embedded/stringified JSON (recursively parsed)
* Polymorphic variations (same path, different data types)
**Key DataPancake Behavior:** When an attribute is first discovered, DataPancake **proactively creates all 7 polymorphic versions** immediately. Only versions matching discovered data types are activated (`VERSION_STATUS = 'active'`). Inactive versions remain ready for future activation, enabling seamless schema evolution.
***
## Documentation
* [**Attribute Discovery**](/core-concepts/attribute-metadata/attribute-discovery-process) - Discovery process and embedded JSON handling
* [**Attribute Types**](/core-concepts/attribute-metadata/attribute-metadata-types) - Discovered, Schema, and Virtual attributes
* [**Polymorphic Versions**](/core-concepts/attribute-metadata/polymorphic-versions) - The 7 polymorphic variations and proactive creation
* [**Attribute Metadata Fields**](/core-concepts/attribute-metadata/datapancake-metadata-fields) - Complete field reference
* [**Arrays & Nested Structures**](/core-concepts/attribute-metadata/arrays-and-nested-structures) - Array handling and relationships
* [**Attribute Lifecycle**](/core-concepts/attribute-metadata/attribute-lifecycle) - Creation, versioning, and schema evolution
* [**Attributes in Code Generation**](/core-concepts/attribute-metadata/attributes-in-code-generation) - How attributes become SQL columns
* [**Integration & API**](/core-concepts/attribute-metadata/integration-and-api) - Stored procedures and views
***
## Quick Reference
**Attribute Types:**
* `'Discovered'` - Found during scanning (`ATTRIBUTE_CREATE_TYPE = 'Discover'`)
* `'Schema'` - Created from schema samples (`ATTRIBUTE_CREATE_TYPE = 'Schema'`)
* `'Virtual'` - User-created via UI/API
**The 7 Polymorphic Versions** (created proactively for every attribute):
1. `str` → `{name}_str`
2. `int` → `{name}_int`
3. `float` → `{name}_float`
4. `bool` → `{name}_bool`
5. `object` → `{name}_object`
6. `array_primitive` → `{name}_array_primitive`
7. `array_object` → `{name}_array_object`
**Key Tables:**
* `core.datasource_attribute` - Core attribute metadata
* `core.datasource_attribute_polymorphic_version` - Version-specific metadata
# Attribute Discovery Process
How DataPancake discovers attributes during scanning, including polymorphic detection and recursive parsing of stringified JSON.
## Discovery Process
1. **Recursively traverse records** - Every nested level is visited
2. **Track attribute paths** - Complete paths from root to leaf (e.g., `customer.contact.email`)
3. **Identify data types** - Source data type inferred for each path occurrence
4. **Create all 7 polymorphic versions** - Proactively created when attribute first discovered
5. **Activate matching versions** - Only versions matching discovered types set to `VERSION_STATUS = 'active'`
6. **Create attribute records** - Records created in `core.datasource_attribute` and `core.datasource_attribute_polymorphic_version`
***
## What Gets Discovered
* **All attribute paths** - Complete paths from root to leaf (e.g., `customer.contact.email`)
* **Nested objects** - Every level of object nesting
* **Nested arrays** - Both object arrays (`ARRAY_TYPE = 'object'`) and primitive arrays (`ARRAY_TYPE = 'primitive'`)
* **Embedded JSON** - JSON stored as strings (`HAS_EMBEDDED_CONTENT = TRUE`); recursively parsed
* **Polymorphic variations** - All data type variations for the same path (handled via polymorphic versions)
***
## Example: Polymorphic Discovery
**Record 1:**
```json
{
"customer_id": "C001",
"customer": {
"address": "123 Main St, Anytown, ST 12345",
"metadata": "{\"source\":\"web\",\"tags\":[\"vip\"]}"
}
}
```
**Record 2:**
```json
{
"customer_id": "C002",
"customer": {
"address": {"street": "456 Oak Ave", "city": "Springfield"},
"metadata": "{\"source\":\"mobile\",\"tags\":[\"new\"]}"
}
}
```
**Discovery Results:**
* `customer.address`:
* Record 1: `str` → `address_str` activated
* Record 2: `object` → `address_object` activated (existing version)
* `customer.metadata`:
* `HAS_EMBEDDED_CONTENT = TRUE`
* Recursively parsed to discover: `metadata.source`, `metadata.tags[]`
# Attribute Metadata Types
Overview of the three attribute types and where they come from: Discovered (from scans), Schema (from samples), and Virtual (user-created).
DataPancake categorizes attributes into three types based on their origin:
***
## Discovered Attributes
**Definition:** Attributes automatically discovered during the scanning process.
**Characteristics:**
* Created when `ATTRIBUTE_CREATE_TYPE = 'Discover'` in scan configuration
* Represents actual fields found in source data
* Cannot be deleted (only `RECORD_STATUS = 'inactive'`)
* All 7 polymorphic versions created proactively
**Creation:** During scan process via `sp_upsert_attribute`. All 7 polymorphic versions created in `core.datasource_attribute_polymorphic_version`; only matching versions set to `VERSION_STATUS = 'active'`.
***
## Schema Attributes
**Definition:** Attributes created from a schema sample rather than full data scanning.
**Characteristics:**
* Created when `ATTRIBUTE_CREATE_TYPE = 'Schema'` in scan configuration
* Based on schema sample from `DATASOURCE_OBJECT_SCHEMA_SAMPLE` field
* All 7 polymorphic versions created proactively
* Can be updated when full scans are performed
**Use Cases:** Rapid prototyping without full data scans; testing configurations with sample schemas.
***
## Virtual Attributes
**Definition:** User-created custom attributes that don't exist in the source data.
**Characteristics:**
* Created via UI or `sp_upsert_virtual_datasource_attribute` stored procedure
* Single polymorphic version (no polymorphism)
* Automatically set to `INCLUDE_IN_CODE_GEN = TRUE`
* `ATTRIBUTE_TYPE = 'Virtual'`
**Required fields:**
* Attribute name (no spaces)
* Source data type
* Snowflake data type
* Transformation expression (SQL)
**Optional fields:**
* Parent array (for array-level virtual attributes)
* `W_QUESTION_CATEGORY` (for Cortex Analyst semantic models)
**Use Cases:**
* Derived fields (e.g., `full_name = first_name || ' ' || last_name`)
* Semantic model metrics/filters/dimensions
* Business logic not in source data
***
## Comparison
| Feature | Discovered | Schema | Virtual |
| --------------------------- | ------------------ | ------------------ | -------------- |
| **Source** | Full data scan | Schema sample | User-created |
| **ATTRIBUTE\_CREATE\_TYPE** | `'Discover'` | `'Schema'` | N/A |
| **Polymorphic Versions** | All 7 created | All 7 created | Single version |
| **Can Delete** | No (mark inactive) | No (mark inactive) | Yes |
| **Update Method** | Re-scan | Re-scan or update | UI/API update |
# Attribute Metadata Details
Complete reference for attribute metadata, covering discovered source schema fields and configurable extended metadata that controls SQL transformation, security policies, and code generation.
## Overview
Attribute metadata is stored in:
* `core.datasource_attribute` - Core attribute information
* `core.datasource_attribute_polymorphic_version` - Version-specific metadata
**Source Schema Metadata:** Automatically discovered during scanning (read-only except `RECORD_STATUS`).
**Extended Metadata:** User-configurable fields for transformation, security, and code generation.
## Metadata Organization
Pipeline Designer organizes metadata by UI tabs:
* [**Source Schema**](/core-concepts/attribute-metadata/attribute-metadata-details/source-schema) - Discovered attributes, paths, types, structure
* [**Destination Schema**](/core-concepts/attribute-metadata/attribute-metadata-details/destination-schema) - Snowflake data type configuration, keys
* [**Transformation & Security**](/core-concepts/attribute-metadata/attribute-metadata-details/transformation-security) - Column naming, transformations, security policies
* [**Arrays**](/core-concepts/attribute-metadata/attribute-metadata-details/arrays) - Array-specific configuration and relationships
* [**Foreign Keys**](/core-concepts/attribute-metadata/attribute-metadata-details/foreign-keys) - Foreign key relationships for array tables
***
## Core Attribute Fields
Stored in `core.datasource_attribute` (applies to all polymorphic versions):
* `ATTRIBUTE_PATH` - Full path in source data (e.g., `customer.contact.email`)
* `ATTRIBUTE_PATH_EMBEDDED` - Path for attributes within embedded/stringified JSON
* `ATTRIBUTE_NAME` - Leaf name (e.g., `email` from `customer.contact.email`)
* `ATTRIBUTE_LEVEL` - Nesting depth (0 = root, increments per level)
* `ATTRIBUTE_ORDER` - Ordering for attributes at same level
* `PARENT_OBJECT` - Parent object path (e.g., `customer.contact` for `customer.contact.email`)
* `PARENT_ARRAY` - Parent array path if within array (e.g., `orders` for `orders[0].order_id`)
* `PARENT_ARRAY_EMBEDDED` - Parent array path for embedded JSON arrays
* `RECORD_STATUS` - `'active'` or `'inactive'` (editable)
* `ATTRIBUTE_TYPE` - `'Discovered'`, `'Schema'`, or `'Virtual'`
* `ATTRIBUTE_SCHEMA_COMPONENT_TYPE` - Internal classification
* `IS_PRIMARY_KEY` - Boolean for primary key identification
***
## Polymorphic Version Fields
Stored in `core.datasource_attribute_polymorphic_version` (version-specific):
**Type Information:**
* `SOURCE_DATA_TYPE` - Inferred type (`'str'`, `'int'`, `'float'`, `'bool'`, `'object'`, `'array'`, `'null'`)
* `POLYMORPHIC_ATTRIBUTE_NAME` - Unique version name (e.g., `email_str`, `orders_array_object`)
* `VERSION_STATUS` - `'active'` or `'inactive'`
* `IS_ARRAY` - Boolean for array type
* `ARRAY_TYPE` - `'object'`, `'primitive'`, or `'primitive,object'`
* `ARRAY_PRIMITIVE_TYPE` - Element type for primitive arrays (`'str'`, `'int'`, `'float'`, `'bool'`)
* `HAS_EMBEDDED_CONTENT` - Boolean for embedded/stringified JSON
* `SAMPLE_VALUE` - Sample value (strings default to `"string value"` for privacy)
* `VERSION_STATUS_DATE` - Timestamp when version was created/last activated
**Snowflake Data Type:**
* `DATA_PLATFORM_DATA_TYPE` - Snowflake data type (editable)
* `DATA_PLATFORM_DATA_TYPE_PRECISION` - Precision for numeric types (editable)
* `DATA_PLATFORM_DATA_TYPE_SCALE` - Scale for numeric types (editable)
* `DATA_PLATFORM_DATA_TYPE_DATETIMEFORMAT` - DateTime format strings (editable)
* `USE_DATETIME_FORMAT` - Boolean for using formats (editable)
**Transformation & Naming:**
* `ALIAS_NAME` - Custom column alias (editable)
* `CODE_GENERATED_COLUMN_NAME` - Final column name (read-only, auto-generated)
* `NULL_VALUE_EXPRESSION` - SQL for null handling (editable, only when `TRANSFORMATION_TYPE = 'No Transformation'`)
* `TRANSFORMATION_TYPE` - `'No Transformation'` or `'SQL Expression'` (editable)
* `TRANSFORMATION_EXPRESSION` - Custom SQL transformation (editable)
* `TRANSFORMATION_EXPRESSION_COMMENT` - Documentation (editable)
**Semantic Layer:**
* `INCLUDE_IN_SEMANTIC_LAYER` - Boolean for view inclusion (editable)
* `SEMANTIC_LAYER_EXPRESSION` - View-level transformation (editable)
* `SEMANTIC_LAYER_EXPRESSION_COMMENT` - Documentation (editable)
* `SEMANTIC_LAYER_ALIAS_NAME` - View column alias (editable)
**Security:**
* `INCLUDE_IN_SECURITY_ROW_LEVEL_POLICY` - Boolean for row-level security (editable)
* `MASKING_POLICY_NAME` - Masking policy name (editable)
* `MASKING_POLICY_PARAMETERS` - Policy parameters (editable)
**Schema Consolidation:**
* `SCHEMA_INSERT_REGULAR_EXPRESSION_SEARCH` - Regex pattern (editable)
* `SCHEMA_INSERT_SQL_EXPRESSION` - SQL expression (editable)
**Code Generation:**
* `INCLUDE_IN_CODE_GEN` - Boolean for code generation (editable)
* `PARENT_INCLUDE_IN_CODE_GEN` - Boolean for parent inclusion (read-only)
**Additional:**
* `IS_ENUM` - Boolean for enum values (editable)
* `HAS_ALL_UNIQUE_VALUES` - Boolean for unique values (editable)
* `HAS_ALL_NULL_VALUES` - Boolean for null values (read-only)
* `ADD_TO_CLUSTER_BY` - Boolean for CLUSTER BY clause (editable)
***
## Metadata Workflow
1. **Discovery** - Source schema metadata discovered during scanning
2. **Configuration** - Extended metadata configured in Pipeline Designer
3. **Code Generation** - Metadata drives SQL generation for Dynamic Tables and Views
4. **Materialization** - Generated SQL creates materialized tables with transformations
5. **Semantic Layer** - Additional transformations applied in views
## Quick Reference
See individual pages for complete field details:
* [Source Schema](/core-concepts/attribute-metadata/attribute-metadata-details/source-schema) - `RECORD_STATUS` (editable)
* [Destination Schema](/core-concepts/attribute-metadata/attribute-metadata-details/destination-schema) - Data types, precision, scale, datetime formats, keys
* [Transformation & Security](/core-concepts/attribute-metadata/attribute-metadata-details/transformation-security) - Naming, transformations, semantic layer, security
* [Arrays](/core-concepts/attribute-metadata/attribute-metadata-details/arrays) - Array configuration, relationships, aliases
* [Foreign Keys](/core-concepts/attribute-metadata/attribute-metadata-details/foreign-keys) - Foreign key relationships
# Source Schema
Read-only metadata discovered during scanning, including attribute paths, nesting levels, data types, structure information, and sample values representing what DataPancake found in your source data.
## Overview
Source Schema metadata represents what DataPancake discovered during scanning. Primarily read-only (except `RECORD_STATUS`). Provides structure, types, and content information.
***
## Source Schema Fields
### Path Information
**Attribute Path (`ATTRIBUTE_PATH`)**
* The full path to the attribute in the source data
* Example: `customer.contact.email`
* Used to reference the attribute in source queries
* **Read-only** - Set during discovery
**Attribute Path Embedded (`ATTRIBUTE_PATH_EMBEDDED`)**
* Path for attributes found within embedded/stringified JSON
* Tracks nested JSON structures within string fields
* Example: For JSON stored as a string, this tracks the path within that JSON
* **Read-only** - Set during discovery
**Attribute Name (`ATTRIBUTE_NAME`)**
* The leaf name of the attribute
* Example: For path `customer.contact.email`, the name is `email`
* **Read-only** - Extracted from attribute path
***
### Structure Information
**Attribute Level (`ATTRIBUTE_LEVEL`)**
* The nesting depth of the attribute
* Root level attributes have level 0
* Each nested level increments the count
* Example: `customer` = level 0, `customer.contact` = level 1, `customer.contact.email` = level 2
* **Read-only** - Calculated during discovery
**Attribute Order (`ATTRIBUTE_ORDER`)**
* Ordering information for attributes at the same level
* Used for consistent presentation in the UI
* **Read-only** - Set during discovery
**Parent Object (`PARENT_OBJECT`)**
* The parent object path containing this attribute
* Example: For `customer.contact.email`, parent object is `customer.contact`
* Empty for root-level attributes
* **Read-only** - Set during discovery
**Parent Array (`PARENT_ARRAY`)**
* The parent array path if this attribute is within an array
* Example: For `orders[0].order_id`, parent array is `orders`
* Used for foreign key relationship configuration
* Empty for non-array attributes
* **Read-only** - Set during discovery
**Parent Array Embedded (`PARENT_ARRAY_EMBEDDED`)**
* Parent array path for attributes within embedded JSON arrays
* Tracks arrays within stringified JSON
* **Read-only** - Set during discovery
***
### Type Information
**Source Data Type (`SOURCE_DATA_TYPE`)**
* Inferred data type from source data
* Values: `'str'`, `'int'`, `'float'`, `'bool'`, `'object'`, `'array'`, `'null'`
* **Read-only** - Inferred during scanning
**Polymorphic Attribute Name (`POLYMORPHIC_ATTRIBUTE_NAME`)**
* Unique name for this polymorphic version
* Format: `{attribute_name}_{type}` or `{attribute_name}_array_{array_type}`
* Examples: `email_str`, `price_float`, `orders_array_object`
* **Read-only** - Generated based on source data type
**Array Type (`ARRAY_TYPE`)**
* For array attributes, the type of array elements
* Values: `'object'`, `'primitive'`, `'primitive,object'`
* Only applicable when `SOURCE_DATA_TYPE = 'array'`
* **Read-only** - Determined during discovery
**Array Primitive Type (`ARRAY_PRIMITIVE_TYPE`)**
* For primitive arrays, the data type of array elements
* Values: `'str'`, `'int'`, `'float'`, `'bool'`
* Only applicable when `ARRAY_TYPE = 'primitive'` or `ARRAY_TYPE = 'primitive,object'`
* **Read-only** - Determined during discovery
***
### Content Information
**Sample Value (`SAMPLE_VALUE`)**
* A sample value from the source data
* For string types, defaults to `"string value"` for privacy/security
* Helps users understand the data content
* **Read-only** - Captured during scanning
**Has Embedded Content (`HAS_EMBEDDED_CONTENT`)**
* Boolean indicating if this attribute contains embedded/stringified JSON
* Triggers recursive parsing of the embedded content
* When `TRUE`, DataPancake recursively parses the JSON string to discover nested attributes
* **Read-only** - Detected during scanning
***
### Attribute Classification
**Attribute Type (`ATTRIBUTE_TYPE`)**
* The origin of the attribute
* Values:
* `'Discovered'` - Found during scanning
* `'Schema'` - Created from schema sample
* `'Virtual'` - User-created custom attribute
* **Read-only** - Set based on how attribute was created
**Attribute Schema Component Type (`ATTRIBUTE_SCHEMA_COMPONENT_TYPE`)**
* Classifies the schema component type
* Used for internal organization and categorization
* **Read-only** - Set during discovery
**Version Status Date (`VERSION_STATUS_DATE`)**
* Timestamp when polymorphic version was created or last activated
* Used for tracking schema evolution
* **Read-only** - Set when version is created/activated
***
### Status Control
**Attribute Record Status (`RECORD_STATUS`)**
* Controls whether attribute is active or inactive
* Values: `'active'`, `'inactive'`
* **Editable** - Only editable field in Source Schema tab
* Active attributes included in code generation (if `INCLUDE_IN_CODE_GEN = TRUE`)
* Inactive attributes excluded from code generation
***
## Virtual Attributes
User-created custom attributes (created via UI or `sp_upsert_virtual_datasource_attribute`).
**Required fields:**
* Attribute Name (no spaces)
* Source Data Type
* Snowflake Data Type
* Transformation Expression (SQL)
**Optional fields:**
* Parent Array (for array-level virtual attributes)
* `W_QUESTION_CATEGORY` (for Cortex Analyst semantic models)
* Description
**Characteristics:**
* Single polymorphic version (no polymorphism)
* Automatically set to `INCLUDE_IN_CODE_GEN = TRUE`
* `ATTRIBUTE_TYPE = 'Virtual'`
* Can reference other attributes using `{attribute_name}` placeholder in expressions
***
## Common Scenarios
**Understanding nested structure:**
* Use `ATTRIBUTE_LEVEL` (0 = root, increments per level) and `PARENT_OBJECT` to understand nesting
**Identifying embedded JSON:**
* Check `HAS_EMBEDDED_CONTENT = TRUE` for stringified JSON
* `ATTRIBUTE_PATH_EMBEDDED` shows nested structure within string fields
**Working with arrays:**
* `ARRAY_TYPE` indicates `'object'`, `'primitive'`, or `'primitive,object'`
* `ARRAY_PRIMITIVE_TYPE` (if applicable) shows element type
* `PARENT_ARRAY` shows containing array for nested attributes
**Deactivating attributes:**
* Set `RECORD_STATUS = 'inactive'` to exclude from code generation without deleting
* Useful for temporarily excluding, preserving history, or testing
# Destination Schema
Configuration for mapping source attributes to Snowflake data types, including precision, scale, datetime formats, primary key identification, and data quality indicators for Dynamic Tables.
## Overview
Destination Schema metadata controls how source attributes map to Snowflake data types and identifies key characteristics for Dynamic Tables. Configure target schema here.
***
## Snowflake Data Type Configuration
### Data Platform Data Type
**Data Platform Data Type (`DATA_PLATFORM_DATA_TYPE`)**
* Snowflake data type for this attribute
* Examples: `VARCHAR`, `VARCHAR(n)`, `NUMBER`, `NUMBER(p,s)`, `TIMESTAMP_NTZ`, `DATE`, `BOOLEAN`, `VARIANT`
* **Editable** - Override inferred type
* DataPancake infers from source data; override when inference is incorrect or you need specific types/precision
***
### Numeric Type Configuration
**Data Platform Data Type Precision (`DATA_PLATFORM_DATA_TYPE_PRECISION`)**
* For numeric types, precision (total number of digits)
* **Editable** - Applies to `DECIMAL`, `DEC`, `NUMERIC`, `NUMBER`
* Example: For `NUMBER(10,2)`, precision is 10
**Data Platform Data Type Scale (`DATA_PLATFORM_DATA_TYPE_SCALE`)**
* For numeric types, scale (number of decimal places)
* **Editable** - Applies to `DECIMAL`, `DEC`, `NUMERIC`, `NUMBER`
* Example: For `NUMBER(10,2)`, scale is 2
**Note:** Set based on known data ranges; avoid over-sizing (wastes storage).
***
### DateTime Configuration
**Data Platform Data Type DateTime Format (`DATA_PLATFORM_DATA_TYPE_DATETIMEFORMAT`)**
* Comma-separated list of valid Snowflake datetime formats
* Used for accurate datetime parsing
* **Editable** - Users can specify formats if inference is incorrect
* Example: `'MM/DD/YYYY', 'YYYY-MM-DD', 'MM-DD-YYYY HH24:MI:SS'`
* See [Snowflake datetime formats documentation](https://docs.snowflake.com/en/sql-reference/data-types-datetime#date-and-time-formats)
**Use DateTime Format (`USE_DATETIME_FORMAT`)**
* Boolean for using datetime formats in Dynamic Table SQL generation
* **Editable**
* `FALSE`: Uses `try_to_{data_type}` with `AUTO` detection
* `TRUE`: Uses `DATA_PLATFORM_DATA_TYPE_DATETIMEFORMAT` for parsing
**When to use formats:** Consistent formats, need precise control, `AUTO` unreliable.
**When to use AUTO:** Inconsistent/unknown formats, want Snowflake auto-detection.
***
### Key Identification
**Is Primary Key (`IS_PRIMARY_KEY`)**
* Boolean indicating if attribute is part of primary key
* **Editable** - Multiple attributes can be marked (composite keys)
* Used for metadata table generation and relationship tracking
**Has All Unique Values (`HAS_ALL_UNIQUE_VALUES`)**
* Boolean indicating if all values in this version are unique
* **Editable** - Mark based on data analysis
* Useful for identifying potential primary keys
***
### Data Quality Indicators
**Is Enum (`IS_ENUM`)**
* Boolean indicating if version contains only enum values
* **Editable** - Mark for categorical data (e.g., status codes)
* Useful for validation and documentation
**Has All Null Values (`HAS_ALL_NULL_VALUES`)**
* Boolean indicating if all values are null
* **Read-only** - Detected during scanning
* Useful for identifying unused attributes
***
## Reference Fields (Read-only)
Shown for reference in Destination Schema tab:
* `ATTRIBUTE_PATH` - Attribute identification
* `POLYMORPHIC_ATTRIBUTE_NAME` - Version name
* `SOURCE_DATA_TYPE` - Source type reference
* `SAMPLE_VALUE` - Data content reference
* `ATTRIBUTE_FOREIGN_KEY_COLUMNS` - Configured in Foreign Keys section
***
## Data Type Selection
**String types:**
* `VARCHAR` - Variable length without constraints
* `VARCHAR(n)` - Known maximum length
* `VARIANT` - Truly variable type, preserve original structure
**Numeric types:**
* Set precision/scale based on known data ranges
* Balance flexibility vs. storage efficiency
* Financial data: Use appropriate scale (typically 2-4 decimal places)
**DateTime types:**
* `DATE` - Date only
* `TIME` - Time only
* `TIMESTAMP_NTZ` - Timestamp without timezone
* `TIMESTAMP_LTZ` - Timestamp with local timezone
* `TIMESTAMP_TZ` - Timestamp with timezone
See [Snowflake data types documentation](https://docs.snowflake.com/en/sql-reference/data-types.html) for details.
***
## Common Scenarios
**Correcting incorrect type inference:**
1. Change `DATA_PLATFORM_DATA_TYPE` to correct type
2. Set `DATA_PLATFORM_DATA_TYPE_PRECISION` and `DATA_PLATFORM_DATA_TYPE_SCALE` if numeric
3. Verify with sample data
**Configuring datetime formats:**
1. Check `SAMPLE_VALUE` for actual format
2. Set `DATA_PLATFORM_DATA_TYPE_DATETIMEFORMAT` with correct format(s)
3. Set `USE_DATETIME_FORMAT = TRUE`
4. Test with code generation
**Setting primary keys:**
1. Identify attributes that uniquely identify records
2. Set `IS_PRIMARY_KEY = TRUE` (multiple for composite keys)
3. Verify uniqueness with `HAS_ALL_UNIQUE_VALUES`
**Optimizing numeric types:**
1. Analyze actual data ranges
2. Set `DATA_PLATFORM_DATA_TYPE_PRECISION` to minimum needed
3. Set `DATA_PLATFORM_DATA_TYPE_SCALE` appropriately
---
[Next Page](/llms-full.txt/1)