Sprinkle Docs
  • What is Sprinkle?
  • Quick Start
  • Analysing your data
    • 🔭Analytics Overview
    • 💠Data Models
      • *️Variables
      • 🌲Hierarchies
      • 🤿Column Mask
    • 🎉Switch to New Reports & Dashboards
    • 🆕Reports
      • Overview
      • Build Using Tables
        • Create a new Report
        • Layout and options
        • Build and Format - Overview
        • Apply Row Limits
        • Identify Date Columns
        • Filter your data
        • Visualizations
          • Table
          • Pivot
          • Line Chart
          • Bar Chart
          • Column Chart
          • Area Chart
          • Combo Chart
          • Scatter & Bubble Plot
          • Pie Chart
          • Funnel Chart
          • Stat Card
          • Point Map
          • Heat Map
          • Radial gauge chart
        • Advanced Features
          • Custom Analysis
          • Variables
          • Table & Quick Calculations
          • Drill - Hierarchical & Date
          • Break Out
          • RLS in Table reports
          • Scheduled Exports
          • Embedding Table Reports
      • Build Using Models
        • Create a new report
        • Layout and options
        • Visualizations
        • Advanced Features
      • Build SQL Reports
        • Create a new Report
        • Layout and options
        • Writing a SQL Code on Editor
        • Visualizations
        • Variables in SQL Reports
    • 🆕Dashboards
      • 🌀Filters
      • 👆Click Behaviour
      • ⏰Data Alerts
      • 🗓️Date Drill
      • 📤Scheduled Exports
      • 🔗Embed link
      • 🖥️Dashboard layout
      • 📱Mobile Dashboards
  • Transforming your data
    • 🔰SQL Transform
    • 📓Python Notebooks
  • Integrating your data
    • ☁️Destination Warehouses
      • AWS Athena
        • Manage storage of Flow tables
      • AWS Redshift
      • Azure Synapse
      • Databricks
      • Google BigQuery
      • MySQL
      • Postgres
      • Snowflake
      • SQL Server
      • K8 Setup
        • AWS EKS
        • Google GKE
        • Azure AKS
    • ⚙️Warehouse & Storage Setup
  • Ingesting your data
    • ☄️Data Imports
      • Databases
        • Azure Cosmos DB
        • Azure Table Storage
        • Google BigQuery
        • Mongo DB
        • MySQL DB
        • Oracle DB
        • Postgres DB
        • SQL Server DB
        • Features
          • Ingestion Modes
          • Add Multiple Datasets
          • CDC Setup
            • CDC setup in Mysql
            • CDC setup in Postgres
            • CDC setup in Mongo
            • CDC setup in SQL Server
          • Destination Create Table Clause
          • SSH Tunnel Setup
      • Files
        • AWS S3
        • AWS S3 External
        • Azure Blob
        • FTP
        • Google Cloud Storage
        • Google Sheet
        • SFTP
      • Applications
        • Apple Search Ads
        • Appsflyer
        • Branch
        • Clevertap
        • Facebook Ads
        • Freshdesk
        • Freshsales
        • Google Ads
        • Google Ads V2
        • Google Analytics
        • Google Analytics 4
        • Google Analytics MCF
        • Google Search Console
        • Hubspot
        • Impact Ads
        • Intercom
        • Klaviyo
        • Leadsquared
        • LinkedIn Ads
        • Magento
        • Mailchimp
        • Marketo
        • Mixpanel
        • MoEngage
        • Rocketlane
        • Salesforce
        • SAP S4
        • Shopify
        • Snapchat Marketing
        • TikTok Ads
        • WooCommerce
        • Zendesk Chat
        • Zendesk Support
        • Zoho Analytics
        • Zoho Books
        • Zoho CRM
        • Zoho Desk
        • Zoho Invoice
        • Zoho Subscription
      • Events
        • Apache Kafka
        • AWS Kinesis
        • Azure EventHub
    • 📤File Uploads
    • 🤖API Pulls
    • 🕸️Webhooks
  • Collaborating on data
    • 📤Sharing
    • 💬Comments
    • ⚡Activity
    • 🏷️Labels
  • Managing Schedules and Data Refreshes
    • ⏱️Schedules
    • 🔔Notifications
  • User Management
    • 🔑Access Management
    • 🧑‍🤝‍🧑Groups
    • 📂Folders
    • 🔄Syncing users, groups and RLS
    • 📧Azure AD Integration
  • Data Security & Privacy
    • 🔐Security at Sprinkle
    • 📄GDPR
    • 📄Privacy Policy
  • Release Notes
    • 📢Release Notes
      • 🗒️Release Notes - v12.1 (New)
      • 🗒️Release Notes - v12.0
      • 🗒️Release Notes - v11.0
      • 🗒️Release Notes - v10.8
      • 🗒️Release Notes - v10.7
      • 🗒️Release Notes - v10.6
      • 🗒️Release Notes - v10.5
      • 🗒️Release Notes - v10.4
      • 🗒️Release Notes - v10.3
      • 🗒️Release Notes - v10.2
      • 🗒️Release Notes - v10.1
      • 🗒️Release Notes - v10.0
      • 🗒️Release Notes - v9.31
      • 🗒️Release Notes - v9.30
      • 🗒️Release Notes - v9.29
      • 🗒️Release Notes - v9.28
      • 🗒️Release Notes - v9.27
      • 🗒️Release Notes - v9.25
      • 🗒️Release Notes - v9.24
      • 🗒️Release Notes - v9.23
      • 🗒️Release Notes - v9.22
      • 🗒️Release Notes - v9.21
      • 🗒️Release Notes - v9.20
      • 🗒️Release Notes - v9.19
      • 🗒️Release Notes - v9.18
      • 🗒️Release Notes - v9.17
      • 🗒️Release Notes - v9.16
      • 🗒️Release Notes - v9.14
      • 🗒️Release Notes - v9.13
      • 🗒️Release Notes - v9.12
      • 🗒️Release Notes -v9.8
      • 🗒️Release Notes - v9.7
      • 🗒️Release Notes - v9.6
      • 🗒️Release Notes - v9.5
      • 🗒️Release Notes - v9.4
      • 🗒️Release Notes - v9.3
      • 🗒️Release Notes - v9.2
      • 🗒️Release Notes - v9.1
      • 🗒️Release Notes - v9.0 (Major)
      • 🗒️Release Notes - v7.23
      • 🗒️Release Notes - v7.21
      • 🗒️Release Notes - v7.20
      • 🗒️Release Notes - v7.15
      • 🗒️Release Notes - v7.14
      • 🗒️Release Notes - v7.13
Powered by GitBook
On this page
  • BIGQUERY
  • Syntax
  • ATHENA
  • SNOWFLAKE
  • Syntax
  • REDSHIFT
  • Syntax
  1. Ingesting your data
  2. Data Imports
  3. Databases
  4. Features

Destination Create Table Clause

Overview of clauses that can be used in the Destination Create Table Clause field while adding tables to the destination (warehouse) through Data Imports in Sprinkle.

PreviousCDC setup in SQL ServerNextSSH Tunnel Setup

Last updated 1 year ago

The destination-create table clause in Sprinkle enables you to provide additional clauses for warehouse-create table queries such as clustering, partitioning, and more depending on the warehouse. It is useful for optimizing DML (Data Manipulation Language) statements to ensure optimal query performance.

Features like partitioning, Clustering, and more can be used by providing these clauses in the Destination Create Table Clause. Let's understand each in detail.

Clustering a table refers to sorting data based on the columns provided by the user which is highly beneficial in boosting query performance.

Clustering can be used when:

  1. If your query has multiple filtering columns and they often filter on some particular columns.

  2. If the filtered columns have too many unique values then clustering should be preferred.

  3. It is hard to decipher query cost estimation while clustering so if you don’t have any cost considerations clustering is the best option.

Partitioning is dividing a table into different segments that help in easy management and efficient querying of your data.

Partitioning can be used when :

  1. When the user needs to manage data in a particular partition.

  2. If a user wants to know the query cost before the query is run then in that case partition can be used.

There are more such clauses that can be useful and give you granular control while creating tables in the warehouse. Those are explained below in context to the type of data warehouse.

BIGQUERY

Syntax

PARTITION BY partition_expression
CLUSTER BY clustering_column_list

[PARTITION BY partition_expression]

ATHENA

The destination create table clause is not supported in Athena.

SNOWFLAKE

Syntax

CLUSTER BY ( <expr> [ , <expr> , ... ] ) 

[ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]

In cluster by one or more columns can be added as the clustering key on the basis of which clustering needs to be done.

REDSHIFT

Syntax

DISTSTYLE { AUTO | EVEN | KEY | ALL } 
DISTKEY ( column_name )
[COMPOUND | INTERLEAVED ] SORTKEY ( column_name [,...]) |  [ SORTKEY AUTO ]
ENCODE AUTO

[ DISTSTYLE { AUTO | EVEN | KEY | ALL } ]

Diststyle specifies the data distribution style for the complete table and helps in the arrangement of data to locate it easily. The default distribution style is Auto but according to requirements, users can use other distribution styles as well.

There are four distribution styles in redshift:

  1. Auto: It is the default distribution style selected by redshift that primarily depends on the size of the incoming data.

  2. Even: In this, data is evenly distributed by a leading node to other nodes and is the simplest distribution style.

  3. Key: This distribution style is selected when the table undergoes many joining operations or complex querying. It helps in ensuring optimal query performance.

  4. All: In this, a copy of every table is distributed among all the nodes, so this distribution style is generally preferred for tables small in size to save storage space on the redshift cluster.

[ DISTKEY ( column_name ) ]

In distkey users needs to specify the name of the column on the basis of which distribution style is to be applied.

[ [COMPOUND | INTERLEAVED ] SORTKEY

[ ENCODE AUTO ]

Partition by is an optional clause that helps to create a partition by the column name that is mentioned by the user. It has many partition expressions to know more about them

[CLUSTER BYclustering_column_list]

Cluster by is also an optional clause that creates clusters on the basis of the cluster columns specified. To know more about the clustering column list, .

To know more about how to create a table in BigQuery,

To know more about how to create a table in Snowflake,

In this users need to specify one or more columns that are to be selected as the sortkey. The sortkey determines the sorting order of data once it is loaded. Additionally, there are two sort styles offered by redshift for more information

Encode auto is the default encoding type on any column that is used to boost query performance in amazon redshift. For more information on encoding

To know more about how to create a table in Redshift,

☄️
➡️
➡️
➡️
click here
click here
click here
click here
click here
click here
click here